Aurora-Core/docs/erd.md

Aurora Core — Entity-Relationship Diagram

This is the Core 0.1 schema (36 tables, see database.md for column-level detail). Association/junction tables are drawn as relationships directly where Mermaid supports it; a few many-to-many tables with their own attributes (movie_companies.role, credits.*) are drawn as entities.

erDiagram
    MOVIE ||--o{ MOVIE_ALTERNATE_TITLE : has
    MOVIE ||--o{ MOVIE_SPOKEN_LANGUAGE : has
    MOVIE ||--o{ MOVIE_PRODUCTION_COUNTRY : has
    MOVIE ||--o{ RELEASE_DATE : has
    MOVIE ||--o{ MOVIE_RECOMMENDATION : recommends
    MOVIE }o--o{ GENRE : "movie_genres"
    MOVIE }o--o{ KEYWORD : "movie_keywords"
    MOVIE }o--o{ COMPANY : "movie_companies"
    MOVIE }o--|| COLLECTION : "belongs to"
    MOVIE ||--o{ CREDIT : "cast/crew"
    MOVIE ||--o{ ARTWORK : "images"
    MOVIE ||--o{ VIDEO : "trailers"
    MOVIE ||--o{ EXTERNAL_ID : "provider refs"

    SERIES ||--o{ SEASON : has
    SEASON ||--o{ EPISODE : has
    SERIES ||--o{ SERIES_ALTERNATE_TITLE : has
    SERIES ||--o{ SERIES_SPOKEN_LANGUAGE : has
    SERIES ||--o{ SERIES_PRODUCTION_COUNTRY : has
    SERIES }o--o{ GENRE : "series_genres"
    SERIES }o--o{ KEYWORD : "series_keywords"
    SERIES }o--o{ COMPANY : "series_companies (network/studio)"
    SERIES ||--o{ CREDIT : "cast/crew/creators"
    SERIES ||--o{ ARTWORK : "images"
    SERIES ||--o{ VIDEO : "trailers"
    SERIES ||--o{ EXTERNAL_ID : "provider refs"
    EPISODE ||--o{ CREDIT : "cast/guest stars/crew"
    EPISODE ||--o{ ARTWORK : "stills"
    EPISODE ||--o{ EXTERNAL_ID : "provider refs"
    SEASON ||--o{ ARTWORK : "posters"
    SEASON ||--o{ EXTERNAL_ID : "provider refs"

    COLLECTION ||--o{ MOVIE : "collection_movies"
    COLLECTION ||--o{ SERIES : "collection_series"
    COLLECTION ||--o{ COLLECTION : "collection_relations"
    COLLECTION ||--o{ ARTWORK : "images"

    COMPANY ||--o{ COMPANY : "parent_company"
    COMPANY ||--o{ ARTWORK : "logos"
    COMPANY ||--o{ EXTERNAL_ID : "provider refs"
    COMPANY }o--o| COUNTRY : "based in"

    PERSON ||--o{ CREDIT : "credited on"
    PERSON ||--o{ ARTWORK : "profile images"
    PERSON ||--o{ EXTERNAL_ID : "provider refs"

    PROVIDER ||--o{ PROVIDER_RECORD : "provenance"
    PROVIDER ||--o{ INGESTION_JOB : "runs"
    PROVIDER ||--o{ EXTERNAL_ID : "issues"
    PROVIDER ||--o{ ARTWORK : "sourced"
    PROVIDER ||--o{ VIDEO : "sourced"
    PROVIDER ||--o{ CREDIT : "sourced"
    INGESTION_JOB ||--o{ PROVIDER_RECORD : produces

    COUNTRY ||--o{ CERTIFICATION : defines
    RELEASE_DATE }o--o| CERTIFICATION : rated

    MOVIE {
        uuid id PK
        string title
        string sort_title
        enum status
        date primary_release_date
        int runtime_minutes
        uuid collection_id FK
        uuid source_provider_id FK
    }
    SERIES {
        uuid id PK
        string title
        enum status
        date first_air_date
        int number_of_seasons
    }
    SEASON {
        uuid id PK
        uuid series_id FK
        int season_number
    }
    EPISODE {
        uuid id PK
        uuid season_id FK
        uuid series_id FK
        int episode_number
    }
    PERSON {
        uuid id PK
        string name
        date birthday
        string known_for_department
    }
    CREDIT {
        uuid id PK
        uuid person_id FK
        string entity_type
        uuid entity_id
        string department
        string job
        string character
    }
    COMPANY {
        uuid id PK
        string name
        enum company_type
        uuid parent_company_id FK
    }
    COLLECTION {
        uuid id PK
        string name
        string slug
    }
    GENRE {
        uuid id PK
        string name
    }
    KEYWORD {
        uuid id PK
        string name
    }
    ARTWORK {
        uuid id PK
        string entity_type
        uuid entity_id
        enum artwork_type
        uuid provider_id FK
        string original_url
    }
    VIDEO {
        uuid id PK
        string entity_type
        uuid entity_id
        enum video_type
        uuid provider_id FK
        string url
    }
    EXTERNAL_ID {
        uuid id PK
        string entity_type
        uuid entity_id
        uuid provider_id FK
        string external_id
    }
    PROVIDER {
        uuid id PK
        string slug
        string name
    }
    PROVIDER_RECORD {
        uuid id PK
        uuid provider_id FK
        string entity_type
        string external_id
        uuid aurora_entity_id
        enum match_confidence
        jsonb raw_data
    }
    INGESTION_JOB {
        uuid id PK
        uuid provider_id FK
        string job_type
        enum status
        string idempotency_key
    }
    COUNTRY {
        string code PK
        string name
    }
    CERTIFICATION {
        uuid id PK
        string country_code FK
        string system
        string rating
    }
    RELEASE_DATE {
        uuid id PK
        uuid entity_id
        string country_code FK
        date release_date
        uuid certification_id FK
    }

Notes on the polymorphic tables

CREDIT, ARTWORK, VIDEO, EXTERNAL_ID, and RELEASE_DATE attach to more than one entity type (movie, series, season, episode, person, company, collection) via an (entity_type, entity_id) pair rather than a typed foreign key. Postgres can't enforce referential integrity across a polymorphic pair with a single FOREIGN KEY, so integrity there is enforced by the repository/service layer (app/repositories/polymorphic.py), not the schema. See database.md for the tradeoff.