Transactions
POST /api/transactions runs several writes, on any tables, in one PostgreSQL transaction, in order. Either all of them commit or none do.
Use it when one action touches unrelated tables. When a technician closes an order: update the order, add the time entry, record the parts used, and free the van.
POST /api/transactions
{
"operations": [
{ "key": "order", "operation": "update", "schema": "public", "table": "work_orders",
"id": "8f1c…", "data": { "status": "COMPLETED", "resolution": "Replaced the run capacitor.",
"customer_signature": "f5d2…" } },
{ "key": "time", "operation": "insert", "schema": "public", "table": "time_entries",
"data": { "work_order": "$order.id", "technician": "e21a…",
"started_at": "2026-10-14T09:00:00Z", "ended_at": "2026-10-14T11:30:00Z" } },
{ "key": "part", "operation": "insert", "schema": "public", "table": "work_order_parts",
"data": { "work_order": "$order.id", "part": "a1…", "warehouse": "v3…", "quantity": 1 } },
{ "key": "van", "operation": "update", "schema": "public", "table": "vehicles",
"id": "5b2e…", "data": { "status": "AVAILABLE" } }
]
}
{ "data": {
"order": { "id": "8f1c…", "status": "COMPLETED", … },
"time": { "id": "…", "minutes": 150, … },
"part": { "id": "…", … },
"van": { "id": "5b2e…", "status": "AVAILABLE", … }
} }
If the van has no stock of that part, the stock trigger raises an error and the order isn't completed either.
Operations
operation | Needs | Does |
|---|---|---|
insert | data | creates a row. data may carry $child lists, a nested write. |
update | id, data | updates the row with that primary key |
delete | id | deletes the row with that primary key |
Each operation has a key, unique in the request, and names its schema and table. update and delete need a single-column primary key.
References between operations
A string "$<key>.<field>" in a later operation's data or id is replaced by that field of an earlier operation's result: "$order.id" above. References only point backwards, to operations already run. An unknown key or field is 400 TRANSACTION_REFERENCE_NOT_FOUND.
References read the result as the caller sees it. A field you can't read can't be copied this way.
Permissions
- The caller's role needs the Can run transactions capability (in the role's General section). Otherwise the answer is
403 TRANSACTION_EXECUTION_DENIED. - Then each operation is checked exactly as its own route would be: the method must be exposed, and the permission, fields, row filter, payload checks and guardrails all apply. A transaction never grants more than the separate requests would.
- One refused operation fails the whole transaction.
Errors
An error says which operation failed, with its key in operation:
{ "errors": [ { "source": "POSTGRESQL", "code": "CHECK_VIOLATION", "operation": "part",
"message": "Not enough stock in this warehouse.", "field": "quantity" } ] }
A form can use it to mark the line that failed.
Limits and settings
| Setting | Default |
|---|---|
transactions.enabled | on (off: 403 TRANSACTIONS_DISABLED) |
transactions.max_operations | 20 (400 TRANSACTION_LIMIT_EXCEEDED) |
transactions.timeout_ms | each statement's timeout; 0 uses api.request_timeout_ms |
There is no partial commit, no continue-on-error and no savepoints. A transaction accepts an Idempotency-Key, so a retried request runs once. With Prefer: respond-async it's queued and runs in the background. See Calling functions.
Transactions or a function?
When the same multi-step write always happens together and its logic belongs to the data, write a PostgreSQL function and call it. Every client then gets the same behavior, and the rules live in the database. Use transactions when the client decides which writes go together.
The SQL it runs
This is the SQL TotalBase sent to PostgreSQL for the example above, as PostgreSQL's own log recorded it - ids shortened, long column lists cut with …. Every value travels as a parameter ($1), never inside the SQL text. This one is a dispatcher's transaction, two operations: assigning an order, then a quote for its site, with "site": "$order.site".
BEGIN;
SELECT set_config('statement_timeout', '30000', true),
set_config('totalbase.actor_user_id', 'a26c…', true);
-- order: an update. The old row is locked and checked against the role's row filter (their branch)…
WITH old_rows AS (
SELECT t0."id" AS __old_key_0, t0."status" AS __old_4, …
FROM "public"."work_orders" AS t0
WHERE t0."id" = $3 AND t0."branch" = $4 -- $3 = the order, $4 = the user's branch
FOR UPDATE OF t0
), upd AS (
UPDATE "public"."work_orders" AS t0 SET "status" = $1, "technician" = $2
FROM old_rows WHERE t0."id" = old_rows.__old_key_0
RETURNING t0.*, t0.xmin::text AS __version, old_rows.*
)
SELECT *, (upd."branch" = $5) AS __authorized FROM upd; -- …and the new row too
INSERT INTO totalbase.logs (…) SELECT … FROM jsonb_array_elements($1::jsonb) …; -- its audit entry
INSERT INTO totalbase.outbox (…) SELECT … FROM jsonb_array_elements($1::jsonb) …; -- its event, for webhooks and workflows
-- quote: an insert. "$order.site" was replaced by the site the first operation returned.
WITH ins AS (INSERT INTO "public"."quotes" ("customer", "site", "valid_until") VALUES ($1, $2, $3) RETURNING *)
SELECT *, true AS __authorized FROM ins; -- $2 = 'e712…', the order's site
INSERT INTO totalbase.logs (…) SELECT … FROM jsonb_array_elements($1::jsonb) …; -- its audit entry
INSERT INTO totalbase.outbox (…) SELECT … FROM jsonb_array_elements($1::jsonb) …; -- its event, for webhooks and workflows
COMMIT;
Each operation compiles exactly as its own route would, on the same connection, inside one BEGIN … COMMIT. A false __authorized, a constraint or a trigger's error stops it there and PostgreSQL rolls back everything before it.
Next steps
- Calling functions.
- Writing: idempotency keys.