1-- Accounts, signed in with passkeys (WebAuthn), and each account's radio
2-- history. Owners: src/accounts-store.ts reads and writes users,
3-- credentials, sessions and auth_challenges (for src/auth.ts);
4-- src/radio.ts reads and writes radio_plays.
5--
6-- What is identifying here, and why (migrations/CLAUDE.md): a user is a
7-- random id and the display name its owner typed, so the site can say who
8-- is signed in and charge that account's daily Jev budget (src/budget.ts).
9-- A credential is the passkey's public key, which only verifies signatures.
10-- A session is the SHA-256 of a random cookie token, never the token, so a
11-- read of this table cannot sign anyone in. No address, email, password or
12-- request is stored. Times are milliseconds since the epoch, UTC.
13
14CREATE TABLE users (
15  id TEXT PRIMARY KEY CHECK (length(id) = 22 AND id NOT GLOB '*[^A-Za-z0-9_-]*'),
16  display_name TEXT NOT NULL CHECK (
17    length(display_name) BETWEEN 1 AND 40 AND display_name = trim(display_name)
18  ),
19  created_at INTEGER NOT NULL CHECK (created_at > 0)
20) STRICT;
21
22-- A passkey: its credential id (base64url, as the browser reports it), the
23-- COSE public key, the authenticator's signature counter (0 for most
24-- synced passkeys, which do not count), and the transports the browser
25-- reported, as a JSON array of strings. A user has one or more.
26CREATE TABLE credentials (
27  id TEXT PRIMARY KEY CHECK (
28    length(id) BETWEEN 16 AND 1366 AND id NOT GLOB '*[^A-Za-z0-9_-]*'
29  ),
30  user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
31  public_key BLOB NOT NULL CHECK (length(public_key) > 0),
32  sign_count INTEGER NOT NULL CHECK (sign_count >= 0),
33  transports TEXT NOT NULL CHECK (json_valid(transports) AND json_type(transports) = 'array'),
34  created_at INTEGER NOT NULL CHECK (created_at > 0)
35) STRICT;
36CREATE INDEX credentials_by_user ON credentials (user_id);
37
38-- A signed-in browser: the SHA-256 (hex) of its cookie's token.
39CREATE TABLE sessions (
40  id_hash TEXT PRIMARY KEY CHECK (length(id_hash) = 64 AND id_hash NOT GLOB '*[^0-9a-f]*'),
41  user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
42  created_at INTEGER NOT NULL CHECK (created_at > 0),
43  expires_at INTEGER NOT NULL CHECK (expires_at > created_at)
44) STRICT;
45CREATE INDEX sessions_by_user ON sessions (user_id);
46CREATE INDEX sessions_by_expiry ON sessions (expires_at);
47
48-- A WebAuthn ceremony in flight: the challenge the Worker issued, what it
49-- is for, and until when. Consumed by the verify step with DELETE …
50-- RETURNING, so each is used at most once, and refused once expired.
51--   login     sign in with any passkey on this site; no user yet
52--   register  a new account: its id and display name, created on verify
53--   add       another passkey for the signed-in account `user_id`
54CREATE TABLE auth_challenges (
55  challenge TEXT PRIMARY KEY CHECK (
56    length(challenge) = 43 AND challenge NOT GLOB '*[^A-Za-z0-9_-]*'
57  ),
58  purpose TEXT NOT NULL CHECK (purpose IN ('login', 'register', 'add')),
59  user_id TEXT,
60  display_name TEXT,
61  created_at INTEGER NOT NULL CHECK (created_at > 0),
62  expires_at INTEGER NOT NULL CHECK (expires_at > created_at),
63  -- (a CHECK passes when it is NULL, so every test here is NOT NULL first)
64  CHECK (
65    (purpose = 'login' AND user_id IS NULL AND display_name IS NULL)
66    OR (
67      purpose = 'register' AND user_id IS NOT NULL AND length(user_id) = 22
68      AND display_name IS NOT NULL AND length(display_name) BETWEEN 1 AND 40
69    )
70    OR (purpose = 'add' AND user_id IS NOT NULL AND display_name IS NULL)
71  )
72) STRICT;
73CREATE INDEX auth_challenges_by_expiry ON auth_challenges (expires_at);
74
75-- What the radio played for a signed-in listener, newest last: the song
76-- (a "<theme>/<song>" id the deploy had) and when it started. Kept to the
77-- newest few hundred per user by src/radio.ts.
78CREATE TABLE radio_plays (
79  id INTEGER PRIMARY KEY,
80  user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
81  song_id TEXT NOT NULL CHECK (
82    length(song_id) BETWEEN 3 AND 129 AND song_id GLOB '*/*' AND song_id NOT GLOB '*[^a-z0-9/-]*'
83  ),
84  played_at INTEGER NOT NULL CHECK (played_at > 0)
85) STRICT;
86CREATE INDEX radio_plays_by_user ON radio_plays (user_id, played_at);