docs/database-schema.md

MicProtocol – Database Schema

Entity Relationship Diagram

erDiagram
    USER ||--o{ WORKSPACE_MEMBER : member_of
    WORKSPACE ||--o{ WORKSPACE_MEMBER : has_members
    WORKSPACE ||--o{ PODCAST : owns
    PODCAST ||--o{ GUEST : prospects
    PODCAST ||--o{ EPISODE_TEMPLATE : defines
    PODCAST ||--o{ TAG : categorizes
    GUEST ||--o{ GUEST_TAG : tagged
    TAG ||--o{ GUEST_TAG : categorized
    GUEST ||--o{ OUTREACH_SEQUENCE : enrolled_in
    OUTREACH_SEQUENCE ||--o{ OUTREACH_MESSAGE : contains
    GUEST ||--o{ EPISODE : featured_in
    PODCAST ||--o{ EPISODE : produces
    EPISODE ||--o{ BOOKING : scheduled_via
    EPISODE ||--o{ ASSET_REQUEST : requires
    ASSET_REQUEST ||--o{ ASSET_SUBMISSION : fulfilled_by
    EPISODE ||--o{ PROMO_TASK : generates
    
    USER {
        uuid id PK
        string email UK
        string clerk_id UK
        json profile
        string default_timezone
        timestamp created_at
        timestamp updated_at
    }

    WORKSPACE {
        uuid id PK
        string name
        string slug UK
        json billing_settings
        timestamp created_at
        timestamp updated_at
    }

    WORKSPACE_MEMBER {
        uuid id PK
        uuid user_id FK
        uuid workspace_id FK
        enum role "owner|admin|editor|viewer"
        timestamp joined_at
    }

    PODCAST {
        uuid id PK
        uuid workspace_id FK
        string title
        string rss_feed_url
        json recording_settings
        json outreach_defaults
        string calendar_integration_id
        timestamp created_at
        timestamp updated_at
    }

    GUEST {
        uuid id PK
        uuid podcast_id FK
        string email "unique per podcast"
        string full_name
        string company
        string title
        json social_links
        text bio
        enum status "prospect|contacted|scheduled|recorded|published"
        int priority_score "0-100"
        timestamp last_contact_at
        json custom_fields
        timestamp created_at
        timestamp updated_at
    }

    TAG {
        uuid id PK
        uuid podcast_id FK
        string name "unique per podcast"
        string color
    }

    GUEST_TAG {
        uuid id PK
        uuid guest_id FK
        uuid tag_id FK
        timestamp added_at
    }

    OUTREACH_SEQUENCE {
        uuid id PK
        uuid guest_id FK
        enum status "active|paused|completed"
        uuid template_id FK
        int current_step
        timestamp started_at
        timestamp updated_at
    }

    OUTREACH_MESSAGE {
        uuid id PK
        uuid sequence_id FK
        enum type "initial|followup|booking|reminder"
        string subject
        text body_html
        text body_text
        timestamp sent_at
        timestamp opened_at
        timestamp clicked_at
        json metadata
    }

    EPISODE {
        uuid id PK
        uuid podcast_id FK
        uuid guest_id FK
        string title
        enum status "draft|scheduled|recorded|editing|published"
        timestamp scheduled_at
        int duration_minutes
        text host_notes
        text guest_instructions
        enum recording_platform "riverside|zoom|studio"
        timestamp created_at
        timestamp updated_at
    }

    EPISODE_TEMPLATE {
        uuid id PK
        uuid podcast_id FK
        string name
        int duration_minutes
        json required_assets
        text guest_instructions
    }

    BOOKING {
        uuid id PK
        uuid episode_id FK
        string calendar_event_id
        timestamp start_time
        timestamp end_time
        string timezone
        string meet_link
        json calendar_provider_data
        boolean is_confirmed
    }

    ASSET_REQUEST {
        uuid id PK
        uuid episode_id FK
        enum type "headshot|bio|social_links|intro_audio|talking_points"
        boolean is_required
        text instructions
        timestamp due_date
    }

    ASSET_SUBMISSION {
        uuid id PK
        uuid request_id FK
        string file_url
        string file_type
        int file_size
        enum status "pending|approved|rejected"
        timestamp submitted_at
    }

    PROMO_TASK {
        uuid id PK
        uuid episode_id FK
        string title
        text description
        string platform
        timestamp scheduled_at
        timestamp completed_at
        json metadata
    }

Key Design Decisions

Guest Uniqueness is Per-Podcast

A guest can appear on multiple shows with different statuses. The composite unique constraint @@unique([podcastId, email]) allows this while preventing duplicates within a single podcast.

Outreach Sequences are State Machines

Sequences track currentStep and have active|paused|completed status, enabling A/B testing of cadence (e.g., Day 0, Day 3, Day 7 follow-ups). Messages track sentAt, openedAt, and clickedAt independently.

Asset Requests are Template-Driven

Podcasts define required assets per episode template via JSON in EpisodeTemplate.requiredAssets. When an episode is created from a template, asset requests are automatically generated.

Row-Level Security

The workspace → podcast → guest hierarchy ensures data isolation. All queries scope through podcastId, which chains to workspaceId, preventing cross-workspace data leakage.

Index Strategy

Performance-critical queries are covered by composite indexes:

• guests(podcast_id, status) — Kanban board column queries
• guests(podcast_id, priority_score) — priority sorting
• outreach_sequences(guest_id, status) — active sequence lookups
• bookings(start_time, end_time) — calendar overlap detection
• asset_requests(episode_id, type) — completion status checks