馃 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 // Get user's subscription if they have one
187 const subscription = db
188 .query<{ id: string }, [number]>(
189 "SELECT id FROM subscriptions WHERE user_id = ? ORDER BY created_at DESC LIMIT 1",
190 )
191 .get(userId);
192
193 // Cancel subscription if it exists (soft cancel - keeps access until period end)
194 if (subscription) {
195 try {
196 const { polar } = await import("./polar");
197 await polar.subscriptions.update({
198 id: subscription.id,
199 subscriptionUpdate: { cancelAtPeriodEnd: true },
200 });
201 console.log(
202 `[User Delete] Canceled subscription ${subscription.id} for user ${userId}`,
203 );
204 } catch (error) {
205 console.error(
206 `[User Delete] Failed to cancel subscription ${subscription.id}:`,
207 error,
208 );
209 // Continue with user deletion even if subscription cancellation fails
210 }
211 }
212
213 db.run("DELETE FROM users WHERE id = ?", [userId]);
214}
215
216export function updateUserEmail(userId: number, newEmail: string): void {
217 db.run("UPDATE users SET email = ? WHERE id = ?", [newEmail, userId]);
218}
219
220export function updateUserName(userId: number, newName: string): void {
221 db.run("UPDATE users SET name = ? WHERE id = ?", [newName, userId]);
222}
223
224export function updateUserAvatar(userId: number, avatar: string): void {
225 db.run("UPDATE users SET avatar = ? WHERE id = ?", [avatar, userId]);
226}
227
228export async function updateUserPassword(
229 userId: number,
230 newPassword: string,
231): Promise<void> {
232 db.run("UPDATE users SET password_hash = ? WHERE id = ?", [
233 newPassword,
234 userId,
235 ]);
236 db.run("DELETE FROM sessions WHERE user_id = ?", [userId]);
237}
238
239export function isUserAdmin(userId: number): boolean {
240 const result = db
241 .query<{ role: UserRole }, [number]>("SELECT role FROM users WHERE id = ?")
242 .get(userId);
243
244 return result?.role === "admin";
245}
246
247export function updateUserRole(userId: number, role: UserRole): void {
248 db.run("UPDATE users SET role = ? WHERE id = ?", [role, userId]);
249}
250
251export function getAllUsers(): Array<{
252 id: number;
253 email: string;
254 name: string | null;
255 avatar: string;
256 created_at: number;
257 role: UserRole;
258}> {
259 return db
260 .query<
261 {
262 id: number;
263 email: string;
264 name: string | null;
265 avatar: string;
266 created_at: number;
267 role: UserRole;
268 last_login: number | null;
269 },
270 []
271 >(
272 "SELECT id, email, name, avatar, created_at, role, last_login FROM users ORDER BY created_at DESC",
273 )
274 .all();
275}
276
277export function getAllTranscriptions(): Array<{
278 id: string;
279 user_id: number;
280 user_email: string;
281 user_name: string | null;
282 original_filename: string;
283 status: string;
284 created_at: number;
285 error_message: string | null;
286}> {
287 return db
288 .query<
289 {
290 id: string;
291 user_id: number;
292 user_email: string;
293 user_name: string | null;
294 original_filename: string;
295 status: string;
296 created_at: number;
297 error_message: string | null;
298 },
299 []
300 >(
301 `SELECT
302 t.id,
303 t.user_id,
304 u.email as user_email,
305 u.name as user_name,
306 t.original_filename,
307 t.status,
308 t.created_at,
309 t.error_message
310 FROM transcriptions t
311 LEFT JOIN users u ON t.user_id = u.id
312 ORDER BY t.created_at DESC`,
313 )
314 .all();
315}
316
317export function deleteTranscription(transcriptionId: string): void {
318 const transcription = db
319 .query<{ id: string; filename: string }, [string]>(
320 "SELECT id, filename FROM transcriptions WHERE id = ?",
321 )
322 .get(transcriptionId);
323
324 if (!transcription) {
325 throw new Error("Transcription not found");
326 }
327
328 // Delete database record
329 db.run("DELETE FROM transcriptions WHERE id = ?", [transcriptionId]);
330
331 // Delete files (audio file and transcript files)
332 try {
333 const audioPath = `./uploads/${transcription.filename}`;
334 const transcriptPath = `./transcripts/${transcriptionId}.txt`;
335 const vttPath = `./transcripts/${transcriptionId}.vtt`;
336
337 if (Bun.file(audioPath).size) {
338 Bun.write(audioPath, "").then(() => {
339 // File deleted by overwriting with empty content, then unlink
340 import("node:fs").then((fs) => {
341 fs.unlinkSync(audioPath);
342 });
343 });
344 }
345
346 if (Bun.file(transcriptPath).size) {
347 import("node:fs").then((fs) => {
348 fs.unlinkSync(transcriptPath);
349 });
350 }
351
352 if (Bun.file(vttPath).size) {
353 import("node:fs").then((fs) => {
354 fs.unlinkSync(vttPath);
355 });
356 }
357 } catch {
358 // Files might not exist, ignore errors
359 }
360}
361
362export function getSessionsForUser(userId: number): Session[] {
363 const now = Math.floor(Date.now() / 1000);
364 return db
365 .query<Session, [number, number]>(
366 "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",
367 )
368 .all(userId, now);
369}
370
371export function deleteSessionById(sessionId: string, userId: number): boolean {
372 const result = db.run("DELETE FROM sessions WHERE id = ? AND user_id = ?", [
373 sessionId,
374 userId,
375 ]);
376 return result.changes > 0;
377}
378
379export function deleteAllUserSessions(userId: number): void {
380 db.run("DELETE FROM sessions WHERE user_id = ?", [userId]);
381}
382
383export function updateUserEmailAddress(userId: number, newEmail: string): void {
384 db.run("UPDATE users SET email = ? WHERE id = ?", [newEmail, userId]);
385}
386
387export interface UserWithStats {
388 id: number;
389 email: string;
390 name: string | null;
391 avatar: string;
392 created_at: number;
393 role: UserRole;
394 last_login: number | null;
395 transcription_count: number;
396 subscription_status: string | null;
397 subscription_id: string | null;
398}
399
400export function getAllUsersWithStats(): UserWithStats[] {
401 return db
402 .query<UserWithStats, []>(
403 `SELECT
404 u.id,
405 u.email,
406 u.name,
407 u.avatar,
408 u.created_at,
409 u.role,
410 u.last_login,
411 COUNT(DISTINCT t.id) as transcription_count,
412 s.status as subscription_status,
413 s.id as subscription_id
414 FROM users u
415 LEFT JOIN transcriptions t ON u.id = t.user_id
416 LEFT JOIN subscriptions s ON u.id = s.user_id AND s.status IN ('active', 'trialing', 'past_due')
417 GROUP BY u.id
418 ORDER BY u.created_at DESC`,
419 )
420 .all();
421}