I put my business rules in the database instead of React, here's what it bought me

When I built CardBridge, the scary question was simple: what stops a user from skipping a step in the escrow flow?

If the rules live in React, the honest answer is "nothing, if they open the network tab." Anyone can call your API directly. Anyone can replay a request. The frontend is a suggestion, not a guard.

This post is about the decision that shaped the whole project: moving the rules into Postgres. Here's how it works, what it bought me, and what it cost.

The problem CardBridge solves

You find a great discount on something, but it only applies to a card you don't own. Someone else owns that card and would buy it for you for a small fee. The idea is easy. The hard part is trust:

• The requester needs to know the product will actually arrive.
• The cardholder needs to know they'll be paid.
• The platform needs an auditable record of every step.

That makes it a trust problem far more than a UI problem. And trust problems are exactly where "the frontend checks it" falls apart.

The decision: the database is the referee

A transaction moves through these states:

MATCHED → FUNDED → PURCHASED → SHIPPED → DELIVERED → RELEASED

plus three exits: CANCELLED, DISPUTED and REFUNDED.

Instead of letting the client update a status column, every change goes through one function, transition_transaction. A BEFORE UPDATE trigger validates the change against three things: a map of legal transitions, the caller's role, and the transaction's invariants. If anything is off, Postgres refuses.

Here's the idea in simplified form. This is a sketch, not the production schema:

-- Which (from -> to) moves are legal, and who may make them
create table legal_transitions (
  from_status text,
  to_status   text,
  allowed_role text,
  primary key (from_status, to_status, allowed_role)
);

create function guard_transition() returns trigger as $$
begin
  if old.status = new.status then
    return new;                      -- not a status change
  end if;

  if not exists (
    select 1 from legal_transitions
    where from_status = old.status
      and to_status   = new.status
      and allowed_role = current_caller_role(new)
  ) then
    raise exception 'illegal transition % -> % for this caller',
      old.status, new.status;
  end if;

  return new;
end $$ language plpgsql;

create trigger transactions_guard
before update on transactions
for each row execute function guard_transition();

An AFTER UPDATE trigger then does the bookkeeping: it writes an immutable event log row for the transition, keeps the original request's status in sync, and credits the user's tier.

The result: a buggy or malicious client simply cannot skip a step. It's not that the client is asked not to. The database says no.

Row Level Security on every table

The second half of the story is access control. Only Supabase's public anon key ships to the browser. That key is not a secret, so safety can't come from hiding it. It has to come from Row Level Security (RLS) on every table, so each user can only read and change the transactions they're part of.

A few extra guards sit on top:

• A trigger stops people from promoting their own profile privileges.
• Public-safe views expose only what the browse feed needs.
• Matching has database-level guards: you can't accept your own request, and the escrow amount is computed on insert instead of trusted from the client.

That last one matters. If the client sends the amount, the client controls the amount. If the database computes it, nobody can lie about it.

Timeouts on two clocks

Abandoned transactions shouldn't sit in limbo forever. A configurable timeout policy runs in two places: when a transaction page loads, and on a scheduled Edge Function (escrow-timeout-sweep). Disputed transactions are skipped by every automatic action, so a dispute genuinely freezes escrow.

What it bought me

• The frontend can be wrong and nothing breaks. I can ship a UI bug without shipping a money bug.
• Every state change is auditable. The event log is written by a trigger, not by code someone has to remember to call.
• I stopped worrying about "what if someone calls the API directly". They can. The database still decides.

What it cost

I want to be as honest about the costs as the benefits.

• Debugging triggers is slower than debugging a function in the same language as the rest of the app. There's no breakpoint to set; there's an error string to interpret.
• Postgres has sharp edges. You can't use a new enum value in the same transaction that adds it. So adding MATCHED and SHIPPED needed its own migration that has to commit before the state machine's migration runs.
• RLS policies can recurse. The SELECT and UPDATE policies on requests and transactions referenced each other and looped. I broke the cycle with SECURITY DEFINER helper functions. (More on RLS in a later post.)
• The whole thing ended up as fourteen ordered migrations, including the fixes. Versioned SQL is good discipline, but it's also more ceremony than editing a file.
• There is no automated test suite yet. I'll come back to this on Day 20 with a concrete plan, because it's the biggest gap in the project.

A note on what's real

To be clear about the prototype: the escrow and KYC providers are stubbed. The lifecycle, the limits and the state machine are real; the money and identity checks don't touch a live payment or identity provider yet. I'd rather say that here than let the word "escrow" imply more than it does.

My takeaway

Put rules where they can't be bypassed. Put presentation where it's cheap to change.

That sounds obvious written down, but it took building a project where money moves for me to feel it. The frontend is for the user's convenience. The database is for everybody's protection.

Try it yourself: a 20-minute version

You don't need my whole project to feel the idea. Here's a tiny, runnable version with orders instead of escrow. Paste it into any Postgres database.

create table orders (
  id     serial primary key,
  status text not null default 'PENDING'
);

-- The only legal moves
create table legal_moves (from_status text, to_status text, primary key (from_status, to_status));
insert into legal_moves values
  ('PENDING','PAID'), ('PAID','SHIPPED'), ('SHIPPED','DELIVERED');

create function guard_order() returns trigger as $
begin
  if new.status is distinct from old.status
     and not exists (select 1 from legal_moves
                     where from_status = old.status and to_status = new.status) then
    raise exception 'illegal move % -> %', old.status, new.status;
  end if;
  return new;
end $ language plpgsql;

create trigger orders_guard before update on orders
for each row execute function guard_order();

insert into orders default values;                 -- PENDING
update orders set status = 'PAID' where id = 1;    -- ok
update orders set status = 'DELIVERED' where id = 1;  -- ERROR: illegal move PAID -> DELIVERED

Run the last line and watch the database refuse. That refusal is the whole idea. Now imagine that every client, in every language, in every future version of your app, hits the same wall.

Exercises

1. Add a CANCELLED state reachable from PENDING and PAID only.
2. Add an order_events table and an AFTER UPDATE trigger that logs every change.
3. Make order_events append-only by revoking UPDATE and DELETE on it.
4. Add a role check: only an admin may move an order to CANCELLED after PAID.

When not to put logic in the database

I don't want to oversell this. It isn't always the right call.

• Rules that change weekly. Business rules that product teams tweak constantly are painful to ship as migrations.
• Heavy computation. The database is a shared, scarce resource. Don't make it do work an app server can.
• Teams without SQL skill. If nobody can read the triggers, nobody can maintain them.
• Logic that needs outside calls. Calling payment providers or email services from inside a trigger is a recipe for pain.
• Prototypes you'll throw away. The ceremony costs more than it saves.

A good compromise: put the invariants (things that must never be violated) in the database, and keep the policies (things that may change) in application code.

Questions I get asked

"Isn't that vendor lock-in?" Triggers and RLS are standard PostgreSQL, not a vendor feature. Supabase makes them convenient, but the logic would move with the database.

"How do you version it?" As ordered migrations. CardBridge's schema is fourteen of them, including the fixes, so the database can be rebuilt from scratch.

"What about the UX when Postgres rejects something?" Catch the error in the RPC layer and translate it into a friendly message. The database refuses; the frontend explains.

Have you moved logic into the database before? Did you regret it, or would you do it again? I'd especially like to hear from anyone who's tested trigger-based logic well, because that's my next problem.