Problem
The API's database layer duplicates logic per-backend via paired _on_connection-suffixed functions (one code path built against a SQLite connection, one against a Postgres connection) rather than a single executor-generic implementation. This duplication is already large and grows with every new feature, since each new query or mutation needs to be written and tested twice.
Evidence
Occurrences of the _on_connection naming pattern, current main:
crates/sandboxwich-api/src/handlers/leases.rs — 64 occurrencescrates/sandboxwich-api/src/handlers/snapshots.rs — 30 occurrencescrates/sandboxwich-api/src/reconcile.rs — 27 occurrencescrates/sandboxwich-api/src/handlers/sandboxes.rs — 24 occurrences
That's 145 occurrences across just four files, not counting handlers/jobs.rs, handlers/workers.rs, handlers/operations.rs, and others that likely follow the same pattern.
Suggested approach
Investigate whether sqlx's Executor trait (generic over &mut SqliteConnection / &mut PgConnection, or an Any pool) can collapse the paired functions into one generic-over-executor implementation, keeping only the genuinely backend-divergent SQL (e.g. INSERT ... ON CONFLICT syntax differences, RETURNING support, atomic-counter upsert dialects) as explicit branches. This is a larger refactor and should land incrementally, file-by-file, each covered by the existing SQLite + Postgres contract-test suites so behavior parity is provable at each step, per the AGENTS.md guidance on keeping every change gate-clean.
Relationship to ROADMAP
Not a promotion-gate blocker directly, but every gate (Conformance in particular, "SQLite and PostgreSQL contract suites pass on the release commit") gets more expensive to keep true as this duplication compounds.
Problem
The API's database layer duplicates logic per-backend via paired
_on_connection-suffixed functions (one code path built against a SQLite connection, one against a Postgres connection) rather than a single executor-generic implementation. This duplication is already large and grows with every new feature, since each new query or mutation needs to be written and tested twice.Evidence
Occurrences of the
_on_connectionnaming pattern, currentmain:crates/sandboxwich-api/src/handlers/leases.rs— 64 occurrencescrates/sandboxwich-api/src/handlers/snapshots.rs— 30 occurrencescrates/sandboxwich-api/src/reconcile.rs— 27 occurrencescrates/sandboxwich-api/src/handlers/sandboxes.rs— 24 occurrencesThat's 145 occurrences across just four files, not counting
handlers/jobs.rs,handlers/workers.rs,handlers/operations.rs, and others that likely follow the same pattern.Suggested approach
Investigate whether sqlx's
Executortrait (generic over&mut SqliteConnection/&mut PgConnection, or anAnypool) can collapse the paired functions into one generic-over-executor implementation, keeping only the genuinely backend-divergent SQL (e.g.INSERT ... ON CONFLICTsyntax differences,RETURNINGsupport, atomic-counter upsert dialects) as explicit branches. This is a larger refactor and should land incrementally, file-by-file, each covered by the existing SQLite + Postgres contract-test suites so behavior parity is provable at each step, per theAGENTS.mdguidance on keeping every change gate-clean.Relationship to ROADMAP
Not a promotion-gate blocker directly, but every gate (Conformance in particular, "SQLite and PostgreSQL contract suites pass on the release commit") gets more expensive to keep true as this duplication compounds.