# Database API service The database API is a single, safe surface for **reading** every table in the platform. It exposes generic per-table reads, 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. The database API is **strictly read-only**: it can never insert, update, replace, delete, or restore data in any way. > 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 } ``` ## Reads per table The API exposes only two table-scoped read routes. There are no insert, update, delete, or restore endpoints, by design. - `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`). ``` GET /dbapi/bookmarks?filter.user_uid=u1&limit=20 -> { "table": "bookmarks", "rows": [ ... ], "count": 7, "next_cursor": null } GET /dbapi/users/uid/ -> { "table": "users", "row": { "uid": "...", "username": "...", ... } } ``` ## 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` (the database API is read-only and cannot change data); 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()`, or through any other part of the database API. The API cannot change data in any way. ## 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 read-only capability conversationally through admin-only tools: - `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). There are no write tools. Devii cannot insert, update, or delete data through the database API. ## 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 - The API is strictly read-only: there are no insert, update, delete, or restore routes or tools, so it can never change data. - 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. - Reads exclude soft-deleted rows by default (opt in with `?include_deleted=true`). - All Devii database tools are admin-only and read-only.