API reference · Data plane

SQL

Run one SQLite statement against the project's database, with a deliberately narrow grammar and guard rails that protect the platform columns and the access presets.

The same two routes exist on the management plane for an account key: POST /v1/projects/:pid/sql and GET /v1/projects/:pid/sql/history. A pk_ is always refused with 401.

The grammar#

SELECT <cols|*> FROM <table> [WHERE <col> <op> <value> [AND ...]] [ORDER BY <col> [ASC|DESC]] [LIMIT <n>]
INSERT INTO <table> (<cols>) VALUES (<values>) [, (<values>) ...]
UPDATE <table> SET <col> = <value> [, ...] [WHERE ...]
DELETE FROM <table> [WHERE ...]
CREATE TABLE <table> (<col> <TEXT|INTEGER|REAL|BOOLEAN|JSON>, ...)
ALTER TABLE <table> ADD COLUMN <col> <type>
ALTER TABLE <table> DROP COLUMN <col>
ALTER TABLE <table> RENAME COLUMN <col> TO <col>
DROP TABLE <table>

• One statement per request. No OR, no join, no aggregate, no subquery.
• At most 40 AND conditions in a WHERE, at most 40 assignments in a SET; VALUES has no cap on tuples. The database engine accepts 100 bound parameters per statement, and these ceilings are what fits.
• Column types: TEXT, INTEGER, REAL, BOOLEAN, JSON. This is SQLite — no SERIAL, jsonb, timestamptz or now().
• A CREATE TABLE never declares the five platform columns: the platform adds them. A table created here starts on the prive preset; change it with PATCH /v1/tables/:table or the set_table_preset tool.

Guard rails#

1. The platform columns (id, owner_id, space_id, created_at, updated_at) cannot be dropped, renamed, retyped or written → 403 forbidden.
2. The reserved prefixes _mb_, sqlite_ and _cf_ cannot be read, altered or created, whatever the key → 403 forbidden.
3. After every successful DDL the schema is rescanned. A table with no declared preset is forced to prive, and a warning is written in the request log.
4. Guard rails are applied to the parsed statement — the unquoted table name, the statement type — never to its text. Case, quoting, backticks or comments do not get around them.

Destructive statements confirm in two steps#

A statement is destructive when it does not name its target: DROP TABLE, ALTER TABLE … DROP COLUMN, ALTER TABLE … RENAME COLUMN, and a DELETE or UPDATE whose WHERE carries no equality on id. Row-by-row CRUD through the rows routes is never destructive.

The first call answers 428 confirmation_required with will_destroy (what would happen) and a confirmation_token; nothing has run. The second call, same query plus the confirmation_token in the body, executes. The token is single-use, lives 5 minutes, and is bound to the account, the project, the key class, the computed effect and the state of the target — if the table changed in between, the API answers 428 again with a fresh token.

An sk_ confirms itself: it is the project's own server. An ak_ must also present X-Account-Session: without a signed-in human it receives 401, and no token is issued. The reason is written in The human boundary.

History#

The history keeps the text of the last 50 statements, their status, row count, duration and timestamp — never their results. Two things follow. The query text carries the literals of your INSERT and UPDATE statements (e-mail addresses, tokens, business identifiers), so the history is not a confidentiality boundary. And the history is the project's, not a key's: sk_ and ak_ see the same 50 entries. It is bounded to 50 entries and 30 days, purged when a statement is written.

Idempotency#

POST /v1/sql deliberately ignores Idempotency-Key: re-running a statement is the wanted behaviour, and an INSERT replayed must insert again.