# MicProtocol – Database Schema

## Entity Relationship Diagram

```mermaid
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