馃 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
81function getCurrentVersion(): number {
82 const result = db
83 .query<{ version: number }, []>(
84 "SELECT MAX(version) as version FROM schema_migrations",
85 )
86 .get();
87 return result?.version ?? 0;
88}
89
90function applyMigration(
91 version: number,
92 sql: string,
93 index: number,
94 total: number,
95) {
96 const current = getCurrentVersion();
97 if (current >= version) return;
98
99 const isTTY = typeof process !== "undefined" && process.stdout?.isTTY;
100 const startMsg = `Applying migration ${index + 1} of ${total}`;
101
102 if (isTTY) {
103 process.stdout.write(`${startMsg}...`);
104 const start = performance.now();
105 db.run(sql);
106 db.run("INSERT INTO schema_migrations (version) VALUES (?)", [version]);
107 const duration = Math.round(performance.now() - start);
108 process.stdout.write(`\r${startMsg} (${duration}ms)\n`);
109 } else {
110 console.log(startMsg);
111 db.run(sql);
112 db.run("INSERT INTO schema_migrations (version) VALUES (?)", [version]);
113 }
114}
115
116// Apply all migrations
117const current = getCurrentVersion();
118const pending = migrations.filter((m) => m.version > current);
119
120for (const [index, migration] of pending.entries()) {
121 applyMigration(migration.version, migration.sql, index, pending.length);
122}
123
124export default db;