REST API reference

The /v1/* REST API is the bearer-token surface. Same endpoints power the SDK and (under the hood) the MCP server. Base URL: https://api.persql.com.

Authentication

Every request needs:

Authorization: Bearer psql_live_xxxxxxxxxxxxxxxxxxxx

The token is namespace-scoped — the :ns path segment must match the token’s namespace, otherwise the API returns 403 Token namespace mismatch.

`POST /v1/db/:ns/:db/query`

Run a single SQL statement.

Body:

{
  "sql": "SELECT id, email FROM customers WHERE id = ?",
  "params": [42]
}

Response:

{
  "success": true,
  "data": {
    "columns": ["id", "email"],
    "rows": [[42, "ada@example.com"]],
    "rowsRead": 1,
    "rowsWritten": 0
  }
}

Headers:

Idempotency-Key: <key> — 24h-cached replay (see Idempotency). Response includes Idempotency-Replayed: true on a hit.

`POST /v1/db/:ns/:db/batch`

Run up to 100 statements in one round-trip.

Body:

{
  "statements": [
    { "sql": "INSERT INTO orders (id, total) VALUES (?, ?)", "params": ["o1", 99.5] },
    { "sql": "INSERT INTO orders (id, total) VALUES (?, ?)", "params": ["o2", 12.0] },
    { "sql": "SELECT COUNT(*) AS n FROM orders" }
  ],
  "transaction": false
}

transaction: true wraps the batch in BEGIN/COMMIT; the first error rolls everything back.

Response: array of QueryResult (one per statement).

`GET /v1/db/:ns/:db/tables`

List user-defined tables and their row counts.

Response:

{
  "success": true,
  "data": [
    { "name": "orders", "rowCount": 1284 },
    { "name": "customers", "rowCount": 56 }
  ]
}

Discoverability — `describe`, `search`, `doctor`

The three calls an agent should make on a fresh database, in order. All three are read-scoped; PUT /describe requires admin (it changes how every other client interprets the schema).

`GET /v1/db/:ns/:db/describe`

Returns the entire schema graph plus stored semantic descriptions in one round-trip.

{
  "success": true,
  "data": {
    "databaseDescription": "Order data for acme",
    "tables": [
      {
        "table": "orders",
        "description": "One row per checkout",
        "columns": [
          { "name": "id", "type": "INTEGER", "pk": true, "notNull": true,
            "description": "" },
          { "name": "customer_id", "type": "INTEGER", "pk": false,
            "notNull": true, "description": "FK → customers.id" }
        ],
        "foreignKeys": [
          { "from": "customer_id", "toTable": "customers", "toColumn": "id" }
        ]
      }
    ]
  }
}

`PUT /v1/db/:ns/:db/describe`

Persist semantic docs. Pass any subset; only the fields you set are updated. Requires an admin-role token.

{
  "databaseDescription": "Order data for acme",
  "tables": [
    {
      "table": "orders",
      "description": "One row per checkout",
      "columns": [{ "column": "customer_id", "description": "FK → customers.id" }]
    }
  ]
}

`GET /v1/db/:ns/:db/search?q=...&limit=25`

Natural-language ranked search across table names, column names, and stored descriptions.

{
  "success": true,
  "data": {
    "query": "customer billing addresses",
    "hits": [
      { "kind": "table", "table": "addresses", "column": null,
        "description": "...", "score": 6 }
    ]
  }
}

`GET /v1/db/:ns/:db/doctor`

Lints the schema for LLM-hostile patterns — missing primary keys, ambiguous column names, unindexed foreign keys.

{
  "success": true,
  "data": {
    "findings": [
      { "code": "missing_pk", "severity": "warning", "table": "events",
        "column": null,
        "message": "Table \"events\" has no primary key.",
        "suggestion": "Add an integer PRIMARY KEY..." }
    ],
    "tablesScanned": 12,
    "generatedAt": "2026-05-05T..."
  }
}

`meta` envelope on `query` / `batch`

Every successful POST /v1/.../query and POST /v1/.../batch response includes a meta object alongside data:

{
  "success": true,
  "data": { "columns": [...], "rows": [...], "rowsRead": 1, "rowsWritten": 0 },
  "meta": {
    "rowsRead": 1,
    "rowsWritten": 0,
    "durationMs": 4,
    "statementCount": 1,
    "costUsd": 0.000001,
    "snapshot": null,
    "queryLogId": "qlog_..."
  }
}

meta.snapshot is set when a destructive statement (DDL, unbounded DELETE/UPDATE, REPLACE) auto-captured a PITR-labeled snapshot before running. Pass X-PerSQL-No-Snapshot: 1 to opt out.

meta.queryLogId joins back to _persql_meta_query_log inside the database — agents can SELECT … WHERE id = ? to inspect the entry.

`GET /v1/db/:ns/:db/queries`

Engine telemetry — one entry per query/batch call. Server-side mirror of db.queryLog(). Same data lives at _persql_meta_query_log inside the database for in-SQL JOINs.

Query param	Notes
`since` / `until`	ISO timestamp filters
`tokenId`	Per-token cost partitioning
`status`	`ok` or `error`
`cursor` / `pageSize`	Pagination

Branch handoff

`POST /v1/db/:ns/:db/branches/:ref/pin`

Mint a single-use handoff token (phand_…) pinned to one (database, branch, role). Used by orchestrator agents to delegate to sub-agents.

{ "role": "write", "ttlSec": 900 }

Response:

{ "success": true, "data": { "token": "phand_...", "expiresAt": "..." } }

`POST /v1/handoff/claim`

Sub-agent side. Redeems a phand_… for a regular psql_live_… token scoped exactly to the pinned (database, branch, role). Single use: the handoff is consumed by this call. No bearer token required — the handoff itself is the proof.

{ "token": "phand_...", "name": "sub-agent-1" }

Token scopes

API tokens can carry per-resource scope rows that pin them to one or more (databaseId, branchRef, role) triples:

{
  "name": "ci-pr-42",
  "scopes": [
    { "databaseId": "db_...", "branchRef": "pr-42", "role": "write" }
  ]
}

Empty scopes = namespace-wide (back-compat). When set, the token can only touch the listed resources. Effective role for a request is min(token.role, scope.role).

Propose / apply

Pre-flight a write. See Propose / apply.

POST /v1/db/:ns/:db/propose
{ "sql": "...", "params": [...], "ttlSec": 600 }

Response data:

{
  "sql": "...",
  "plan": [[0, 0, 0, "SCAN ..."]],
  "estimatedAffectedRows": 137,
  "executionToken": "pmut_…",
  "expiresAt": "...",
  "action": "write"
}

POST /v1/db/:ns/:db/apply
{ "executionToken": "pmut_…" }

Single-use. A second call returns 404. Token is pinned to the originating bearer + database; mismatch returns 403.

Claim a branch

POST /v1/db/:ns/:db/claim_branch
{ "purpose": "...", "ttlSec": 3600, "role": "write" }

Returns { branchRef, databaseId, databaseSlug, namespaceSlug, token, tokenId, role, expiresAt, outcome }. The plaintext token is returned once. See Claim a branch.

Plan-Key (multi-step idempotency)

Pair Plan-Key (stable across the plan) with Plan-Step (stable per step) to make a multi-call plan resumable on retry. Only successful (2xx) responses are cached, 24h TTL, scoped per token. Cache hits return Plan-Replayed: true. See Idempotency → Multi-step plans.

Approval rules

A require_approval-matching write returns 403 with the approval envelope (approvalToken, approvalUrl, hits, expiresAt). After a member approves it in the console, the agent calls:

POST /v1/db/:ns/:db/redeem_approval
{ "approvalToken": "appr_…" }

deny-matching writes return 403 with hits but no token. See Approval rules.

Error envelope

Every error returns:

{
  "success": false,
  "error": "<human-readable message>",
  "errorDetail": {
    "kind": "unique_violation",
    "table": "users",
    "column": "email",
    "hint": "..."
  }
}

errorDetail is set on /v1/query, /v1/batch, /v1/apply, /v1/redeem_approval failures whenever the underlying SQLite error matches a known shape. See Structured SQL errors.

HTTP	When
`400`	Bad input (missing `sql`, malformed JSON, batch > 100, validation failure)
`401`	Missing / invalid bearer
`403`	Token namespace doesn’t match `:ns`
`404`	Database not found in namespace
`409`	Slug collision on resource creation
`429`	Per-IP flood control hit, or the billing meter is briefly unreachable. Headers: `Retry-After`. Throws `RateLimitError` in the SDK.
`500`	Internal error

Spending controls

There is no per-token throughput cap and no per-namespace request count cap. Each database is its own Durable Object — tenants cannot interfere — and the prepaid namespace balance is the only spend control: a token spends as fast as you call it until the balance returns 402. If you want a tighter blast-radius for a single key (CI, an end-user, a sub-agent), mint a separate namespace with its own balance and scope the token to it.

A coarse per-IP flood control still runs at the edge to short-circuit anonymous floods before they reach auth.

Telemetry

Every query and batch call writes a query event with namespaceId, dbId, tokenId, status, rowsRead, rowsWritten, durationMs, and statementCount. The customer console aggregates these into the Usage dashboard; the admin app uses them for billing reconciliation.

REST API reference

Authentication

POST /v1/db/:ns/:db/query

POST /v1/db/:ns/:db/batch

GET /v1/db/:ns/:db/tables

Discoverability — describe, search, doctor

GET /v1/db/:ns/:db/describe

PUT /v1/db/:ns/:db/describe

GET /v1/db/:ns/:db/search?q=...&limit=25

GET /v1/db/:ns/:db/doctor

meta envelope on query / batch

GET /v1/db/:ns/:db/queries