schema_postgres.sql

-- Basic catalog schema (Postgres)
-- Stores known songs and their fingerprint hashes.

CREATE TABLE IF NOT EXISTS songs (
  song_id TEXT PRIMARY KEY,
  title TEXT,
  artist TEXT,
  file_path TEXT NOT NULL,
  duration_sec DOUBLE PRECISION,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS fingerprints (
  -- Hash values are unsigned 32-bit in code (up to 2^32-1) so int4 can overflow.
  hash BIGINT NOT NULL,
  time_sec DOUBLE PRECISION NOT NULL,
  song_id TEXT NOT NULL REFERENCES songs(song_id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_fingerprints_hash ON fingerprints(hash);
CREATE INDEX IF NOT EXISTS idx_fingerprints_song_id ON fingerprints(song_id);

CREATE TABLE IF NOT EXISTS users (
  user_id BIGSERIAL PRIMARY KEY,
  username TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,
  role TEXT NOT NULL CHECK (role IN ('admin', 'user')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);