# 02 — Data Model

Authoritative relational schema for users, libraries, media, playlists,
play history, recommendations, and operational tables. Provided in
**PostgreSQL** dialect (normative) with **SQLite** deltas listed in §7.
Migrations are managed with `golang-migrate`; this document corresponds to
migration `0001_initial`.

## 1. Entity-relationship overview

```mermaid
erDiagram
    USERS ||--o{ SESSIONS : has
    USERS ||--o{ API_KEYS : has
    USERS ||--o{ USER_LIBRARIES : "granted"
    LIBRARIES ||--o{ USER_LIBRARIES : "grants"
    SOURCES ||--|| LIBRARIES : "backs"
    LIBRARIES ||--o{ MEDIA_FILES : contains
    MEDIA_FILES ||--|| TRACKS : "manifests as"
    ALBUMS ||--o{ TRACKS : groups
    ARTISTS ||--o{ ALBUMS : "credited (album artist)"
    ARTISTS ||--o{ TRACK_ARTISTS : credited
    TRACKS ||--o{ TRACK_ARTISTS : credits
    GENRES ||--o{ TRACK_GENRES : tags
    TRACKS ||--o{ TRACK_GENRES : tagged
    USERS ||--o{ PLAYLISTS : owns
    PLAYLISTS ||--o{ PLAYLIST_ITEMS : contains
    TRACKS ||--o{ PLAYLIST_ITEMS : "appears in"
    USERS ||--o{ PLAY_HISTORY : plays
    TRACKS ||--o{ PLAY_HISTORY : played
    USERS ||--o{ ANNOTATIONS : rates
    TRACKS ||--o{ AUDIO_FEATURES : "has features"
    USERS ||--|| USER_SETTINGS : configures
    USERS ||--o{ PLAYERS : registers
    LIBRARIES ||--o{ SCANS : scanned
    USERS ||--o{ STREAM_TOKENS : mints
```

## 2. Conventions

- Primary keys: UUIDv7, column `id`.
- `created_at` / `updated_at` on every mutable table; `updated_at` maintained
  by application code (not triggers) for SQLite parity.
- Soft state (e.g. `missing` media files) over hard deletes where re-scans can
  resurrect rows; everything else uses `ON DELETE CASCADE` deliberately.
- Text normalization: `order_*` columns hold case-folded, article-stripped,
  accent-folded sort keys computed at write time (e.g. "The Beatles" →
  "beatles"). Searching uses `search_text` columns (lowercased concatenation),
  with Postgres trigram index / SQLite FTS5 (see §6).

## 3. DDL — identity & access

```sql
CREATE TABLE users (
    id              uuid PRIMARY KEY,
    username        text NOT NULL UNIQUE,          -- case-insensitive unique via index below
    email           text,
    password_hash   text NOT NULL,                 -- argon2id PHC string, see docs/05 §3
    is_admin        boolean NOT NULL DEFAULT false,
    is_active       boolean NOT NULL DEFAULT true,
    last_login_at   timestamptz,
    created_at      timestamptz NOT NULL,
    updated_at      timestamptz NOT NULL
);
CREATE UNIQUE INDEX users_username_ci ON users (lower(username));

CREATE TABLE sessions (
    id              uuid PRIMARY KEY,
    user_id         uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash      text NOT NULL UNIQUE,          -- sha256(token); raw token only in cookie
    user_agent      text,
    ip_created      text,
    expires_at      timestamptz NOT NULL,
    created_at      timestamptz NOT NULL,
    last_seen_at    timestamptz NOT NULL
);
CREATE INDEX sessions_user ON sessions(user_id);
CREATE INDEX sessions_expiry ON sessions(expires_at);

CREATE TABLE api_keys (
    id              uuid PRIMARY KEY,
    user_id         uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name            text NOT NULL,                 -- user-visible label, e.g. "Pixel 8"
    token_hash      text NOT NULL UNIQUE,
    scopes          text NOT NULL DEFAULT 'full',  -- 'full' | 'readonly'
    last_used_at    timestamptz,
    expires_at      timestamptz,                   -- NULL = no expiry
    created_at      timestamptz NOT NULL
);
CREATE INDEX api_keys_user ON api_keys(user_id);

-- Track-scoped, short-lived tokens for Cast devices / <audio> tags.
CREATE TABLE stream_tokens (
    id              uuid PRIMARY KEY,
    user_id         uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash      text NOT NULL UNIQUE,
    track_id        uuid REFERENCES tracks(id) ON DELETE CASCADE,  -- NULL = session-scoped (queue handoff)
    player_id       uuid REFERENCES players(id) ON DELETE SET NULL,
    expires_at      timestamptz NOT NULL,
    created_at      timestamptz NOT NULL
);
CREATE INDEX stream_tokens_expiry ON stream_tokens(expires_at);
```

## 4. DDL — storage, libraries, media

```sql
CREATE TABLE sources (
    id                  uuid PRIMARY KEY,
    kind                text NOT NULL CHECK (kind IN ('s3','webdav')),
    name                text NOT NULL,
    -- Common
    root_path           text NOT NULL DEFAULT '',   -- S3 key prefix or WebDAV sub-path
    -- S3
    s3_endpoint         text,                       -- NULL = AWS default endpoint
    s3_region           text,
    s3_bucket           text,
    s3_force_path_style boolean NOT NULL DEFAULT false,
    s3_access_key_enc   bytea,                      -- AES-256-GCM, see docs/05 §7
    s3_secret_key_enc   bytea,
    s3_allow_presign    boolean NOT NULL DEFAULT false,
    s3_presign_ttl_secs integer NOT NULL DEFAULT 3600,
    -- WebDAV
    dav_base_url        text,
    dav_username        text,
    dav_password_enc    bytea,
    dav_auth_mode       text CHECK (dav_auth_mode IN ('basic','digest','none')),
    dav_insecure_tls    boolean NOT NULL DEFAULT false,
    -- Health
    last_check_at       timestamptz,
    last_check_ok       boolean,
    last_check_error    text,
    created_at          timestamptz NOT NULL,
    updated_at          timestamptz NOT NULL,
    CHECK (
      (kind = 's3'     AND s3_bucket IS NOT NULL AND s3_region IS NOT NULL)
      OR
      (kind = 'webdav' AND dav_base_url IS NOT NULL)
    )
);

CREATE TABLE libraries (
    id              uuid PRIMARY KEY,
    source_id       uuid NOT NULL UNIQUE REFERENCES sources(id) ON DELETE RESTRICT,
    name            text NOT NULL UNIQUE,
    auto_scan_cron  text,                           -- e.g. '0 3 * * *'; NULL = manual only
    last_scan_at    timestamptz,
    track_count     integer NOT NULL DEFAULT 0,     -- denormalized, maintained by scanner
    total_bytes     bigint  NOT NULL DEFAULT 0,
    created_at      timestamptz NOT NULL,
    updated_at      timestamptz NOT NULL
);

CREATE TABLE user_libraries (
    user_id     uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    library_id  uuid NOT NULL REFERENCES libraries(id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, library_id)
);

CREATE TABLE media_files (
    id              uuid PRIMARY KEY,
    library_id      uuid NOT NULL REFERENCES libraries(id) ON DELETE CASCADE,
    object_key      text NOT NULL,                  -- S3 key / WebDAV href (decoded, source-relative)
    size_bytes      bigint NOT NULL,
    etag            text,                           -- S3 ETag; WebDAV getetag if present
    last_modified   timestamptz,                    -- backend mtime
    content_type    text,
    suffix          text NOT NULL,                  -- 'flac','mp3','ogg','m4a','opus','wav',...
    missing         boolean NOT NULL DEFAULT false, -- vanished from backend; grace before delete
    missing_since   timestamptz,
    scanned_at      timestamptz NOT NULL,
    created_at      timestamptz NOT NULL,
    updated_at      timestamptz NOT NULL,
    UNIQUE (library_id, object_key)
);
CREATE INDEX media_files_missing ON media_files(library_id, missing);
```

### Canonical musical entities

The scanner derives `artists`, `albums`, `tracks` from tags. Identity rules:

- **Artist identity:** `mbz_artist_id` when present, else
  `lower(name)` within the whole server (artists are global, not per-library —
  matches Subsonic client expectations).
- **Album identity:** `mbz_album_id` when present, else
  `(album_artist_id, lower(album_name), release_year?)`. Multi-disc albums are
  one album row.
- A `tracks` row exists 1:1 with a non-missing `media_files` row (duplicate
  tags → duplicate tracks; de-dup is a UI concern, not a model concern).

```sql
CREATE TABLE artists (
    id              uuid PRIMARY KEY,
    name            text NOT NULL,
    order_name      text NOT NULL,                  -- sort key (folded)
    mbz_artist_id   text,                           -- MusicBrainz, nullable
    image_object    text,                           -- cache key of fetched/derived image
    album_count     integer NOT NULL DEFAULT 0,
    track_count     integer NOT NULL DEFAULT 0,
    search_text     text NOT NULL,
    created_at      timestamptz NOT NULL,
    updated_at      timestamptz NOT NULL
);
CREATE INDEX artists_order ON artists(order_name);
CREATE UNIQUE INDEX artists_mbz ON artists(mbz_artist_id) WHERE mbz_artist_id IS NOT NULL;

CREATE TABLE albums (
    id              uuid PRIMARY KEY,
    name            text NOT NULL,
    order_name      text NOT NULL,
    album_artist_id uuid REFERENCES artists(id) ON DELETE SET NULL,  -- NULL = Various Artists
    is_compilation  boolean NOT NULL DEFAULT false,
    release_year    integer,
    mbz_album_id    text,
    genre_primary   text,
    cover_media_id  uuid,                           -- media_file whose embedded art / folder art is used
    track_count     integer NOT NULL DEFAULT 0,
    duration_secs   double precision NOT NULL DEFAULT 0,
    size_bytes      bigint NOT NULL DEFAULT 0,
    search_text     text NOT NULL,
    created_at      timestamptz NOT NULL,
    updated_at      timestamptz NOT NULL
);
CREATE INDEX albums_artist ON albums(album_artist_id);
CREATE INDEX albums_order ON albums(order_name);
CREATE INDEX albums_year ON albums(release_year);
CREATE UNIQUE INDEX albums_mbz ON albums(mbz_album_id) WHERE mbz_album_id IS NOT NULL;

CREATE TABLE tracks (
    id              uuid PRIMARY KEY,
    media_file_id   uuid NOT NULL UNIQUE REFERENCES media_files(id) ON DELETE CASCADE,
    library_id      uuid NOT NULL REFERENCES libraries(id) ON DELETE CASCADE,
    album_id        uuid REFERENCES albums(id) ON DELETE SET NULL,
    title           text NOT NULL,
    order_title     text NOT NULL,
    track_number    integer,
    disc_number     integer,
    duration_secs   double precision NOT NULL DEFAULT 0,
    bitrate_kbps    integer,
    sample_rate_hz  integer,
    channels        integer,
    codec           text,                           -- 'flac','mp3','aac','opus','vorbis',...
    bpm             integer,
    release_year    integer,
    mbz_track_id    text,
    replaygain_track_db double precision,
    replaygain_album_db double precision,
    has_embedded_art boolean NOT NULL DEFAULT false,
    lyrics          text,
    comment         text,
    search_text     text NOT NULL,
    created_at      timestamptz NOT NULL,
    updated_at      timestamptz NOT NULL
);
CREATE INDEX tracks_album ON tracks(album_id, disc_number, track_number);
CREATE INDEX tracks_library ON tracks(library_id);
CREATE INDEX tracks_order_title ON tracks(order_title);

CREATE TABLE track_artists (
    track_id    uuid NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,
    artist_id   uuid NOT NULL REFERENCES artists(id) ON DELETE CASCADE,
    role        text NOT NULL DEFAULT 'artist' CHECK (role IN ('artist','albumartist','composer')),
    position    integer NOT NULL DEFAULT 0,         -- credit order
    PRIMARY KEY (track_id, artist_id, role)
);
CREATE INDEX track_artists_artist ON track_artists(artist_id);

CREATE TABLE genres (
    id      uuid PRIMARY KEY,
    name    text NOT NULL UNIQUE                    -- normalized: trimmed, title-cased
);

CREATE TABLE track_genres (
    track_id uuid NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,
    genre_id uuid NOT NULL REFERENCES genres(id) ON DELETE CASCADE,
    PRIMARY KEY (track_id, genre_id)
);
CREATE INDEX track_genres_genre ON track_genres(genre_id);
```

## 5. DDL — playlists, history, annotations, players, recommendations

```sql
CREATE TABLE playlists (
    id              uuid PRIMARY KEY,
    owner_id        uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name            text NOT NULL,
    comment         text,
    is_public       boolean NOT NULL DEFAULT false, -- visible read-only to other users of shared libraries
    item_count      integer NOT NULL DEFAULT 0,
    duration_secs   double precision NOT NULL DEFAULT 0,
    created_at      timestamptz NOT NULL,
    updated_at      timestamptz NOT NULL,
    UNIQUE (owner_id, name)
);

CREATE TABLE playlist_items (
    id          uuid PRIMARY KEY,
    playlist_id uuid NOT NULL REFERENCES playlists(id) ON DELETE CASCADE,
    track_id    uuid NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,
    position    integer NOT NULL,                   -- 0-based, dense; reorder = bulk position rewrite in one tx
    added_at    timestamptz NOT NULL,
    UNIQUE (playlist_id, position) DEFERRABLE INITIALLY DEFERRED
);
CREATE INDEX playlist_items_track ON playlist_items(track_id);

-- One row per *play event*. A play is recorded when the client reports
-- >= 50% listened OR >= 4 minutes (Subsonic scrobble semantics honoured too).
CREATE TABLE play_history (
    id              uuid PRIMARY KEY,
    user_id         uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    track_id        uuid NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,
    player_id       uuid REFERENCES players(id) ON DELETE SET NULL,
    played_at       timestamptz NOT NULL,           -- client-reported when scrobbled with time, else server now()
    duration_played_secs double precision,          -- NULL for legacy scrobbles
    completed       boolean NOT NULL DEFAULT true,  -- false = skip (< 30% played); signal for recs
    source          text NOT NULL DEFAULT 'manual'
                    CHECK (source IN ('manual','radio','playlist','album','queue','subsonic')),
    created_at      timestamptz NOT NULL
);
CREATE INDEX play_history_user_time ON play_history(user_id, played_at DESC);
CREATE INDEX play_history_track ON play_history(track_id);

-- Per-user, per-entity star/rating/playcount rollup (Subsonic 'annotation' concept).
CREATE TABLE annotations (
    user_id     uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    item_type   text NOT NULL CHECK (item_type IN ('track','album','artist')),
    item_id     uuid NOT NULL,                      -- polymorphic; integrity enforced in app layer
    starred_at  timestamptz,
    rating      integer CHECK (rating BETWEEN 1 AND 5),
    play_count  integer NOT NULL DEFAULT 0,         -- denormalized from play_history
    last_played_at timestamptz,
    PRIMARY KEY (user_id, item_type, item_id)
);
CREATE INDEX annotations_starred ON annotations(user_id, item_type, starred_at)
    WHERE starred_at IS NOT NULL;

-- A registered playback endpoint with its transcoding profile.
CREATE TABLE players (
    id                  uuid PRIMARY KEY,
    user_id             uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name                text NOT NULL,              -- 'Chrome on desk', 'Pixel 8', 'Living Room TV'
    kind                text NOT NULL CHECK (kind IN ('web','android','subsonic','cast','other')),
    client_string       text,                       -- Subsonic 'c' param or app UA, for auto-matching
    transcode_codec     text,                       -- NULL = raw/passthrough preferred
    transcode_max_kbps  integer,
    report_real_format  boolean NOT NULL DEFAULT true,
    last_seen_at        timestamptz,
    created_at          timestamptz NOT NULL,
    UNIQUE (user_id, name)
);

-- Per-user settings, including the recommendation window N.
CREATE TABLE user_settings (
    user_id             uuid PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    rec_window_n        integer NOT NULL DEFAULT 10 CHECK (rec_window_n BETWEEN 1 AND 100),
    rec_exclude_played_mins integer NOT NULL DEFAULT 240,  -- don't repeat a track played in last X minutes
    rec_diversity       double precision NOT NULL DEFAULT 0.3 CHECK (rec_diversity BETWEEN 0 AND 1),
    rec_explore         double precision NOT NULL DEFAULT 0.15 CHECK (rec_explore BETWEEN 0 AND 1),
    rec_same_artist_max integer NOT NULL DEFAULT 2,        -- max consecutive same-artist tracks in radio
    ui_theme            text NOT NULL DEFAULT 'system',
    default_library_id  uuid REFERENCES libraries(id) ON DELETE SET NULL,
    updated_at          timestamptz NOT NULL
);

-- Feature vectors for the recommendation engine (docs/07).
CREATE TABLE audio_features (
    track_id        uuid PRIMARY KEY REFERENCES tracks(id) ON DELETE CASCADE,
    version         integer NOT NULL,               -- feature schema version
    meta_vec        bytea NOT NULL,                 -- float32[], metadata-derived (see 07 §4.1)
    audio_vec       bytea,                          -- float32[], audio embedding; NULL until phase-2 job runs
    computed_at     timestamptz NOT NULL
);
```

## 6. Operational tables, search, derived data

```sql
CREATE TABLE scans (
    id              uuid PRIMARY KEY,
    library_id      uuid NOT NULL REFERENCES libraries(id) ON DELETE CASCADE,
    mode            text NOT NULL CHECK (mode IN ('incremental','full')),
    status          text NOT NULL CHECK (status IN ('running','done','failed','cancelled')),
    started_at      timestamptz NOT NULL,
    finished_at     timestamptz,
    objects_listed  integer NOT NULL DEFAULT 0,
    files_added     integer NOT NULL DEFAULT 0,
    files_changed   integer NOT NULL DEFAULT 0,
    files_removed   integer NOT NULL DEFAULT 0,
    error           text
);
CREATE INDEX scans_library ON scans(library_id, started_at DESC);

CREATE TABLE jobs (
    id          uuid PRIMARY KEY,
    kind        text NOT NULL,        -- 'scan','features','cache_gc','token_gc'
    key         text NOT NULL,        -- single-flight key, e.g. 'scan:<libraryId>'
    status      text NOT NULL CHECK (status IN ('queued','running','done','failed')),
    payload     text,                 -- JSON
    run_after   timestamptz NOT NULL,
    started_at  timestamptz,
    finished_at timestamptz,
    error       text,
    created_at  timestamptz NOT NULL
);
CREATE UNIQUE INDEX jobs_singleflight ON jobs(key) WHERE status IN ('queued','running');
```

### Search

- **PostgreSQL:** `pg_trgm` extension; GIN trigram indexes on
  `artists.search_text`, `albums.search_text`, `tracks.search_text`;
  ranked with `similarity()`.
- **SQLite:** FTS5 contentless tables `fts_artists`, `fts_albums`,
  `fts_tracks` kept in sync by the application on upsert/delete (no triggers,
  to keep write paths explicit and testable).

Both yield the same API behaviour: prefix + fuzzy match, ranked, paginated.

### Denormalized counters

`albums.track_count/duration_secs/size_bytes`, `artists.album_count/track_count`,
`libraries.track_count/total_bytes`, `playlists.item_count/duration_secs`, and
`annotations.play_count` are maintained transactionally by services (scanner,
playlist service, history service). A nightly `verify_counters` job recomputes
and corrects drift, logging discrepancies.

## 7. SQLite dialect deltas

| Postgres | SQLite |
|---|---|
| `uuid` | `TEXT` (canonical lowercase hyphenated) |
| `timestamptz` | `TEXT` (RFC 3339 UTC) |
| `bytea` | `BLOB` |
| `boolean` | `INTEGER` 0/1 (driver-mapped) |
| `double precision` | `REAL` |
| Partial / expression indexes | Supported as-is (SQLite ≥ 3.9) |
| `DEFERRABLE INITIALLY DEFERRED` | Not supported → playlist reorder uses two-phase position rewrite (set to `position + 1000000`, then final) |
| `pg_trgm` | FTS5 (see §6) |

SQLite MUST run with `journal_mode=WAL`, `busy_timeout=5000`,
`foreign_keys=ON`, `synchronous=NORMAL`.

## 8. Retention & privacy

- `play_history` is retained indefinitely by default; per-user purge endpoint
  (`DELETE /api/v1/me/history`) deletes rows and decrements annotation
  counters.
- `sessions` purged at expiry +7 days; `stream_tokens` purged at expiry +1 h
  (`token_gc` job).
- Deleting a user cascades all personal data (sessions, keys, playlists,
  history, annotations, settings, players, stream tokens) — the schema's
  cascade graph guarantees this without application sweeps.

## 9. Migration policy (for later milestones)

- Every schema change is a new numbered `golang-migrate` pair (`.up.sql` /
  `.down.sql`) in both dialect directories; CI applies all migrations against
  empty Postgres and SQLite databases and runs schema-equivalence assertions.
- Destructive migrations require a deprecation release in between (N supports
  old+new, N+1 drops old).