The refund arrived after the payout, so the fix was two nullable columns and no code

CogniPrep pays creators a commission when someone buys through their code. The policy is published in our terms, and you can read it on cogniprep.app/terms, section 7.2:

Commission accrues only when a payment completes, and is reversed if that payment is later refunded or charged back

Payouts are made manually, in batches, to the payout email you gave us

Those two lines are comfortable next to each other right up to the moment the order changes. Refund before the payout and the reversal is a status change on a row nobody has been paid for. Refund after the payout and the money has already left the account. The policy sentence is the same; the thing the database has to represent is not.

Why not a negative row

The obvious move is a correcting ledger entry: insert a commission of minus the amount and let the sum come out right. Our schema says no, and it says it with an index:

// Idempotent accrual: at most one commission per checkout session.
uniqueIndex('affiliate_commissions_session_idx').on(table.stripe_checkout_session_id),

Every commission row belongs to exactly one Stripe checkout session, and that uniqueness is what makes accrual idempotent when a webhook is delivered twice. A correcting row has no session of its own. It would either duplicate the original session id, which the index refuses, or carry nothing there, which quietly turns the strongest invariant in the table into a suggestion.

So the overpayment is not a new row. It is a state the existing row can be in.

The two columns

// For a row refunded AFTER it was paid out: the later payout that deducted
// the overpayment. A reversed row with a payout_id and no clawback_payout_id
// is money the creator still owes back, and the next payout nets it off.
// Undoing that later payout deletes it, which clears this and leaves the
// deduction pending again.
clawback_payout_id: text().references(() => affiliatePayoutsTable.id, {
  onDelete: 'set null',
}),

Read as a state machine, a commission row now says four different things with two foreign keys and a status:

• accrued, no payout: owed to the creator, waiting out its hold.
• paid, payout set: settled.
• reversed, no payout: refunded in time, nothing to recover, nothing to do.
• reversed, payout set, clawback null: we paid for a sale that was refunded. This is the one that matters, and the next payout batch nets it off.

The ON DELETE set null is the part I like. Payout batches can be undone, because they are recorded by hand and a human can record the wrong one. Deleting the batch that performed a clawback clears clawback_payout_id and drops the row straight back into "still owed back", rather than leaving a settled-looking row whose settlement no longer exists. The reversal of a correction has to put you back where you were, and the cheapest way to get that is to make the correction a reference rather than a copied number.

It gets an index, because the question the payout screen asks is "what did this batch deduct", which is a lookup by exactly this column:

CREATE INDEX "affiliate_commissions_clawback_payout_idx"
  ON "affiliate_commissions" USING btree ("clawback_payout_id");

Deleting a code is two operations wearing one button

The same migration adds one column to the codes table:

// Set when an admin deletes a code that has sales or payouts. Its commissions
// and payouts are financial records, so the row is kept (inactive, and hidden
// from the admin list) rather than cascading them away. A code with no
// history is deleted outright instead.
archived_at: timestamp(),

An admin pressing Delete on a code that nobody has ever used should get a delete: the row is a typo, and a tombstone for a typo is clutter. An admin pressing the same button on a code with three sales and a payout behind it must not get a delete, because the rows downstream of it are an account of money that moved. Same button, same intent, two different operations, and which one you get is decided by whether the code has history.

That also means "active" stops being a single flag. The table already has is_active, with a partial index on the true half, plus a valid_from and valid_until window. Archival is a third condition on top of both, and the intent recorded in the comment is that archived codes drop out of the admin list, so a deleted code never returns as a ghost row an operator has to reason about.

The part worth arguing with

Those columns are in the schema and the migration is written. The admin flows that use them are not built yet, which is the opposite of how I usually work.

It was deliberate here. The shape of financial state is the one thing you cannot iterate your way out of, because by the time you discover the shape was wrong there are rows in it, and a schema fix becomes a rewrite of history that has to agree with the money that actually moved. Code written against the wrong columns is a bad afternoon. Rows in the wrong columns are a reconciliation project.

The column comments above are the whole specification. Four states, one nullable reference, one index, and a rule about which deletes are allowed to be deletes.

See it

The policy half is public and the implementation half is not, which is a fair description of most payment code. Read section 7.2 at cogniprep.app/terms and you can match each promise to a column: "accrues only when a payment completes" is the unique index on the checkout session, "reversed if that payment is later refunded" is the status, and "payouts are made manually, in batches" is the row the two foreign keys point at. The commission is a percentage of the list prices on cogniprep.app/pricing, fixed when a creator is invited, which is why the figures in this table are integer pence and never a converted display amount.