馃 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<
194 { id: string; status: string; cancel_at_period_end: number },
195 [number]
196 >(
197 "SELECT id, status, cancel_at_period_end FROM subscriptions WHERE user_id = ? ORDER BY created_at DESC LIMIT 1",
198 )
199 .get(userId);
200
201 // Cancel subscription if it exists and is not already canceled or scheduled to cancel
202 if (
203 subscription &&
204 subscription.status !== "canceled" &&
205 subscription.status !== "expired" &&
206 !subscription.cancel_at_period_end
207 ) {
208 try {
209 const { polar } = await import("./polar");
210 await polar.subscriptions.update({
211 id: subscription.id,
212 subscriptionUpdate: { cancelAtPeriodEnd: true },
213 });
214 console.log(
215 `[User Delete] Canceled subscription ${subscription.id} for user ${userId}`,
216 );
217 } catch (error) {
218 console.error(
219 `[User Delete] Failed to cancel subscription ${subscription.id}:`,
220 error,
221 );
222 // Continue with user deletion even if subscription cancellation fails
223 }
224 } else if (subscription) {
225 console.log(
226 `[User Delete] Skipping cancellation for subscription ${subscription.id} (status: ${subscription.status}, cancel_at_period_end: ${subscription.cancel_at_period_end})`,
227 );
228 }
229
230 // Reassign class transcriptions to ghost user (id=0)
231 // Delete personal transcriptions (no class_id)
232 db.run(
233 "UPDATE transcriptions SET user_id = 0 WHERE user_id = ? AND class_id IS NOT NULL",
234 [userId],
235 );
236 db.run("DELETE FROM transcriptions WHERE user_id = ? AND class_id IS NULL", [
237 userId,
238 ]);
239
240 // Delete user (CASCADE will handle sessions, passkeys, subscriptions, class_members)
241 db.run("DELETE FROM users WHERE id = ?", [userId]);
242}
243
244export function updateUserEmail(userId: number, newEmail: string): void {
245 db.run("UPDATE users SET email = ? WHERE id = ?", [newEmail, userId]);
246}
247
248export function updateUserName(userId: number, newName: string): void {
249 db.run("UPDATE users SET name = ? WHERE id = ?", [newName, userId]);
250}
251
252export function updateUserAvatar(userId: number, avatar: string): void {
253 db.run("UPDATE users SET avatar = ? WHERE id = ?", [avatar, userId]);
254}
255
256export async function updateUserPassword(
257 userId: number,
258 newPassword: string,
259): Promise<void> {
260 db.run("UPDATE users SET password_hash = ? WHERE id = ?", [
261 newPassword,
262 userId,
263 ]);
264 db.run("DELETE FROM sessions WHERE user_id = ?", [userId]);
265}
266
267/**
268 * Email verification functions
269 */
270
271export function createEmailVerificationToken(userId: number): {
272 code: string;
273 token: string;
274 sentAt: number;
275} {
276 // Generate a 6-digit code for user to enter
277 const code = Math.floor(100000 + Math.random() * 900000).toString();
278 const id = crypto.randomUUID();
279 const token = crypto.randomUUID(); // Separate token for URL
280 const expiresAt = Math.floor(Date.now() / 1000) + 24 * 60 * 60; // 24 hours
281 const sentAt = Math.floor(Date.now() / 1000); // Timestamp when code is created
282
283 // Delete any existing tokens for this user
284 db.run("DELETE FROM email_verification_tokens WHERE user_id = ?", [userId]);
285
286 // Store the code as the token field (for manual entry)
287 db.run(
288 "INSERT INTO email_verification_tokens (id, user_id, token, expires_at) VALUES (?, ?, ?, ?)",
289 [id, userId, code, expiresAt],
290 );
291
292 // Store the URL token as a separate entry
293 db.run(
294 "INSERT INTO email_verification_tokens (id, user_id, token, expires_at) VALUES (?, ?, ?, ?)",
295 [crypto.randomUUID(), userId, token, expiresAt],
296 );
297
298 return { code, token, sentAt };
299}
300
301export function verifyEmailToken(
302 token: string,
303): { userId: number; email: string } | null {
304 const now = Math.floor(Date.now() / 1000);
305
306 const result = db
307 .query<{ user_id: number; email: string }, [string, number]>(
308 `SELECT evt.user_id, u.email
309 FROM email_verification_tokens evt
310 JOIN users u ON evt.user_id = u.id
311 WHERE evt.token = ? AND evt.expires_at > ?`,
312 )
313 .get(token, now);
314
315 if (!result) return null;
316
317 // Mark email as verified
318 db.run("UPDATE users SET email_verified = 1 WHERE id = ?", [result.user_id]);
319
320 // Delete the token (one-time use)
321 db.run("DELETE FROM email_verification_tokens WHERE token = ?", [token]);
322
323 return { userId: result.user_id, email: result.email };
324}
325
326export function verifyEmailCode(userId: number, code: string): boolean {
327 const now = Math.floor(Date.now() / 1000);
328
329 const result = db
330 .query<{ user_id: number }, [number, string, number]>(
331 `SELECT user_id
332 FROM email_verification_tokens
333 WHERE user_id = ? AND token = ? AND expires_at > ?`,
334 )
335 .get(userId, code, now);
336
337 if (!result) return false;
338
339 // Mark email as verified
340 db.run("UPDATE users SET email_verified = 1 WHERE id = ?", [userId]);
341
342 // Delete the token (one-time use)
343 db.run("DELETE FROM email_verification_tokens WHERE user_id = ?", [userId]);
344
345 return true;
346}
347
348export function isEmailVerified(userId: number): boolean {
349 const result = db
350 .query<{ email_verified: number }, [number]>(
351 "SELECT email_verified FROM users WHERE id = ?",
352 )
353 .get(userId);
354
355 return result?.email_verified === 1;
356}
357
358export function getVerificationCodeSentAt(userId: number): number | null {
359 const result = db
360 .query<{ created_at: number }, [number]>(
361 "SELECT MAX(created_at) as created_at FROM email_verification_tokens WHERE user_id = ?",
362 )
363 .get(userId);
364
365 return result?.created_at ?? null;
366}
367
368/**
369 * Password reset functions
370 */
371
372export function createPasswordResetToken(userId: number): string {
373 const token = crypto.randomUUID();
374 const id = crypto.randomUUID();
375 const expiresAt = Math.floor(Date.now() / 1000) + 60 * 60; // 1 hour
376
377 // Delete any existing tokens for this user
378 db.run("DELETE FROM password_reset_tokens WHERE user_id = ?", [userId]);
379
380 db.run(
381 "INSERT INTO password_reset_tokens (id, user_id, token, expires_at) VALUES (?, ?, ?, ?)",
382 [id, userId, token, expiresAt],
383 );
384
385 return token;
386}
387
388export function verifyPasswordResetToken(token: string): number | null {
389 const now = Math.floor(Date.now() / 1000);
390
391 const result = db
392 .query<{ user_id: number }, [string, number]>(
393 "SELECT user_id FROM password_reset_tokens WHERE token = ? AND expires_at > ?",
394 )
395 .get(token, now);
396
397 return result?.user_id ?? null;
398}
399
400export function consumePasswordResetToken(token: string): void {
401 db.run("DELETE FROM password_reset_tokens WHERE token = ?", [token]);
402}
403
404/**
405 * Email change functions
406 */
407
408export function createEmailChangeToken(
409 userId: number,
410 newEmail: string,
411): 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(
428 token: string,
429): { userId: number; newEmail: string } | null {
430 const now = Math.floor(Date.now() / 1000);
431
432 const result = db
433 .query<{ user_id: number; new_email: string }, [string, number]>(
434 "SELECT user_id, new_email FROM email_change_tokens WHERE token = ? AND expires_at > ?",
435 )
436 .get(token, now);
437
438 if (!result) return null;
439
440 return { userId: result.user_id, newEmail: result.new_email };
441}
442
443export function consumeEmailChangeToken(token: string): void {
444 db.run("DELETE FROM email_change_tokens WHERE token = ?", [token]);
445}
446
447export function isUserAdmin(userId: number): boolean {
448 const result = db
449 .query<{ role: UserRole }, [number]>("SELECT role FROM users WHERE id = ?")
450 .get(userId);
451
452 return result?.role === "admin";
453}
454
455export function updateUserRole(userId: number, role: UserRole): void {
456 db.run("UPDATE users SET role = ? WHERE id = ?", [role, userId]);
457}
458
459export function getAllUsers(): Array<{
460 id: number;
461 email: string;
462 name: string | null;
463 avatar: string;
464 created_at: number;
465 role: UserRole;
466}> {
467 return db
468 .query<
469 {
470 id: number;
471 email: string;
472 name: string | null;
473 avatar: string;
474 created_at: number;
475 role: UserRole;
476 last_login: number | null;
477 },
478 []
479 >(
480 "SELECT id, email, name, avatar, created_at, role, last_login FROM users ORDER BY created_at DESC",
481 )
482 .all();
483}
484
485export function getAllTranscriptions(
486 limit = 50,
487 cursor?: string,
488): {
489 data: Array<{
490 id: string;
491 user_id: number;
492 user_email: string;
493 user_name: string | null;
494 original_filename: string;
495 status: string;
496 created_at: number;
497 error_message: string | null;
498 }>;
499 pagination: {
500 limit: number;
501 hasMore: boolean;
502 nextCursor: string | null;
503 };
504} {
505 type TranscriptionRow = {
506 id: string;
507 user_id: number;
508 user_email: string;
509 user_name: string | null;
510 original_filename: string;
511 status: string;
512 created_at: number;
513 error_message: string | null;
514 };
515
516 let transcriptions: TranscriptionRow[];
517
518 if (cursor) {
519 const { decodeCursor } = require("./cursor");
520 const parts = decodeCursor(cursor);
521
522 if (parts.length !== 2) {
523 throw new Error("Invalid cursor format");
524 }
525
526 const cursorTime = Number.parseInt(parts[0] || "", 10);
527 const id = parts[1] || "";
528
529 if (Number.isNaN(cursorTime) || !id) {
530 throw new Error("Invalid cursor format");
531 }
532
533 transcriptions = db
534 .query<TranscriptionRow, [number, number, string, number]>(
535 `SELECT
536 t.id,
537 t.user_id,
538 u.email as user_email,
539 u.name as user_name,
540 t.original_filename,
541 t.status,
542 t.created_at,
543 t.error_message
544 FROM transcriptions t
545 LEFT JOIN users u ON t.user_id = u.id
546 WHERE t.created_at < ? OR (t.created_at = ? AND t.id < ?)
547 ORDER BY t.created_at DESC, t.id DESC
548 LIMIT ?`,
549 )
550 .all(cursorTime, cursorTime, id, limit + 1);
551 } else {
552 transcriptions = db
553 .query<TranscriptionRow, [number]>(
554 `SELECT
555 t.id,
556 t.user_id,
557 u.email as user_email,
558 u.name as user_name,
559 t.original_filename,
560 t.status,
561 t.created_at,
562 t.error_message
563 FROM transcriptions t
564 LEFT JOIN users u ON t.user_id = u.id
565 ORDER BY t.created_at DESC, t.id DESC
566 LIMIT ?`,
567 )
568 .all(limit + 1);
569 }
570
571 const hasMore = transcriptions.length > limit;
572 if (hasMore) {
573 transcriptions.pop();
574 }
575
576 let nextCursor: string | null = null;
577 if (hasMore && transcriptions.length > 0) {
578 const { encodeCursor } = require("./cursor");
579 const last = transcriptions[transcriptions.length - 1];
580 if (last) {
581 nextCursor = encodeCursor([last.created_at.toString(), last.id]);
582 }
583 }
584
585 return {
586 data: transcriptions,
587 pagination: {
588 limit,
589 hasMore,
590 nextCursor,
591 },
592 };
593}
594
595export function deleteTranscription(transcriptionId: string): void {
596 const transcription = db
597 .query<{ id: string; filename: string }, [string]>(
598 "SELECT id, filename FROM transcriptions WHERE id = ?",
599 )
600 .get(transcriptionId);
601
602 if (!transcription) {
603 throw new Error("Transcription not found");
604 }
605
606 // Delete database record
607 db.run("DELETE FROM transcriptions WHERE id = ?", [transcriptionId]);
608
609 // Delete files (audio file and transcript files)
610 try {
611 const audioPath = `./uploads/${transcription.filename}`;
612 const transcriptPath = `./transcripts/${transcriptionId}.txt`;
613 const vttPath = `./transcripts/${transcriptionId}.vtt`;
614
615 if (Bun.file(audioPath).size) {
616 Bun.write(audioPath, "").then(() => {
617 // File deleted by overwriting with empty content, then unlink
618 import("node:fs").then((fs) => {
619 fs.unlinkSync(audioPath);
620 });
621 });
622 }
623
624 if (Bun.file(transcriptPath).size) {
625 import("node:fs").then((fs) => {
626 fs.unlinkSync(transcriptPath);
627 });
628 }
629
630 if (Bun.file(vttPath).size) {
631 import("node:fs").then((fs) => {
632 fs.unlinkSync(vttPath);
633 });
634 }
635 } catch {
636 // Files might not exist, ignore errors
637 }
638}
639
640export function getSessionsForUser(userId: number): Session[] {
641 const now = Math.floor(Date.now() / 1000);
642 return db
643 .query<Session, [number, number]>(
644 "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",
645 )
646 .all(userId, now);
647}
648
649export function deleteSessionById(sessionId: string, userId: number): boolean {
650 const result = db.run("DELETE FROM sessions WHERE id = ? AND user_id = ?", [
651 sessionId,
652 userId,
653 ]);
654 return result.changes > 0;
655}
656
657export function deleteAllUserSessions(userId: number): void {
658 db.run("DELETE FROM sessions WHERE user_id = ?", [userId]);
659}
660
661export function updateUserEmailAddress(userId: number, newEmail: string): void {
662 db.run("UPDATE users SET email = ? WHERE id = ?", [newEmail, userId]);
663}
664
665export interface UserWithStats {
666 id: number;
667 email: string;
668 name: string | null;
669 avatar: string;
670 created_at: number;
671 role: UserRole;
672 last_login: number | null;
673 transcription_count: number;
674 subscription_status: string | null;
675 subscription_id: string | null;
676}
677
678export function getAllUsersWithStats(
679 limit = 50,
680 cursor?: string,
681): {
682 data: UserWithStats[];
683 pagination: {
684 limit: number;
685 hasMore: boolean;
686 nextCursor: string | null;
687 };
688} {
689 let users: UserWithStats[];
690
691 if (cursor) {
692 const { decodeCursor } = require("./cursor");
693 const parts = decodeCursor(cursor);
694
695 if (parts.length !== 2) {
696 throw new Error("Invalid cursor format");
697 }
698
699 const cursorTime = Number.parseInt(parts[0] || "", 10);
700 const cursorId = Number.parseInt(parts[1] || "", 10);
701
702 if (Number.isNaN(cursorTime) || Number.isNaN(cursorId)) {
703 throw new Error("Invalid cursor format");
704 }
705
706 users = db
707 .query<UserWithStats, [number, number, number, number]>(
708 `SELECT
709 u.id,
710 u.email,
711 u.name,
712 u.avatar,
713 u.created_at,
714 u.role,
715 u.last_login,
716 COUNT(DISTINCT t.id) as transcription_count,
717 s.status as subscription_status,
718 s.id as subscription_id
719 FROM users u
720 LEFT JOIN transcriptions t ON u.id = t.user_id
721 LEFT JOIN subscriptions s ON u.id = s.user_id AND s.status IN ('active', 'trialing', 'past_due')
722 WHERE u.created_at < ? OR (u.created_at = ? AND u.id < ?)
723 GROUP BY u.id
724 ORDER BY u.created_at DESC, u.id DESC
725 LIMIT ?`,
726 )
727 .all(cursorTime, cursorTime, cursorId, limit + 1);
728 } else {
729 users = db
730 .query<UserWithStats, [number]>(
731 `SELECT
732 u.id,
733 u.email,
734 u.name,
735 u.avatar,
736 u.created_at,
737 u.role,
738 u.last_login,
739 COUNT(DISTINCT t.id) as transcription_count,
740 s.status as subscription_status,
741 s.id as subscription_id
742 FROM users u
743 LEFT JOIN transcriptions t ON u.id = t.user_id
744 LEFT JOIN subscriptions s ON u.id = s.user_id AND s.status IN ('active', 'trialing', 'past_due')
745 GROUP BY u.id
746 ORDER BY u.created_at DESC, u.id DESC
747 LIMIT ?`,
748 )
749 .all(limit + 1);
750 }
751
752 const hasMore = users.length > limit;
753 if (hasMore) {
754 users.pop();
755 }
756
757 let nextCursor: string | null = null;
758 if (hasMore && users.length > 0) {
759 const { encodeCursor } = require("./cursor");
760 const last = users[users.length - 1];
761 if (last) {
762 nextCursor = encodeCursor([last.created_at.toString(), last.id.toString()]);
763 }
764 }
765
766 return {
767 data: users,
768 pagination: {
769 limit,
770 hasMore,
771 nextCursor,
772 },
773 };
774}