Nested Writes

A nested write creates a parent row and its children in one POST, such as a quote with its items or a purchase order with its lines. Everything runs in one transaction: either the whole tree is created or none of it.

Syntax

A property named $ followed by a child table's name holds a list of child rows:

POST /api/public/table/quotes
{
  "data": {
    "customer": "6a0f…",
    "site": "3c9e…",
    "valid_until": "2026-11-15",
    "$quote_items": [
      { "description": "Split unit 3000 frigorías, installed", "quantity": 2, "unit_price": 980000 },
      { "description": "Copper line, per meter", "part": "a1…", "quantity": 12, "unit_price": 14500, "discount_pct": 10 }
    ]
  }
}

TotalBase inserts the quote, then each item with quote_items.quote set to the new quote's id. You don't send the foreign key; sending it is 400 NESTED_FOREIGN_KEY_NOT_ALLOWED.

Children can have children of their own, and a parent can have several child lists side by side:

{ "data": {
    "supplier": "s7…", "warehouse": "w1…",
    "$purchase_order_items": [ { "part": "a1…", "quantity": 50, "unit_cost": 4200 } ]
} }

The $ prefix keeps this separate from your own columns: a jsonb column named quote_items and the nested list $quote_items never collide.

The response

201 with the parent as stored, and each child list under its $ key:

{ "data": {
    "id": "q9…", "customer": "6a0f…", "status": "DRAFT", "valid_until": "2026-11-15",
    "$quote_items": [ { "id": "…", "quantity": 2, … }, { "id": "…", "quantity": 12, … } ]
} }

The parent is read again after its children are in, so a column that a trigger updates from the children, such as a total summed from the items, is already correct in the answer.

How children are matched

For $child, TotalBase needs:

• a table named child in the parent's schema, exposed with POST;
• exactly one foreign key from it to the parent. With none, the answer is 400 INVALID_NESTED_RELATION. With two (a billing_customer and a shipping_customer), it's 400 AMBIGUOUS_NESTED_RELATION. TotalBase never guesses.

Permissions and rules apply to every row

Each row of the tree is a full create of its own table: its own CREATE permission and fields, row filter, payload checks, audit entry and event. Permission to create quotes doesn't imply permission to create quote items.

Acme's discount cap shows this. A salesperson's quote with a 25% line fails on that item, with 422 PAYLOAD_CHECK_FAILED, and the quote isn't created either.

Any failure anywhere rolls back the whole tree: a permission, a constraint, a trigger, a RAISE EXCEPTION.

Limits

The whole payload is checked against the limits and relations before anything is written.

What nested writes don't do

• Only on create. A PATCH with a $ property is refused (400 UNSUPPORTED_NESTED_OPERATION). Change children through their own table, or several tables in one transaction.
• No synchronization. Children you don't send are never deleted or unlinked.
• Only child tables. A nested list follows a foreign key from the child to the parent. You can't create the parent's referenced rows (its customer) this way.
• Not with bulk=true or on_conflict. A nested create inside a transaction works.

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.

BEGIN;
SELECT set_config('statement_timeout', '30000', true),
       set_config('totalbase.actor_user_id', 'd039…', true);  -- who writes, for triggers and the audit

-- The quote. `__authorized` is the caller's row filter checked on the new row: `true` when they have none.
WITH ins AS (INSERT INTO "public"."quotes" ("customer", "site", "valid_until") VALUES ($1, $2, $3) RETURNING *)
SELECT *, true AS __authorized FROM ins;                      -- $1 = '6a0f…', $2 = '3c9e…', $3 = '2026-11-15'
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

-- Each item, its foreign key set to the new quote's id.
WITH ins AS (INSERT INTO "public"."quote_items" ("description", "quantity", "unit_price", "quote") VALUES ($1, $2, $3, $4) RETURNING *)
SELECT *, true AS __authorized FROM ins;                      -- $4 = '7de0…', the quote's id
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
WITH ins AS (INSERT INTO "public"."quote_items" ("description", "part", "quantity", "unit_price", "discount_pct", "quote") VALUES ($1, $2, $3, $4, $5, $6) RETURNING *)
SELECT *, true AS __authorized FROM ins;
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

-- The quote read again: its total, summed by a trigger from the items, is already right.
SELECT * FROM "public"."quotes" WHERE "id" = $1;
COMMIT;

If any statement fails (a CHECK on an item, a payload check, a refused row filter), PostgreSQL rolls the whole transaction back: no quote, no items, no audit entry.

Next steps

• Transactions.
• Master-detail: a master-detail form saves this way.