0002_accounts.sqlAccounts, signed in with passkeys (WebAuthn), and each account's radio history. Owners: src/accounts-store.ts reads and writes users, credentials, sessions and auth_challenges (for src/auth.ts); src/radio.ts reads and writes radio_plays.
What is identifying here, and why (migrations/CLAUDE.md): a user is a random id and the display name its owner typed, so the site can say who is signed in and charge that account's daily Jev budget (src/budget.ts). A credential is the passkey's public key, which only verifies signatures. A session is the SHA-256 of a random cookie token, never the token, so a read of this table cannot sign anyone in. No address, email, password or request is stored. Times are milliseconds since the epoch, UTC.
A passkey: its credential id (base64url, as the browser reports it), the COSE public key, the authenticator's signature counter (0 for most synced passkeys, which do not count), and the transports the browser 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);
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);
A WebAuthn ceremony in flight: the challenge the Worker issued, what it
is for, and until when. Consumed by the verify step with DELETE …
RETURNING, so each is used at most once, and refused once expired.
login sign in with any passkey on this site; no user yet
register a new account: its id and display name, created on verify
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);
What the radio played for a signed-in listener, newest last: the song (a "<theme>/<song>" id the deploy had) and when it started. Kept to the 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);