Optional PostgreSQL backend for the unified cache (design validated, call-site port unfinished)

[foundev]

Findings and validated design from a spike replacing/augmenting the SQLite unified cache with PostgreSQL, selected by configuration (BIFROST_DATABASE_URL set → Postgres; unset → SQLite exactly as today). The spike worktree was deliberately discarded; nothing was pushed or merged. This issue is the record needed to redo it efficiently.

Coupling inventory (why this is not a config-only change today)

• ~502 raw SQL statements against rusqlite (296 SELECT / 121 INSERT / 34 UPDATE / 31 DELETE / 20 CREATE); ~158 rusqlite::Connection in signatures; no storage abstraction existed.
• SQLite-load-bearing machinery, all in crates/bifrost-core/cache_db.rs + cache_gc.rs: PRAGMA user_version migration versioning, verify_upgraded_store's sqlite_master.sql text-equality invariant, online-backup version seeding, WAL/auto_vacuum/page_size/mmap tuning, 55 WITHOUT ROWID tables, EXPLAIN QUERY PLAN assertions in store/relational_query.rs tests.
• Dialect-divergent call sites (runtime forks needed on a Postgres arm): 37 INSERT OR IGNORE, 1 INSERT OR REPLACE, 40 GLOB, 47 json_extract, 6 json_each, 2 last_insert_rowid.

Validated design (all of this worked, end-to-end, against Postgres 16 in docker)

1. Dual-arm facade crates/bifrost-core/src/db.rs (~1.4k lines): rusqlite-shaped API (Connection/Transaction/Statement/Rows/MappedRows/Row::get::<_, T>/params!/params_from_iter/OptionalExtension/Error::QueryReturnedNoRows|FromSqlConversionFailure) as an enum over a rusqlite arm and a sync postgres = 0.19 client. Key decisions that made it work:
   • One shared dynamic Value{Null,Integer,Real,Text,Blob} model for rows/params on both arms → SQLite's dynamic typing preserved on Postgres (PG column type drives the fetch, FromSql converts).
   • ?/?N → $N rewriting at prepare time (quote/comment-aware), pass-through on SQLite.
   • SQLite arm keeps rusqlite lazy row streaming (the store has deliberate streaming read paths); PG arm fetches eagerly.
   • PG transactions emulated textually (BEGIN/SAVEPOINT bifrost_sp_N), rollback-on-drop; &mut scoping preserved.
   • SQLite-only lifecycle has no facade: Connection::sqlite()/sqlite_mut() escape hatch; pragma_update/busy_timeout are PG no-ops, pragma_update_and_check errors on PG.
2. Postgres schema: no migration-chain port. Fold the SQLite chain 0018..0032 in-memory (python sqlite3 executescript in file order), dump sqlite_master, translate once, FK-topo-sort tables. Translation rules: drop WITHOUT ROWID/STRICT; INTEGER→bigint; BLOB→bytea; NOT GLOB '*[^0-9a-f]*'→~ '^[0-9a-f]*$'; json_valid(x)→(x IS JSON) (PG16+); length(CAST(x AS BLOB))→octet_length(x); datetime('now')→to_char(now() AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS'); rowid-alias file_version_id INTEGER PRIMARY KEY→bigint GENERATED BY DEFAULT AS IDENTITY; parenthesize the COALESCE(exact_fqn_tail, '') expression index. Result (migrations/cache-pg/0032-baseline.sql): 51 tables, 88 indexes, 4 views (order live_parsed_blobs before its dependents), plus a store_version row.
3. Config seam in cache_db.rs: open_cache_connection[_readonly][_with_url] dispatching on BIFROST_DATABASE_URL (pure postgres_url_from(Option<OsString>) for testability; blank ≠ set). Store identity: bifrost_v{N}_{hash16} schema per store — version parsed from the bifrost_cache.v<N>.db file name, hash16 = sha256 of the canonical cache dir → keeps side-by-side versions, worktree convergence, per-repo isolation. First open: pg_advisory_lock → DROP/CREATE SCHEMA → apply baseline → verify store_version == CURRENT_MIGRATION_VERSION (loud failure if the PG baseline wasn't regenerated after a SQLite migration bump). Readonly refuses uninitialized stores and sets default_transaction_read_only=on. Gotcha: pg_advisory_lock returns VOID — the row reader must map VOID→Null.
4. Tests/CI: ungated tests for baseline version drift, schema naming, sqlite dispatch; gated integration test on BIFROST_TEST_DATABASE_URL (init → reopen → readonly enforcement); CI approach = postgres:16 service container on the source-contract job. Local: docker run -d --rm --name bifrost-pg-dev -e POSTGRES_PASSWORD=bifrost -e POSTGRES_DB=bifrost -p 127.0.0.1:15432:5432 postgres:16 then BIFROST_TEST_DATABASE_URL="postgres://postgres:bifrost@127.0.0.1:15432/bifrost" cargo test -p brokk-bifrost-core --lib cache_db::tests::postgres. Full bifrost-core suite stayed green (336/336) with the SQLite path untouched throughout.

Where the spike stopped

Call-site port of bifrost-analysis/bifrost-mcp (8 files; store/mod.rs alone is 22.8k lines). Mechanical rusqlite:: → brokk_bifrost_core::db:: swap plus facade shape-compat fixes (two-generic get::<_, T> via a RowIndex trait; Row<'_> phantom lifetime; single-generic MappedRows<'stmt, F> — the FnMut(&Row)->Result<T> bound constrains T, no PhantomData needed) took the error count 885 → ~156, remaining were mostly E0308s not yet diagnosed. Still untouched: the ~130 dialect forks above, cache_gc.rs port, moving analyzer/GC callers from open_unified_connection to open_cache_connection, data-boundaries.md docs.

Open questions for a redo

• Edition 2024: std::env::set_var is unsafe → keep the _with_url injection split for tests.
• INSERT OR IGNORE → ON CONFLICT DO NOTHING covers unique/PK conflicts only (not CHECK/NOT NULL) — audit the 37 sites.
• PG arm TLS is not wired (NoTls) — needed before any non-localhost use.