馃 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
65function getCurrentVersion(): number {
66 const result = db
67 .query<{ version: number }, []>(
68 "SELECT MAX(version) as version FROM schema_migrations",
69 )
70 .get();
71 return result?.version ?? 0;
72}
73
74function applyMigration(
75 version: number,
76 sql: string,
77 index: number,
78 total: number,
79) {
80 const current = getCurrentVersion();
81 if (current >= version) return;
82
83 const isTTY = typeof process !== "undefined" && process.stdout?.isTTY;
84 const startMsg = `Applying migration ${index + 1} of ${total}`;
85
86 if (isTTY) {
87 process.stdout.write(`${startMsg}...`);
88 const start = performance.now();
89 db.run(sql);
90 db.run("INSERT INTO schema_migrations (version) VALUES (?)", [version]);
91 const duration = Math.round(performance.now() - start);
92 process.stdout.write(`\r${startMsg} (${duration}ms)\n`);
93 } else {
94 console.log(startMsg);
95 db.run(sql);
96 db.run("INSERT INTO schema_migrations (version) VALUES (?)", [version]);
97 }
98}
99
100// Apply all migrations
101const current = getCurrentVersion();
102const pending = migrations.filter((m) => m.version > current);
103
104for (const [index, migration] of pending.entries()) {
105 applyMigration(migration.version, migration.sql, index, pending.length);
106}
107
108export default db;