馃 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
209function getCurrentVersion(): number {
210 const result = db
211 .query<{ version: number }, []>(
212 "SELECT MAX(version) as version FROM schema_migrations",
213 )
214 .get();
215 return result?.version ?? 0;
216}
217
218function applyMigration(
219 version: number,
220 sql: string,
221 index: number,
222 total: number,
223) {
224 const current = getCurrentVersion();
225 if (current >= version) return;
226
227 const isTTY = typeof process !== "undefined" && process.stdout?.isTTY;
228 const startMsg = `Applying migration ${index + 1} of ${total}`;
229
230 if (isTTY) {
231 process.stdout.write(`${startMsg}...`);
232 const start = performance.now();
233 db.run(sql);
234 db.run("INSERT INTO schema_migrations (version) VALUES (?)", [version]);
235 const duration = Math.round(performance.now() - start);
236 process.stdout.write(`\r${startMsg} (${duration}ms)\n`);
237 } else {
238 console.log(startMsg);
239 db.run(sql);
240 db.run("INSERT INTO schema_migrations (version) VALUES (?)", [version]);
241 }
242}
243
244// Apply all migrations
245const current = getCurrentVersion();
246const pending = migrations.filter((m) => m.version > current);
247
248for (const [index, migration] of pending.entries()) {
249 applyMigration(migration.version, migration.sql, index, pending.length);
250}
251
252export default db;