Repository Public
1-- Native clients (SP Music for macOS): incremental library sync, device linking, listening history.2-- Nothing here changes what the web player reads or writes.34-- ── Incremental sync ─────────────────────────────────────────────────────────────────────────────────5-- Every library row carries a revision taken from one sequence. A client remembers the highest revision it6-- has applied and asks for what is above it; deletions are kept as tombstones.7--8-- Writers are serialised by a transaction-level advisory lock taken BEFORE the revision is drawn, so revisions9-- commit in increasing order: a reader's snapshot can never contain revision N+1 while N is still uncommitted.10CREATE SEQUENCE library_rev;1112ALTER TABLE artists ADD COLUMN rev bigint NOT NULL DEFAULT nextval('library_rev');13ALTER TABLE albums ADD COLUMN rev bigint NOT NULL DEFAULT nextval('library_rev');14ALTER TABLE tracks ADD COLUMN rev bigint NOT NULL DEFAULT nextval('library_rev');15CREATE INDEX artists_rev ON artists (rev);16CREATE INDEX albums_rev ON albums (rev);17CREATE INDEX tracks_rev ON tracks (rev);1819CREATE TABLE library_tombstones (20 kind text NOT NULL CHECK (kind IN ('artist', 'album', 'track')),21 id uuid NOT NULL,22 rev bigint NOT NULL,23 deleted_at timestamptz NOT NULL DEFAULT now(),24 PRIMARY KEY (kind, id)25);26CREATE INDEX library_tombstones_rev ON library_tombstones (rev);2728CREATE FUNCTION library_next_rev() RETURNS bigint LANGUAGE plpgsql AS $$29BEGIN30 PERFORM pg_advisory_xact_lock(7741001);31 RETURN nextval('library_rev');32END $$;3334CREATE FUNCTION library_bump() RETURNS trigger LANGUAGE plpgsql AS $$35BEGIN36 NEW.rev := library_next_rev();37 RETURN NEW;38END $$;3940CREATE FUNCTION library_tombstone() RETURNS trigger LANGUAGE plpgsql AS $$41BEGIN42 INSERT INTO library_tombstones (kind, id, rev) VALUES (TG_ARGV[0], OLD.id, library_next_rev())43 ON CONFLICT (kind, id) DO UPDATE SET rev = EXCLUDED.rev, deleted_at = now();44 RETURN OLD;45END $$;4647-- A change to a track's files or lyrics is a change to the track as a client sees it.48CREATE FUNCTION library_touch_track() RETURNS trigger LANGUAGE plpgsql AS $$49DECLARE tid uuid;50BEGIN51 IF TG_OP = 'DELETE' THEN tid := OLD.track_id; ELSE tid := NEW.track_id; END IF;52 IF tid IS NOT NULL THEN UPDATE tracks SET rev = rev WHERE id = tid; END IF; -- library_bump assigns the new revision53 RETURN NULL;54END $$;5556CREATE TRIGGER artists_rev BEFORE INSERT OR UPDATE ON artists FOR EACH ROW EXECUTE FUNCTION library_bump();57CREATE TRIGGER albums_rev BEFORE INSERT OR UPDATE ON albums FOR EACH ROW EXECUTE FUNCTION library_bump();58CREATE TRIGGER tracks_rev BEFORE INSERT OR UPDATE ON tracks FOR EACH ROW EXECUTE FUNCTION library_bump();59CREATE TRIGGER artists_gone AFTER DELETE ON artists FOR EACH ROW EXECUTE FUNCTION library_tombstone('artist');60CREATE TRIGGER albums_gone AFTER DELETE ON albums FOR EACH ROW EXECUTE FUNCTION library_tombstone('album');61CREATE TRIGGER tracks_gone AFTER DELETE ON tracks FOR EACH ROW EXECUTE FUNCTION library_tombstone('track');62CREATE TRIGGER media_assets_touch AFTER INSERT OR UPDATE OR DELETE ON media_assets FOR EACH ROW EXECUTE FUNCTION library_touch_track();63CREATE TRIGGER lyrics_touch AFTER INSERT OR UPDATE OR DELETE ON lyrics FOR EACH ROW EXECUTE FUNCTION library_touch_track();6465-- ── Device linking ───────────────────────────────────────────────────────────────────────────────────66-- A native app shows a short code; a signed-in browser approves it; the app then receives its own session.67-- Only hashes of the device secret are stored. Rows are short-lived (10 minutes) and single-use.68CREATE TABLE device_links (69 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),70 device_hash text NOT NULL UNIQUE,71 user_code text NOT NULL UNIQUE,72 device_name text NOT NULL DEFAULT '',73 ip text,74 created_at timestamptz NOT NULL DEFAULT now(),75 expires_at timestamptz NOT NULL,76 approved_by uuid REFERENCES users(id) ON DELETE CASCADE,77 decided_at timestamptz,78 denied boolean NOT NULL DEFAULT false,79 consumed_at timestamptz80);8182-- ── Listening history ────────────────────────────────────────────────────────────────────────────────83-- The id is generated by the client, so an event sent twice (retry after a lost answer) is stored once.84CREATE TABLE play_events (85 id uuid PRIMARY KEY,86 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,87 track_id uuid NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,88 played_at timestamptz NOT NULL,89 listened_ms integer NOT NULL DEFAULT 0 CHECK (listened_ms >= 0),90 completed boolean NOT NULL DEFAULT false,91 source text NOT NULL DEFAULT 'stream' CHECK (source IN ('stream', 'local')),92 client text NOT NULL DEFAULT '',93 created_at timestamptz NOT NULL DEFAULT now()94);95CREATE INDEX play_events_user ON play_events (user_id, played_at DESC);96