accounts-store.tsThe accounts' storage: users, credentials (passkeys), sessions and the WebAuthn challenges in flight, in the site's D1 database (the DB binding; schema in worker/migrations/0002_accounts.sql). auth.ts decides; this only reads and writes.
A challenge is consumed with DELETE … RETURNING: D1 runs every write on its one primary, one statement at a time, so of two verifies racing for the same challenge exactly one gets the row, and an expired row is never returned. That is what makes a challenge single-use and short-lived without a Durable Object: nothing here is live coordination, only a row that must be taken once.
12import { tokenHash } from './session';
14export type User = { id: string; displayName: string }; 15export type Purpose = 'login' | 'register' | 'add'; 16export type Challenge = { 17 challenge: string; 18 purpose: Purpose; 19 userId: string | null; 20 displayName: string | null; 21}; 22export type Credential = { 23 id: string; 24 userId: string; 25 publicKey: Uint8Array<ArrayBuffer>; 26 signCount: number; 27 transports: string[]; 28}; 29 30export function d1Accounts(db: D1Database, now: () => number = Date.now) { 31 return { 32 async putChallenge(c: Challenge, ttlMs: number): Promise<void> { 33 const at = now(); 34 await db.batch([ 35 db.prepare('DELETE FROM auth_challenges WHERE expires_at <= ?').bind(at), 36 db 37 .prepare( 38 'INSERT INTO auth_challenges (challenge, purpose, user_id, display_name, created_at, expires_at) VALUES (?, ?, ?, ?, ?, ?)', 39 ) 40 .bind(c.challenge, c.purpose, c.userId, c.displayName, at, at + ttlMs), 41 ]); 42 },
The challenge, removed so it can never be used again; null when it was never issued, is already used, or has expired.
46 async takeChallenge(challenge: string): Promise<Challenge | null> { 47 const row = await db 48 .prepare( 49 'DELETE FROM auth_challenges WHERE challenge = ? AND expires_at > ? RETURNING purpose, user_id, display_name', 50 ) 51 .bind(challenge, now()) 52 .first<{ purpose: Purpose; user_id: string | null; display_name: string | null }>(); 53 return row && { challenge, purpose: row.purpose, userId: row.user_id, displayName: row.display_name }; 54 },
56 async user(id: string): Promise<User | null> { 57 const row = await db 58 .prepare('SELECT id, display_name FROM users WHERE id = ?') 59 .bind(id) 60 .first<{ id: string; display_name: string }>(); 61 return row && { id: row.id, displayName: row.display_name }; 62 }, 63 64 async credential(id: string): Promise<Credential | null> { 65 const row = await db 66 .prepare('SELECT id, user_id, public_key, sign_count, transports FROM credentials WHERE id = ?') 67 .bind(id) 68 .first<{ id: string; user_id: string; public_key: ArrayBuffer | number[]; sign_count: number; transports: string }>(); 69 if (!row) return null; 70 return { 71 id: row.id, 72 userId: row.user_id, 73 // D1 returns a BLOB as an array of bytes; node:sqlite (the tests) as a Uint8Array 74 publicKey: new Uint8Array(row.public_key as ArrayLike<number>), 75 signCount: row.sign_count, 76 transports: JSON.parse(row.transports) as string[], 77 }; 78 }, 79 80 async credentialIds(userId: string): Promise<{ id: string; transports: string[] }[]> { 81 const { results } = await db 82 .prepare('SELECT id, transports FROM credentials WHERE user_id = ? ORDER BY created_at') 83 .bind(userId) 84 .all<{ id: string; transports: string }>(); 85 return results.map((r) => ({ id: r.id, transports: JSON.parse(r.transports) as string[] })); 86 },
A new account with its first passkey, and a session for it: all or nothing.
89 async createUser(user: User, credential: Omit<Credential, 'userId'>, sessionToken: string, ttlMs: number) { 90 const at = now(); 91 await db.batch([ 92 db.prepare('INSERT INTO users (id, display_name, created_at) VALUES (?, ?, ?)').bind(user.id, user.displayName, at), 93 insertCredential(db, { ...credential, userId: user.id }, at), 94 insertSession(db, await tokenHash(sessionToken), user.id, at, ttlMs), 95 ]); 96 },
98 async addCredential(credential: Credential): Promise<void> { 99 await insertCredential(db, credential, now()).run(); 100 }, 101 102 async setSignCount(id: string, signCount: number): Promise<void> { 103 await db.prepare('UPDATE credentials SET sign_count = ? WHERE id = ?').bind(signCount, id).run(); 104 }, 105 106 async createSession(userId: string, sessionToken: string, ttlMs: number): Promise<void> { 107 const at = now(); 108 await db.batch([ 109 db.prepare('DELETE FROM sessions WHERE expires_at <= ?').bind(at), 110 insertSession(db, await tokenHash(sessionToken), userId, at, ttlMs), 111 ]); 112 },
The signed-in user for a session token, or null when it is unknown or expired.
115 async sessionUser(sessionToken: string): Promise<User | null> { 116 const row = await db 117 .prepare( 118 'SELECT u.id, u.display_name FROM sessions s JOIN users u ON u.id = s.user_id WHERE s.id_hash = ? AND s.expires_at > ?', 119 ) 120 .bind(await tokenHash(sessionToken), now()) 121 .first<{ id: string; display_name: string }>(); 122 return row && { id: row.id, displayName: row.display_name }; 123 },
── what anyone may read (data.ts) ────────────── Every account, newest first, with how much of each kind it has. Never a secret: passkeys, sessions and challenges are counted, not shown.
132 async accountsPage(limit: number, offset: number): Promise<{ total: number; accounts: AccountSummary[] }> { 133 const at = now(); 134 const [total, page] = (await db.batch([ 135 db.prepare('SELECT count(*) AS n FROM users'), 136 db 137 .prepare( 138 `SELECT u.id, u.display_name AS name, u.created_at AS joined, 139 (SELECT count(*) FROM credentials c WHERE c.user_id = u.id) AS passkeys, 140 (SELECT count(*) FROM sessions s WHERE s.user_id = u.id AND s.expires_at > ?1) AS sessions, 141 (SELECT count(*) FROM radio_plays r WHERE r.user_id = u.id) AS radio_plays, 142 (SELECT count(*) FROM listener_songs l WHERE l.user_id = u.id) AS songs, 143 (SELECT count(*) FROM comments m WHERE m.user_id = u.id) AS comments, 144 (SELECT count(*) FROM pitches p WHERE p.user_id = u.id) AS pitches 145 FROM users u ORDER BY u.created_at DESC, u.id LIMIT ?2 OFFSET ?3`, 146 ) 147 .bind(at, limit, offset), 148 ])) as D1Result<Record<string, unknown>>[]; 149 return { 150 total: (total.results[0]?.n as number) ?? 0, 151 accounts: page.results.map((r) => ({ 152 id: r.id as string, 153 name: r.name as string, 154 joined: r.joined as number, 155 passkeys: r.passkeys as number, 156 sessions: r.sessions as number, 157 radioPlays: r.radio_plays as number, 158 songs: r.songs as number, 159 comments: r.comments as number, 160 pitches: r.pitches as number, 161 })), 162 }; 163 },
One account as anyone may see it: its passkeys and sessions by date only (never a credential id, public key or session hash).
167 async accountView(id: string): Promise<AccountView | null> { 168 const [users, credentials, sessions] = (await db.batch([ 169 db.prepare('SELECT id, display_name, created_at FROM users WHERE id = ?').bind(id), 170 db 171 .prepare('SELECT created_at, sign_count, transports FROM credentials WHERE user_id = ? ORDER BY created_at') 172 .bind(id), 173 db.prepare('SELECT created_at, expires_at FROM sessions WHERE user_id = ? ORDER BY created_at DESC').bind(id), 174 ])) as D1Result<Record<string, unknown>>[]; 175 const u = users.results[0]; 176 if (!u) return null; 177 const at = now(); 178 return { 179 id: u.id as string, 180 name: u.display_name as string, 181 joined: u.created_at as number, 182 passkeys: credentials.results.map((c) => ({ 183 created: c.created_at as number, 184 signCount: c.sign_count as number, 185 transports: JSON.parse(c.transports as string) as string[], 186 })), 187 sessions: sessions.results.map((r) => ({ 188 created: r.created_at as number, 189 expires: r.expires_at as number, 190 active: (r.expires_at as number) > at, 191 })), 192 }; 193 },
How many rows each accounts table holds; challenges by purpose, and how many are still live (not yet used or expired).
197 async accountTotals() { 198 const at = now(); 199 const [users, credentials, sessions, challenges, radio] = (await db.batch([ 200 db.prepare('SELECT count(*) AS n FROM users'), 201 db.prepare('SELECT count(*) AS n FROM credentials'), 202 db.prepare('SELECT count(*) AS n, coalesce(sum(expires_at > ?), 0) AS active FROM sessions').bind(at), 203 db 204 .prepare('SELECT purpose, count(*) AS n, coalesce(sum(expires_at > ?), 0) AS live FROM auth_challenges GROUP BY purpose') 205 .bind(at), 206 db.prepare('SELECT count(*) AS n FROM radio_plays'), 207 ])) as D1Result<Record<string, number | string>>[]; 208 const n = (r: D1Result<Record<string, number | string>>) => (r.results[0]?.n as number) ?? 0; 209 return { 210 users: n(users), 211 credentials: n(credentials), 212 sessions: { rows: n(sessions), active: (sessions.results[0]?.active as number) ?? 0 }, 213 challenges: Object.fromEntries( 214 challenges.results.map((r) => [r.purpose as string, { rows: r.n as number, live: r.live as number }]), 215 ) as Partial<Record<Purpose, { rows: number; live: number }>>, 216 radioPlays: n(radio), 217 }; 218 }, 219 }; 220} 221export type Accounts = ReturnType<typeof d1Accounts>;
223export type AccountSummary = { 224 id: string; 225 name: string; 226 joined: number; 227 passkeys: number; 228 sessions: number; // active 229 radioPlays: number; 230 songs: number; 231 comments: number; 232 pitches: number; 233}; 234export type AccountView = { 235 id: string; 236 name: string; 237 joined: number; 238 passkeys: { created: number; signCount: number; transports: string[] }[]; 239 sessions: { created: number; expires: number; active: boolean }[]; 240}; 241 242const insertCredential = (db: D1Database, c: Credential, at: number) => 243 db 244 .prepare( 245 'INSERT INTO credentials (id, user_id, public_key, sign_count, transports, created_at) VALUES (?, ?, ?, ?, ?, ?)', 246 ) 247 .bind(c.id, c.userId, c.publicKey, c.signCount, JSON.stringify(c.transports), at); 248 249const insertSession = (db: D1Database, idHash: string, userId: string, at: number, ttlMs: number) => 250 db 251 .prepare('INSERT INTO sessions (id_hash, user_id, created_at, expires_at) VALUES (?, ?, ?, ?)') 252 .bind(idHash, userId, at, at + ttlMs);