Spaces:
Running
Running
| # 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 | |