SPB Git

Repository Public

db.ts 4.9 KB 148 lines typescript

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
Files main
Menu

Theme