Skip to main content

​
Entity Relationship Overview

The database uses foreign keys to maintain referential integrity across all business units.

​
Core Relationships

​
User → Organization

users
  └── organization_id → organizations.id
Every user belongs to one organization. Organizations enable multi-tenant access.

​
User → Creator Profile

users
  └── creator_profiles.user_id → users.id (one-to-one)
Users who become creators get a profile linked to their account.

​
Publisher Relationships

​
Vertical → Sources → Posts

publisher_verticals
  └── publisher_ig_sources.vertical_id → publisher_verticals.id
        └── publisher_ig_posts.source_id → publisher_ig_sources.id
  └── publisher_posts.vertical_id → publisher_verticals.id
        └── publisher_post_analytics.post_id → publisher_posts.id
        └── publisher_post_viator_products.post_id → publisher_posts.id
Flow:
  1. Verticals define industry categories
  2. IG sources belong to verticals
  3. Scraped posts belong to sources
  4. Generated articles belong to verticals
  5. Analytics track article performance
  6. Viator products link articles to affiliate revenue

​
Content Analysis Chain

publisher_ig_posts
  └── content_analysis.source_post_id → publisher_ig_posts.id
        └── content_platform_scores.analysis_id → content_analysis.id
        └── content_recommendations.analysis_id → content_analysis.id
Scraped content flows through analysis pipeline.

​
Platform Relationships

​
Creator → Licenses → Purchases

creator_profiles
  └── creator_licenses.creator_id → creator_profiles.id
        └── creator_license_purchases.license_id → creator_licenses.id
  └── creator_payouts.creator_id → creator_profiles.id
Flow:
  1. Creators upload content
  2. Content becomes licensable
  3. Purchases track transactions
  4. Payouts track creator earnings

​
Verification Chain

users
  └── creator_profiles.user_id → users.id
        └── iris_verification_logs.creator_id → creator_profiles.id
        └── voice_profiles.creator_id → creator_profiles.id
Biometric verification links to creator identity.

​
Agency Relationships

​
Client → Projects → Deliverables

agency_clients
  └── agency_projects.client_id → agency_clients.id
        └── agency_deliverables.project_id → agency_projects.id
  └── agency_retainers.client_id → agency_clients.id
Flow:
  1. Clients onboard
  2. Projects define scoped work
  3. Deliverables track individual items
  4. Retainers track ongoing relationships

​
Store Relationships

​
Products → Orders

shopify_products
  └── shopify_order_items.product_id → shopify_products.id
        └── shopify_orders.id → shopify_order_items.order_id

shopify_customers
  └── shopify_orders.customer_id → shopify_customers.id
Standard e-commerce relationships.

​
Jarvis Relationships

​
Conversations → Messages → Tools

users
  └── jarvis_conversations.user_id → users.id
        └── jarvis_messages.conversation_id → jarvis_conversations.id
        └── jarvis_tool_logs.conversation_id → jarvis_conversations.id
Voice sessions link to users with full message and tool history.

​
Cross-Product Joins

​
Publisher Posts → Store Products

-- Which products appear in which articles?
SELECT 
  pp.title as article,
  sp.name as product
FROM publisher_posts pp
JOIN publisher_post_products ppp ON ppp.post_id = pp.id
JOIN shopify_products sp ON sp.id = ppp.product_id;

​
Creator → User → Orders

-- Creator's purchase history (for insights)
SELECT 
  cp.display_name,
  so.total,
  so.created_at
FROM creator_profiles cp
JOIN users u ON u.id = cp.user_id
JOIN shopify_orders so ON so.customer_email = u.email;

​
Agency Client → Shopify Orders

-- Client's store performance
SELECT 
  ac.company_name,
  COUNT(so.id) as orders,
  SUM(so.total) as revenue
FROM agency_clients ac
JOIN shopify_orders so ON so.client_id = ac.id
GROUP BY ac.id;

​
Visual Diagram

                    ┌──────────────┐
                    │    users     │
                    └──────┬───────┘
                           │
          ┌────────────────┼────────────────┐
          │                │                │
          ▼                ▼                ▼
   ┌──────────────┐ ┌──────────────┐ ┌──────────────┐
   │organizations │ │creator_      │ │jarvis_       │
   │              │ │profiles      │ │conversations │
   └──────────────┘ └──────┬───────┘ └──────┬───────┘
                           │                │
          ┌────────────────┤                │
          ▼                ▼                ▼
   ┌──────────────┐ ┌──────────────┐ ┌──────────────┐
   │creator_      │ │voice_        │ │jarvis_       │
   │licenses      │ │profiles      │ │messages      │
   └──────────────┘ └──────────────┘ └──────────────┘

   ┌──────────────┐
   │publisher_    │
   │verticals     │
   └──────┬───────┘
          │
   ┌──────┴───────┐
   ▼              ▼
┌──────────┐ ┌──────────┐
│ig_sources│ │posts     │
└────┬─────┘ └────┬─────┘
     │            │
     ▼            ▼
┌──────────┐ ┌──────────┐
│ig_posts  │ │analytics │
└──────────┘ └──────────┘

​
Indexing Strategy

All foreign keys have indexes for JOIN performance: 
-- Example index creation
CREATE INDEX idx_publisher_posts_vertical_id 
ON publisher_posts(vertical_id);

CREATE INDEX idx_jarvis_messages_conversation_id 
ON jarvis_messages(conversation_id);

Full Table Reference

See all column definitions