# database > Talk to PostgreSQL, MySQL, and SQLite from iii — query, execute, transactions, prepared statements, and change feeds. | field | value | |-------|-------| | version | 0.5.17 | | type | binary | | license | Apache-2.0 | | repo | https://github.com/iii-hq/workers | | supported_targets | aarch64-apple-darwin, x86_64-pc-windows-msvc, aarch64-pc-windows-msvc, x86_64-unknown-linux-gnu, aarch64-unknown-linux-gnu, x86_64-unknown-linux-musl, armv7-unknown-linux-gnueabihf | | author | iii | ## installation ```sh iii trigger compose::add worker=database@0.5.17 ``` ## readme # database > Connect to PostgreSQL, MySQL, and SQLite. Run queries, prepared statements, transactions, and subscribe to row-level change feeds. | field | value | |-------|-------| | type | binary | | supported_targets | x86_64-apple-darwin, aarch64-apple-darwin, x86_64-unknown-linux-gnu, aarch64-unknown-linux-gnu | | author | iii | ## Install ```sh iii trigger compose::add worker=database ``` ## Skills Install the `database` agent skill for Claude Code, Cursor, and 30+ other agents: ```bash npx skills add iii-hq/workers --skill database ``` Browse or install every worker skill at once: ```bash npx skills add iii-hq/workers --list npx skills add iii-hq/workers --all ``` ## Configure Runtime settings live in the **`configuration` worker** under id **`database`**. The worker registers its JSON Schema at startup, reads the live value via `configuration::get`, and hot-reloads connection pools when the value changes. Persisted values default to `./data/configuration/database.yaml` (fs adapter). Edit that file directly or call `configuration::set` — both propagate without a worker restart. ### Zero-config default With no seed file and no stored configuration value, the worker uses a built-in default: ```yaml databases: primary: url: sqlite:./data/iii.db pool: max: 10 idle_timeout_ms: 30000 acquire_timeout_ms: 5000 ``` This is seeded into the `configuration` worker on first register and used as a runtime fallback when the stored value is `null`. ### Optional seed file Pass `--config ` to supply a YAML seed file. When present, its `databases` block is passed as `initial_value` on `configuration::register` (overriding the built-in default for first-time registration). See [`config.yaml.example`](config.yaml.example). Engine-managed deployments can inline config under the worker entry; the engine delivers it via `--config` as before. ### Value shape SQLite is the recommended starting point — no server, just a file: ```yaml databases: primary: url: sqlite:./data/iii.db pool: max: 10 idle_timeout_ms: 30000 acquire_timeout_ms: 5000 analytics: url: ${ANALYTICS_URL:postgres://localhost/analytics} pool: { max: 5 } history_max_entries: 200 # console query history caps, defaults shown history_max_bytes: 262144 # 0 disables recording ``` `history_max_entries` / `history_max_bytes` cap the per-database console query history stored on the [`state`](../state) worker — whichever cap hits first, oldest entries are dropped. Applied live. Set or replace the whole value: ```bash iii trigger configuration::get id=database iii trigger configuration::set id=database value='{"databases":{"primary":{"url":"sqlite:./data/iii.db"}}}' ``` Env placeholders use **`${VAR:default}`** syntax. The configuration worker expands them on every `configuration::get` call, so env changes propagate without a restart. URL scheme picks the driver: `sqlite:`, `postgres://`, `postgresql://`, `mysql://`. ### Hot reload When configuration changes (`configuration::set`, or an external edit to `./data/configuration/database.yaml`), the worker rebuilds connection pools in place. Invalid configs are rejected and the previous pools are kept. In-flight prepared-statement handles and open transactions continue on their original pool until they expire. ### TLS (postgres + mysql) Postgres and mysql connections default to **`tls.mode: require`** — TLS handshake required, certificate chain validated against the system trust store, hostname verification skipped (matches libpq's `sslmode=require`). Override per-database: ```yaml databases: primary: url: postgres://app@db.example.com:5432/app tls: mode: verify-full # disable | require | verify-full (default: require) ca_cert: /etc/ssl/internal-ca.pem # optional; extends the system trust store trust_native: true # default true; set false to trust only ca_cert local: url: postgres://dev@localhost:5432/dev tls: mode: disable # plaintext, local development only ``` - **`disable`** — plaintext. Local dev only. - **`require`** (default) — encrypted; cert chain validated; hostname is **not** verified. Catches passive eavesdropping, doesn't catch a determined MITM with their own valid-chain cert. - **`verify-full`** — encrypted; cert chain validated; cert hostname must match the URL host. Production default for managed services (RDS, Neon, Supabase). `ca_cert` lets you point at a CA bundle for self-hosted databases or managed providers whose root isn't in the OS trust store. **Additive by default**: the supplied certs extend the system trust store rather than replacing it, so the same `TlsConfig` surface works for one database that needs a private CA and another that doesn't. Set `tls.trust_native: false` to switch to the strict-isolation posture (only the `ca_cert` certs trusted; the public web PKI is rejected). Postgres only — `mysql_async`'s rustls path always bundles `webpki_roots` and offers no upstream knob to suppress it. #### Connecting to managed providers **Supabase.** Every Supabase endpoint (direct, transaction pooler, session pooler) presents certificates signed by *Supabase Intermediate 2021 CA*, which is not in the OS trust store. By default `tls.mode: require` fails with `pool connection failed (tls)`. Download the CA from your project dashboard (or `https://supabase.com/downloads/prod-ca-2021.crt`) and point `tls.ca_cert` at it: ```yaml databases: primary: url: postgresql://postgres.:@aws-0-.pooler.supabase.com:6543/postgres tls: mode: verify-full ca_cert: /etc/ssl/supabase-prod-ca-2021.crt ``` `ca_cert` is additive — your existing CA pinning for other databases keeps working alongside this entry. **Neon.** Drop `?sslmode=` and `?channel_binding=` from URLs copied out of the Neon dashboard, and configure TLS via the `tls` YAML block instead: ```yaml databases: primary: url: postgres://user:pass@ep-xxx-pooler..aws.neon.tech/neondb tls: mode: require # or verify-full ``` Neon's default `?channel_binding=require` cannot work through the pooler endpoint: TLS terminates at the pooler, so SCRAM-SHA-256-PLUS isn't advertised by the inner server, and `tokio-postgres` refuses to fall back. Leaving the URL param in surfaces as `pool connection failed (auth)`. SQLite ignores the `tls` block (local-file driver). ## Quick start (SQLite) ```ts import { registerWorker } from 'iii-sdk' const iii = registerWorker(process.env.III_URL ?? 'ws://127.0.0.1:49134') await iii.trigger({ function_id: 'database::execute', payload: { db: 'primary', sql: 'CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, email TEXT)', }, }) await iii.trigger({ function_id: 'database::execute', payload: { db: 'primary', sql: 'INSERT INTO users (email) VALUES (?), (?)', params: ['a@x', 'b@x'], }, }) const { rows } = await iii.trigger({ function_id: 'database::query', payload: { db: 'primary', sql: 'SELECT id, email FROM users ORDER BY id', }, }) ``` ## Functions | Function | Purpose | |---|---| | `database::query` | Read SQL. Returns `{ rows, row_count, columns }`. | | `database::execute` | Write SQL. Returns `{ affected_rows, last_insert_id, returned_rows }`.
**`returned_rows`** come from a `RETURNING` clause written into the SQL (SQLite, Postgres — MySQL has none): `INSERT INTO t (n) VALUES (?) RETURNING id`. The `returning: [...]` option never adds the clause; SQLite rejects it without one, Postgres and MySQL ignore it.
**`last_insert_id` semantics:** SQLite/MySQL surface the engine's `last_insert_rowid()` / `LAST_INSERT_ID()` (only populated for INSERT). Postgres has no equivalent — `last_insert_id` is set from the **first column of the first RETURNING row** of an INSERT, so put your PK first: `RETURNING id, name`, not `RETURNING name, id`. | | `database::executeBatch` | Convenience form of `transaction`: statements may be bare SQL strings or `{ sql, params }` objects (use `params` for dynamic values instead of inlining them). Same envelope and semantics as `transaction` — atomic, rolls back on first failure, reports `failed_index`, supports `isolation`. | | `database::prepareStatement` | Pin a connection and return `{ handle: { id, expires_at } }`. | | `database::runStatement` | Run a prepared SQL statement by handle. (No `timeout_ms` — uses the pinned connection's session lifetime; configure via `ttl_seconds` on `prepareStatement`.) | | `database::transaction` | Atomic batch sequence; rolls back on first failure. One-shot — pass all statements together. Rejects bare transaction-control SQL (`BEGIN`/`COMMIT`/`ROLLBACK`/…) and empty statements with `INVALID_PARAM`. | | `database::beginTransaction` | Open an interactive transaction. Returns `{ transaction: { id, expires_at } }`. Configurable `timeout_ms` (default 30 000, max 300 000) auto-rolls back if the deadline elapses. | | `database::transactionQuery` | Read SQL inside an interactive transaction. Same envelope as `query`. | | `database::transactionExecute` | Write SQL inside an interactive transaction. Same envelope as `execute`. Rejects bare `BEGIN`/`COMMIT`/`ROLLBACK`/`SAVEPOINT`/`SET TRANSACTION` with `INVALID_PARAM` — finalize via the dedicated handlers below. | | `database::commitTransaction` | Commit an open SQL transaction, making its writes permanent. Subsequent calls against the same id return `TRANSACTION_NOT_FOUND`. | | `database::rollbackTransaction` | Roll back an open SQL transaction, discarding its uncommitted writes. Subsequent calls against the same id return `TRANSACTION_NOT_FOUND`. | | `database::listDatabases` | List configured databases. Returns `{ databases, count }`; each entry has `name`, `driver`, credential-redacted `url`, `pool` settings, and `tls` (`mode`, `ca_cert_present`, `trust_native`). Config only — no health checks or live pool stats. | ### Reading the schema | Function | Description | |---|---| | `database::listTables` | Every table and view, with its kind and (postgres) its schema. | | `database::describeTable` | One table: columns with type, nullability, default, primary-key membership and foreign-key target; plus indexes and a planner row estimate. Foreign keys are structured `{ schema, table, column }`, not a joined string. | | `database::describeSchema` | The same shape for every table at once. One catalog query per aspect across the whole database rather than one call per table, so a 200-table schema costs a handful of queries. `include_indexes` is off by default. | | `database::schemaDiagram` | Positioned table nodes and routed foreign-key edges, plus each table's hub `degree`, the `isolated` tables, and remaining edge `crossings`. Layout runs server-side, so a renderer only draws. | ### Reading data | Function | Description | |---|---| | `database::browseTable` | Paged, sorted, filtered table read — no SQL from the caller. Filters are structured (`{ column, op, value }`) and compile to a parameterised `WHERE` for the driver in hand; `total` honours the same filters. Sorts accept a `mode` (`natural`, `length`, `absolute_value`, `random`) applied across the whole table, not just the page. To follow a foreign key, filter on equality with `page_size: 1`. | | `database::explain` | The query plan as a tree with per-node cost, row estimates and warnings, instead of the driver's raw text. `analyze` collects real timings by **running** the statement, so it defaults to `false` and is refused for anything that is not a single read. | | `database::columnStats` | Profile a table's columns. Reads the planner's own statistics by default — free and approximate, labelled `source: planner`. `exact: true` runs real aggregates and scans the table; it is refused above a row-count ceiling. To profile rows you already hold, pipe a `browseTable` result through the `fp` worker instead. | ### Operations | Function | Description | |---|---| | `database::health` | Check live pool occupancy plus active queries, table sizes, blocking locks and cache hit ratio. Each section reports separately as `available`, `unsupported` or `denied`, so a driver gap or a restricted role never reads as an empty result. | | `database::terminateQuery` | Terminate a backend session, or cancel just its statement with `cancel_only`. Takes an id from `health`. Separate from `health` because it is a write. | ### Saved queries and history Stored in the [`state`](https://github.com/iii-hq/workers/tree/main/state) worker, scoped per database, so they survive restarts and any agent can read them. | Function | Description | |---|---| | `database::saveQuery` | Save a named query. Saving under an existing name replaces it. | | `database::listSavedQueries` | Saved queries for a database, sorted by name. | | `database::deleteSavedQuery` | Delete by id or by name. | | `database::history` | Recent queries, newest first. Best effort — recording never blocks or fails a query, so this is a convenience rather than an audit log. For an audit trail bind `database::row-changed`. Stored history is capped per database (`history_max_entries` / `history_max_bytes`, defaults 200 entries / 256KB — oldest dropped first, `0` disables) and holds metadata only: SQL text (truncated to 4000 chars), verb, timing, row count — never result rows. An oversized or unreadable stored value is replaced wholesale on the next write. | ## Triggers ### `database::row-changed` Fires after a row change commits. Two capture modes, chosen per database in the worker config (`capture:`): - **`statements`** (default) — reports mutations made *through this worker* (`execute`, `executeBatch`, `transaction`, and the interactive transaction surface) by classifying the SQL. A write applied by psql, another worker, or a database-side trigger is invisible to it. No database setup; identical on SQLite, Postgres and MySQL. - **`native`** — reports committed writes from *any* client. Postgres: per-table triggers + LISTEN/NOTIFY (the role needs TRIGGER on the table). File-backed SQLite: triggers feeding a changelog table, drained on filesystem wake-up. MySQL: the binlog replication stream (`REPLICATION SLAVE, REPLICATION CLIENT` grants, `binlog_row_metadata=FULL` for row identity). Bindings must name a `table`. ```yaml triggers: - type: database::row-changed config: db: primary # required table: orders # optional on statements, required on native; case- and schema-insensitive ops: [insert] # optional; insert / update / delete / other ``` Event: `{ db, table, op, affected_rows, returning?, truncated?, at }`, where `op` is `insert` / `update` / `delete` / `other`. The full schema is on the trigger type (`engine::triggers::info`). **Which rows changed — `returning`.** The event never invents row data: - On `statements` it is the writer's own `RETURNING` projection, verbatim. A listener that needs to know *which* row changed depends on the writer having written `RETURNING ` into its SQL — the `returning` option on `execute` does not add the clause. Without it the event carries only `affected_rows`. `UPDATE` reports the new values, `DELETE` the removed row. MySQL has no `RETURNING`, so statements-mode events there never carry rows. - On `native` it is the primary-key columns of each changed row (every column of a composite key; `UPDATE` reports the new key), so a listener gets row identity without the writer's cooperation. Capped at 100 rows per event — `truncated: true` says the rest were dropped, `affected_rows` stays exact. Absent for a table without a primary key. Values are encoded as `database::query` returns the same column (Postgres `bigint` as a string, for example). - When not to bind: if the writer is you and already holds the new values in `execute`'s `returned_rows`, there is nothing to wait for. Worth knowing: - **Announced on commit, never before.** Statements inside an interactive transaction are buffered until `commitTransaction`; a rollback — including the timeout watcher's — drops the buffer. Atomic batches announce their statements in order only after the whole batch commits. Native capture is commit-gated by the database itself. - **Delivery is best-effort.** Dispatch happens after commit and is not durable or atomic with the database write. There is no replay, retry, or exactly-once guarantee; a crash between commit and dispatch can lose an event. Subscriber failures are logged and never fail the write. A harness wake notification renders the event as JSON within ~8,000 characters; an over-long `returning` keeps its leading rows and ends with one marker string, `"…N more entries omitted (array shortened for this notification; JSON is complete)"` (N is what the wake dropped, on top of any `truncated` cap above), so the text still parses and `affected_rows` stays exact. Only an event whose non-array fields alone exceed the budget is cut as plain text with an `…(truncated)` tail. Re-read the table on wake when a binding can see wide or many-row writes. - **`table` can be null on `statements`.** The table is read off the SQL. A CTE-wrapped write (`WITH … INSERT`) still fires, with `table: null`, rather than being dropped; a binding that named a table simply does not match it. Omit `table` to match every write, including these. The classifier reads keywords rather than parsing, so any `WITH` statement whose text contains `INSERT`/`UPDATE`/`DELETE`/`MERGE` — even inside a string literal — fires the same way. - **`runStatement` does not fire on `statements`.** The prepared-run path returns rows, not an affected-row count, and an event that invented one would be lying. Use `execute` when you need the change announced. On a `native` database the database itself reports the write, whichever path it took. - **Native caveats.** Postgres additionally trims keys to fit NOTIFY's 8000-byte payload. SQLite's triggers call `json_object`, so every process writing the file needs libsqlite ≥ 3.38 (JSON built in) — an older client fails its own INSERT with `no such function`. MySQL carries keys only under `binlog_row_metadata=FULL`; on the `MINIMAL` default the events are count-only and registration warns. ## Errors Returned `IIIError::Handler` bodies carry a stable `code` field: | Code | Meaning | |---|---| | `POOL_TIMEOUT` | Pool acquire exceeded `acquire_timeout_ms`. | | `QUERY_TIMEOUT` | Query exceeded `timeout_ms`. | | `STATEMENT_NOT_FOUND` | Handle expired or unknown — re-prepare. | | `TRANSACTION_NOT_FOUND` | Transaction id unknown, already committed/rolled back, or timed out (auto-rolled-back by the watcher). | | `UNKNOWN_DB` | `db` parameter doesn't match any configured database. | | `INVALID_PARAM` | JSON value couldn't be coerced for the target driver, transaction-control SQL was sent to `transactionExecute` (use `commitTransaction` / `rollbackTransaction`), or a `transaction`/`executeBatch` batch contained transaction-control SQL or an empty statement. | | `DRIVER_ERROR` | Wraps underlying driver error with `driver` and `inner_code` (nullable). `inner_code` format is per-driver: Postgres = SQLSTATE 5-char string (e.g. `42P01`), MySQL = server error number as string, SQLite = `rusqlite::ErrorCode` debug name. Pool-acquire failures use the message form `pool connection failed ()` where `` is one of `tls`, `auth`, `network`, `server-policy`, or `unknown` — a redacted hint so untrusted callers can self-triage without seeing host/userinfo/db fragments. The full driver error is in the worker's stderr via `tracing::warn!`. | | `CONFIG_ERROR` | Config parse or pool init failure. | ## Driver compatibility A few operations are no-ops on certain drivers. They emit a `tracing::warn!` rather than an error: | Operation | SQLite | Postgres | MySQL | |---|---|---|---| | `execute` / `transactionExecute` with a `RETURNING` clause in the SQL | ✓ rows returned | ✓ rows returned | no RETURNING in MySQL | | `execute` `returning: [...]` option (never adds the clause) | rejected without a clause (`RETURNING_MISMATCH`) | accepted, no effect (warn) | accepted, no effect (warn) | | `transaction` `isolation: read_committed` / `repeatable_read` | warn + use serializable | ✓ | ✓ | | `transaction` `isolation: serializable` | ✓ (`BEGIN IMMEDIATE`) | ✓ | ✓ | ## Troubleshooting - **Pool exhausted (`POOL_TIMEOUT`)**: bump `pool.max` or shorten the longest-running query. Live `prepareStatement` handles each pin one connection from the pool until they expire. - **`STATEMENT_NOT_FOUND` from a long-lived handle**: handles are bounded to `ttl_seconds` (default 3600, max 86400). Re-prepare and retry. - **`DRIVER_ERROR` "pool connection failed (...)"**: the parenthesized class tells you where to look. - `(tls)` — handshake or cert-chain failure. For managed providers (Supabase, self-signed corporate CAs), supply `tls.ca_cert`; see "Connecting to managed providers" above. - `(auth)` — credential or pg_hba/SCRAM rejection. Includes Neon's `?channel_binding=require` failing through the pooler endpoint (drop the URL param, use `tls.mode` in YAML). - `(network)` — TCP refuse, DNS, route, or peer reset. Check host/port reachability and any firewalls. - `(server-policy)` — server reachable and TLS+auth OK, but the server actively refused (e.g. `max_connections` exceeded, admin shutdown). Look at the worker stderr for the underlying driver message. ## License Apache 2.0 — see [LICENSE](https://github.com/iii-hq/workers/blob/main/LICENSE). ## skills This version ships 1 skill file for agents, also browsable on the worker page: - [`SKILL.md`](https://workers.iii.dev/workers/database?tab=skills&skill=SKILL.md&version=0.5.17) ### SKILL.md # database The database worker connects to PostgreSQL, MySQL, and SQLite through a managed per-database connection pool. Every callable surface lives under the `database::*` namespace. The driver is chosen from each database URL scheme (`sqlite:`, `postgres://`, `postgresql://`, `mysql://`). Runtime settings live in the `configuration` worker under id `database`; pools hot-reload when the value changes. SQLite is the recommended starting point. Placeholder syntax: `?` for SQLite and MySQL, `$1`/`$2`/… for Postgres. ## When to Use - You need to read rows from a configured database (`database::query`). - You need to insert, update, delete, or run DDL and read affected-row counts or autoincrement ids (`database::execute`). - Several statements must commit or roll back together as one unit (`database::transaction`, `database::executeBatch`, or the interactive transaction surface). - The same parameterized SQL will run many times and you want to skip per-call parse/plan cost (`database::prepareStatement` + `database::runStatement`). - You need read-your-writes across round-trips with logic between steps (`database::beginTransaction` … `commitTransaction` / `rollbackTransaction`). ## Boundaries - Not a migration tool, ORM, or schema designer — pass raw SQL only. - Not a general pub/sub bus. `database::row-changed` reports what THIS worker wrote, on commit (`capture: statements`, the default) or, on a `capture: native` database, any client's committed writes. - `database::query` is read-oriented; use `database::execute` for writes. Keep reads on `query`: it runs them read-only, `execute` does not. - Prepared handles pin a pool connection until TTL expiry — not transactions. Batch `database::transaction` / `database::executeBatch` need every statement up front; use the interactive surface when code must branch between steps. - Rows come back from a `RETURNING` clause written into the SQL (SQLite, Postgres; MySQL has none). The `returning` option never adds the clause: SQLite rejects it without one, Postgres and MySQL ignore it. SQLite degrades `read_committed` / `repeatable_read` isolation to serializable. - For filesystem or shell operations, use the `shell` worker instead. ## Functions - `database::query` — run read-only SQL and return rows, row count, and column metadata. - `database::execute` — run write SQL (INSERT/UPDATE/DELETE/DDL) and return affected rows, optional last insert id, and the rows of a `RETURNING` clause in the SQL. - `database::executeBatch` — convenience form of `transaction`: statements may be bare SQL strings or `{sql, params}` objects (prefer `params` for dynamic values). Same atomic semantics, envelope, and `failed_index` reporting as `transaction`. - `database::prepareStatement` — parse and plan SQL once; return a handle that pins a pool connection until TTL expiry. - `database::runStatement` — re-execute a prepared handle with new bind params; response shape matches `query`. - `database::transaction` — run an ordered batch of statements atomically; rolls back on first failure and reports `failed_index`. - `database::beginTransaction` — open an interactive transaction and return an id plus expiry deadline. - `database::transactionQuery` — read SQL inside an open interactive transaction; same envelope as `query`. - `database::transactionExecute` — write SQL inside an open interactive transaction; same envelope as `execute`. Rejects bare transaction-control SQL — finalize via `commitTransaction` or `rollbackTransaction`. - `database::commitTransaction` — commit and finalize an interactive transaction. - `database::rollbackTransaction` — roll back and finalize an interactive transaction. - `database::listDatabases` — every configured database with its driver, credential-redacted URL, pool settings and TLS mode. Config only; use `database::health` for live state. Interactive transactions auto-roll back when `timeout_ms` elapses (default 30 s, max 5 min). Prepared handles default to a 1 h TTL (max 24 h) with no explicit release call — let them expire or stop using them when done. ### Reading the schema One shape across all three drivers — prefer these over hand-writing `sqlite_master` / `information_schema` / `PRAGMA`. - `database::listTables` — tables and views, with kind and (postgres) schema. - `database::describeTable` — columns with type, nullability, default, primary key and a structured `foreign_key` of `{ schema, table, column }`; plus indexes and a planner row estimate. - `database::describeSchema` — the same for every table in one pass. Use this rather than looping `describeTable`. - `database::schemaDiagram` — positioned nodes, routed foreign-key edges, hub `degree` and `isolated` tables. For reasoning about a schema's shape, not only for drawing it. ### Reading data - `database::browseTable` — paged, sorted, filtered reads with no SQL. Filters are `{ column, op, value }` and `total` honours them. Follow a foreign key with an equality filter at `page_size: 1`. - `database::explain` — the plan as a tree with costs and warnings. `analyze` **runs** the statement, so it defaults to false and is refused for anything that is not a single read. - `database::columnStats` — planner statistics by default (approximate, labelled `source: planner`); `exact: true` scans. To profile rows you already hold, use the `fp` worker on a `browseTable` result instead. ### Operations and reuse - `database::health` — pool occupancy, active queries, table sizes, locks, cache ratio. Each section is `available`, `unsupported` or `denied`, so a driver gap is never mistaken for an empty result. - `database::terminateQuery` — end a session, or cancel its statement with `cancel_only`. Takes an id from `database::health`. - `database::saveQuery`, `database::listSavedQueries`, `database::deleteSavedQuery` — named queries per database, kept in the `state` worker. - `database::history` — recent queries, newest first. Best effort, not an audit log; bind `database::row-changed` for that. ## Reacting to writes Register a `database::row-changed` trigger to be told when this worker commits a change, instead of polling: ```json { "trigger_type": "database::row-changed", "config": { "db": "primary", "table": "orders", "ops": ["insert"] } } ``` The event is `{ db, table, op, affected_rows, returning?, truncated?, at }`. It fires on commit — an interactive transaction's writes are announced by `commitTransaction`, and a rollback announces nothing. `returning` says which rows: on a `capture: statements` database it is the writer's own `RETURNING` projection, so write `RETURNING ` into the SQL when a listener needs row identity; on a `capture: native` database (which also hears other clients' writes) it is the changed rows' primary keys, capped at 100 with `truncated: true` past that. `table` is null when the statement's table cannot be read off the SQL (a CTE-wrapped write), and `runStatement` does not fire on the statements path because it has no affected-row count to report. Delivery is best-effort: it is not durable with the commit and has no replay or exactly-once guarantee. ## api reference ```json { "functions": [ { "description": "Open an interactive transaction; returns a handle to use with transactionQuery/transactionExecute/commitTransaction/rollbackTransaction.", "metadata": {}, "name": "database::beginTransaction", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "db": { "default": null, "description": "Logical database name. Optional — omitting it targets the sole configured database, or `primary` when several are configured.", "type": [ "string", "null" ] }, "isolation": { "default": null, "type": [ "string", "null" ] }, "timeout_ms": { "default": null, "format": "uint64", "minimum": 0, "type": [ "integer", "null" ] } }, "title": "BeginTxReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "definitions": { "TxHandleResponse": { "description": "JSON wire envelope returned by `beginTransaction`.", "properties": { "expires_at": { "format": "date-time", "type": "string" }, "id": { "type": "string" } }, "required": [ "expires_at", "id" ], "type": "object" } }, "properties": { "transaction": { "$ref": "#/definitions/TxHandleResponse" } }, "required": [ "transaction" ], "title": "BeginTxResp", "type": "object" } }, { "description": "Read a table page by page with typed filters and sorts, without writing SQL. Filters are structured (column, op, value) and compile to a parameterised WHERE for the driver in hand; the total honours the same filters. Use an equality filter at page_size 1 to follow a foreign key.", "metadata": {}, "name": "database::browseTable", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "definitions": { "Direction": { "enum": [ "asc", "desc" ], "type": "string" }, "FilterOp": { "oneOf": [ { "enum": [ "contains", "not_contains", "equals", "not_equals", "starts_with", "ends_with", "gt", "gte", "lt", "lte", "is_true", "is_false", "is_null", "is_not_null", "not_in" ], "type": "string" }, { "description": "Inclusive range; needs both `value` and `value2`.", "enum": [ "between" ], "type": "string" }, { "description": "NULL or the empty string. Distinct from `is_null` on purpose.", "enum": [ "is_empty" ], "type": "string" }, { "description": "Set membership, over `values`.", "enum": [ "in" ], "type": "string" } ] }, "FilterSpec": { "properties": { "case_sensitive": { "default": null, "description": "Postgres only. Rejected elsewhere rather than silently ignored.", "type": [ "boolean", "null" ] }, "column": { "type": "string" }, "disabled": { "default": false, "description": "Kept in the list but not applied.", "type": "boolean" }, "op": { "$ref": "#/definitions/FilterOp" }, "value": { "default": null }, "value2": { "default": null, "description": "Upper bound for `between`." }, "values": { "default": [], "description": "Operands for `in` / `not_in`.", "items": true, "type": "array" } }, "required": [ "column", "op" ], "type": "object" }, "NullsPosition": { "enum": [ "first", "last" ], "type": "string" }, "SortMode": { "description": "Type-aware sort modes, applied server-side over the whole table.", "oneOf": [ { "enum": [ "default", "length", "absolute_value", "random" ], "type": "string" }, { "description": "`item2` before `item10`. The mode users actually notice.", "enum": [ "natural" ], "type": "string" } ] }, "SortSpec": { "properties": { "column": { "type": "string" }, "direction": { "allOf": [ { "$ref": "#/definitions/Direction" } ], "default": "asc" }, "mode": { "allOf": [ { "$ref": "#/definitions/SortMode" } ], "default": "default" }, "nulls": { "anyOf": [ { "$ref": "#/definitions/NullsPosition" }, { "type": "null" } ], "default": null } }, "required": [ "column" ], "type": "object" } }, "properties": { "db": { "default": null, "type": [ "string", "null" ] }, "filters": { "description": "Combined with AND.", "items": { "$ref": "#/definitions/FilterSpec" }, "type": "array" }, "include_total": { "default": true, "description": "A filtered `COUNT(*)` is a second query and can be expensive on a large table. Turn it off while the caller is still typing.", "type": "boolean" }, "page": { "default": 0, "description": "Zero-based.", "format": "uint32", "minimum": 0, "type": "integer" }, "page_size": { "default": 50, "format": "uint32", "minimum": 0, "type": "integer" }, "schema": { "default": null, "type": [ "string", "null" ] }, "sort": { "description": "Applied in order; sort priority is position in the list.", "items": { "$ref": "#/definitions/SortSpec" }, "type": "array" }, "table": { "type": "string" }, "timeout_ms": { "default": 30000, "format": "uint64", "minimum": 0, "type": "integer" } }, "required": [ "table" ], "title": "BrowseTableReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "definitions": { "ColumnMeta": { "properties": { "name": { "type": "string" }, "type": { "type": "string" } }, "required": [ "name", "type" ], "type": "object" } }, "properties": { "columns": { "items": { "$ref": "#/definitions/ColumnMeta" }, "type": "array" }, "has_more": { "description": "Derived from a sentinel row, so it is correct without a count.", "type": "boolean" }, "page": { "format": "uint32", "minimum": 0, "type": "integer" }, "page_size": { "format": "uint32", "minimum": 0, "type": "integer" }, "rows": { "items": { "additionalProperties": true, "type": "object" }, "type": "array" }, "total": { "description": "Total matching the same filters. Absent when not requested.", "format": "int64", "type": [ "integer", "null" ] } }, "required": [ "columns", "has_more", "page", "page_size", "rows" ], "title": "BrowseTableResp", "type": "object" } }, { "description": "Profile a table's columns. Reads the planner's own statistics by default, which is free and approximate; `exact` runs real aggregates and scans the table. To profile rows you already hold, pipe a browseTable result through the fp worker instead.", "metadata": {}, "name": "database::columnStats", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "columns": { "default": null, "description": "Omit to profile every column.", "items": { "type": "string" }, "type": [ "array", "null" ] }, "db": { "default": null, "type": [ "string", "null" ] }, "exact": { "default": false, "description": "Run real aggregates instead of reading planner statistics. This scans the table.", "type": "boolean" }, "schema": { "default": null, "type": [ "string", "null" ] }, "table": { "type": "string" }, "timeout_ms": { "default": 30000, "format": "uint64", "minimum": 0, "type": "integer" }, "top_n": { "default": 10, "format": "uint", "minimum": 0, "type": "integer" } }, "required": [ "table" ], "title": "ColumnStatsReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "definitions": { "ColumnStat": { "properties": { "distinct_count": { "format": "int64", "type": [ "integer", "null" ] }, "max": true, "mean": { "format": "double", "type": [ "number", "null" ] }, "min": true, "name": { "type": "string" }, "null_count": { "format": "int64", "type": [ "integer", "null" ] }, "null_fraction": { "format": "double", "type": [ "number", "null" ] }, "row_count": { "format": "int64", "type": [ "integer", "null" ] }, "source": { "$ref": "#/definitions/StatSource" }, "top_values": { "description": "Only populated in `exact` mode; the planner's own most-common-value lists are not portable enough to report faithfully.", "items": { "$ref": "#/definitions/TopValue" }, "type": "array" } }, "required": [ "name", "source", "top_values" ], "type": "object" }, "StatSource": { "oneOf": [ { "description": "Read from the planner's own statistics. Approximate, and free.", "enum": [ "planner" ], "type": "string" }, { "description": "Counted by running aggregates over the table.", "enum": [ "computed" ], "type": "string" } ] }, "TopValue": { "properties": { "count": { "format": "int64", "type": "integer" }, "value": true }, "required": [ "count", "value" ], "type": "object" } }, "properties": { "approximate": { "description": "True when the numbers came from the planner rather than a count.", "type": "boolean" }, "columns": { "items": { "$ref": "#/definitions/ColumnStat" }, "type": "array" }, "schema": { "type": [ "string", "null" ] }, "table": { "type": "string" } }, "required": [ "approximate", "columns", "table" ], "title": "ColumnStatsResp", "type": "object" } }, { "description": "Commit an open SQL transaction, making its writes permanent.", "metadata": {}, "name": "database::commitTransaction", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "transaction_id": { "type": "string" } }, "required": [ "transaction_id" ], "title": "CommitTxReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "committed": { "type": "boolean" } }, "required": [ "committed" ], "title": "CommitTxResp", "type": "object" } }, { "description": "Delete a saved query by id or by name.", "metadata": {}, "name": "database::deleteSavedQuery", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "db": { "default": null, "type": [ "string", "null" ] }, "id": { "description": "Either the id returned by `saveQuery`, or the name it was saved under.", "type": "string" } }, "required": [ "id" ], "title": "DeleteSavedReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "deleted": { "type": "boolean" } }, "required": [ "deleted" ], "title": "DeleteSavedResp", "type": "object" } }, { "description": "Describe every table at once — the same shape as describeTable, but one catalog query per aspect across the whole database instead of one call per table. Use this to reason about relationships; set include_indexes only when you need them.", "metadata": {}, "name": "database::describeSchema", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "db": { "default": null, "type": [ "string", "null" ] }, "include_indexes": { "default": false, "description": "Indexes cost one extra catalog query. Off by default because the common caller (a relationship diagram) only needs columns and keys.", "type": "boolean" }, "max_tables": { "default": 500, "format": "uint", "minimum": 0, "type": "integer" }, "tables": { "default": null, "description": "Restrict to these tables. Omit for every table in the database.", "items": { "type": "string" }, "type": [ "array", "null" ] }, "timeout_ms": { "default": 30000, "format": "uint64", "minimum": 0, "type": "integer" } }, "title": "DescribeSchemaReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "definitions": { "ColumnDesc": { "properties": { "default_value": { "type": [ "string", "null" ] }, "foreign_key": { "anyOf": [ { "$ref": "#/definitions/ForeignKeyRef" }, { "type": "null" } ] }, "name": { "type": "string" }, "nullable": { "type": "boolean" }, "position": { "description": "1-based ordinal, as the driver reports it.", "format": "int32", "type": "integer" }, "primary_key": { "type": "boolean" }, "type": { "description": "Driver-reported type text (`TEXT`, `integer`, `varchar(255)`).", "type": "string" } }, "required": [ "name", "nullable", "position", "primary_key", "type" ], "type": "object" }, "ForeignKeyRef": { "description": "Where a foreign key points. Structured rather than a `\"table.column\"` string so a schema-qualified target stays unambiguous.", "properties": { "column": { "type": "string" }, "schema": { "type": [ "string", "null" ] }, "table": { "type": "string" } }, "required": [ "column", "table" ], "type": "object" }, "IndexDesc": { "properties": { "columns": { "description": "Indexed columns in ordinal order. Empty when the index is on an expression rather than plain columns.", "items": { "type": "string" }, "type": "array" }, "name": { "type": "string" }, "primary": { "type": "boolean" }, "unique": { "type": "boolean" } }, "required": [ "columns", "name", "primary", "unique" ], "type": "object" }, "TableDescription": { "properties": { "columns": { "items": { "$ref": "#/definitions/ColumnDesc" }, "type": "array" }, "indexes": { "items": { "$ref": "#/definitions/IndexDesc" }, "type": "array" }, "kind": { "$ref": "#/definitions/TableKind" }, "row_count_estimate": { "description": "Planner estimate, never a `COUNT(*)`. Absent when the driver has no cheap estimate (sqlite) or has not analyzed the table yet.", "format": "int64", "type": [ "integer", "null" ] }, "schema": { "type": [ "string", "null" ] }, "table": { "type": "string" } }, "required": [ "columns", "indexes", "kind", "table" ], "type": "object" }, "TableKind": { "enum": [ "table", "view" ], "type": "string" } }, "properties": { "count": { "format": "uint", "minimum": 0, "type": "integer" }, "tables": { "items": { "$ref": "#/definitions/TableDescription" }, "type": "array" }, "truncated": { "description": "True when `max_tables` cut the result short. Never silently truncate.", "type": "boolean" } }, "required": [ "count", "tables", "truncated" ], "title": "DescribeSchemaResp", "type": "object" } }, { "description": "Describe one table or view: columns with type, nullability, default, primary-key membership and foreign-key target; plus indexes and a planner row estimate. Foreign keys are structured (schema, table, column), not a joined string.", "metadata": {}, "name": "database::describeTable", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "db": { "default": null, "type": [ "string", "null" ] }, "schema": { "default": null, "type": [ "string", "null" ] }, "table": { "description": "Table or view name; may be schema-qualified (`analytics.events`) on postgres. Use `schema` when the name itself contains a dot.", "type": "string" }, "timeout_ms": { "default": 30000, "format": "uint64", "minimum": 0, "type": "integer" } }, "required": [ "table" ], "title": "DescribeTableReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "definitions": { "ColumnDesc": { "properties": { "default_value": { "type": [ "string", "null" ] }, "foreign_key": { "anyOf": [ { "$ref": "#/definitions/ForeignKeyRef" }, { "type": "null" } ] }, "name": { "type": "string" }, "nullable": { "type": "boolean" }, "position": { "description": "1-based ordinal, as the driver reports it.", "format": "int32", "type": "integer" }, "primary_key": { "type": "boolean" }, "type": { "description": "Driver-reported type text (`TEXT`, `integer`, `varchar(255)`).", "type": "string" } }, "required": [ "name", "nullable", "position", "primary_key", "type" ], "type": "object" }, "ForeignKeyRef": { "description": "Where a foreign key points. Structured rather than a `\"table.column\"` string so a schema-qualified target stays unambiguous.", "properties": { "column": { "type": "string" }, "schema": { "type": [ "string", "null" ] }, "table": { "type": "string" } }, "required": [ "column", "table" ], "type": "object" }, "IndexDesc": { "properties": { "columns": { "description": "Indexed columns in ordinal order. Empty when the index is on an expression rather than plain columns.", "items": { "type": "string" }, "type": "array" }, "name": { "type": "string" }, "primary": { "type": "boolean" }, "unique": { "type": "boolean" } }, "required": [ "columns", "name", "primary", "unique" ], "type": "object" }, "TableKind": { "enum": [ "table", "view" ], "type": "string" } }, "properties": { "columns": { "items": { "$ref": "#/definitions/ColumnDesc" }, "type": "array" }, "indexes": { "items": { "$ref": "#/definitions/IndexDesc" }, "type": "array" }, "kind": { "$ref": "#/definitions/TableKind" }, "row_count_estimate": { "description": "Planner estimate, never a `COUNT(*)`. Absent when the driver has no cheap estimate (sqlite) or has not analyzed the table yet.", "format": "int64", "type": [ "integer", "null" ] }, "schema": { "type": [ "string", "null" ] }, "table": { "type": "string" } }, "required": [ "columns", "indexes", "kind", "table" ], "title": "TableDescription", "type": "object" } }, { "description": "Run a write statement (INSERT/UPDATE/DELETE/DDL); put RETURNING in the SQL to get rows back and into row-changed events. The `returning` option never adds the clause.", "metadata": {}, "name": "database::execute", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "db": { "default": null, "description": "Logical database name. Optional — omitting it targets the sole configured database, or `primary` when several are configured.", "type": [ "string", "null" ] }, "params": { "default": [], "items": true, "type": "array" }, "returning": { "default": [], "description": "Optional, and NOT what produces rows: this never adds a `RETURNING` clause or projects columns — write the clause into `sql`. SQLite refuses a non-empty list when the statement returns no rows (`RETURNING_MISMATCH`), so a write cannot silently lose its identity; Postgres and MySQL ignore the list with a warning.", "items": { "type": "string" }, "type": "array" }, "sql": { "description": "The write statement. To get rows back — and to have them ride the `database::row-changed` event this write fires — put a `RETURNING` clause in the SQL itself: `INSERT INTO t (n) VALUES (?) RETURNING id` (SQLite, Postgres; MySQL has no RETURNING). Without one the response has `returned_rows: []` and the event carries only `affected_rows`.", "type": "string" } }, "required": [ "sql" ], "title": "ExecuteReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "affected_rows": { "format": "uint64", "minimum": 0, "type": "integer" }, "last_insert_id": { "description": "SQLite/MySQL: the engine's last insert id, INSERT only. Postgres has none, so it is the first column of the first `RETURNING` row of an INSERT — put the key first: `RETURNING id, name`.", "type": [ "string", "null" ] }, "returned_rows": { "description": "The rows the SQL's `RETURNING` clause produced (or a `SELECT`/`VALUES` sent here). Empty without such a clause.", "items": { "additionalProperties": true, "type": "object" }, "type": "array" } }, "required": [ "affected_rows", "returned_rows" ], "title": "ExecuteResp", "type": "object" } }, { "description": "Run an ordered batch of SQL statements atomically (bare strings or {sql, params} objects); rolls back on first failure. A statement's RETURNING rows come back in results[].rows.", "metadata": {}, "name": "database::executeBatch", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "definitions": { "BatchStatement": { "anyOf": [ { "type": "string" }, { "$ref": "#/definitions/TxStmtReq" } ] }, "TxStmtReq": { "properties": { "params": { "default": [], "items": true, "type": "array" }, "sql": { "description": "One statement. A `RETURNING` clause in the SQL (SQLite, Postgres) puts the rows in this step's `results[].rows` and on the `database::row-changed` event fired for it after commit.", "type": "string" } }, "required": [ "sql" ], "type": "object" } }, "properties": { "db": { "default": null, "description": "Logical database name. Optional — omitting it targets the sole configured database, or `primary` when several are configured.", "type": [ "string", "null" ] }, "isolation": { "default": null, "description": "Optional: `read_committed` | `repeatable_read` | `serializable`.", "type": [ "string", "null" ] }, "statements": { "description": "Statements to run in order inside one transaction. Put dynamic values in `params`, not inline in the SQL.", "items": { "$ref": "#/definitions/BatchStatement" }, "type": "array" } }, "required": [ "statements" ], "title": "ExecuteBatchReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "definitions": { "TxStepResp": { "properties": { "affected_rows": { "format": "uint64", "minimum": 0, "type": "integer" }, "rows": { "items": { "items": true, "type": "array" }, "type": "array" } }, "required": [ "affected_rows", "rows" ], "type": "object" } }, "properties": { "committed": { "type": "boolean" }, "error": true, "failed_index": { "format": "uint", "minimum": 0, "type": [ "integer", "null" ] }, "results": { "items": { "$ref": "#/definitions/TxStepResp" }, "type": [ "array", "null" ] } }, "required": [ "committed" ], "title": "TxResp", "type": "object" } }, { "description": "Explain a statement's query plan as a tree with per-node costs, row estimates and warnings. `analyze` collects real timings by RUNNING the statement, so it defaults to false and is refused for anything that is not a single read.", "metadata": {}, "name": "database::explain", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "analyze": { "default": false, "description": "Runs the statement to collect real timings. Refused for anything that is not a read.", "type": "boolean" }, "db": { "default": null, "type": [ "string", "null" ] }, "params": { "default": [], "items": true, "type": "array" }, "sql": { "type": "string" }, "timeout_ms": { "default": 30000, "format": "uint64", "minimum": 0, "type": "integer" } }, "required": [ "sql" ], "title": "ExplainReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "definitions": { "NodeClass": { "enum": [ "scan", "index", "join", "sort", "aggregate", "cte", "limit", "other" ], "type": "string" }, "PlanFormat": { "enum": [ "pg_json", "sqlite_query_plan", "mysql_json", "unknown" ], "type": "string" }, "PlanNode": { "properties": { "children": { "items": { "$ref": "#/definitions/PlanNode" }, "type": "array" }, "cost_startup": { "format": "double", "type": [ "number", "null" ] }, "cost_total": { "format": "double", "type": [ "number", "null" ] }, "detail": { "type": "string" }, "id": { "format": "uint32", "minimum": 0, "type": "integer" }, "label": { "type": "string" }, "loops": { "format": "double", "type": [ "number", "null" ] }, "node_class": { "$ref": "#/definitions/NodeClass" }, "parent": { "format": "uint32", "minimum": 0, "type": [ "integer", "null" ] }, "relation": { "type": [ "string", "null" ] }, "rows_actual": { "format": "double", "type": [ "number", "null" ] }, "rows_estimated": { "format": "double", "type": [ "number", "null" ] }, "time_ms": { "format": "double", "type": [ "number", "null" ] }, "width": { "format": "int64", "type": [ "integer", "null" ] } }, "required": [ "children", "detail", "id", "label", "node_class" ], "type": "object" }, "PlanWarning": { "properties": { "kind": { "$ref": "#/definitions/WarningKind" }, "message": { "type": "string" }, "node_id": { "format": "uint32", "minimum": 0, "type": "integer" }, "severity": { "$ref": "#/definitions/Severity" } }, "required": [ "kind", "message", "node_id", "severity" ], "type": "object" }, "Severity": { "enum": [ "info", "warn" ], "type": "string" }, "WarningKind": { "oneOf": [ { "description": "A sequential scan over a large relation.", "enum": [ "seq_scan_large" ], "type": "string" }, { "description": "Estimated and actual row counts differ by an order of magnitude — usually stale statistics.", "enum": [ "estimate_skew" ], "type": "string" }, { "description": "An inner loop executed a very large number of times.", "enum": [ "nested_loop_large" ], "type": "string" } ] } }, "properties": { "analyzed": { "type": "boolean" }, "format": { "$ref": "#/definitions/PlanFormat" }, "raw": { "description": "The driver's own output, so a caller is never stuck when the shape is one we do not recognise." }, "root": { "anyOf": [ { "$ref": "#/definitions/PlanNode" }, { "type": "null" } ] }, "warnings": { "items": { "$ref": "#/definitions/PlanWarning" }, "type": "array" } }, "required": [ "analyzed", "format", "warnings" ], "title": "ExplainResp", "type": "object" } }, { "description": "Get a table's column layout for reading: widths, hidden columns and column order. Stored in the state worker rather than a browser, so it survives a restart and any caller can set it up for someone else.", "metadata": {}, "name": "database::getTableView", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "db": { "default": null, "type": [ "string", "null" ] }, "table": { "type": "string" } }, "required": [ "table" ], "title": "GetTableViewReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "hidden": { "default": [], "description": "Columns the reader has hidden. Order is not meaningful.", "items": { "type": "string" }, "type": "array" }, "order": { "default": [], "description": "Column display order. Unlisted columns keep their natural position after the listed ones.", "items": { "type": "string" }, "type": "array" }, "widths": { "additionalProperties": { "format": "double", "type": "number" }, "default": {}, "description": "Per-column pixel width. Absent means \"size to content\".", "type": "object" } }, "title": "TableView", "type": "object" } }, { "description": "Check live pool occupancy plus active queries, table sizes, blocking locks and cache hit ratio. Each section reports separately as available, unsupported or denied, so a driver gap or a restricted role never reads as an empty result.", "metadata": {}, "name": "database::health", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "db": { "default": null, "type": [ "string", "null" ] }, "timeout_ms": { "default": 15000, "format": "uint64", "minimum": 0, "type": "integer" } }, "title": "HealthReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "definitions": { "ActiveQuery": { "properties": { "duration_ms": { "format": "int64", "type": [ "integer", "null" ] }, "id": { "type": "string" }, "sql": { "type": "string" }, "state": { "type": [ "string", "null" ] }, "user": { "type": [ "string", "null" ] } }, "required": [ "id", "sql" ], "type": "object" }, "CacheStats": { "properties": { "blocks_hit": { "format": "int64", "type": "integer" }, "blocks_read": { "format": "int64", "type": "integer" }, "hit_ratio": { "description": "Fraction of block reads served from cache. A healthy OLTP database usually sits well above 0.99.", "format": "double", "type": "number" } }, "required": [ "blocks_hit", "blocks_read", "hit_ratio" ], "type": "object" }, "LockInfo": { "properties": { "blocked_id": { "type": "string" }, "blocked_sql": { "type": "string" }, "blocking_id": { "type": "string" }, "blocking_sql": { "type": [ "string", "null" ] }, "relation": { "type": [ "string", "null" ] } }, "required": [ "blocked_id", "blocked_sql", "blocking_id" ], "type": "object" }, "PoolStats": { "description": "Live pool occupancy, for `database::health`.\n\n`size` and `idle` are `None` where the underlying pool does not expose them — `mysql_async` keeps its counters private. Reporting `None` rather than zero matters: \"unknown\" and \"no idle connections\" are different answers, and a health panel that conflates them is actively misleading.", "properties": { "idle": { "format": "uint32", "minimum": 0, "type": [ "integer", "null" ] }, "max": { "format": "uint32", "minimum": 0, "type": "integer" }, "size": { "format": "uint32", "minimum": 0, "type": [ "integer", "null" ] }, "waiting": { "format": "uint32", "minimum": 0, "type": [ "integer", "null" ] } }, "required": [ "max" ], "type": "object" }, "ProbeResult_for_Array_of_ActiveQuery": { "description": "One section of the report.", "oneOf": [ { "description": "The driver answered.", "properties": { "data": { "items": { "$ref": "#/definitions/ActiveQuery" }, "type": "array" }, "status": { "enum": [ "available" ], "type": "string" } }, "required": [ "data", "status" ], "type": "object" }, { "description": "The driver has no equivalent of this concept.", "properties": { "reason": { "type": "string" }, "status": { "enum": [ "unsupported" ], "type": "string" } }, "required": [ "reason", "status" ], "type": "object" }, { "description": "The driver has it, but this role may not read it.", "properties": { "reason": { "type": "string" }, "status": { "enum": [ "denied" ], "type": "string" } }, "required": [ "reason", "status" ], "type": "object" } ] }, "ProbeResult_for_Array_of_LockInfo": { "description": "One section of the report.", "oneOf": [ { "description": "The driver answered.", "properties": { "data": { "items": { "$ref": "#/definitions/LockInfo" }, "type": "array" }, "status": { "enum": [ "available" ], "type": "string" } }, "required": [ "data", "status" ], "type": "object" }, { "description": "The driver has no equivalent of this concept.", "properties": { "reason": { "type": "string" }, "status": { "enum": [ "unsupported" ], "type": "string" } }, "required": [ "reason", "status" ], "type": "object" }, { "description": "The driver has it, but this role may not read it.", "properties": { "reason": { "type": "string" }, "status": { "enum": [ "denied" ], "type": "string" } }, "required": [ "reason", "status" ], "type": "object" } ] }, "ProbeResult_for_Array_of_TableSize": { "description": "One section of the report.", "oneOf": [ { "description": "The driver answered.", "properties": { "data": { "items": { "$ref": "#/definitions/TableSize" }, "type": "array" }, "status": { "enum": [ "available" ], "type": "string" } }, "required": [ "data", "status" ], "type": "object" }, { "description": "The driver has no equivalent of this concept.", "properties": { "reason": { "type": "string" }, "status": { "enum": [ "unsupported" ], "type": "string" } }, "required": [ "reason", "status" ], "type": "object" }, { "description": "The driver has it, but this role may not read it.", "properties": { "reason": { "type": "string" }, "status": { "enum": [ "denied" ], "type": "string" } }, "required": [ "reason", "status" ], "type": "object" } ] }, "ProbeResult_for_CacheStats": { "description": "One section of the report.", "oneOf": [ { "description": "The driver answered.", "properties": { "data": { "$ref": "#/definitions/CacheStats" }, "status": { "enum": [ "available" ], "type": "string" } }, "required": [ "data", "status" ], "type": "object" }, { "description": "The driver has no equivalent of this concept.", "properties": { "reason": { "type": "string" }, "status": { "enum": [ "unsupported" ], "type": "string" } }, "required": [ "reason", "status" ], "type": "object" }, { "description": "The driver has it, but this role may not read it.", "properties": { "reason": { "type": "string" }, "status": { "enum": [ "denied" ], "type": "string" } }, "required": [ "reason", "status" ], "type": "object" } ] }, "TableSize": { "properties": { "index_bytes": { "format": "int64", "type": [ "integer", "null" ] }, "row_estimate": { "format": "int64", "type": [ "integer", "null" ] }, "schema": { "type": [ "string", "null" ] }, "table": { "type": "string" }, "total_bytes": { "format": "int64", "type": [ "integer", "null" ] } }, "required": [ "table" ], "type": "object" } }, "properties": { "active_queries": { "$ref": "#/definitions/ProbeResult_for_Array_of_ActiveQuery" }, "cache": { "$ref": "#/definitions/ProbeResult_for_CacheStats" }, "db": { "type": "string" }, "driver": { "type": "string" }, "locks": { "$ref": "#/definitions/ProbeResult_for_Array_of_LockInfo" }, "pool": { "$ref": "#/definitions/PoolStats" }, "table_sizes": { "$ref": "#/definitions/ProbeResult_for_Array_of_TableSize" }, "worker_version": { "type": "string" } }, "required": [ "active_queries", "cache", "db", "driver", "locks", "pool", "table_sizes", "worker_version" ], "title": "HealthResp", "type": "object" } }, { "description": "List recent queries run against a database, newest first. Best effort — recording never blocks or fails a query, so it is a convenience, not an audit log. For an audit trail bind database::row-changed.", "metadata": {}, "name": "database::history", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "db": { "default": null, "type": [ "string", "null" ] }, "limit": { "default": null, "format": "uint", "minimum": 0, "type": [ "integer", "null" ] } }, "title": "HistoryReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "definitions": { "HistoryEntry": { "properties": { "at": { "type": "string" }, "duration_ms": { "format": "uint64", "minimum": 0, "type": [ "integer", "null" ] }, "row_count": { "format": "uint", "minimum": 0, "type": [ "integer", "null" ] }, "sql": { "type": "string" }, "verb": { "type": "string" } }, "required": [ "at", "sql", "verb" ], "type": "object" } }, "properties": { "count": { "format": "uint", "minimum": 0, "type": "integer" }, "entries": { "items": { "$ref": "#/definitions/HistoryEntry" }, "type": "array" } }, "required": [ "count", "entries" ], "title": "HistoryResp", "type": "object" } }, { "description": "List all configured databases with connection details (driver, credential-redacted URL, pool settings, TLS mode). Config only — no health checks or live pool statistics.", "metadata": {}, "name": "database::listDatabases", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "title": "ListDatabasesReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "definitions": { "DatabaseInfo": { "properties": { "driver": { "description": "\"postgres\" | \"mysql\" | \"sqlite\".", "type": "string" }, "name": { "description": "Logical key (e.g. \"primary\").", "type": "string" }, "pool": { "$ref": "#/definitions/PoolInfo" }, "tls": { "$ref": "#/definitions/TlsInfo" }, "url": { "description": "Connection URL with credentials redacted.", "type": "string" } }, "required": [ "driver", "name", "pool", "tls", "url" ], "type": "object" }, "PoolInfo": { "description": "Pool settings echoed back from config (no live stats).", "properties": { "acquire_timeout_ms": { "format": "uint64", "minimum": 0, "type": "integer" }, "idle_timeout_ms": { "format": "uint64", "minimum": 0, "type": "integer" }, "max": { "format": "uint32", "minimum": 0, "type": "integer" } }, "required": [ "acquire_timeout_ms", "idle_timeout_ms", "max" ], "type": "object" }, "TlsInfo": { "description": "TLS settings. `ca_cert` is reported as a presence boolean only — never the path, which would leak filesystem layout.", "properties": { "ca_cert_present": { "type": "boolean" }, "mode": { "$ref": "#/definitions/TlsMode" }, "trust_native": { "type": "boolean" } }, "required": [ "ca_cert_present", "mode", "trust_native" ], "type": "object" }, "TlsMode": { "oneOf": [ { "description": "No TLS. Plaintext connection. Local-dev only.", "enum": [ "disable" ], "type": "string" }, { "description": "TLS handshake required; certificate chain validated; hostname NOT verified. Matches libpq's `sslmode=require`. The default.", "enum": [ "require" ], "type": "string" }, { "description": "TLS handshake required; certificate chain validated; certificate hostname must match the URL host. Matches libpq's `sslmode=verify-full`.", "enum": [ "verify-full" ], "type": "string" } ] } }, "properties": { "count": { "format": "uint", "minimum": 0, "type": "integer" }, "databases": { "items": { "$ref": "#/definitions/DatabaseInfo" }, "type": "array" } }, "required": [ "count", "databases" ], "title": "ListDatabasesResp", "type": "object" } }, { "description": "List the saved queries for a database, sorted by name.", "metadata": {}, "name": "database::listSavedQueries", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "db": { "default": null, "type": [ "string", "null" ] } }, "title": "ListSavedReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "definitions": { "SavedQuery": { "properties": { "description": { "type": [ "string", "null" ] }, "id": { "type": "string" }, "name": { "type": "string" }, "saved_at": { "type": "string" }, "sql": { "type": "string" } }, "required": [ "id", "name", "saved_at", "sql" ], "type": "object" } }, "properties": { "count": { "format": "uint", "minimum": 0, "type": "integer" }, "queries": { "items": { "$ref": "#/definitions/SavedQuery" }, "type": "array" } }, "required": [ "count", "queries" ], "title": "ListSavedResp", "type": "object" } }, { "description": "List every table and view in a database, with its kind and (on postgres) its schema. Reads the driver's own catalog, so no dialect-specific SQL is needed from the caller.", "metadata": {}, "name": "database::listTables", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "db": { "default": null, "description": "Logical database name. Optional — omitting it targets the sole configured database, or `primary` when several are configured.", "type": [ "string", "null" ] }, "timeout_ms": { "default": 30000, "format": "uint64", "minimum": 0, "type": "integer" } }, "title": "ListTablesReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "definitions": { "TableKind": { "enum": [ "table", "view" ], "type": "string" }, "TableRef": { "description": "A relation. `schema` is populated only where the driver has a meaningful namespace above the table (postgres); it is never concatenated into `name`, because `analytics.events` and a table literally called `analytics.events` are different things and callers must be able to tell them apart.", "properties": { "kind": { "$ref": "#/definitions/TableKind" }, "name": { "type": "string" }, "schema": { "type": [ "string", "null" ] } }, "required": [ "kind", "name" ], "type": "object" } }, "properties": { "count": { "format": "uint", "minimum": 0, "type": "integer" }, "tables": { "items": { "$ref": "#/definitions/TableRef" }, "type": "array" } }, "required": [ "count", "tables" ], "title": "ListTablesResp", "type": "object" } }, { "description": "Internal: reload connection pools from the authoritative configuration when it changes.", "metadata": { "internal": true }, "name": "database::on-config-change", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "description": "Event delivered to the internal `database::on-config-change` handler. A struct (not `Value`) keeps the request schema concrete; the handler re-fetches the configuration id; unknown fields are ignored.", "properties": { "id": { "default": null, "description": "Configuration id that changed (advisory; the handler re-fetches the value). Schema-only: kept to publish a typed request schema; the handler ignores it.", "type": [ "string", "null" ] } }, "title": "OnConfigChangeEvent", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "description": "Ack returned by the internal `database::on-config-change` handler.", "properties": { "ok": { "type": "boolean" } }, "required": [ "ok" ], "title": "OnConfigChangeResponse", "type": "object" } }, { "description": "Prepare a parameterized SQL statement once for repeated runs.", "metadata": {}, "name": "database::prepareStatement", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "db": { "default": null, "description": "Logical database name. Optional — omitting it targets the sole configured database, or `primary` when several are configured.", "type": [ "string", "null" ] }, "sql": { "type": "string" }, "ttl_seconds": { "default": 3600, "format": "uint64", "minimum": 0, "type": "integer" } }, "required": [ "sql" ], "title": "PrepareReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "definitions": { "HandleResponse": { "properties": { "expires_at": { "format": "date-time", "type": "string" }, "id": { "type": "string" } }, "required": [ "expires_at", "id" ], "type": "object" } }, "properties": { "handle": { "$ref": "#/definitions/HandleResponse" } }, "required": [ "handle" ], "title": "PrepareResp", "type": "object" } }, { "description": "Run a read-only SQL query and return the result rows.", "metadata": {}, "name": "database::query", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "db": { "default": null, "description": "Logical database name. Optional — omitting it targets the sole configured database, or `primary` when several are configured.", "type": [ "string", "null" ] }, "params": { "default": [], "items": true, "type": "array" }, "sql": { "type": "string" }, "timeout_ms": { "default": 30000, "format": "uint64", "minimum": 0, "type": "integer" } }, "required": [ "sql" ], "title": "QueryReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "definitions": { "ColumnMeta": { "properties": { "name": { "type": "string" }, "type": { "type": "string" } }, "required": [ "name", "type" ], "type": "object" } }, "properties": { "columns": { "items": { "$ref": "#/definitions/ColumnMeta" }, "type": "array" }, "row_count": { "format": "uint", "minimum": 0, "type": "integer" }, "rows": { "items": { "additionalProperties": true, "type": "object" }, "type": "array" } }, "required": [ "columns", "row_count", "rows" ], "title": "QueryResp", "type": "object" } }, { "description": "Roll back an open SQL transaction, discarding its uncommitted writes.", "metadata": {}, "name": "database::rollbackTransaction", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "transaction_id": { "type": "string" } }, "required": [ "transaction_id" ], "title": "RollbackTxReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "rolled_back": { "type": "boolean" } }, "required": [ "rolled_back" ], "title": "RollbackTxResp", "type": "object" } }, { "description": "Run a prepared SQL statement by handle, passing fresh parameters.", "metadata": {}, "name": "database::runStatement", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "handle_id": { "type": "string" }, "params": { "default": [], "items": true, "type": "array" } }, "required": [ "handle_id" ], "title": "RunReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "definitions": { "ColumnMeta": { "properties": { "name": { "type": "string" }, "type": { "type": "string" } }, "required": [ "name", "type" ], "type": "object" } }, "properties": { "columns": { "items": { "$ref": "#/definitions/ColumnMeta" }, "type": "array" }, "row_count": { "format": "uint", "minimum": 0, "type": "integer" }, "rows": { "items": { "additionalProperties": true, "type": "object" }, "type": "array" } }, "required": [ "columns", "row_count", "rows" ], "title": "QueryResp", "type": "object" } }, { "description": "Save a named query against a database. Stored in the state worker, so it survives restarts and an agent can save one for a human to find in the console. Saving under an existing name replaces it.", "metadata": {}, "name": "database::saveQuery", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "db": { "default": null, "type": [ "string", "null" ] }, "description": { "default": null, "type": [ "string", "null" ] }, "name": { "type": "string" }, "sql": { "type": "string" } }, "required": [ "name", "sql" ], "title": "SaveQueryReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "id": { "type": "string" }, "replaced": { "type": "boolean" } }, "required": [ "id", "replaced" ], "title": "SaveQueryResp", "type": "object" } }, { "description": "Replace the stored layout for a table. Widths are clamped to a usable range; columns the table no longer has are kept rather than rejected, so a rename degrades to a missing width instead of an error.", "metadata": {}, "name": "database::saveTableView", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "db": { "default": null, "type": [ "string", "null" ] }, "hidden": { "default": [], "description": "Columns the reader has hidden. Order is not meaningful.", "items": { "type": "string" }, "type": "array" }, "order": { "default": [], "description": "Column display order. Unlisted columns keep their natural position after the listed ones.", "items": { "type": "string" }, "type": "array" }, "table": { "type": "string" }, "widths": { "additionalProperties": { "format": "double", "type": "number" }, "default": {}, "description": "Per-column pixel width. Absent means \"size to content\".", "type": "object" } }, "required": [ "table" ], "title": "SaveTableViewReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "saved": { "type": "boolean" } }, "required": [ "saved" ], "title": "SaveTableViewResp", "type": "object" } }, { "description": "Diagram the database schema: tables as positioned nodes, foreign keys as routed edges. Also reports hub degree per table, isolated tables and remaining edge crossings. Reads the whole catalog in a handful of queries rather than one per table.", "metadata": {}, "name": "database::schemaDiagram", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "db": { "default": null, "type": [ "string", "null" ] }, "depth": { "default": 1, "description": "How many foreign-key hops out from `focus` to include. Ignored without one. 1 is the table and its direct relations.", "format": "uint", "minimum": 0, "type": "integer" }, "focus": { "default": null, "description": "Lay out only the neighbourhood of this table, `depth` hops out.", "type": [ "string", "null" ] }, "include_views": { "default": false, "type": "boolean" }, "max_tables": { "default": 200, "format": "uint", "minimum": 0, "type": "integer" }, "timeout_ms": { "default": 30000, "format": "uint64", "minimum": 0, "type": "integer" } }, "title": "SchemaDiagramReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "definitions": { "DiagramColumn": { "properties": { "foreign_key": { "type": "boolean" }, "name": { "type": "string" }, "nullable": { "type": "boolean" }, "primary_key": { "type": "boolean" }, "type": { "type": "string" } }, "required": [ "foreign_key", "name", "nullable", "primary_key", "type" ], "type": "object" }, "DiagramComponent": { "description": "One connected group of tables and its bounding box.", "properties": { "h": { "format": "double", "type": "number" }, "hub": { "description": "The most-referenced table in the group, if it has more than one member.", "type": [ "string", "null" ] }, "index": { "description": "Stable index, in layout order.", "format": "uint", "minimum": 0, "type": "integer" }, "tables": { "description": "Member tables, by node id.", "items": { "type": "string" }, "type": "array" }, "w": { "format": "double", "type": "number" }, "x": { "format": "double", "type": "number" }, "y": { "format": "double", "type": "number" } }, "required": [ "h", "index", "tables", "w", "x", "y" ], "type": "object" }, "DiagramEdge": { "properties": { "from": { "type": "string" }, "from_column": { "type": "string" }, "points": { "description": "Polyline, already routed. Anchored on the column row where the column is visible, on the node edge otherwise.", "items": { "$ref": "#/definitions/Point" }, "type": "array" }, "self_loop": { "type": "boolean" }, "to": { "type": "string" }, "to_column": { "type": "string" } }, "required": [ "from", "from_column", "points", "self_loop", "to", "to_column" ], "type": "object" }, "DiagramNode": { "properties": { "columns": { "items": { "$ref": "#/definitions/DiagramColumn" }, "type": "array" }, "degree": { "description": "Number of foreign keys touching this table, in or out. Renderers use it to emphasise hubs.", "format": "uint32", "minimum": 0, "type": "integer" }, "h": { "format": "double", "type": "number" }, "hidden_columns": { "description": "Columns not drawn because of `MAX_ROWS`.", "format": "uint", "minimum": 0, "type": "integer" }, "rank": { "format": "uint32", "minimum": 0, "type": "integer" }, "schema": { "type": [ "string", "null" ] }, "table": { "type": "string" }, "w": { "format": "double", "type": "number" }, "x": { "format": "double", "type": "number" }, "y": { "format": "double", "type": "number" } }, "required": [ "columns", "degree", "h", "hidden_columns", "rank", "table", "w", "x", "y" ], "type": "object" }, "Point": { "properties": { "x": { "format": "double", "type": "number" }, "y": { "format": "double", "type": "number" } }, "required": [ "x", "y" ], "type": "object" } }, "properties": { "components": { "description": "Connected groups, with the box that encloses each. A schema is usually several independent clusters rather than one graph, and saying so is most of what makes a large diagram readable — a reader can take in \"four unrelated groups\" at a glance instead of scanning for edges that are not there.", "items": { "$ref": "#/definitions/DiagramComponent" }, "type": "array" }, "crossings": { "description": "Edge crossings remaining after ordering. Lower is a tidier diagram.", "format": "uint32", "minimum": 0, "type": "integer" }, "edges": { "items": { "$ref": "#/definitions/DiagramEdge" }, "type": "array" }, "focus": { "description": "Echoed when the caller asked for a neighbourhood rather than the whole schema.", "type": [ "string", "null" ] }, "frontier": { "default": [], "description": "Tables one hop beyond what was drawn. Non-empty means there is more to expand into, which is the difference between a diagram that looks complete and one that says where it stops.", "items": { "type": "string" }, "type": "array" }, "height": { "format": "double", "type": "number" }, "isolated": { "description": "Tables with no foreign keys at all, placed on a trailing shelf.", "items": { "type": "string" }, "type": "array" }, "nodes": { "items": { "$ref": "#/definitions/DiagramNode" }, "type": "array" }, "truncated": { "type": "boolean" }, "width": { "format": "double", "type": "number" } }, "required": [ "components", "crossings", "edges", "height", "isolated", "nodes", "truncated", "width" ], "title": "SchemaDiagramResp", "type": "object" } }, { "description": "Terminate a backend session, or cancel just its running statement with `cancel_only`. Takes an id from database::health. Separate from health because it is a write.", "metadata": {}, "name": "database::terminateQuery", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "cancel_only": { "default": false, "description": "Ask the backend to cancel the running statement but keep the session. The default terminates the session outright.", "type": "boolean" }, "db": { "default": null, "type": [ "string", "null" ] }, "id": { "description": "Backend pid (postgres) or connection id (mysql), as reported by `database::health`.", "type": "string" }, "timeout_ms": { "default": 15000, "format": "uint64", "minimum": 0, "type": "integer" } }, "required": [ "id" ], "title": "TerminateReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "id": { "type": "string" }, "terminated": { "type": "boolean" } }, "required": [ "id", "terminated" ], "title": "TerminateResp", "type": "object" } }, { "description": "Test a candidate database URL (plus optional TLS) with one throwaway connection. Configured pools are untouched; reports ok/driver/latency/server version; failures are data, not errors.", "metadata": {}, "name": "database::testConnection", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "definitions": { "TlsConfig": { "description": "TLS settings for a single database. Applies to postgres and mysql. Sqlite is local-file and ignores this block.\n\nDefault is `mode: require` — TLS handshake required, certificate chain validated against the system trust store, hostname verification skipped (matching libpq's `sslmode=require` semantics). Use `mode: verify-full` to additionally verify the certificate hostname matches the URL host, and `mode: disable` to opt out of TLS entirely (local-dev only).", "properties": { "ca_cert": { "default": null, "description": "Optional path to a PEM file containing one or more CA certificates. Additive by default — these certs **extend** the system trust store rather than replace it. Set `trust_native: false` for strict-isolation deployments that must only trust the operator-supplied bundle.", "type": [ "string", "null" ] }, "mode": { "allOf": [ { "$ref": "#/definitions/TlsMode" } ], "default": "require", "description": "TLS mode: `disable` (plaintext), `require` (default), or `verify-full`." }, "trust_native": { "default": true, "description": "When true (default), the system/native trust store is loaded in addition to any `ca_cert` bundle. Set to `false` to trust only the `ca_cert` certificates — useful when an operator wants to pin trust to a private CA and explicitly *not* accept the public web PKI.\n\nEffective for postgres. MySQL is forced-additive: `mysql_async`'s rustls path always loads the Mozilla `webpki_roots` bundle and extends it with `ca_cert` — there is no upstream knob to suppress the bundled roots, so `trust_native: false` only affects postgres.\n\nNote: with both `trust_native: false` *and* `ca_cert: None` on postgres, no trust roots are available; pool construction fails with `CONFIG_ERROR`.", "type": "boolean" } }, "type": "object" }, "TlsMode": { "oneOf": [ { "description": "No TLS. Plaintext connection. Local-dev only.", "enum": [ "disable" ], "type": "string" }, { "description": "TLS handshake required; certificate chain validated; hostname NOT verified. Matches libpq's `sslmode=require`. The default.", "enum": [ "require" ], "type": "string" }, { "description": "TLS handshake required; certificate chain validated; certificate hostname must match the URL host. Matches libpq's `sslmode=verify-full`.", "enum": [ "verify-full" ], "type": "string" } ] } }, "properties": { "timeout_ms": { "default": null, "description": "Overall budget for the attempt. Default 5000, capped at 30000.", "format": "uint64", "minimum": 0, "type": [ "integer", "null" ] }, "tls": { "anyOf": [ { "$ref": "#/definitions/TlsConfig" }, { "type": "null" } ], "default": null, "description": "TLS settings to probe with. Defaults like a configured database (mode `require`) when omitted." }, "url": { "description": "Connection url to probe (`postgres://…`, `mysql://…`, `sqlite:…`).", "type": "string" } }, "required": [ "url" ], "title": "TestConnectionReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "driver": { "description": "\"postgres\" | \"mysql\" | \"sqlite\" | \"unknown\".", "type": "string" }, "latency_ms": { "description": "Wall time of the whole attempt.", "format": "uint64", "minimum": 0, "type": "integer" }, "message": { "description": "Why the probe failed (credentials scrubbed). Absent on success.", "type": [ "string", "null" ] }, "ok": { "description": "Whether a connection was established and answered a query.", "type": "boolean" }, "server_version": { "description": "Server version string, when the probe got far enough to ask.", "type": [ "string", "null" ] } }, "required": [ "driver", "latency_ms", "ok" ], "title": "TestConnectionResp", "type": "object" } }, { "description": "Run a list of SQL statements in one atomic transaction. A statement's RETURNING rows come back in results[].rows and ride its row-changed event.", "metadata": {}, "name": "database::transaction", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "definitions": { "TxStmtReq": { "properties": { "params": { "default": [], "items": true, "type": "array" }, "sql": { "description": "One statement. A `RETURNING` clause in the SQL (SQLite, Postgres) puts the rows in this step's `results[].rows` and on the `database::row-changed` event fired for it after commit.", "type": "string" } }, "required": [ "sql" ], "type": "object" } }, "properties": { "db": { "default": null, "description": "Logical database name. Optional — omitting it targets the sole configured database, or `primary` when several are configured.", "type": [ "string", "null" ] }, "isolation": { "default": null, "type": [ "string", "null" ] }, "statements": { "items": { "$ref": "#/definitions/TxStmtReq" }, "type": "array" } }, "required": [ "statements" ], "title": "TxReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "definitions": { "TxStepResp": { "properties": { "affected_rows": { "format": "uint64", "minimum": 0, "type": "integer" }, "rows": { "items": { "items": true, "type": "array" }, "type": "array" } }, "required": [ "affected_rows", "rows" ], "type": "object" } }, "properties": { "committed": { "type": "boolean" }, "error": true, "failed_index": { "format": "uint", "minimum": 0, "type": [ "integer", "null" ] }, "results": { "items": { "$ref": "#/definitions/TxStepResp" }, "type": [ "array", "null" ] } }, "required": [ "committed" ], "title": "TxResp", "type": "object" } }, { "description": "Execute a write inside an open transaction; put RETURNING in the SQL to get rows back. BEGIN/COMMIT/ROLLBACK are rejected; use commit/rollbackTransaction.", "metadata": {}, "name": "database::transactionExecute", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "params": { "default": [], "items": true, "type": "array" }, "returning": { "default": [], "description": "Optional, and NOT what produces rows: never adds a `RETURNING` clause — write it into `sql`. SQLite refuses a non-empty list when the statement returns no rows (`RETURNING_MISMATCH`); Postgres and MySQL ignore it with a warning.", "items": { "type": "string" }, "type": "array" }, "sql": { "description": "The write statement. Put a `RETURNING` clause in the SQL itself to get rows back and onto the `database::row-changed` event that `commitTransaction` fires for this write (SQLite, Postgres; MySQL has no RETURNING).", "type": "string" }, "transaction_id": { "type": "string" } }, "required": [ "sql", "transaction_id" ], "title": "TxExecuteReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "affected_rows": { "format": "uint64", "minimum": 0, "type": "integer" }, "last_insert_id": { "type": [ "string", "null" ] }, "returned_rows": { "items": { "additionalProperties": true, "type": "object" }, "type": "array" } }, "required": [ "affected_rows", "returned_rows" ], "title": "TxExecuteResp", "type": "object" } }, { "description": "Query rows inside an open transaction.", "metadata": {}, "name": "database::transactionQuery", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "properties": { "params": { "default": [], "items": true, "type": "array" }, "sql": { "type": "string" }, "transaction_id": { "type": "string" } }, "required": [ "sql", "transaction_id" ], "title": "TxQueryReq", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "definitions": { "ColumnMeta": { "properties": { "name": { "type": "string" }, "type": { "type": "string" } }, "required": [ "name", "type" ], "type": "object" } }, "properties": { "columns": { "items": { "$ref": "#/definitions/ColumnMeta" }, "type": "array" }, "row_count": { "format": "uint", "minimum": 0, "type": "integer" }, "rows": { "items": { "additionalProperties": true, "type": "object" }, "type": "array" } }, "required": [ "columns", "row_count", "rows" ], "title": "QueryResp", "type": "object" } }, { "description": "Serve the database worker's injected console UI assets (content function for its console:script / console:style triggers).", "metadata": { "internal": true }, "name": "database::ui-content", "request_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "description": "Input of the content function: the console asks for one asset by path.", "properties": { "path": { "description": "The asset path from the trigger config (e.g. `state/page.js`).", "type": "string" } }, "required": [ "path" ], "title": "UiContentInput", "type": "object" }, "response_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "description": "Output of the content function.", "properties": { "content": { "description": "The asset source, verbatim.", "type": "string" }, "content_type": { "description": "MIME type the console should serve the asset with.", "type": "string" } }, "required": [ "content", "content_type" ], "title": "UiContentResult", "type": "object" } } ], "triggers": [ { "description": "Fires after a row change commits, filtered by `db`, optional `table`, and optional `ops`. On a `capture: statements` database (the default) it reports only writes made THROUGH this worker, with the writer's own RETURNING rows as `returning` — a listener that needs to know WHICH row changed depends on the writer putting `RETURNING ` in its SQL. On a `capture: native` database it reports any client's committed writes, with the changed rows' primary keys as `returning` (capped; `truncated` flags the rest). Best-effort delivery, no replay.", "invocation_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "additionalProperties": false, "definitions": { "Op": { "description": "The kind of row change a statement makes.", "oneOf": [ { "enum": [ "insert", "update", "delete" ], "type": "string" }, { "description": "A write that is none of the above: a CTE-wrapped statement, `MERGE`, a driver-specific form.", "enum": [ "other" ], "type": "string" } ] } }, "description": "Per-binding config: which database, and optionally which table.", "properties": { "db": { "description": "Database handle, as named in the worker's config.", "type": "string" }, "ops": { "default": null, "description": "Operation filter. Omit to hear every operation.", "items": { "$ref": "#/definitions/Op" }, "type": [ "array", "null" ] }, "table": { "default": null, "description": "Table filter. Matched case-insensitively and ignoring a schema qualifier. Optional on a `capture: statements` database (omit to hear every table); required on a `capture: native` one, where per-table database triggers are what make other clients' writes visible.", "type": [ "string", "null" ] } }, "required": [ "db" ], "title": "RowChangedConfig", "type": "object" }, "metadata": {}, "name": "database::row-changed", "return_schema": { "$schema": "http://json-schema.org/draft-07/schema#", "definitions": { "Op": { "description": "The kind of row change a statement makes.", "oneOf": [ { "enum": [ "insert", "update", "delete" ], "type": "string" }, { "description": "A write that is none of the above: a CTE-wrapped statement, `MERGE`, a driver-specific form.", "enum": [ "other" ], "type": "string" } ] } }, "description": "What a subscriber receives.", "properties": { "affected_rows": { "description": "Rows the statement changed. Exact even when `returning` is capped.", "format": "uint64", "minimum": 0, "type": "integer" }, "at": { "description": "Epoch millis at emit time.", "format": "int64", "type": "integer" }, "db": { "type": "string" }, "op": { "$ref": "#/definitions/Op" }, "returning": { "description": "Which rows changed, when that is known. On a `capture: statements` database this is the writer's own `RETURNING` projection — whatever columns the writer's SQL named, nothing more — so it is absent when the writer's SQL had no `RETURNING` clause. On a `capture: native` database it is the primary-key columns of each changed row (UPDATE reports the new key), capped at 100 rows per event; absent for a table without a primary key. Values are encoded like `database::query` returns them for the same column.", "items": { "additionalProperties": true, "type": "object" }, "type": [ "array", "null" ] }, "table": { "description": "`null` when the statement was recognisably a write but its table could not be read out of the SQL (a CTE-wrapped write, for example). Always set on a `capture: native` database.", "type": [ "string", "null" ] }, "truncated": { "description": "True when `returning` holds fewer keys than `affected_rows` because the per-event cap dropped the rest (native capture only). Omitted when false.", "type": "boolean" } }, "required": [ "affected_rows", "at", "db", "op" ], "title": "RowChangedEvent", "type": "object" } } ] } ```