Data API

Writing

Create, update and delete rows - one, many, or by filter - with upserts, optimistic concurrency and idempotency keys.

Writes go through the same pipeline wherever they come from (this API, a DataView form, a workflow): permissions, PostgreSQL's constraints, the audit log and an event, in one transaction.

Create

POST /api/public/table/work_orders
{ "data": { "type": "REPAIR", "priority": "HIGH", "site": "3c9e…", "branch": "0b6f…",
            "description": "Compressor trips after 10 minutes." } }

201, with the row as PostgreSQL stored it: defaults, values set by triggers (number, due_at) and generated columns included.

{ "data": { "id": "8f1c…", "number": "WO-000413", "status": "NEW", "due_at": "2026-10-15T12:00:00Z", … } }

{ "data": {} } creates a row from the table's defaults alone.

Update

By primary key:

PATCH /api/public/table/work_orders/8f1c…
{ "data": { "status": "ASSIGNED", "technician": "e21a…" } }

Or every row matching a filter:

PATCH /api/public/table/work_orders?where[status][_eq]=NEW&where[site][_eq]=3c9e…
{ "data": { "priority": "URGENT" } }

By key, the answer is the row, or 404 ROW_NOT_FOUND if it doesn't exist or you can't see it. By filter, it's the list of updated rows, possibly empty. An update without where is refused while api.require_where_for_update is on (the default), so a missing filter never updates a whole table.

Your permissions check each row both before and after the change. See Permissions.

Delete

DELETE /api/public/table/visits/77ab…
DELETE /api/public/table/visits?where[scheduled_start][_lt]=$INTERVAL.1y

The answer is 200 with what was removed. A delete without where is refused while api.require_where_for_delete is on. Related rows follow your foreign keys: ON DELETE CASCADE deletes them, and a restricting key makes the delete fail with 409 FOREIGN_KEY_VIOLATION. TotalBase never deletes children itself.

What a write returns

A write returns what you could read: the fields of your READ grant. With CREATE but no READ, you get only the primary key. Password columns are never returned.

Several rows at once

bulk=true makes data a list, up to 500 items (api.bulk_max_items):

POST /api/public/table/time_entries?bulk=true
{ "data": [
  { "work_order": "8f1c…", "technician": "e21a…", "started_at": "2026-10-14T09:00:00Z", "ended_at": "2026-10-14T11:30:00Z" },
  { "work_order": "8f1c…", "technician": "e21a…", "started_at": "2026-10-14T12:15:00Z", "ended_at": "2026-10-14T13:00:00Z" }
] }

Request	Each item
`POST ?bulk=true`	a row to create
`PATCH ?bulk=true`	the primary key plus the fields to change
`DELETE ?bulk=true`	an object with the primary key

All or nothing: the batch runs in one transaction, and one failing item rolls back every item.

Upserts: on_conflict

To insert a row or, if it already exists, update it, add on_conflict beside data:

POST /api/public/table/parts?bulk=true
{
  "data": [
    { "sku": "HV-CAP-35", "name": "Run capacitor 35 µF", "unit_cost": 4200, "unit_price": 7800 },
    { "sku": "SO-MC4-PR", "name": "MC4 connector pair", "unit_cost": 1900, "unit_price": 3500 }
  ],
  "on_conflict": {
    "target": ["sku"],
    "action": "UPDATE",
    "data": { "unit_cost": "$INSERT.unit_cost", "unit_price": "$INSERT.unit_price" }
  }
}

Acme loads its supplier's price list this way: new parts are created, and known ones get the new prices.

target must be exactly the columns of the primary key or of a UNIQUE constraint.
action: "UPDATE" updates the existing row with on_conflict.data. $INSERT.<column> is the value you tried to insert (PostgreSQL's EXCLUDED). It needs both CREATE and UPDATE permission, and a row it updates is checked and audited as an update.
action: "NOTHING" leaves the existing row alone. The answer is 201 with "data": null for that row.

PostgreSQL resolves the conflict atomically (INSERT … ON CONFLICT). There's no read-then-write race. Upserts work for single and bulk creates.

Optimistic writes

To make sure you don't overwrite someone else's change, send the version you read:

GET /api/public/table/work_orders/8f1c…          → meta.version "48213", ETag "48213"

PATCH /api/public/table/work_orders/8f1c…
If-Match: "48213"
{ "data": { "resolution": "Replaced the capacitor." } }

If the row changed since you read it, the answer is 409 ROW_VERSION_CONFLICT and nothing is written. The version is PostgreSQL's own row version, so there's no column to add. The App's forms do this: saving over someone else's newer change fails instead of overwriting it silently. If-Match works on PATCH and DELETE by key.

Retrying safely: idempotency keys

A client that times out doesn't know whether its create went through. Send an Idempotency-Key and retry with the same key:

POST /api/public/table/invoices
Idempotency-Key: wo-8f1c-invoice
{ "data": { … } }

The first request runs. A repeat with the same key and the same body returns the recorded answer without running again. The same key with a different body is 409 IDEMPOTENCY_KEY_REUSED, and a repeat while the first is still running is 409 IDEMPOTENCY_REQUEST_IN_PROGRESS. A failed attempt frees the key. Keys last 24 hours (idempotency.ttl_hours) and are scoped to your credential and the operation.

Keys work on POST to tables, on function calls and on transactions. For a table or function that must never be written twice, turn on Require an idempotency key under its Endpoints. A request without one then gets 400 IDEMPOTENCY_KEY_REQUIRED.

Errors you'll meet

Error	Status	Means
`ACTION_DENIED`	403	no permission for the action, or the row before or after the change is outside your row filter
`FIELD_PERMISSION_DENIED`	403	a field you may not write. Fields are never silently dropped.
`PAYLOAD_CHECK_FAILED`	422	a payload check refused the values
`NOT_NULL_VIOLATION`, `CHECK_VIOLATION`	422	PostgreSQL refused the values
`UNIQUE_VIOLATION`, `FOREIGN_KEY_VIOLATION`	409	PostgreSQL refused because of other rows
`RAISE_EXCEPTION`	422	a trigger or function refused, with its own message
`GENERATED_ALWAYS`	400	you wrote a column PostgreSQL computes

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. Here, a dispatcher assigns an order by its key; their role's row filter is their branch:

BEGIN;
SELECT set_config('statement_timeout', '30000', true),
       set_config('totalbase.actor_user_id', 'a26c…', true);
WITH old_rows AS (
  SELECT t0."id" AS __old_key_0, t0."status" AS __old_4, …    -- the row before, for the audit's changes
  FROM "public"."work_orders" AS t0
  WHERE t0."id" = $3 AND t0."branch" = $4                     -- the old row must pass the filter
  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 so must the new one
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;

A row outside the filter is never selected, so it isn't touched: 404 ROW_NOT_FOUND. A new row that leaves the filter, such as moving the order to another branch, comes back with __authorized false and the transaction is rolled back: 403 ACTION_DENIED. xmin is the row's version, the ETag of optimistic writes.

Next steps

Nested writes: a row with its children.
Transactions: several writes on different tables.

Aggregating

Totals, counts, averages and groups: _sum, _count, _avg, _min, _max, group_by and having.

Nested Writes

Create a row together with its children - a quote with its items - in one request and one transaction.