馃 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
143function getCurrentVersion(): number {
144 const result = db
145 .query<{ version: number }, []>(
146 "SELECT MAX(version) as version FROM schema_migrations",
147 )
148 .get();
149 return result?.version ?? 0;
150}
151
152function applyMigration(
153 version: number,
154 sql: string,
155 index: number,
156 total: number,
157) {
158 const current = getCurrentVersion();
159 if (current >= version) return;
160
161 const isTTY = typeof process !== "undefined" && process.stdout?.isTTY;
162 const startMsg = `Applying migration ${index + 1} of ${total}`;
163
164 if (isTTY) {
165 process.stdout.write(`${startMsg}...`);
166 const start = performance.now();
167 db.run(sql);
168 db.run("INSERT INTO schema_migrations (version) VALUES (?)", [version]);
169 const duration = Math.round(performance.now() - start);
170 process.stdout.write(`\r${startMsg} (${duration}ms)\n`);
171 } else {
172 console.log(startMsg);
173 db.run(sql);
174 db.run("INSERT INTO schema_migrations (version) VALUES (?)", [version]);
175 }
176}
177
178// Apply all migrations
179const current = getCurrentVersion();
180const pending = migrations.filter((m) => m.version > current);
181
182for (const [index, migration] of pending.entries()) {
183 applyMigration(migration.version, migration.sql, index, pending.length);
184}
185
186export default db;