Payment Processing Low-Level Design: Idempotency, Stripe Integration, and Webhook Handling

A payment processing pipeline accepts customer payment details, communicates with a payment processor (Stripe, Braintree, Adyen), and reliably records the outcome — all while handling partial failures, network timeouts, and duplicate submissions. Core challenges: idempotency (never double-charge), atomicity (payment status and order state must stay in sync), handling async webhook confirmation, and PCI-DSS compliance (never touch raw card data).

Core Data Model

CREATE TYPE payment_status AS ENUM ('initiated','pending','succeeded','failed','refunded','disputed');

CREATE TABLE Payment (
    payment_id        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id          UUID NOT NULL,
    user_id           UUID NOT NULL,
    amount_cents      INT NOT NULL,
    currency          CHAR(3) NOT NULL DEFAULT 'USD',
    status            payment_status NOT NULL DEFAULT 'initiated',
    idempotency_key   TEXT NOT NULL UNIQUE,   -- caller-provided dedup key
    processor         TEXT NOT NULL,          -- 'stripe', 'braintree'
    processor_txn_id  TEXT,                   -- e.g. Stripe PaymentIntent ID
    processor_method  TEXT,                   -- 'card', 'ach', 'apple_pay'
    failure_code      TEXT,
    failure_message   TEXT,
    initiated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    completed_at      TIMESTAMPTZ
);
CREATE INDEX idx_payment_order ON Payment (order_id);
CREATE INDEX idx_payment_user  ON Payment (user_id, initiated_at DESC);

CREATE TABLE Refund (
    refund_id        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    payment_id       UUID NOT NULL REFERENCES Payment(payment_id),
    amount_cents     INT NOT NULL,
    reason           TEXT,
    idempotency_key  TEXT NOT NULL UNIQUE,
    processor_ref_id TEXT,
    status           TEXT NOT NULL DEFAULT 'pending',
    created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Initiating a Payment (Stripe PaymentIntent Flow)

import stripe, uuid
from dataclasses import dataclass

stripe.api_key = "sk_..."

@dataclass
class ChargeRequest:
    order_id: str
    user_id: str
    amount_cents: int
    currency: str
    payment_method_id: str  # Stripe PM token from client (never raw card)
    idempotency_key: str    # caller generates: f"pay-{order_id}-{attempt_num}"

def initiate_payment(conn, req: ChargeRequest) -> dict:
    """
    Idempotent payment initiation.
    If called twice with the same idempotency_key, returns the existing payment.
    """
    with conn.cursor() as cur:
        cur.execute(
            "SELECT payment_id, status, processor_txn_id FROM Payment WHERE idempotency_key = %s",
            (req.idempotency_key,)
        )
        existing = cur.fetchone()

    if existing:
        # Idempotent: return existing record without re-charging
        return {"payment_id": existing[0], "status": existing[1],
                "processor_txn_id": existing[2], "idempotent_replay": True}

    payment_id = str(uuid.uuid4())

    # Step 1: Insert payment record in 'initiated' state BEFORE calling Stripe
    # This ensures we have a record even if Stripe call succeeds but DB write fails
    with conn.cursor() as cur:
        cur.execute("""
            INSERT INTO Payment
            (payment_id, order_id, user_id, amount_cents, currency, idempotency_key, processor)
            VALUES (%s,%s,%s,%s,%s,%s,'stripe')
        """, (payment_id, req.order_id, req.user_id, req.amount_cents,
              req.currency, req.idempotency_key))
    conn.commit()

    # Step 2: Call Stripe with their idempotency key
    try:
        intent = stripe.PaymentIntent.create(
            amount=req.amount_cents,
            currency=req.currency.lower(),
            payment_method=req.payment_method_id,
            confirm=True,
            return_url="https://app.example.com/payment/complete",
            idempotency_key=req.idempotency_key  # Stripe deduplicates too
        )
    except stripe.error.CardError as e:
        _update_payment_failed(conn, payment_id, e.code, e.user_message)
        raise

    # Step 3: Update payment with processor response
    status = "succeeded" if intent.status == "succeeded" else "pending"
    with conn.cursor() as cur:
        cur.execute("""
            UPDATE Payment
            SET status=%s, processor_txn_id=%s, processor_method=%s,
                completed_at=CASE WHEN %s='succeeded' THEN NOW() ELSE NULL END
            WHERE payment_id=%s
        """, (status, intent.id, intent.payment_method, status, payment_id))
    conn.commit()

    return {"payment_id": payment_id, "status": status, "processor_txn_id": intent.id}

def _update_payment_failed(conn, payment_id: str, code: str, message: str):
    with conn.cursor() as cur:
        cur.execute(
            "UPDATE Payment SET status='failed', failure_code=%s, failure_message=%s, completed_at=NOW() WHERE payment_id=%s",
            (code, message, payment_id)
        )
    conn.commit()

Handling Async Webhook Confirmation

def handle_stripe_webhook(conn, event: dict):
    """
    Stripe sends payment_intent.succeeded and payment_intent.payment_failed
    webhooks asynchronously — the synchronous API call may return 'processing'
    for some payment methods (bank transfers, 3D Secure).
    """
    event_type = event["type"]
    intent = event["data"]["object"]
    processor_txn_id = intent["id"]

    with conn.cursor() as cur:
        cur.execute(
            "SELECT payment_id, status FROM Payment WHERE processor_txn_id = %s",
            (processor_txn_id,)
        )
        row = cur.fetchone()

    if not row:
        # Unknown payment — log and return 200 to prevent Stripe from retrying
        return

    payment_id, current_status = row

    if event_type == "payment_intent.succeeded" and current_status != "succeeded":
        with conn.cursor() as cur:
            cur.execute(
                "UPDATE Payment SET status='succeeded', completed_at=NOW() WHERE payment_id=%s",
                (payment_id,)
            )
        conn.commit()
        fulfill_order(conn, payment_id)

    elif event_type == "payment_intent.payment_failed" and current_status not in ("succeeded","refunded"):
        err = intent.get("last_payment_error", {})
        _update_payment_failed(conn, payment_id, err.get("code",""), err.get("message",""))

Issuing a Refund

def refund_payment(conn, payment_id: str, amount_cents: int | None, reason: str, idempotency_key: str) -> dict:
    """
    Partial or full refund. Idempotent via idempotency_key.
    """
    # Check for existing refund with same key
    with conn.cursor() as cur:
        cur.execute("SELECT refund_id, status FROM Refund WHERE idempotency_key = %s", (idempotency_key,))
        existing = cur.fetchone()
    if existing:
        return {"refund_id": existing[0], "status": existing[1], "idempotent_replay": True}

    with conn.cursor() as cur:
        cur.execute("SELECT processor_txn_id, amount_cents, status FROM Payment WHERE payment_id = %s", (payment_id,))
        row = cur.fetchone()
    if not row or row[2] != "succeeded":
        raise ValueError("Cannot refund a payment that is not succeeded")

    processor_txn_id, original_amount, _ = row
    refund_amount = amount_cents or original_amount  # full refund if not specified

    stripe_refund = stripe.Refund.create(
        payment_intent=processor_txn_id,
        amount=refund_amount,
        reason=reason,
        idempotency_key=idempotency_key
    )

    refund_id = str(uuid.uuid4())
    with conn.cursor() as cur:
        cur.execute("""
            INSERT INTO Refund (refund_id, payment_id, amount_cents, reason, idempotency_key, processor_ref_id, status)
            VALUES (%s,%s,%s,%s,%s,%s,'succeeded')
        """, (refund_id, payment_id, refund_amount, reason, idempotency_key, stripe_refund.id))
        if refund_amount == original_amount:
            cur.execute("UPDATE Payment SET status='refunded' WHERE payment_id=%s", (payment_id,))
    conn.commit()
    return {"refund_id": refund_id, "status": "succeeded"}

Key Interview Points

Never store raw card data: PCI-DSS scope requires quarterly audits, penetration tests, and network segmentation for any system that stores, transmits, or processes raw card numbers. Use a payment processor’s client-side tokenization (Stripe Elements, Braintree Drop-in UI) — raw card data never touches your servers. Your server only receives a payment_method_id token. This takes your infrastructure out of PCI-DSS scope.
Write before calling the processor: Insert the Payment row in “initiated” state before calling Stripe. If the Stripe call succeeds but your DB write fails (crash between steps), the “insert first” pattern ensures you have a record to reconcile against. Stripe’s idempotency key prevents a duplicate charge on retry. Without this, a crash between the Stripe call and DB write produces an untracked charge — revenue collected with no internal record.
Idempotency key design: The idempotency key should encode intent: f”pay-{order_id}-v1″. On retry, the same key returns the existing outcome without re-charging. Change the version (v2) only when the intent genuinely changes (different amount, different order). Don’t use timestamp-based keys — retries generate different keys and cause duplicate charges.
3D Secure and async confirmation: Some card issuers require 3D Secure (customer authentication challenge). Stripe returns status=”requires_action” synchronously; the browser redirects the customer to the bank’s 3DS page. The PaymentIntent transitions to “succeeded” or “failed” asynchronously via webhook. Always handle the webhook path even if your checkout flow is synchronous — some payment methods (ACH, SEPA) always confirm asynchronously.
Reconciliation job: Run nightly: query Stripe for all PaymentIntents in the last 24 hours and compare with your Payment table. Flag any Stripe PaymentIntent without a matching Payment row (untracked charge) or any Payment row in “pending” state with no matching Stripe record. Email the finance team for manual investigation. This safety net catches any discrepancy that webhooks missed.

{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”Why must you write the Payment record before calling the Stripe API?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”If you call Stripe first and then write to the database, a crash between the Stripe call and the DB write produces an untracked charge: Stripe has charged the customer and holds the funds, but your database has no record. Reconciliation (matching Stripe’s records to yours) can recover this, but it’s complex and error-prone. Writing the Payment row in "initiated" state first ensures you always have a record to update. On retry, the idempotency key prevents Stripe from creating a duplicate charge. On recovery from crash, you can query for "initiated" payments older than 5 minutes and either confirm (lookup in Stripe by idempotency key) or mark as failed. The write-before-call pattern is the foundation of reliable payment systems.”}},{“@type”:”Question”,”name”:”How does Stripe’s idempotency key prevent double charges on network retries?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Network errors during payment are indistinguishable from successful requests that lost the response. Your server sends a charge request to Stripe; the connection drops; you don’t know if Stripe charged the customer or not. The idempotency key solves this: pass the same key on retry (Stripe-Idempotency-Key header). Stripe’s server checks if a request with this key was already processed. If yes: return the original response (same PaymentIntent) without creating a new charge. If no: process the request normally. The key must be unique per logical operation (one charge), not per HTTP request. Using a request ID (different per retry) as the idempotency key defeats the purpose. Store the idempotency key in your Payment table so you can reconstruct it on retry.”}},{“@type”:”Question”,”name”:”How do you handle 3D Secure authentication in the payment flow?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”3D Secure (3DS) requires the customer to authenticate with their bank before the payment is approved — they are redirected to a bank-hosted challenge page. Stripe returns status="requires_action" for such payments. Flow: (1) create PaymentIntent; (2) if status="requires_action", return the next_action.redirect_to_url to the frontend; (3) browser redirects to the bank authentication page; (4) bank redirects back to your return_url after authentication; (5) Stripe sends payment_intent.succeeded or payment_intent.payment_failed webhook. Your system must handle both the synchronous API response (for non-3DS payments) and the async webhook (for 3DS and bank transfer payments). Never fulfill the order on API response alone — always wait for the webhook confirmation.”}},{“@type”:”Question”,”name”:”What does PCI-DSS compliance mean for payment processing?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”PCI-DSS (Payment Card Industry Data Security Standard) defines security requirements for systems that store, process, or transmit raw card data (card number, CVV, expiry). Non-compliance risks: fines, card network bans, and liability for fraud. The easiest path: achieve PCI SAQ-A (the lowest tier) by using Stripe’s client-side tokenization. Stripe Elements collects card data in an iframe hosted on Stripe’s servers — raw card data never touches your server. Your server only receives a payment_method_id token. SAQ-A requires: HTTPS everywhere, no card data in logs, and a completed annual self-assessment questionnaire. Never log raw card numbers, even in error logs. Scan code for card number patterns (16-digit sequences) in CI to prevent accidental logging.”}},{“@type”:”Question”,”name”:”How do you implement a reliable reconciliation job for payments?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Discrepancies between your Payment table and Stripe’s records can occur from: webhook delivery failures, idempotency key collisions, or bugs. Nightly reconciliation: (1) query Stripe for all PaymentIntents created in the last 24 hours (stripe.PaymentIntent.list with created range); (2) for each Stripe PaymentIntent, find the matching Payment row by processor_txn_id; (3) if no match: untracked charge — alert and create a placeholder record; (4) if status mismatch: update your record to match Stripe’s authoritative state. Run reconciliation for 7 days of history (webhooks can be delayed significantly). Alert on any discrepancy > $0.01. Store reconciliation run results in a ReconciliationRun table for audit.”}}]}

Payment processing and idempotency design is discussed in Stripe system design interview questions.

Payment processing and financial transaction design is covered in Coinbase system design interview preparation.

Payment processing and checkout system design is discussed in Shopify system design interview guide.