Migrate query store from SQLite (better-sqlite3) to PostgreSQL

[atomantic]

Summary

Migrate the local query/index store from SQLite (better-sqlite3) to PostgreSQL. Postgres is already running on this machine (used by PortOS), so standing up a shared/companion database is low-friction.

JSON files in data/person/*.json remain the source of truth (Layer 1), so this is a migration of the derived query layer (Layer 2) only — it can be rebuilt at any time via scripts/rebuild.ts, which substantially de-risks the change.

Motivation

• Native-binding fragility. better-sqlite3 ships a compiled native addon that breaks on Node upgrades / dep bumps (e.g. the recent "Could not locate the bindings file" outage that 500'd every data endpoint and dropped avatars). A networked Postgres client (pg) has no native-compile step and no per-Node-version binary to keep in sync.
• Consolidation. Postgres is already deployed on this box for PortOS — one database engine to operate, back up, and monitor.
• Concurrency. better-sqlite3 is synchronous/single-process; Postgres handles concurrent readers/writers cleanly (useful during long indexing runs while the UI is browsing).

Scope / touch points

• server/src/db/schema.sql (~306 lines) — translate DDL to Postgres.
• server/src/db/sqlite.service.ts — the data-access layer (~46 files reference SQLite/sqliteService).
• server/src/lib/sqlite-writer.ts (~550 lines) — write path during indexing.
• server/src/db/migrations/* — port migration runner; notably 003_rebuild_fts.ts.
• scripts/rebuild.ts, scripts/migrate.ts, scripts/prune.ts, scripts/purge.ts.
• server/src/services/database.service.ts — the SQLite-vs-JSON selection logic (recently hardened to fall back to JSON; keep a similar "is Postgres reachable?" guard).
• ecosystem.config.cjs / config — add a DATABASE_URL (or host/port/db/user/pass) for the Postgres connection.

Notable translation work

• Full-text search: SQLite FTS5 (MATCH, the *_fts virtual tables, 003_rebuild_fts) → Postgres tsvector + GIN index, or pg_trgm for fuzzy/substring matching. Search API behavior must stay equivalent.
• Sync → async: better-sqlite3 is synchronous; pg is promise-based. The data-access layer and its callers need to become async.
• Types: SQLite's loose typing / INTEGER booleans → real Postgres boolean, timestamptz, jsonb (for the JSON columns), etc.
• Upserts: INSERT ... ON CONFLICT exists in both, but verify conflict targets line up with Postgres unique constraints.
• Identity: ULIDs stay as the canonical IDs (store as text/char(26)); no change to the identity model.

Suggested approach

1. Stand up a sparsetree Postgres database (reuse the PortOS instance) and add connection config.
2. Port schema.sql to Postgres DDL + a fresh migration baseline.
3. Add a pg-backed data-access module behind the same interface sqlite.service.ts exposes, so call sites change minimally.
4. Rebuild the query layer from the JSON source of truth (scripts/rebuild.ts) rather than copying rows out of SQLite — cleaner and validates the new schema end-to-end.
5. Keep the JSON-fallback guard in database.service.ts; add a "Postgres unreachable → fall back to JSON" path.
6. Remove better-sqlite3 and the SQLite-specific code once parity is verified.

Open questions

• Dedicated sparsetree DB on the existing Postgres cluster, or a separate instance?
• Keep SQLite as an optional offline/embedded backend (driver abstraction), or fully replace it?
• Search parity: is tsvector/GIN sufficient, or do we want pg_trgm fuzzy matching for name search?

Decomposed into

• Add PostgreSQL schema and connection foundation #148 — Add PostgreSQL schema and connection foundation
• Rebuild the PostgreSQL query store from JSON source data #149 — Rebuild the PostgreSQL query store from JSON source data
• Move core database reads to PostgreSQL with JSON fallback #150 — Move core database reads to PostgreSQL with JSON fallback
• Port person search to PostgreSQL full-text indexing #151 — Port person search to PostgreSQL full-text indexing
• Port relationship and identity query services to PostgreSQL #152 — Port relationship and identity query services to PostgreSQL
• Port local user data and enrichment services to PostgreSQL #153 — Port local user data and enrichment services to PostgreSQL
• Port audit persistence to PostgreSQL #154 — Port audit persistence to PostgreSQL
• Cut over tooling to PostgreSQL and remove SQLite #155 — Cut over tooling to PostgreSQL and remove SQLite

[atomantic]

Decomposing into independently shippable capability slices: PostgreSQL foundation, rebuild/write path, core read fallback, search parity, relationship queries, local-state features, audit persistence, and final SQLite removal.