F2.A — bun:sqlite setup, WAL, migration runner

[HXYerror]

Part of #23.

Background

No persistent store exists. F2 (keys), F3 (events), and F4 (key_id resolution) need shared SQLite. Bun ships bun:sqlite built-in — zero new runtime dependency.

Goal

A bun:sqlite database at ~/.local/share/copilot-api/copilot-api.db with WAL, a migration runner using PRAGMA user_version, and typed query helpers.

Tasks

• src/lib/db.ts: open db, set PRAGMAs OUTSIDE any transaction (per backend review B3 — PRAGMA journal_mode is a no-op inside BEGIN):
  • PRAGMA journal_mode=WAL
  • PRAGMA synchronous=NORMAL
  • PRAGMA foreign_keys=ON
• Migration runner: read user_version, apply pending migrations/NNN_*.sql inside its own transaction, bump user_version in same transaction; commit
• First migration 001_init.sql is empty (placeholder); subsequent migrations come from F2.B and F3.A
• Set umask 077 before opening DB on first creation; chmod 0600 on *.db, *.db-wal, *.db-shm after creation; verify on startup and warn loudly if perms are wider (security hardening per S12)
• Migrations must complete BEFORE the HTTP listener binds (per security review S13) — otherwise admin routes can race with schema setup. Make migration sync part of startup.
• Graceful shutdown closes db handle (data loss otherwise on WAL)
• Document: backup must include .db + .db-wal + .db-shm together, OR use VACUUM INTO / sqlite3 .backup for consistent snapshots (per backend review previous_response_id stateful chain support #21)
• Tests: fresh dir → migrations run; second open → no-op; corruption → clear error; perms verified

Acceptance criteria

• bun run start on empty data dir creates the DB with journal_mode=wal (verify via sqlite3 .dbinfo)
• Adding a new migration file is the only step needed for schema changes
• File mode is 0600 for all SQLite files
• HTTP listener does not bind until migrations complete

File pointers

• New: src/lib/db.ts, src/lib/migrations/001_init.sql, tests/db.test.ts
• Touch: src/lib/paths.ts (add dbPath()), src/start.ts (wire migration sync)

Dependencies

Blocks F2.B, F3.A.

[HXYerror]

Implemented in two commits: 0b3f519 (feat) + 919dd47 (crew review fixes).

Changes shipped:

• src/lib/db.ts: openDb() (WAL+NORMAL+FK pragmas, lstat symlink guard, umask try/finally, fchmod hardening), runMigrations() (BEGIN EXCLUSIVE, user_version pattern, multi-statement run()), initDb()/closeDb()/getDb()/resetDb()
• src/lib/migrations/001_init.sql: empty placeholder
• src/lib/paths.ts: dbPath() + DB_PATH in PATHS
• src/start.ts: initDb() before HTTP listener, SIGINT/SIGTERM graceful shutdown with guarded getDb()
• tests/db.test.ts: 8 tests (WAL, perms, fresh version, apply+bump, idempotent, multi-statement, corrupt SQL error, getDb guard)

Review findings addressed: umask try/finally, BEGIN EXCLUSIVE, symlink-clobber via lstat+fchmod, SIGTERM getDb() guard, closeDb nullifies _db, unexported _db, { cause: err } stack trace, re-harden post-migration. 250 tests pass.