1-- Who played each performance, and when to the millisecond. Owner:
2-- src/listening-store.ts writes both (for src/listening.ts, which takes the
3-- player from the session cookie, never from the request's body);
4-- src/data.ts and src/activity.ts read them.
5--
6-- What is identifying here, and why (migrations/CLAUDE.md): a performance
7-- names the account that played it, when one was signed in, so a listener
8-- can find their own takes ("recently played", their profile) and everyone
9-- can see who played what (the activity feed, the data tab). Like every row
10-- it is public. A take outlives its player's account: deleting the account
11-- leaves the take, anonymous (ON DELETE SET NULL). Signed out, a take has
12-- no player, as every take did before this migration.
13--
14-- A performance of a listener's song is recorded too, its `song` being
15-- "listener:<id>" (the id convention the MCPs and the page use); 0003's
16-- CHECK on `song` (3 to 129 characters) already admits it.
17
18ALTER TABLE performances ADD COLUMN user_id TEXT REFERENCES users (id) ON DELETE SET NULL
19  CHECK (user_id IS NULL OR (length(user_id) = 22 AND user_id NOT GLOB '*[^A-Za-z0-9_-]*'));
20
21-- When the take was stored, milliseconds since the epoch, UTC. Takes from
22-- before this migration kept only their day: they are dated noon UTC of
23-- it, which orders them correctly by day and says nothing false about the
24-- hour to anyone who reads `day` beside it. SQLite's ALTER TABLE cannot add
25-- a column that is NOT NULL without a default, so the column allows NULL,
26-- and none stays: the backfill below dates every take stored before, and
27-- the trigger every take the Worker live during the deploy stores without
28-- it (the deploy migrates before it publishes, worker/migrations/CLAUDE.md).
29ALTER TABLE performances ADD COLUMN recorded_at INTEGER CHECK (recorded_at IS NULL OR recorded_at > 0);
30UPDATE performances SET recorded_at = CAST(strftime('%s', day || ' 12:00:00') AS INTEGER) * 1000 WHERE recorded_at IS NULL;
31CREATE TRIGGER performances_dated AFTER INSERT ON performances WHEN NEW.recorded_at IS NULL
32BEGIN
33  UPDATE performances SET recorded_at = CAST(strftime('%s', NEW.day || ' 12:00:00') AS INTEGER) * 1000 WHERE id = NEW.id;
34END;
35
36-- an account's takes, newest first (you tab, profiles); a song's, newest first (takes, moves)
37CREATE INDEX performances_by_player ON performances (user_id, recorded_at);
38CREATE INDEX performances_by_song_time ON performances (song, recorded_at);