1# worker/migrations
2
3The schema of `jevstrudel`, the site's D1 database (the Worker's `DB`
4binding), as numbered SQL files that wrangler applies in order, each once
5per database. It records what it has applied in the database's own
6`d1_migrations` table.
7
8| File | What it adds |
9|---|---|
10| `0001_votes.sql` | `votes`: the "do you agree with Jev?" counts, one row per (a, b, pick) (`src/votes.ts`). |
11| `0002_accounts.sql` | `users`, `credentials` (passkeys), `sessions` (token hashes) and `auth_challenges` (`src/accounts-store.ts`, for `src/auth.ts`), and `radio_plays`, a signed-in listener's radio history (`src/radio.ts`). |
12| `0003_listening.sql` | `reactions` (every listener's 🔥/😴, a count per song, section and reaction), `performances` (one play of a song, for a replay link) and `performance_segments` (what each jev() played per segment: Jev's decision history) (`src/listening.ts`). |
13| `0004_listener_content.sql` | `listener_songs`, `song_revisions` (each with Jev's screening verdict and the critic's score), `covers` (records; the images are R2), `comments`, `pitches`, and `content_jobs`, the screening and scoring Jev still owes, kept by triggers (`src/content-store.ts`, for `src/content.ts` and `src/content-jobs.ts`). |
14| `0005_pitch_votes.sql` | `pitch_votes`, one row per account per pitch it voted for, and `listener_songs.pitch`, the pitch a listener song answers, set by its author (`src/content-store.ts`, for `src/content.ts`). Who voted for what is public, like every row. |
15| `0006_performance_players.sql` | `performances.user_id`, the account that played a take (signed in; public, set null when the account is deleted), and `performances.recorded_at`, when to the millisecond (takes from before are dated noon UTC of their day) (`src/listening-store.ts`, for `src/listening.ts`, `src/data.ts` and `src/activity.ts`). |
16
17Every table is public to read, people included, on the jev panel's data
18tab (`src/data.ts`), except the columns that are secrets: a session's
19`id_hash`, a credential's `id` and `public_key`, and every
20`auth_challenges` row, which are counted and dated only. jevstrudel is a
21playground shared in the Jev community; that is what it stores, shown.
22
23Where they are applied:
24
25- `buck2 run //:dev` and `//:preview` apply them to their local databases
26  on every start (`tools/dev/migrate.mjs`).
27- `nix run .#deploy` applies them to the real database before publishing
28  the Worker (`tools/deploy/resources.mjs`).
29- The tests apply them to an in-memory SQLite (`worker/test/d1.ts`).
30
31To add one: the next number, a lowercase name,
32`wrangler d1 migrations create jevstrudel <name>` from `worker/` (or write the
33file by hand), then restart `//:dev`, or apply it to the running one with
34`wrangler d1 migrations apply jevstrudel --local --env dev --config
35worker/wrangler.json` from the repo root.
36
37To look at the local data: `wrangler d1 execute jevstrudel --local --env dev
38--config worker/wrangler.json --command "SELECT * FROM votes"`.