pipu-music/docs/data-model.md

Модель данных (PostgreSQL 17)

Соглашения:

• первичные ключи — uuid (UUIDv7, генерируются в приложении — сортируемы по времени);
• время — timestamptz, UTC;
• мягкое удаление только там, где явно указано (deleted_at);
• миграции — services/api/migrations, goose, только вперёд (down-миграции для dev).
• Расширения: pgvector, pg_trgm, citext.

Пользователи и доступ

CREATE TYPE user_role AS ENUM ('admin', 'moderator', 'user');

CREATE TABLE users (
  id                      uuid PRIMARY KEY,
  username                citext NOT NULL UNIQUE,        -- 3..32, [a-z0-9._-]
  display_name            text,
  password_hash           text NOT NULL,                 -- argon2id PHC string
  role                    user_role NOT NULL DEFAULT 'user',
  locale                  text NOT NULL DEFAULT 'ru',    -- BCP 47: ru, en, ...
  ui_prefs                jsonb NOT NULL DEFAULT '{}',   -- {theme: dark|light|system, wave_variant: orb|liquid,
                                                         --  web_player: bar|island, mobile_player: cover|wave,
                                                         --  ambient: bool, crossfade_s: 0..6, normalize: bool}
  must_change_password    boolean NOT NULL DEFAULT false,
  must_change_credentials boolean NOT NULL DEFAULT false, -- только для bootstrap-админа: логин + пароль
  is_blocked              boolean NOT NULL DEFAULT false,
  token_version           int NOT NULL DEFAULT 0,        -- инкремент = мгновенный отзыв access-токенов
  failed_login_count      int NOT NULL DEFAULT 0,
  locked_until            timestamptz,
  created_by              uuid REFERENCES users(id),
  created_at              timestamptz NOT NULL DEFAULT now(),
  updated_at              timestamptz NOT NULL DEFAULT now(),
  last_login_at           timestamptz
);

CREATE TABLE refresh_tokens (
  id           uuid PRIMARY KEY,
  user_id      uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  family_id    uuid NOT NULL,                -- цепочка ротаций одного входа
  family_started_at timestamptz NOT NULL,    -- время входа: абсолютный срок семьи (180 дней)
  token_hash   bytea NOT NULL UNIQUE,        -- SHA-256 от токена
  device_name  text,                         -- "Chrome / Windows", "Pixel 8"
  client_type  text NOT NULL,                -- 'web' | 'android'
  created_at   timestamptz NOT NULL DEFAULT now(),
  expires_at   timestamptz NOT NULL,
  used_at      timestamptz,                  -- ротирован
  revoked_at   timestamptz
);
CREATE INDEX ON refresh_tokens (user_id) WHERE revoked_at IS NULL;

CREATE TABLE audit_log (
  id          uuid PRIMARY KEY,
  actor_id    uuid REFERENCES users(id),
  action      text NOT NULL,                 -- 'user.create', 'user.role_change', 'source.create', ...
  target_type text,
  target_id   uuid,
  payload     jsonb,
  created_at  timestamptz NOT NULL DEFAULT now()
);

Хранилища и файлы

CREATE TYPE source_type   AS ENUM ('nfs', 'local');
CREATE TYPE source_status AS ENUM ('pending', 'mounted', 'error', 'disabled');
CREATE TYPE tag_priority  AS ENUM ('tags_first', 'path_first');

CREATE TABLE library_sources (
  id             uuid PRIMARY KEY,
  name           text NOT NULL,
  type           source_type NOT NULL,
  nfs_server     text,                 -- host / IP (для type=nfs)
  nfs_export     text,                 -- /export/music
  nfs_options    text NOT NULL DEFAULT 'ro,nfsvers=4.1,soft,timeo=150,retrans=3',
  local_path     text,                 -- для type=local (внутри /library-host)
  tag_priority   tag_priority NOT NULL DEFAULT 'tags_first',
  accepts_uploads boolean NOT NULL DEFAULT false, -- монтируется на запись, принимает одобренные загрузки
  write_template text,                  -- шаблон раскладки: '{albumartist}/{year} - {album}/{track:02} - {title}.{ext}'
  scan_interval  interval,             -- NULL = только вручную
  status         source_status NOT NULL DEFAULT 'pending',
  status_message text,
  last_scan_at   timestamptz,
  created_at     timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE path_patterns (           -- упорядоченный список регэкспов источника
  id         uuid PRIMARY KEY,
  source_id  uuid NOT NULL REFERENCES library_sources(id) ON DELETE CASCADE,
  position   int NOT NULL,             -- порядок применения, первый совпавший побеждает
  regex      text NOT NULL,            -- RE2 с именованными группами
  is_enabled boolean NOT NULL DEFAULT true,
  UNIQUE (source_id, position)
);

CREATE TABLE media_files (
  id            uuid PRIMARY KEY,
  source_id     uuid NOT NULL REFERENCES library_sources(id) ON DELETE CASCADE,
  rel_path      text NOT NULL,          -- путь относительно корня источника
  size_bytes    bigint NOT NULL,
  mtime         timestamptz NOT NULL,
  content_hash  bytea,                  -- xxhash первых/последних 1 МБ + размер: быстро и устойчиво к переименованию
  sha256        bytea,                  -- полный хэш файла (для проверки дублей при загрузке; считается фоном)
  upload_file_id uuid,                  -- если файл попал через очередь на добавление
  format        text NOT NULL,          -- flac, mp3, m4a, ogg, opus, wav
  codec         text, bitrate int, sample_rate int, bit_depth int, channels int,
  duration_ms   int NOT NULL,
  track_id      uuid,                   -- FK ставится после создания tracks
  missing_since timestamptz,            -- файл пропал при скане
  scan_error    text,
  fingerprinted_at timestamptz,         -- Chromaprint посчитан (или fpcalc не смог); NULL — в очереди
  UNIQUE (source_id, rel_path)
);
CREATE INDEX ON media_files (content_hash);
CREATE INDEX ON media_files (sha256);

CREATE TABLE track_fingerprints (       -- Chromaprint, для поиска дублей при загрузке
  file_id      uuid PRIMARY KEY REFERENCES media_files(id) ON DELETE CASCADE,  -- трек — через media_files
  duration_ms  int NOT NULL,
  fingerprint  int[] NOT NULL,          -- raw-отпечаток fpcalc
  fp_prefix    bigint NOT NULL          -- первые 64 бита — грубый индекс для отбора кандидатов
);
CREATE INDEX ON track_fingerprints (fp_prefix);
CREATE INDEX ON track_fingerprints (duration_ms);

Очередь на добавление (загрузки)

CREATE TYPE upload_status AS ENUM (
  'uploading', 'processing', 'pending_review', 'approved', 'importing', 'imported',
  'import_failed', 'duplicate', 'invalid', 'rejected', 'withdrawn', 'expired');

CREATE TABLE upload_batches (           -- одна «пачка» загрузки (обычно альбом / папка)
  id          uuid PRIMARY KEY,
  author_id   uuid NOT NULL REFERENCES users(id),
  comment     text,                     -- комментарий автора для админа
  cover_path  text,                     -- обложка пачки (JPEG/PNG/WebP), вшивается при импорте
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE upload_files (
  id               uuid PRIMARY KEY,
  batch_id         uuid NOT NULL REFERENCES upload_batches(id) ON DELETE CASCADE,
  author_id        uuid NOT NULL REFERENCES users(id),
  status           upload_status NOT NULL,
  original_name    text NOT NULL,
  client_rel_path  text,                -- относительный путь из перетащенной папки
  size_bytes       bigint NOT NULL,     -- заявленный, затем фактический
  sha256           bytea,
  staging_path     text,
  format           text, codec text, bitrate int, sample_rate int, bit_depth int,
  duration_ms      int,
  tags             jsonb,               -- исходные теги файла
  metadata         jsonb,               -- итоговые поля {artist, albumartist, album, year, disc, track, title, genre}
  metadata_edited  boolean NOT NULL DEFAULT false,
  matched_artist_id uuid REFERENCES artists(id),
  matched_album_id  uuid REFERENCES albums(id),
  cover_upload_id  uuid,                -- отдельная картинка обложки из той же пачки
  duplicate_of_track_id uuid REFERENCES tracks(id),
  duplicate_level  text,                -- sha256 | metadata | fingerprint
  possible_duplicate boolean NOT NULL DEFAULT false,
  quality_upgrade boolean NOT NULL DEFAULT false, -- тот же трек, но лучше по качеству
  target_source_id uuid REFERENCES library_sources(id),
  target_rel_path  text,
  status_reason    text,                -- код причины: uploads.duplicate, uploads.invalid_audio, текст отклонения
  reviewed_by      uuid REFERENCES users(id),
  reviewed_at      timestamptz,
  imported_file_id uuid REFERENCES media_files(id),
  created_at       timestamptz NOT NULL DEFAULT now(),
  updated_at       timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON upload_files (status);
CREATE INDEX ON upload_files (author_id, created_at DESC);
CREATE INDEX ON upload_files (sha256);

-- Счётчик квоты (одна строка). Обновляется в той же транзакции, что и смена статуса.
-- При старте api и каждые 10 минут сверяется с SUM(size_bytes) по «занимающим» статусам.
CREATE TABLE upload_quota (
  id          boolean PRIMARY KEY DEFAULT true CHECK (id),
  used_bytes  bigint NOT NULL DEFAULT 0
);

-- Когда автор последний раз открыл «Мои предложения» (бейдж новых решений).
CREATE TABLE upload_visits (
  user_id  uuid PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  seen_at  timestamptz NOT NULL DEFAULT now()
);

Каталог

CREATE TABLE artists (
  id           uuid PRIMARY KEY,
  name         text NOT NULL,
  sort_name    text NOT NULL,
  name_norm    text NOT NULL UNIQUE,     -- нормализованное имя: lower, ё→е, без пунктуации
  image_path   text,                     -- кэш изображения
  created_at   timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE albums (
  id               uuid PRIMARY KEY,
  title            text NOT NULL,
  title_norm       text NOT NULL,
  album_artist_id  uuid NOT NULL REFERENCES artists(id),  -- без тегов — исполнитель трека или «Unknown artist»
  year             int,
  cover_path       text,                 -- путь в кэше обложек (/cache/covers/<hash>.webp)
  cover_colors     text[],               -- палитра обложки (3 цвета: доминирующий, второй, акцент) — для ambient-фона,
                                         -- цветов кнопки волны и плейсхолдера обложки
  UNIQUE (album_artist_id, title_norm)
);

CREATE TABLE tracks (
  id            uuid PRIMARY KEY,
  title         text NOT NULL,
  title_norm    text NOT NULL,                -- ключ поиска дублей: (album_id, title_norm, duration ±2 с)
  album_id      uuid NOT NULL REFERENCES albums(id),
  disc_no       int, track_no int,
  year          int,
  duration_ms   int NOT NULL,
  primary_file_id uuid REFERENCES media_files(id),  -- лучший по качеству файл, если дубли
  explicit      boolean,
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now()
);
ALTER TABLE media_files ADD FOREIGN KEY (track_id) REFERENCES tracks(id) ON DELETE SET NULL;

CREATE TABLE track_artists (
  track_id  uuid REFERENCES tracks(id) ON DELETE CASCADE,
  artist_id uuid REFERENCES artists(id) ON DELETE CASCADE,
  role      text NOT NULL DEFAULT 'main',   -- main | feat
  position  int NOT NULL,
  PRIMARY KEY (track_id, artist_id, role)
);

CREATE TABLE genres (
  id        serial PRIMARY KEY,
  name      text NOT NULL,
  name_norm text NOT NULL UNIQUE,
  parent_id int REFERENCES genres(id)       -- иерархия: "metal" → "death metal"
);
CREATE TABLE track_genres (
  track_id uuid REFERENCES tracks(id) ON DELETE CASCADE,
  genre_id int  REFERENCES genres(id),
  source   text NOT NULL,                   -- 'tag' | 'analysis'
  weight   real NOT NULL DEFAULT 1,         -- для analysis — вероятность модели
  PRIMARY KEY (track_id, genre_id, source)
);

Аудиопризнаки (заполняет analyzer)

CREATE TABLE track_features (
  track_id       uuid PRIMARY KEY REFERENCES tracks(id) ON DELETE CASCADE,
  analyzer_ver   text NOT NULL,             -- версия пайплайна, для пересчёта
  bpm            real, bpm_confidence real,
  key            text, scale text,          -- 'A', 'minor'
  loudness_lufs  real,
  energy         real,                      -- 0..1
  danceability   real,                      -- 0..1
  valence        real,                      -- 0..1 (грустно → весело)
  arousal        real,                      -- 0..1 (спокойно → возбуждённо)
  acousticness   real, instrumentalness real,
  mood_happy real, mood_sad real, mood_relaxed real, mood_aggressive real, mood_party real,
  intro_ms       int,                       -- для мягкого старта / кроссфейда
  analyzed_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE track_embeddings (
  track_id  uuid PRIMARY KEY REFERENCES tracks(id) ON DELETE CASCADE,
  audio     vector(256),                    -- аудио-эмбеддинг (Discogs-EffNet → PCA 256)
  cf        vector(64),                     -- item-фактор ALS (обновляется ночью)
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON track_embeddings USING hnsw (audio vector_cosine_ops);
CREATE INDEX ON track_embeddings USING hnsw (cf vector_cosine_ops);

CREATE TABLE analysis_jobs (
  track_id    uuid PRIMARY KEY REFERENCES tracks(id) ON DELETE CASCADE,
  status      text NOT NULL DEFAULT 'queued', -- queued | running | done | failed
  attempts    int NOT NULL DEFAULT 0,
  error       text,
  locked_at   timestamptz,
  updated_at  timestamptz NOT NULL DEFAULT now()
);

Пользовательские данные

CREATE TYPE reaction AS ENUM ('like', 'dislike');

CREATE TABLE track_reactions (             -- Коллекция → Лайки / Дизлайки
  user_id    uuid REFERENCES users(id) ON DELETE CASCADE,
  track_id   uuid REFERENCES tracks(id) ON DELETE CASCADE,
  reaction   reaction NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, track_id)
);
CREATE INDEX ON track_reactions (user_id, reaction, created_at DESC);

CREATE TABLE artist_reactions (            -- «не рекомендовать исполнителя» и т.п.
  user_id uuid REFERENCES users(id) ON DELETE CASCADE,
  artist_id uuid REFERENCES artists(id) ON DELETE CASCADE,
  reaction reaction NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, artist_id)
);

-- Сырые события — источник истины для истории и обучения. Партиционирование по месяцам.
CREATE TABLE play_events (
  id            uuid NOT NULL,             -- event_id от клиента (идемпотентность)
  user_id       uuid NOT NULL,
  track_id      uuid NOT NULL,
  type          text NOT NULL,             -- play_start | play_end | skip | seek | like | unlike | dislike | undislike
  occurred_at   timestamptz NOT NULL,      -- время на клиенте (важно для офлайна)
  received_at   timestamptz NOT NULL DEFAULT now(),
  position_ms   int,                       -- где произошло
  played_ms     int,                       -- реально прослушано в этой попытке (для play_end/skip)
  context_type  text,                      -- wave | album | artist | search | collection | history | queue
  context_id    text,                      -- id альбома / wave_session и т.п.
  wave_session_id uuid,
  client_type   text NOT NULL,             -- web | android
  offline       boolean NOT NULL DEFAULT false,
  PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);
CREATE INDEX ON play_events (user_id, occurred_at DESC);

-- Агрегаты для быстрого доступа (обновляются триггерно в api и пересчитываются ночью)
CREATE TABLE user_track_stats (
  user_id        uuid, track_id uuid,
  play_count     int NOT NULL DEFAULT 0,   -- засчитанные прослушивания (≥ 50% или ≥ 4 мин)
  complete_count int NOT NULL DEFAULT 0,
  skip_count     int NOT NULL DEFAULT 0,
  early_skip_count int NOT NULL DEFAULT 0, -- скип < 30 с
  total_played_ms bigint NOT NULL DEFAULT 0,
  last_played_at timestamptz,
  implicit_score real NOT NULL DEFAULT 0,  -- см. recommendations.md
  PRIMARY KEY (user_id, track_id)
);

CREATE TABLE playback_state (              -- «запомнить трек и момент»
  user_id      uuid PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  track_id     uuid REFERENCES tracks(id) ON DELETE SET NULL,
  position_ms  int NOT NULL DEFAULT 0,
  queue        jsonb NOT NULL DEFAULT '[]',   -- [{track_id, source}] до 500 элементов
  queue_index  int NOT NULL DEFAULT 0,
  context      jsonb,                         -- {type:'wave', wave_session_id, activity, mood}
  shuffle      boolean NOT NULL DEFAULT false,
  repeat_mode  text NOT NULL DEFAULT 'off',   -- off | one | all
  volume       real NOT NULL DEFAULT 1,
  device_id    text,                          -- кто записал последним
  updated_at   timestamptz NOT NULL DEFAULT now()
);

Рекомендации

CREATE TABLE wave_sessions (
  id           uuid PRIMARY KEY,
  user_id      uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  activity     text,                       -- running | waking_up | focus | workout | commute | chill | sleep
  mood         text,                       -- energetic | happy | calm | sad
  seed_type    text,                       -- null | artist | track | album
  seed_id      uuid,
  started_at   timestamptz NOT NULL DEFAULT now(),
  ended_at     timestamptz,
  tracks_served int NOT NULL DEFAULT 0
);

CREATE TABLE wave_impressions (            -- что выдали и откуда (для обучения ранжировщика и бандита)
  wave_session_id uuid REFERENCES wave_sessions(id) ON DELETE CASCADE,
  position        int NOT NULL,
  track_id        uuid NOT NULL,
  candidate_source text NOT NULL,          -- audio_sim | cf | artist | genre | fresh | rediscover | popular
  score           real,
  model_ver       text,
  served_at       timestamptz NOT NULL DEFAULT now(),
  outcome         text,                    -- complete | skip_early | skip | like | dislike (заполняется по событиям)
  PRIMARY KEY (wave_session_id, position)
);

CREATE TABLE user_profiles_rec (           -- векторы вкуса и параметры бандита
  user_id      uuid PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  taste_audio  vector(256),                -- взвешенное среднее эмбеддингов положительных треков
  taste_cf     vector(64),                 -- user-фактор ALS
  genre_prefs  jsonb NOT NULL DEFAULT '{}',-- {genre_id: weight}
  bandit       jsonb NOT NULL DEFAULT '{}',-- {source: {alpha, beta}}
  exploration  real NOT NULL DEFAULT 0.15,
  updated_at   timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE model_versions (
  id          text PRIMARY KEY,            -- 'ranker-2026-10-19T03:00'
  kind        text NOT NULL,               -- als | ranker
  metrics     jsonb,                       -- ndcg@10, recall@50, ...
  is_active   boolean NOT NULL DEFAULT false,
  created_at  timestamptz NOT NULL DEFAULT now()
);

Прочее

CREATE TABLE app_settings (key text PRIMARY KEY, value jsonb NOT NULL);
-- 'bootstrap.completed', 'transcode.cache_limit_gb', 'update.gitea',
-- 'uploads.queue_limit_bytes' (10 ГБ), 'uploads.per_user_limit_bytes' (null), 'uploads.max_file_bytes' (2 ГБ),
-- 'uploads.default_source_id', 'uploads.allow_quality_upgrade' (false), ...

Очистка и хранение

• play_events — хранить бессрочно (объём маленький: 100 польз. × ~300 событий/день ≈ 11 млн/год, ~2 ГБ), партиции старше 2 лет можно архивировать.
• wave_impressions — 180 дней.
• refresh_tokens — удалять истёкшие через 7 дней после expires_at.