PostgreSQL storage backend (fleet-standardization + multi-host shared vault)

[belikh]

Motivation

jupiterOS (our NixOS fleet) is standardizing infrastructure choices — PostgreSQL is the fleet database (a dedicated Postgres host/service already runs in production). hyperresearch is becoming load-bearing agent infrastructure on that fleet, which makes its SQLite-only storage the odd one out:

• One supported DB tech across the fleet instead of a special case per tool
• True multi-writer concurrency — SQLite WAL allows a single writer; a Postgres backend lets research agents on multiple tailnet hosts run fetcher/investigator waves against ONE shared vault concurrently
• Mature backup/ops story — pgBackRest/pg_dump, monitoring, replication instead of bespoke SQLite snapshot jobs
• Centralized vault without NFS-mounted SQLite (locking corruption risk) — clients hit the DB server over the network while markdown notes stay local

Current state (inspected at HEAD 71b69dd)

• core/db.py uses stdlib sqlite3 directly (WAL mode, FK enforcement); connection acquisition is centralized in get_connection() — only 4 files touch the connection layer
• Full-text search depends on FTS5: notes_fts virtual table, MATCH queries, bm25() with weights spliced into the ranking argument (search/fts.py) — the main SQLite-specific surface
• Hand-rolled SQLite-flavored DDL migrations (core/migrations.py)
• ~40 files issue raw SQL total, but dialect-specific constructs appear concentrated in the three files above
• No backend/dsn option in config.toml

Proposed design

1. Config surface: [storage] backend = "sqlite" | "postgres" + dsn (with {env:VAR} interpolation, matching the project's existing env-substitution conventions). Default stays sqlite — zero breakage for single-user installs.
2. Connection layer: abstract the 4 touch-points behind a small engine interface (thin custom dialect layer, or SQLAlchemy Core behind an optional extra hyperresearch[postgres] → psycopg[binary]).
3. Search abstraction: extract an index interface with two implementations — FTS5 (MATCH/bm25) for SQLite, tsvector/tsquery + GIN + ts_rank for Postgres. search/fts.py already isolates nearly all of this logic.
4. Schema/migrations: dual-dialect migration set (JSON→JSONB on PG); consider Alembic if SQLAlchemy is adopted.
5. Migration tooling: hyperresearch migrate-db --to postgres --dsn … to port an existing vault's index. Notes remain plain markdown files on disk in both backends — only the index/DB moves, so cutover is low-risk and reversible.
6. Concurrency win: document/validate parallel CLI invocations (fetcher waves, multi-host agents) against the same Postgres vault — something SQLite's single-writer model can't offer.

Acceptance criteria

• status, lint, search, sync, note show/list, run verify all green against a Postgres backend
• FTS parity tests: identical query sets return identical note-id results on both backends
• Concurrent-writer smoke test (≥3 parallel writers, mixed notes/claims/graph writes)
• Fresh-install path works with zero Postgres (SQLite remains default)
• migrate-db round-trips an existing vault without data loss
• Docs: config reference + a docker-compose test fixture for CI