馃 distributed transcription service
thistle.dunkirk.sh
dunkirk.sh
1import { Database } from "bun:sqlite";
2
3// Use test database when NODE_ENV is test
4const dbPath =
5 process.env.NODE_ENV === "test" ? "thistle.test.db" : "thistle.db";
6export const db = new Database(dbPath);
7
8console.log(`[Database] Using database: ${dbPath}`);
9
10// Schema version tracking
11db.run(`
12 CREATE TABLE IF NOT EXISTS schema_migrations (
13 version INTEGER PRIMARY KEY,
14 applied_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
15 )
16`);
17
18const migrations = [
19 {
20 version: 1,
21 name: "Initial schema with all tables and constraints",
22 sql: `
23 -- Users table
24 CREATE TABLE IF NOT EXISTS users (
25 id INTEGER PRIMARY KEY AUTOINCREMENT,
26 email TEXT UNIQUE NOT NULL,
27 password_hash TEXT,
28 name TEXT,
29 avatar TEXT DEFAULT 'd',
30 role TEXT NOT NULL DEFAULT 'user',
31 last_login INTEGER,
32 email_verified BOOLEAN DEFAULT 0,
33 email_notifications_enabled BOOLEAN DEFAULT 1,
34 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
35 );
36
37 CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);
38 CREATE INDEX IF NOT EXISTS idx_users_last_login ON users(last_login);
39 CREATE INDEX IF NOT EXISTS idx_users_email_verified ON users(email_verified);
40
41 -- Sessions table
42 CREATE TABLE IF NOT EXISTS sessions (
43 id TEXT PRIMARY KEY,
44 user_id INTEGER NOT NULL,
45 ip_address TEXT,
46 user_agent TEXT,
47 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
48 expires_at INTEGER NOT NULL,
49 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
50 );
51
52 CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id);
53 CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions(expires_at);
54
55 -- Passkeys table
56 CREATE TABLE IF NOT EXISTS passkeys (
57 id TEXT PRIMARY KEY,
58 user_id INTEGER NOT NULL,
59 credential_id TEXT NOT NULL UNIQUE,
60 public_key TEXT NOT NULL,
61 counter INTEGER NOT NULL DEFAULT 0,
62 transports TEXT,
63 name TEXT,
64 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
65 last_used_at INTEGER,
66 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
67 );
68
69 CREATE INDEX IF NOT EXISTS idx_passkeys_user_id ON passkeys(user_id);
70 CREATE INDEX IF NOT EXISTS idx_passkeys_credential_id ON passkeys(credential_id);
71
72 -- Rate limiting table
73 CREATE TABLE IF NOT EXISTS rate_limit_attempts (
74 id INTEGER PRIMARY KEY AUTOINCREMENT,
75 key TEXT NOT NULL,
76 timestamp INTEGER NOT NULL
77 );
78
79 CREATE INDEX IF NOT EXISTS idx_rate_limit_key_timestamp ON rate_limit_attempts(key, timestamp);
80
81 -- Classes table
82 CREATE TABLE IF NOT EXISTS classes (
83 id TEXT PRIMARY KEY,
84 course_code TEXT NOT NULL,
85 name TEXT NOT NULL,
86 professor TEXT NOT NULL,
87 semester TEXT NOT NULL,
88 year INTEGER NOT NULL,
89 archived BOOLEAN DEFAULT 0,
90 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
91 );
92
93 CREATE INDEX IF NOT EXISTS idx_classes_semester_year ON classes(semester, year);
94 CREATE INDEX IF NOT EXISTS idx_classes_archived ON classes(archived);
95 CREATE INDEX IF NOT EXISTS idx_classes_course_code ON classes(course_code);
96
97 -- Class members table
98 CREATE TABLE IF NOT EXISTS class_members (
99 class_id TEXT NOT NULL,
100 user_id INTEGER NOT NULL,
101 enrolled_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
102 PRIMARY KEY (class_id, user_id),
103 FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
104 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
105 );
106
107 CREATE INDEX IF NOT EXISTS idx_class_members_user_id ON class_members(user_id);
108 CREATE INDEX IF NOT EXISTS idx_class_members_class_id ON class_members(class_id);
109
110 -- Meeting times table
111 CREATE TABLE IF NOT EXISTS meeting_times (
112 id TEXT PRIMARY KEY,
113 class_id TEXT NOT NULL,
114 label TEXT NOT NULL,
115 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
116 FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE
117 );
118
119 CREATE INDEX IF NOT EXISTS idx_meeting_times_class_id ON meeting_times(class_id);
120
121 -- Transcriptions table
122 CREATE TABLE IF NOT EXISTS transcriptions (
123 id TEXT PRIMARY KEY,
124 user_id INTEGER NOT NULL,
125 class_id TEXT,
126 meeting_time_id TEXT,
127 filename TEXT NOT NULL,
128 original_filename TEXT NOT NULL,
129 status TEXT NOT NULL DEFAULT 'pending',
130 progress INTEGER NOT NULL DEFAULT 0,
131 error_message TEXT,
132 whisper_job_id TEXT,
133 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
134 updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
135 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
136 FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
137 FOREIGN KEY (meeting_time_id) REFERENCES meeting_times(id) ON DELETE SET NULL
138 );
139
140 CREATE INDEX IF NOT EXISTS idx_transcriptions_user_id ON transcriptions(user_id);
141 CREATE INDEX IF NOT EXISTS idx_transcriptions_class_id ON transcriptions(class_id);
142 CREATE INDEX IF NOT EXISTS idx_transcriptions_status ON transcriptions(status);
143 CREATE INDEX IF NOT EXISTS idx_transcriptions_whisper_job_id ON transcriptions(whisper_job_id);
144 CREATE INDEX IF NOT EXISTS idx_transcriptions_meeting_time_id ON transcriptions(meeting_time_id);
145
146 -- Class waitlist table
147 CREATE TABLE IF NOT EXISTS class_waitlist (
148 id TEXT PRIMARY KEY,
149 user_id INTEGER NOT NULL,
150 course_code TEXT NOT NULL,
151 course_name TEXT NOT NULL,
152 professor TEXT NOT NULL,
153 semester TEXT NOT NULL,
154 year INTEGER NOT NULL,
155 meeting_times TEXT,
156 additional_info TEXT,
157 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
158 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
159 );
160
161 CREATE INDEX IF NOT EXISTS idx_waitlist_user_id ON class_waitlist(user_id);
162 CREATE INDEX IF NOT EXISTS idx_waitlist_course_code ON class_waitlist(course_code);
163
164 -- Subscriptions table
165 CREATE TABLE IF NOT EXISTS subscriptions (
166 id TEXT PRIMARY KEY,
167 user_id INTEGER NOT NULL,
168 customer_id TEXT NOT NULL,
169 status TEXT NOT NULL,
170 current_period_start INTEGER,
171 current_period_end INTEGER,
172 cancel_at_period_end BOOLEAN DEFAULT 0,
173 canceled_at INTEGER,
174 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
175 updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
176 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
177 );
178
179 CREATE INDEX IF NOT EXISTS idx_subscriptions_user_id ON subscriptions(user_id);
180 CREATE INDEX IF NOT EXISTS idx_subscriptions_status ON subscriptions(status);
181 CREATE INDEX IF NOT EXISTS idx_subscriptions_customer_id ON subscriptions(customer_id);
182
183 -- Email verification tokens table
184 CREATE TABLE IF NOT EXISTS email_verification_tokens (
185 id TEXT PRIMARY KEY,
186 user_id INTEGER NOT NULL,
187 token TEXT NOT NULL UNIQUE,
188 expires_at INTEGER NOT NULL,
189 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
190 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
191 );
192
193 CREATE INDEX IF NOT EXISTS idx_verification_tokens_user_id ON email_verification_tokens(user_id);
194 CREATE INDEX IF NOT EXISTS idx_verification_tokens_token ON email_verification_tokens(token);
195
196 -- Password reset tokens table
197 CREATE TABLE IF NOT EXISTS password_reset_tokens (
198 id TEXT PRIMARY KEY,
199 user_id INTEGER NOT NULL,
200 token TEXT NOT NULL UNIQUE,
201 expires_at INTEGER NOT NULL,
202 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
203 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
204 );
205
206 CREATE INDEX IF NOT EXISTS idx_password_reset_tokens_user_id ON password_reset_tokens(user_id);
207 CREATE INDEX IF NOT EXISTS idx_password_reset_tokens_token ON password_reset_tokens(token);
208
209 -- Email change tokens table
210 CREATE TABLE IF NOT EXISTS email_change_tokens (
211 id TEXT PRIMARY KEY,
212 user_id INTEGER NOT NULL,
213 new_email TEXT NOT NULL,
214 token TEXT NOT NULL UNIQUE,
215 expires_at INTEGER NOT NULL,
216 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
217 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
218 );
219
220 CREATE INDEX IF NOT EXISTS idx_email_change_tokens_user_id ON email_change_tokens(user_id);
221 CREATE INDEX IF NOT EXISTS idx_email_change_tokens_token ON email_change_tokens(token);
222
223 -- Create ghost user for deleted accounts
224 INSERT OR IGNORE INTO users (id, email, password_hash, name, avatar, role, created_at)
225 VALUES (0, 'ghosty@thistle.internal', NULL, 'Ghosty', '馃懟', 'user', strftime('%s', 'now'));
226 `,
227 },
228 {
229 version: 2,
230 name: "Add sections support to classes and class members",
231 sql: `
232 -- Add section_number to classes (nullable for existing classes)
233 ALTER TABLE classes ADD COLUMN section_number TEXT;
234
235 -- Add section_id to class_members (nullable - NULL means default section)
236 ALTER TABLE class_members ADD COLUMN section_id TEXT;
237
238 -- Create sections table to track all available sections for a class
239 CREATE TABLE IF NOT EXISTS class_sections (
240 id TEXT PRIMARY KEY,
241 class_id TEXT NOT NULL,
242 section_number TEXT NOT NULL,
243 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
244 FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
245 UNIQUE(class_id, section_number)
246 );
247
248 CREATE INDEX IF NOT EXISTS idx_class_sections_class_id ON class_sections(class_id);
249
250 -- Add section_id to transcriptions to track which section uploaded it
251 ALTER TABLE transcriptions ADD COLUMN section_id TEXT;
252
253 CREATE INDEX IF NOT EXISTS idx_transcriptions_section_id ON transcriptions(section_id);
254 `,
255 },
256 {
257 version: 3,
258 name: "Add voting system for collaborative recording selection",
259 sql: `
260 -- Add vote count to transcriptions
261 ALTER TABLE transcriptions ADD COLUMN vote_count INTEGER NOT NULL DEFAULT 0;
262
263 -- Add auto-submitted flag to track if transcription was auto-selected
264 ALTER TABLE transcriptions ADD COLUMN auto_submitted BOOLEAN DEFAULT 0;
265
266 -- Create votes table to track who voted for which recording
267 CREATE TABLE IF NOT EXISTS recording_votes (
268 id TEXT PRIMARY KEY,
269 transcription_id TEXT NOT NULL,
270 user_id INTEGER NOT NULL,
271 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
272 FOREIGN KEY (transcription_id) REFERENCES transcriptions(id) ON DELETE CASCADE,
273 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
274 UNIQUE(transcription_id, user_id)
275 );
276
277 CREATE INDEX IF NOT EXISTS idx_recording_votes_transcription_id ON recording_votes(transcription_id);
278 CREATE INDEX IF NOT EXISTS idx_recording_votes_user_id ON recording_votes(user_id);
279 `,
280 },
281 {
282 version: 4,
283 name: "Add recording_date to transcriptions for chronological ordering",
284 sql: `
285 -- Add recording_date (timestamp when the recording was made, not uploaded)
286 -- Defaults to created_at for existing records
287 ALTER TABLE transcriptions ADD COLUMN recording_date INTEGER;
288
289 -- Set recording_date to created_at for existing records
290 UPDATE transcriptions SET recording_date = created_at WHERE recording_date IS NULL;
291
292 -- Create index for ordering by recording date
293 CREATE INDEX IF NOT EXISTS idx_transcriptions_recording_date ON transcriptions(recording_date);
294 `,
295 },
296];
297
298function getCurrentVersion(): number {
299 const result = db
300 .query<{ version: number }, []>(
301 "SELECT MAX(version) as version FROM schema_migrations",
302 )
303 .get();
304 return result?.version ?? 0;
305}
306
307function applyMigration(
308 version: number,
309 sql: string,
310 index: number,
311 total: number,
312) {
313 const current = getCurrentVersion();
314 if (current >= version) return;
315
316 const isTTY = typeof process !== "undefined" && process.stdout?.isTTY;
317 const startMsg = `Applying migration ${index + 1} of ${total}`;
318
319 if (isTTY) {
320 process.stdout.write(`${startMsg}...`);
321 const start = performance.now();
322 db.run(sql);
323 db.run("INSERT INTO schema_migrations (version) VALUES (?)", [version]);
324 const duration = Math.round(performance.now() - start);
325 process.stdout.write(`\r${startMsg} (${duration}ms)\n`);
326 } else {
327 console.log(startMsg);
328 db.run(sql);
329 db.run("INSERT INTO schema_migrations (version) VALUES (?)", [version]);
330 }
331}
332
333// Apply all migrations
334const current = getCurrentVersion();
335const pending = migrations.filter((m) => m.version > current);
336
337for (const [index, migration] of pending.entries()) {
338 applyMigration(migration.version, migration.sql, index, pending.length);
339}
340
341export default db;