1// The accounts' storage: users, credentials (passkeys), sessions and the
2// WebAuthn challenges in flight, in the site's D1 database (the DB binding;
3// schema in worker/migrations/0002_accounts.sql). auth.ts decides; this
4// only reads and writes.
5//
6// A challenge is consumed with DELETE … RETURNING: D1 runs every write on
7// its one primary, one statement at a time, so of two verifies racing for
8// the same challenge exactly one gets the row, and an expired row is
9// never returned. That is what makes a challenge single-use and short-lived
10// without a Durable Object: nothing here is live coordination, only a row
11// that must be taken once.
12import { tokenHash } from './session';
13
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    },
43
44    // The challenge, removed so it can never be used again; null when it
45    // 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    },
55
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    },
87
88    // 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    },
97
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    },
113
114    // 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    },
124
125    async deleteSession(sessionToken: string): Promise<void> {
126      await db.prepare('DELETE FROM sessions WHERE id_hash = ?').bind(await tokenHash(sessionToken)).run();
127    },
128
129    // ── what anyone may read (data.ts) ──────────────
130    // Every account, newest first, with how much of each kind it has. Never
131    // 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    },
164
165    // One account as anyone may see it: its passkeys and sessions by date
166    // 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    },
194
195    // How many rows each accounts table holds; challenges by purpose, and
196    // 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>;
222
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);