Build Postgres backend (tracking)

[jstuart0]

Current state

The DATABASE_URL env var is parsed in src/server/config.ts, and config.useSqlite correctly detects a Postgres URL. But no downstream code path uses it. src/server/db/client.ts logs a warning and falls back to SQLite whenever Postgres is configured.

Previously, the README + wiki claimed Postgres support. Those claims were removed in the CHANGELOG entry under `[Unreleased]` / "Fixed — documentation". This issue tracks the actual backend port.

Why this matters

• Multi-replica scale-out requires a shared backend. SQLite + WAL handles single-pod fine; anything beyond requires Postgres.
• A few users have asked about running AgentPulse in k8s with >1 replica. Today the answer is "not yet" — we want the answer to be "yes, set DATABASE_URL".

Scope

This is a multi-phase project on the scale of the security remediation plan. Full phased plan is in `thoughts/2026-04-24-postgres-backend-plan.md` (local — `thoughts/` is gitignored). High-level:

1. Phase 1 — dual-dialect schema. Use Drizzle's `pgTable` alongside `sqliteTable`, gated by a backend selector. Every table gets a Postgres-native form (BOOLEAN, TIMESTAMP, BIGSERIAL) that maps to the same TypeScript shape as its SQLite sibling.
2. Phase 2 — migration runner. Drop the hand-rolled `CREATE TABLE IF NOT EXISTS` + `ALTER TABLE` retry loop. Use `drizzle-kit` to generate per-dialect migration files. Apply at boot with transaction-safe execution on both backends.
3. Phase 3 — raw-SQL audit. ~80+ sites use `datetime('now')`, `json_extract`, `AUTOINCREMENT`, `PRAGMA`, integer-as-boolean. Each one replaced with either a Drizzle expression or a dialect-aware helper.
4. Phase 4 — lock semantics. The migration-retry-on-lock logic is SQLite-specific. Postgres doesn't need it; replace with its own advisory-lock approach for single-replica-at-a-time migrations.
5. Phase 5 — test matrix. Every test that touches the DB runs against both backends. GitHub Actions CI matrix adds a `postgres:16` service.
6. Phase 6 — deployment docs. Restore the Postgres sections to README / wiki, with honest instructions.

Exit criteria

• `DATABASE_URL=postgres://…` spins AgentPulse up against a real Postgres.
• All 178 existing tests pass against Postgres in CI.
• Rolling k8s updates with 2+ replicas don't corrupt state.
• Docs match reality.

Not in scope

• Postgres-specific features (stored procedures, materialized views) — keep the schema portable.
• A SQLite → Postgres data-migration tool. First cut: spin up with an empty Postgres and re-enroll agents. We can add a migrator later if there's demand.

Help wanted

This is a multi-week project, not a good-first-issue. Experienced Drizzle / Postgres contributors — please comment on the plan before starting so scope stays aligned.