Adapter-owned lowering over host-resolved SQL-v1
- Status: Accepted
- Date: 2026-09-04
- Supersedes: thin-adapter wording in this ADR (2026-08-21). Domain
ownership is unchanged; physical SQL realization belongs to adapters
(shared SDK
bookclerk-db-exec), not a host dialect lowerer. - Related: #178, #177, #191, Workers RPC + workerd
Bookclerk ships three first-party database guests (SQLite, PostgreSQL,
Cloudflare D1). Ordinary CRUD already crosses a generic SeaORM proxy.
Atomic work did not: the wire type was a named
domain enum (deleteUser, publishDomainEvent, claimNextJob, …). D1
recompiled each variant into schema-aware SQL inside the guest; SQLite and
PostgreSQL ran a separate SeaORM implementation of the same invariants.
Event claiming branched on DatabaseBackend::Postgres for
pg_advisory_xact_lock. Schema changes required coordinated edits in the
host and the D1 planner.
That made D1 a second Bookclerk repository, not a SQL adapter.
Supported backends are SQLite, PostgreSQL, and Cloudflare D1 (SQLite dialect over HTTP). Document, key-value, and other non-SQL stores are out of scope. A guest that cannot meet the required semantics must fail capability negotiation rather than silently weaken correctness.
| Layer | Owns |
|---|---|
Host / bookclerk-library |
Schema and migrations; domain operations; SQL-v1 parse/admission/type/authz; backend-independent desugars (ORDER BY NULLS, NULLIF for / %); structured proofs; result interpretation; idempotency policy |
Adapter SDK / bookclerk-db-exec |
Placeholders, COLLATE "C", LIKE→GLOB, helper rewrites, overflow SQL shape, INSERT OR IGNORE → ON CONFLICT, DDL/identity, txn/D1 batch, result normalization |
| Database plugin | Connection and transport; capability advertisement; calling the SDK (D1 owns HTTP); engine execution |
| ABI | Generic, bounded execution primitives, structured proofs on adapter execute, and capability negotiation. It must not require an adapter to understand users, jobs, books, events, or Bookclerk table names |
Domain names (publishDomainEvent, claimNextJob, deleteUser) stay in
host code. The ABI is an escape hatch for backend mechanics, not a second
repository interface. Adapters must not reparse canonical SQL to rediscover
semantics; they apply proof sites and mechanical lowering.
After openSession the host calls typed AdapterDatabaseSession.capabilities.
DbCapabilities advertises the SQL contract version,
execution semantics (atomicBatch, returning, affectedRows,
cancellation), schema versioning (schemaMigrations is required; host
policy ignores timing), and all
numeric limits (maxBinds, maxStatements, maxResultRows, maxPayloadBytes,
maxResultBytes, maxCellBytes, maxRequestBytes,
maxAtomicResultBytes, maxFunctionArgs, maxSchemaColumns,
maxPatternBytes, maxLoweredStatementBytes). schemaMigrations is the
only schema flag: every adapter must keep the host's
bookclerk_schema_migrations journal (there is no PRAGMA user_version
alternative and no separate atomic-schema-batch flag). Bootstrap metadata
(sqlFamily, SeaORM dialect) is not on typed DbCapabilities;
it travels on the separate typed DbBootstrap / the host connect path after
semantic negotiation succeeds. DbCapabilities @15 is pluginDatabases,
not a leftover sqlFamily tombstone. @19 is maxLoweredStatementBytes.
@20 consistentBackupRead and @21 atomicUnitRestore split consistent
capture from complete per-unit replacement. @22 maxStreamedResultBytes
and @23 maxStreamedResultRows are the budget of
AdapterDatabaseSession.executeStreamed / GuestDatabase.executeStreamed,
which answer the same atomic batch as single-segment ExecuteStreamFrames
so a result may exceed the scalar ceiling; 0 on both means the adapter
does not stream, and when set each is at least its inline counterpart. Backup orchestration must not
branch on sqlite/postgres/d1 plugin identity. First-party D1 advertises
neither flag (sequential HTTP is not a consistent image and is not complete
unit replacement). Native D1
export/import is not a Bookclerk backup path.
The host must not invent capabilities from the plugin id. Missing required
fields, atomicBatch: false, returning: false, unspecified (0) limits,
limits below the host's compiled minimums, or maxPayloadBytes /
maxRequestBytes / maxAtomicResultBytes above MAX_SCALAR_BYTES are a hard error. Wake
page size and IN (…) chunking are derived from maxBinds.
maxPayloadBytes bounds request SQL plus binds per statement and must not
exceed the scalar ceiling. maxLoweredStatementBytes (0 = unspecified) is
the adapter's physical statement ceiling after standardized Bookclerk
sqlite-family lowering (overflow wraps, LIKE→GLOB, NULLIF, INSERT OR IGNORE,
query LIMIT wrap, bytes-placeholder expansion). After the host has a real
ResolvedStatement, and before adapter dispatch, it compares that proven
upper bound to the advertised ceiling. The host must not branch on engine
identity. D1 advertises 100000 and still validates the same bound as
defense in depth. D1 maxPayloadBytes remains the largest canonical payload
whose mechanical-only lowering is proven to stay within that 100 KiB
ceiling. maxRequestBytes / maxAtomicResultBytes bound
the whole encoded ExecuteRequest / ExecuteReply. Guests track encoded
result bytes incrementally as statement results are built and keep one
exact pre-commit check.
First-party values: D1 maxBinds = 100, maxPayloadBytes = 25000,
maxFunctionArgs = 32, maxSchemaColumns = 100, maxPatternBytes = 16
(GLOB expansion headroom under D1’s 50-byte physical LIKE/GLOB cap). SQLite
and PostgreSQL report engine bind/function/column caps. Host minima stay
satisfiable by D1. Physical engines are an explicit enum (Sqlite |
Postgres | D1); unknown SeaORM backends (including MySQL) fail closed
— never _ => sqlite.
The host compiler emits canonical Bookclerk SQL (? placeholders,
SQLite-shaped helpers such as INSERT OR IGNORE, json_extract,
json_valid). Host semantic desugars (backend-independent) rewrite
unspecified ORDER BY to explicit NULLS FIRST/LAST and / % divisors
to NULLIF(x, 0) unless the divisor is already structurally
NULLIF(<expression>, 0). The normative grammar, types, helpers, result semantics, and
version policy live in docs/plugins/sql-contract/v1.md;
machine-readable vectors are under
crates/bookclerk-db-exec/testdata/sql_v1/. Adapter admission is “passes
Bookclerk SQL v1 conformance,” not affinity with SQLite/PostgreSQL identity.
Adapter SDKs lower placeholders, helpers, collation, and overflow at execute
time (bookclerk-db-exec::lower_canonical_sql_typed) using structured proofs
on AdapterDatabaseSession.execute. Optional plan choices may branch only on
semantic capabilities, not on plugin id or diagnostic engine identity.
sqlContractVersion versions are monotonic supersets; hosts require
>= SQL_CONTRACT_VERSION.
Canonical SQL text stays the public representation. Parser-library AST never
appears on Cap’n Proto, guest SDKs, or ExecuteRequest. A host-only spike
compared Syntaqlite 0.9 (Lemon C + Analyzer) and sqlparser-rs 0.59
(SQLiteDialect + Visitor) against the SQL-v1 corpus. Neither deletes a
Bookclerk-owned category of machinery: Syntaqlite physical_tables_accessed
omits DML destinations and is SQLite-typed; sqlparser-rs has no semantic
analysis, so TScan / TypeCx would remain. Admission stays the fail-closed
recursive-descent grammar. The comparison crate was removed after go/no-go;
independent wins that did land: full CREATE schema on ResolvedStatement,
SeaQuery for host catalog DML, and proof-directed authorization when a type
env exists.
sqlFamily and SeaORM dialect are bootstrap-only (typed DbBootstrap on
the plugin-host connect path). Typed DbCapabilities does not carry them
(@15 is pluginDatabases; @16–@19 are numeric caps including
maxLoweredStatementBytes; @20/@21 are backup flags). DbBootstrap.engine
is a diagnostic physical-engine name. The host never admits, rejects, or
generates SQL from engine — any string is valid. An architecture lint
(scripts/check-db-plugin-isolation.py) forbids bookclerk-library production
sources from reading bootstrap fields, calling physical lowering helpers,
inspecting SeaORM get_database_backend for SQL generation, or defining
planner-side SqlFamily. SeaORM proxy open maps bootstrap in
bookclerk-plugin-host after typed capability negotiation succeeds and
always uses the canonical SQLite-shaped query builder.
First-party connect wiring (DbConnectParams::{Sqlite,D1,Postgres}) injects
host-resolved paths and secrets for sqlite / d1 / postgres. That is a
convenience, not the contract: any other databaseAdapter plugin id receives
the public DatabaseAdapterConfig payload and must read connection settings
from plugin-owned config / secrets bindings. Missing semantic capabilities
fail closed; an unfamiliar diagnostic engine does not.
The data plane is typed AdapterDatabaseSession.execute(ExecuteRequest) -> ExecuteReply (granted job SQL uses GuestDatabase.execute). Every request is an ordered non-empty statement list
(batch-of-one for ordinary reads/mutations). Parameters and rows use
Cap'n DbValue (null(expectedType), bool, int64, float64, text,
bytes). Unknown union members fail closed as unsupported. Cursor is
result transport, not a second mutation primitive. Host-private interactive
begin lives on HostAdapterDatabaseSession for the first-party SeaORM
proxy (plugin_host.capnp); nested work uses AdapterTransaction.execute
on the open txn so it stays on the typed data plane without a second BEGIN.
The guest runs the statements as one SQL transaction (D1 HTTP
{ "batch": [...] }; SQLite/PostgreSQL BEGIN) and returns per-statement
rows / rowsAffected / timing. The host validates the envelope before
interpret_plan. A statement that yields more than maxResultRows fails
the plan (rollback / D1 ambiguous) rather than truncating. D1 HTTP cannot
roll back after the JSON body returns, so the guest refuses RETURNING
unless the host-IR maxRows is 1, the SQL string is a single statement
(no top-level ;), and any VALUES list is exactly one tuple. Overflow
or an oversized HTTP body after a committed batch is unavailable (replay
the same operationId); only a definitive non-retryable 4xx is permanent.
Receipt rows live in host-authored SQL against bookclerk_receipts.
Guests must not parse Bookclerk operation names or interpret receipts.
rowsAffected is uniform by kind: select is 0; returning is the
number of returned rows; execute is the engine change count.
Stable error categories come from SQLSTATE / rusqlite codes (not English
"unique" matching): constraint → conflict; serialization/busy/locked →
unavailable; timeout → deadline_exceeded; COMMIT-time I/O or lost HTTP
→ unavailable (retry the same operationId); unsupported SQL →
unsupported; syntax → invalid_params.
Cancellation and deadlines stay RPC/session-level (not a field on the SQL
plan). The host races in-flight typed execute against a cancel flag
(drop aborts the RPC). Guests may see an optional deadlineUnixMs on
ExecuteRequest (transport metadata; not hashed). Observed cancel/deadline
before BEGIN/HTTP or between statements is cancelled /
deadline_exceeded; around COMMIT / HTTP return is unavailable
(ambiguous).
Domain/store code must not branch on a concrete database type for
correctness. Serialization uses schema-based slot rows
(bookclerk_slots): INSERT the key, then UPDATE bump = bump + 1
to take a write lock. There are no PostgreSQL advisory locks or
engine-specific singleton-row dual paths; the job queue takes the same
slot lock as every other atomic unit.
JSON filter / catalog matching for event claim is evaluated in the host. The atomic mutation is a compare-and-set on a concrete delivery id.
A backend that cannot provide parameterized statements, reliable affected-row counts, atomic batch or interactive transactions, durable commit with replay-safe lost-response handling, or the negotiated bind limits is not loaded.
- First-party database plugins connect, advertise caps, and call the shared
adapter SDK. The host selects and applies
bookclerk_schema_migrationsafter capability negotiation (generic execute / one atomic batch; D1 schema apply is still one host-compiled HTTP batch). - An architecture lint forbids plugin and
bookclerk-db-guestproduction sources from importing Bookclerk migrations, embedding application table names, or interpreting named operations (DbAtomicParams,atomic_status,interpret_plan). The same lint forbidsbookclerk-libraryplanners and domain code from reading bootstrap-onlyengine, rewriting placeholders, calling adapter lowering (lower_canonical_*/realize_*_ddl/from_adapter_backend), inspectingget_database_backendfor SQL generation, or emitting engine catalog/isolation SQL. It also forbidssplit(';')on schema/SQL packs,_ => sqlitefallbacks, SeaORMDatabaseBackend::/DbBackend::, andPhysicalEnginein library production. Canonical?transport lives inbookclerk-db-exec(execute_canonical/canonical_statement); adapters own physical engines. Plugins may importbookclerk-db-execlowering and typed execute. - Equal performance across engines is not guaranteed.
- Integration plugins never receive database credentials or raw connections.
- Non-SQL backends remain unsupported by product policy, not by a missing adapter.
- The SQL-v1 lexer, grammar and adapter lowering are fuzzed per
Untrusted-input fuzzing (
sql_parse,sql_lower). Guest-SQL authorization against a real SQLite authorizer oracle, whose observed accesses must stay inside theGuestSqlPolicy, is fuzzed byguest_sql_authz(seedocs/contributing/fuzzing.md).
Source: docs/adr/sql-database-contract.md at main · View as Markdown