# Bookclerk SQL contract v1

This document is the **normative** definition of `sqlContractVersion = 1`.
BookclerkSQL is a **host-owned portable subset**, not “SQLite everywhere.”
Adapter admission is “passes Bookclerk SQL v1 conformance,” not “looks like
SQLite or PostgreSQL.” Guests speak only this grammar. Adapters alone lower
to SQLite, PostgreSQL 16+, or Cloudflare D1. Machine-readable vectors live under
[`crates/bookclerk-db-exec/testdata/sql_v1/`](https://github.com/Bookclerk/bookclerk/tree/main/crates/bookclerk-db-exec/testdata/sql_v1/)
and are consumed by `bookclerk-db-exec` lowering tests and library conformance.

## Version compatibility

- Contract versions are **monotonic supersets**: every statement form, type,
  and semantic guarantee in version *N* remains valid in version *N+1*.
- Guests advertise the highest version they implement as `sqlContractVersion`.
- Hosts require `sqlContractVersion >= SQL_CONTRACT_VERSION` (currently `1`).
- A **non-superset** redesign needs a new contract identity and a plugin ABI
  break (`apiVersion` / `plugin.toml` `api_version`). Do not increment this
  scalar and keep `>=` if N+1 would drop or change a guarantee of N.

## Grammar (canonical form)

Guest SQL is admitted in this order:

1. **SQL-v1 grammar** — positive/versioned Bookclerk SQL (not a SQLite dialect).
2. **Typed expressions** — the host builds exactly one resolved typed proof
   (`ResolvedStatement`) per canonical statement, bound to the SHA-256 of that
   exact SQL string (after host semantic desugars). Authorization, schema
   companions, result expectations, and adapter lowering consume that proof;
   none re-resolve names or types. Proofs travel on host→adapter
   `AdapterDatabaseSession.execute` (`AdapterExecuteRequest.proofs`).
   `GuestDatabase.execute` stays untrusted `ExecuteRequest` without proofs.
   The proof is typed against a durable binding catalog (`bookclerk_sql_catalog` /
   `bookclerk_sql_schema`) plus in-request `CREATE`/`DROP`. Unknown
   tables/columns fail closed. Empty catalog means “no plugin tables,” not
   “unknown → NULL.” `host_authoritative` skips **authorization** only;
   typing and TEXT lowering still run (from the canonical host schema or an
   equivalent already-resolved proof). New atomic batches snapshot catalog
   and physical types in autocommit **before** `BEGIN`, so SQLite does not
   hold a reserved lock across paged catalog loads. On a SQLite file the
   SQLite adapter remembers that autocommit snapshot and reuses it while one
   small key read (the file, `PRAGMA schema_version`, the catalog row count
   and the `bookclerk_sql_schema` rows) is unchanged, so a batch does not
   reload the catalog and reparse every table; any DDL, by any connection,
   changes the key. Nested typed `execute` on an already-open transaction
   still snapshots inside that transaction, never from the memo.
   `SchemaAction::Create` stores the full `CreateTableSchema` (ordered columns,
   SQL-v1 types, nullability, identity, PK/UNIQUE/defaults/CHECK/FKs) plus
   fingerprint and no-op. Catalog companions and Postgres identity objects
   consume that schema; they do not reparse canonical CREATE text. Host
   catalog INSERT/DELETE DML is built with SeaQuery (`SqliteQueryBuilder`)
   from the proof. Adapter-private catalogs are reserved names; guests cannot
   grant themselves `sqlite_master` / `information_schema` probes.
3. **Binding scope/security** — isolated schema, reserved-name, and ref checks
   using `proof.physical_accesses` and `proof.functions` (no second alias walk)
   when a type environment is present (binding-owned and library guests with
   catalog types). A projection wildcard (`SELECT *`, `alias.*`, `RETURNING *`)
   records each physical column plus `"*"` on that table; column-scoped grants
   reject the wildcard the same way the lexical authorizer does. `column: None`
   is table presence only (FROM / DML destination), not a star. Library guests
   without a type env still use lexical `parse_guest_sql_refs` so SQLite-only
   helpers remain authorizable without pretending they are portable SQL-v1 types.
4. **Capability/limit validation** — binds, result selection, negotiated caps.
5. **Canonical `ExecuteRequest`** — host wrap (receipts) without engine identity.
   Receipt wrappers get their own proofs bound to the wrapper SQL.
6. **Adapter-edge lowering** — at execute, against the isolated physical DB,
   reading TEXT collation sites from the proof (never `ident_is_text`).

Hosts emit **canonical Bookclerk SQL**. Host semantic desugars (not adapter
rewrites) make unspecified `ORDER BY` explicit (`NULLS FIRST`/`LAST`, SQLite
default) and wrap `/` `%` divisors as `NULLIF(x, 0)`. Adapters lower to the
engine at execute time. Domain planners must not branch on engine identity.

- **DML and queries** — `bookclerk-db-exec::lower_canonical_sql_typed`
  (placeholders, `IFNULL`, `json_extract`, `INSERT OR IGNORE`, 2+-arg
  `min`/`max`, `json_valid`, `round`/`sum`/`avg` wire types, `LIKE`→`GLOB`,
  proof-directed `COLLATE "C"` and INTEGER overflow). Default NULLS are
  **not** adapter rewrites.
- **DDL types and identity** — adapter execution edge
  (`realize_host_ddl` / binding DDL helpers).
  The generic DML pass does not classify statements as DDL.
- **`INSERT OR IGNORE … SELECT` / `WITH`** — adapters wrap the row source as
  `SELECT * FROM (<source>) AS bookclerk_src WHERE true` before `ON CONFLICT DO
  NOTHING` so SQLite/D1 UPSERT is unambiguous. `VALUES` is not wrapped.
  `bookclerk_src` is reserved like every `bookclerk_`-prefixed identifier.

### Placeholders

- Positional binds use bare `?` only (1-based order of appearance in code spans).
- `$n`, `?NNN`, and other engine-specific bind spellings are outside SQL v1
  and are rejected for every guest before adapter dispatch.
- Placeholders inside string/identifier literals, line/block comments, and
  PostgreSQL dollar-quotes are not binds (adapters must preserve them).

### Statement forms

Supported top-level forms for guest-authorized and host-authored plans
(fail-closed recursive-descent grammar, not a denylist):

| Form | Notes |
| --- | --- |
| `WITH` CTEs + `SELECT …` | CTE bodies are `SELECT` only (no nested DML). `DISTINCT` is admitted; `DISTINCT ON` is not. Mixed aggregate + non-aggregate select lists are SQLite-shaped and **not** in this contract; keep aggregates in their own statement (or `GROUP BY`). |
| `SELECT` clauses | `FROM` / `JOIN` (`INNER` / `LEFT` / `CROSS`; not `RIGHT` / `FULL`), `WHERE` / `GROUP BY` / `HAVING` / `ORDER BY` [`ASC`\|`DESC`] [`NULLS FIRST`\|`LAST`] / `LIMIT` / `OFFSET`, `UNION [ALL]` (not `EXCEPT` / `INTERSECT`). |
| `INSERT INTO …` / `INSERT OR IGNORE INTO …` | Optional `RETURNING` |
| `UPDATE …` / `DELETE …` | Optional `RETURNING` |
| Schema DDL | Host-only for library and session guests. `GuestSqlPolicy::binding_owned` admits idempotent `CREATE`/`DROP` `TABLE`/`INDEX` `IF [NOT] EXISTS` on a physically separate plugin database. Binding helpers are this contract (not a wider SQLite dialect). Postgres adapters lower canonical types (`INTEGER PRIMARY KEY AUTOINCREMENT` → `BIGINT PRIMARY KEY` plus a transactional identity trigger, `BLOB` → `BYTEA`, case-insensitive) at their execute edge. |

Expression operators (closed): `=` `<>` `!=` `<` `>` `<=` `>=`, `AND` `OR` `NOT`,
`IS [NOT] NULL`, `IN`, `BETWEEN`, `LIKE` (not `ILIKE`), `\|\|` `+` `-` `*` `/` `%`,
`CASE`, `CAST(x AS INTEGER\|REAL\|TEXT\|BLOB\|BOOLEAN)`, policy-allowed function
calls, `.` qualification, `?`, literals. Rejected for every guest: `::`,
`ILIKE`, `SIMILAR`, `OVER`, `FILTER`, `GLOB` / `MATCH` / `REGEXP`. `COLLATE` is rejected except `COLLATE NOCASE` on an `ORDER BY` key or a `CREATE INDEX` key (the library title-order fold).

`INSERT OR IGNORE` is canonical and means **unique / primary-key conflict
only**. `NOT NULL` and `CHECK` still abort on every backend. Adapters lower
the form to `INSERT … ON CONFLICT DO NOTHING` (conflict clause before
`RETURNING`) for SQLite, D1, and Postgres — SQLite `OR IGNORE` would otherwise
swallow `NOT NULL`. When the source is `SELECT`/`WITH`, the wrap above is
required. `REPLACE INTO`, `INSERT OR REPLACE` / `OR ABORT` /
`OR FAIL` / `OR ROLLBACK` are outside SQL v1 and are rejected before adapter
dispatch.

Binding `CREATE TABLE` / `CREATE INDEX` types are `INTEGER`, `REAL`, `TEXT`,
`BLOB`, and `BOOLEAN` only. `AUTOINCREMENT` is valid **once**, immediately
after `PRIMARY KEY`, and only when the column type is `INTEGER` (`INTEGER
PRIMARY KEY AUTOINCREMENT`). `INTEGER AUTOINCREMENT`, `REAL AUTOINCREMENT`,
and duplicate `AUTOINCREMENT` are rejected. `BOOL` is not an alias. Tails
such as `STRICT`, `WITHOUT ROWID`, `JSONB`, `NUMERIC(p,s)`, `VARCHAR(n)`,
`CASCADE` / `RESTRICT` on `DROP`, and backend-specific index clauses
(`USING`, `INCLUDE`, and column `COLLATE` other than `COLLATE NOCASE` on an index key) are rejected. Adapter lowering of
type/identity tokens and `RETURNING` is case-insensitive in code spans (the
host write-gate constant is still matched exactly).

Identity after `INTEGER PRIMARY KEY AUTOINCREMENT` is SQLite-equivalent:
after an explicit id insert, the next omit-id is `max(committed id)+1` and
deleted ids are not reused. Explicit identity values are admitted. Postgres
adapters do **not** use `BIGSERIAL`/`setval` (those do not roll back with the
`ExecuteRequest`). They keep a transactional `BIGINT` high-water row in
adapter-private `bookclerk_identity` and a `BEFORE INSERT` trigger generated
from the **parsed** identity column name. `DROP TABLE` deletes that table's
catalog and identity rows in the same atomic request and drops
`bc_if_<digest>()`; it does **not** emit `DROP TRIGGER … ON` after the
relation is gone. Recreate starts at 1.
SQLite/D1 keep native `AUTOINCREMENT`. Guests cannot name `bookclerk_identity`
or `bookclerk_sql_catalog`.

### Identifiers and quoting

- Identifiers are canonical **unquoted** names: `[A-Za-z_][A-Za-z0-9_]*`,
  **≤ 63 bytes after case fold** (PostgreSQL `NAMEDATALEN-1`). 63 accepted;
  64 rejected. Quoted / mixed-case object names (`"Foo"`, `` `Foo` ``, `[Foo]`)
  are rejected. Adapter-private Postgres identity objects use hash-based names
  with separate prefixes: `bc_if_<digest>` (function) and `bc_it_<digest>`
  (trigger), 16 hex digits of SHA-256 of the folded table name, always under
  63 bytes. Two canonical names that would collide under Postgres 63-byte
  truncation produce distinct generated object names.
- Unquoted names fold to lowercase in authorization (`guest_sql`); adapters
  must not reinterpret authorization. `CREATE TABLE Foo` and `SELECT … FROM
  foo` name the same object on every admitted backend.

## Types and `DbValue`

| Wire (`DbValue`) | Canonical SQL expectation |
| --- | --- |
| `null(expectedType)` | SQL `NULL` with typed metadata |
| `bool` | Canonical SQL `BOOLEAN` (not INTEGER 0/1 storage leaking onto the wire) |
| `int64` | Exact 64-bit integer (not JSON number) |
| `float64` | IEEE float |
| `text` | UTF-8 text: Unicode scalar values excluding U+0000 |
| `bytes` | Opaque byte string (NULs allowed) |

Unknown Cap'n union members fail closed as `unsupported`.

### Portable TEXT

BookclerkSQL `TEXT` is Unicode scalar values on the UTF-8 wire, excluding
U+0000. UTF-8 is the encoding, not a smaller repertoire. The host rejects NUL
in parser literals, `DbValue::Text` binds/results, and catalog TEXT cells
**before** adapter dispatch, including SQLite. `BYTES`/`BLOB` may contain
`0x00`. PostgreSQL adapters require `server_encoding` / `client_encoding`
UTF8 (or a strict UTF-8 equivalent) at connect; SQL_ASCII / LATINn fail
closed.

### Advertised numeric limits

Host admission enforces advertised `DbCapabilities` before dispatch. Adapters
must not over-advertise a physical engine limit they cannot hide. A guest
reads the caps its session negotiated with `GuestDatabase.limits` (the
binding's `limits()` in every SDK) and sizes requests and reads before
sending; exceeding one fails with the typed `limitExceeded` error, never a
truncated result.

| Field | Meaning |
| --- | --- |
| `maxBinds` / `maxStatements` / `maxPayloadBytes` | Binds, statements, and canonical SQL+binds per statement |
| `maxResultRows` / `maxResultBytes` / `maxCellBytes` | Result envelope (fail closed, never truncate) |
| `maxRequestBytes` / `maxAtomicResultBytes` | Whole encoded request / reply |
| `maxStreamedResultBytes` / `maxStreamedResultRows` | Whole reply and rows per statement when the result is read as a stream (`executeStreamed`); `0` on both means the adapter does not stream. When set, each is at least its inline counterpart and at most the host maximum (64 MiB, 250 000 rows), since the host holds the reply while it reassembles it |
| `maxFunctionArgs` | Physical vararg ceiling after adapter hiding. Counts the arguments of one function call; the members of an `IN` list, a `VALUES` row, a column list or a subquery are not arguments. Hosts require at least 32 (the portable `json_object` maximum) |
| `maxSchemaColumns` | Columns per `CREATE TABLE` / result width |
| `maxPatternBytes` | UTF-8 bytes of a `LIKE` pattern value (literals and TEXT binds). Sqlite-family GLOB expansion is 3× for `[` `*` `?`; D1 advertises 16 so expanded GLOB stays ≤ 50 |

### Streamed results

`AdapterDatabaseSession.executeStreamed` and `GuestDatabase.executeStreamed`
run the same atomic batch as `execute` but answer it as a sequence of
`ExecuteStreamFrame` messages instead of one `ExecuteReply`, so a result
may grow to `maxStreamedResultBytes` / `maxStreamedResultRows` instead of
the inline caps. The batch still commits (or rolls back) whole before the
first frame is sent; streaming changes how the result crosses the wire,
not its atomicity.

Each frame is one single-segment Cap'n Proto message, at most 256 KiB
unless it carries a single larger row. The sequence is `head`
(`operationId`, statement count), then per statement a `statement` frame
(index, columns, `rowsAffected`) followed by zero or more `rows` frames,
then `end` with the timing, or `err` with a `PluginError` at any point.
The reply is `inline` bytes when the frames fit in one scalar, else a
`ByteSource`. A receiver decodes frame by frame, holds the raw bytes to
`maxStreamedResultBytes` plus framing slack, and fails closed with
`limitExceeded` (`maxStreamedResultRows` / `maxStreamedResultBytes`) as
soon as a budget is passed, so an oversized result never lands whole.

The whole-reply budget (`maxAtomicResultBytes`, or `maxStreamedResultBytes`
for a streamed batch) is also enforced while the batch runs, not only on the
encoded reply: an adapter holds every statement to the smaller of
`maxResultBytes` and what is left of the whole-reply budget, so a batch of
individually admissible results fails with `limitExceeded`
(`maxAtomicResultBytes`) at the statement that would exceed it, and the
remaining statements never run or occupy memory.

The host reads every standalone library and binding batch through
`executeStreamed` when the adapter streams, and the SDKs read a guest's
`all()` and `iterate()` the same way over the granted channel's
`POST /db/execute-streamed` (see
[Working within database limits](/docs/plugins/database-limits/index.md)). An adapter that
does not stream answers both methods with `unsupported`. An isolate
(workerd) adapter's streamed reply travels inline through the launcher, so
its frames must fit one scalar: the SDK clamps such an adapter's advertised
`maxStreamedResultBytes` to 256 KiB so the host's budget is one the
adapter can hold to before it commits, and frames past one scalar come
back as `limitExceeded` (`maxScalarBytes`). First-party sqlite and
Postgres advertise 64 MiB and 250 000 rows; D1 advertises 8 MiB and
50 000 rows, and reads its HTTP responses under a body ceiling sized for
the budget of the attempt.

### Isolate adapter batches and replay receipts

A guest batch reaches an adapter wrapped in a replay receipt: a prune
`DELETE` and a prior-receipt `SELECT` come first, every guest DML write
is gated on the receipt being absent, and a stub `INSERT` claims the
receipt last (`AdapterExecuteRequest.receipt` gives the guest statement
count as `guestLen` and the guest request's `requestHash` as
`guestHash`). Guest DDL carries no receipt predicate: the host admits only
idempotent DDL (`CREATE`/`DROP` … `IF [NOT] EXISTS`), and the SDK helpers
skip all remaining guest work, DDL included, once the prior-receipt
`SELECT` shows a completed or conflicting receipt. The adapter stores the
guest reply on that receipt before `COMMIT`, so a retry with the same
`operationId` after a lost reply replays the recorded result instead of
running the writes again.

Isolate adapters get this from the SDKs instead of writing it themselves:

- TypeScript: `executeAdapterBatch` and `executeAdapterBatchStreamed` from
  `@bookclerk/plugin-sdk/workerd`.
- Python: `execute_adapter_batch` and `execute_adapter_batch_streamed` from
  `bookclerk_plugin_sdk.db_value`.

The adapter supplies a `begin` that opens a transaction with `run`,
`commit` and `rollback`. The helper runs the wrapped statements, skips the
guest work a completed prior receipt already covers, runs the finalize
`UPDATE` that stores the reply (or the read-only marker for a batch of
`SELECT`s), and commits. When the prior-receipt `SELECT` found nothing
but the stub claimed nothing either, another attempt committed the
receipt in between; the helper rolls back with `unavailable` so the
host's retry replays that receipt. The receipt records the measured
timing, which a replay answers with. A result cell the host would refuse (a non-finite
`float64` or TEXT with U+0000) rolls the batch back with `invalidParams`.
The streamed variant takes the streamed budget the adapter advertises
(`executeStreamLimits(caps)` / `execute_stream_limits(caps)`). It encodes
the frames inside the transaction, stops with `limitExceeded`
(`maxScalarBytes`) as soon as they pass one scalar, decodes them under
that budget the way the host does, and holds the encoded `ExecuteReply` to
the byte budget itself (the decoder allows framing slack past it). Any
failure rolls back, so a reply the host would refuse never commits. A write whose reply is over the
64 KiB replay payload also rolls back, with `internal`. The SDK tests run both helpers on SQLite against a
receipt-wrap vector the host generates
(`crates/bookclerk-library/fixtures/guest_receipt/isolate_adapter.json`).

`json_object` even arity 2–32 is portable (16 key/value pairs). That matches
D1’s physical `maxFunctionArgs`. Sqlite-family adapters must not nest
`json_patch` to admit more pairs: JSON Merge Patch deletes null values and
changes duplicate-key behavior. Scalar `min`/`max` / `coalesce` are already
≤ 32 in the helper table. Postgres `json_build_object` does not need nesting
and is cast to TEXT (`(json_build_object(…)::text)`) except when a nested
`json_object` is a constructor value (that call stays `json` so it nests as
an object). Unproven `LIKE` pattern expressions fail closed when `maxPatternBytes` is
finite. Do not use `PRAGMA case_sensitive_like`.

### NULL and coercion

- Typed null carries `expectedType` for empty-result metadata and decode.
- Adapters must not collapse distinct Cap'n types through JSON number/array
  shims on the granted Cap'n path.
- **`CAST` matrix (proven only):** same-type `CAST`; `INTEGER` ↔ `REAL` when
  the conversion is proven; typed `NULL` to any admitted type. Rejected:
  `TEXT`→`INTEGER`/`BOOLEAN`/`BLOB` (including `CAST('1' AS INTEGER)`),
  `BLOB`→`TEXT`/`INTEGER`, `BOOLEAN`↔numeric, and any untested coercion.
  Store the value in the destination type instead.
- Unification (same rules on every backend): `IFNULL`/`COALESCE`/`NULLIF`/
  `CASE`/`UNION` arms, comparisons, scalar `min`/`max`, arithmetic
  (`INTEGER+REAL` → `REAL`). `sum(x)` requires `INTEGER`. `avg`/`round`
  require numeric. `lower`/`upper`/`LIKE` require `TEXT`.
- **BOOLEAN contexts** (`BOOLEAN | NULL` only): `WHERE` / `HAVING` /
  `JOIN … ON` / `DELETE`/`UPDATE` WHERE, searched `CASE WHEN <pred>`,
  `NOT` / `AND` / `OR` operands, and `CHECK (…)` on binding `CREATE TABLE`.
  `SELECT 1 WHERE 1` is rejected. Simple `CASE expr WHEN v` unifies `v` with
  `expr`.
- **Assignment sites:** `INSERT` destination list is the explicit column list
  or catalog declaration order. `VALUES` **and** `SELECT`/`WITH` must match
  arity and unify with each dest column. `UPDATE … SET col = rhs` uses the
  same dest-typing rule. Guest v1 does not admit `ON CONFLICT … DO UPDATE SET`
  (canonical form is `INSERT OR IGNORE` → adapter `ON CONFLICT DO NOTHING`).
- **Operators:** `+` `-` `*` `/` are INTEGER/REAL/NULL (result REAL if any
  REAL). `%` is INTEGER/NULL only (REAL is not portable). `||` / `LIKE` are
  TEXT/NULL. Comparisons unify both sides.
- **Helpers (typed arguments):** `replace(TEXT, TEXT, TEXT) → TEXT`;
  `substr(TEXT, INTEGER [, INTEGER]) → TEXT`; `round(numeric [, INTEGER]) →
  REAL`; `lower`/`upper`/`trim` TEXT-ish; `length` TEXT|BLOB; `json_extract`
  TEXT path; `sum` INTEGER-only. Unknown helper names are grammar-denied;
  typecheck does not `Ok(NULL)` for unknown calls.
- **Derived names / CTEs:** visible FROM keys are `AS alias` (or the table
  name). `WITH c(x, y) AS (…)` honors explicit CTE output names. Recursive
  CTEs infer the **anchor** first, publish those concrete types into
  self-scope, then typecheck recursive arms and unify every arm with the
  anchor. Anonymous or duplicate derived columns are rejected when they must
  be addressable (`SELECT 1` in a CTE later qualified, `AS (a, a)`,
  expression columns without `AS`). Top-level `SELECT` lists may omit `AS`
  (cells are positional). Postgres labels those items `?column?`; the
  adapter uniquifies colliding labels. Explicit duplicate aliases (`SELECT x, x`)
  still fail closed. Correlated subqueries resolve qualified
  aliases against enclosing FROM maps (nested `EXISTS` may name `i.user_id`
  after `FROM portal_identities i`).
- **Schema fingerprint:** `CREATE TABLE IF NOT EXISTS` is keyed on a
  structured schema IR (column order/names/types, identity, DEFAULTs, and
  admitted constraints — not normalized CREATE text). Catalog has the table
  and the fingerprint matches → no-op (no catalog/identity companions).
  Fingerprint differs → reject. Catalog lacks the table → INSERT catalog/
  schema rows (no `ON CONFLICT DO UPDATE`); identity companions only on this
  path. Physical reconciliation on SQLite, D1, and PostgreSQL fail-closes:
  catalog absent + physical exists (do not adopt orphans), catalog present +
  physical missing, or catalog vs physical column IR disagree. D1 uses the
  same `sqlite_master` fingerprint path as SQLite. Catalog load is fail-closed
  (`Result`): RPC/adapter/shape errors are `Err`; missing catalog objects are
  `Ok(empty)`. Rows page by keyset `(table_name, ordinal)` at
  `min(negotiated maxResultRows, FIRST_PARTY_MAX_RESULT_ROWS)` with the
  binding cancel and deadline forwarded. Malformed cells / unknown `sql_type`
  fail closed.
- **Runtime semantics (portable):** `/` and `%` by zero yield NULL. The host
  rewrites the divisor as `NULLIF(x, 0)` unless it is already structurally
  `NULLIF(<expression>, 0)`. A `NULLIF` whose second argument is not `0`
  (for example `NULLIF(0, 1)`, which evaluates to `0`) still gets an outer
  guard. Adapters must not repeat this rewrite. INTEGER `+` `-` `*` and `abs` in
  DML/query SQL that would overflow i64 yield NULL (proof-directed `CASE`
  wraps; guests must not depend on wraparound or a backend error). Overflow
  wrapping is **not** applied to `CREATE TABLE` `CHECK` expressions; INTEGER
  `+` `-` `*` `abs` in `CHECK` is rejected at admission. `round` halfway rounds
  away from zero (`round(1.5)=2`, `round(2.5)=3`) via Postgres `NUMERIC`.
  `substr` is 1-based; portable vectors use start ≥ 1 and length ≥ 0.
  Negative/`NULL` `substr` indexes are outside the portable claim. Postgres
  `substr(text, int, int)` does not accept bigint, and a bound INTEGER
  parameter is bigint, so lowering clamps the start and length into the
  `int4` range and then casts them to `INTEGER`. A length above 2³¹−1 still
  means the rest of the string. The argument is evaluated once, and NULL stays
  NULL. SQLite keeps `substr` unchanged. `length` of
  TEXT is characters; `replace` / `lower` / `abs` of values in range follow
  engine defaults locked by shared vectors.
  Host dispatch-latency SQL may call `julianday(TEXT) → REAL`; Postgres
  lowering rewrites that exact expression to `EXTRACT(EPOCH …)`. Guests
  cannot call `julianday` (not in the binding helper allowlist).
- **`DEFAULT`** must match the declared column type: integer literals for
  `INTEGER`, numeric for `REAL`, strings for `TEXT`, `X'hex'` for `BLOB`
  (kept in v1; Postgres DDL lowering rewrites `DEFAULT X'deadbeef'` to
  `DEFAULT decode('deadbeef', 'hex')` after `BLOB`→`BYTEA`), `TRUE`/`FALSE`
  for `BOOLEAN` (not `1`). Parenthesized `DEFAULT (…)` is rejected unless the
  inner expression is a matrix-legal `CAST`.
- Admitted helper signatures already land in the universal `DbValue` domain
  (`round` → `Float64`, integer `sum`/`count` → `Int64`, `avg` → `Float64`);
  callers are not required to `CAST` for those shapes. Other computed numerics
  that would be engine `NUMERIC` should still `CAST` to `INTEGER` or `REAL`.
- Conformance vectors compare **typed** `DbValue` cells (`Int64` is not
  `Float64(n)` or `Text("n")`). SQLite expression columns may still be
  `DbType::Unspecified`; Postgres OID mapping should be `Int64` / `Text`.

## Helpers (canonical)

| Canonical | Arity | Wire | Meaning |
| --- | --- | --- | --- |
| `IFNULL(a, b)` | 2 | (inputs) | First non-null |
| `json_extract(col, '$.path')` | 2 | text | JSON path extract |
| `json_object(k, v, …)` | even 2–32 | text | Object constructor (key/value pairs). Portable maximum is 16 pairs (32 arguments), matching D1 `maxFunctionArgs`. Adapters must not emulate larger calls with `json_patch`. |
| `json_valid(expr)` | 1 | int64 0/1 | Predicate (`= 0` / `= 1`) |
| `min(a, b, …)` / `max(a, b, …)` | 2+ | (inputs) | Scalar least/greatest. Any NULL argument yields NULL (NULL-poison). One-arg `MIN`/`MAX` stay aggregates. |
| `round(x)` / `round(x, n)` | 1–2 | float64 | Round; Postgres `CAST`s through `NUMERIC` onto `DOUBLE PRECISION`. |
| `avg(x)` | 1 | float64 | Average |
| `sum(x)` | 1 | int64 | Integer aggregate (REAL-sum portability is not claimed) |
| `count(*)` / `count(x)` | 1 | int64 | Count |

Wrong arity is rejected before dispatch, including `IFNULL`/`COALESCE`/`NULLIF`
(`2` / `2+` / `2`), `abs`/`sum`/`avg`/`count`/`length`/`lower`/`upper`/`json_valid`
(`1`), and even-arity `json_object` (`2`–`32` arguments, 16 key/value pairs). Postgres lowering
maps `IFNULL` / `json_*` / 2+-arg `min`/`max` to `COALESCE`, `jsonb` operators,
`(json_build_object(…)::text)` (nested `json_object` values stay `json` so they
nest as objects), `IS [NOT] JSON`, and `LEAST`/`GREATEST` with an explicit
NULL guard (`CASE WHEN a IS NULL OR b IS NULL … THEN NULL ELSE LEAST/GREATEST(…)
END`). SQLite/D1 execute those canonical forms directly after the unique-conflict
`INSERT OR IGNORE` rewrite. `MAX(attempt_count, 1)` in host plans is the same
2-arg scalar form.

Plugin-owned database bindings (`GuestSqlPolicy::binding_owned`) authorize
these helpers plus portable scalars (`abs`, `cast`, `coalesce`,
`length`, `lower`, `nullif`, `replace`, `substr`,
`trim`, `upper`). `hex` is not portable (PostgreSQL has no `hex()`); it remains
available to library guests. SQLite-only names (`strftime`, `typeof`, `group_concat`,
`iif`, `instr`, `quote`, `total`, `date`, `datetime`, `time`, `json_array`)
are denied so a binding cannot depend on a dialect the contract does not
guarantee. See [Isolated plugin database bindings](/docs/plugins/abi/index.md#isolated-plugin-database-bindings).

### `ORDER BY`

Default NULL ordering matches SQLite: NULL sorts as **smallest** (`ASC NULLS
FIRST`, `DESC NULLS LAST`). The host desugars omitted NULLS into explicit
clauses before typecheck. Explicit `NULLS FIRST` / `NULLS LAST` is admitted
and left unchanged. Adapters must not repeat this rewrite.

Host library pages order titles with `COLLATE NOCASE` (ASCII case-fold, then code points). That spelling is admitted on `ORDER BY` and `CREATE INDEX` keys. Any other collation name is rejected. Postgres lowering rewrites the fold to `lower(ident COLLATE "C")` only in those key lists. A `WHERE` predicate that still contains `COLLATE NOCASE` is left unchanged.

Postgres `CREATE INDEX` keys each receive `NULLS FIRST`, matching the host `ORDER BY` desugar (NULL sorts as smallest). The check is the keyword `NULLS`, so an identifier such as `nulls_flag` still receives the clause. `CREATE INDEX IF NOT EXISTS` does not alter an index that already exists, so a database created before this lowering can keep a different NULL order until that index is rebuilt.

`lower` and `upper` of a TEXT column, a string literal, or a direct `?` argument whose bind is TEXT or untyped are `COLLATE "C"` sites. A nested call (`lower(substr(body, ?, ?))`) and a parenthesized subquery do not collate their placeholders. An INTEGER, REAL, BLOB, or BOOLEAN bind is not a collate site, so Postgres does not see `COLLATE` on bigint. Postgres `lower` uses the argument collation's `LC_CTYPE`; without `"C"`, a UTF-8 database locale folds `É` to `é`, which SQLite does not. A direct TEXT placeholder lowers to `lower(($1 COLLATE "C"))`. The books expression index lowers to `lower((uuid COLLATE "C"))` on Postgres so the predicate matches the index. `IF NOT EXISTS` leaves an already-created `lower(uuid)` index unchanged until it is rebuilt. SQLite keeps `lower(uuid)`.

A TEXT column compared with `=` to a placeholder, or with `IN` to a list of placeholders only (`uuid IN (?, ?)`), stays bare so the btree collation matches the `UNIQUE(uuid)` index. A literal in that comparison or list is still collated. Postgres DDL (`lower_canonical_ddl_to_postgres` and binding schema batches) fails closed when a `CREATE INDEX` `COLLATE NOCASE` tie-break or a `lower`/`upper` column argument cannot be typed from the catalog or an earlier `CREATE TABLE` in the same batch. An untyped key is not emitted as a different index. Query entry points (`lower_canonical_sql`, `lower_canonical_to_postgres`) stay infallible: an untyped index key sent through them remains bare.

TEXT `ORDER BY` is binary / code-point order. Postgres lowering applies
`COLLATE "C"` only at TEXT expression spans recorded on the statement proof
(comparisons, scalar `min`/`max`, `ORDER BY` keys, `LIKE` / `NOT LIKE`,
`lower`/`upper`). INTEGER/REAL/BOOLEAN/BLOB expressions never receive
`COLLATE "C"`. `json_extract` path string arguments are omitted from the
proof (so the path stays a bare literal for helper lowering); a comparison
or `ORDER BY` on a `'$.…'` string still receives `"C"`. Host-authored
`ORDER BY` TEXT uses the same annotations. SQLite/D1 need no COLLATE rewrite
(BINARY default).
The conformance suite uses ASCII and non-ASCII vectors. Locale-sensitive
Unicode order is outside v1.

Canonical `LIKE` is **case-sensitive**. Grammar keeps `LIKE` and still
rejects `ILIKE` and `ESCAPE`. SQLite/D1 adapters rewrite `LIKE`/`NOT LIKE` to
`GLOB` with bind-safe `replace` (escape `[` `*` `?`, then `%`→`*` `_`→`?`).
Do not use `PRAGMA case_sensitive_like`. Guests cannot write `GLOB`.

## Result semantics

- **`rowsAffected`**: mandatory (`affectedRows` capability). Reports rows
  changed by DML; `INSERT OR IGNORE` / `ON CONFLICT DO NOTHING` that insert
  zero report `0`.
- **`RETURNING`**: mandatory (`returning` capability). Column order and names
  come from engine metadata; empty results still expose column metadata.
- **Transactions**: one `ExecuteRequest` is one atomic batch. Failure before
  commit rolls back all statements in the request.
- **Errors**: stable categories (`conflict`, `deadline`, `invalidParams`,
  `internal`, …). Ambiguous commit / lost response uses durable receipt replay
  of the original `ExecuteReply`. A batch that only reads stores a receipt
  without its rows and re-runs the reads on replay, so a read-only result
  is bounded by the result budget it was read under, not by the 64 KiB
  replay payload. A request or result over a negotiated cap
  is `limitExceeded`: its message is `limit exceeded: <cap> is <n>; used <m>`
  and its `details` JSON carries `{ "limit", "cap", "used" }`, so callers
  branch on the cap name instead of matching text. Every emitter (adapter
  result capping, host admission, reply validation, the SDK request-size
  check) uses that one shape.
- **Bounds**: `maxResultRows`, `maxResultBytes`, `maxAtomicResultBytes`,
  `maxRequestBytes` fail closed before commit / dispatch; a streamed read
  fails closed at `maxStreamedResultRows` / `maxStreamedResultBytes`
  instead. `maxLoweredStatementBytes`
  (`0` unspecified) is compared to a proof-aware sqlite-family lowering upper
  bound after `ResolvedStatement` exists. D1 advertises `100000` for that
  ceiling; `maxPayloadBytes` remains a proven canonical cap so mechanical-only
  lowering of an admitted statement stays within 100 KiB.

## Conformance admission

A new SQL adapter is admitted when it:

1. Negotiates `DbCapabilities` with `sqlContractVersion >= 1` and required
   semantic flags/limits (not by matching a plugin id or diagnostic `engine`).
2. Executes the **sql_v1** corpus (lowering + shared behavioral vectors) without
   host library changes keyed on adapter identity.
3. Keeps `sqlFamily` / `dialect` on `DbBootstrap` (host-private bootstrap
   metadata) only — not on typed `DbCapabilities`. `DbCapabilities` `@17` is
   `pluginDatabases`, `@18`–`@20` are numeric caps, `@21` is
   `maxLoweredStatementBytes`, and `@22`/`@23` are `consistentBackupRead` /
   `atomicUnitRestore`. Reports any diagnostic `engine` string on `DbBootstrap`;
   the host always emits canonical `?` SQL. Architecture lint forbids planner
   reads of bootstrap fields and host calls to physical lowering helpers.
