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