Building the reconciliation data model

When a reconciliation process produces answers nobody trusts, the cause is rarely the matching rules. It is that the underlying structure cannot hold the situation that occurred, so something had to be forced into a shape that does not fit.

This is what the structure has to be able to represent.

Store what each party said, separately

The foundational decision, and the one most often got wrong.

Keep your store's record, the gateway's record and the bank's record as three distinct things, each stored as received. Then hold the matches between them as their own records.

The tempting alternative is one enriched order record with fee and payout fields filled in as the data arrives. It is simpler, it works, and it destroys the ability to answer the question that matters: what did each party actually say?

When a figure is disputed — by an auditor, by your provider, by your own suspicion — you need the gateway's original assertion, unmodified, alongside your own. If the fee was written into the order record and later corrected, the original is gone and the disagreement cannot be investigated.

Store the source data immutably. Derive everything else.

The states that have to exist

A payment is not paid or unpaid. Modelling it as a boolean is the single most common structural error, and each missing state produces its own class of unexplainable difference.

Authorised, not captured. Funds held, money not moved. An authorisation that expires uncaptured is a paid order that silently becomes unpaid.

Captured, not settled. The payment succeeded and the payout has not arrived. Normal for days.

Settled, not paid out. The gateway has finalised it and the transfer is pending.

Paid out, not allocated. Money in the bank whose settlement report has not been processed — see when the payout arrives before the report.

Withheld. Included in a settlement and retained as reserve — see payment gateway holds and reserves.

Reversed. Succeeded and then undone, before or after settlement.

Disputed. Under chargeback, outcome unknown, possibly returning.

Seven states, and each one exists because real money sits in it. A model with two states has to represent the other five as something else, and whatever it chooses will be wrong somewhere.

Money needs its currency attached

For any store selling across a border, a bare amount is not enough.

Store the transaction currency, the amount in it, the rate applied, the rate date, and the base-currency amount. All five, on every record.

Two of the five are usually kept, and then a question arrives — why does this payout differ from the order total — and the answer requires the rate and the date that are missing. Recomputing at today's rate produces a different number and no explanation — see currency conversion at checkout and who pays for it.

Both dates, always

Every record needs the date the event happened and the date you learned about it.

Nearly every process keeps only the first, and the second is what makes late-arriving data tractable. A chargeback on a three-month-old order is an event dated then and known now. Accounted for in the old period, it reopens a closed month; accounted for with no reference to the original, it floats.

Holding both lets you say what your books showed at the time and what they show now, and reconcile the difference. That is the difference between explaining a restatement and discovering one — see reconciling across a period boundary.

Matches are records, not fields

A match is a relationship with properties, and it needs to be stored as one.

Which records were matched. Which tier or rule produced it — reference, composite, or a person. When. By whom, if manual. And, critically, whether it was later unmatched, and why.

That last property is the one that gets omitted, and it is what makes an audit possible. A match stored as a field on the order can be overwritten, leaving no trace that a different allocation was there yesterday. A match stored as a record can be superseded with the history intact.

Automated matching gets things wrong occasionally. The question is not whether it errs but whether you can see that it did — see rolling back an automation that failed.

Keep the raw file

Whatever arrived — the settlement file, the API response, the bank export — keep it as it arrived, alongside the parsed records.

Three reasons, all of which arrive eventually.

Your parser had a bug. Discovered three months later. With the raw files you reprocess; without them the period cannot be corrected.

The provider republished a file with different contents. Comparing what you received against what is now available is only possible if you kept the original — see gateway API or settlement file.

Somebody asks how a figure was derived. The raw source plus the parsing rule is a complete answer. A parsed record alone is an assertion.

Storage is cheap. Reprocessing a period you no longer have the inputs for is not possible at any price.

What this buys you

A structure holding source data immutably, seven payment states, full currency detail, both dates, matches as records and the raw files can answer questions that a flatter model cannot.

Why does this payout differ from these orders. What did we think last month and what do we think now. Which matches were automatic and which were decided by a person. What changed when we reprocessed. Where exactly is our unexplained money sitting.

None of those need answering often. Each of them, when it does need answering, is either a five-minute query or a week of reconstruction, and the model decides which — see keeping automated books healthy.

Common questions

Why store store, gateway and bank data separately?

Because each is a party's assertion about the same money, and when a figure is disputed you need the original unmodified. Writing the gateway's fee into an enriched order record is simpler and destroys the ability to say what the gateway actually reported, so a later correction leaves no evidence of what came before it.

How many payment states does a reconciliation model need?

Seven, at minimum: authorised but not captured, captured but not settled, settled but not paid out, paid out but not allocated, withheld as reserve, reversed, and disputed. Each exists because real money sits in it, so a model with only paid and unpaid must represent the other five as something they are not.

Why record the date you learned about an event as well as the date it happened?

Because late-arriving data is routine. A chargeback on a three-month-old order is dated then and known now, and holding both dates lets you say what the books showed at the time and what they show now. Keeping only the event date means either reopening a closed period or leaving the item floating.

Why keep the raw settlement files after parsing them?

Because parsers have bugs discovered months later, providers sometimes republish files with different contents, and someone will eventually ask how a figure was derived. With the raw files a period can be reprocessed and a derivation demonstrated; without them the period cannot be corrected at all.

Related: decomposing a payout line by line · matching orders to transactions to payouts · what APIs you need for Shopify reconciliation