Reconciling Duplicate Payments After an Incident

Reversal is not the end of a duplicate-charge incident; proving the reversal was complete and correct is. That proof comes from a three-way reconciliation between your ledger, the payment processor’s records and the audit table your remediation wrote. This runbook is the closing step of incident response for duplicate processing.

Prerequisites. A frozen incident table listing the charges to reverse, a reversal_audit table populated by the remediation job, and read access to the processor’s API or settlement export.

---

Step 1 — Load the processor’s records

Never reconcile against your own data alone. The incident happened because your view and the processor’s view diverged.

# Stripe: export every charge in the incident window with its metadata.
stripe charges list \
  --created "gte=$(date -d '2026-08-01 09:12:00Z' +%s)" \
  --created "lt=$(date -d '2026-08-01 14:41:00Z' +%s)" \
  --limit 100 --expand 'data.refunds' \
  | jq -r '.data[] | [.id, .metadata.idempotency_key, .amount, .currency,
                      .status, .captured, (.refunds.data | length),
                      .created] | @csv' \
  > processor_window.csv

CREATE TABLE incident_2026_08_01_processor (
    processor_charge_id text PRIMARY KEY,
    idempotency_key     text,
    amount_minor        bigint      NOT NULL,
    currency            text        NOT NULL,
    status              text        NOT NULL,
    captured            boolean     NOT NULL,
    refund_count        int         NOT NULL,
    created_at          timestamptz NOT NULL
);
\copy incident_2026_08_01_processor FROM 'processor_window.csv' WITH (FORMAT csv)

Use the settlement export rather than the API where one exists. The API reflects current state; the settlement file reflects what actually moved money, and for a financial reconciliation that distinction matters.

---

Step 2 — Join into one view

CREATE VIEW incident_2026_08_01_recon AS
SELECT
    coalesce(l.processor_charge_id, p.processor_charge_id) AS charge_id,
    coalesce(l.idempotency_key, p.idempotency_key)         AS idempotency_key,
    l.customer_id,
    l.amount_minor        AS ledger_amount,
    p.amount_minor        AS processor_amount,
    p.status              AS processor_status,
    p.captured,
    p.refund_count,
    a.action              AS audit_action,
    a.processor_ref       AS audit_processor_ref,
    CASE
      WHEN p.processor_charge_id IS NULL                      THEN 'phantom_ledger'
      WHEN l.processor_charge_id IS NULL                      THEN 'unrecorded_charge'
      WHEN l.amount_minor <> p.amount_minor                   THEN 'amount_mismatch'
      WHEN d.reverse_id IS NOT NULL AND a.action IS NULL      THEN 'not_yet_reversed'
      WHEN a.action IN ('void','refund') AND p.refund_count = 0
           AND p.captured                                     THEN 'claimed_unconfirmed'
      ELSE 'reconciled'
    END AS bucket
  FROM ledger_charges l
  FULL OUTER JOIN incident_2026_08_01_processor p
    ON p.processor_charge_id = l.processor_charge_id
  LEFT JOIN incident_2026_08_01_duplicates d
    ON d.reverse_id = l.id
  LEFT JOIN reversal_audit a
    ON a.charge_id = l.id AND a.run_id = 'incident-2026-08-01'
 WHERE coalesce(l.created_at, p.created_at)
       BETWEEN TIMESTAMPTZ '2026-08-01 09:12:00Z' AND TIMESTAMPTZ '2026-08-01 14:41:00Z';

FULL OUTER JOIN is essential. An inner join silently drops the unrecorded_charge bucket — charges that exist at the processor but never made it into your ledger — which is precisely the population you most need to find.

---

Step 3 — Classify

SELECT bucket, count(*), sum(coalesce(processor_amount, ledger_amount))/100.0 AS value
  FROM incident_2026_08_01_recon
 GROUP BY bucket ORDER BY count(*) DESC;

unrecorded_charge deserves its severity. It is invisible to every query that starts from your own database, it means a customer was charged for something you have no record of, and it is the bucket most likely to become a regulatory conversation.

---

Step 4 — Resolve each bucket

RUN = "incident-2026-08-01"

def resolve_not_yet_reversed(conn) -> None:
    rows = conn.execute("""
        SELECT charge_id, idempotency_key FROM incident_2026_08_01_recon
         WHERE bucket = 'not_yet_reversed'
    """).fetchall()
    for row in rows:
        # Same derived key as the original run: re-running is a no-op at the
        # processor if the reversal actually did land and we simply missed it.
        key = f"{RUN}:reverse:{row.charge_id}"
        result = processor.refunds.create(charge=row.charge_id, reason="duplicate",
                                          idempotency_key=key)
        audit(row, action="refund", reason="reconciliation_catchup",
              processor_ref=result.id)

def resolve_unrecorded(conn) -> None:
    rows = conn.execute("""
        SELECT charge_id, processor_amount, idempotency_key
          FROM incident_2026_08_01_recon WHERE bucket = 'unrecorded_charge'
    """).fetchall()
    for row in rows:
        # Backfill FIRST so the charge is visible, then classify it like any other.
        conn.execute("""INSERT INTO ledger_charges
                          (processor_charge_id, amount_minor, idempotency_key,
                           source, created_at)
                        VALUES (%s, %s, %s, 'reconciliation_backfill', now())
                        ON CONFLICT (processor_charge_id) DO NOTHING""",
                     (row.charge_id, row.processor_amount, row.idempotency_key))
        audit(row, action="backfilled", reason="present_at_processor_only")

Every resolution writes an audit row. A reconciliation that fixes data without recording what it fixed has replaced one unexplained state with another.

---

Step 5 — Prove closure and freeze the evidence

-- The closure check. Commit this query to the repository; it must be re-runnable
-- months later when someone asks whether the incident was really resolved.
SELECT bucket, count(*)
  FROM incident_2026_08_01_recon
 WHERE bucket <> 'reconciled'
 GROUP BY bucket;
-- expect: zero rows

-- Value closure: what left the business, and what came back.
SELECT
  sum(processor_amount) FILTER (WHERE bucket = 'reconciled')            / 100.0 AS total_charged,
  sum(a.amount_minor)   FILTER (WHERE a.action IN ('void','refund'))    / 100.0 AS total_returned,
  count(DISTINCT r.customer_id) FILTER (WHERE a.action IS NOT NULL)              AS customers_made_whole
  FROM incident_2026_08_01_recon r
  LEFT JOIN reversal_audit a ON a.charge_id = r.charge_id AND a.run_id = 'incident-2026-08-01';

# Freeze the evidence: materialise the view, stamp it, and mark it for long retention.
psql <<'SQL'
CREATE TABLE incident_2026_08_01_recon_final AS
  SELECT *, now() AS frozen_at FROM incident_2026_08_01_recon;
COMMENT ON TABLE incident_2026_08_01_recon_final IS
  'Incident 2026-08-01 reconciliation. RETENTION: 7 years. Do not drop.';
SQL

The incident_ prefix and the retention comment are not decoration — they are what stop a routine cleanup of analysis tables from deleting the only proof that the incident was resolved.

---

Failure scenarios and debugging

---

SRE / observability checklist

1. reconciliation_bucket_count{bucket} — Gauge per bucket, exported while the incident is open. Closure is every non-reconciled bucket at zero.
2. unrecorded_charge_count — The bucket that matters most. Any non-zero value is a customer charged for something invisible to your systems.
3. reversal_audit_unmatched_total — Counter of audit rows with no corresponding processor refund. Non-zero means a claimed reversal did not land.
4. reconciliation_value_delta — Gauge of total_charged - total_returned for the duplicate set. Should reach exactly zero.
5. incident_table_present — Boolean gauge from a scheduled check, for the full retention period. Catches accidental cleanup years later.
6. run_id on every audit and reconciliation row — So a second remediation pass is distinguishable from the first, and neither can be confused for the other.

---

Related

• Incident Response for Duplicate Processing — the parent page covering containment, scoping and reversal.
• Writing a Duplicate Charge Runbook — the on-call document that triggers this reconciliation.
• Archiving Expired Idempotency Keys for Audit — the key archive that makes reconstructing an old window possible at all.
• Monitoring Idempotency Metrics & Dashboards — the signals that shorten detection so the window needing reconciliation stays small.