Pluggable DB backends, line by line

apps/cli/src/find-db.js — read path, before

// every read command opened bun:sqlite, no matter what
export async function openSmithersDb(dbPath) {
  const { Database } = await import("bun:sqlite");
  const sqlite = new Database(dbPath); // ← always sqlite
  const db = drizzle(sqlite);
  ...
}

resolveSmithersBackendChoice.js — default, before

const backend = explicitBackend ?? envBackend ?? configBackend ?? "pglite";
// default pglite, but reads can't open pglite → migration tripwire fires

resolveSmithersBackendChoice.js :330

const backend = explicitBackend ?? envBackend ?? configBackend
              ?? markerBackend ?? "sqlite";
const source  = explicitBackend ? "options"
              : envBackend     ? "env"
              : configBackend  ? "config"
              : markerBackend  ? "marker"
              :                  "default";

Precedence: a flag (--backend), then SMITHERS_BACKEND, then smithers.config.ts, then a workspace .smithers/backend.json marker, then the new default — sqlite. The lightest local store: no socket, no pg client, no port, no migration on a clean install.

resolveSmithersBackendChoice.js :336

const shouldInspectPostgres =
  backend === "postgres" ||
  Boolean(opts.connection || explicitPostgresConnectionString);

const postgresStore = shouldInspectPostgres
  ? await inspectPostgresStore({
      connectionString: explicitPostgresConnectionString
        ?? env.SMITHERS_POSTGRES_URL ?? env.DATABASE_URL, ... })
  : emptyProbe;

The bug the reviewer caught: the first cut probed Postgres whenever an ambient DATABASE_URL was set — a near-universal dev env var. That meant every smithers ps on a default-sqlite repo would open a pg connection, and a firewalled host could hang it. Now we only probe Postgres when the resolved backend is postgres, or you passed an explicit connection.

resolveSmithersBackendChoice.js :345 + :362 + :373

// 1) collect every store that actually has runs
const populated = [
  ...(sqliteStore.runCount  > 0 ? [{ backend:"sqlite", ... }] : []),
  ...(pgliteStore.runCount  > 0 ? [{ backend:"pglite", ... }] : []),
  ...(postgresStore.runCount> 0 ? [{ backend:"postgres",...}] : []),
];
// 2) legacy sqlite + non-sqlite default, no marker → migrate
if ((backend==="pglite"||backend==="postgres") && sqliteStore.runCount>0 && !marker)
  throw migrationRequiredError(...);
// 3) resolved backend differs from where the runs are → fail loud
const authoritative = populated[0];
if (authoritative && authoritative.backend !== backend)
  throw divergenceError({ sourceBackend: authoritative.backend, ... });

packages/smithers/src/openSmithersStore.js :90

export async function openSmithersStore(opts = {}) {
  const mode = opts.mode ?? "write";
  return retryNotFound(async () => {
    const choice = await resolveSmithersBackendChoice(opts);
    if (mode === "read") assertReadStoreExists(choice, opts);
    if (choice.backend === "sqlite") {           // sync, zero-socket
      const opened = await openSqliteStore(choice.dbPath);
      return { choice, adapter: opened.adapter, db: opened.db, ... };
    }
    const api = await openSmithersBackend({}, opts); // pglite/postgres
    if (mode === "read") await assertRunsTableExists(api.db);
    return { choice, adapter: new SmithersDb(api.db), db: api.db,
             cleanup: async () => { await api.close?.(); } };
  }, opts.wait);
}

apps/cli/src/find-db.js — findAndOpenDb, after

export async function findAndOpenDb(from, opts) {
  const opened = await openSmithersStore({
    cwd: from ?? process.cwd(),
    mode: "read",
    wait: opts,
  });
  return { adapter: opened.adapter, dbPath: opened.dbPath, cleanup: opened.cleanup };
}

Every read command — ps, inspect, output, chat, the scheduler, the TUI — flows through this. Point it at a pglite or postgres workspace and it opens that store. The old "always bun:sqlite" line is gone, and so is the silent-empty failure mode.

Before — pglite/postgres → sqlite rebuilt tables column-only

// CREATE TABLE from information_schema columns
// → no PRIMARY KEY, no indexes; row-count test still passed ✅😱

migrateSmithersStore.js :778 — after

sqlite = new Database(dbPath);
ensureSmithersTables(drizzle(sqlite)); // canonical schema FIRST (PK + indexes)
await prepareSqliteTarget(sqlite, pgConn, tables); // extras IF NOT EXISTS
for (const table of tables)
  tableResults.push(await copyPgTableToSqlite(pgConn, sqlite, table, ...));
// then verify row counts match, per table, or throw

apps/cli/tests/sqlite-default-roundtrip.e2e.test.js

test("default sqlite init/run/read round-trip needs no migration prompt", () => {
  const repo = createTempRepo();
  writeTestWorkflow(repo, ".smithers/workflows/default-roundtrip.tsx");

  const run = runSmithers(["workflow","run","default-roundtrip", ...]);
  expect(run.exitCode).toBe(0);
  expect(run.stderr).not.toContain("SMITHERS_MIGRATION_REQUIRED");

  const ps = runSmithers(["ps"], ...);
  expect(JSON.stringify(ps.json)).toContain("sqlite-default-roundtrip");

  const inspect = runSmithers(["inspect","sqlite-default-roundtrip"], ...);
  expect(inspect.json.run.id).toBe("sqlite-default-roundtrip");

  const output = runSmithers(["output","sqlite-default-roundtrip","write-result"]);
  expect(output.stdout).toContain("fixture workflow ran");
});

Compatibility matrix — what feeds the browser SQLite

deployment   engine backend   sync backing      Electric?
local        SQLite           gateway WS        no
local        PGlite           gateway WS        no
local        Postgres         gateway WS        no
cloud        Postgres         Electric (svr)    yes
cloud        PGlite           gateway WS        no  (no Electric src)
any          —  browser never connects to Electric directly

Status: the storage layer (this changeset) is done. This sync layer is Track B — the client already has the SyncTransport seam and both gateway + Electric collection sources; the remaining work is to collapse the client to one path, move Electric behind the gateway, and test every cell of this matrix end-to-end (local + cloud). That work + its test plan is being designed next.