The Loams Data API: SQL over HTTP, designed for retries and agents

Planned The Loams Data API is a published draft specification. No Loams component serves it yet.

The Postgres and MySQL wire protocols are excellent for an application server that holds a connection pool. They are poor for three callers Loams cares about: an edge function that lives for 50 milliseconds and cannot open a TCP connection, a browser-side or mobile client, and an AI agent that should never hold a connection with write grants. Each of those wants to send SQL as one HTTP request, know exactly what to do when it fails, and be stopped before it does damage. The Data API is our answer, and we wrote it as a spec that anyone can implement rather than a Loams-only endpoint.

What the protocol isCopy Anchor Link

SQL stays the languageCopy Anchor Link

The API carries SQL in the target's own dialect: PostgreSQL for postgres: targets, TiDB's MySQL dialect for sql: targets, DataFusion for engine: targets. It adds no query language. Design 58 looked at the alternatives and did not find one worth betting on: FQL went with Fauna, which shut down its hosted service on 30 May 2025; Gel (EdgeDB) joined Vercel and shut down Gel Cloud; PRQL stayed a compiler to SQL. The improvements worth having are arriving inside SQL instead, such as pipe syntax and SQL/PGQ graph queries.

It is also deliberately not REST over tables in the PostgREST style. It is RPC-shaped, and the non-goals in the spec say so: no new query language, no PostgREST-style routes, no replacement for the wire protocols (drivers keep working beside it), no client or bidirectional streaming, no migration tooling.

Framing: Connect callsCopy Anchor Link

Every unary call is POST /loams.data.v1.DataService/<Method> with a JSON or protobuf body, so curl and fetch work with no SDK. ExecuteArrow and Subscribe are server-streaming over the Connect streaming protocol, and should also be served over gRPC and gRPC-Web (the Connect RPC post covers the three protocols). An optional WebSocket at /loams.data.v1/ws (subprotocol loams.data.v1) carries the same messages as JSON frames with a request id; it only lowers latency for chatty sessions and has the same semantics as the HTTP calls. TLS is required except on loopback.

The servicesCopy Anchor Link

DataService has Execute, ExecuteArrow, Call, Begin, Continue, Commit, Rollback, Explain and Subscribe. OperationService registers and replays named operations, and is separate so it can be authorized as administration.

The request contextCopy Anchor Link

Every request names a target: postgres:<project>/<branch>/<database>, sql:<database>/<branch>, or engine:<namespace> (which has no branches). It carries a consistency mode (STRONG by default, AT_LEAST a token, or REPLICA_OK), a budget, an optional idempotency key, and W3C traceparent. Authentication is Authorization: Bearer <credential>; how a Loams credential becomes a Postgres role is open (Q917), with PostgREST's authenticator-role pattern as the default.

Batches, operations, and batonsCopy Anchor Link

• Execute runs 1 to 256 statements, each a single SQL statement, in one transaction by default: every statement commits or none does, and an error names the failing statement's index. AUTOCOMMIT_EACH commits each statement and stops at the first failure. Transaction control statements are refused inside a batch. This is the Cloudflare D1 batch model.
• Call invokes a named operation: a SQL function in the database (Postgres CREATE FUNCTION in schema api by default), registered with its argument types, declared write set, idempotency rule and allowed callers. Operations must be deterministic: no direct now() or random(); the server passes a recorded _loams_now and _loams_seed. Every call is logged as a CloudEvent io.loams.dev.data.call.v1, so the log can be replayed on a branch. That is Convex mutations or SpacetimeDB reducers, in standard SQL.
• Interactive transactions are opt-in, using libSQL Hrana's baton model: Begin returns a baton, each Continue returns a new one, and each baton is valid for one call. Calls stay unary, so no stream is held. The transaction rolls back after idle_timeout, 10 seconds by default and at most 60.

Errors that tell you what to doCopy Anchor Link

Most SQL-over-HTTP APIs hand you an engine error code and leave the retry policy to you. The Data API puts exactly one ErrorDetail with a category on every error, and the category is authoritative:

Idempotency inside the transactionCopy Anchor Link

The interesting part is COMMIT_UNKNOWN. An idempotency ledger outside the database cannot tell whether a commit whose acknowledgement was lost actually happened. So for postgres: and sql: writes the server records the key inside the transaction it protects, as a row in _loams.requests(key, digest, result, expires_at) written just before COMMIT. A retry with the same key either finds the row and gets the stored result, or finds no row and knows the first attempt did not commit. The window is 24 hours, and reusing a key with a different request digest is refused with idempotency_key_reused. Stripe's idempotency keys are the model; recording them in the same transaction is the step further.

Dry runs and budgetsCopy Anchor Link

Explain runs nothing: it returns each statement's plan and estimates and, more usefully, the write set, each table that would be written with the kind of change and estimated rows. On Postgres it is EXPLAIN (FORMAT JSON) without ANALYZE, in a transaction that is always rolled back.

Every request runs under a budget: maximum duration, rows, bytes and cost units. Cost is checked at planning, before anything runs; time, rows and bytes are enforced while it runs, and a breach cancels and rolls back. A truncated result is never returned as success. Agent credentials default to 5 seconds, 10,000 rows and 16 MiB, and "unlimited" is not a valid default for anyone.

Results in three sizesCopy Anchor Link

• JSON rows, with a column schema (type, Postgres OID, nullability, origin table and column, key membership), up to 10,000 rows or 1 MiB by default. 64-bit integers and decimals travel as strings so nothing loses precision.
• Arrow IPC from ExecuteArrow, up to 256 MiB inline by default.
• Presigned partitions: for larger results, the server writes Parquet (or Arrow) files and returns URLs that expire after an hour, with the objects deleted within 24 hours.

Live queriesCopy Anchor Link

Subscribe takes one read-only query and returns a snapshot, then keyed added, removed and changed diffs with cursors. The server follows the change streams Loams already has (logical decoding for Postgres, CDC for Loams SQL, changelog streams for the engine) and re-runs the query, debounced by 100 ms by default. A client that reconnects with its cursor gets the diffs since then, or a fresh snapshot if the stream was trimmed. Each event maps onto a CloudEvent, so a subscription can also be consumed as CloudEvents.

The agent profileCopy Anchor Link

This is the part we care most about. In the Supabase MCP attack demonstrated in July 2025, an agent with a service-role database connection read a support ticket containing hidden instructions and wrote another table's contents back into the ticket: private data, untrusted input and a way out, all in one tool (Simon Willison's "lethal trifecta"). It was a researcher's proof of concept, and Supabase said no customer had reported a leak. The Data API's agent profile is the default for agent credentials and cannot be turned off for them:

• Read-only raw SQL, as a read-only transaction and a role without write grants.
• Writes only through named operations marked agent_callable.
• Writes only on an agent branch: the first write creates agent/<session> from the target branch, later reads in that session see it, nothing reaches a protected branch, and the branch expires 7 days after its last write. Changes reach the real branch through a human-approved merge or a replay by a non-agent credential.
• Budgets on.
• Generated MCP tools (one per operation, plus describe_schema, explain and a read-only query), with row data wrapped in an envelope marked untrusted. See the MCP post.

How Loams will implement itCopy Anchor Link

D1050 places the servers: a data module of loams-gateway for Postgres (connecting to the project's PgDog as an ordinary client over TLS and SCRAM) and Loams SQL (through loams-sqlgate), and the loams binary for engine: targets, on the same DataFusion planner as Flight SQL. It also amends the unified API design (§44 §5.1): the planned loams.sql.v1.SqlService Query and QueryArrow ship as planned, then become a shim over DataService for engine targets, served for at least one more minor release.

Loams will mirror proto/loams/data/v1/ from the spec repository unchanged; on dev there is no mirror yet and no implementation. Conformance is claimed in levels per target kind (Core, Transactions, Arrow, Operations, Live, Agent, WebSocket), plus properties such as exactly-once and branches, and the suite in conformance/ is public, so a claim can be checked by anyone.

Gotchas, as the draft standsCopy Anchor Link

• It is draft v0. Nothing is stable until data-api-1.0; draft versions carry no compatibility promise.
• Cost units do not compare across targets. They are the planner's cost on Postgres and rows times width elsewhere. The spec says so.
• Batons pin a server. Behind a load balancer, the baton must route back to the server holding the transaction.
• Replays of large results return counts only. Stored results are kept up to 1 MiB; a larger replay is flagged as truncated.
• No savepoints in v1. A failed statement inside an interactive transaction rolls the whole transaction back.
• engine: targets are read-only for agents, because they have no branches.

Read nextCopy Anchor Link

• Postgres wire protocol and MySQL wire protocol, which stay the way drivers connect.
• Connect RPC, the transport.
• Arrow Flight SQL and ADBC, the bulk path for analytics.