Switch the persistence layer from better-sqlite3 to postgres-js (async),
rewrite the schema in drizzle-orm/pg-core, and add Docker + scripts to
spin up Postgres 16 with pgvector locally on port 5433.
Schema changes beyond the SQLite → PG dialect swap:
- embedding_chunk.embedding becomes vector(384) with an HNSW cosine index,
enabling native similarity search via the `<=>` operator (no more
JSON-serialized arrays + JS-side cosine).
- jsonb for previously-text JSON columns (achievements, content_structured,
metadata).
- application_event generalised into a unified activity feed: adds
event_type, title, contact_id, scheduled_at, completed_at, outcome.
- New tables: contact_application (M:N junction so a recruiter can appear
in multiple applications) and company_research (per-company knowledge log).
Driver swap touches every query call site: all functions in src/db/queries
are now async, and all callers in src/app/api/**/*.ts and src/lib/{rag,claude}
await accordingly. SQLite-specific SQL (julianday, date('now', ...))
translated to Postgres equivalents (extract(epoch ...), current_date,
date_trunc('week', ...)).
Includes scripts/dump-sqlite.mjs and scripts/restore-postgres.mjs to migrate
existing data (run once: dump from old .db, then restore into the running
PG container).
Richard Nixon
0fee844c7a