Smithers · Architecture

Pluggable Database Backends

SQLite‑first locally, Postgres + Electric SQL for the cloud — one resolved backend honored by every path: the run, the CLI reads, the gateway, and the UI.

SQLite · local default PGlite · opt‑in Postgres · cloud Electric · multiplayer sync
Design doc: .smithers/specs/pluggable-db-backends.md
Why we're here

Off‑the‑shelf, it doesn't round‑trip

  • Write ≠ read. workflow run writes a SQLite store (seeded workflows use the sync, sqlite‑only createSmithers()).
  • Then the read defaults to PGlite and throws SMITHERS_MIGRATION_REQUIRED on a brand‑new install.
  • Worse: reads are sqlite‑only. find-db.js always opens bun:sqlite, so a PGlite run is invisible — ps shows [], exit 0.
  • The migration gate is a tripwire bolted on because reads can't read PGlite.
Path A — sqlite write, pglite read workflow run → SQLite ps / inspect default → PGlite MIGRATION REQUIRED Path B — pglite write, sqlite‑only read workflow run → PGlite (.smithers/pg) ps / inspect opens bun:sqlite runs: [ ] silent Neither path works. Root cause: write & read pick the backend independently.
Root cause

Two disagreeing backend decisions

WRITE / RUN path inherits the workflow's own factory call createSmithers → SQLite READ path resolveSmithersBackendChoice then opens bun:sqlite anyway default → PGlite they never agree engine just uses workflow.db — no resolution
  • The run path never resolves a backend — it uses whatever factory the workflow module called at import time.
  • The read path resolves a default (PGlite) but then opens bun:sqlite regardless.
  • No shared contract → the two halves of the same version disagree.
  • No e2e test does a full init → run → ps round‑trip, so it shipped.
The fix · spine

One resolved backend, every path

  • New shared openSmithersStore() in packages/smithers: resolve once, open exactly that backend, return a SmithersDb adapter + teardown.
  • findAndOpenDb() delegates to it — so ps/inspect/output/chat read the resolved backend (PGlite & Postgres included).
  • Run, gateway, eval/monitor all share the same opener. No more split brain.
  • Empty reads never provision a store.
run / up ps · inspect gateway eval · monitor tui openSmithersStore() resolve once → open that backend SQLitedefault PGliteopt‑in Postgrescloud
Decision

SQLite‑first, everywhere local

  • Flip the resolver default and openSmithersBackend's fresh‑workspace default from PGlite → SQLite.
  • Lightest local backend: no socket server, no pg client, no free‑port dance, zero migration on clean init.
  • Zero churn: seeded workflows already use createSmithers() (sqlite) — now coherent with reads.
  • PGlite / Postgres stay first‑class, selected by --backend / env / smithers.config.ts.
  • Intentionally rewrites the locked "fresh → PGlite" test.
init.smithers/ workflow run→ smithers.db ps · inspectreads SQLite ✓ no migration. ever. on clean init.
The crux

Sync source ≠ engine backend

Electric needs real Postgres — but that constraint must not dictate the local default. So the sync transport is mode‑selected, parallel to the pluggable engine backend.

LOCAL · single‑player (default) SQLite engine gateway WS / RPCdirect SyncSource UI (smithers ui) No Electric · no Postgres · no socket CLOUD · multiplayer Postgres engine Electric SQL shapeslogical replication UI (same) Writes still go through actions / RPC
Client attachment

The same UI, two transports

  • gateway-client / gateway-react talk to a pluggable client transport, not a hard‑wired backend.
  • Local: the transport is the gateway WS/RPC stream — it reads through SmithersDb, so it works off SQLite with no Electric lib loaded.
  • Cloud: the transport swaps to Electric shapes (via the electric‑proxy); the UI components don't change.
  • A single capability/URL handshake picks the mode — adding Electric is a transport swap, not a UI rewrite.
  • Since SQLite is now the default, the default client path is gateway‑WS‑direct.
UI · gateway-react hooks components · smithers ui gateway-client · pluggable transport one consumer API · mode‑selected WS / RPC stream SmithersDb → SQLite / PGlite LOCAL · default Electric shapes electric-proxy ← Postgres CLOUD · multiplayer PGlite is never an Electric source.
Safety

The store with run history wins

  • Symmetric rule, not "sqlite is legacy": physical run history is authoritative.
  • Fail loud only when the resolved backend diverges from where runs actually live and no receipt/config authorizes it.
  • Two stores both populated → SMITHERS_BACKEND_CONFLICT, never a silent pick.
  • .smithers/backend.json = authorization marker; migrated.json = migration receipt. Probes still win.
  • Clean install never trips it. Resolution is pure — reads never write markers.
probe every store runCount per backend runs in resolved→ use it ✓ runs elsewhere→ fail loud + migrate runs in two→ CONFLICT clean install: no runs anywhere → SQLite default, no prompt
Migration

Any direction — with an agent backstop

  • smithers migrate becomes multi‑directional: --from/--to any pair (incl. pglite → sqlite).
  • Existing PGlite users (from the old default) fail loud + offered migrate — never silently stranded.
  • Copy‑only: source left intact; receipts written only after verified row counts.
  • If deterministic migrate fails → an agent does it. A durable repair workflow diagnoses, completes table‑by‑table, verifies — crash‑safe & resumable.
  • Human‑approval gate before anything destructive.
SQLite PGlite Postgres deterministic copy (CI‑covered) fails? agent repair workflow diagnose → complete → verify · resumable · approval‑gated
Rollout

Phased, lowest‑risk first

Phase 1 · SQLite default + read openerfixes the clean‑init bug · minimal churn Phase 2 · symmetric probes + conflict gateexisting PGlite runs never hidden Phase 3 · multi‑directional migratelaunch gate: pglite → sqlite + agent fallback Phase 4 · gateway + sync‑source validationlive UI off SQLite, no Electric Phase 5 · cleanup across load sitesbackend‑aware teardown everywhere Phase 6 · secondary apps + authoring docsapps/review · scaffold prompts Phase 7 · Postgres / Electric cloudmultiplayer sync · cloud‑only

Phase 1 ships the fix

SQLite default + a backend‑aware read opener makes init → run → ps round‑trip and unblocks onboarding — by itself.

Phases 2–3 protect upgraders

Symmetric detection + multi‑directional migrate (with the agent backstop) keep existing PGlite users safe.

End state

What "good" looks like

Zero‑friction local

SQLite by default. init → run → ps → inspect just works — no migration, no socket, no config.

Truly pluggable

One resolved backend honored by run, reads, gateway, eval. PGlite & Postgres are first‑class, not special‑cased.

Sync that scales up

Same UI: gateway‑WS direct off SQLite locally, Electric + Postgres for cloud multiplayer. A transport swap.

Safe upgrades

Physical‑truth legacy rule, multi‑directional migrate, agent repair fallback, and an e2e round‑trip matrix so this never regresses.

Next: implement Phase 1 (codex) → review (opus + codex) → land.

1 / 11
SMITHERS · PLUGGABLE DB · use ← → / space