馃 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}
15
16export interface Session {
17 id: string;
18 user_id: number;
19 ip_address: string | null;
20 user_agent: string | null;
21 created_at: number;
22 expires_at: number;
23}
24
25export function createSession(
26 userId: number,
27 ipAddress?: string,
28 userAgent?: string,
29): string {
30 const sessionId = crypto.randomUUID();
31 const expiresAt = Math.floor(Date.now() / 1000) + SESSION_DURATION;
32
33 db.run(
34 "INSERT INTO sessions (id, user_id, ip_address, user_agent, expires_at) VALUES (?, ?, ?, ?, ?)",
35 [sessionId, userId, ipAddress ?? null, userAgent ?? null, expiresAt],
36 );
37
38 return sessionId;
39}
40
41export function getSession(sessionId: string): Session | null {
42 const now = Math.floor(Date.now() / 1000);
43
44 const session = db
45 .query<Session, [string, number]>(
46 "SELECT id, user_id, ip_address, user_agent, created_at, expires_at FROM sessions WHERE id = ? AND expires_at > ?",
47 )
48 .get(sessionId, now);
49
50 return session ?? null;
51}
52
53export function getUserBySession(sessionId: string): User | null {
54 const session = getSession(sessionId);
55 if (!session) return null;
56
57 const user = db
58 .query<User, [number]>(
59 "SELECT id, email, name, avatar, created_at, role FROM users WHERE id = ?",
60 )
61 .get(session.user_id);
62
63 return user ?? null;
64}
65
66export function deleteSession(sessionId: string): void {
67 db.run("DELETE FROM sessions WHERE id = ?", [sessionId]);
68}
69
70export function cleanupExpiredSessions(): void {
71 const now = Math.floor(Date.now() / 1000);
72 db.run("DELETE FROM sessions WHERE expires_at <= ?", [now]);
73}
74
75export async function createUser(
76 email: string,
77 password: string,
78 name?: string,
79): Promise<User> {
80 // Generate deterministic avatar from email
81 const encoder = new TextEncoder();
82 const data = encoder.encode(email.toLowerCase());
83 const hashBuffer = await crypto.subtle.digest("SHA-256", data);
84 const hashArray = Array.from(new Uint8Array(hashBuffer));
85 const avatar = hashArray
86 .map((b) => b.toString(16).padStart(2, "0"))
87 .join("")
88 .substring(0, 16);
89
90 const result = db.run(
91 "INSERT INTO users (email, password_hash, name, avatar) VALUES (?, ?, ?, ?)",
92 [email, password, name ?? null, avatar],
93 );
94
95 const user = db
96 .query<User, [number]>(
97 "SELECT id, email, name, avatar, created_at, role FROM users WHERE id = ?",
98 )
99 .get(Number(result.lastInsertRowid));
100
101 if (!user) {
102 throw new Error("Failed to create user");
103 }
104
105 return user;
106}
107
108export async function authenticateUser(
109 email: string,
110 password: string,
111): Promise<User | null> {
112 const result = db
113 .query<
114 {
115 id: number;
116 email: string;
117 name: string | null;
118 avatar: string;
119 password_hash: string;
120 created_at: number;
121 role: UserRole;
122 },
123 [string]
124 >(
125 "SELECT id, email, name, avatar, password_hash, created_at, role FROM users WHERE email = ?",
126 )
127 .get(email);
128
129 if (!result) {
130 // Dummy comparison to prevent timing-based account enumeration
131 const dummyHash = "0".repeat(64);
132 password === dummyHash;
133 return null;
134 }
135
136 if (password !== result.password_hash) return null;
137
138 return {
139 id: result.id,
140 email: result.email,
141 name: result.name,
142 avatar: result.avatar,
143 created_at: result.created_at,
144 role: result.role,
145 };
146}
147
148export function getUserSessionsForUser(userId: number): Session[] {
149 const now = Math.floor(Date.now() / 1000);
150
151 const sessions = db
152 .query<Session, [number, number]>(
153 "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",
154 )
155 .all(userId, now);
156
157 return sessions;
158}
159
160export function getSessionFromRequest(req: Request): string | null {
161 const cookie = req.headers.get("cookie");
162 if (!cookie) return null;
163
164 const match = cookie.match(/session=([^;]+)/);
165 return match?.[1] ?? null;
166}
167
168export function deleteUser(userId: number): void {
169 db.run("DELETE FROM users WHERE id = ?", [userId]);
170}
171
172export function updateUserEmail(userId: number, newEmail: string): void {
173 db.run("UPDATE users SET email = ? WHERE id = ?", [newEmail, userId]);
174}
175
176export function updateUserName(userId: number, newName: string): void {
177 db.run("UPDATE users SET name = ? WHERE id = ?", [newName, userId]);
178}
179
180export function updateUserAvatar(userId: number, avatar: string): void {
181 db.run("UPDATE users SET avatar = ? WHERE id = ?", [avatar, userId]);
182}
183
184export async function updateUserPassword(
185 userId: number,
186 newPassword: string,
187): Promise<void> {
188 db.run("UPDATE users SET password_hash = ? WHERE id = ?", [
189 newPassword,
190 userId,
191 ]);
192 db.run("DELETE FROM sessions WHERE user_id = ?", [userId]);
193}
194
195export function isUserAdmin(userId: number): boolean {
196 const result = db
197 .query<{ role: UserRole }, [number]>("SELECT role FROM users WHERE id = ?")
198 .get(userId);
199
200 return result?.role === "admin";
201}
202
203export function updateUserRole(userId: number, role: UserRole): void {
204 db.run("UPDATE users SET role = ? WHERE id = ?", [role, userId]);
205}
206
207export function getAllUsers(): Array<{
208 id: number;
209 email: string;
210 name: string | null;
211 avatar: string;
212 created_at: number;
213 role: UserRole;
214}> {
215 return db
216 .query<
217 {
218 id: number;
219 email: string;
220 name: string | null;
221 avatar: string;
222 created_at: number;
223 role: UserRole;
224 },
225 []
226 >("SELECT id, email, name, avatar, created_at, role FROM users ORDER BY created_at DESC")
227 .all();
228}
229
230export function getAllTranscriptions(): Array<{
231 id: string;
232 user_id: number;
233 user_email: string;
234 user_name: string | null;
235 original_filename: string;
236 status: string;
237 created_at: number;
238 error_message: string | null;
239}> {
240 return db
241 .query<
242 {
243 id: string;
244 user_id: number;
245 user_email: string;
246 user_name: string | null;
247 original_filename: string;
248 status: string;
249 created_at: number;
250 error_message: string | null;
251 },
252 []
253 >(
254 `SELECT
255 t.id,
256 t.user_id,
257 u.email as user_email,
258 u.name as user_name,
259 t.original_filename,
260 t.status,
261 t.created_at,
262 t.error_message
263 FROM transcriptions t
264 LEFT JOIN users u ON t.user_id = u.id
265 ORDER BY t.created_at DESC`,
266 )
267 .all();
268}
269
270export function deleteTranscription(transcriptionId: string): void {
271 const transcription = db
272 .query<{ id: string; filename: string }, [string]>(
273 "SELECT id, filename FROM transcriptions WHERE id = ?",
274 )
275 .get(transcriptionId);
276
277 if (!transcription) {
278 throw new Error("Transcription not found");
279 }
280
281 // Delete database record
282 db.run("DELETE FROM transcriptions WHERE id = ?", [transcriptionId]);
283
284 // Delete files (audio file and transcript files)
285 try {
286 const audioPath = `./uploads/${transcription.filename}`;
287 const transcriptPath = `./transcripts/${transcriptionId}.txt`;
288 const vttPath = `./transcripts/${transcriptionId}.vtt`;
289
290 if (Bun.file(audioPath).size) {
291 Bun.write(audioPath, "").then(() => {
292 // File deleted by overwriting with empty content, then unlink
293 import("node:fs").then((fs) => {
294 fs.unlinkSync(audioPath);
295 });
296 });
297 }
298
299 if (Bun.file(transcriptPath).size) {
300 import("node:fs").then((fs) => {
301 fs.unlinkSync(transcriptPath);
302 });
303 }
304
305 if (Bun.file(vttPath).size) {
306 import("node:fs").then((fs) => {
307 fs.unlinkSync(vttPath);
308 });
309 }
310 } catch {
311 // Files might not exist, ignore errors
312 }
313}