Your database is not the truth: building payment reconciliation in Go and Python

In the last article I built a payment endpoint that survives retries. Same key, same operation, no double charges — the API is correct. This one starts one layer out, because the API being correct doesn't mean the system is correct.

Here's the gap. My server charged the card. The rail — Stripe, the card network, whichever — received the charge. Then the rail tried to tell me it succeeded, via a webhook. And the webhook never arrived. My database still says the charge is pending. The rail's records say it succeeded and the money moved days ago. Both systems are internally consistent. Both are wrong about each other. That's drift, and it's what reconciliation exists to catch.

This article builds a reconciliation loop from scratch, in Go and in Python. Same shape as the last one: naive version, break it on purpose, fix it in stages. By the end you'll have a system that pulls the rail's record of truth on a schedule, diffs it against your own state, and knows the difference between a mismatch it can repair automatically and one that needs a human.

Then I'll do something the last article didn't: measure it. There's a runnable harness at the end that injects lost, duplicated, and reordered webhooks at a known rate and counts exactly how much drift each version of the ingest leaves behind. The headline number surprised me, and it's the reason this article exists in the shape it does.

What is reconciliation in a payment system?#

Reconciliation is the process of comparing two independent records of the same thing and finding where they disagree. In payments, the two records are: your database, which tracks what your system thinks happened, and the rail's record, which tracks what actually moved money. When they agree, everything is fine. When they don't, you have drift, and every unhandled drift is either a customer you charged and didn't credit, or money the rail says you have that your database doesn't know about.

Why isn't my database the source of truth?#

Because my database only knows what my server was told, and my server is only told through channels that can fail.

Here is the sequence for a single successful charge, in real production:

1. My server sends the charge request to the rail.
2. The rail charges the card. Money moves.
3. The rail returns a synchronous response — sometimes.
4. The rail sends a webhook confirming the final state — sometimes.
5. My server writes the confirmed state to my database.

Steps 3 and 4 both use the network. Networks drop, retry, reorder. My server might get the synchronous response but never the webhook. Or it might get the webhook but never the synchronous response. Or it might get the webhook three times. Or it might get the webhook for a charge that was reversed thirty seconds later.

In every one of those cases, my database ends up holding a story that doesn't match reality. And I have no way to notice, from inside my own system, that I'm wrong — because everything I can see says everything is fine.

What happens when a webhook is lost, duplicated, or arrives out of order?#

Three concrete failure modes. Each one produces a specific kind of drift that reconciliation has to catch.

Lost webhook. The rail sends "charge succeeded," it never arrives, my database stays in pending. Reality: money moved. My records: money hasn't moved. If I don't reconcile, I never fulfill the order the customer paid for. This is the most common drift and the most damaging one.

Duplicated webhook. The rail retries a webhook it wasn't sure I received. I get "charge succeeded" twice for the same charge. Without deduplication, I might record two credits, or two fulfillment events, or fire two "payment received" emails. Same event, different consequences depending on where the duplicate lands in my code.

Out-of-order webhooks. For a single charge, the rail sends "succeeded" and then "refunded" a few seconds later. Both webhooks are in flight. They arrive in reverse order. If I process the refund first — noticing the charge doesn't exist yet — I might reject it as invalid, and then apply the succeeded event afterward, ending up with a charge in my database that has no record of its refund.

Each of these leaves my database in a state that's internally sensible but factually wrong.

Version 1: naive event ingest#

Here's the first version most people write. It processes each webhook as it arrives:

func (s *Server) HandleWebhook(w http.ResponseWriter, r *http.Request) {
    var event RailEvent
    if err := json.NewDecoder(r.Body).Decode(&event); err != nil {
        http.Error(w, "bad request", http.StatusBadRequest)
        return
    }
 
    switch event.Type {
    case "charge.succeeded":
        s.db.MarkChargeSucceeded(event.ChargeID)
    case "charge.refunded":
        s.db.MarkChargeRefunded(event.ChargeID)
    }
 
    w.WriteHeader(http.StatusOK)
}

The same thing in Python, with the same hole in it:

@app.post("/webhooks/rail")
async def handle_webhook(request: Request):
    event = RailEvent(**await request.json())
 
    if event.type == "charge.succeeded":
        db.mark_charge_succeeded(event.charge_id)
    elif event.type == "charge.refunded":
        db.mark_charge_refunded(event.charge_id)
 
    return Response(status_code=200)

This is broken for every failure mode above. A duplicate charge.succeeded runs the success path twice. An out-of-order refunded arrives for a charge that doesn't exist yet, and either errors or silently no-ops. A lost webhook leaves the charge in pending forever, and this endpoint has no way to know.

The runnable version of this failure is in examples/payment-reconciliation/01-naive-ingest in the repo — clone it and watch it happen before reading the fix.

The fix has two parts, and they're separate concerns: make the ingest itself idempotent and order-tolerant, and add a reconciliation loop that catches what the ingest missed. Most teams build the first and assume it covers the second. The measurement further down is what convinced me it doesn't come close.

Version 2: idempotent event ingest#

Every webhook the rail sends has a unique event ID. I use it exactly the way I used the idempotency key in the last article — as a claim, with the database's unique constraint doing the deduplication. Same primitive, different name, one layer out:

CREATE TABLE rail_events (
    event_id     TEXT PRIMARY KEY,
    event_type   TEXT NOT NULL,
    charge_id    TEXT NOT NULL,
    payload      JSONB NOT NULL,
    received_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    processed_at TIMESTAMPTZ
);

The claim is the whole trick. INSERT ... ON CONFLICT DO NOTHING either wins or it doesn't, atomically, and only the winner does the work:

func (s *Server) HandleWebhook(w http.ResponseWriter, r *http.Request) {
    var event RailEvent
    if err := json.NewDecoder(r.Body).Decode(&event); err != nil {
        http.Error(w, "bad request", http.StatusBadRequest)
        return
    }
 
    // Claim the event ID. Duplicates fail the insert and we return 200
    // so the rail stops retrying.
    claimed, err := s.db.ClaimEvent(r.Context(), event)
    if err != nil {
        http.Error(w, "internal error", http.StatusInternalServerError)
        return
    }
    if !claimed {
        w.WriteHeader(http.StatusOK) // already processed
        return
    }
 
    // Apply the event to the charge state, tolerating out-of-order arrival.
    if err := s.applyEvent(r.Context(), event); err != nil {
        // Don't 500 to the rail — that triggers infinite retries.
        // Log and let reconciliation catch it later.
        s.log.Error("apply failed", "event", event.ID, "err", err)
    }
 
    w.WriteHeader(http.StatusOK)
}

@app.post("/webhooks/rail")
async def handle_webhook(request: Request):
    event = RailEvent(**await request.json())
 
    # Claim the event ID; duplicates lose the race and return 200.
    if not await db.claim_event(event):
        return Response(status_code=200)  # already processed
 
    try:
        await apply_event(event)
    except Exception:
        # Never 500 to the rail — that triggers infinite retries.
        # Log it and let reconciliation catch it later.
        log.exception("apply failed", extra={"event_id": event.id})
 
    return Response(status_code=200)

The out-of-order tolerance lives in applyEvent. Instead of "mark succeeded" and "mark refunded" as independent writes, I merge each event into a charge-state record that knows how to accept events in any order:

func (s *Server) applyEvent(ctx context.Context, e RailEvent) error {
    return s.db.WithTx(ctx, func(tx *sql.Tx) error {
        charge, err := s.db.LoadChargeForUpdate(tx, e.ChargeID)
        if errors.Is(err, sql.ErrNoRows) {
            // Charge doesn't exist yet — create a shell we can fill in.
            charge = &Charge{ID: e.ChargeID, State: "unknown"}
            s.db.InsertCharge(tx, charge)
        } else if err != nil {
            return err
        }
 
        switch e.Type {
        case "charge.succeeded":
            // Only advance forward; never overwrite a refunded state.
            if charge.State == "unknown" || charge.State == "pending" {
                charge.State = "succeeded"
            }
        case "charge.refunded":
            // Refund is terminal — accept regardless of prior state.
            charge.State = "refunded"
        }
        charge.LastEventAt = e.CreatedAt
        return s.db.SaveCharge(tx, charge)
    })
}

async def apply_event(e: RailEvent) -> None:
    async with db.transaction() as tx:
        charge = await tx.load_charge_for_update(e.charge_id)
        if charge is None:
            # Charge doesn't exist yet — create a shell we can fill in.
            charge = Charge(id=e.charge_id, state="unknown")
            await tx.insert_charge(charge)
 
        if e.type == "charge.succeeded":
            # Only advance forward; never overwrite a refunded state.
            if charge.state in ("unknown", "pending"):
                charge.state = "succeeded"
        elif e.type == "charge.refunded":
            # Refund is terminal — accept regardless of prior state.
            charge.state = "refunded"
 
        charge.last_event_at = e.created_at
        await tx.save_charge(charge)

Two things worth naming here. The state machine has a terminal state (refunded) that can be reached from anywhere, and a forward-only state (succeeded) that can be overwritten by later events but doesn't overwrite them. That's how out-of-order arrival stops being a bug. And I always respond 200 to the rail after claiming the event, even if applying it failed — because a non-200 makes the rail retry forever, and I have a better mechanism for handling failure: the reconciliation loop.

Version 3: the reconciliation loop#

Idempotent ingest fixes duplicates and reordering. It does not fix the case where a webhook never arrives. For that, I need a job that periodically asks the rail: "here's what I think happened in the last N hours — what actually happened?" and compares.

The shape:

func (s *Server) ReconcileWindow(ctx context.Context, from, to time.Time) (ReconcileReport, error) {
    // 1. Pull the rail's record of truth for this window.
    railCharges, err := s.rail.ListCharges(ctx, from, to)
    if err != nil {
        return ReconcileReport{}, err
    }
 
    // 2. Pull my own record for the same window.
    myCharges, err := s.db.ListCharges(ctx, from, to)
    if err != nil {
        return ReconcileReport{}, err
    }
 
    // 3. Diff them.
    report := diff(railCharges, myCharges)
 
    // 4. Act on each category of discrepancy.
    return s.applyReconcileActions(ctx, report)
}

async def reconcile_window(self, start: datetime, end: datetime) -> ReconcileReport:
    # 1. The rail's record of truth for this window.
    rail_charges = await self.rail.list_charges(start, end)
    # 2. My own record for the same window.
    my_charges = await self.db.list_charges(start, end)
    # 3. Diff them.
    report = diff(rail_charges, my_charges)
    # 4. Act on each category of discrepancy.
    return await self.apply_reconcile_actions(report)

The interesting work is in diff. Every reconciliation ever written boils down to sorting each record into one of four buckets:

type ReconcileReport struct {
    Matched    []ChargePair // rail and DB agree
    OnlyInRail []RailCharge // rail knows about it, DB doesn't
    OnlyInDB   []DBCharge   // DB knows about it, rail doesn't
    Mismatched []ChargePair // both know about it, but disagree on state
}

Each bucket means something different:

• Matched is the boring, good case — most of your volume lives here and you do nothing.
• Only in rail is almost always a lost webhook. The rail confirms money moved, my database has no idea. This is repairable automatically: I ingest the missing event as if the webhook had arrived.
• Only in DB is rarer and weirder. Usually it means I recorded an intent to charge but the rail never actually processed it (a network failure right at the boundary). Sometimes it means my system created a phantom row it shouldn't have. This one usually needs a human to look at it.
• Mismatched is the most serious. Both systems know about the charge but disagree on its state — I say succeeded, the rail says refunded, or vice versa. Never auto-repair mismatches. Alert.

func (s *Server) applyReconcileActions(ctx context.Context, r ReconcileReport) (ReconcileReport, error) {
    for _, c := range r.OnlyInRail {
        // Synthesize the webhook we never received, feed it through the same
        // ingest path so idempotency still holds if it later arrives for real.
        synthetic := RailEvent{
            ID:        "recon-" + c.ID,
            Type:      c.TerminalEventType(),
            ChargeID:  c.ID,
            CreatedAt: c.UpdatedAt,
        }
        _ = s.applyEvent(ctx, synthetic)
    }
    for _, m := range r.Mismatched {
        s.alerts.Fire(ctx, "mismatch", m)
    }
    for _, o := range r.OnlyInDB {
        s.alerts.Fire(ctx, "orphan_in_db", o)
    }
    return r, nil
}

async def apply_reconcile_actions(self, r: ReconcileReport) -> ReconcileReport:
    for c in r.only_in_rail:
        # Synthesize the webhook we never received and feed it through the
        # same ingest path, so idempotency holds if the real one shows up.
        await apply_event(RailEvent(
            id=f"recon-{c.id}",
            type=c.terminal_event_type(),
            charge_id=c.id,
            created_at=c.updated_at,
        ))
    for m in r.mismatched:
        await self.alerts.fire("mismatch", m)
    for o in r.only_in_db:
        await self.alerts.fire("orphan_in_db", o)
    return r

Two details that matter more than they look. The synthetic event ID is prefixed (recon-) so it can't collide with a real event ID from the rail — if the real webhook eventually shows up, it gets its own separate claim and the state machine handles the no-op gracefully. And I feed synthetic events through the same applyEvent function, not a shortcut path — because the state machine's logic applies just as much to repaired events as to real ones.

How much drift does each version actually leave?#

Everything above is the standard advice, and I'd written it three times before I thought to measure it. So I built a small harness: one rail, one unreliable webhook channel, and the three ingest strategies from this article, all fed the identical delivery transcript so any difference in the results comes from the ingest logic and nothing else.

It's a runnable repo, not a paragraph of prose — clone examples/payment-reconciliation and run the three programs in order:

cd 01-naive-ingest        && go run .   # or: python3 sim.py
cd ../02-idempotent-ingest && go run .   # or: python3 sim.py
cd ../03-reconciliation    && go run .   # or: python3 sim.py

Three harms are measured separately, because each maps to one of the three failure modes:

• Stuck — the rail moved money, my database still says pending. Caused by loss.
• 2x credit — the side effect fired more than once for one charge. Caused by duplicates.
• Wrong state — both sides terminal, and I disagree with the rail. Caused by reordering, and by lost refunds.

The results#

Reconciliation auto-repaired 453 charges and raised 56 alerts for humans.

Read the first column again. Idempotent ingest left exactly as many charges stuck as the naive version did — 4.53%, the same number to the basis point. Deduplication and a well-built state machine did outstanding work on the harms they address: duplicate side effects went from 9.25% of charges to zero, and wrong terminal states nearly halved. They did nothing at all for lost webhooks, because there is nothing they can do. You cannot deduplicate a message that never arrived.

That result holds across the whole range of loss rates:

The first two columns are identical at every single loss rate. Idempotency is not a partial mitigation for message loss; it is orthogonal to it.

The last column is the part worth internalising. Reconciliation doesn't drive disagreement to zero — at 5% loss it leaves 0.56%. But every one of those remaining charges is a lost refund event, where my database says succeeded and the rail says refunded: two confident, terminal, conflicting beliefs. That's a mismatch, and Rule 4 says never auto-repair a mismatch. So reconciliation surfaced all 56 as alerts rather than guessing.

That's the actual guarantee, and it's a more useful one than "no drift":

The Python port produces the same pattern from a different random stream — 4.34% stuck under both naive and idempotent ingest, 9.93% duplicate credits eliminated, 434 repaired and 49 alerted. The specific decimals depend on the PRNG; the identity between the naive and idempotent stuck columns does not.

What this is and isn't. It's a deterministic simulation of three named failure modes, not production telemetry. It doesn't model network partitions, rail-side outages, clock skew, or the partial-batch weirdness in the next section. What it does establish is a lower bound: even with a perfectly reliable database, a perfectly correct state machine, and a rail that never lies, message loss alone leaves a few percent of your charges wrong, and no amount of ingest hardening touches that number. Change the rates to match your own webhook delivery stats and rerun it — the shape of the result won't move.

Version 4: reconciling a payout batch#

Everything above assumes each event describes one charge. Payouts break that assumption. A payout is a batch — I ask the rail to send money to 500 different recipients in one call — and the rail processes them independently. Some succeed. Some fail. Some are still pending after the batch is "complete."

The naive version treats a payout as one atomic thing: it either succeeded or it didn't. That's wrong. A batch's state is really a rollup of 500 individual states, and reconciliation has to work at both levels.

type PayoutBatch struct {
    ID        string
    State     string // "processing", "partial", "complete"
    Transfers []Transfer
}
 
type Transfer struct {
    ID          string
    BatchID     string
    RecipientID string
    AmountCents int64
    State       string // "pending", "succeeded", "failed"
    FailReason  string
}

@dataclass
class Transfer:
    id: str
    batch_id: str
    recipient_id: str
    amount_cents: int
    state: str  # "pending" | "succeeded" | "failed"
    fail_reason: str | None = None
 
 
@dataclass
class PayoutBatch:
    id: str
    state: str  # "processing" | "partial" | "complete"
    transfers: list[Transfer]

Reconciling a batch means two passes. First, reconcile each transfer individually — same four-bucket diff as before, one transfer at a time. Then reconcile the batch's rollup state against the sum of its transfers' states. A batch marked complete in my database with three failed transfers underneath it is a drift, even if every individual transfer matches the rail.

func (s *Server) ReconcileBatch(ctx context.Context, batchID string) error {
    batch, _ := s.db.LoadBatch(ctx, batchID)
    railTransfers, _ := s.rail.ListTransfersInBatch(ctx, batchID)
 
    // Per-transfer reconciliation.
    for _, rt := range railTransfers {
        mt := batch.FindTransfer(rt.ID)
        if mt == nil {
            s.repairMissingTransfer(ctx, batch, rt)
        } else if mt.State != rt.State {
            s.alerts.Fire(ctx, "transfer_mismatch", rt.ID)
        }
    }
 
    // Rollup check.
    computed := computeBatchState(batch.Transfers)
    if batch.State != computed {
        s.alerts.Fire(ctx, "batch_state_drift", batchID)
    }
    return nil
}

async def reconcile_batch(self, batch_id: str) -> None:
    batch = await self.db.load_batch(batch_id)
    rail_transfers = await self.rail.list_transfers_in_batch(batch_id)
 
    # Per-transfer reconciliation.
    for rt in rail_transfers:
        mt = batch.find_transfer(rt.id)
        if mt is None:
            await self.repair_missing_transfer(batch, rt)
        elif mt.state != rt.state:
            await self.alerts.fire("transfer_mismatch", rt.id)
 
    # Rollup check.
    if batch.state != compute_batch_state(batch.transfers):
        await self.alerts.fire("batch_state_drift", batch_id)

The partial-failure case is where payouts genuinely differ from single charges: a batch can be correctly in a partial state, meaning some transfers succeeded and some didn't, and that's a real terminal outcome, not a bug. Your reconciliation has to know the difference between "this batch is in a broken state" and "this batch is correctly reporting partial success," and that distinction lives in the state machine, not in the diff.

When shouldn't you reconcile?#

Two cases where reconciliation is the wrong answer.

When the two sides aren't independent. If both records ultimately derive from the same source, you're not reconciling — you're checking your own arithmetic. That's still useful, but it's a different technique (invariant checks, not reconciliation), and it can't detect the failures reconciliation is meant to catch.

When the drift you're looking for is inside a single request. Reconciliation runs periodically and catches drift over time. If the correctness question is "did this single API call do the right thing," you need transactional guarantees inside the request path — the idempotency and staging patterns from the previous article — not a job that runs an hour later. Reconciliation is for systemic drift, not per-request bugs.

Reference checklist#

Hold your own reconciliation system against this:

• Webhooks ingested via a unique-constraint claim on the rail's event ID.
• Webhook handler always returns 200 after claiming, even if applying fails.
• Event application via a state machine that tolerates out-of-order arrival (forward-only states, terminal states).
• Reconciliation runs on a fixed schedule, over defined time windows.
• Diff produces four buckets: matched, only-in-rail, only-in-DB, mismatched.
• Only-in-rail auto-repaired by synthesizing an event through the same ingest path.
• Synthetic events have IDs that can't collide with real events.
• Mismatches never auto-repaired — always alerted for human review.
• For batches: two-level reconciliation, individual items and rollup state.
• Metrics on each bucket over time — a rising only-in-rail count is an early signal of webhook infrastructure problems.
• Reconciliation windows overlap slightly, so events near a boundary aren't missed.
• Alert volume itself is monitored — a reconciliation loop that silently stops alerting looks identical to one with nothing to report.
• You have measured your own webhook loss rate, rather than assuming it's zero.

Closing#

The last article ended by saying failure becomes survivable when every request has a name and every retry lands on a system that remembers what it already did. This one ends one layer out: correctness doesn't stop at the request boundary. Your API can be perfectly idempotent and your system can still be quietly wrong, because the channels that tell you what happened are the same unreliable networks the requests themselves ran over.

I believed that before I measured it. What I didn't expect was how cleanly the numbers separate the two concerns — that hardening the ingest would erase duplicate side effects entirely and move the lost-webhook number by exactly nothing, at every loss rate I tried.

Looking back at both articles together, they're really the same move applied at two layers: an idempotency key is an optimistic path — assume the request will complete, make it safe to repeat. A reconciliation loop is the pessimistic check — assume something got lost, and prove it one way or the other on a schedule. Neither substitutes for the other, and I don't think that pairing is specific to payments. Anywhere a system claims something happened and something else can independently verify it, the same two questions apply: what's the optimistic path here, and what's the pessimistic check that catches it when the optimistic path is wrong?

That's the lens I'll keep applying as this series goes on. Build the request path and you have an API that survives retries. Build the recovery path and you have a system that survives silence — which, in payments, is the failure that actually costs you customers. The alternative is finding out from them.

Think I made a mistake? Open a PR here.