TLDR: If you're doing multi-table writes as sequential supabase-js calls, one mid-loop error leaves your database in a broken half-state. Push it into a Postgres function. One .rpc() call, one transaction, all-or-nothing.

The Setup

I'm building the supply-chain side of an ecommerce client's internal ops platform on Supabase.

One of the core workflows: receiving a purchase order against incoming inventory.

When a shipment arrives and a warehouse manager clicks "Receive PO," the app needs to:

  1. Lock the PO row and validate it's in shipped status
  2. Loop over each line item → insert an inventory_lots row + update purchase_order_items.received_qty
  3. Insert an inbound_shipments record
  4. Flip the PO status to received

Four kinds of writes. Multiple rows per loop iteration.

What I Was Actually Doing

I had written this as sequential supabase-js calls from a Next.js Server Action.

For webinar edits it was the same pattern — three junction tables (webinar_kols, webinar_topics, webinar_traffic_sources) synced one after another as separate PostgREST calls.

It looked fine. It worked fine in testing.

The Wall

A security + correctness audit flagged it as a correctness issue.

The problem isn't a crash you see. It's the silent one you don't.

A network hiccup, a timeout, a constraint violation on item 3 of 5 — and you end up with inventory lots that exist, items with no received quantity, a status still stuck on shipped.

The exact comment I left in the migration: "a failure mid-loop left a half-received PO (lots exist, items unmarked, status stuck)."

That's a real business problem. Someone's inventory counts are wrong and they don't know it.

The Fix

Push all of it into a Postgres function. Call it once via .rpc().

CREATE OR REPLACE FUNCTION public.receive_po(p_po_id integer, p_items jsonb)
RETURNS void LANGUAGE plpgsql SET search_path = public AS $$
BEGIN
  SELECT ... FROM purchase_orders WHERE id = p_po_id FOR UPDATE;
  -- loop → INSERT inventory_lots, UPDATE purchase_order_items
  -- INSERT inbound_shipments
  -- UPDATE purchase_orders SET status = 'received'
END;
$$;

From the action: await supabase.rpc('receive_po', { p_po_id, p_items }).

One call. One transaction. If anything inside throws, Postgres rolls back EVERYTHING. No half-received POs.

Same fix for sync_webinar_junctions — three junction-table syncs wrapped into one function, NULL array means "leave it alone," empty array means "clear it."

One detail I was glad I got right: both functions are SECURITY INVOKER (the Supabase/PostgREST default). RLS still applies to the calling user. You don't accidentally blow open row access by moving logic into a function.

Why This Matters to Me

I'd been thinking of Supabase's supabase-js as a database client. It's not — it's an HTTP client talking to PostgREST. Every call is a separate HTTP round-trip and a separate implicit transaction.

That changes everything about how you think about multi-step writes.

The rule I've internalized: if two mutations must either both happen or neither happen, they belong inside a Postgres function. The client side is for reads and single-table writes. Anything more complex belongs in the database, where transactions are a first-class primitive.

Write the function once. Call it everywhere. Sleep better.