Our affiliate payout command cannot send money, and that is the feature

CogniPrep pays a handful of people a cut when someone buys through their code. The money moves by bank transfer or PayPal, arranged directly with them. We hold no bank details at all, just an email address to confirm the transfer against.

Which means the payout code is not a payments integration. It is a ledger, and its only job is to be right about what we owe and what we have already paid. That turns out to involve more interesting decisions than an automated transfer would.

Prices and products are on cogniprep.app/pricing, and they are relevant to the last section, so open it in a tab.

The default invocation cannot change anything

pnpm affiliate:payout

That prints a report. Who is owed, how much, how many sales it covers. It mutates nothing. Moving the ledger needs a second flag and an explicit code:

pnpm affiliate:payout -- --code JANE7K2QF --mark-paid --ref "Faster Payments 12/08"

The ordering of the real-world steps is the point: you send the money first, then you record it. The function is called recordManualPayout, not pay. It describes something that already happened.

That is not just naming. A tool that both transfers and records has a window where one has happened and the other has not, and you get to find out which side of the window a crash landed on. A tool that only records has no such window: if it fails, the money has gone and the ledger says it has not, which is a state you can see in the report and fix by running it again. Compare that to the inverse, where the ledger says paid and nobody got anything, and nothing in the system will ever tell you.

Irreversible side effect first, bookkeeping second. The failure you cannot detect is always worse than the one you can.

A 30-day hold, stamped at accrual

/** How long a commission is held before it becomes eligible for payout. Long
 *  enough that card refunds and chargebacks have settled, so we do not pay out on
 *  a sale that later reverses. 30 days matches the typical chargeback window. */
export const PAYOUT_HOLD_DAYS = 30;

hold_until is a column, computed once when the commission accrues, not a predicate evaluated against created_at at report time. Both work today. Only one survives changing the hold period: with a stored column, a change applies to new commissions and leaves existing ones on the terms they accrued under. With a computed predicate, changing the constant retroactively re-dates every commission in the table, including ones you have already told someone the date for.

Store the deadline, not the policy that produced it.

The claim is one transaction and a row lock

Recording a payout does three things: find the eligible rows, insert a payout summarising them, flip them to paid. All three inside a transaction, with the select taking a lock:

const locked = await tx
  .select({ id: ..., amount: ..., currency: ... })
  .from(affiliateCommissionsTable)
  .where(and(
    eq(affiliateCommissionsTable.code_id, codeId),
    eq(affiliateCommissionsTable.status, 'accrued'),
    lt(affiliateCommissionsTable.hold_until, now)
  ))
  .for('update');

if (locked.length === 0) return null;

.for('update') is SELECT ... FOR UPDATE. Without it, two operators running the command at the same moment both read the same eligible set, both insert a payout, and the same balance is recorded as paid twice. With it, the second transaction blocks, then sees rows already marked paid, finds nothing eligible, and returns null.

Note the signature: it returns null rather than throwing. "Nothing to settle" and "a concurrent run already settled it" are the same outcome from the caller's point of view, and the CLI prints the same calm line for both.

And the index exists for exactly this query:

-- WHERE code_id = $1 AND status = 'accrued' AND hold_until < now()
index('affiliate_commissions_code_status_hold_idx')
  .on(table.code_id, table.status, table.hold_until)

Two equality columns then the range column, in that order. The ordering is not stylistic.

Every figure is snapshotted, in integer pence

The commission row stores the list price, the buyer's discount percentage, what the buyer actually paid, the commission percentage, and the commission amount. All of them, even though three are derivable from the other two.

Because the code they came from is editable. Change a code's commission percentage next year and a derived report would silently restate what you owed someone last year. The row is a record of an agreement at a moment, so it carries the whole agreement.

Everything is integer pence. No decimals, no floats, nowhere. The constraints are in the database rather than in TypeScript:

check('commission_status_valid', sql`status IN ('accrued','reversed','paid')`),
check('affiliate_commissions_pct_range',
  sql`buyer_discount_percent BETWEEN 0 AND 100 AND commission_percent BETWEEN 0 AND 100`),
check('commission_amounts_nonneg',
  sql`list_amount >= 0 AND charged_amount >= 0 AND commission_amount >= 0`),

A typecheck runs on code you wrote. A CHECK constraint runs on a migration, a psql session, and a script written at 1am. On a money table, pick the one that cannot be bypassed.

There is also a ceiling on the two percentages combined, well under 100, so a code can never be minted that pays out more than the sale brings in once our own margin and the Stripe fee we absorb are accounted for.

Accrual is idempotent via a unique index

uniqueIndex('affiliate_commissions_session_idx').on(table.stripe_checkout_session_id)

Stripe webhooks are at-least-once. checkout.session.completed can and will arrive twice. Rather than checking for an existing row and then inserting, which is a race with itself, the database refuses the second insert and the handler treats the unique violation as success. One commission per checkout session, enforced by the only component that sees all the attempts at once.

The one line I like most: a currency we refuse to copy

The prices on cogniprep.app/pricing render in your own currency. Mine shows euros. A buyer in the US sees dollars. The Stripe Checkout session is created in the buyer's currency, so session.currency on a completed session is whatever they paid in.

And it is deliberately not written to the commission row:

Every amount recorded is GBP pence, the currency of the catalogue and of the
commissions ledger. The Stripe session itself may be in the buyer's currency,
which is why session.currency is deliberately NOT copied onto the row: a EUR
label on pence figures would make the payout report add euros to pounds.

This is the sort of bug that is impossible to find afterwards. The payout report groups a code's eligible commissions and sums them. If a few rows were labelled EUR while every amount in the table is GBP pence, the sum is still a number, the report still renders, the total is just quietly wrong by however many of those rows there were. No error, no exception, no failing test unless someone thought to write one.

The catalogue is priced in GBP and the ledger is GBP. One currency in the ledger, the buyer's currency only at the till.

The commission is also reduced by the proportion of the charge that was tax. VAT collected is not revenue, so paying a percentage of it to someone would be paying commission on a government's money.

Reversals can outrun a payout, and that is allowed

The charge.refunded webhook marks every commission tied to that payment as reversed, and it will do so whether the row is accrued or already paid:

.where(and(
  eq(affiliateCommissionsTable.stripe_payment_intent_id, paymentIntentId),
  inArray(affiliateCommissionsTable.status, ['accrued', 'paid'])
))

Reversing a paid row does not claw anything back. It records that the sale went away, so the ledger stays truthful even where the cash does not. The 30-day hold exists so that this path is rare rather than impossible, which is the right target: the hold is the mitigation, the reversal is the record.

See it

Open cogniprep.app/pricing and look at which currency the figures are in. That currency is a presentation-layer decision made per visitor. Every row in the commissions ledger behind it is GBP pence, by a rule written as a comment next to the field it refuses to read.

If a geo-priced catalogue and a single-currency ledger sound like they must disagree somewhere, that is exactly the instinct the comment exists to answer. The conversion happens once, at checkout, and nothing downstream is allowed to re-derive it.

Products, all one-time payments, are listed at cogniprep.app/pricing, and the practice itself starts at cogniprep.app/games.