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#

Three sources feeding one 3-way match that produces a mismatch report
Orders, gateway settlements and the bank statement feed one 3-way match

Every payment leaves three records:

  1. Your order - what you charged: amount, currency, gateway reference.
  2. The gateway settlement - what the gateway says it collected: gross, fee, net.
  3. 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:

2026_11_09_000000_create_recon_tables.phpPHP

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');
    }
};
2026_11_09_000001_create_reconciliation_runs_table.phpPHP
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_ref is 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 asOf timestamp: orders with status paid and paid_at <= asOf, settlements with settled_at <= asOf, bank rows with posted_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#

ReconciliationService.phpPHP
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):

Code
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           1

Scheduling it#

A thin job persists each run's report rows so you can route anything actionable by severity and age:

ReconcilePayments.phpPHP
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:

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.