A ledger you can edit from a route handler is not a ledger

Why every movement of value on the payments platform goes through a Postgres function, and what it costs to keep it that way.

3 min readGoldVault · Ledger stage

Application code with a database connection can write anything. That is the whole problem. A route handler that can insert a row into the balances table can also, on a bad day, insert the wrong one, and no amount of care in the handler changes the fact that the capability exists. A ledger that can be edited from application code is a table with a hopeful name.

The rule

On this platform the money tables carry deny-direct-write policies. Nothing running as the browser's role can insert, update or delete a movement of value. Server code holds a service role the policies do not stop, so there the fence is convention: route handlers call functions and nothing else. All of it goes through Postgres functions, one for each kind of movement, and each one checks its preconditions, writes the entries it needs to write, and does so inside one transaction. The application asks; the database decides.

There are a hundred Postgres functions in the migrations today, and roughly a dozen of them move money; the rest read analytics, fire emails and maintain timestamps. The hundred is on the front of this site with that label, because it sounds like a lot and it is meant to, and because the dozen is the number that matters. Every movement is written once, in the one place the browser cannot bypass. The fee rate is not: it is computed in the route handlers, in four places, and handed to the function as a parameter the function only sanity-checks. That is the next thing to move.

Testing what cannot be unit-tested

Functions and row-level policies live in the database, so the usual test runner does not reach them. The tests that exist run in pgTAP, inside Postgres: the deny policies on the balance table, and one ledger function from the earlier wallet model that the app no longer calls. A test impersonates a role, calls a function, and asserts on what happened, including what did not happen. It is the only way to know that the deny policy denies, and the live payment-rail functions do not have that test yet. Said plainly, because the reader sent to check will count the files.

What it costs

A migration for every change to a function. Slower to change than a line of TypeScript, and deliberately so. A ledger is the one part of a payments platform where speed of change is a cost, not a benefit. When someone asks for a small tweak to how a fee is calculated, the answer involves a migration file, a test, and a review, and that friction is the design working.

It also means the application layer is thinner than people expect. There is no service class that mutates a balance. There is fee and referral arithmetic in TypeScript, which is the previous paragraph restated. And the money-out handler does more than validate, call and report: it checks a freeze flag and a blocklist, consumes the SMS code, claims the row, resolves the processor, and only then calls it. People reading the codebase for the first time go looking for the business logic and find it in SQL. That is the right place for it to be, and it takes a paragraph like this one to explain why.

The pattern travels

The same shape is on the other platforms on this page. The live-operations tool enforces tenant isolation in the database rather than in the app, for the same reason: the app is where mistakes are made, the database is where they are caught. It is not a payments idea. It is an idea about where to put the thing you cannot afford to get wrong.

Written by Dylan Pulver, who built the platform this decision lives in. Software engineer and entrepreneur, Toronto.

More notes
  • The webhook that credits twiceA payment processor delivers every result at least once. The interesting engineering is in what happens when your side fails halfway through.
  • Four rows that were not theirsHardening multi-tenant row-level security in four reversible phases, verified by impersonating a member and counting what they could see.
  • The bug inside the fixA payout race, the fix for it, the bug inside that fix, and why it is the best argument I have for one person owning the whole path.
  • The sale that arrives with no referrerA creator shares a link on Instagram, the buyer taps it, installs the app and purchases. Nothing in that chain carries the creator's name across. Here is what does.
  • The minimum that belongs to someone elseHeld commissions are released to creators once a brand's payout minimum is met. Group the money by creator, the obvious way, and one brand's minimum ends up holding another brand's money.
  • The commission that must not mint twiceA buyer pays and the platform owes a creator a commission. Between those two facts sit a colluding pair, a call that arrives twice, and a cart with three items on one payment.
  • The rule that has to be written twiceFirestore security rules do not cascade to subcollections. Forget that in one place and a single query returns every private message on the platform.
  • Thirty days in the ledgerSplitting a payment at charge time is simpler and wrong: a refund after the creator is paid is a clawback nobody enjoys. Holding the money creates a different set of problems, and each piece of machinery around the hold answers one of them.
  • Arbitrary but consistentA gym's assessment answers become rules that swap an exercise for a member before a session. Two rules can disagree about the same movement. The code says who wins, and the comment admits how.