HTTP API

Every route is under https://api.persql.com/api/v1, takes and returns JSON, and requires a developer key as a bearer token: Authorization: Bearer persql_... (X-Api-Key: persql_... also works). These are the same routes the web app uses; there is no separate API.

Responses and errors

An error returns a status code and { "error": "<sentence>" }:

Status	Meaning
`400 bad request`	The body does not match the route.
`401 sign in first`	No key, or a revoked one.
`402`	The free week or the subscription has ended, the plan covers fewer databases than the account has, or the free week’s database has reached its size limit; the sentence says which. Reads still work and writes do not; saving a query counts as a write.
`404 not found`	The resource does not belong to you, or has been deleted.
`413 file too large`	A photo larger than one request can carry; resize it first.
`415 not a photo`	A file that is not one of the supported photo types.
`422 statement failed`	SQLite refused the SQL; `rejection.message` contains SQLite’s own message.
`429`	The assistant’s hourly limit on turns; `Retry-After` says when to try again.
`507`	The files stored for the account (photos, uploads and exports) have reached the plan’s storage limit, which is higher with a subscription; the sentence gives the amount. Deleting files frees space.

Times are milliseconds since the epoch. A cell is a string, a number, a boolean, null, or bytes as a base64 string.

Databases

GET /databases — your databases: id, name, tableCount, sizeBytes, pendingProposals (changes proposed and not yet approved or discarded), createdAt, updatedAt.

POST /databases { name } — creates a database and returns 201 with it. A name is up to 128 characters.

GET /databases/:id — the database with its tables, each { name, rowCount }.

PATCH /databases/:id { name } — renames it.

DELETE /databases/:id — deletes it with its backups, saved queries, the memories kept with it, conversation, uploads, jobs and files; returns 204.

GET /databases/:id/schema — tables, indexes, foreignKeys and views. Each table has its CREATE statement as sql and its columns, each with:

name, type, pk, notNull, defaultValue and generated;
choices: the literals of a CHECK (c IN (…)) constraint, or null;
references: the { table, column } of a foreign key, or null.

Rows and SQL

GET /databases/:id/tables/:table?offset=0&limit=100 — a page of a table’s rows, as { columns, columnInfo, indexes, rows, keyColumns, keys, total }. limit is at most 500.

columnInfo gives each column’s type, key, NOT NULL, default, whether it is generated, and its choices and references as the schema lists them. indexes are the table’s indexes.
keys[i] identifies row i by keyColumns: the rowid, under a name no column shadows, or the primary key of a WITHOUT ROWID table. So UPDATE t SET c = ? WHERE _rowid_ = ? addresses the row you see.
Rows are in key order unless sort=<column>&dir=asc|desc names another column; the key breaks ties.
q=<text> returns the rows in which any column contains the text.
filter=<JSON> is an array of up to 49 clauses { column, op, value }, all of which must hold. op is one of eq, ne, lt, lte, gt, gte, contains, notContains, starts, empty, notEmpty. empty and notEmpty take no value; contains, notContains and starts match the text as written, ignoring case in ASCII letters; the others compare under the column’s affinity, so "10" against an INTEGER column is compared as a number.
total counts the rows that pass the filters, so each page costs one count and one page of reads. count=false skips the count and returns total: null: request the count on the first page and reuse it.
Sorting on a column without an index sorts the whole table for each page; an index avoids this. For millions of rows, page by rowid in your own SQL instead.

POST /databases/:id/query { sql, params?, offset?, limit?, drawn? } — runs one statement of up to 33333 characters, binding its ? placeholders in order from up to 100 params. It returns { columns, rows, rowsRead, rowsWritten, changes, lastInsertRowid, offset, returned, wrote, ms, backup }.

A write always runs in full, and its RETURNING clause fills columns and rows.
backup is the backup that covers a statement that cannot be undone (the one just taken, or the one still current), or null, so a client can show it without fetching the list.
A read returns one page: limit rows (default 128) starting at offset (default 0), or fewer when the page would exceed a few megabytes. The next page starts at offset plus the number of rows returned.
The first page’s returned counts every row the statement returns. A later page (offset above 0) stops at its last row and has no returned, because counting would run the whole statement again: keep the first page’s count, and treat an empty page as the end.
A later page skips the earlier rows inside SQLite, and they still count as rows read. For millions of rows, page by a key in your own SQL: WHERE id > ? ORDER BY id LIMIT 100.
drawn: true returns the rows to draw a chart from instead: every row while there are at most 1024, and beyond that the first, last, lowest and highest row of each stretch per column, in the statement’s order. returned still counts every row.
A statement that writes counts as a write for the plan check. An UPDATE, DELETE, ALTER, DROP or upsert, in any statement of the string, takes a backup first (see Backups).

POST /databases/:id/batch { statements: [{ sql, params? }, …] } — runs up to 1024 statements in one transaction, so either all of them apply or none does, and returns { results, backup }: one result per statement, in order, and the backup as /query returns it. The plan check, the usage count and the backup rule are the same as for /query; if SQLite refuses a statement, the whole batch fails with a 422. A paste into the web app’s grid is one batch: an UPDATE … RETURNING for each row it covers, and a multi-row INSERT … RETURNING for the rows past the end.

Uploads and import

A file is uploaded to storage in parts, and an import job then reads it. There is no size limit.

POST /databases/:id/uploads { name, type } — starts an upload and returns 201 with { id, name, type, bytes, partBytes }. Send the file in parts of exactly partBytes bytes; the last part may be smaller.

PUT /databases/:id/uploads/:uploadId/parts/:n — uploads one part, with the raw bytes as the body and Content-Length set; n counts from 1. Returns { partNumber, etag }.

POST /databases/:id/uploads/:uploadId/complete { parts: [{ partNumber, etag }] } — completes the upload and returns it with its bytes.

POST /databases/:id/import { uploads: [id] } — starts an import job over up to 10000 uploads: CSV, TSV, Excel, a SQL file, a SQLite file, or a zip of any of these. Returns 202 with the job. The job’s result lists the tables created (name, rows, from) and the files skipped, each with a reason.

Export and jobs

POST /databases/:id/export { kind, table? } — kind is sql, sqlite or csv; csv requires a table, and the others take none. Returns 202 with the job; when the job is done, its result has fileName and bytes.

GET /databases/:id/jobs — the database’s jobs, newest first.

GET /jobs/:jobId — one job: kind (import, export, backup, restore), status (queued, running, done, failed), progress, result, error. Poll it about once a second.

GET /jobs/:jobId/download — the finished export’s bytes, with Content-Disposition, available for seven days.

Backups

GET /databases/:id/backups — the backups, newest first: id, label, auto, kind (point, a point in time SQLite keeps for thirty days, or file, a SQL file), status, bytes, expiresAt, createdAt.

POST /databases/:id/backups { label? } — writes a SQL file and returns 201 with { backup, job }. The file is ready when the job is done.

POST /databases/:id/backups/:backupId/restore — restores the backup after backing up the current state, and returns { before, job }: before is that new backup, and job is null when the restored backup was a point in time and the restore completed immediately.

GET /databases/:id/backups/:backupId/download — the SQL of a ready file.

DELETE /databases/:id/backups/:backupId — deletes the backup; returns 204.

Saved queries

A saved query is a SELECT saved under a name, with the way it is shown: shows is rows, bar, line or number (the first cell of the result). The web app and the phone app show a database’s saved queries when it is opened, each run again, in the order they were saved.

GET /databases/:id/saved-queries, POST /databases/:id/saved-queries { name, sql, shows? } (default rows), PATCH /databases/:id/saved-queries/:queryId { name?, sql?, shows? }, DELETE /databases/:id/saved-queries/:queryId.

The assistant

The assistant is the web app’s conversation. These routes drive it and are listed for completeness.

POST /run { instruction, photos? } — starts a run with no database attached, as the web app’s home screen does when a message is typed there, and returns 202 { runId }.

The assistant reads the account’s databases (each one’s name, table count and saved queries, never their rows) and attaches the one the message is about (open_database) or creates one named for it (create_database). From then on, the run and its thread belong to that database.
The web app joins the run through POST /run/stream with attach; an attached event names the database.
POST /run/stream, POST /run/message, POST /run/cancel, GET /thread, DELETE /runs, POST /photos and GET /photos/:photoId work like the database routes below, for the conversation that has no database yet; GET /mentions offers the databases.

For a database’s conversation:

GET /databases/:id/thread ?before= — the newest page of the thread, { runs, proposals, earlier }: the runs, the changes proposed in them, and the cursor to pass as before for the previous page, or null at the start.
DELETE /databases/:id/runs — clears the thread.
GET /databases/:id/mentions ?query=&kind=&before= — what a message can mention after @, as { kinds, items, earlier }: a few tables and saved queries whose names hold the words, each { kind, id, name }. With kind (table or query), a page of that kind alone, and earlier is the cursor to pass as before for the next page, or null after the last. A message names one as @[name](kind:id), and the assistant reads it before it answers.
POST /databases/:id/run/stream { instruction?, photos?, after?, runId?, attach? } — starts or rejoins a run as a stream of events. photos is up to 4 ids from /photos, which the assistant reads on that turn as the message’s pictures (a receipt, a list, a screen); a turn may consist of photos alone.
POST /databases/:id/run/message { instruction, photos? } — sends a message while a run is working. The message joins the run at its next step instead of waiting for the run to end, and counts as a turn. Returns { id }, or { id: null } when no run is working on the database; in that case, start a run instead.
POST /databases/:id/run/cancel — stops the run.
GET /databases/:id/watch — a WebSocket that an idle tab keeps open. It receives { type: "run", runId } when a run starts on that database, so the tab can rejoin; it never carries the run itself.
POST /databases/:id/photos — a multipart file of image/jpeg, image/png, image/webp or image/gif, up to 32505856 bytes. A photo larger than the assistant can read is resized on arrival, as the web app and the phone app resize it before sending. Returns 201 { id }, and is subject to the plan check like an import.
GET /databases/:id/photos/:photoId — the photo’s bytes, private to the account. A photo belongs to its database and is deleted with it.
POST /proposals/:proposalId/apply and POST /proposals/:proposalId/discard — the user’s decision on a proposal.

Account

GET /usage — this month’s runs, queries, rows written and tokens.
GET /plan — the trial, the subscription and how many databases it covers, how many databases the account has, and what is on offer.
GET /agents — the agents you allowed in through MCP sign-in for hosted clients: client id, name, and when.
DELETE /agents/:clientId — removes one: its tokens stop working at once, and it has to ask again.

The subscription and store routes (POST /subscription/checkout, POST /subscription/confirm, POST /subscription/cancel, POST /subscription/resume, POST /subscription/units, POST /subscription/portal, POST /purchases/apple, POST /purchases/google) are used by the web app and the phone app; an integration that uses a key does not need them.

The Help, memory and voice routes (POST /support/images, GET /support/:id/images/:imageId, POST /memories, PATCH /memories/:id, POST /speech) are used by the web app and the phone app; an integration that uses a key does not need them.

GET /databases/:id/notes, POST /databases/:id/notes, PATCH /notes/:noteId and DELETE /notes/:noteId remain for earlier phone app versions, and read and write a database’s memories as notes.