Payment Reconciliation: Matching Payments to Invoices, Stripe Webhooks, and Dispute Handling

Payment reconciliation is the process of verifying that every payment you received matches an invoice, that your payment processor's records match your database, and that your bank account matches both. When reconciliation breaks — and it will, because webhooks fail, edge cases exist, and payment processors have bugs — you either discover it immediately with automated checks or you discover it months later in an audit.

This guide covers the implementation patterns that keep your payment records accurate.

---

The Reconciliation Problem

What should be true at any point in time:
  Stripe balance = Your DB payments total = Your bank balance

What actually happens:
  1. Webhook fails delivery → DB doesn't record payment → DB < Stripe
  2. Webhook delivered twice → DB records duplicate → DB > Stripe
  3. Refund processed in Stripe → DB not updated → DB > Stripe
  4. Dispute filed → Stripe charges back → DB doesn't reflect → DB > Stripe
  5. Subscription renews → DB marks paid before payment clears → DB > Stripe

Reconciliation is the process of detecting and resolving these discrepancies.

---

Idempotent Webhook Processing

Stripe delivers webhooks at-least-once — which means the same event can arrive multiple times. Your webhook handler must be idempotent: processing the same event N times produces the same result as processing it once.

// lib/webhooks/stripeWebhook.ts
import Stripe from 'stripe';
import { db } from '@/lib/db';

const stripe = new Stripe(process.env.STRIPE_SECRET_KEY!);

// Store processed webhook IDs to prevent double-processing
async function isEventProcessed(eventId: string): Promise<boolean> {
  const result = await db.query(
    'SELECT id FROM webhook_events WHERE stripe_event_id = $1',
    [eventId]
  );
  return result.rows.length > 0;
}

async function markEventProcessed(eventId: string, type: string): Promise<void> {
  await db.query(
    `INSERT INTO webhook_events (stripe_event_id, event_type, processed_at)
     VALUES ($1, $2, NOW())
     ON CONFLICT (stripe_event_id) DO NOTHING`,
    [eventId, type]
  );
}

// Webhook endpoint
export async function handleStripeWebhook(
  rawBody: Buffer,
  signature: string
): Promise<void> {
  // Verify webhook signature — never trust unverified webhooks
  let event: Stripe.Event;
  try {
    event = stripe.webhooks.constructEvent(
      rawBody,
      signature,
      process.env.STRIPE_WEBHOOK_SECRET!
    );
  } catch (err) {
    throw new Error(`Webhook signature verification failed: ${err}`);
  }

  // Idempotency check
  if (await isEventProcessed(event.id)) {
    console.info(`Webhook ${event.id} already processed — skipping`);
    return;
  }

  // Process event
  await processStripeEvent(event);

  // Mark as processed (after successful processing)
  await markEventProcessed(event.id, event.type);
}

async function processStripeEvent(event: Stripe.Event): Promise<void> {
  switch (event.type) {
    case 'invoice.payment_succeeded':
      await handlePaymentSucceeded(event.data.object as Stripe.Invoice);
      break;
    case 'invoice.payment_failed':
      await handlePaymentFailed(event.data.object as Stripe.Invoice);
      break;
    case 'charge.dispute.created':
      await handleDisputeCreated(event.data.object as Stripe.Dispute);
      break;
    case 'charge.refunded':
      await handleRefund(event.data.object as Stripe.Charge);
      break;
    default:
      console.info(`Unhandled event type: ${event.type}`);
  }
}

---

Database Schema for Payment Records

-- Core payment ledger tables
CREATE TABLE invoices (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    stripe_invoice_id TEXT UNIQUE,
    amount_cents    INTEGER NOT NULL,
    currency        TEXT NOT NULL DEFAULT 'usd',
    status          TEXT NOT NULL CHECK (status IN ('draft', 'open', 'paid', 'void', 'uncollectible')),
    due_date        DATE,
    paid_at         TIMESTAMPTZ,
    period_start    TIMESTAMPTZ,
    period_end      TIMESTAMPTZ,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE payments (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    invoice_id      UUID REFERENCES invoices(id),
    stripe_payment_intent_id TEXT UNIQUE,
    stripe_charge_id TEXT UNIQUE,
    amount_cents    INTEGER NOT NULL,
    currency        TEXT NOT NULL DEFAULT 'usd',
    status          TEXT NOT NULL CHECK (status IN ('pending', 'succeeded', 'failed', 'refunded', 'disputed')),
    payment_method  TEXT,  -- 'card', 'ach', 'wire'
    processed_at    TIMESTAMPTZ,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE refunds (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    payment_id      UUID NOT NULL REFERENCES payments(id),
    stripe_refund_id TEXT UNIQUE,
    amount_cents    INTEGER NOT NULL,
    reason          TEXT,
    status          TEXT NOT NULL CHECK (status IN ('pending', 'succeeded', 'failed')),
    processed_at    TIMESTAMPTZ,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE disputes (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    payment_id      UUID NOT NULL REFERENCES payments(id),
    stripe_dispute_id TEXT UNIQUE,
    amount_cents    INTEGER NOT NULL,
    reason          TEXT,
    status          TEXT NOT NULL CHECK (status IN ('warning_needs_response', 'needs_response', 'under_review', 'won', 'lost')),
    due_by          TIMESTAMPTZ,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

-- Idempotency table for webhooks
CREATE TABLE webhook_events (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    stripe_event_id TEXT UNIQUE NOT NULL,
    event_type      TEXT NOT NULL,
    processed_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_payments_tenant ON payments(tenant_id, created_at DESC);
CREATE INDEX idx_payments_status ON payments(status) WHERE status != 'succeeded';
CREATE INDEX idx_disputes_status ON disputes(status) WHERE status NOT IN ('won', 'lost');

---

Automated Reconciliation Check

Run this daily to detect discrepancies between Stripe and your database:

# scripts/reconcile_stripe.py
import stripe
import psycopg2
from datetime import datetime, timedelta
from dataclasses import dataclass

stripe.api_key = os.environ['STRIPE_SECRET_KEY']

@dataclass
class ReconciliationResult:
    period: str
    stripe_total_cents: int
    db_total_cents: int
    discrepancy_cents: int
    missing_in_db: list[str]    # Stripe payment intents not in DB
    missing_in_stripe: list[str]  # DB payments with no Stripe record

def reconcile_period(start_date: datetime, end_date: datetime) -> ReconciliationResult:
    # Fetch all succeeded payments from Stripe for the period
    stripe_payments = {}
    for payment_intent in stripe.PaymentIntent.list(
        created={'gte': int(start_date.timestamp()), 'lte': int(end_date.timestamp())},
        limit=100,
    ).auto_paging_iter():
        if payment_intent.status == 'succeeded':
            stripe_payments[payment_intent.id] = payment_intent.amount

    # Fetch all succeeded payments from DB for the period
    with psycopg2.connect(os.environ['DATABASE_URL']) as conn:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT stripe_payment_intent_id, amount_cents
                FROM payments
                WHERE status = 'succeeded'
                  AND processed_at BETWEEN %s AND %s
            """, (start_date, end_date))
            db_payments = {row[0]: row[1] for row in cur.fetchall()}

    stripe_ids = set(stripe_payments.keys())
    db_ids = set(db_payments.keys())

    missing_in_db = list(stripe_ids - db_ids)
    missing_in_stripe = list(db_ids - stripe_ids)

    return ReconciliationResult(
        period=f"{start_date.date()} to {end_date.date()}",
        stripe_total_cents=sum(stripe_payments.values()),
        db_total_cents=sum(db_payments.values()),
        discrepancy_cents=sum(stripe_payments.values()) - sum(db_payments.values()),
        missing_in_db=missing_in_db,
        missing_in_stripe=missing_in_stripe,
    )

def run_daily_reconciliation():
    yesterday = datetime.utcnow() - timedelta(days=1)
    start = yesterday.replace(hour=0, minute=0, second=0)
    end = yesterday.replace(hour=23, minute=59, second=59)

    result = reconcile_period(start, end)

    if result.discrepancy_cents != 0 or result.missing_in_db or result.missing_in_stripe:
        alert_finance_team(result)

    # Save to reconciliation log
    save_reconciliation_result(result)
    print(f"Reconciliation complete: ${result.discrepancy_cents / 100:.2f} discrepancy")

if __name__ == '__main__':
    run_daily_reconciliation()

---

Handling Disputes

Disputes (chargebacks) require immediate action — Stripe's deadline for evidence submission is 7–21 days depending on card network.

async function handleDisputeCreated(dispute: Stripe.Dispute): Promise<void> {
  // Record in DB
  await db.query(
    `INSERT INTO disputes (payment_id, stripe_dispute_id, amount_cents, reason, status, due_by)
     VALUES (
       (SELECT id FROM payments WHERE stripe_charge_id = $1),
       $2, $3, $4, $5, $6
     )`,
    [
      dispute.charge,
      dispute.id,
      dispute.amount,
      dispute.reason,
      dispute.status,
      new Date(dispute.evidence_details.due_by! * 1000),
    ]
  );

  // Alert finance team immediately
  await sendSlackAlert({
    channel: '#billing-alerts',
    text: `⚠️ New dispute: ${dispute.reason} | Amount: $${dispute.amount / 100} | Due: ${new Date(dispute.evidence_details.due_by! * 1000).toLocaleDateString()}`,
    urgent: true,
  });

  // Auto-gather evidence for the disputed payment
  const evidence = await gatherDisputeEvidence(dispute.charge as string);
  if (evidence.isAutoSubmittable) {
    await stripe.disputes.update(dispute.id, { evidence });
  }
}

async function gatherDisputeEvidence(chargeId: string) {
  // Retrieve order details, shipping records, customer communications
  // For digital services: service delivery logs, login records
  const charge = await stripe.charges.retrieve(chargeId);
  const payment = await db.getPaymentByChargeId(chargeId);

  return {
    customer_name: charge.billing_details.name,
    customer_email_address: charge.billing_details.email,
    service_date: payment.processedAt.toISOString().split('T')[0],
    // Add service delivery evidence specific to your product
  };
}

---

Reconciliation Dashboard Query

-- Daily reconciliation summary for finance dashboard
SELECT
    DATE(processed_at) AS date,
    COUNT(*) AS payment_count,
    SUM(amount_cents) FILTER (WHERE status = 'succeeded') / 100.0 AS gross_revenue,
    SUM(amount_cents) FILTER (WHERE status = 'refunded') / 100.0 AS refunded,
    COUNT(*) FILTER (WHERE status = 'disputed') AS disputes,
    SUM(amount_cents) FILTER (WHERE status = 'succeeded') / 100.0
      - SUM(amount_cents) FILTER (WHERE status = 'refunded') / 100.0 AS net_revenue
FROM payments
WHERE processed_at > NOW() - INTERVAL '30 days'
GROUP BY DATE(processed_at)
ORDER BY date DESC;

---

Working With Viprasol

We implement payment infrastructure for SaaS and marketplace products — Stripe integration with idempotent webhook handling, reconciliation pipelines, dispute management workflows, and the financial reporting needed for accounting and investor reporting.

→ Talk to our fintech team about payment infrastructure.

---

See Also

• Stripe Connect Marketplace Payments — multi-party payment flows
• Webhook Design Patterns — idempotency and retry patterns
• Fintech Compliance Software — regulatory requirements for payment systems
• SaaS Metrics and KPIs — MRR, churn, and revenue metrics
• Web Development Services — payment system development