Repository Public
1-- Music SP — initial schema. The database holds metadata and references only; audio lives in private storage.23CREATE EXTENSION IF NOT EXISTS citext;4CREATE EXTENSION IF NOT EXISTS pg_trgm;56-- ── Accounts and authentication ──────────────────────────────────────────────────────────────────────7CREATE TABLE users (8 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),9 email citext NOT NULL UNIQUE,10 display_name text NOT NULL,11 role text NOT NULL DEFAULT 'listener' CHECK (role IN ('admin', 'listener')),12 is_owner boolean NOT NULL DEFAULT false,13 locale text NOT NULL DEFAULT 'fr' CHECK (locale IN ('fr', 'en')),14 accent text NOT NULL DEFAULT 'ember',15 disabled_at timestamptz,16 created_at timestamptz NOT NULL DEFAULT now()17);18CREATE UNIQUE INDEX users_single_owner ON users (is_owner) WHERE is_owner;1920-- Passkeys: public data only (credential id, public key, counter, metadata).21CREATE TABLE authenticators (22 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),23 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,24 credential_id text NOT NULL UNIQUE,25 public_key bytea NOT NULL,26 counter bigint NOT NULL DEFAULT 0,27 transports text[] NOT NULL DEFAULT '{}',28 device_type text,29 backed_up boolean NOT NULL DEFAULT false,30 name text NOT NULL DEFAULT 'Passkey',31 created_at timestamptz NOT NULL DEFAULT now(),32 last_used_at timestamptz33);34CREATE INDEX authenticators_user ON authenticators (user_id);3536-- One-time enrolment tokens: owner setup and recovery (shell only), listener/admin invitations (admin panel).37CREATE TABLE setup_tokens (38 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),39 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,40 token_hash text NOT NULL UNIQUE,41 purpose text NOT NULL CHECK (purpose IN ('setup', 'recovery', 'invite')),42 expires_at timestamptz NOT NULL,43 used_at timestamptz,44 created_at timestamptz NOT NULL DEFAULT now()45);4647CREATE TABLE recovery_codes (48 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),49 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,50 code_hash text NOT NULL UNIQUE,51 used_at timestamptz,52 created_at timestamptz NOT NULL DEFAULT now()53);5455CREATE TABLE webauthn_challenges (56 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),57 challenge text NOT NULL,58 kind text NOT NULL CHECK (kind IN ('registration', 'authentication')),59 user_id uuid REFERENCES users(id) ON DELETE CASCADE,60 expires_at timestamptz NOT NULL61);6263CREATE TABLE sessions (64 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),65 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,66 token_hash text NOT NULL UNIQUE,67 csrf_token text NOT NULL,68 method text NOT NULL,69 user_agent text NOT NULL DEFAULT '',70 ip text,71 created_at timestamptz NOT NULL DEFAULT now(),72 last_seen_at timestamptz NOT NULL DEFAULT now(),73 expires_at timestamptz NOT NULL,74 revoked_at timestamptz75);76CREATE INDEX sessions_user ON sessions (user_id) WHERE revoked_at IS NULL;7778CREATE TABLE rate_limits (79 key text NOT NULL,80 window_start timestamptz NOT NULL,81 count integer NOT NULL DEFAULT 0,82 PRIMARY KEY (key, window_start)83);8485CREATE TABLE audit_events (86 id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,87 at timestamptz NOT NULL DEFAULT now(),88 user_id uuid REFERENCES users(id) ON DELETE SET NULL,89 actor text NOT NULL,90 action text NOT NULL,91 entity text NOT NULL DEFAULT '',92 entity_id text NOT NULL DEFAULT '',93 meta jsonb NOT NULL DEFAULT '{}',94 ip text95);96CREATE INDEX audit_events_at ON audit_events (at DESC);9798CREATE TABLE worker_heartbeat (99 name text PRIMARY KEY,100 beat_at timestamptz NOT NULL DEFAULT now(),101 info jsonb NOT NULL DEFAULT '{}'102);103104-- ── Library ──────────────────────────────────────────────────────────────────────────────────────────105-- *_norm columns hold the lower-cased, accent-stripped form used for search and duplicate constraints.106CREATE TABLE artists (107 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),108 name text NOT NULL,109 name_norm text NOT NULL UNIQUE,110 created_at timestamptz NOT NULL DEFAULT now()111);112CREATE INDEX artists_trgm ON artists USING gin (name_norm gin_trgm_ops);113114CREATE TABLE genres (115 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),116 name text NOT NULL,117 name_norm text NOT NULL UNIQUE118);119120-- Cover art, deduplicated by content hash. Files: art/<sha256>/orig.<ext> and art/<sha256>/<size>.jpg121CREATE TABLE artworks (122 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),123 sha256 text NOT NULL UNIQUE,124 mime text NOT NULL,125 width integer NOT NULL,126 height integer NOT NULL,127 created_at timestamptz NOT NULL DEFAULT now()128);129130CREATE TABLE albums (131 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),132 title text NOT NULL,133 title_norm text NOT NULL,134 artist_id uuid NOT NULL REFERENCES artists(id) ON DELETE RESTRICT,135 year integer CHECK (year BETWEEN 1000 AND 2999),136 genre_id uuid REFERENCES genres(id) ON DELETE SET NULL,137 artwork_id uuid REFERENCES artworks(id) ON DELETE SET NULL,138 created_at timestamptz NOT NULL DEFAULT now(),139 UNIQUE (artist_id, title_norm)140);141CREATE INDEX albums_trgm ON albums USING gin (title_norm gin_trgm_ops);142CREATE INDEX albums_created ON albums (created_at DESC);143144CREATE TABLE tracks (145 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),146 title text NOT NULL,147 title_norm text NOT NULL,148 artist_id uuid NOT NULL REFERENCES artists(id) ON DELETE RESTRICT,149 album_id uuid NOT NULL REFERENCES albums(id) ON DELETE RESTRICT,150 track_no integer,151 disc_no integer NOT NULL DEFAULT 1,152 year integer CHECK (year BETWEEN 1000 AND 2999),153 genre_id uuid REFERENCES genres(id) ON DELETE SET NULL,154 duration_ms integer NOT NULL DEFAULT 0,155 codec text NOT NULL,156 container text NOT NULL,157 sample_rate integer,158 bit_depth integer,159 channels integer,160 bitrate integer,161 -- 'yes': every supported browser decodes the original; 'some': Safari only (ALAC, AIFF); 'no': DSD and the like162 web_playable text NOT NULL CHECK (web_playable IN ('yes', 'some', 'no')),163 -- compatibility rendition (AAC): none | queued | working | ready | error | disabled164 compat_state text NOT NULL DEFAULT 'none' CHECK (compat_state IN ('none', 'queued', 'working', 'ready', 'error', 'disabled')),165 compat_error text,166 analysis_requested boolean NOT NULL DEFAULT false, -- admin asked for a fresh analysis (worker picks it up)167 created_at timestamptz NOT NULL DEFAULT now(),168 updated_at timestamptz NOT NULL DEFAULT now()169);170CREATE INDEX tracks_album ON tracks (album_id, disc_no, track_no);171CREATE INDEX tracks_artist ON tracks (artist_id);172CREATE INDEX tracks_trgm ON tracks USING gin (title_norm gin_trgm_ops);173CREATE INDEX tracks_compat ON tracks (compat_state) WHERE compat_state = 'queued';174CREATE INDEX tracks_analysis ON tracks (id) WHERE analysis_requested;175176-- Every stored file: the untouched original, derived renditions, cover originals and thumbnails.177-- storage_key is an opaque key resolved by the storage driver; it never comes from a client.178CREATE TABLE media_assets (179 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),180 kind text NOT NULL CHECK (kind IN ('original', 'rendition', 'artwork', 'thumbnail')),181 storage_key text NOT NULL UNIQUE,182 sha256 text,183 size_bytes bigint NOT NULL,184 mime text NOT NULL,185 track_id uuid REFERENCES tracks(id) ON DELETE CASCADE,186 artwork_id uuid REFERENCES artworks(id) ON DELETE CASCADE,187 created_at timestamptz NOT NULL DEFAULT now(),188 CHECK ((track_id IS NULL) <> (artwork_id IS NULL))189);190-- One original per track, and the same audio file cannot be stored twice.191CREATE UNIQUE INDEX media_assets_original_track ON media_assets (track_id) WHERE kind = 'original';192CREATE UNIQUE INDEX media_assets_original_sha ON media_assets (sha256) WHERE kind = 'original';193CREATE UNIQUE INDEX media_assets_rendition_track ON media_assets (track_id) WHERE kind = 'rendition';194CREATE INDEX media_assets_artwork ON media_assets (artwork_id);195196-- ── Imports (also the processing queue of the worker) ────────────────────────────────────────────────197CREATE TABLE uploads (198 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),199 user_id uuid REFERENCES users(id) ON DELETE SET NULL,200 parent_id uuid REFERENCES uploads(id) ON DELETE CASCADE, -- entry extracted from an archive201 filename text NOT NULL, -- display only, sanitised202 size_bytes bigint NOT NULL,203 received_bytes bigint NOT NULL DEFAULT 0,204 -- uploading → received → validating → extracting → ready | duplicate | error | cancelled205 status text NOT NULL DEFAULT 'uploading'206 CHECK (status IN ('uploading', 'received', 'validating', 'extracting', 'ready', 'duplicate', 'error', 'cancelled')),207 error_code text,208 error_detail text,209 sha256 text,210 kind text, -- detected from content: flac, mp3, zip…211 track_id uuid REFERENCES tracks(id) ON DELETE SET NULL,212 duplicate_of uuid REFERENCES tracks(id) ON DELETE SET NULL,213 replace boolean NOT NULL DEFAULT false, -- admin confirmed replacing the duplicate214 attempts integer NOT NULL DEFAULT 0,215 created_at timestamptz NOT NULL DEFAULT now(),216 updated_at timestamptz NOT NULL DEFAULT now()217);218CREATE INDEX uploads_queue ON uploads (created_at) WHERE status = 'received';219CREATE INDEX uploads_recent ON uploads (created_at DESC);220221-- ── Per-user data ────────────────────────────────────────────────────────────────────────────────────222CREATE TABLE playlists (223 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),224 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,225 name text NOT NULL,226 created_at timestamptz NOT NULL DEFAULT now(),227 updated_at timestamptz NOT NULL DEFAULT now()228);229CREATE INDEX playlists_user ON playlists (user_id, updated_at DESC);230231CREATE TABLE playlist_tracks (232 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),233 playlist_id uuid NOT NULL REFERENCES playlists(id) ON DELETE CASCADE,234 track_id uuid NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,235 position integer NOT NULL,236 added_at timestamptz NOT NULL DEFAULT now()237);238CREATE INDEX playlist_tracks_order ON playlist_tracks (playlist_id, position);239240CREATE TABLE favorites (241 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,242 track_id uuid NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,243 created_at timestamptz NOT NULL DEFAULT now(),244 PRIMARY KEY (user_id, track_id)245);246247CREATE TABLE favorite_albums (248 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,249 album_id uuid NOT NULL REFERENCES albums(id) ON DELETE CASCADE,250 created_at timestamptz NOT NULL DEFAULT now(),251 PRIMARY KEY (user_id, album_id)252);253254-- Resume: the queue, the position in it and the player preferences of each account.255CREATE TABLE playback_state (256 user_id uuid PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,257 queue uuid[] NOT NULL DEFAULT '{}',258 queue_index integer NOT NULL DEFAULT 0,259 position_ms integer NOT NULL DEFAULT 0,260 shuffle boolean NOT NULL DEFAULT false,261 repeat text NOT NULL DEFAULT 'off' CHECK (repeat IN ('off', 'all', 'one')),262 volume real NOT NULL DEFAULT 1 CHECK (volume BETWEEN 0 AND 1),263 updated_at timestamptz NOT NULL DEFAULT now()264);265