SPB Git

Repository Public

001_init.sql 12.0 KB 265 lines sql

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
Files main
Menu

Theme