Stripe Webhook → Postgres Ledger: The 50-Line Pattern That Survives an Audit

Every Series B audit asks the same question about your payment data: can you prove this transaction happened, when it happened, and that you recorded it exactly once? The webhook handler that fails this test always looks the same — direct writes to a mutable `payments` table with no duplicate detection, no audit trail, and no way to reconstruct what Stripe actually sent you three months ago.

We have wired Stripe webhooks to Postgres on twelve projects in the last eighteen months. The pattern that survives regulatory review is not the one most tutorials teach. It is built around three principles: immutable ledger entries, idempotent webhook processing, and an audit trail that accountants can actually read. Here is the 50-line implementation we now ship by default.

Why Direct Payment Table Writes Fail Audits

The naive approach writes webhook data directly to a `payments` table, updating rows when payment status changes. This breaks audit compliance in three ways we see repeatedly: no immutable record of what Stripe sent, no protection against duplicate webhook processing, and no way to prove the sequence of events that led to the current state.

Stripe sends webhooks multiple times. A `payment_intent.succeeded` event might arrive twice if your endpoint returns a 500 on the first attempt. If your handler updates the same payment row both times, you have lost the audit trail. Worse, if the second webhook carries slightly different metadata (Stripe sometimes enriches events on retry), you have overwritten evidence without knowing it.

The third failure mode hits during reconciliation. Your CFO needs to prove that a $2,400 payment processed on March 15th matches the invoice, the bank deposit, and the Stripe dashboard. If your payments table shows only the final state, you cannot reconstruct the timeline that auditors expect: webhook received → payment captured → funds transferred → deposit confirmed.

The Ledger Schema That Survives Regulatory Review

An audit-compliant ledger is append-only. Every webhook becomes an immutable ledger entry with the raw Stripe event data preserved exactly as received. Current payment status is derived from the ledger, not stored separately.

CREATE TABLE stripe_ledger (
  id SERIAL PRIMARY KEY,
  stripe_event_id TEXT NOT NULL UNIQUE,
  event_type TEXT NOT NULL,
  stripe_object_id TEXT NOT NULL,
  raw_event JSONB NOT NULL,
  processed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  api_version TEXT NOT NULL,
  livemode BOOLEAN NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_stripe_ledger_object_id ON stripe_ledger (stripe_object_id);
CREATE INDEX idx_stripe_ledger_event_type ON stripe_ledger (event_type);
CREATE INDEX idx_stripe_ledger_processed_at ON stripe_ledger (processed_at);

The `stripe_event_id` enforces idempotency at the database level. The `raw_event` JSONB column preserves the exact webhook payload Stripe sent. The `api_version` and `livemode` fields are audit requirements — you need to prove which Stripe API version processed the event and whether it happened in production or test mode.

Current payment state lives in a separate view that aggregates ledger entries. This separation is what passes audit review — the immutable ledger proves what happened, the derived view shows what it means.

CREATE VIEW current_payments AS
SELECT 
  stripe_object_id as payment_intent_id,
  (raw_event->>'amount')::INTEGER as amount_cents,
  raw_event->>'currency' as currency,
  raw_event->>'status' as status,
  raw_event->'metadata' as metadata,
  MAX(processed_at) as last_updated
FROM stripe_ledger 
WHERE event_type LIKE 'payment_intent.%'
GROUP BY stripe_object_id, raw_event->>'amount', raw_event->>'currency', 
         raw_event->>'status', raw_event->'metadata';

Idempotency Keys and Duplicate Detection

Stripe webhooks carry an `id` field that uniquely identifies each event. This becomes your idempotency key. The Postgres UPSERT pattern handles webhook retries without data corruption — if the same event arrives twice, the second attempt becomes a no-op.

import { NextRequest } from 'next/server';
import Stripe from 'stripe';
import { db } from '@/lib/db';

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

export async function POST(req: NextRequest) {
  const body = await req.text();
  const sig = req.headers.get('stripe-signature')!;
  
  let event: Stripe.Event;
  
  try {
    event = stripe.webhooks.constructEvent(body, sig, process.env.STRIPE_WEBHOOK_SECRET!);
  } catch (err) {
    console.error('Webhook signature verification failed:', err);
    return new Response('Webhook signature verification failed', { status: 400 });
  }

  // Idempotent ledger insert
  try {
    await db.query(`
      INSERT INTO stripe_ledger (
        stripe_event_id, event_type, stripe_object_id, 
        raw_event, api_version, livemode
      ) VALUES ($1, $2, $3, $4, $5, $6)
      ON CONFLICT (stripe_event_id) DO NOTHING
    `, [
      event.id,
      event.type,
      event.data.object.id,
      JSON.stringify(event),
      event.api_version,
      event.livemode
    ]);
    
    return new Response('OK', { status: 200 });
  } catch (err) {
    console.error('Database error:', err);
    return new Response('Internal server error', { status: 500 });
  }
}

The `ON CONFLICT (stripe_event_id) DO NOTHING` clause is the key. If Stripe retries a webhook, the database silently ignores the duplicate. Your application logic never sees it. The webhook returns 200, Stripe marks it as delivered, and your ledger stays clean.

Error Handling and Retry Logic

Webhook error handling is binary: 200 means success, anything else triggers Stripe's retry logic. The status code you return determines whether Stripe will attempt redelivery. Here is when to use each response code and why it matters for audit compliance.

• 200 OK — webhook processed successfully, ledger entry created, do not retry

• 400 Bad Request — webhook signature invalid or malformed payload, do not retry

• 500 Internal Server Error — database connection failed or constraint violation, retry with backoff

• 503 Service Unavailable — system maintenance or temporary overload, retry immediately

The most common mistake is returning 400 for database errors. If your Postgres connection pool is exhausted, that is a temporary condition — return 500 so Stripe retries in fifteen minutes. Only return 400 for permanent failures like signature verification errors or malformed JSON.

We hit this on a Medusa + Stripe project where webhook volume spiked during a flash sale. The connection pool saturated, webhook writes started failing, and the developer returned 400 thinking it would 'fail fast'. Stripe stopped retrying those webhooks. Three days later, the accounting team found $18,000 in payments that never made it to the ledger. The fix was changing one status code.

Transaction Isolation for Multi-Table Updates

Real applications need to update multiple tables when a payment succeeds — customer balance, invoice status, subscription state. The ledger entry and the business logic updates must land together or fail together. Postgres transactions handle this atomicity requirement.

async function processPaymentWebhook(event: Stripe.Event) {
  const client = await db.getClient();
  
  try {
    await client.query('BEGIN');
    
    // Insert ledger entry (idempotent)
    const ledgerResult = await client.query(`
      INSERT INTO stripe_ledger (
        stripe_event_id, event_type, stripe_object_id, 
        raw_event, api_version, livemode
      ) VALUES ($1, $2, $3, $4, $5, $6)
      ON CONFLICT (stripe_event_id) DO NOTHING
      RETURNING id
    `, [event.id, event.type, event.data.object.id, JSON.stringify(event), event.api_version, event.livemode]);
    
    // If this is a duplicate webhook, skip business logic
    if (ledgerResult.rows.length === 0) {
      await client.query('COMMIT');
      return;
    }
    
    // Business logic updates
    if (event.type === 'payment_intent.succeeded') {
      const paymentIntent = event.data.object as Stripe.PaymentIntent;
      
      await client.query(`
        UPDATE invoices SET status = 'paid', paid_at = NOW() 
        WHERE stripe_payment_intent_id = $1
      `, [paymentIntent.id]);
      
      await client.query(`
        UPDATE customers SET account_balance = account_balance + $1 
        WHERE id = $2
      `, [paymentIntent.amount, paymentIntent.metadata.customer_id]);
    }
    
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

The `RETURNING id` clause on the ledger insert tells you whether this is a new webhook or a retry. If the insert returns no rows, the event already exists in the ledger, and you skip the business logic updates. This prevents double-processing without a separate existence check.

Webhook Signature Verification

Most Stripe webhook tutorials mention signature verification but do not explain why it matters for compliance. The webhook signature proves the request came from Stripe, not from an attacker who guessed your endpoint URL. For PCI compliance, you must verify every webhook before processing payment data.

Stripe signs each webhook with a secret key you configure in the dashboard. The signature includes the request timestamp to prevent replay attacks. If verification fails, return 400 immediately — do not log the payload, do not attempt to parse it, do not write anything to your database.

function verifyWebhookSignature(body: string, signature: string, secret: string): Stripe.Event {
  try {
    return stripe.webhooks.constructEvent(body, signature, secret);
  } catch (err) {
    if (err instanceof Stripe.errors.StripeSignatureVerificationError) {
      console.error('Invalid webhook signature');
      throw new Error('Webhook signature verification failed');
    }
    throw err;
  }
}

The Stripe SDK handles timestamp validation automatically. If a webhook is more than five minutes old, `constructEvent` throws a signature error. This prevents replay attacks where someone captures a valid webhook and resends it later.

Monitoring and Alerting

Production webhook monitoring tracks four metrics that predict audit problems before they happen: webhook delivery success rate, processing latency, duplicate detection rate, and ledger gap detection. Here are the alert thresholds we set on every project.

• Webhook success rate below 99.5% over 15 minutes — indicates signature failures or database connectivity issues

• Processing latency above 2 seconds p95 — suggests connection pool exhaustion or slow business logic

• Duplicate webhook rate above 5% over 1 hour — normal retry rate is 1-2%, higher suggests systematic 500 responses

• Ledger gaps detected — missing sequence numbers in Stripe event IDs indicate lost webhooks

The ledger gap check is the most important. Stripe event IDs are sequential within each event type. If you see `payment_intent.succeeded` events with IDs `evt_123`, `evt_125`, and `evt_127`, you are missing `evt_124` and `evt_126`. This usually means webhooks that returned 400 by mistake and will not be retried.

Performance at Scale

At 10,000+ webhooks per day, three optimizations keep the ledger performant: partial indexes on the most common queries, connection pooling sized for webhook burst traffic, and JSONB indexing for metadata searches that accounting teams run during reconciliation.

-- Partial index for successful payment events only
CREATE INDEX idx_successful_payments 
ON stripe_ledger (stripe_object_id, processed_at) 
WHERE event_type = 'payment_intent.succeeded';

-- JSONB index for metadata searches
CREATE INDEX idx_ledger_metadata 
ON stripe_ledger USING GIN ((raw_event->'data'->'object'->'metadata'));

-- Index for audit date range queries
CREATE INDEX idx_ledger_audit_range 
ON stripe_ledger (processed_at, livemode) 
WHERE livemode = true;

Connection pool sizing matters more for webhooks than for typical web traffic. Webhooks arrive in bursts — Stripe might deliver 200 events in thirty seconds after a batch job runs. Size your pool for the burst, not the average. We typically run 20-30 connections for webhook endpoints that handle 1,000+ events per day.

Need this webhook-to-ledger pattern implemented for your audit timeline?Tell us what you are wiring upand we will walk through the integration specifics for your stack.

On every Stripe + Postgres project we now ship this ledger pattern on day one — it has paid for itself on every audit so far. The fifty lines of webhook handling code prevent the three failure modes that break compliance: mutable payment records, duplicate processing, and missing audit trails. Your Series B review will ask for this data. Better to have it ready than to rebuild it under deadline pressure.