Affiliate commission tracking that adds up.
AffiliateTracking does affiliate commission tracking on a double-entry ledger. Every sale, refund and payout is a journal that sums to exactly zero, and nobody can edit one later. A mistake gets a new entry that reverses it, so your affiliates see the same history you do.
Every journal sums to 0.00Fixes are new entries, never editsRules enforced by the database
One number is not a record
Where commission tracking goes wrong
Most payout disputes come down to one question: how did this number get here? These are the three ways the answer gets lost.
A balance with no history
When a balance is one number you keep overwriting, nobody can show how it got there. Here, every change is its own journal, with a time and a source.
Retries that pay twice
Billing tools resend webhooks when they don't hear back. Count every delivery and one sale becomes two commissions. Here, one event credits once.
Fixes that erase the past
Edit a wrong amount and the old one is gone, along with your side of the dispute. Here, a fix is a new entry and the original stays on record.
Double-entry, in one line. It works like a bank transfer: money leaves one account and lands in another, so the total never changes. Each move is a journal, and every journal sums to exactly 0.00.
Follow one sale through the books
Three journals take a commission from sale to payout. Here's each one, line by line, the way the ledger stores it.
ExampleA $200.00 sale at a 25% rate earns affiliate 42 a $50.00 commission. Made-up numbers, real account names.
-
Day 0The sale comes insource: conversion
Journal 1, sale credited: EXPENSE:COMMISSION plus 50.00, HOLD minus 50.00, sum 0.00 EXPENSE:COMMISSION +50.00 HOLD −50.00 Sum 0.00 AffiliateTracking credits the commission and parks it in HOLD. It isn't owed yet, so no payout run can touch it.
-
Day 30The refund window closessource: maturation
Journal 2, commission matures: HOLD plus 50.00, AFFILIATE_PAYABLE:42 minus 50.00, sum 0.00 HOLD +50.00 AFFILIATE_PAYABLE:42 −50.00 Sum 0.00 The commission matures and moves into what you owe affiliate 42. The wait is 30 days by default, and you set it per campaign.
-
Next payout runThe affiliate gets paidsource: payout
Journal 3, payout: AFFILIATE_PAYABLE:42 plus 50.00, PAYOUT_CLEARING minus 50.00, sum 0.00 AFFILIATE_PAYABLE:42 +50.00 PAYOUT_CLEARING −50.00 Sum 0.00 You owe affiliate 42 nothing now. Payouts cover whole commissions, never part of one.
How to read the signs: plus is a debit, minus is a credit. Money you owe shows as a minus, the same way the product shows it.
After all 3 journalsAdd up every account and you get zero. The doctor command runs the same check on your whole ledger.
- EXPENSE:
COMMISSION - +50.00
- HOLD
- 0.00
- AFFILIATE_PAYABLE:
42 - 0.00
- PAYOUT_CLEARING
- −50.00
- Sum of everything
- 0.00
| HOLD | +50.00 |
|---|---|
| EXPENSE:COMMISSION | −50.00 |
| Sum | 0.00 |
The money comes straight back out of HOLD and the commission ends as cancelled. A partial refund claws back the same share: refund $100.00 and $25.00 comes back.
| AFFILIATE_PAYABLE:42 | +50.00 |
|---|---|
| EXPENSE:COMMISSION | −50.00 |
| Sum | 0.00 |
The payable goes to +50.00, which means affiliate 42 owes you $50.00. The next payout run nets it against their new earnings.
Triggers, not habits
Rules the database enforces
These rules live in database triggers, below the app. So they hold even if our code has a bug, or someone runs a bad script by hand.
No journal out of balance
Every journal has to sum to exactly 0.00. The database refuses one that doesn't.
No one-sided entries
A journal needs at least two lines. Money always comes from somewhere.
No paying more than you owe
A payout can't take a payable past zero. Refund clawbacks are the one exception, and payouts net them.
No edits or deletes
Nobody can change or remove a money row. A fix is a new entry that reverses the old one.
No skipped steps
A commission goes pending, approved, then paid. Or it ends cancelled or clawed back. Nothing else.
No splits over 100%
Split a sale's commission between affiliates and the shares can't add up to more than all of it.
No oversized clawbacks
A clawback points to its commission and can't take back more than that commission paid.
No part-payments
Payouts cover whole commissions. Paying half of one would break reconciliation, so it isn't allowed.
Money is whole cents. Every amount lives as an integer in the smallest unit, so there's no floating-point rounding anywhere. On Postgres you can also run the app as a role with no DELETE or TRUNCATE rights on money tables. The deploy guide shows how.
A webhook retry counts once
Billing tools resend webhooks when they don't get a quick answer. A balanced ledger won't catch that on its own, because a second credit still sums to zero.
We tested it. With the unique check switched off, a replayed event paid twice and the books still balanced. So the ledger keeps each event from every billing tool we connect to once, by its source and event ID, and a repeat posts nothing. Every journal carries its own key too, so a job that died halfway finishes on retry instead of running twice.
- 14:02:11 UTCevt_example_1First deliveryCredited $50.00
- 14:02:41 UTCevt_example_1Same event, sent againAlready processed
- 14:07:41 UTCevt_example_1Same event, sent againAlready processed
Your affiliates can check the math
Each affiliate gets a portal built from the same journal you use. Their earnings sit on top, and below that, every journal that touched their balance.
Every journal, listed
Each row shows the journal number, its source, the amount, how many lines it has, its sum and a running balance. Refunds show up as their own clawback rows.
Only their own
Each affiliate signs in with a key scoped to their account, so they see their entries and nobody else's.
Shorter disputes
When someone asks where a number came from, you send a journal number instead of rebuilding it in a spreadsheet.
Check the books yourself, any time
On SQLite installs, the built-in doctor command adds up your ledger from scratch and compares it with your commission records. One command, 6 checks.
- Every entry ever posted adds up to 0
- No single journal is out of balance
- What the ledger says you owe matches the commission records
- Yesterday's click totals match the raw clicks
- Fewer than 1,000 outgoing events are waiting to send
- Open fraud holds, counted for your review queue
Checks 1 to 3 decide the result. The others report.
# Check the books $ ./affiliate-engine -db sqlite:affiliate.db doctor (one line per check) doctor: ALL CHECKS PASSED # Anything off prints FAILURES DETECTED # and exits with code 1, so cron can alert you.
Ledger FAQ
What is a double-entry ledger?
It's a way of recording money where every move has two sides: it leaves one account and lands in another. Each move is a journal, and each journal sums to zero, so a missing or extra amount shows up right away.
Why does money I owe show as a negative number?
That's the accounting sign rule. What you owe an affiliate is a credit to their payable account, and credits are negative. A payable of -$1,289.00 means you owe $1,289.00.
Can I edit a commission with the wrong amount?
No. Nobody can edit or delete a money row. A refund or an upheld fraud hold posts a reversing journal, so the original and the fix both stay on record.
What happens if a buyer refunds after I've paid the affiliate?
The clawback takes their payable above zero, which means they owe you. The next payout subtracts it from their new earnings. If they owe more than they've earned, nothing is paid until it's covered.
How are partial refunds handled?
The same share of the commission comes back. Refund half the sale and half the commission comes back, worked out in whole cents. It can never take back more than the commission.
Can a webhook retry pay a commission twice?
No. AffiliateTracking keeps each event once, by its source and event ID. A repeat delivery shows as already processed and posts nothing.
How do different currencies work?
Each commission keeps its own currency. Payout runs group by affiliate and currency, so they never add amounts in different currencies together.
Does the ledger work on SQLite and Postgres?
Yes. The same rules and triggers run on both. Self-host starts on SQLite with nothing extra to install, and you can move to Postgres when you outgrow it.
Which plans include the ledger?
All of them. It's the core of the product, not an add-on. See self-host and cloud prices.
Books that balance
from your first sale.
Self-host for $99 once, and every commission lands in the ledger from day one. Or let us run it, free for 14 days.