Reconciling a ledger against a settlement file

Captured in your database means the gateway accepted an instruction. Settled means the money moved, net of fees, into the account you can actually spend. Those are different days, and they are different amounts.

Reconciliation is the job that reads the gateway’s settlement file and checks it against the ledger. If you skip it, refunds, partial captures and fee lines silently diverge from what finance thinks you hold. A sandbox will not show you this. Test payments often never settle, which is why it is called out in what a sandbox will not let you test.

What you need

• A ledger row per payment, keyed by the provider’s payment id, with amount in minor units and a currency. Major units and floats will not match a file.
• The settlement file, or the settlement API, for one payout period.
• A matcher that classifies every row as matched, missing, or conflicting. It must not “fix” conflicts by updating the ledger.

Match on the reference, then compare the money

Amount is not an identifier. Two payments of 100.00 SAR on the same day are normal. The provider payment id is the join key. If a file row has no id you recognise, it is an exception, not a fuzzy match.

public enum Match { Matched, MissingInLedger, MissingInFile, Conflict }

public readonly record struct SettlementLine(
    string ProviderPaymentId,
    long AmountMinor,
    string Currency,
    long FeeMinor);

public static Match Classify(LedgerEntry? ledger, SettlementLine? line)
{
    if (ledger is null && line is not null) return Match.MissingInLedger;
    if (ledger is not null && line is null) return Match.MissingInFile;
    if (ledger!.Currency != line!.Currency) return Match.Conflict;
    if (ledger.AmountMinor != line.AmountMinor) return Match.Conflict;
    if (ledger.FeeMinor != line.FeeMinor) return Match.Conflict;
    return Match.Matched;
}

Fees are their own comparison. A file that says you captured 10,000 and were paid 9,650 is not a conflict if 350 is the fee and you stored 350 as the fee. It is a conflict if you stored the net as the gross, or if you ignored fees and expected 10,000 to land.

Run the classification in both directions. Lines in the file with no ledger row are money you did not know about — a payment recorded only at the gateway, or a file from the wrong account. Ledger rows that are captured and old enough to have settled, with no file line, are money you are recording and not receiving.

“Old enough” is a parameter you set from the provider’s published payout delay, and you confirm it against the first real file. Do not hard-code T+2 because a blog did. Until you have seen one file, treat the delay as unknown and do not alert on absence.

What the job is not allowed to do

The matcher writes a report. It does not post correcting entries by itself.

A conflicting amount might be a partial capture you failed to record, a currency exponent you mishandled (KWD and BHD have three decimal places; a two-place assumption will conflict on every line), or a file you parsed in the wrong column. Auto-adjusting the ledger toward the file hides all three, and it hides a malformed parse just as effectively as a real fee change.

public sealed record ReconReport(
    int Matched,
    IReadOnlyList<string> MissingInLedger,
    IReadOnlyList<string> MissingInFile,
    IReadOnlyList<string> Conflicts);

public static ReconReport Build(
    IReadOnlyDictionary<string, LedgerEntry> ledger,
    IReadOnlyList<SettlementLine> file,
    DateTimeOffset capturedBefore)
{
    var missingInLedger = new List<string>();
    var conflicts = new List<string>();
    var seen = new HashSet<string>(StringComparer.Ordinal);
    var matched = 0;

    foreach (var line in file)
    {
        seen.Add(line.ProviderPaymentId);
        ledger.TryGetValue(line.ProviderPaymentId, out var entry);
        switch (Classify(entry, line))
        {
            case Match.Matched: matched++; break;
            case Match.MissingInLedger: missingInLedger.Add(line.ProviderPaymentId); break;
            case Match.Conflict: conflicts.Add(line.ProviderPaymentId); break;
        }
    }

    var missingInFile = ledger.Values
        .Where(e => e.State == PaymentState.Captured
                    && e.CapturedAt <= capturedBefore
                    && !seen.Contains(e.ProviderPaymentId))
        .Select(e => e.ProviderPaymentId)
        .ToList();

    return new ReconReport(matched, missingInLedger, missingInFile, conflicts);
}

Page a person when Conflicts or MissingInLedger is non-empty. Absence from the file can wait until you trust the delay. A conflict cannot.

Refunds after settlement are a second line, not an edit of the capture line. If you subtract the refund from the original ledger row, the file — which still shows the capture, plus a separate refund — will conflict forever. Keep both rows. The same state machine as out-of-order webhooks already has a refunded transition; the file should see both events.

Failure modes

Symptom	Cause	Fix
Every line conflicts by a few percent	Compared gross to net and called the fee an error	Store fee separately and compare both
Every KWD line conflicts	Treated three-decimal currencies as two	Minor units from the currency exponent, not a fixed ×100
Same payment flagged every day	Report not idempotent on the payout id	Key the run to the file’s payout id; rerunning updates that run
Ledger “corrected” to a bad parse	Job wrote adjustments	Report only; a human posts
Refunds never match	Refund subtracted from the capture row	Two ledger rows, two file lines

Production checklist

• Provider payment id as the join key, amounts in minor units
• Fee stored and compared on its own
• Both directions: file-not-in-ledger and ledger-not-in-file
• No automatic ledger writes from the matcher
• Payout delay confirmed from a real file before absence alerts arm
• One report per payout id, safe to rerun
• Currency exponents not assumed

Sources

There is no single scheme file format to cite, because each gateway publishes its own. The rule that does travel — do not treat an authorisation or a capture response as settled funds — is why the matcher exists.

Use the provider’s settlement documentation for column names, and date the copy you parsed. When the column you keyed on disappears, the report should fail closed on a parse error, not classify every line as missing.

Related: retry and backoff, for the duplicate charges that show up here as two ledger rows and one file line.