Repository Public
1// SQLite storage (node:sqlite, WAL). Migrations are append-only and tracked with PRAGMA user_version.2import { DatabaseSync } from "node:sqlite";34const MIGRATIONS: string[] = [5 // 1 — identity6 `7 CREATE TABLE users (8 id TEXT PRIMARY KEY,9 handle BLOB NOT NULL UNIQUE, -- WebAuthn user.id (random, no PII)10 display_name TEXT NOT NULL,11 created_at INTEGER NOT NULL,12 recovery_hash TEXT, -- scrypt hash of the recovery authenticator (never the recovery key itself)13 recovery_salt TEXT,14 seq INTEGER NOT NULL DEFAULT 0 -- per-user sync sequence15 );16 CREATE TABLE credentials (17 id TEXT PRIMARY KEY, -- base64url credential id18 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,19 public_key BLOB NOT NULL,20 counter INTEGER NOT NULL DEFAULT 0,21 transports TEXT,22 device_type TEXT,23 backed_up INTEGER NOT NULL DEFAULT 0,24 label TEXT NOT NULL DEFAULT '',25 created_at INTEGER NOT NULL,26 last_used_at INTEGER27 );28 CREATE INDEX credentials_user ON credentials(user_id);29 CREATE TABLE devices (30 id TEXT PRIMARY KEY,31 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,32 name TEXT NOT NULL,33 platform TEXT NOT NULL,34 model TEXT NOT NULL DEFAULT '',35 app_version TEXT NOT NULL DEFAULT '',36 enc_public_key TEXT NOT NULL DEFAULT '', -- device key-agreement public key (base64), used to wrap the account key37 key_envelope TEXT, -- account key sealed to enc_public_key by an already trusted device38 key_envelope_from TEXT,39 created_at INTEGER NOT NULL,40 last_seen_at INTEGER NOT NULL,41 last_sync_at INTEGER,42 revoked_at INTEGER43 );44 CREATE INDEX devices_user ON devices(user_id);45 CREATE TABLE sessions (46 token_hash TEXT PRIMARY KEY,47 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,48 device_id TEXT NOT NULL REFERENCES devices(id) ON DELETE CASCADE,49 created_at INTEGER NOT NULL,50 last_used_at INTEGER NOT NULL,51 expires_at INTEGER NOT NULL,52 reauth_at INTEGER,53 ip_hash TEXT54 );55 CREATE INDEX sessions_user ON sessions(user_id);56 CREATE TABLE challenges (57 id TEXT PRIMARY KEY,58 kind TEXT NOT NULL,59 challenge TEXT NOT NULL,60 user_id TEXT,61 data TEXT,62 expires_at INTEGER NOT NULL63 );64 CREATE TABLE invites (65 code_hash TEXT PRIMARY KEY,66 note TEXT NOT NULL DEFAULT '',67 created_at INTEGER NOT NULL,68 used_at INTEGER,69 used_by TEXT70 );71 `,72 // 2 — sync (ciphertext only) and account key material73 `74 CREATE TABLE sync_records (75 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,76 collection TEXT NOT NULL,77 id TEXT NOT NULL,78 seq INTEGER NOT NULL,79 deleted INTEGER NOT NULL DEFAULT 0,80 payload TEXT NOT NULL DEFAULT '', -- base64 AEAD ciphertext; empty for tombstones81 key_version INTEGER NOT NULL DEFAULT 1,82 device_id TEXT,83 updated_at INTEGER NOT NULL,84 PRIMARY KEY (user_id, collection, id)85 );86 CREATE INDEX sync_feed ON sync_records(user_id, seq);87 CREATE TABLE account_keys (88 user_id TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,89 key_version INTEGER NOT NULL DEFAULT 1,90 key_check TEXT NOT NULL, -- public verifier derived from the account key (lets a device detect a wrong key)91 recovery_blob TEXT NOT NULL, -- account key sealed under the recovery key (client side)92 created_at INTEGER NOT NULL,93 updated_at INTEGER NOT NULL94 );95 `,96 // 3 — operations: usage counters (no queries, no URLs)97 `98 CREATE TABLE usage_daily (99 day TEXT NOT NULL,100 provider TEXT NOT NULL,101 kind TEXT NOT NULL,102 requests INTEGER NOT NULL DEFAULT 0,103 credits REAL NOT NULL DEFAULT 0,104 cache_hits INTEGER NOT NULL DEFAULT 0,105 errors INTEGER NOT NULL DEFAULT 0,106 latency_ms_total INTEGER NOT NULL DEFAULT 0,107 PRIMARY KEY (day, provider, kind)108 );109 `,110];111112export type DB = DatabaseSync;113114export function openDatabase(path: string): DB {115 const db = new DatabaseSync(path);116 db.exec("PRAGMA journal_mode = WAL; PRAGMA foreign_keys = ON; PRAGMA busy_timeout = 5000; PRAGMA synchronous = NORMAL;");117 const row = db.prepare("PRAGMA user_version").get() as { user_version: number };118 for (let v = row.user_version; v < MIGRATIONS.length; v++) {119 db.exec("BEGIN");120 try {121 db.exec(MIGRATIONS[v]);122 db.exec(`PRAGMA user_version = ${v + 1}`);123 db.exec("COMMIT");124 } catch (error) {125 db.exec("ROLLBACK");126 throw error;127 }128 }129 return db;130}131132export function schemaVersion(db: DB): number {133 return (db.prepare("PRAGMA user_version").get() as { user_version: number }).user_version;134}135136/** Runs `fn` inside an IMMEDIATE transaction. */137export function transaction<T>(db: DB, fn: () => T): T {138 db.exec("BEGIN IMMEDIATE");139 try {140 const result = fn();141 db.exec("COMMIT");142 return result;143 } catch (error) {144 db.exec("ROLLBACK");145 throw error;146 }147}148