1-- Pitches: listeners' votes on them, and the listener songs that answer
2-- them. Owner: src/content-store.ts reads and writes both, for
3-- src/content.ts (the routes); src/data.ts reads them for the data tab.
4--
5-- What is identifying here, and why (migrations/CLAUDE.md): a vote names
6-- the account that cast it, so each account votes once per pitch and can
7-- take its vote back. Like every row, it is public: the data tab shows who
8-- voted for what (worker/README.md, What is public). Times are milliseconds
9-- since the epoch, UTC.
10
11-- One account's vote for one pitch; taking it back deletes the row. Only a
12-- public pitch can be voted on (content.ts checks; a held pitch's votes, if
13-- it were ever held after, stay as they were).
14CREATE TABLE pitch_votes (
15  pitch TEXT NOT NULL REFERENCES pitches (id) ON DELETE CASCADE,
16  user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
17  created_at INTEGER NOT NULL CHECK (created_at > 0),
18  PRIMARY KEY (pitch, user_id)
19) STRICT;
20CREATE INDEX pitch_votes_by_user ON pitch_votes (user_id);
21
22-- The pitch a listener song answers, set by the song's author (a site song
23-- says so in its SPEC.md, `pitch:`, which the build reads): the maker of a
24-- song is the one who can say what it answers. NULL when it answers none,
25-- and again if the pitch is deleted.
26ALTER TABLE listener_songs ADD COLUMN pitch TEXT REFERENCES pitches (id) ON DELETE SET NULL
27  CHECK (pitch IS NULL OR (length(pitch) = 22 AND pitch NOT GLOB '*[^A-Za-z0-9_-]*'));
28CREATE INDEX listener_songs_by_pitch ON listener_songs (pitch);