When the Webhook Succeeds but Your Database Write Doesn't: Handling Orphaned Payments

The provider says the money moved. Your database has no record of it. Somewhere between "payment settled" and "order created," a process crashed, a connection timed out, or the pod got evicted, and now you are holding real money with nothing to attach it to. The customer paid. Your system behaves as if they did not. This is the worst class of bug in payments, because it is silent, it involves real money, and almost no tutorial prepares you for it.

Every payment provider's documentation says the same reassuring thing: "verify the webhook signature and update your database." That sentence quietly assumes the update cannot fail. In production it fails all the time. The database is briefly down, the row lock times out, a deploy restarts the process mid-request, the JSON has a field your schema rejects. The webhook arrived, the provider considers its job done, and your write never committed.

The uncomfortable truth is that you cannot prevent this with a better try/catch. The failure can happen after you have acknowledged the webhook. The real answer is a system designed so that every payment either gets recorded or gets caught by reconciliation, and reconciliation is the part nobody explains end to end. So I will.

Why "just wrap it in a try/catch" does not save you

Picture the naive handler. It verifies the signature, opens a transaction, inserts the order, commits, and returns 200. Wrap the whole thing in try/catch and on any error return 500 so the provider retries. Surely that is safe?

It is not, and the reason is timing. There is a window between "your write commits" and "the provider receives your 200." If your process dies in that window, the write succeeded but the provider never got the ack, so it retries, and now you insert the order twice. Flip it the other way: your handler returns 200 and then, before the response is flushed, the box is killed. The provider marks the event delivered and never retries, but your write may not have committed. A try/catch cannot see either of these; the failure is outside the block.

Retries do not fix this on their own either. A provider that retries on non-2xx will hammer your endpoint, and if your handler is not idempotent, every retry is another duplicate. So the two properties you actually need are: acknowledging must be cheap and near-unfailable, and processing must be safe to repeat. That is what the outbox pattern gives you, and what a monolithic handler cannot.

Layer 1: idempotency keyed on the provider's ID, not a unique constraint alone

Idempotency means processing the same event twice has the same effect as processing it once. The key is the provider's own identifier for the event or transaction, because that is the thing that stays constant across retries. Stripe sends an event id (evt_...) and every object has a stable ID; Paystack and M-Pesa give you a transaction reference. Store it and check it before doing anything.

CREATE TABLE processed_events (
  provider        text        NOT NULL,
  event_id        text        NOT NULL,
  received_at     timestamptz NOT NULL DEFAULT now(),
  status          text        NOT NULL DEFAULT 'received',
  PRIMARY KEY (provider, event_id)
);

People often reach for "just put a unique constraint on the order's transaction_ref and let the duplicate insert fail." That works only for the exact final row and only if the whole business operation is that single insert. The moment processing touches two tables, sends an email, or calls another service, a unique constraint on one row does not make the operation idempotent; a retry can re-send the email even though the order insert failed. An explicit idempotency record that gates the entire operation is stronger than hoping a constraint on the last write catches everything. My longer argument for this, in the migrations context, is in Your Migration Will Run Twice, and the M-Pesa-specific version is in Your M-Pesa Callback Will Fire Twice.

Layer 2: the outbox so ack and processing are decoupled

The handler's only job is to durably record that the event arrived, then ack. It does not create the order inline. That single insert is small, fast, and unlikely to fail, and if it does fail you return non-2xx and the provider retries safely because of Layer 1.

@app.post("/webhooks/paystack")
async def paystack_webhook(request: Request):
    raw = await request.body()
    if not verify_paystack(raw, request.headers.get("x-paystack-signature"), SECRET):
        return Response(status_code=401)

    event = json.loads(raw)
    event_id = event["data"]["reference"]

    # One small, idempotent write. INSERT ... ON CONFLICT DO NOTHING.
    sql = (
        "INSERT INTO processed_events (provider, event_id, payload) "
        "VALUES ('paystack', :id, :payload) "
        "ON CONFLICT (provider, event_id) DO NOTHING"
    )
    inserted = await db.execute(sql, {"id": event_id, "payload": raw.decode()})
    # Ack immediately whether it was new or a duplicate.
    return Response(status_code=200)

A separate worker then reads unprocessed rows from processed_events and runs the real logic (create the order, credit the wallet, whatever), marking each row done in the same transaction as the business write so the two commit together. If the worker crashes, the row is still received and gets picked up again. Because the operation is idempotent, re-running it is safe. This is the outbox pattern in its smallest honest form. If you run Celery or a similar queue, the worker is a task; the memory-leak caveats I hit running Celery long-term are in Celery's unresolved worker memory leak.

Note what this buys you: the provider gets its fast 200, so it stops retrying, and the risky work happens somewhere with its own retry loop that does not depend on the HTTP request staying alive. The crash window that lost money in the naive design is gone, because a crash now leaves a durable received row instead of nothing.

Layer 3: reconciliation, the part nobody writes down

Layers 1 and 2 shrink the failure window to almost nothing, but "almost" is not "zero." A webhook can be lost entirely (never delivered, misrouted, dropped by a WAF), and then you have money the provider recorded and no event at all. The only thing that catches this is asking the provider, on a schedule, "what did you settle, and does it match what I have?"

Step 1: Pull the provider's ledger on a schedule

Every serious provider exposes a list/transactions API. Run a job every few minutes that fetches settled payments since the last run:

# scheduled every ~5 minutes
provider_payments = paystack.transactions.list(status="success", since=last_cursor)
for p in provider_payments:
    local = db.fetchone(
        "SELECT id FROM orders WHERE transaction_ref = %s", (p["reference"],)
    )
    if local is None:
        flag_orphaned_payment(p)   # provider says paid, we have no record

Step 2: Flag orphans into a queue a human can see

An orphaned payment goes into an orphaned_payments table with the provider's reference, amount, customer, and the time it was detected. This is not an error log line that scrolls away; it is a work queue. The whole point is that these surface within minutes and someone acts on them, rather than being discovered when the monthly numbers do not tie out.

Step 3: Recover the orphan

Recovery is deliberately a human-in-the-loop workflow, because reconstructing an order from a bare payment is a judgement call. The operator opens the flagged payment, matches it to a customer and intended purchase using the provider's metadata (most providers let you attach an order ID at charge time, which makes this trivial, so do attach it), and either creates the missing order through the same idempotent path the worker uses, or refunds if no legitimate intent can be established. Because creation goes through the idempotent path, a late webhook that finally arrives will not double-create.

Layer	Catches	Cost
Idempotency key	Duplicate deliveries and retries	One table, one lookup
Outbox + worker	Crashes during processing, slow business logic	A worker and a queue
Reconciliation	Lost webhooks, everything else	A scheduled job and a human queue

Verification: prove the net actually catches a drop

Do not trust reconciliation you have never seen fire. Test it deliberately: in staging, take a real sandbox payment and drop the webhook on purpose (block the endpoint, or delete the processed_events row before the worker runs). Then run the reconciliation job and confirm the payment appears in orphaned_payments within one cycle.

SELECT provider, reference, amount, detected_at
FROM orphaned_payments
WHERE detected_at > now() - interval '10 minutes';

Expected: the payment you dropped is listed, with the correct amount, detected within one reconciliation interval of when it settled. If it is not there, your reconciliation window or cursor logic has a gap, and you have just learned that in staging instead of in a customer complaint.

What people get wrong

Treating the webhook as the source of truth. The webhook is a notification, not a guarantee. The provider's transactions API is the source of truth. Any design that has no way to reconcile against that API has no floor under it.

Alerting on orphans at month-end. If orphaned payments are only discovered when finance reconciles the monthly statement, a customer has been unhappy for weeks and the trail is cold. The detection job must run on the order of minutes and page or ticket immediately. Minutes, not months, is the entire difference between a shrug and a refund war.

Using Restart=always or endless provider retries as the whole plan. Retries help only if processing is idempotent. Bolting aggressive retries onto a non-idempotent handler manufactures duplicate orders, which is a different flavour of the same money bug. Idempotency first, then retries.

When it is still broken

If orphans keep appearing even with all three layers, check three things. First, are you attaching your own order ID as provider metadata at charge time? Without it, reconciliation cannot always match a payment to an intent, and recovery stays manual forever. Second, is your reconciliation cursor advancing correctly, or is it skipping a window on restart and missing payments settled during the gap? Third, is a WAF or proxy silently dropping a subset of webhooks before they reach your handler, which reconciliation will reveal as a steady trickle of orphans from one source? For building a provider-agnostic layer so this logic is not copy-pasted per gateway, see Build a Payments Port, Not a Daraja Wrapper. Stripe documents its own event-delivery and retry semantics in the webhooks guide, which is worth reading for the exact retry schedule.