Richard Nixon
0fee844c7a
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).
18 lines
470 B
YAML
18 lines
470 B
YAML
services:
|
|
postgres:
|
|
image: pgvector/pgvector:pg16
|
|
container_name: job-agent-postgres
|
|
restart: unless-stopped
|
|
environment:
|
|
POSTGRES_USER: jobagent
|
|
POSTGRES_PASSWORD: jobagent
|
|
POSTGRES_DB: job_agent
|
|
ports:
|
|
- "127.0.0.1:5433:5432"
|
|
volumes:
|
|
- ./storage/pg:/var/lib/postgresql/data
|
|
healthcheck:
|
|
test: ["CMD-SHELL", "pg_isready -U jobagent -d job_agent"]
|
|
interval: 5s
|
|
timeout: 5s
|
|
retries: 10
|