Postgres Transactions (Multi-Statement Mode)

Run multiple SQL statements as a single atomic transaction inside a Postgres Action. All steps commit together or none at all, results from earlier steps flow into later steps via template references, and per-step rollback rules let you fail the whole thing on a row-count miss.

When to use it

• E-commerce checkout — create order, decrement stock, charge balance. Either all three succeed or nothing changes; no orphan orders if the balance check fails.
• Money transfer — debit account A, credit account B. The two updates must commit together or accounts go inconsistent.
• Idempotent ingest — INSERT only if a row doesn't exist; UPDATE if it does. Wrap both in a transaction so concurrent retries don't double-write.
• Multi-table writes with foreign keys — parent INSERT must commit before the child INSERT references its id. Same transaction means same snapshot.

How it works

Statements run in array order on a SINGLE pooled connection wrapped in BEGIN / COMMIT:

BEGIN;
  -- statement 1 (id="create_order")
  INSERT INTO orders ... RETURNING id;
  -- statement 2 (id="reserve_stock")
  UPDATE inventory SET stock = stock - 1 WHERE ...;
  -- statement 3 (id="link_items")
  INSERT INTO order_items (order_id, ...) VALUES ({{create_order.rows[0].id}}, ...);
COMMIT;

Any thrown driver error or rollback-rule miss issues a ROLLBACK and the action throws — letting your flow's error path handle it.

Setup walkthrough

1. Switch to transaction mode

Open the Postgres Action's detail page. Below Connection, hit the Transaction (multi-step) mode button. The single-mode SQL editor is replaced by a Statements panel with a violet header explaining the semantics.

2. Add statements

Click + Add statement. Each card has:

• id — a stable identifier (a-z, 0-9, _) used to reference this step's result from later steps. Pick something descriptive: create_order, charge_balance.
• SQL — the statement itself. Same {{payload.x}} auto-bind path as single-mode (placeholders become $N prepared-statement binds with type preservation).
• Manual parameters (collapsible) — for the rare case you want to write $1, $2 by hand and supply the JSON-array template. Skip unless you have a specific reason.
• Rollback rules (collapsible) — see below.

3. Pick the primary statement

Click Set primary on whichever step's result should be the action's primary output (the one downstream nodes read as payload.rows directly). Defaults to the LAST statement at runtime if you don't set it explicitly.

4. Reorder if needed

The ▲ / ▼ arrows on each card move it up or down. Order matters — earlier steps' results are visible to later steps via template references; reordering rebreaks references if a referenced id is now below.

Variable passing between steps

After each step runs, its result joins the template context under its id as { rows, rowCount }. Subsequent steps can read it the same way they read payload or any cross-node $() reference.

INSERT INTO orders (user_id, total)
VALUES ({{payload.userId}}, {{payload.total}})
RETURNING id, created_at

UPDATE inventory
SET stock = stock - {{payload.quantity}}
WHERE product_id = {{payload.productId}}
  AND stock >= {{payload.quantity}}

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (
  {{create_order.rows[0].id}},
  {{payload.productId}},
  {{payload.quantity}}
)

Rollback rules

Each statement gets an optional Rollback rules block (collapsed by default):

• Min rows affected — rolls back the whole transaction if the step's rowCount is < this. Common: an UPDATE that MUST find a matching row, set min=1.
• Max rows affected — rolls back if rowCount is > this. Common: an UPDATE WHERE id=$1 should only ever touch 1 row, set max=1 as a safety net.

Either guard miss throws an error inside the transaction executor, which triggers ROLLBACK and the action throws to the flow.

Step 2: reserve_stock
  SQL:    UPDATE inventory SET stock = stock - 1
          WHERE product_id = {{payload.productId}} AND stock > 0
  Rules:  Min rows affected = 1

→ If product is out of stock, the UPDATE matches 0 rows.
→ The min-rows check fails, the executor rolls back step 1 (create_order)
→ Action throws "Statement 'reserve_stock' failed expect: rowCount 0 < expected min 1"
→ The order never gets created. Stock count untouched.

Output shape

On success the action emits:

{
  "mode": "transaction",
  "committed": true,
  "primaryStatementId": "create_order",
  "primary": { "rowCount": 1, "rows": [{ "id": 42 }] },

  // shortcuts so existing downstream templates that read
  // payload.rows / payload.rowCount keep working without rewrites
  "rows": [{ "id": 42 }],
  "rowCount": 1,

  // every step's result, keyed by id
  "steps": {
    "create_order":  { "rowCount": 1, "rows": [{ "id": 42 }] },
    "reserve_stock": { "rowCount": 1, "rows": [] },
    "link_items":    { "rowCount": 1, "rows": [{ "id": 99 }] }
  }
}

Downstream nodes can read either the shortcut or the per-step result:

{{$('Postgres action').payload.rows[0].id}}                    // shortcut → primary
{{$('Postgres action').payload.steps.create_order.rows[0].id}} // explicit → step 1
{{$('Postgres action').payload.steps.link_items.rowCount}}     // step 3 row count

Error shape

On rollback the action throws. The error carries enough metadata to debug from the flow's error-handling path:

{
  "message": "Postgres transaction error: Statement 'reserve_stock' failed expect: rowCount 0 < expected min 1",
  "rolledBackAt":   "reserve_stock",
  "rollbackReason": "rowCount 0 < expected min 1",
  "steps": {
    // results of steps that ran BEFORE the rollback fired
    "create_order": { "rowCount": 1, "rows": [{ "id": 42 }] }
  }
}

Even though step 1 (create_order) appears in steps, it was rolled back — the row id shown there is what the database TEMPORARILY assigned during the in-flight transaction. After ROLLBACK that id never existed.

Full example: order checkout

Three-step e-commerce checkout — atomic order creation, stock reservation, and balance debit. Any failure rolls all three back.

Statements

Step 1 — id="create_order"
  SQL:    INSERT INTO orders (user_id, total, status)
          VALUES ({{payload.userId}}, {{payload.total}}, 'pending')
          RETURNING id, created_at
  Rules:  Min=1 (sanity check — INSERT should always create a row)

Step 2 — id="reserve_stock"
  SQL:    UPDATE inventory SET stock = stock - {{payload.quantity}}
          WHERE product_id = {{payload.productId}}
            AND stock >= {{payload.quantity}}
  Rules:  Min=1 (rolls back if product is out of stock)

Step 3 — id="charge_balance"
  SQL:    UPDATE accounts SET balance = balance - {{payload.total}}
          WHERE user_id = {{payload.userId}}
            AND balance >= {{payload.total}}
  Rules:  Min=1 (rolls back if user has insufficient balance)

Primary statement: create_order

Happy path

All three guards pass → COMMIT. Downstream nodes see the new order id at payload.rows[0].id.

Out-of-stock path

Step 2's UPDATE matches 0 rows (stock too low). Min=1 guard fails → ROLLBACK. The order is undone (despite step 1 having "succeeded"), the inventory row stays at its pre-transaction value, and the action throws — the flow's error path can return a "Sorry, out of stock" response to the user.

Insufficient balance path

Steps 1 + 2 succeed; step 3 matches 0 rows because balance < total. ROLLBACK undoes both the order AND the stock decrement. The user's account balance is unchanged.

Limits & gotchas

• Statement timeout is total — the statementTimeoutMs field on the action applies as SET LOCAL statement_timeout for the whole transaction. If you set 10s and you have 5 steps, all 5 share that 10s budget.
• Pool starvation risk — a long-running transaction holds its connection until COMMIT/ROLLBACK. Keep transactions snappy; offload heavy work outside the BEGIN block.
• SAVEPOINT not exposed — v1 only supports all-or-nothing rollback. Partial rollback to a checkpoint is on the roadmap if real workflows need it.
• Duplicate ids rejected — every statement's id must be unique within a transaction. The dashboard flags duplicates inline; the backend rejects the payload.
• DDL inside transactions — Postgres transactional DDL works (unlike MySQL). You CAN CREATE TABLE inside the BEGIN if you really need to.

Testing

The Run Test panel works the same way it does in single-mode. Provide a payload, click Run, and the result viewer shows the full output shape (mode, committed, steps, primary). Errors propagate the rollback metadata so you can see which step triggered it without running the SQL by hand.