馃 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 = process.env.NODE_ENV === "test" ? "thistle.test.db" : "thistle.db";
5export const db = new Database(dbPath);
6
7console.log(`[Database] Using database: ${dbPath}`);
8
9// Schema version tracking
10db.run(`
11 CREATE TABLE IF NOT EXISTS schema_migrations (
12 version INTEGER PRIMARY KEY,
13 applied_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
14 )
15`);
16
17const migrations = [
18 {
19 version: 1,
20 name: "Initial schema with all tables and constraints",
21 sql: `
22 -- Users table
23 CREATE TABLE IF NOT EXISTS users (
24 id INTEGER PRIMARY KEY AUTOINCREMENT,
25 email TEXT UNIQUE NOT NULL,
26 password_hash TEXT,
27 name TEXT,
28 avatar TEXT DEFAULT 'd',
29 role TEXT NOT NULL DEFAULT 'user',
30 last_login INTEGER,
31 email_verified BOOLEAN DEFAULT 0,
32 email_notifications_enabled BOOLEAN DEFAULT 1,
33 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
34 );
35
36 CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);
37 CREATE INDEX IF NOT EXISTS idx_users_last_login ON users(last_login);
38 CREATE INDEX IF NOT EXISTS idx_users_email_verified ON users(email_verified);
39
40 -- Sessions table
41 CREATE TABLE IF NOT EXISTS sessions (
42 id TEXT PRIMARY KEY,
43 user_id INTEGER NOT NULL,
44 ip_address TEXT,
45 user_agent TEXT,
46 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
47 expires_at INTEGER NOT NULL,
48 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
49 );
50
51 CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id);
52 CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions(expires_at);
53
54 -- Passkeys table
55 CREATE TABLE IF NOT EXISTS passkeys (
56 id TEXT PRIMARY KEY,
57 user_id INTEGER NOT NULL,
58 credential_id TEXT NOT NULL UNIQUE,
59 public_key TEXT NOT NULL,
60 counter INTEGER NOT NULL DEFAULT 0,
61 transports TEXT,
62 name TEXT,
63 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
64 last_used_at INTEGER,
65 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
66 );
67
68 CREATE INDEX IF NOT EXISTS idx_passkeys_user_id ON passkeys(user_id);
69 CREATE INDEX IF NOT EXISTS idx_passkeys_credential_id ON passkeys(credential_id);
70
71 -- Rate limiting table
72 CREATE TABLE IF NOT EXISTS rate_limit_attempts (
73 id INTEGER PRIMARY KEY AUTOINCREMENT,
74 key TEXT NOT NULL,
75 timestamp INTEGER NOT NULL
76 );
77
78 CREATE INDEX IF NOT EXISTS idx_rate_limit_key_timestamp ON rate_limit_attempts(key, timestamp);
79
80 -- Classes table
81 CREATE TABLE IF NOT EXISTS classes (
82 id TEXT PRIMARY KEY,
83 course_code TEXT NOT NULL,
84 name TEXT NOT NULL,
85 professor TEXT NOT NULL,
86 semester TEXT NOT NULL,
87 year INTEGER NOT NULL,
88 archived BOOLEAN DEFAULT 0,
89 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
90 );
91
92 CREATE INDEX IF NOT EXISTS idx_classes_semester_year ON classes(semester, year);
93 CREATE INDEX IF NOT EXISTS idx_classes_archived ON classes(archived);
94 CREATE INDEX IF NOT EXISTS idx_classes_course_code ON classes(course_code);
95
96 -- Class members table
97 CREATE TABLE IF NOT EXISTS class_members (
98 class_id TEXT NOT NULL,
99 user_id INTEGER NOT NULL,
100 enrolled_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
101 PRIMARY KEY (class_id, user_id),
102 FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
103 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
104 );
105
106 CREATE INDEX IF NOT EXISTS idx_class_members_user_id ON class_members(user_id);
107 CREATE INDEX IF NOT EXISTS idx_class_members_class_id ON class_members(class_id);
108
109 -- Meeting times table
110 CREATE TABLE IF NOT EXISTS meeting_times (
111 id TEXT PRIMARY KEY,
112 class_id TEXT NOT NULL,
113 label TEXT NOT NULL,
114 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
115 FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE
116 );
117
118 CREATE INDEX IF NOT EXISTS idx_meeting_times_class_id ON meeting_times(class_id);
119
120 -- Transcriptions table
121 CREATE TABLE IF NOT EXISTS transcriptions (
122 id TEXT PRIMARY KEY,
123 user_id INTEGER NOT NULL,
124 class_id TEXT,
125 meeting_time_id TEXT,
126 filename TEXT NOT NULL,
127 original_filename TEXT NOT NULL,
128 status TEXT NOT NULL DEFAULT 'pending',
129 progress INTEGER NOT NULL DEFAULT 0,
130 error_message TEXT,
131 whisper_job_id TEXT,
132 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
133 updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
134 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
135 FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
136 FOREIGN KEY (meeting_time_id) REFERENCES meeting_times(id) ON DELETE SET NULL
137 );
138
139 CREATE INDEX IF NOT EXISTS idx_transcriptions_user_id ON transcriptions(user_id);
140 CREATE INDEX IF NOT EXISTS idx_transcriptions_class_id ON transcriptions(class_id);
141 CREATE INDEX IF NOT EXISTS idx_transcriptions_status ON transcriptions(status);
142 CREATE INDEX IF NOT EXISTS idx_transcriptions_whisper_job_id ON transcriptions(whisper_job_id);
143 CREATE INDEX IF NOT EXISTS idx_transcriptions_meeting_time_id ON transcriptions(meeting_time_id);
144
145 -- Class waitlist table
146 CREATE TABLE IF NOT EXISTS class_waitlist (
147 id TEXT PRIMARY KEY,
148 user_id INTEGER NOT NULL,
149 course_code TEXT NOT NULL,
150 course_name TEXT NOT NULL,
151 professor TEXT NOT NULL,
152 semester TEXT NOT NULL,
153 year INTEGER NOT NULL,
154 meeting_times TEXT,
155 additional_info TEXT,
156 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
157 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
158 );
159
160 CREATE INDEX IF NOT EXISTS idx_waitlist_user_id ON class_waitlist(user_id);
161 CREATE INDEX IF NOT EXISTS idx_waitlist_course_code ON class_waitlist(course_code);
162
163 -- Subscriptions table
164 CREATE TABLE IF NOT EXISTS subscriptions (
165 id TEXT PRIMARY KEY,
166 user_id INTEGER NOT NULL,
167 customer_id TEXT NOT NULL,
168 status TEXT NOT NULL,
169 current_period_start INTEGER,
170 current_period_end INTEGER,
171 cancel_at_period_end BOOLEAN DEFAULT 0,
172 canceled_at INTEGER,
173 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
174 updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
175 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
176 );
177
178 CREATE INDEX IF NOT EXISTS idx_subscriptions_user_id ON subscriptions(user_id);
179 CREATE INDEX IF NOT EXISTS idx_subscriptions_status ON subscriptions(status);
180 CREATE INDEX IF NOT EXISTS idx_subscriptions_customer_id ON subscriptions(customer_id);
181
182 -- Email verification tokens table
183 CREATE TABLE IF NOT EXISTS email_verification_tokens (
184 id TEXT PRIMARY KEY,
185 user_id INTEGER NOT NULL,
186 token TEXT NOT NULL UNIQUE,
187 expires_at INTEGER NOT NULL,
188 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
189 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
190 );
191
192 CREATE INDEX IF NOT EXISTS idx_verification_tokens_user_id ON email_verification_tokens(user_id);
193 CREATE INDEX IF NOT EXISTS idx_verification_tokens_token ON email_verification_tokens(token);
194
195 -- Password reset tokens table
196 CREATE TABLE IF NOT EXISTS password_reset_tokens (
197 id TEXT PRIMARY KEY,
198 user_id INTEGER NOT NULL,
199 token TEXT NOT NULL UNIQUE,
200 expires_at INTEGER NOT NULL,
201 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
202 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
203 );
204
205 CREATE INDEX IF NOT EXISTS idx_password_reset_tokens_user_id ON password_reset_tokens(user_id);
206 CREATE INDEX IF NOT EXISTS idx_password_reset_tokens_token ON password_reset_tokens(token);
207
208 -- Email change tokens table
209 CREATE TABLE IF NOT EXISTS email_change_tokens (
210 id TEXT PRIMARY KEY,
211 user_id INTEGER NOT NULL,
212 new_email TEXT NOT NULL,
213 token TEXT NOT NULL UNIQUE,
214 expires_at INTEGER NOT NULL,
215 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
216 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
217 );
218
219 CREATE INDEX IF NOT EXISTS idx_email_change_tokens_user_id ON email_change_tokens(user_id);
220 CREATE INDEX IF NOT EXISTS idx_email_change_tokens_token ON email_change_tokens(token);
221
222 -- Create ghost user for deleted accounts
223 INSERT OR IGNORE INTO users (id, email, password_hash, name, avatar, role, created_at)
224 VALUES (0, 'ghosty@thistle.internal', NULL, 'Ghosty', '馃懟', 'user', strftime('%s', 'now'));
225 `,
226 },
227];
228
229function getCurrentVersion(): number {
230 const result = db
231 .query<{ version: number }, []>(
232 "SELECT MAX(version) as version FROM schema_migrations",
233 )
234 .get();
235 return result?.version ?? 0;
236}
237
238function applyMigration(
239 version: number,
240 sql: string,
241 index: number,
242 total: number,
243) {
244 const current = getCurrentVersion();
245 if (current >= version) return;
246
247 const isTTY = typeof process !== "undefined" && process.stdout?.isTTY;
248 const startMsg = `Applying migration ${index + 1} of ${total}`;
249
250 if (isTTY) {
251 process.stdout.write(`${startMsg}...`);
252 const start = performance.now();
253 db.run(sql);
254 db.run("INSERT INTO schema_migrations (version) VALUES (?)", [version]);
255 const duration = Math.round(performance.now() - start);
256 process.stdout.write(`\r${startMsg} (${duration}ms)\n`);
257 } else {
258 console.log(startMsg);
259 db.run(sql);
260 db.run("INSERT INTO schema_migrations (version) VALUES (?)", [version]);
261 }
262}
263
264// Apply all migrations
265const current = getCurrentVersion();
266const pending = migrations.filter((m) => m.version > current);
267
268for (const [index, migration] of pending.entries()) {
269 applyMigration(migration.version, migration.sql, index, pending.length);
270}
271
272export default db;