warehouse: a relative SQLite file: URI still follows the working directory, and the fix is platform-dependent

Parti dalla gestione delle connessioni del warehouse relativa agli URI dei file SQLite e confrontala con il tentativo di correzione #1204 descritto nell’issue. Riproduci il problema con un database reale e uno esca mentre cambi cwd/--dir, quindi aggiungi il probe del modo URI a runtime e il comportamento di riscrittura esclusivo di SQLite. Il lavoro è completato quando ogni ramo elencato è coperto da test con binari compilati sulle piattaforme distribuite e i dati esca dimostrano che viene aperto il database previsto.

What happens

A connection configured with a relative SQLite file: URI follows the process working directory, so --dir can point it at a different database. On macOS this is a genuine instance of the #1203 defect class, and a nastier one: the create-on-open guard cannot catch it, because the failure is opening the wrong existing database rather than making a new empty one.

Reproduced on macOS with bun:sqlite, one real store and one decoy:

config:            file:warehouse.db
cwd:               <decoy dir>
opens:             ["decoy_table"]      ← the decoy, not the configured store

with an absolute rewrite:
config:            file:/abs/path/warehouse.db
opens:             ["zorbulax_ledger"]  ← the intended store

Why it is not fixed in #1204

I implemented the rewrite in #1204 and then removed it, because it is platform-dependent in a way I could not verify across the shipped targets.

bun:sqlite's URI handling is not uniform. On macOS, file:warehouse.db is parsed as a URI (SQLITE_OPEN_URI behaviour): the reproduction above is real. On Linux CI the same test failed — the configured path opened nothing, which is consistent with file: being treated as a literal filename rather than a URI there. Windows was never verified at all, and it is a shipped build target (packages/opencode/script/build.ts).

That matters because the rewrite changes which database opens. Applying it on a platform where file: is a literal filename turns a working config into a broken one — precisely the class of bug #1203 is about. Shipping it half-verified would have been worse than leaving the exotic case alone.

The attempt also produced four separate regressions during review, each caught only by empirical testing, which is a fair signal about how much care this needs:

• Percent-encoded absolute paths. file:%2Fvar%2Fwh.db is absolute; SQLite decodes before opening. Treating it as relative produced a path that does not exist.
• file::memory: is SQLite's in-memory URI. Absolutizing it turned an in-memory database into a file on disk.
• Case sensitivity. SQLite recognises only a lowercase file:. A case-insensitive match rewrote FILE:warehouse.db, a literal filename, into a different path.
• Special characters in the base directory. A project path containing a literal %, ?, or # is URI syntax and needs encoding before being joined.

What a fix needs

1. Detect at runtime whether bun:sqlite on the current platform actually honours URI mode — e.g. probe new Database("file::memory:", { readwrite: true, create: false }) once, which succeeds only when URI parsing is active — and rewrite only when it does.
2. Decide relative-versus-absolute on the decoded path.
3. Decline the exact :memory: and decoded-colon-led forms.
4. Match the scheme case-sensitively.
5. Percent-encode the base directory before joining.
6. SQLite only. DuckDB reads file: as an extension scheme and errors with Extension "file.duckdb_extension" not found, so a rewrite there dresses up a path that never worked.
7. Cover every branch with a compiled-binary test on each shipped platform, with a decoy database planted so a wrong resolution reads plausible wrong data rather than nothing.

Severity

Low in practice. No evidence any user writes file: URIs into connections.json; the ordinary relative path form, which is what #1203 reported, is fixed on every platform by #1204. Filing so the hole is recorded with its evidence rather than forgotten.