馃 distributed transcription service
thistle.dunkirk.sh
dunkirk.sh
1import db from "../db/schema";
2
3const SESSION_DURATION = 7 * 24 * 60 * 60; // 7 days in seconds
4
5export type UserRole = "user" | "admin";
6
7export interface User {
8 id: number;
9 email: string;
10 name: string | null;
11 avatar: string;
12 created_at: number;
13 role: UserRole;
14 last_login: number | null;
15}
16
17export interface Session {
18 id: string;
19 user_id: number;
20 ip_address: string | null;
21 user_agent: string | null;
22 created_at: number;
23 expires_at: number;
24}
25
26export function createSession(
27 userId: number,
28 ipAddress?: string,
29 userAgent?: string,
30): string {
31 const sessionId = crypto.randomUUID();
32 const expiresAt = Math.floor(Date.now() / 1000) + SESSION_DURATION;
33
34 db.run(
35 "INSERT INTO sessions (id, user_id, ip_address, user_agent, expires_at) VALUES (?, ?, ?, ?, ?)",
36 [sessionId, userId, ipAddress ?? null, userAgent ?? null, expiresAt],
37 );
38
39 return sessionId;
40}
41
42export function getSession(sessionId: string): Session | null {
43 const now = Math.floor(Date.now() / 1000);
44
45 const session = db
46 .query<Session, [string, number]>(
47 "SELECT id, user_id, ip_address, user_agent, created_at, expires_at FROM sessions WHERE id = ? AND expires_at > ?",
48 )
49 .get(sessionId, now);
50
51 return session ?? null;
52}
53
54export function getUserBySession(sessionId: string): User | null {
55 const session = getSession(sessionId);
56 if (!session) return null;
57
58 const user = db
59 .query<User, [number]>(
60 "SELECT id, email, name, avatar, created_at, role, last_login FROM users WHERE id = ?",
61 )
62 .get(session.user_id);
63
64 return user ?? null;
65}
66
67export function getUserByEmail(email: string): User | null {
68 const user = db
69 .query<User, [string]>(
70 "SELECT id, email, name, avatar, created_at, role, last_login FROM users WHERE email = ?",
71 )
72 .get(email);
73
74 return user ?? null;
75}
76
77export function deleteSession(sessionId: string): void {
78 db.run("DELETE FROM sessions WHERE id = ?", [sessionId]);
79}
80
81export function cleanupExpiredSessions(): void {
82 const now = Math.floor(Date.now() / 1000);
83 db.run("DELETE FROM sessions WHERE expires_at <= ?", [now]);
84}
85
86export async function createUser(
87 email: string,
88 password: string,
89 name?: string,
90): Promise<User> {
91 // Generate deterministic avatar from email
92 const encoder = new TextEncoder();
93 const data = encoder.encode(email.toLowerCase());
94 const hashBuffer = await crypto.subtle.digest("SHA-256", data);
95 const hashArray = Array.from(new Uint8Array(hashBuffer));
96 const avatar = hashArray
97 .map((b) => b.toString(16).padStart(2, "0"))
98 .join("")
99 .substring(0, 16);
100
101 const result = db.run(
102 "INSERT INTO users (email, password_hash, name, avatar) VALUES (?, ?, ?, ?)",
103 [email, password, name ?? null, avatar],
104 );
105
106 const user = db
107 .query<User, [number]>(
108 "SELECT id, email, name, avatar, created_at, role, last_login FROM users WHERE id = ?",
109 )
110 .get(Number(result.lastInsertRowid));
111
112 if (!user) {
113 throw new Error("Failed to create user");
114 }
115
116 return user;
117}
118
119export async function authenticateUser(
120 email: string,
121 password: string,
122): Promise<User | null> {
123 const result = db
124 .query<
125 {
126 id: number;
127 email: string;
128 name: string | null;
129 avatar: string;
130 password_hash: string;
131 created_at: number;
132 role: UserRole;
133 last_login: number | null;
134 },
135 [string]
136 >(
137 "SELECT id, email, name, avatar, password_hash, created_at, role, last_login FROM users WHERE email = ?",
138 )
139 .get(email);
140
141 if (!result) {
142 // Dummy comparison to prevent timing-based account enumeration
143 const dummyHash = "0".repeat(64);
144 password === dummyHash;
145 return null;
146 }
147
148 if (password !== result.password_hash) return null;
149
150 // Update last_login
151 const now = Math.floor(Date.now() / 1000);
152 db.run("UPDATE users SET last_login = ? WHERE id = ?", [now, result.id]);
153
154 return {
155 id: result.id,
156 email: result.email,
157 name: result.name,
158 avatar: result.avatar,
159 created_at: result.created_at,
160 role: result.role,
161 last_login: now,
162 };
163}
164
165export function getUserSessionsForUser(userId: number): Session[] {
166 const now = Math.floor(Date.now() / 1000);
167
168 const sessions = db
169 .query<Session, [number, number]>(
170 "SELECT id, user_id, ip_address, user_agent, created_at, expires_at FROM sessions WHERE user_id = ? AND expires_at > ? ORDER BY created_at DESC",
171 )
172 .all(userId, now);
173
174 return sessions;
175}
176
177export function getSessionFromRequest(req: Request): string | null {
178 const cookie = req.headers.get("cookie");
179 if (!cookie) return null;
180
181 const match = cookie.match(/session=([^;]+)/);
182 return match?.[1] ?? null;
183}
184
185export async function deleteUser(userId: number): Promise<void> {
186 // Prevent deleting the ghost user
187 if (userId === 0) {
188 throw new Error("Cannot delete ghost user account");
189 }
190
191 // Get user's subscription if they have one
192 const subscription = db
193 .query<{ id: string; status: string; cancel_at_period_end: number }, [number]>(
194 "SELECT id, status, cancel_at_period_end FROM subscriptions WHERE user_id = ? ORDER BY created_at DESC LIMIT 1",
195 )
196 .get(userId);
197
198 // Cancel subscription if it exists and is not already canceled or scheduled to cancel
199 if (
200 subscription &&
201 subscription.status !== 'canceled' &&
202 subscription.status !== 'expired' &&
203 !subscription.cancel_at_period_end
204 ) {
205 try {
206 const { polar } = await import("./polar");
207 await polar.subscriptions.update({
208 id: subscription.id,
209 subscriptionUpdate: { cancelAtPeriodEnd: true },
210 });
211 console.log(
212 `[User Delete] Canceled subscription ${subscription.id} for user ${userId}`,
213 );
214 } catch (error) {
215 console.error(
216 `[User Delete] Failed to cancel subscription ${subscription.id}:`,
217 error,
218 );
219 // Continue with user deletion even if subscription cancellation fails
220 }
221 } else if (subscription) {
222 console.log(
223 `[User Delete] Skipping cancellation for subscription ${subscription.id} (status: ${subscription.status}, cancel_at_period_end: ${subscription.cancel_at_period_end})`,
224 );
225 }
226
227 // Reassign class transcriptions to ghost user (id=0)
228 // Delete personal transcriptions (no class_id)
229 db.run(
230 "UPDATE transcriptions SET user_id = 0 WHERE user_id = ? AND class_id IS NOT NULL",
231 [userId],
232 );
233 db.run(
234 "DELETE FROM transcriptions WHERE user_id = ? AND class_id IS NULL",
235 [userId],
236 );
237
238 // Delete user (CASCADE will handle sessions, passkeys, subscriptions, class_members)
239 db.run("DELETE FROM users WHERE id = ?", [userId]);
240}
241
242export function updateUserEmail(userId: number, newEmail: string): void {
243 db.run("UPDATE users SET email = ? WHERE id = ?", [newEmail, userId]);
244}
245
246export function updateUserName(userId: number, newName: string): void {
247 db.run("UPDATE users SET name = ? WHERE id = ?", [newName, userId]);
248}
249
250export function updateUserAvatar(userId: number, avatar: string): void {
251 db.run("UPDATE users SET avatar = ? WHERE id = ?", [avatar, userId]);
252}
253
254export async function updateUserPassword(
255 userId: number,
256 newPassword: string,
257): Promise<void> {
258 db.run("UPDATE users SET password_hash = ? WHERE id = ?", [
259 newPassword,
260 userId,
261 ]);
262 db.run("DELETE FROM sessions WHERE user_id = ?", [userId]);
263}
264
265/**
266 * Email verification functions
267 */
268
269export function createEmailVerificationToken(userId: number): { code: string; token: string; sentAt: number } {
270 // Generate a 6-digit code for user to enter
271 const code = Math.floor(100000 + Math.random() * 900000).toString();
272 const id = crypto.randomUUID();
273 const token = crypto.randomUUID(); // Separate token for URL
274 const expiresAt = Math.floor(Date.now() / 1000) + 24 * 60 * 60; // 24 hours
275 const sentAt = Math.floor(Date.now() / 1000); // Timestamp when code is created
276
277 // Delete any existing tokens for this user
278 db.run("DELETE FROM email_verification_tokens WHERE user_id = ?", [userId]);
279
280 // Store the code as the token field (for manual entry)
281 db.run(
282 "INSERT INTO email_verification_tokens (id, user_id, token, expires_at) VALUES (?, ?, ?, ?)",
283 [id, userId, code, expiresAt],
284 );
285
286 // Store the URL token as a separate entry
287 db.run(
288 "INSERT INTO email_verification_tokens (id, user_id, token, expires_at) VALUES (?, ?, ?, ?)",
289 [crypto.randomUUID(), userId, token, expiresAt],
290 );
291
292 return { code, token, sentAt };
293}
294
295export function verifyEmailToken(
296 token: string,
297): { userId: number; email: string } | null {
298 const now = Math.floor(Date.now() / 1000);
299
300 const result = db
301 .query<
302 { user_id: number; email: string },
303 [string, number]
304 >(
305 `SELECT evt.user_id, u.email
306 FROM email_verification_tokens evt
307 JOIN users u ON evt.user_id = u.id
308 WHERE evt.token = ? AND evt.expires_at > ?`,
309 )
310 .get(token, now);
311
312 if (!result) return null;
313
314 // Mark email as verified
315 db.run("UPDATE users SET email_verified = 1 WHERE id = ?", [result.user_id]);
316
317 // Delete the token (one-time use)
318 db.run("DELETE FROM email_verification_tokens WHERE token = ?", [token]);
319
320 return { userId: result.user_id, email: result.email };
321}
322
323export function verifyEmailCode(
324 userId: number,
325 code: string,
326): boolean {
327 const now = Math.floor(Date.now() / 1000);
328
329 const result = db
330 .query<
331 { user_id: number },
332 [number, string, number]
333 >(
334 `SELECT user_id
335 FROM email_verification_tokens
336 WHERE user_id = ? AND token = ? AND expires_at > ?`,
337 )
338 .get(userId, code, now);
339
340 if (!result) return false;
341
342 // Mark email as verified
343 db.run("UPDATE users SET email_verified = 1 WHERE id = ?", [userId]);
344
345 // Delete the token (one-time use)
346 db.run("DELETE FROM email_verification_tokens WHERE user_id = ?", [userId]);
347
348 return true;
349}
350
351export function isEmailVerified(userId: number): boolean {
352 const result = db
353 .query<{ email_verified: number }, [number]>(
354 "SELECT email_verified FROM users WHERE id = ?",
355 )
356 .get(userId);
357
358 return result?.email_verified === 1;
359}
360
361export function getVerificationCodeSentAt(userId: number): number | null {
362 const result = db
363 .query<{ created_at: number }, [number]>(
364 "SELECT MAX(created_at) as created_at FROM email_verification_tokens WHERE user_id = ?",
365 )
366 .get(userId);
367
368 return result?.created_at ?? null;
369}
370
371/**
372 * Password reset functions
373 */
374
375export function createPasswordResetToken(userId: number): string {
376 const token = crypto.randomUUID();
377 const id = crypto.randomUUID();
378 const expiresAt = Math.floor(Date.now() / 1000) + 60 * 60; // 1 hour
379
380 // Delete any existing tokens for this user
381 db.run("DELETE FROM password_reset_tokens WHERE user_id = ?", [userId]);
382
383 db.run(
384 "INSERT INTO password_reset_tokens (id, user_id, token, expires_at) VALUES (?, ?, ?, ?)",
385 [id, userId, token, expiresAt],
386 );
387
388 return token;
389}
390
391export function verifyPasswordResetToken(token: string): number | null {
392 const now = Math.floor(Date.now() / 1000);
393
394 const result = db
395 .query<{ user_id: number }, [string, number]>(
396 "SELECT user_id FROM password_reset_tokens WHERE token = ? AND expires_at > ?",
397 )
398 .get(token, now);
399
400 return result?.user_id ?? null;
401}
402
403export function consumePasswordResetToken(token: string): void {
404 db.run("DELETE FROM password_reset_tokens WHERE token = ?", [token]);
405}
406
407/**
408 * Email change functions
409 */
410
411export function createEmailChangeToken(userId: number, newEmail: string): string {
412 const token = crypto.randomUUID();
413 const id = crypto.randomUUID();
414 const expiresAt = Math.floor(Date.now() / 1000) + 24 * 60 * 60; // 24 hours
415
416 // Delete any existing email change tokens for this user
417 db.run("DELETE FROM email_change_tokens WHERE user_id = ?", [userId]);
418
419 db.run(
420 "INSERT INTO email_change_tokens (id, user_id, new_email, token, expires_at) VALUES (?, ?, ?, ?, ?)",
421 [id, userId, newEmail, token, expiresAt],
422 );
423
424 return token;
425}
426
427export function verifyEmailChangeToken(token: string): { userId: number; newEmail: string } | null {
428 const now = Math.floor(Date.now() / 1000);
429
430 const result = db
431 .query<{ user_id: number; new_email: string }, [string, number]>(
432 "SELECT user_id, new_email FROM email_change_tokens WHERE token = ? AND expires_at > ?",
433 )
434 .get(token, now);
435
436 if (!result) return null;
437
438 return { userId: result.user_id, newEmail: result.new_email };
439}
440
441export function consumeEmailChangeToken(token: string): void {
442 db.run("DELETE FROM email_change_tokens WHERE token = ?", [token]);
443}
444
445export function isUserAdmin(userId: number): boolean {
446 const result = db
447 .query<{ role: UserRole }, [number]>("SELECT role FROM users WHERE id = ?")
448 .get(userId);
449
450 return result?.role === "admin";
451}
452
453export function updateUserRole(userId: number, role: UserRole): void {
454 db.run("UPDATE users SET role = ? WHERE id = ?", [role, userId]);
455}
456
457export function getAllUsers(): Array<{
458 id: number;
459 email: string;
460 name: string | null;
461 avatar: string;
462 created_at: number;
463 role: UserRole;
464}> {
465 return db
466 .query<
467 {
468 id: number;
469 email: string;
470 name: string | null;
471 avatar: string;
472 created_at: number;
473 role: UserRole;
474 last_login: number | null;
475 },
476 []
477 >(
478 "SELECT id, email, name, avatar, created_at, role, last_login FROM users ORDER BY created_at DESC",
479 )
480 .all();
481}
482
483export function getAllTranscriptions(): Array<{
484 id: string;
485 user_id: number;
486 user_email: string;
487 user_name: string | null;
488 original_filename: string;
489 status: string;
490 created_at: number;
491 error_message: string | null;
492}> {
493 return db
494 .query<
495 {
496 id: string;
497 user_id: number;
498 user_email: string;
499 user_name: string | null;
500 original_filename: string;
501 status: string;
502 created_at: number;
503 error_message: string | null;
504 },
505 []
506 >(
507 `SELECT
508 t.id,
509 t.user_id,
510 u.email as user_email,
511 u.name as user_name,
512 t.original_filename,
513 t.status,
514 t.created_at,
515 t.error_message
516 FROM transcriptions t
517 LEFT JOIN users u ON t.user_id = u.id
518 ORDER BY t.created_at DESC`,
519 )
520 .all();
521}
522
523export function deleteTranscription(transcriptionId: string): void {
524 const transcription = db
525 .query<{ id: string; filename: string }, [string]>(
526 "SELECT id, filename FROM transcriptions WHERE id = ?",
527 )
528 .get(transcriptionId);
529
530 if (!transcription) {
531 throw new Error("Transcription not found");
532 }
533
534 // Delete database record
535 db.run("DELETE FROM transcriptions WHERE id = ?", [transcriptionId]);
536
537 // Delete files (audio file and transcript files)
538 try {
539 const audioPath = `./uploads/${transcription.filename}`;
540 const transcriptPath = `./transcripts/${transcriptionId}.txt`;
541 const vttPath = `./transcripts/${transcriptionId}.vtt`;
542
543 if (Bun.file(audioPath).size) {
544 Bun.write(audioPath, "").then(() => {
545 // File deleted by overwriting with empty content, then unlink
546 import("node:fs").then((fs) => {
547 fs.unlinkSync(audioPath);
548 });
549 });
550 }
551
552 if (Bun.file(transcriptPath).size) {
553 import("node:fs").then((fs) => {
554 fs.unlinkSync(transcriptPath);
555 });
556 }
557
558 if (Bun.file(vttPath).size) {
559 import("node:fs").then((fs) => {
560 fs.unlinkSync(vttPath);
561 });
562 }
563 } catch {
564 // Files might not exist, ignore errors
565 }
566}
567
568export function getSessionsForUser(userId: number): Session[] {
569 const now = Math.floor(Date.now() / 1000);
570 return db
571 .query<Session, [number, number]>(
572 "SELECT id, user_id, ip_address, user_agent, created_at, expires_at FROM sessions WHERE user_id = ? AND expires_at > ? ORDER BY created_at DESC",
573 )
574 .all(userId, now);
575}
576
577export function deleteSessionById(sessionId: string, userId: number): boolean {
578 const result = db.run("DELETE FROM sessions WHERE id = ? AND user_id = ?", [
579 sessionId,
580 userId,
581 ]);
582 return result.changes > 0;
583}
584
585export function deleteAllUserSessions(userId: number): void {
586 db.run("DELETE FROM sessions WHERE user_id = ?", [userId]);
587}
588
589export function updateUserEmailAddress(userId: number, newEmail: string): void {
590 db.run("UPDATE users SET email = ? WHERE id = ?", [newEmail, userId]);
591}
592
593export interface UserWithStats {
594 id: number;
595 email: string;
596 name: string | null;
597 avatar: string;
598 created_at: number;
599 role: UserRole;
600 last_login: number | null;
601 transcription_count: number;
602 subscription_status: string | null;
603 subscription_id: string | null;
604}
605
606export function getAllUsersWithStats(): UserWithStats[] {
607 return db
608 .query<UserWithStats, []>(
609 `SELECT
610 u.id,
611 u.email,
612 u.name,
613 u.avatar,
614 u.created_at,
615 u.role,
616 u.last_login,
617 COUNT(DISTINCT t.id) as transcription_count,
618 s.status as subscription_status,
619 s.id as subscription_id
620 FROM users u
621 LEFT JOIN transcriptions t ON u.id = t.user_id
622 LEFT JOIN subscriptions s ON u.id = s.user_id AND s.status IN ('active', 'trialing', 'past_due')
623 GROUP BY u.id
624 ORDER BY u.created_at DESC`,
625 )
626 .all();
627}