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