# Database API service The database API is a single, safe surface for reading and updating **every table** in the platform. It exposes generic CRUD per table, a validated read-only `query()`, a natural-language-to-SQL designer backed by the platform AI gateway, and asynchronous query execution streamed over a websocket. It is mounted at `/dbapi` and is reachable only by administrators or by internal services. > Audience: administrators, maintainers, and trusted internal services. It reuses the existing data layer (`dataset` with the production pragmas), the [async job framework](/docs/architecture-jobs.html), the [AI gateway](/docs/services-gateway.html), and the Devii action catalog. It does not introduce a second database or a new ORM. ## Who can call it There is exactly one authorization boundary, enforced on every route: - An **administrator** authenticated by session cookie or by an admin API key. - An **internal service** presenting the gateway internal key as `Authorization: Bearer ` or `X-API-KEY: `. Anyone else (members, guests) receives `403 Forbidden`, and the denial is written to the audit log as `database.access.denied`. There is no public or member-facing access. ## Tables and the deny list Every table-scoped route runs through a guard that validates the table name against a strict pattern, confirms the table exists, and rejects any table on the deny list. The deny list always contains the credential and session tables (`sessions`, `password_resets`, `cache_state`) and can be extended by the `dbapi_deny_tables` setting on the service config. The guard protects both the path segment and the table names referenced by a designed or submitted SQL query, so neither a crafted URL nor an AI-designed query can reach a denied table. ``` GET /dbapi/tables { "tables": [ { "name": "posts", "row_count": 1240, "soft_delete": true }, ... ], "count": 49 } GET /dbapi/posts/schema { "table": "posts", "columns": [ { "name": "uid", "type": "TEXT" }, ... ], "soft_delete": true, "row_count": 1240 } ``` ## CRUD per table All writes go through the structured CRUD, which is parameterized, soft-delete aware, and never executes raw SQL. The endpoints are: - `GET /dbapi/{table}` lists rows newest-first with keyset pagination. Filter with `?filter.=value` (equality) or `?gte.=`, `?lte.=`, `?gt.=`, `?lt.=` (comparisons), full-text search common columns with `?search=`, page with `?before=` and `?limit=` (max 500), and include soft-deleted rows with `?include_deleted=true`. - `GET /dbapi/{table}/{key}/{value}` returns one row where the key column equals the value (the key is usually `uid`). - `POST /dbapi/{table}` inserts a row. The body is the column map. Inserts are **born live**: `uid` and `created_at` are filled automatically when those columns exist, and soft-delete tables get `deleted_at: null` and `deleted_by: null` so the row is visible immediately. **Unknown columns are rejected**, so the API can never grow or pollute the schema. - `PATCH /dbapi/{table}/{key}/{value}` updates a row. `uid` and `id` can never be changed, and `updated_at` is set when present. - `DELETE /dbapi/{table}/{key}/{value}` removes a row. By default this is a **soft delete** (the row is stamped and disappears from normal reads but stays restorable from Trash). Pass `?hard=true` to permanently purge it, or for tables that have no soft-delete columns. - `POST /dbapi/{table}/{key}/{value}/restore` clears a soft delete. Every mutation writes an audit event (`database.row.insert`, `.update`, `.delete`, `.restore`). ``` POST /dbapi/bookmarks { "user_uid": "u1", "target_type": "post", "target_uid": "p1" } -> { "table": "bookmarks", "ok": true, "mode": "insert", "row": { "uid": "...", "deleted_at": null, ... } } DELETE /dbapi/bookmarks/uid/ -> { "ok": true, "mode": "soft", ... } POST /dbapi/bookmarks/uid//restore -> { "ok": true, "mode": "restore", ... } DELETE /dbapi/bookmarks/uid/?hard=true -> { "ok": true, "mode": "hard", ... } ``` ## Read-only query() `POST /dbapi/query` runs a single SQL `SELECT` and returns the rows. It is **hard SELECT-only**: any other statement is refused. Validation runs in three stages before a query executes: 1. **Parse and classify** with `sqlglot`: determine the statement type, the tables referenced, and whether the query has a `WHERE`, a `JOIN`, and a `LIMIT`. 2. **Flag suspicious shapes**: a `SELECT` with no `WHERE`, `JOIN`, or `LIMIT` (which scans an entire table), multiple statements, or `ATTACH`/`PRAGMA`/`VACUUM` style statements. 3. **Dry run** the statement with `EXPLAIN` on a **separate read-only connection** (opened `mode=ro` with `PRAGMA query_only=ON`), which validates the SQL against the real schema without executing its body. A non-`SELECT` returns `409 Conflict` with a hint to use the CRUD routes; an invalid `SELECT` returns `400`; a valid query returns the rows plus a `suspicious` list. Execution itself also happens on the read-only connection and is capped at `dbapi_max_rows`. ``` POST /dbapi/query { "sql": "SELECT uid, username FROM users WHERE role = 'Admin' LIMIT 20" } -> { "sql": "...", "valid": true, "rows": [ ... ], "row_count": 3, "truncated": false, "suspicious": [] } POST /dbapi/query { "sql": "DELETE FROM posts" } -> 409 { "valid": false, "statement_type": "delete", "error": "Only SELECT queries run through query(); ..." } POST /dbapi/query { "sql": "SELECT * FROM users" } -> 200 { "valid": true, "suspicious": ["SELECT has no WHERE, JOIN, or LIMIT and may return an entire table."], ... } ``` Mutations are never possible through `query()`. To change data, use the structured CRUD routes above. ## Ask in plain language `POST /dbapi/nl` turns a natural-language question about one table into a validated `SELECT`. The designer builds a system prompt from the table schema and a handful of example rows, asks the AI gateway to write the query, and then **re-prompts the model with the validator's error until the SQL validates** (up to three attempts). For soft-delete tables it instructs the model to add `deleted_at IS NULL` unless `apply_soft_delete` is set to false. By default it returns only the SQL; pass `execute: true` to also run it read-only and include the rows. ``` POST /dbapi/nl { "question": "all users registered longer than three days", "table": "users", "execute": true } -> { "sql": "SELECT * FROM users WHERE created_at < '...' AND deleted_at IS NULL", "valid": true, "attempts": 1, "applied_soft_delete": true, "executed": true, "rows": [ ... ], "row_count": 12 } ``` The model is configurable (`dbapi_nl_model`, blank uses the internal `molodetz` model), as is an optional operator preamble (`dbapi_nl_system_preamble`). The call is attributed to the calling administrator's API key so its cost rolls up under that user. ## Asynchronous queries For heavy or large result sets, run the query off the request path: - `POST /dbapi/query/async` validates the SQL, enqueues a `dbquery` job, and returns `{ uid, status_url, ws_url }`. - `GET /dbapi/query/{uid}` returns the job status. - `GET /dbapi/query/{uid}/result` returns the full result set (read from disk) and extends the retention window. - `WS /dbapi/query/{uid}/ws` streams live progress. Like every job websocket it is served only by the service lock owner: a non-owner worker closes with code `4013` and the client retries until it lands on the owner. On connect the socket replays any buffered frames, then streams `progress` frames and a terminal `done` (or `failed`) frame. The job writes its result to the runtime data directory (`config.DBAPI_DIR/{uid}/result.json`), outside the package, and the result is removed when the job's retention expires. ## Devii An administrator's Devii assistant exposes the same capability conversationally through admin-only tools: - Read: `db_list_tables`, `db_table_schema`, `db_list_rows`, `db_get_row`, `db_query` (SELECT only, and it surfaces any `suspicious` warnings), and `db_design_query` (natural language to SQL). - Write: `db_insert_row`, `db_update_row`, `db_delete_row`. Each is **confirmation gated**: the first call is refused and Devii must show the user exactly what will change and obtain explicit confirmation before calling again with `confirm=true`. The mutation tools pass arbitrary columns as a JSON object string in `values_json`. This is how the rule "a non-SELECT is always confirmed" is honored: raw SQL stays read-only, and every write is a confirm-gated CRUD tool. ## Configuration On `/admin/services` the **Database API** service (`dbquery`) exposes: - `dbapi_max_rows` - hard cap on rows returned by any query (default 5000). - `dbapi_nl_model` - model used to design SQL from natural language (blank uses `molodetz`). - `dbapi_nl_system_preamble` - optional operator text prepended to the NL-to-SQL prompt. - `dbapi_deny_tables` - comma separated extra tables to hide. - The standard job fields: artifact retention, maximum concurrent jobs, and job timeout. ## Security summary - One authorization boundary: administrator or internal key, enforced on every route, audited on denial. - Table allow/deny guard on every table-scoped path and on every table referenced by a query. - Raw SQL is always read-only, on a dedicated `query_only` connection, so even a validator miss cannot mutate. - CRUD rejects unknown columns, so the API never alters the schema. - Inserts are born live, deletes are soft and audited, reads exclude soft-deleted rows by default. - Devii read tools are admin-only; Devii write tools are admin-only and confirmation gated.