馃 distributed transcription service
thistle.dunkirk.sh
dunkirk.sh
1import { Database } from "bun:sqlite";
2
3export const db = new Database("thistle.db");
4
5// Schema version tracking
6db.run(`
7 CREATE TABLE IF NOT EXISTS schema_migrations (
8 version INTEGER PRIMARY KEY,
9 applied_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
10 )
11`);
12
13const migrations = [
14 {
15 version: 1,
16 name: "Complete schema with class system",
17 sql: `
18 -- Users table
19 CREATE TABLE IF NOT EXISTS users (
20 id INTEGER PRIMARY KEY AUTOINCREMENT,
21 email TEXT UNIQUE NOT NULL,
22 password_hash TEXT,
23 name TEXT,
24 avatar TEXT DEFAULT 'd',
25 role TEXT NOT NULL DEFAULT 'user',
26 last_login INTEGER,
27 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
28 );
29
30 CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);
31 CREATE INDEX IF NOT EXISTS idx_users_last_login ON users(last_login);
32
33 -- Sessions table
34 CREATE TABLE IF NOT EXISTS sessions (
35 id TEXT PRIMARY KEY,
36 user_id INTEGER NOT NULL,
37 ip_address TEXT,
38 user_agent TEXT,
39 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
40 expires_at INTEGER NOT NULL,
41 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
42 );
43
44 CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id);
45 CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions(expires_at);
46
47 -- Passkeys table
48 CREATE TABLE IF NOT EXISTS passkeys (
49 id TEXT PRIMARY KEY,
50 user_id INTEGER NOT NULL,
51 credential_id TEXT NOT NULL UNIQUE,
52 public_key TEXT NOT NULL,
53 counter INTEGER NOT NULL DEFAULT 0,
54 transports TEXT,
55 name TEXT,
56 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
57 last_used_at INTEGER,
58 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
59 );
60
61 CREATE INDEX IF NOT EXISTS idx_passkeys_user_id ON passkeys(user_id);
62 CREATE INDEX IF NOT EXISTS idx_passkeys_credential_id ON passkeys(credential_id);
63
64 -- Rate limiting table
65 CREATE TABLE IF NOT EXISTS rate_limit_attempts (
66 id INTEGER PRIMARY KEY AUTOINCREMENT,
67 key TEXT NOT NULL,
68 timestamp INTEGER NOT NULL
69 );
70
71 CREATE INDEX IF NOT EXISTS idx_rate_limit_key_timestamp ON rate_limit_attempts(key, timestamp);
72
73 -- Classes table
74 CREATE TABLE IF NOT EXISTS classes (
75 id TEXT PRIMARY KEY,
76 course_code TEXT NOT NULL,
77 name TEXT NOT NULL,
78 professor TEXT NOT NULL,
79 semester TEXT NOT NULL,
80 year INTEGER NOT NULL,
81 archived BOOLEAN DEFAULT 0,
82 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
83 );
84
85 CREATE INDEX IF NOT EXISTS idx_classes_semester_year ON classes(semester, year);
86 CREATE INDEX IF NOT EXISTS idx_classes_archived ON classes(archived);
87
88 -- Class members table
89 CREATE TABLE IF NOT EXISTS class_members (
90 class_id TEXT NOT NULL,
91 user_id INTEGER NOT NULL,
92 enrolled_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
93 PRIMARY KEY (class_id, user_id),
94 FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
95 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
96 );
97
98 CREATE INDEX IF NOT EXISTS idx_class_members_user_id ON class_members(user_id);
99 CREATE INDEX IF NOT EXISTS idx_class_members_class_id ON class_members(class_id);
100
101 -- Meeting times table
102 CREATE TABLE IF NOT EXISTS meeting_times (
103 id TEXT PRIMARY KEY,
104 class_id TEXT NOT NULL,
105 label TEXT NOT NULL,
106 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
107 FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE
108 );
109
110 CREATE INDEX IF NOT EXISTS idx_meeting_times_class_id ON meeting_times(class_id);
111
112 -- Transcriptions table
113 CREATE TABLE IF NOT EXISTS transcriptions (
114 id TEXT PRIMARY KEY,
115 user_id INTEGER NOT NULL,
116 class_id TEXT,
117 meeting_time_id TEXT,
118 filename TEXT NOT NULL,
119 original_filename TEXT NOT NULL,
120 status TEXT NOT NULL DEFAULT 'pending',
121 progress INTEGER NOT NULL DEFAULT 0,
122 error_message TEXT,
123 whisper_job_id TEXT,
124 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
125 updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
126 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
127 FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
128 FOREIGN KEY (meeting_time_id) REFERENCES meeting_times(id) ON DELETE SET NULL
129 );
130
131 CREATE INDEX IF NOT EXISTS idx_transcriptions_user_id ON transcriptions(user_id);
132 CREATE INDEX IF NOT EXISTS idx_transcriptions_class_id ON transcriptions(class_id);
133 CREATE INDEX IF NOT EXISTS idx_transcriptions_status ON transcriptions(status);
134 CREATE INDEX IF NOT EXISTS idx_transcriptions_whisper_job_id ON transcriptions(whisper_job_id);
135 `,
136 },
137];
138
139function getCurrentVersion(): number {
140 const result = db
141 .query<{ version: number }, []>(
142 "SELECT MAX(version) as version FROM schema_migrations",
143 )
144 .get();
145 return result?.version ?? 0;
146}
147
148function applyMigration(
149 version: number,
150 sql: string,
151 index: number,
152 total: number,
153) {
154 const current = getCurrentVersion();
155 if (current >= version) return;
156
157 const isTTY = typeof process !== "undefined" && process.stdout?.isTTY;
158 const startMsg = `Applying migration ${index + 1} of ${total}`;
159
160 if (isTTY) {
161 process.stdout.write(`${startMsg}...`);
162 const start = performance.now();
163 db.run(sql);
164 db.run("INSERT INTO schema_migrations (version) VALUES (?)", [version]);
165 const duration = Math.round(performance.now() - start);
166 process.stdout.write(`\r${startMsg} (${duration}ms)\n`);
167 } else {
168 console.log(startMsg);
169 db.run(sql);
170 db.run("INSERT INTO schema_migrations (version) VALUES (?)", [version]);
171 }
172}
173
174// Apply all migrations
175const current = getCurrentVersion();
176const pending = migrations.filter((m) => m.version > current);
177
178for (const [index, migration] of pending.entries()) {
179 applyMigration(migration.version, migration.sql, index, pending.length);
180}
181
182export default db;