OrderBind Multi-Tenant ERP SaaS

Context

Orders in one tool, customers in another, procurement in a third. Enterprise ERP pricing doesn't work at that size, and three tools that don't share a customer record don't either.

Challenge

One database holds every tenant's orders. A tenant who goes looking for someone else's should find nothing, and I wasn't going to run a deployment per customer to get that.

Non-negotiables

• Tenant data must never leak, even on application bugs
• Credit metering accurate to the action
• Audit logs that survive admin actions

Calls I made

1. 01

   RLS, not application checks

   Every table carries a row-level security policy keyed on tenant id. Application code that forgets the filter still cannot leak data.

2. 02

   Credits as RPCs

   Metered actions go through Supabase RPCs that check and decrement credits in the same transaction. No race window.

The decision, in code

Metering that cannot be raced or bypassed

-- Isolation lives in the database, not in the handler. A route that forgets
-- .eq('tenant_id', ...) returns zero rows instead of another tenant's orders.
create policy tenant_isolation on orders
  for all
  using      (tenant_id = auth.jwt() ->> 'tenant_id')
  with check (tenant_id = auth.jwt() ->> 'tenant_id');

-- Check-then-decrement across two round trips lets a tenant spend the same
-- credit twice under concurrency. One statement, one row lock, no window.
create function spend_credits(p_tenant uuid, p_amount int, p_reason text)
returns int
language plpgsql
security definer
as $$
declare
  remaining int;
begin
  update tenant_credits
     set balance = balance - p_amount
   where tenant_id = p_tenant
     and balance  >= p_amount     -- the guard IS the update predicate
  returning balance into remaining;

  if not found then
    raise exception 'insufficient_credits' using errcode = 'P0001';
  end if;

  insert into credit_ledger (tenant_id, delta, reason)
  values (p_tenant, -p_amount, p_reason);

  return remaining;
end;
$$;

Folding the balance check into the UPDATE predicate is what removes the race. Postgres takes a row lock for the write, and that lock is already guarding the read — two concurrent calls serialise, and the second gets `not found` instead of a negative balance. The ledger insert rides the transaction, which is why billing and balance can't disagree.

Trade-offs accepted

• The migration history is forward-only: 50+ files, none of them rebased. Every production schema state replays from it, and working out how one table got its shape means reading the history in order. Whoever joins next pays for that.
• Tenants share one hostname and carry their tenant id in the path. I deferred per-tenant subdomains to ship v1; the cost is that a tenant cannot be given a URL of their own.