One HTTP API: REST, SQL and a live OpenAPI spec

Most databases expect a driver. A client library opens a connection, speaks the database's wire protocol and keeps connections in a pool. To reach the data from a browser, a serverless function or a shell script, someone writes an HTTP service in front of the database and keeps it in step with the schema.

An InventDB instance serves HTTP itself. The process that stores the records also answers REST calls, SQL over HTTP and MCP, so any language with an HTTP client and a JSON parser can use the database without a driver. This article describes those surfaces, the rules that hold across all of them, and where they stop.

The surfaces

Records live in types, and types live in namespaces: pms.properties is the properties type in the pms namespace. The first write to a namespace or type creates it, and the engine indexes every property the first time it sees one. Indexing is part of every write and cannot be switched off, so a read sent after a write has returned sees that write.

One token for every surface

A client signs in once and sends the token with every call:

curl -s -X POST https://<your-workspace>.inventdb.com/api/auth/login \
  -H 'content-type: application/json' \
  -d '{ "username": "maya", "password": "..." }'

The response carries a JWT, which goes in Authorization: Bearer <token>. Unattended jobs use a service account instead: it exchanges its own secret for a token through the OAuth client_credentials grant, and revoking it touches no person's password.

Before any handler runs, the server attaches the caller to the request: who they are, their role and the app they signed in through. The engine reads that identity wherever SQL runs and wherever records are written, so a person's grants and row rules apply in the same way to REST, SQL, bulk calls and transactions. Our article on row-level security explains how the rules are applied.

Records over REST

A record is a JSON object, and POST /db/{namespace}/{type} takes it as the whole body:

curl -s -X POST https://<your-workspace>.inventdb.com/db/pms/properties \
  -H 'Authorization: Bearer $TOKEN' -H 'content-type: application/json' \
  -d '{ "property_id": "P-5099", "owner_id": "O-1002", "city": "Charlottesville",
        "state": "VA", "beds": 2, "market_rent": 1525, "status": "Occupied" }'

# 201
{ "ok": true, "id": "d5f17a05-5806-48bf-88ab-e9a586bd06b9" }

The engine assigns an _id, a UUID unless you supply one, and stamps _createdAt, _updatedAt and _createdBy. The creator comes from the verified caller, so a client cannot record someone else as the author.

GET /db/{namespace}/{type}/{id} returns the record itself, with no envelope. A record outside the caller's row rules returns 404 rather than 403, so the API does not reveal whether an id exists. GET /db/{namespace}/{type} lists the type's ids from its id index without reading any record bodies.

PUT /db/{namespace}/{type} updates the record whose _id is in the body. The body becomes the new version of the record, so send the whole record rather than only the changed fields; the engine carries _createdAt and _createdBy over and stamps the update time and the person who made it. A body without _id is a 400 and an unknown id is a 404. DELETE writes a tombstone that removes the record from normal reads.

SQL over HTTP, for reads

POST /sql takes one SELECT statement and returns the rows as a JSON array. Joins, GROUP BY with aggregates, ORDER BY with LIMIT and MEANING() for semantic search all work. INSERT, UPDATE and DELETE are refused; writes go through the record, bulk and transaction routes, which is where referential integrity is checked.

curl -s -X POST 'https://<your-workspace>.inventdb.com/sql?metrics=1' \
  -H 'Authorization: Bearer $TOKEN' -H 'content-type: application/json' \
  -d '{ "sql": "SELECT p.city, l.tenant_name, l.contract_rent FROM pms.properties p JOIN pms.leases l ON l.property_id = p.property_id WHERE l.status = '\''Active'\'' ORDER BY l.contract_rent DESC LIMIT 3" }'

# rows, then a metrics block with elapsedMs and count
{ "rows": [
    { "p.city": "Herndon", "l.tenant_name": "Jordan Cooper", "l.contract_rent": 4625 },
    ... ], "metrics": { ... } }

Result keys follow the SELECT list. A qualified column such as p.city comes back under the key "p.city", so joined types that share a column name never collide; add an alias for a plain key. With ?metrics=1 the rows are wrapped with the server-side time and the row count.

For large results, add ?page=0&pageSize=100. The server reads any LIMIT and OFFSET the statement already has, composes the window for that page with the engine's own parser, and returns { rows, total, page, pageSize, elapsedMs }. For a SELECT over one type without aggregates, total is an exact count. For joins and aggregates it is null, and the client pages until a page comes back short.

An administrator can set a row cap for the instance; by default there is none. When a cap governs a read with no LIMIT, the server asks the engine for one row more than the cap, returns the capped rows, and sets X-InventDB-Truncated: true and X-InventDB-Row-Cap so the client knows. Aggregates, statements whose own LIMIT is within the cap, and paged reads are not affected. Each request also has a wall-clock ceiling of five minutes by default, and if the client disconnects, the server tells the engine to stop work on that query.

POST /sql/analyze parses a statement without running it and returns its sources, its projected columns and flags such as whether it joins or aggregates. Tools use it to label a result before any storage is read.

Bulk writes and transactions

POST /api/{namespace}/{type}/bulk inserts many records in one request, sent as a bare array or as { "documents": [...] }. The caller's grant and row rules are checked against the batch before anything is written, and the response returns the inserted count and the new ids. The delete routes work on sets: deleteMany by ids, delete by a filter whose terms are joined with AND, and deleteAll, which empties a type only when the body carries { "confirm": "CONFIRM" }. Dropping a whole type or namespace is reserved for administrators.

When several writes must land together, open a transaction:

POST /tx                                # 201 { "ok": true, "txId": "tx-8f3c..." }

POST /tx/tx-8f3c.../execute
{ "operation": "update", "namespace": "pms", "type": "leases",
  "id": "c129df1c-73b9-4339-8fc8-ad271fd505e8",
  "document": { ... the whole updated lease ... } }

POST /tx/tx-8f3c.../commit                # 200 { "ok": true }, or 400 on a conflict

Each execute call carries one operation: insert, update, delete or get. Running SQL inside a transaction is reserved for administrators. Concurrency control is optimistic. The transaction notes the version of every record it reads, and if another transaction changes one of those records and commits first, this commit fails and none of its writes are applied. The client can then retry from fresh reads.

Foreign keys with referential integrity

Relationships between types are foreign keys. PUT /api/relationships/{namespace} stores the namespace's graph as a list of edges, each naming a source type and property, a target type and property, a cardinality and an enforce flag:

{ "edges": [
  { "sourceType": "leases", "sourceProperty": "property_id",
    "targetType": "properties", "targetProperty": "property_id",
    "cardinality": "many-to-one", "enforce": true, "label": "belongs to" } ] }

For an enforced edge, an insert or update whose key points at a parent that does not exist is refused with a 409, and so is a delete of a parent that child records still reference. A null or missing key is accepted, so the relationship can be optional. deleteMany checks the whole batch before it removes anything. The target property can be a business key such as property_id or the parent's _id. A PUT replaces the whole graph, so read it first and send back the edited list. POST /api/relationships/{namespace}/detect suggests edges from property names and confirms them by sampling the data; nothing is saved until an administrator stores the graph.

A spec the instance serves about itself

Every instance serves its own OpenAPI 3.1 document at /api/openapi.json, the same document as YAML at /api/openapi.yaml, an interactive reference at /api/docs and Swagger UI at /api/docs/swagger. These routes need no token, so a tool can read the spec before it holds credentials; each endpoint still enforces its own authentication.

The document is assembled when the server starts. 63 operations are described by annotations on their handlers in the code, and 156 more come from a maintained description of the rest of the surface. The merge never overwrites an annotated operation, and the result describes more than 200 operations on InventDB SOAR. On an InventDB Serverless instance the merge leaves out the 20 operations that call a model and retitles the document, so each instance's spec lists what that instance serves. Request bodies carry working examples against the sample property-management data, and because the document is standard OpenAPI 3.1, a code generator can produce a typed client from it.

Limits and trade-offs

• SQL over HTTP is read-only. Every write uses the record, bulk or transaction routes.
• Values go into the SQL text. The parameters field of /sql is reserved, so your code must escape single quotes in literals.
• A PUT replaces the record. There is no partial-update route for records over REST, so read the record, change it and send all of it back.
• A bulk insert is not all or nothing. Rows that fail are reported in the response's error field alongside the ids that were written. Use a transaction when a set of writes must succeed or fail together.
• Indexing runs on every write. Large loads pay that cost in the write path, so send them through the bulk route in sensible batches.
• Paged totals are exact only for single-type SELECTs. Joins and aggregates return a null total.

Trying it

The API reference lists every endpoint with requests and responses you can paste, using the same property-management data as the examples above. On your own instance, open /api/docs to make calls against your data with your own token. InventDB Serverless and InventDB SOAR serve this API from the same engine; InventDB SOAR adds the operations that call its AI.