All notes

· 6 min read

A ledger that cannot pay twice

Metered credits across three balance buckets, built so a duplicated payment event, a lapsed subscription or a client with bad intentions can't change a balance wrongly.

What the ledger is holding

A SaaS I run charges credits for its expensive actions, which are the ones that call a model. Credits arrive three ways and behave three ways, and that's where most of the design is.

  • Free monthly. Ten credits, refilled every thirty days, for people without a subscription. It resets, it doesn't stack.
  • Subscription monthly. An allotment that's set each billing cycle rather than added to, and that expires when the period lapses.
  • Purchased. Bought outright. It never expires and nothing clears it.

Spending drains them in that order, so free first, then subscription, then purchased. That order is the only user-facing promise in the system, and it's there to protect the bucket somebody paid cash for. Perishable credits get used while they're still worth something.

Nothing in this note went wrong. It's about the properties I wanted the money path to have before any of it was live, and where each one is enforced.

Money arithmetic with no database in it

The arithmetic is a pure module. There's no client, no await and no clock of its own, because the current time is passed in as an argument.

export function spend(s: CreditState, amount: number, nowMs: number): SpendResult {
  const state = normalize(s, nowMs);
  const total = totalBalance(state);
  if (total < amount) return { ok: false, shortfall: amount - total };

  let remaining = amount;
  const fromFree = Math.min(state.freeMonthly, remaining);
  remaining -= fromFree;
  const fromSub = Math.min(state.subMonthly, remaining);
  remaining -= fromSub;
  const fromPurchased = remaining;
  …
}

The server layer does three things around it. It loads the row, runs the function and persists the result. That split is what makes the drain order, the shortfall arithmetic and the boundary cases (spending the exact balance, spending zero, spending when free and subscription are both empty) testable at all, with no database anywhere near the suite. The tests hold a fixed epoch millisecond as NOW and read like a specification, because with time as a parameter nothing in the module is non-deterministic.

Persistence is compare-and-swap on the bucket values, with a retry, so a concurrent spend can't write a balance computed from a stale row.

One function, one transaction

Fulfilment is where money actually changes hands, and it's a single security definer Postgres function, not TypeScript, because everything it does has to happen together or not at all. The payment webhook is what calls it.

  -- Idempotency: skip if this Stripe event was already applied.
  if p_stripe_event_id is not null and exists (
    select 1 from public.credit_transactions where stripe_event_id = p_stripe_event_id
  ) then
    return;
  end if;

  -- Lock the user's credit row for the duration of the transaction.
  perform 1 from public.user_credits where user_id = p_user_id for update;

It then updates the right bucket (purchased adds to the never-expiring balance, subscription sets the monthly allotment and marks the subscription active), recomputes the total, and writes one append-only audit row holding the delta, the bucket, the resulting balance and the originating event id. So it's one call, one transaction and one row in the log.

In application code this would be a read, a write and an insert with a network boundary between each, and a crash in either gap leaves a user paid up but uncredited, or credited with no receipt. Inside the function there aren't any gaps to crash in.

Why the check isn't enough

Payment providers retry. A webhook that's already succeeded will be delivered again, and a system that grants credits twice for one payment is broken in the direction that costs money.

The early return above covers that, but on its own it wouldn't be enough. If two copies of the same webhook arrive together, both can run the exists check before either has inserted its audit row, and both will find nothing.

So I don't leave idempotency to the check. credit_transactions.stripe_event_id is declared unique, so the second transaction to insert a given id fails on the constraint and rolls back, including its bucket update, because the update and the insert are the same transaction.

So the check handles the normal case quickly, and the unique constraint is what makes it safe. I use this a lot. I think if a rule matters, the database should enforce it, and the check in the application is just an optimisation of a rule that would hold anyway.

Browsers can't change a balance

A ledger needs one more property, which is that there's no route from a browser to a balance.

The grant function is revoked from public, anon and authenticated and granted only to the service role, so it exists but can't be reached by anyone holding a user token. The balances table has a row-level security policy for reading your own row and no write policy at all, restrictive or otherwise. There's nothing for a client request to satisfy, so every write goes through the server's service-role client or doesn't happen.

It's a small thing to write down and it settles a lot of questions at once. There's no policy under which the database would accept a balance change from a client, whatever the request looks like.

Expiry and refills on read

The part I'm most pleased with is normalize. It has no side effects and runs on every read and every spend. It expires the subscription bucket if the billing period has lapsed, and refills the free allotment if thirty days have passed and the user isn't a subscriber.

if (next.subPeriodEnd !== null && nowMs > next.subPeriodEnd) {
  next.subMonthly = 0;
  next.hasActiveSub = false;
}

if (!next.hasActiveSub && nowMs - next.freePeriodStart >= FREE_REFILL_INTERVAL_MS) {
  next.freeMonthly = FREE_MONTHLY;
  next.freePeriodStart = nowMs;
}

If you read them in order, you get one thing for free. A lapsed subscription clears hasActiveSub in the first block, which makes the user eligible for the free refill in the second, in the same pass. So a subscriber who stops subscribing goes back to the free tier the next time they look at their balance.

There's no cron job, no scheduled task and no queue. A subscription expiring isn't an event the system has to be told about and could miss. It's worked out from the current time whenever anybody asks. And because the transformation only ever sets values and never accumulates them, it's idempotent, so two concurrent reads racing to persist it end up with the same answer.

A scheduled job would have needed monitoring, retries, and a plan for what happens when it doesn't run.

The bit I'm not happy with

There's one weak spot. The signup grant is a database trigger, so the free monthly allotment and the one-time bonus appear as literals in a migration as well as in constants.ts. A migration is point-in-time SQL (rewriting old ones to track a constant would be worse), so it carries a comment naming the two constants it mirrors and saying to keep them in sync.

A comment is a weak guarantee and I'd rather not need one. It's the only place in this design where correctness depends on somebody reading a note, and I think it's worth knowing exactly where that place is.

Published postgres · billing · idempotency