burrito.space
group conversations with models and local files
1// SQLite access. v2 is a single global DB — no per-project SQLite,
2// no blob directory. Everything content-shaped lives in client CRDTs.
3// Schema is managed by versioned migrations (server/schemas/index.ts).
4
5import Database from 'better-sqlite3'
6import * as fs from 'node:fs'
7import * as path from 'node:path'
8import { GLOBAL_MIGRATIONS, type Migration } from './schemas/index.js'
9import { DATA_DIR } from './paths.js'
10
11export { DATA_DIR }
12
13// v1 (Centrosome era) shipped a different global schema (no
14// identity_pub on users, no webauthn_credentials, no outbox table) plus
15// a `projects/<id>.sqlite` directory of per-project DBs that v2 doesn't
16// understand. If we boot against a v1 volume, wipe it clean rather than
17// limping forward with stale tables that v2 queries will silently miss.
18function looksLikeV1Schema(db: Database.Database): boolean {
19 const hasMeta = db
20 .prepare(
21 `SELECT 1 AS x FROM sqlite_master WHERE type='table' AND name='meta'`,
22 )
23 .get() as { x: number } | undefined
24 if (!hasMeta) return false
25 const hasV2Table = db
26 .prepare(
27 `SELECT 1 AS x FROM sqlite_master WHERE type='table' AND name='webauthn_credentials'`,
28 )
29 .get() as { x: number } | undefined
30 if (hasV2Table) return false
31 const hasUsers = db
32 .prepare(
33 `SELECT 1 AS x FROM sqlite_master WHERE type='table' AND name='users'`,
34 )
35 .get() as { x: number } | undefined
36 // If users existed without a webauthn_credentials sibling, this is
37 // either v1 or a half-migrated v2 we should also rebuild.
38 return !!hasUsers
39}
40
41function wipeAllUserTables(db: Database.Database): void {
42 const tables = db
43 .prepare(
44 `SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'`,
45 )
46 .all() as Array<{ name: string }>
47 db.exec('PRAGMA foreign_keys = OFF')
48 for (const { name } of tables) db.exec(`DROP TABLE IF EXISTS "${name}"`)
49 db.exec('PRAGMA foreign_keys = ON')
50}
51
52// Drop the v1 `projects/` directory if it exists — those were the
53// per-project SQLite files + content-addressed blob dirs. v2 doesn't
54// touch them, so leaving them in place would just eat disk forever.
55function wipeV1ProjectFiles(): void {
56 const projectsDir = path.join(DATA_DIR, 'projects')
57 if (!fs.existsSync(projectsDir)) return
58 try {
59 fs.rmSync(projectsDir, { recursive: true, force: true })
60 console.warn(`[migration] removed legacy v1 projects dir: ${projectsDir}`)
61 } catch (err) {
62 console.warn('[migration] could not remove legacy projects dir:', err)
63 }
64}
65
66function applyMigrations(
67 db: Database.Database,
68 migrations: Migration[],
69): void {
70 db.exec(
71 'CREATE TABLE IF NOT EXISTS meta (k TEXT PRIMARY KEY, v TEXT NOT NULL)',
72 )
73 const row = db
74 .prepare(`SELECT v FROM meta WHERE k = 'schema_version'`)
75 .get() as { v: string } | undefined
76 const current = row ? parseInt(row.v, 10) : 0
77 for (const m of migrations) {
78 if (m.version <= current) continue
79 const run = (): void => {
80 if (m.sql) db.exec(m.sql)
81 if (m.apply) m.apply(db)
82 db.prepare(
83 `INSERT INTO meta (k, v) VALUES ('schema_version', ?)
84 ON CONFLICT(k) DO UPDATE SET v = excluded.v`,
85 ).run(String(m.version))
86 }
87 if (m.useTransaction === false) {
88 run()
89 } else {
90 db.transaction(run)()
91 }
92 }
93}
94
95const globalDb = new Database(path.join(DATA_DIR, 'global.sqlite'))
96globalDb.pragma('journal_mode = WAL')
97globalDb.pragma('foreign_keys = ON')
98
99if (looksLikeV1Schema(globalDb)) {
100 console.warn('[migration] detected v1 schema in global.sqlite; wiping for v2 bootstrap')
101 wipeAllUserTables(globalDb)
102 wipeV1ProjectFiles()
103}
104
105applyMigrations(globalDb, GLOBAL_MIGRATIONS)
106
107export function db(): Database.Database {
108 return globalDb
109}
110
111// Periodic outbox TTL sweep. Outbox entries expire 30 days after
112// creation regardless of delivery. WebAuthn challenges expire after
113// a few minutes. .unref() so the timer doesn't keep the process alive.
114const SWEEP_INTERVAL_MS = 60 * 60 * 1000 // 1h
115const sweepTimer = setInterval(() => {
116 try {
117 const n = globalDb
118 .prepare('DELETE FROM outbox WHERE expires_at < ?')
119 .run(Date.now()).changes
120 if (n > 0) console.log(`[outbox] swept ${n} expired entries`)
121 globalDb
122 .prepare('DELETE FROM webauthn_challenges WHERE expires_at < ?')
123 .run(Date.now())
124 } catch (err) {
125 console.warn('[sweep]', err)
126 }
127}, SWEEP_INTERVAL_MS)
128sweepTimer.unref()