Database architecture

Product handlers now depend on one execution and transaction contract. SQLite and PostgreSQL differences are bound once at the store boundary.

Execution path

1. A validated provider selects a dialect.
2. The store owns the driver connection and pool.
3. Handlers send static SQL with one placeholder convention.
4. The executor binds placeholders and transactions for the selected engine.
5. Stable API errors classify provider constraint failures.

What the dialect owns

Binder safety

The binder does not replace question marks inside string literals, quoted identifiers, line comments or block comments. Mixed placeholder styles and unterminated SQL are rejected. Internal identifiers must match a narrow generated-name grammar before quoting.

SELECT id FROM _trestle_jobs
WHERE status=? AND available_at<=?

# PostgreSQL execution
SELECT id FROM _trestle_jobs
WHERE status=$1 AND available_at<=$2

Dependency boundary

The pinned pure-Go SQLite driver remains unchanged. PostgreSQL uses the pinned lib/pq database/sql driver after a dependency and license review. Driver connections never reach handlers.

Provider-neutral guarantees

Because lib/pq registers only the legacy driver interface, database/sql can establish connections outside the request context. Trestle therefore injects its configured whole-second connect timeout as the driver's connect_timeout into every PostgreSQL connection configuration through structured URL handling; the rewritten DSN is never exposed.

The applied schema version is derived from validated migration history on both providers, and SQLite's PRAGMA user_version is only a compatibility mirror.