馃 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 user schema",
17 sql: `
18 CREATE TABLE IF NOT EXISTS users (
19 id INTEGER PRIMARY KEY AUTOINCREMENT,
20 email TEXT UNIQUE NOT NULL,
21 password_hash TEXT NOT NULL,
22 name TEXT,
23 avatar TEXT DEFAULT 'd',
24 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
25 );
26
27 CREATE TABLE IF NOT EXISTS sessions (
28 id TEXT PRIMARY KEY,
29 user_id INTEGER NOT NULL,
30 ip_address TEXT,
31 user_agent TEXT,
32 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
33 expires_at INTEGER NOT NULL,
34 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
35 );
36
37 CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id);
38 CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions(expires_at);
39 `,
40 },
41 {
42 version: 2,
43 name: "Add transcriptions table",
44 sql: `
45 CREATE TABLE IF NOT EXISTS transcriptions (
46 id TEXT PRIMARY KEY,
47 user_id INTEGER NOT NULL,
48 filename TEXT NOT NULL,
49 original_filename TEXT NOT NULL,
50 status TEXT NOT NULL DEFAULT 'uploading',
51 progress INTEGER NOT NULL DEFAULT 0,
52 transcript TEXT,
53 error_message TEXT,
54 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
55 updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
56 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
57 );
58
59 CREATE INDEX IF NOT EXISTS idx_transcriptions_user_id ON transcriptions(user_id);
60 CREATE INDEX IF NOT EXISTS idx_transcriptions_status ON transcriptions(status);
61 `,
62 },
63 {
64 version: 3,
65 name: "Add whisper_job_id to transcriptions",
66 sql: `
67 ALTER TABLE transcriptions ADD COLUMN whisper_job_id TEXT;
68 CREATE INDEX IF NOT EXISTS idx_transcriptions_whisper_job_id ON transcriptions(whisper_job_id);
69 `,
70 },
71 {
72 version: 4,
73 name: "Remove transcript column from transcriptions",
74 sql: `
75 -- SQLite 3.35.0+ supports DROP COLUMN
76 ALTER TABLE transcriptions DROP COLUMN transcript;
77 `,
78 },
79 {
80 version: 5,
81 name: "Add rate limiting table",
82 sql: `
83 CREATE TABLE IF NOT EXISTS rate_limit_attempts (
84 id INTEGER PRIMARY KEY AUTOINCREMENT,
85 key TEXT NOT NULL,
86 timestamp INTEGER NOT NULL
87 );
88
89 CREATE INDEX IF NOT EXISTS idx_rate_limit_key_timestamp ON rate_limit_attempts(key, timestamp);
90 `,
91 },
92 {
93 version: 6,
94 name: "Add role-based auth system",
95 sql: `
96 -- Add role column (default to 'user')
97 ALTER TABLE users ADD COLUMN role TEXT NOT NULL DEFAULT 'user';
98
99 -- Create index on role
100 CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);
101 `,
102 },
103 {
104 version: 7,
105 name: "Add WebAuthn passkey support",
106 sql: `
107 CREATE TABLE IF NOT EXISTS passkeys (
108 id TEXT PRIMARY KEY,
109 user_id INTEGER NOT NULL,
110 credential_id TEXT NOT NULL UNIQUE,
111 public_key TEXT NOT NULL,
112 counter INTEGER NOT NULL DEFAULT 0,
113 transports TEXT,
114 name TEXT,
115 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
116 last_used_at INTEGER,
117 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
118 );
119
120 CREATE INDEX IF NOT EXISTS idx_passkeys_user_id ON passkeys(user_id);
121 CREATE INDEX IF NOT EXISTS idx_passkeys_credential_id ON passkeys(credential_id);
122
123 -- Make password optional for users who only use passkeys
124 CREATE TABLE users_new (
125 id INTEGER PRIMARY KEY AUTOINCREMENT,
126 email TEXT UNIQUE NOT NULL,
127 password_hash TEXT,
128 name TEXT,
129 avatar TEXT DEFAULT 'd',
130 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
131 role TEXT NOT NULL DEFAULT 'user'
132 );
133
134 INSERT INTO users_new SELECT * FROM users;
135 DROP TABLE users;
136 ALTER TABLE users_new RENAME TO users;
137
138 CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);
139 `,
140 },
141 {
142 version: 8,
143 name: "Add last_login to users",
144 sql: `
145 ALTER TABLE users ADD COLUMN last_login INTEGER;
146 CREATE INDEX IF NOT EXISTS idx_users_last_login ON users(last_login);
147 `,
148 },
149 {
150 version: 9,
151 name: "Add class_name to transcriptions",
152 sql: `
153 ALTER TABLE transcriptions ADD COLUMN class_name TEXT;
154 CREATE INDEX IF NOT EXISTS idx_transcriptions_class_name ON transcriptions(class_name);
155 `,
156 },
157];
158
159function getCurrentVersion(): number {
160 const result = db
161 .query<{ version: number }, []>(
162 "SELECT MAX(version) as version FROM schema_migrations",
163 )
164 .get();
165 return result?.version ?? 0;
166}
167
168function applyMigration(
169 version: number,
170 sql: string,
171 index: number,
172 total: number,
173) {
174 const current = getCurrentVersion();
175 if (current >= version) return;
176
177 const isTTY = typeof process !== "undefined" && process.stdout?.isTTY;
178 const startMsg = `Applying migration ${index + 1} of ${total}`;
179
180 if (isTTY) {
181 process.stdout.write(`${startMsg}...`);
182 const start = performance.now();
183 db.run(sql);
184 db.run("INSERT INTO schema_migrations (version) VALUES (?)", [version]);
185 const duration = Math.round(performance.now() - start);
186 process.stdout.write(`\r${startMsg} (${duration}ms)\n`);
187 } else {
188 console.log(startMsg);
189 db.run(sql);
190 db.run("INSERT INTO schema_migrations (version) VALUES (?)", [version]);
191 }
192}
193
194// Apply all migrations
195const current = getCurrentVersion();
196const pending = migrations.filter((m) => m.version > current);
197
198for (const [index, migration] of pending.entries()) {
199 applyMigration(migration.version, migration.sql, index, pending.length);
200}
201
202export default db;