DB API

Authentication

The base URL is https://z8data.com. Use your own service URL for another deployment. Send Authorization: Bearer <db-key> on every query.

The token is case-sensitive; the Bearer scheme is not. Use one space before the token, with no extra text, whitespace, commas, or second key.

There are three shared service keys and no keys per user or database. The server reads them from .env. See key settings and rotation. Keep keys in private client configuration, not in public browser code.

Authentication runs before JSON parsing or database access. Missing or invalid keys return 401. A valid key with the wrong API role returns 403. Both DB keys pass route authentication; SQLite then checks whether each SQL statement is allowed.

Create the database file first with the Admin API. DB keys cannot create or delete database files. A query for a missing database returns 404 and creates no file. Old dataset and user keys are not accepted.

Database identity

1{"dir":"prod","filename":"app.db"}

The pair identifies one database. There is no generated ID or per-database key. Both strings must contain valid Unicode. Names are not trimmed or repaired. File name case follows the server filesystem.

Use / between folder names. Absolute paths, leading or trailing slashes, empty path parts, ., .., backslashes, colons, and NUL characters are rejected. A filename cannot contain a slash. Names ending in -wal, -shm, or -journal are reserved, with any letter case.

Paths must stay inside the database root. Symbolic links in database paths and hard links to database or sidecar files are rejected. Text such as ?mode=ro inside a filename stays filename text; it is not a SQLite connection option.

Send the identity as JSON fields in the query body. Do not URL-encode these JSON strings. See storage rules for the database root and file ownership.

Parameter and result values

Integer text uses decimal digits with an optional minus sign. Spaces, a plus sign, exponents, leading zeros, and -0 are invalid. 0 is valid. Tagged integers within the safe number range are accepted too. BLOBs must use the standard alphabet, not URL-safe base64; whitespace, missing required padding, and noncanonical encodings are rejected.

Tags have exactly the two fields shown above. Extra tag fields, malformed tags, booleans, arrays, other objects, non-finite numbers, and integers outside the allowed range are rejected. Use 0 or 1 for booleans. Parameters are validated before the batch starts.

1{"dir":"prod","filename":"app.db","statements":[{
2  "sql":"SELECT ? AS large_integer, ? AS bytes, ? AS empty_bytes, ? AS missing",
3  "params":[{"type":"integer","value":"9223372036854775807"},{"type":"blob","base64":"AQID"},{"type":"blob","base64":""},null]
4}]}

1{"results":[{
2  "columns":["large_integer","bytes","empty_bytes","missing"],
3  "rows":[[{"type":"integer","value":"9223372036854775807"},{"type":"blob","base64":"AQID"},{"type":"blob","base64":""},null]]
4}]}

Results use JSON strings, finite numbers, null, and the same tags. Safe SQLite integers become JSON numbers; larger SQLite integers use the integer tag. SQLite REAL values stay JSON numbers. BLOBs always use the BLOB tag. A value that cannot be encoded returns UNSUPPORTED_RESULT_VALUE and rolls back the batch. It is never silently replaced with null.

Results and direct write counts

A read with no matching rows still has its column names and rows: []. Reads omit changes. An INSERT, UPDATE, or DELETE without RETURNING has empty columns and rows, plus its write count. With RETURNING, it has rows and a write count.

Schema writes return empty columns and rows with changes: 0. Setting application_id or user_version also reports zero changes. EXPLAIN and EXPLAIN QUERY PLAN for permitted SQL return rows and omit changes, even when explaining a write. A statement never reports a previous statement's write count.

1{"results":[
2  {"columns":["name"],"rows":[]},
3  {"columns":["value","value"],"rows":[[1,2]]},
4  {"columns":[],"rows":[],"changes":2}
5]}

This example shows three independent result shapes. Rows are arrays so duplicate column names cannot overwrite each other. Use RETURNING when you need inserted IDs; there is no separate last-insert-ID response field.

Transactions and rollback

After authentication and input validation, the service opens the existing database. All statements run in order on one connection in one transaction. Later statements see earlier changes in that batch. Read/write-key batches use BEGIN IMMEDIATE, even for SELECT-only batches. Read-only-key batches use BEGIN. Concurrent read/write batches for the same database are serialized.

Commit happens only after every statement succeeds and the full result can be encoded as JSON. A 200 response means commit succeeded. A statement, deferred constraint, result encoding, or commit failure rolls back the full batch, including schema changes. There are no partial results. Statement errors have an index; begin and commit errors have a null index.

Caller transaction commands are denied: BEGIN, COMMIT, END, ROLLBACK, SAVEPOINT, and RELEASE. A statement that cannot run inside the service transaction fails; it is never moved outside the transaction. For example, VACUUM cannot be used as a maintenance escape.

Rollback finishes before a statement error is returned. If rollback itself fails, the connection is discarded and the response is INTERNAL_ERROR. Connections and statement caches keep a fixed DB role. Do not rely on temporary tables or connection state surviving across requests.

Do not retry write batches automatically. If a connection drops before you receive a response, the batch may already have committed. Check the data before sending the write again. A busy response or queue timeout is not a partial success response.

PRAGMAs and SQL rules

Both roles may use these read PRAGMAs, with an optional argument where SQLite supports one:

1table_info, table_xinfo, table_list,
2index_info, index_xinfo, index_list,
3foreign_key_list, foreign_key_check,
4integrity_check, quick_check

Both roles may read these settings without an assignment:

1application_id, user_version, schema_version,
2page_count, freelist_count, page_size, encoding,
3foreign_keys, synchronous, journal_mode, query_only,
4trusted_schema, busy_timeout, cache_size, auto_vacuum,
5compile_options, pragma_list, function_list, module_list

Only the read/write key may assign application_id and user_version. These changes are part of the batch transaction. All other PRAGMAs are denied, including changes to connection settings. These examples each belong in a separate statement item:

1PRAGMA table_info(users);
2PRAGMA user_version;
3PRAGMA user_version = 2; -- read/write key only

WAL, synchronous=FULL, foreign keys, defensive mode, and trusted_schema=OFF stay enabled as configured by the service. SQLite's busy timeout is 5,000 ms. Extension loading, ATTACH, DETACH, and the functions load_extension, readfile, writefile, and fts3_tokenizer are blocked.

Read-only connections open with mode=ro, readOnly: true, and query_only=ON. An engine authorizer also blocks data and schema writes, temporary writes, and unsafe PRAGMAs. Read/write connections use mode=rw so queries cannot create missing files. If a protection step fails, the connection closes and the operation fails.

Limits and waiting

There are no application body-size, SQL-length, batch-size, or result-row caps. All rows are returned in one JSON response. Use SQL LIMIT to reduce results. SQLite, available memory, and your network or proxy can still limit a request.

A single large task can exceed the byte budget and be rejected even when the queue is empty. Exceeding either admission budget returns 500 WORKER_QUEUE_FULL. Waiting too long returns 500 WORKER_QUEUE_TIMEOUT. Admin database work shares these budgets. Do not split a batch automatically: that would change its transaction boundary.

HTTP, caching, and CORS

Query success and handled errors are JSON with Cache-Control: no-store. Send a JSON body; the handler parses it as JSON without checking the Content-Type header. Invalid or empty JSON returns 400 INVALID_REQUEST after authentication.

The route accepts POST. OPTIONS is a public preflight: it returns 204 with no body, does not run SQL, and needs no key. GET, HEAD, PUT, PATCH, and DELETE return the framework's 405 response before the query handler runs. Framework errors do not use the database JSON error contract or the query handler's CORS headers.

These CORS headers are included on query responses, including handled errors, and preflight responses. Cookie credentials are not enabled. Admin routes do not enable cross-origin access. Use a private server-side client when a key must stay hidden from browser users.

Errors

1{"error":{"code":"SQLITE_CONSTRAINT_UNIQUE","message":"SQLite constraint failed","statementIndex":1}}

DB query and Admin database errors have exactly these three fields inside error. Use code to handle an error. Messages use fixed text and omit SQL, keys, parameter values, stack traces, and server file paths. There are no partial results.

statementIndex starts at zero. It identifies the failed statement, including invalid statement input. It is null for request, auth, path, queue, database open, transaction begin or commit, and Admin errors. A rollback failure also returns a null index with INTERNAL_ERROR.

* above includes the primary code and its extended codes. A known extended code is kept, such as SQLITE_CONSTRAINT_UNIQUE. An unknown extended code falls back to its primary code. Telemetry uses the separate string error format shown on the Admin API page.