Skip to content
allanninal.dev/build
Collaborate

Part 1 of 7 · Payout reconciler series ~6 min read

A payout reconciler on AWS for a few dollars a month

A homeware retailer with one shop, a website and a marketplace listing booked £451,168 of sales last year. It sold £486,213. Nobody had taken anything and nobody had mistyped anything. Every payout that reached the bank had been entered as a sale, and a payout is not a sale.

person holding credit card swipe machine
Photo by Blake Wisz on Unsplash

Key takeaways

  • A payout is a net figure: sales less refunds, disputes, fees and whatever the provider held back.
  • Booking payouts as sales understates revenue and hides every cost the provider deducted.
  • One clearing account per provider, whose balance is what that provider owes you.
  • That balance can be checked against the provider’s own figure, which is the point.
  • Designed on AWS for about $2 a month.

The whole system on one page

Before any code, here is the shape of what we are designing.

System: provider reports and bank lines reconciled into a gross ledger journalThree boxes across the top sit outside the AWS account. On the left, Payment providers: reports, statements and payouts. In the middle, The bank: deposits and debits, with a reference. On the right, The accounts: where the journal finally lands. The first two connect by arrows down to the AWS account container below, labelled daily and as it lands, and an arrow labelled at month end runs back up to The accounts. Inside the AWS account are three components in a row. Settlement lines: every sale, refund, fee and hold. Clearing accounts: one per provider, checked against theirs. And Match and close: the bank, the cut-off and a gross journal. A note says the reports and the bank lines already exist, and what is missing is an account whose balance says what each provider owes.AWS ACCOUNTPayment providersreports, statementsand payoutsThe bankdeposits and debits,with a referenceThe accountswhere the journalfinally landsSettlement linesevery sale, refund,fee and holdClearing accountsone per provider,checked against theirsMatch and closebank, cut-off anda gross journaldailyas it landsat month endThe reports and bank lines already exist. What is missing is an account whose balance says what each provider owes.
Fig 1. Three things outside the account, three pieces inside it. The middle box is the one that makes the other two checkable.
  • Machine learning
  • Networking
  • Management
  • Analytics

Why this is hard for a business that is otherwise well run

Because the bank statement looks like the answer. A deposit arrives from the card terminal provider every working day, the amount is roughly what the till said, and the obvious bookkeeping entry is a sale of that amount. Doing anything else means opening a report nobody asked for, in a format that differs for every provider, and splitting one bank line into dozens.

It also works well enough to go unnoticed for years. The bank reconciles, because the entries were copied from the bank. The VAT return goes in. The only symptom is that the sales figure in the accounts is a little lower than the sales figure in the till and the website, and every business has a story for why those two never quite agree.

What runs (the inside)

  • Settlement lines. Every provider’s report broken into individual sales, refunds, disputes, fees and holds, in one shape. Part 2.
  • Clearing accounts. One per provider, whose balance is what that provider owes you, checked against the provider’s own figure. Part 3.
  • The unexplained lines. Adjustments with a sentence where a type code should be, and how they get a type. Part 4.
  • Match and close. Bank lines matched to payouts, the month cut off on the date of the sale, and a journal posted gross. Part 5.

One retailer, one financial year

One retailer's year from payouts banked to the gross figure actually soldA horizontal row of five boxes joined by arrows. Five hundred and seventy-three payouts from four providers, four hundred and fifty-one thousand one hundred and sixty-eight pounds banked. Booked as sales: the same four hundred and fifty-one thousand one hundred and sixty-eight pounds, because that is what arrived. Four hundred and eighty-six thousand two hundred and thirteen pounds sold, gross, per the providers' reports. Deducted: thirty-three thousand eight hundred and seventy-six pounds in refunds, disputes and fees. And More held: one thousand one hundred and sixty-nine pounds more held by the providers than a year earlier, owed to the business and in nobody's books. A note says none of this is missing money, but money in the wrong place: costs netted off sales, and a balance owed to the business that no ledger was carrying.ONE RETAILER, ONE FINANCIAL YEAR573 payoutsfrom four providers,£451,168 bankedBooked as sales£451,168, becausethat is what arrived£486,213 soldgross, per theproviders' reportsDeducted£33,876 in refunds,disputes and feesMore held£1,169 owed to you,in nobody's booksNone of this is missing money. All of it is money in the wrong place: costs netted off sales, and a balance owed to the business that no ledger was carrying.
Fig 2. The same year as one line. The gap between the second box and the third is £35,045, and every pound of it was explained in a report nobody opened.

In plain words

Each provider publishes a report of what it did with your money: every payment it took, every refund it sent back, every dispute it lost on your behalf, every fee, and every amount it decided to hold for a while. It then pays out whatever is left, in a lump, on its own schedule. The report is the ledger; the payout is only its last line.

The system reads those reports as they appear and turns each one into settlement lines of a single shape, whichever provider it came from. A card terminal provider’s daily file, an online checkout’s balance report, a PayPal activity download and a marketplace’s fortnightly statement all become the same thing: a dated line with a type, an amount and the payout it belongs to.

Those lines post to a clearing account, one for each provider. Sales push the balance up; refunds, fees and disputes pull it down; a payout moves money from the clearing account to the bank. What is left at any moment is what the provider owes you, and the provider publishes its own version of that figure, so the two can be compared.

Then the bank lines are matched to the payouts, the month is cut off on the date each sale happened rather than the date it was paid out, and the accounts receive a journal with sales, refunds, disputes and fees on their own lines. The sales figure in the books becomes the figure the till and the website already knew.

Design rules that shaped every decision

  • A payout is never a sale. It is a transfer from a clearing account to the bank.
  • Every provider gets its own clearing account, and its balance must be explainable.
  • The lines inside a payout must sum to the payout to the penny, or nothing posts.
  • Reports are parsed. The model only sees what no rule can type.
  • Cut-off follows the date of the sale, not the date of the money.
  • History is appended. Provider reports only go back so far, and this store is the record.

What it does not do

It does not judge whether the fees are reasonable. A fee line here is an expense with a date and an amount, and whether it is the right amount is a different question with its own system elsewhere in this series. Mixing the two produces a reconciliation that stops every month to argue about pricing.

It does not fight disputes either. A dispute appears here as money leaving the clearing account and, if it is later won, as money coming back. Gathering the evidence to win it happens somewhere else; the reconciler only needs the outcome.

And it does not post to the accounts on its own. It writes a journal and a reconciliation pack for each month, and a person imports the journal after reading the pack. A month-end entry that changes reported sales is the kind of entry somebody should have looked at.

The next four posts walk through each piece: what is actually inside a payout, the clearing account that makes it checkable, the adjustment lines nobody can explain, and matching the bank before closing the month. One diagram per post, a cost breakdown, and an engineering reference at the end.

All posts