My payment script left orders stuck as "pending" forever. Here is the small reconciliation job that fixed it

dev.to2026-11-08 19:080 views

Last week I found 11 orders in my database that had been "pending" for days. The customers had paid. My script had crashed between "charge succeeded" and "mark order paid". Nothing was wrong with the payment provider. The bug was mine: I trusted a single write to happen exactly once.

This post shows the failure and a small reconciliation job that repairs it. It uses only the Python standard library, so you can copy it.

The failure

def checkout(order_id, amount): charge = provider.charge(amount) # 1. money moves  db.execute("UPDATE orders SET status='paid' WHERE id=?", (order_id,)) # 2. we record it

If the process dies, times out or is restarted between step 1 and step 2, the money has moved but the row still says pending. Retrying the whole function can charge the customer twice. Doing nothing leaves the order stuck.

Fix part 1: a stable request fingerprint

Give each logical charge one key that never changes across retries. Stripe documents this as an idempotency key: https://docs.stripe.com/api/idempotent_requests

import hashlib def fingerprint(order_id: str, amount_cents: int) -> str: raw = f"{order_id}:{amount_cents}".encode() return hashlib.sha256(raw).hexdigest()[:32]

Send it with the charge. A retry with the same key returns the original result instead of a new charge.

Fix part 2: a reconciliation job

Run this every few minutes. It looks only at rows that have been pending longer than a safe window, asks the provider what really happened, and then fixes the row.

import sqlite3, time STALE_AFTER = 15 * 60 # seconds  def reconcile(db, provider): cutoff = int(time.time()) - STALE_AFTER rows = db.execute( "SELECT id, fingerprint FROM orders WHERE status='pending' AND created_at < ?", (cutoff,), ).fetchall() for order_id, fp in rows: remote = provider.lookup(fp) # ask the source of truth  if remote is None: db.execute("UPDATE orders SET status='failed' WHERE id=?", (order_id,)) elif remote.paid: db.execute("UPDATE orders SET status='paid' WHERE id=?", (order_id,)) db.commit()

Three rules keep it safe:

Only touch rows older than the window, so you never race a request that is still running.
The provider is the source of truth, not your local guess.
Make the job itself idempotent: running it twice must give the same result.