Kill the Friday CSV: idempotent affiliate payouts

Every affiliate program we've onboarded had the same Friday ritual. Someone exports a CSV from the affiliate platform, opens a spreadsheet, pastes wallet addresses next to amounts, eyeballs it, and starts sending. Halfway through, a transaction fails, or the laptop sleeps, or finance asks "did Maria already get paid?" — and now nobody knows which rows are done. The safe move becomes the dangerous one: re-send the whole file and hope.

That ritual is the bug. The fix is not a bigger spreadsheet. It's making the payout run replayable, so re-sending the whole file pays each affiliate exactly once no matter how many times you press go.

There is no "send batch" button, and that's the point

People assume mass payouts means one API call that swallows a CSV and atomically sends 4,000 transfers. We deliberately didn't build that. Mass payouts is a fan-out over the single-payout API — your runner loops the file and calls POST /api/v1/payouts once per line. No POST /payouts/batches. No magic envelope.

This sounds like more work. It's actually less, because it gives you the one property a black-box batch endpoint can't: per-line idempotency you control.

Each line carries its own idempotency_key. The natural choice is the identifier the affiliate platform already gives you — the commission line ID, or a deterministic hash of (program_id, affiliate_id, period). Not a row number. Not a UUID you mint at send time. The key has to be stable across reruns of the same source data, or it buys you nothing.

POST /api/v1/payouts
X-API-Key: $HALFIN_API_KEY
Content-Type: application/json

{
  "currency": "USDT_TRC20",
  "amount": "182.40",
  "destination": "TExampleTronAddress0000000000000000000",
  "idempotency_key": "comm_2026w23_aff_4817",
  "reference": "Affiliate commission · week 23 · aff_4817"
}

Send that line twice — same idempotency_key — and the second call is a no-op that returns the original payout. Send it from a crashed run, a retried run, a nervous-operator double-click: still one payment. That is the whole trick, and it's why retrying the entire file is safe. The exact response shape is in the single payouts docs.

Why "retry the whole file" has to be the supported path

Operators don't surgically retry row 2,817. Under pressure they re-run the job. So design for that.

A correct runner does this per line:

1. Read the line and compute the stable idempotency_key.
2. POST the payout with that key in the idempotency_key field.
3. Record the returned payout ID and state next to the source line.
4. On any error that isn't a definitive rejection — timeout, 5xx, dropped connection — retry the same line with the same key, not a new one.

The lines already paid short-circuit. The lines that failed get another honest attempt. The run becomes idempotent end to end, and "did Maria already get paid?" stops being a question you answer by squinting at a blockchain explorer. It's a state on the payout record.

The failure mode to avoid is generating a fresh key on retry. That turns one commission into two payments and one annoyed affiliate into one annoyed finance team. The key is the contract. Treat it like one.

Approval is a human gate, not a code path

Idempotency keeps you from paying twice. It does nothing to stop you paying the wrong amount to the right person, which is the more common and more expensive mistake. That's what the approval step is for, and it belongs to a human.

The pattern we recommend: the runner stages the full payout set, the dashboard shows it as a reviewable list — total, line count, per-asset breakdown, any addresses that failed validation — and a second operator approves before anything sends. Two people, one of whom didn't build the file. Boring, and it catches the decimal-point error that no schema validation will.

Validate before the human looks, so they review signal and not noise:

• Address format matches the chosen network. A USDT on Tron address pasted into an Ethereum payout is a rejection, not a send.
• The asset and network are actually supported for that program.
• Duplicate idempotency_keys inside the same file are flagged — usually a sign the source export double-counted a commission.
• Per-affiliate totals look sane against the program's history.

Lines that fail validation don't block the rest. They land in an exceptions list someone owns, while the clean lines proceed.

The audit trail is the deliverable

When an affiliate emails "where's my money," the answer should take ten seconds, not a forensic dig. Every payout carries its source reference and its idempotency_key, so you can walk it backward: commission line → payout ID → on-chain transaction → confirmation. Finance closes the week from exportable records, not screenshots, and the idempotency_key is the join key between your affiliate platform and the payment layer.

This is also why you sign your status events. payout.completed arrives over a webhook when a line settles on-chain — verify the HMAC signature before you mark the commission paid in your own system. Don't poll the payout endpoint in a loop waiting for it; let the event tell you, then reconcile. (We wrote up the delivery side in designing webhooks that survive everything — the same idempotency discipline applies on the receiving end.)

A rollout that won't scare your finance team

Don't go from spreadsheet to fully automated fan-out in one Friday.

1. Single payouts, by hand. Pay five internal test affiliates through the API. Confirm the idempotency key short-circuits a deliberate double-send.
2. A small real batch with a human gate. Twenty affiliates, runner stages, operator approves in the dashboard, you watch every payout.completed.
3. The full file, retried on purpose. Run it, kill it at row 50, re-run the whole thing. Prove the count of on-chain sends equals the count of distinct commission lines. If it doesn't, your keys aren't stable — fix that before scaling.
4. Automated from the affiliate platform once the support playbook is written and someone other than the author has run it.

The goal of stage three is not throughput. It's the moment finance watches you re-run a half-failed payout file and sees the total stay exactly the same. That's when the Friday CSV finally dies.

The operating rule

The single-payout fan-out plus a stable per-line key plus a human approval gate is not three features. It's one property — a payout run you can replay without fear — expressed three ways. Build for the retry, because the retry is what actually happens.

For where this sits in a full program, the affiliate marketing hub maps deposits, tracking, and payouts together. The payout side is the part you'll touch every week, so it's the part worth making boring.

R. Adeyemi, halfin payments engineering