馃 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 version: 2,
139 name: "Add section column to classes table",
140 sql: `
141 ALTER TABLE classes ADD COLUMN section TEXT;
142 CREATE INDEX IF NOT EXISTS idx_classes_course_code ON classes(course_code);
143 `,
144 },
145 {
146 version: 3,
147 name: "Add class waitlist table",
148 sql: `
149 CREATE TABLE IF NOT EXISTS class_waitlist (
150 id TEXT PRIMARY KEY,
151 user_id INTEGER NOT NULL,
152 course_code TEXT NOT NULL,
153 course_name TEXT NOT NULL,
154 professor TEXT NOT NULL,
155 section TEXT,
156 semester TEXT NOT NULL,
157 year INTEGER NOT NULL,
158 additional_info TEXT,
159 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
160 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
161 );
162
163 CREATE INDEX IF NOT EXISTS idx_waitlist_user_id ON class_waitlist(user_id);
164 CREATE INDEX IF NOT EXISTS idx_waitlist_course_code ON class_waitlist(course_code);
165 `,
166 },
167 {
168 version: 4,
169 name: "Add meeting_times to class_waitlist",
170 sql: `
171 ALTER TABLE class_waitlist ADD COLUMN meeting_times TEXT;
172 `,
173 },
174 {
175 version: 5,
176 name: "Remove section columns",
177 sql: `
178 DROP INDEX IF EXISTS idx_classes_section;
179 ALTER TABLE classes DROP COLUMN section;
180 ALTER TABLE class_waitlist DROP COLUMN section;
181 `,
182 },
183 {
184 version: 6,
185 name: "Add subscriptions table for Polar integration",
186 sql: `
187 -- Subscriptions table
188 CREATE TABLE IF NOT EXISTS subscriptions (
189 id TEXT PRIMARY KEY,
190 user_id INTEGER NOT NULL,
191 customer_id TEXT NOT NULL,
192 status TEXT NOT NULL,
193 current_period_start INTEGER,
194 current_period_end INTEGER,
195 cancel_at_period_end BOOLEAN DEFAULT 0,
196 canceled_at INTEGER,
197 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
198 updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
199 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
200 );
201
202 CREATE INDEX IF NOT EXISTS idx_subscriptions_user_id ON subscriptions(user_id);
203 CREATE INDEX IF NOT EXISTS idx_subscriptions_status ON subscriptions(status);
204 CREATE INDEX IF NOT EXISTS idx_subscriptions_customer_id ON subscriptions(customer_id);
205 `,
206 },
207 {
208 version: 7,
209 name: "Create ghost user for deleted accounts",
210 sql: `
211 -- Create a ghost user account for orphaned transcriptions
212 INSERT OR IGNORE INTO users (id, email, password_hash, name, avatar, role, created_at)
213 VALUES (0, 'ghosty@thistle.internal', NULL, 'Ghosty', '馃懟', 'user', strftime('%s', 'now'));
214 `,
215 },
216 {
217 version: 8,
218 name: "Add email verification system",
219 sql: `
220 -- Add email verification flag to users
221 ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT 0;
222
223 -- Email verification tokens table
224 CREATE TABLE IF NOT EXISTS email_verification_tokens (
225 id TEXT PRIMARY KEY,
226 user_id INTEGER NOT NULL,
227 token TEXT NOT NULL UNIQUE,
228 expires_at INTEGER NOT NULL,
229 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
230 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
231 );
232
233 CREATE INDEX IF NOT EXISTS idx_verification_tokens_user_id ON email_verification_tokens(user_id);
234 CREATE INDEX IF NOT EXISTS idx_verification_tokens_token ON email_verification_tokens(token);
235
236 -- Password reset tokens table
237 CREATE TABLE IF NOT EXISTS password_reset_tokens (
238 id TEXT PRIMARY KEY,
239 user_id INTEGER NOT NULL,
240 token TEXT NOT NULL UNIQUE,
241 expires_at INTEGER NOT NULL,
242 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
243 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
244 );
245
246 CREATE INDEX IF NOT EXISTS idx_password_reset_tokens_user_id ON password_reset_tokens(user_id);
247 CREATE INDEX IF NOT EXISTS idx_password_reset_tokens_token ON password_reset_tokens(token);
248 `,
249 },
250 {
251 version: 4,
252 name: "Add email notification preferences",
253 sql: `
254 ALTER TABLE users ADD COLUMN email_notifications_enabled BOOLEAN DEFAULT 1;
255 `,
256 },
257];
258
259function getCurrentVersion(): number {
260 const result = db
261 .query<{ version: number }, []>(
262 "SELECT MAX(version) as version FROM schema_migrations",
263 )
264 .get();
265 return result?.version ?? 0;
266}
267
268function applyMigration(
269 version: number,
270 sql: string,
271 index: number,
272 total: number,
273) {
274 const current = getCurrentVersion();
275 if (current >= version) return;
276
277 const isTTY = typeof process !== "undefined" && process.stdout?.isTTY;
278 const startMsg = `Applying migration ${index + 1} of ${total}`;
279
280 if (isTTY) {
281 process.stdout.write(`${startMsg}...`);
282 const start = performance.now();
283 db.run(sql);
284 db.run("INSERT INTO schema_migrations (version) VALUES (?)", [version]);
285 const duration = Math.round(performance.now() - start);
286 process.stdout.write(`\r${startMsg} (${duration}ms)\n`);
287 } else {
288 console.log(startMsg);
289 db.run(sql);
290 db.run("INSERT INTO schema_migrations (version) VALUES (?)", [version]);
291 }
292}
293
294// Apply all migrations
295const current = getCurrentVersion();
296const pending = migrations.filter((m) => m.version > current);
297
298for (const [index, migration] of pending.entries()) {
299 applyMigration(migration.version, migration.sql, index, pending.length);
300}
301
302export default db;