馃 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 deleteSession(sessionId: string): void {
68 db.run("DELETE FROM sessions WHERE id = ?", [sessionId]);
69}
70
71export function cleanupExpiredSessions(): void {
72 const now = Math.floor(Date.now() / 1000);
73 db.run("DELETE FROM sessions WHERE expires_at <= ?", [now]);
74}
75
76export async function createUser(
77 email: string,
78 password: string,
79 name?: string,
80): Promise<User> {
81 // Generate deterministic avatar from email
82 const encoder = new TextEncoder();
83 const data = encoder.encode(email.toLowerCase());
84 const hashBuffer = await crypto.subtle.digest("SHA-256", data);
85 const hashArray = Array.from(new Uint8Array(hashBuffer));
86 const avatar = hashArray
87 .map((b) => b.toString(16).padStart(2, "0"))
88 .join("")
89 .substring(0, 16);
90
91 const result = db.run(
92 "INSERT INTO users (email, password_hash, name, avatar) VALUES (?, ?, ?, ?)",
93 [email, password, name ?? null, avatar],
94 );
95
96 const user = db
97 .query<User, [number]>(
98 "SELECT id, email, name, avatar, created_at, role, last_login FROM users WHERE id = ?",
99 )
100 .get(Number(result.lastInsertRowid));
101
102 if (!user) {
103 throw new Error("Failed to create user");
104 }
105
106 return user;
107}
108
109export async function authenticateUser(
110 email: string,
111 password: string,
112): Promise<User | null> {
113 const result = db
114 .query<
115 {
116 id: number;
117 email: string;
118 name: string | null;
119 avatar: string;
120 password_hash: string;
121 created_at: number;
122 role: UserRole;
123 last_login: number | null;
124 },
125 [string]
126 >(
127 "SELECT id, email, name, avatar, password_hash, created_at, role, last_login FROM users WHERE email = ?",
128 )
129 .get(email);
130
131 if (!result) {
132 // Dummy comparison to prevent timing-based account enumeration
133 const dummyHash = "0".repeat(64);
134 password === dummyHash;
135 return null;
136 }
137
138 if (password !== result.password_hash) return null;
139
140 // Update last_login
141 const now = Math.floor(Date.now() / 1000);
142 db.run("UPDATE users SET last_login = ? WHERE id = ?", [now, result.id]);
143
144 return {
145 id: result.id,
146 email: result.email,
147 name: result.name,
148 avatar: result.avatar,
149 created_at: result.created_at,
150 role: result.role,
151 last_login: now,
152 };
153}
154
155export function getUserSessionsForUser(userId: number): Session[] {
156 const now = Math.floor(Date.now() / 1000);
157
158 const sessions = db
159 .query<Session, [number, number]>(
160 "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",
161 )
162 .all(userId, now);
163
164 return sessions;
165}
166
167export function getSessionFromRequest(req: Request): string | null {
168 const cookie = req.headers.get("cookie");
169 if (!cookie) return null;
170
171 const match = cookie.match(/session=([^;]+)/);
172 return match?.[1] ?? null;
173}
174
175export function deleteUser(userId: number): void {
176 db.run("DELETE FROM users WHERE id = ?", [userId]);
177}
178
179export function updateUserEmail(userId: number, newEmail: string): void {
180 db.run("UPDATE users SET email = ? WHERE id = ?", [newEmail, userId]);
181}
182
183export function updateUserName(userId: number, newName: string): void {
184 db.run("UPDATE users SET name = ? WHERE id = ?", [newName, userId]);
185}
186
187export function updateUserAvatar(userId: number, avatar: string): void {
188 db.run("UPDATE users SET avatar = ? WHERE id = ?", [avatar, userId]);
189}
190
191export async function updateUserPassword(
192 userId: number,
193 newPassword: string,
194): Promise<void> {
195 db.run("UPDATE users SET password_hash = ? WHERE id = ?", [
196 newPassword,
197 userId,
198 ]);
199 db.run("DELETE FROM sessions WHERE user_id = ?", [userId]);
200}
201
202export function isUserAdmin(userId: number): boolean {
203 const result = db
204 .query<{ role: UserRole }, [number]>("SELECT role FROM users WHERE id = ?")
205 .get(userId);
206
207 return result?.role === "admin";
208}
209
210export function updateUserRole(userId: number, role: UserRole): void {
211 db.run("UPDATE users SET role = ? WHERE id = ?", [role, userId]);
212}
213
214export function getAllUsers(): Array<{
215 id: number;
216 email: string;
217 name: string | null;
218 avatar: string;
219 created_at: number;
220 role: UserRole;
221}> {
222 return db
223 .query<
224 {
225 id: number;
226 email: string;
227 name: string | null;
228 avatar: string;
229 created_at: number;
230 role: UserRole;
231 last_login: number | null;
232 },
233 []
234 >(
235 "SELECT id, email, name, avatar, created_at, role, last_login FROM users ORDER BY created_at DESC",
236 )
237 .all();
238}
239
240export function getAllTranscriptions(): Array<{
241 id: string;
242 user_id: number;
243 user_email: string;
244 user_name: string | null;
245 original_filename: string;
246 status: string;
247 created_at: number;
248 error_message: string | null;
249}> {
250 return db
251 .query<
252 {
253 id: string;
254 user_id: number;
255 user_email: string;
256 user_name: string | null;
257 original_filename: string;
258 status: string;
259 created_at: number;
260 error_message: string | null;
261 },
262 []
263 >(
264 `SELECT
265 t.id,
266 t.user_id,
267 u.email as user_email,
268 u.name as user_name,
269 t.original_filename,
270 t.status,
271 t.created_at,
272 t.error_message
273 FROM transcriptions t
274 LEFT JOIN users u ON t.user_id = u.id
275 ORDER BY t.created_at DESC`,
276 )
277 .all();
278}
279
280export function deleteTranscription(transcriptionId: string): void {
281 const transcription = db
282 .query<{ id: string; filename: string }, [string]>(
283 "SELECT id, filename FROM transcriptions WHERE id = ?",
284 )
285 .get(transcriptionId);
286
287 if (!transcription) {
288 throw new Error("Transcription not found");
289 }
290
291 // Delete database record
292 db.run("DELETE FROM transcriptions WHERE id = ?", [transcriptionId]);
293
294 // Delete files (audio file and transcript files)
295 try {
296 const audioPath = `./uploads/${transcription.filename}`;
297 const transcriptPath = `./transcripts/${transcriptionId}.txt`;
298 const vttPath = `./transcripts/${transcriptionId}.vtt`;
299
300 if (Bun.file(audioPath).size) {
301 Bun.write(audioPath, "").then(() => {
302 // File deleted by overwriting with empty content, then unlink
303 import("node:fs").then((fs) => {
304 fs.unlinkSync(audioPath);
305 });
306 });
307 }
308
309 if (Bun.file(transcriptPath).size) {
310 import("node:fs").then((fs) => {
311 fs.unlinkSync(transcriptPath);
312 });
313 }
314
315 if (Bun.file(vttPath).size) {
316 import("node:fs").then((fs) => {
317 fs.unlinkSync(vttPath);
318 });
319 }
320 } catch {
321 // Files might not exist, ignore errors
322 }
323}
324
325export function getSessionsForUser(userId: number): Session[] {
326 const now = Math.floor(Date.now() / 1000);
327 return db
328 .query<Session, [number, number]>(
329 "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",
330 )
331 .all(userId, now);
332}
333
334export function deleteSessionById(sessionId: string, userId: number): boolean {
335 const result = db.run("DELETE FROM sessions WHERE id = ? AND user_id = ?", [
336 sessionId,
337 userId,
338 ]);
339 return result.changes > 0;
340}
341
342export function deleteAllUserSessions(userId: number): void {
343 db.run("DELETE FROM sessions WHERE user_id = ?", [userId]);
344}
345
346export function updateUserEmailAddress(userId: number, newEmail: string): void {
347 db.run("UPDATE users SET email = ? WHERE id = ?", [newEmail, userId]);
348}
349
350export interface UserWithStats {
351 id: number;
352 email: string;
353 name: string | null;
354 avatar: string;
355 created_at: number;
356 role: UserRole;
357 last_login: number | null;
358 transcription_count: number;
359}
360
361export function getAllUsersWithStats(): UserWithStats[] {
362 return db
363 .query<UserWithStats, []>(
364 `SELECT
365 u.id,
366 u.email,
367 u.name,
368 u.avatar,
369 u.created_at,
370 u.role,
371 u.last_login,
372 COUNT(t.id) as transcription_count
373 FROM users u
374 LEFT JOIN transcriptions t ON u.id = t.user_id
375 GROUP BY u.id
376 ORDER BY u.created_at DESC`,
377 )
378 .all();
379}