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.
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.
See also: Scale AI Interview Guide 2026: Data Infrastructure, RLHF Pipelines, and ML Engineering
See also: Uber Interview Guide 2026: Dispatch Systems, Geospatial Algorithms, and Marketplace Engineering