platforms/devplace/database_api_service.md

DevPlace Database API Service

The read-only data surface at /dbapi: generic per-table reads, the validated query() call, the natural-language-to-SQL designer, asynchronous execution over a websocket, the table deny list, and the primary-administrator authorization boundary.

devplaceapihttp-apidatabaseread-onlysqlwebsocketauthorizationaudit-logadmin

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 the primary administrator (the oldest Admin account). The database API is strictly read-only: it can never insert, update, replace, delete, or restore data in any way.

Audience: the primary administrator only.

It reuses the existing data layer (dataset with the production pragmas), the async job framework, the AI gateway, 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:

  • The primary administrator, and no one else, authenticated by session cookie or by their API key. The primary administrator is the earliest-created Admin account (resolved by database.getprimaryadminuid / utils.isprimary_admin, the same identity that gates backup downloads). Every other administrator is treated like a member here.

There is no internal-key or service-to-service access: the gateway internal key is not accepted. Anyone else (members, guests, non-primary administrators, and internal callers) receives 403 Forbidden, and the denial is written to the audit log as database.access.denied. There is no public, member-facing, junior-admin, or service 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, passwordresets, cachestate) and can be extended by the dbapidenytables 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.<col>=value (equality) or ?gte.<col>=, ?lte.<col>=, ?gt.<col>=, ?lt.<col>= (comparisons), full-text search common columns with ?search=, page with ?before=<cursor> 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/<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 dbapimaxrows.

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 deletedat IS NULL unless applysoft_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 (dbapinlmodel, blank uses the internal molodetz model), as is an optional operator preamble (dbapinlsystem_preamble). The call is attributed to the calling primary 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, statusurl, wsurl }.
  • 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

The primary administrator's Devii assistant exposes the same read-only capability conversationally through primary-administrator-only tools:

  • dblisttables, dbtableschema, dblistrows, dbgetrow, dbquery (SELECT only, and it surfaces any suspicious warnings), and dbdesign_query (natural language to SQL).

These tools are added to Devii's tool list only for the primary administrator. Members, guests, and non-primary administrators never see them in their session, so their Devii is not even aware the database API exists. 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:

  • dbapimaxrows - hard cap on rows returned by any query (default 5000).
  • dbapinlmodel - model used to design SQL from natural language (blank uses molodetz).
  • dbapinlsystem_preamble - optional operator text prepended to the NL-to-SQL prompt.
  • dbapidenytables - 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: the primary administrator (the oldest Admin) only, enforced on every route, audited on denial. Non-primary administrators, members, guests, and internal callers are all refused.
  • 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 primary-administrator-only and read-only, and are withheld from every other session's tool list.