# Database API service (`devplacepy/services/dbapi/`, `devplacepy/routers/dbapi/`)

This file documents the primary-administrator-only, read-only generic database API. Claude Code auto-loads it when a file under `devplacepy/services/dbapi/` is read or edited.

## Overview

`dbapi/` (routers package, mounted at `/dbapi`) is a **primary-administrator-only, strictly READ-ONLY** generic database API over `dataset` (never inserts/updates/deletes/restores; the write surface was removed because it bypassed every per-route admin safeguard). `tables.py` (`GET /tables`, `GET /{table}/schema`), `crud.py` (read only: `GET /{table}` + `GET /{table}/{key}/{value}`, `?include_deleted`), `query.py` (`POST /query` hard SELECT-only validated read-only run; `POST /query/async` + `GET /query/{uid}` + `WS /query/{uid}/ws` via `DbApiJobService` kind `dbquery`), `nl.py` (`POST /nl` natural-language->SQL via the AI gateway, returns validated SELECT, `execute=true` runs it read-only). **Auth = the PRIMARY administrator only** (the earliest-created Admin, `utils.is_primary_admin` / `database.get_primary_admin_uid` - the same identity that gates backup downloads) by session/api_key (`services/dbapi/policy.py`); every other administrator is refused like a member, and there is **no internal-key path** - the gateway `internal_gateway_key()` is NOT accepted (no service-to-service access). SQL validated by `services/dbapi/validate.py` (sqlglot classify + suspicious-flag + read-only `EXPLAIN` dry-run). Devii tools `db_*` are read-only (list/get/query/nl; no write tools) and flagged `requires_primary_admin=True`, so they are added to the LLM tool list ONLY for the primary administrator (every other session never sees them and Devii is unaware they exist). `services/pubsub/policy.py` resolves its own admin/internal actor and does NOT reuse `dbapi.policy.caller_for`, so the primary-admin restriction does not leak onto the pub/sub bus.

It exposes per-table reads, a validated raw `query()`, a natural-language-to-SQL designer backed by the internal AI gateway, and async query execution streamed over a websocket. **It can never insert, update, replace, delete, or restore data in any way** - there are no write endpoints and no write tools (this is a hard rule; removed because the generic write surface bypassed every per-route admin safeguard - role-change seniority guards, container privilege locks, the audit trail). Reuses the existing dataset helpers, the `JobService`/`ProgressHub`/lock-owner websocket pattern, the AI gateway, and the Devii action catalog - it does not re-implement any of them.

## Auth (single boundary, primary-admin ONLY)

`services/dbapi/policy.py` `caller_for(request)` returns a `Caller` ONLY for the **primary administrator** (a session OR api_key whose user passes `utils.is_primary_admin` - the earliest-created Admin, the same identity that gates backup downloads, resolved by `database.get_primary_admin_uid`), else `None`. **There is no internal-key path** - the gateway `internal_gateway_key()` is NOT accepted (removed at the user's request); there is no service-to-service access. Every other administrator is refused exactly like a member, so the `Caller.kind` is always `"admin"`. `require_caller` raises `DbApiDenied`; the router's `require_dbapi_caller` turns that into a `403` and an audited `database.access.denied`. Members, guests, non-primary admins, and internal callers never reach a handler. **Note:** `services/pubsub/policy.py` no longer reuses this `caller_for` (it would have leaked the primary-admin restriction into the pub/sub bus); it resolves its own admin/internal actor so any admin stays `privileged` on the bus.

## Table guard

`policy.assert_table(name)` enforces a name regex, existence in `db.tables`, and a deny-list (`DEFAULT_DENY = {sessions, password_resets, cache_state}` plus the `dbapi_deny_tables` setting). Used on every table-scoped path and indirectly by the validator's table extraction, so neither a crafted segment nor an NL-designed query can touch a denied table.

## Reads only (`services/dbapi/crud.py`, `routers/dbapi/crud.py`)

`GET /dbapi/{table}` (keyset pagination newest-first, `?filter.<col>=`, `?gte/lte/gt/lt.<col>=`, `?search=`, `?before=`, `?limit=`, `?include_deleted=`) and `GET /dbapi/{table}/{key}/{value}`. There are **no insert/update/delete/restore routes** - the router exposes only the two read handlers, and `services/dbapi/crud.py` keeps only read functions (`schema`, `list_rows`, `count_rows`, `get_row`). The only audit events the API emits are `database.access.denied`, `database.query`, and `database.nl.design`.

## `query()` is hard SELECT-only (`services/dbapi/validate.py`)

`classify()` parses with `sqlglot` (statement type, table list, `has_where/has_join/has_limit`, suspicious notes for missing `WHERE/JOIN/LIMIT`, multiple statements, `ATTACH/PRAGMA/VACUUM`). `validate_select()` rejects anything but a single SELECT, then `dry_run()` does `EXPLAIN` on a **separate read-only** connection (`file:...?mode=ro` + `PRAGMA query_only=ON`) for true validity. `run_select()` executes read-only. `POST /dbapi/query` returns `409` for non-SELECT (the database API is read-only; data cannot be changed through it), `400` for invalid SQL, else rows + a `suspicious` list. The database API can never mutate data through any path.

## NL-to-SQL (`services/dbapi/nl2sql.py`)

`POST /dbapi/nl` builds a system prompt from the table schema + 5 example rows + a soft-delete rule (auto `deleted_at IS NULL` unless `apply_soft_delete=false`), calls the internal gateway (`INTERNAL_GATEWAY_URL`, model `dbapi_nl_model` or `molodetz`, auth = the primary-admin caller's api_key for attribution), and **reprompts with the validator's error until the SQL validates** (max 3 attempts). Returns the validated SQL by default; `execute=true` also runs it read-only and returns rows.

## Async (`services/dbapi/service.py` `DbApiJobService`, kind `dbquery`)

`POST /dbapi/query/async` validates then `queue.enqueue`s; the service re-validates (defense in depth), streams rows in batches to its own `ProgressHub`, writes the full result to `config.DBAPI_DIR/{uid}/result.json`, and returns stats. `GET /dbapi/query/{uid}` (status), `GET /dbapi/query/{uid}/result` (rows from disk, extends retention), `WS /dbapi/query/{uid}/ws` (lock-owner gated, `4013` retry, snapshot-then-stream - the SEO pattern). Config on `/admin/services`: `dbapi_max_rows`, `dbapi_nl_model`, `dbapi_nl_system_preamble`, `dbapi_deny_tables`, plus the standard Jobs fields.

## Devii (primary-admin gated)

**Read-only** tools `db_list_tables`, `db_table_schema`, `db_list_rows`, `db_get_row`, `db_query` (SELECT-only; surface `suspicious`), and `db_design_query` (NL->SQL), all flagged `requires_primary_admin=True` (alongside `requires_admin=True`) in `actions/catalog.py`. The `Action.requires_primary_admin` flag is filtered by `Catalog.tool_schemas_for(authenticated, is_admin, is_primary_admin)` and enforced again in `Dispatcher.dispatch`, so these tools are **added to the LLM tool list only for the primary administrator** - every other administrator's (and member's/guest's) Devii never receives the schemas and is unaware the database API exists. `is_primary_admin` is threaded WS/Telegram/CLI -> `hub.get_or_create(is_primary_admin=)` -> `DeviiSession` -> `Dispatcher`, mirroring how `is_admin` flows (`routers/devii.py` `_resolve_ws_owner`, `services/telegram/bridge.py`, `services/devii/cli.py` which runs both flags `True` as the trusted local operator, and the headless scheduler in `service.py`). There are **no write tools** - the former `db_insert_row`/`db_update_row`/`db_delete_row` were removed along with the HTTP write routes, so Devii cannot change data through the database API.

## nginx

`/dbapi/query/<uid>/ws` has its own upgrade location.
