Multi-Rail Payment Reconciliation in Mexico: Squaring Stripe, Mercado Pago, SPEI and OXXO Against One Ledger You Control

Why “did we get every peso?” is so hard to answer

To reconcile across Stripe, Mercado Pago, SPEI and OXXO, you cannot trust any single gateway dashboard — each only sees its own rail. Build one ledger you control with a row per expected payment, then match every rail’s settlement report or webhook against it: idempotently, net vs gross, accounting for each rail’s settlement timing.

A Mexican business almost never sells on one rail. A typical week looks like this: card payments through Stripe, checkout through Mercado Pago, a few clients paying by SPEI bank transfer, and a chunk of customers paying cash at an OXXO counter. Four rails, running in parallel, every single day.

Here’s the problem nobody warns you about: each rail is its own silo. Stripe has its own settlement timing, its own fee structure, and its own report format. Mercado Pago has a completely different one. SPEI clears on its own clock. OXXO is cash, so it behaves like nothing else. And critically, each rail’s documentation only ever reconciles its own rail. Stripe’s reconciliation docs will happily square what Stripe settled — and they have zero idea that a customer also wired you 8,000 pesos over SPEI yesterday.

So the real business question — did we actually receive every peso we were owed? — crosses all four rails at once. No single gateway dashboard can answer it, because no gateway can see the others. The only thing that can answer it is a ledger you build and control yourself: one source of truth that every rail reports into.

I’m an engineer, not a contador (accountant). What follows is a reconciliation pattern for the system — how to model it and handle the edge cases — not the accounting books. Pair it with your contador for the fiscal side. If you want the lay of the land on the rails themselves first, I wrote that up in the payment gateways in Mexico guide.

One ledger vs four dashboards: what can each actually answer?

Single-rail reconciliation is the default everyone falls into because each gateway hands it to you for free. Stripe’s tools reconcile what Stripe settled. Mercado Pago’s settlement report reconciles Mercado Pago. Each one is good at exactly one question: did my rail pay out what it said it would? That’s a useful question. It is not the question the business is asking.

None of those dashboards knows about the SPEI transfer that never got tied to an order, or the OXXO voucher that expired unpaid, or the card charge you created that silently never settled. A gap that lives between rails is invisible to every per-rail dashboard, because each one only sees inside its own fence.

Multi-rail reconciliation against one ledger flips it. You cross every rail’s source of truth against your own expected-payment rows. Now the gaps surface: a charge created but never settled shows up as a ledger row that never got matched. A duplicate shows up as two rail records fighting over one row. An expired OXXO voucher shows up as a row stuck in “awaiting payment” past its deadline. Your ledger is the authoritative answer; each gateway report is just an input feeding it.

How should you model your reconciliation ledger?

Everything matches against this table, so build it first. The rule: write one row per expected payment the moment you create the charge — not when the money lands. The whole point is to have a record of what you are owed so you can later confirm what you received.

Core columns: order_id, amount, currency, rail, external_id, status, plus a created_at and a settled_at you fill in on match. Model the states honestly — pending, paid, partial, failed, refunded, and for cash, awaiting_payment until the customer actually pays at the counter. A naive two-state (paid/unpaid) model will lie to you the first time a refund or a partial lands.

Store the gross amount, and track the fee per rail separately, so you can reconcile net vs gross later (more on that below). And external_id is your stable key per rail: Stripe’s PaymentIntent or charge id, the Mercado Pago payment id, the SPEI reference, the OXXO voucher id.

-- Illustrative ledger table — confirm column types against your stack
CREATE TABLE payment_ledger (
  id            BIGSERIAL PRIMARY KEY,
  order_id      TEXT        NOT NULL,
  rail          TEXT        NOT NULL,   -- 'stripe' | 'mercadopago' | 'spei' | 'oxxo'
  external_id   TEXT,                   -- stable key per rail (may be null at creation)
  amount_gross  NUMERIC(14,2) NOT NULL, -- what you are owed, before fees
  fee           NUMERIC(14,2),          -- per-rail commission, filled on settlement
  currency      CHAR(3)     NOT NULL DEFAULT 'MXN',
  status        TEXT        NOT NULL DEFAULT 'pending',
    -- pending | awaiting_payment | paid | partial | failed | refunded
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  settled_at    TIMESTAMPTZ,            -- filled when a rail record matches
  UNIQUE (rail, external_id)            -- dedupe key for idempotent matching
);

Where does each rail’s source of truth actually live?

Each rail exposes a different authoritative record. Get the names exactly right, because “the dashboard” is not an API and won’t reconcile at scale.

For Stripe, you have two: the BalanceTransactions API (you can replay these to reconstruct your balance from first principles — see Stripe balances & settlement time) and the payout reconciliation report, which breaks down each automatic payout that landed in your bank account, grouped by reporting category. The report tells you which transactions made up a given payout — that’s how you tie a bank deposit back to individual charges.

For Mercado Pago, it’s the settlement / movements report (your liquidaciones / movimientos) — the Mercado Pago equivalent of the payout breakdown. I covered pulling that side in the Mercado Pago integration guide.

For SPEI, the source of truth is virtual-CLABE webhooks: a per-customer CLABE (the 18-digit Mexican bank account number) so that when money arrives, you know who paid — this is the Bitso / STP virtual-CLABE pattern. Without a per-customer CLABE, a bare SPEI deposit is just an amount and a date with no order attached, which is the hardest thing to reconcile.

For OXXO, it’s the voucher webhook. Because it’s cash, the voucher sits in awaiting_payment with an expiry until the customer walks into a store, then fires a paid event. I walk through wiring those (and SPEI) in the OXXO/SPEI webhooks guide. Whatever rail you’re pulling, treat every pull as an input to your ledger, never the ledger itself — and re-pulling must be safe, which is the next section.

Match by external_id or amount+date — idempotently, net vs gross

Now the matching engine. Primary match is the stable key: join the rail record to your ledger row by external_id. That’s the cleanest, exact match and it should cover the overwhelming majority.

The fallback is amount + date within a tolerance window, for rails where you don’t get a clean id back — a bare SPEI deposit being the classic case. You match an incoming amount to an open ledger row of the same amount within, say, a 48-hour window. It’s fuzzier, so anything that matches this way is a candidate for human review, not auto-close.

Idempotency is non-negotiable. Re-pulling a report or replaying a webhook must never double-count. You dedupe on the rail’s own record id — the UNIQUE (rail, external_id) constraint above does the structural enforcement, and your matcher should also check before writing. Reconciliation jobs get re-run constantly; the day yours double-counts is the day your numbers become fiction.

And the one that trips up everyone: net vs gross. The payout that hits your bank is gross minus the rail’s commission. If you match a 1,000-peso ledger row against a 970-peso bank deposit and call it a mismatch, your reconciliation will never tie out. Match against gross, then verify the fee separately. Commission rates vary per rail and change over time, so model the fee as data per rail — never hardcode a percentage; confirm current rates against each provider. Same discipline for multi-currency: capture the settlement FX from the rail’s own report, don’t infer it.

# Illustrative matcher — confirm report fields + fee handling against each provider
def reconcile_line(rail_record, ledger):
    # 1) idempotency: never double-count a record we've already settled
    if ledger.already_settled(rail_record.rail, rail_record.external_id):
        return "skip-duplicate"

    # 2) primary match on the stable key
    row = ledger.find_by_external_id(rail_record.rail, rail_record.external_id)

    # 3) fallback: amount + date within tolerance (e.g. a bare SPEI deposit)
    if row is None:
        row = ledger.find_open_by_amount_and_window(
            amount_gross=rail_record.gross,
            window_hours=48,
        )
        if row is None:
            return "needs-review"  # nothing to match — investigate

    # 4) NET vs GROSS: payout is gross minus the rail's commission.
    #    Match on gross; verify the fee (fee is data per rail, never hardcoded).
    expected_net = row.amount_gross - rail_record.fee
    if abs(rail_record.net - expected_net) > AMOUNT_TOLERANCE:
        return "needs-review"  # fee discrepancy or partial

    ledger.settle(row, fee=rail_record.fee, settled_at=rail_record.settled_at)
    return "auto-matched"

Why do settlement-timing offsets break same-day reconciliation?

The single biggest reason naive reconciliation “loses” money that’s actually fine: expecting a payment to land the same day you created the charge. It almost never does, and each rail runs on a different clock.

Cards through Stripe settle on a payout delay — the charge and the payout that includes it are different days, sometimes by several days. SPEI is fast (a transfer typically clears quickly), but the webhook that tells you so still arrives on its own timing. And OXXO is cash, so it’s the extreme case: a voucher can sit in awaiting_payment for days until the customer physically walks into a store, settles slower than cards once paid, and can expire entirely unpaid. If you reconcile same-day, you’ll flag a pile of perfectly healthy OXXO vouchers as “missing money” every single morning.

The honest caveat: as of 2026, exact settlement times differ per account and change over time, so confirm current windows against each provider’s docs — don’t hardcode a “T+N” into your logic. And know your report’s day boundary and timezone. Stripe report windows, for example, are computed over a day boundary; if you don’t account for the timezone, you’ll split one day’s volume across two reports and chase a phantom discrepancy.

Bucket the results: auto-matched, needs-review, customer-service

Matching produces a result for every record; what you do with it is what turns reconciliation from a monthly spreadsheet panic into a real workflow. Three buckets.

Auto-matched: stable-key matches and in-tolerance amount+date matches. These close themselves. No human ever touches them, and this should be the overwhelming majority of your volume.

Needs-review: amount mismatches, fee discrepancies, FX edge cases, a payout that doesn’t tie back to its underlying transactions. An engineer or finance person eyeballs these — there’s signal here, but it’s not a customer problem yet.

Customer-service: a charge with no settlement long past its expected window, an OXXO voucher that expired unpaid, a SPEI deposit you genuinely cannot tie to any order. These need someone to contact the customer.

The point of bucketing is that reconciliation becomes a queue, not a fire drill. And this is the operational moat: the gateway docs reconcile their own rail just fine — the part they all skip is the one-ledger crossing plus the bucketing that turns the result into work someone can actually act on.

FAQ

Can’t I just trust each gateway’s dashboard? No — its single blind spot is the cross-rail gap. The concrete failure mode: a charge you created that never settled is invisible, because the only place that row exists as expected revenue is a ledger of your own.

How do I handle OXXO sitting “awaiting payment” for days? Model the awaiting_payment state and the voucher expiry explicitly. Don’t treat an unpaid voucher as lost revenue until it actually expires, and don’t treat it as received until the paid event fires. Both mistakes corrupt your numbers in opposite directions.

Net or gross — which do I store? Store gross and the per-rail fee, because that’s the only way to verify the fee charged was actually correct. Store net alone and a silent over-charge on commission ties out clean and you never catch it.

Does this replace my contador or my CFDI invoicing? No. This is the engineering reconciliation of what money arrived. The fiscal side — CFDI, the factura global — is separate. The factura global has a 24-hour plazo (regla 2.7.1.21 of the RMF 2026); that’s a standing obligation, not a 2026 change — the deadline dropped from 72 hours to 24 hours back in 2022. Defer the fiscal books to your contador.

What settlement time or commission percentage should I hardcode? None. As of 2026, they vary per account and change over time. Pull them from each provider’s current report and docs, and model them as data, not constants.

Square it once, then keep it square

The one ledger you control is the only thing that answers did we get every peso? across every rail at once. No gateway dashboard can, because none of them can see the others.

Build it once, run it on a schedule, and let the auto-matched bucket carry the load while humans only ever touch the exceptions. Be honest about what this is: the architecture is solid, but settlement times and fees vary per account and change, so confirm them current against each provider — and pair the pattern with your contador for the books. If you want more guides on building payments for Mexico and the rest of the region, they’re over in LATAM payments. And if you’re verifying the webhooks you reconcile against, Stripe webhook signature verification is the companion piece.

This multi-rail plumbing is exactly the kind of thing I ship in production for MX clients.