Payment Reconciliation for Engineers
The payment went through, the gateway settled, the bank paid out - did all three agree? The 3-way match, a mismatch taxonomy, fees and FX rules, and a tested Laravel job you can schedule daily.
المحتويات
The payment went through. The gateway settled. The bank paid out. Three systems, three rows - and whether they agree is not something you find out by eyeballing dashboards. This article builds the reconciliation job that checks them mechanically: the 3-way match, the mismatch categories you'll actually see, and a tested Laravel implementation you can schedule. All amounts are integer minor units (see the money bugs article for why floats never touch money).
The 3-way match#

Every payment leaves three records:
- Your order - what you charged: amount, currency, gateway reference.
- The gateway settlement - what the gateway says it collected: gross, fee, net.
- The bank payout - what actually landed in your account.
Reconciliation ties them together per gateway_ref. Anything that doesn't tie out gets categorized, not eyeballed.
Mismatch categories (the taxonomy that matters)#
Every run produces findings keyed by gateway_ref - and one ref can carry more than one finding (a paid order with no settlement and an unmatchable bank payout are reported separately). Duplicated refs collapse to a single DUPLICATE_REF quarantine. Two outcomes are not problems: MATCHED, where all three records agree, and AWAITING_PAYOUT, where a settlement is still inside the expected payout lag - pending, not a mismatch, and never a reason to page anyone. Everything else is a mismatch worth investigating:
| Category | Meaning | First thing to check |
|---|---|---|
| MISSING_SETTLEMENT | order paid, no settlement row at the gateway | gateway settlement report coverage for that day |
| UNKNOWN_ORDER | settlement for an order you don't have | refunds, test mode keys, another system sharing the account |
| AMOUNT_MISMATCH | order amount != settlement gross | currency conversion, partial capture, edited order |
| CURRENCY_MISMATCH | order currency != settlement currency | gateway account config - never fuzzy-match amounts across currencies |
| MISSING_PAYOUT | settled at gateway, absent from bank statement after the lag | payout schedule, bank holidays, payout account |
| PAYOUT_MISMATCH | gateway net != bank amount, same currency | bank-side fees, FX spread on cross-currency payout |
| PAYOUT_CURRENCY_MISMATCH | bank paid in a different currency, even if the amount matches | payout account currency settings - equal amounts in different currencies are not a match |
| UNKNOWN_PAYOUT | bank payout with no settlement row | manual transfers, another system paying into the same account |
| DUPLICATE_REF | more than one import row for the same reference | the import itself - quarantined entirely, never matched |
| GATEWAY_ARITHMETIC | gross != fee + net in the provider's own row | provider data bug - quarantine, never reconcile against it |
The schema is plain: orders (unique gateway_ref, status, paid_at), gateway_settlements (gross/fee/net, currency, settled_at) and bank_payouts (net_minor, currency, posted_at) - all integer minor units, currency carried on every row so equal amounts in different currencies can never match, timestamps on every row so the snapshot filter is exact. A fourth table, reconciliation_runs, stores the report rows. These are the complete migrations, exactly as tested:
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
public function up(): void
{
Schema::create('orders', function (Blueprint $table) {
$table->id();
$table->string('gateway_ref')->unique(); // one order per ref, enforced
$table->unsignedBigInteger('amount_minor');
$table->char('currency', 3);
$table->string('status')->default('pending'); // only 'paid' is reconciled
$table->timestamp('paid_at')->nullable(); // snapshot: paid_at <= asOf
$table->timestamps();
});
Schema::create('gateway_settlements', function (Blueprint $table) {
$table->id();
$table->string('gateway_ref'); // import rows: duplicates possible, detected in code
$table->unsignedBigInteger('gross_minor');
$table->unsignedBigInteger('fee_minor');
$table->unsignedBigInteger('net_minor');
$table->char('currency', 3);
$table->timestamp('settled_at');
});
Schema::create('bank_payouts', function (Blueprint $table) {
$table->id();
$table->string('gateway_ref'); // import rows: duplicates possible, detected in code
$table->unsignedBigInteger('net_minor');
$table->char('currency', 3); // equal amounts in different currencies are not a match
$table->timestamp('posted_at')->nullable(); // snapshot: posted_at <= asOf
$table->timestamps();
});
}
public function down(): void
{
Schema::dropIfExists('bank_payouts');
Schema::dropIfExists('gateway_settlements');
Schema::dropIfExists('orders');
}
};use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration {
public function up(): void
{
Schema::create('reconciliation_runs', function (Blueprint $t) {
$t->id();
$t->string('gateway_ref');
$t->string('category');
$t->string('message');
$t->timestamp('created_at')->nullable();
});
}
public function down(): void
{
Schema::dropIfExists('reconciliation_runs');
}
};Two rules are baked into the code below: verify the provider's arithmetic before trusting the row (gross must equal fee + net, or the row is quarantined), and an order-level mismatch quarantines the whole row - you don't reconcile a payout against an order you already disagree with.
Scope and assumptions#
This is a teaching implementation with a deliberately narrow contract, stated in the code and worth stating here:
- One provider, one merchant account.
gateway_refis the join key; multi-account systems must scope the key to provider + account + reference. - One bank payout row per settlement. Real bank payouts often batch many charges into one credit; batch-level matching (splitting one bank row across settlements) is a separate, harder problem than the transaction-level matching shown here.
- A cumulative snapshot, not a moving window. The job reconciles everything at or before a fixed
asOftimestamp: orders with statuspaidandpaid_at <= asOf, settlements withsettled_at <= asOf, bank rows withposted_at <= asOf. Rows after the snapshot simply don't exist yet for the matcher - a payout posted tomorrow cannot falsely match today. - Payouts take time. A settlement only becomes MISSING_PAYOUT after a lag (2 days here); until then it is AWAITING_PAYOUT, which should not page anyone.
- Imports can contain duplicates.
keyBy()would silently drop them, so duplicated references are detected and quarantined as DUPLICATE_REF before any matching.
The service, executed and tested#
namespace App\Services;
use Carbon\CarbonImmutable;
use Illuminate\Support\Facades\DB;
class ReconciliationService
{
// Deliberately narrow toy scope (see article):
// - one payment provider, one merchant account; gateway_ref is the join key
// - one bank payout row per settlement (no batched payouts; batch-level
// matching is a separate, harder problem discussed in the article)
// - a cumulative snapshot as of $asOf: only orders paid, settlements
// settled and bank rows posted at or before $asOf are in scope
// All amounts are integer minor units. Payouts take time to land, so a
// settlement only becomes MISSING_PAYOUT after PAYOUT_LAG_DAYS.
private const PAYOUT_LAG_DAYS = 2;
public function reconcile(CarbonImmutable $asOf): array
{
$orders = DB::table('orders')->where('status', 'paid')
->where('paid_at', '<=', $asOf)->get()
->keyBy('gateway_ref'); // unique index on orders.gateway_ref
$settlements = DB::table('gateway_settlements')
->where('settled_at', '<=', $asOf)->get();
$payouts = DB::table('bank_payouts')->where('posted_at', '<=', $asOf)->get();
// Imported rows can contain duplicates: keyBy would silently drop
// them, so quarantine every duplicated ref instead of matching one.
[$settleOk, $settleDup] = $this->splitDuplicates($settlements);
[$payoutOk, $payoutDup] = $this->splitDuplicates($payouts);
$report = [];
$dupRefs = $settleDup->keys()->merge($payoutDup->keys())->unique();
foreach ($dupRefs as $ref) {
$report[] = $this->row($ref, 'DUPLICATE_REF',
'more than one import row for this gateway_ref; quarantined');
}
// a duplicated ref is quarantined entirely: none of its rows match anything
$settlements = $settleOk->reject(fn($g) => $dupRefs->contains($g->gateway_ref))->keyBy('gateway_ref');
$bank = $payoutOk->reject(fn($p) => $dupRefs->contains($p->gateway_ref))->keyBy('gateway_ref');
foreach ($settlements as $g) {
$ref = $g->gateway_ref;
$o = $orders->get($ref);
if ($g->gross_minor !== $g->fee_minor + $g->net_minor) {
$report[] = $this->row($ref, 'GATEWAY_ARITHMETIC',
"gross {$g->gross_minor} != fee {$g->fee_minor} + net {$g->net_minor}");
continue; // never reconcile inconsistent provider data against orders
}
if (!$o) {
$report[] = $this->row($ref, 'UNKNOWN_ORDER', 'settlement for an order we do not have');
continue;
}
if ($o->currency !== $g->currency) {
$report[] = $this->row($ref, 'CURRENCY_MISMATCH',
"order {$o->currency} vs settlement {$g->currency}");
continue;
}
if ($o->amount_minor !== $g->gross_minor) {
$report[] = $this->row($ref, 'AMOUNT_MISMATCH',
"order {$o->amount_minor} vs gross {$g->gross_minor}");
continue; // order-level mismatch quarantines the row
}
$payout = $bank->get($ref);
if (!$payout) {
$settledAt = CarbonImmutable::parse($g->settled_at);
if ($settledAt->addDays(self::PAYOUT_LAG_DAYS)->greaterThan($asOf)) {
$report[] = $this->row($ref, 'AWAITING_PAYOUT',
"settled {$settledAt->toDateString()}, payout not due yet");
} else {
$report[] = $this->row($ref, 'MISSING_PAYOUT',
'settled at gateway, still not in bank statement after payout lag');
}
continue;
}
if ($payout->currency !== $g->currency) {
$report[] = $this->row($ref, 'PAYOUT_CURRENCY_MISMATCH',
"settlement {$g->currency} vs bank {$payout->currency}");
continue; // equal amounts in different currencies are not a match
}
if ($payout->net_minor !== $g->net_minor) {
$report[] = $this->row($ref, 'PAYOUT_MISMATCH',
"gateway net {$g->net_minor} vs bank {$payout->net_minor}");
continue;
}
$report[] = $this->row($ref, 'MATCHED', "net {$g->net_minor} {$g->currency} reconciled");
}
foreach ($orders as $ref => $o) {
if (!$settlements->has($ref) && !$dupRefs->contains($ref)) {
$report[] = $this->row($ref, 'MISSING_SETTLEMENT', "order {$o->id} paid but no settlement row");
}
}
foreach ($bank as $ref => $p) {
if (!$settlements->has($ref)) {
$report[] = $this->row($ref, 'UNKNOWN_PAYOUT',
"bank payout {$p->net_minor} {$p->currency} with no settlement row");
}
}
return $report;
}
/** @return array{0: \Illuminate\Support\Collection, 1: \Illuminate\Support\Collection} */
private function splitDuplicates($rows): array
{
$grouped = $rows->groupBy('gateway_ref');
return [
$grouped->filter(fn($g) => $g->count() === 1)->map->first()->values(),
$grouped->filter(fn($g) => $g->count() > 1),
];
}
private function row(string $ref, string $category, string $message): array
{
return ['ref' => $ref, 'category' => $category, 'message' => $message];
}
}The fixture dataset produces this real report (standalone run of the same logic, PHP 8.4):
PM-884 DUPLICATE_REF more than one import row for this gateway_ref; quarantined
PM-881 MATCHED net 24275 EGP reconciled
PM-882 MATCHED net 38840 EGP reconciled
PM-883 AMOUNT_MISMATCH order 1999 vs gross 1900
PM-885 UNKNOWN_ORDER settlement for an order we do not have
PM-889 AWAITING_PAYOUT settled 2026-11-01, payout not due yet
PM-886 MISSING_SETTLEMENT order 1005 paid but no settlement row
PM-890 UNKNOWN_PAYOUT bank payout 1200 EGP with no settlement row
--------------------------------------------------
DUPLICATE_REF 1
MATCHED 2
AMOUNT_MISMATCH 1
UNKNOWN_ORDER 1
AWAITING_PAYOUT 1
MISSING_SETTLEMENT 1
UNKNOWN_PAYOUT 1Scheduling it#
A thin job persists each run's report rows so you can route anything actionable by severity and age:
namespace App\Jobs;
use App\Services\ReconciliationService;
use Carbon\CarbonImmutable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Queue\Queueable;
use Illuminate\Support\Facades\DB;
class ReconcilePayments implements ShouldQueue
{
use Queueable;
public function __construct(private CarbonImmutable $asOf) {}
public function handle(ReconciliationService $recon): void
{
$rows = $recon->reconcile($this->asOf);
DB::table('reconciliation_runs')->insert(array_map(fn($r) => [
'gateway_ref' => $r['ref'],
'category' => $r['category'],
'message' => $r['message'],
'created_at' => now(),
], $rows));
}
}Wire it in routes/console.php:
use App\Jobs\ReconcilePayments;
use Illuminate\Support\Facades\Schedule;
Schedule::job(new ReconcilePayments(now()->toImmutable()))->dailyAt('06:30');Schedule it after your providers actually publish: check when your gateway's settlement report and your bank statement really arrive and run after the later of the two rather than assuming a generic early-morning hour. Page only on mismatch categories, routed by severity and age - AWAITING_PAYOUT is expected and stays silent, and a daily "all matched" email is noise.
Fees and FX#
Fees are why you can't match order amount to bank amount directly: the gateway row decomposes into gross / fee / net, and only net ever reaches the bank. Store all three per settlement; your revenue reports and your accountant both need the fee column, and gross = fee + net is a cheap integrity check on the provider's data.
FX is the case the matcher refuses: if the settlement currency differs from the order currency, that's CURRENCY_MISMATCH, not a discrepancy to average away. Cross-currency settlements must be matched using the provider's own conversion record (their documented rate on that settlement), never a rate you fetch from elsewhere - two sources of truth is how reconciliation scripts manufacture mismatches. The multi-currency exponent problem from the money article applies here too.
Where this connects#
Reconciliation assumes each webhook and order exists exactly once - that's idempotency. And once a mismatch is resolved, the correction belongs in a double-entry ledger, not an UPDATE on a balance column.
Common mistakes#
| Mistake | What it costs |
|---|---|
| Matching order amount to bank amount | Fees guarantee a permanent "mismatch" you learn to ignore - until a real one hides in it |
| Reconciling a row whose gross/fee/net don't add up | You're now debugging the provider's bug in your books |
| Fuzzy amount matching across currencies | False matches; FX belongs in the provider's settlement record |
| Alerting on every run | Alert fatigue; only actionable mismatch categories should page anyone, and pending rows (AWAITING_PAYOUT) never do |
| Fixing mismatches by editing the order | Corrections go through the ledger with an audit trail |
Key takeaways#
Three sources, one reference key, a fixed category taxonomy. Verify provider arithmetic first, quarantine mismatched rows, match integer minor units with currency on every row, snapshot everything at a fixed timestamp, and alert only on actionable mismatches. The service above is tested against a real MySQL database - and it still needs ongoing care: validate import completeness and watch for provider format changes, because those, not the matcher, are what quietly break a reconciliation job.
References#
- Money bugs (minor units, rounding, exponents): /en/blog/money-bugs-currency-rounding-discounts-vat
- Idempotency keys in Laravel: /en/blog/idempotency-keys-laravel-stop-double-charges
- Double-entry ledger wallet: /en/blog/double-entry-ledger-wallet-laravel-mysql
Reconciliation quietly failing somewhere in your payment stack? See /en/services.