---
title: "DevPlace Database API Service"
description: "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."
language: null
framework: null
category: api_design
tags:
  - devplace
  - api
  - http-api
  - database
  - read-only
  - sql
  - websocket
  - authorization
  - audit-log
  - admin
keywords:
  - devplace /dbapi read only table endpoints
  - devplace dbapi query validated sql
  - devplace natural language to sql designer
  - devplace dbapi deny list sessions password_resets
  - devplace primary administrator dbapi 403 audit
last_updated: 2026-08-12
difficulty: advanced
version: "DevPlace (devplace.net), documented 2026-08"
related:
  - ../../databases/sql/general_sql_patterns.md
  - ../../core/security/secure_coding.md
  - README.md
  - authentication.md
  - conventions_and_errors.md
search_priority: normal
status: published
---

# DevPlace 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 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](https://devplace.net/docs/#doc-architecture-jobs), the [AI gateway](https://devplace.net/docs/#doc-services-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.get_primary_admin_uid` / `utils.is_primary_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`, `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.<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 `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 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, 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

The **primary administrator's** Devii assistant exposes the same read-only capability conversationally through primary-administrator-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).

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:

- `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: 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.