#24 Payment Processing System (Stripe-like) — Solution

✦ AI-Generated Solution · System Design · Comprehensive

1. Requirements

Functional

• Hold (authorize): accept (account_number, amount), forward to the correct downstream processor, record approve/deny.
• Charge (capture): on an approved hold, accept a charge (with optional tip) and queue it for settlement.
• Release: if no charge arrives before the hold expires, release the held funds.
• Batch settlement: every evening at 10pm, group captured charges by downstream processor into one file and initiate a bulk wire transfer.

Non-functional

• Correctness over latency — money must never be double-charged or lost. Exactly-once semantics on capture.
• Idempotency — terminals retry; the same logical request must not create two holds/charges.
• Auditability — every state transition is immutable and traceable (regulatory).
• Scale target to discuss: ~thousands of auths/sec; settlement file of millions of rows/day.

2. Core Abstractions & State Machine

Model a payment as a single entity advancing through an explicit state machine. This is the single most important idea the interviewer is looking for.

PENDING ── hold approved ──> AUTHORIZED ── charge req ──> CAPTURED ── 10pm batch ──> SETTLED
   │                             │
   └── hold denied ──> DECLINED  └── timeout (no charge) ──> EXPIRED (funds released)

3. Data Model

-- One row per payment, mutated through its lifecycle (state is indexed for batch queries)
CREATE TABLE payments (
  payment_id      UUID PRIMARY KEY,
  account_number  TEXT NOT NULL,          -- tokenized / vaulted, never raw PAN
  processor_id    TEXT NOT NULL,          -- amex, visa, ...
  amount_auth     BIGINT NOT NULL,        -- minor units (cents)
  amount_capture  BIGINT,                 -- final amount incl. tip
  state           TEXT NOT NULL,          -- PENDING|AUTHORIZED|CAPTURED|SETTLED|DECLINED|EXPIRED
  idempotency_key TEXT UNIQUE,            -- dedupes retries
  hold_expires_at TIMESTAMPTZ,
  created_at      TIMESTAMPTZ DEFAULT now(),
  updated_at      TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_settlement ON payments (processor_id, state, hold_expires_at)
  WHERE state = 'CAPTURED';   -- partial index makes the 10pm batch query cheap

CREATE TABLE payment_events (   -- append-only audit log (event sourcing)
  event_id   BIGSERIAL PRIMARY KEY,
  payment_id UUID NOT NULL,
  type       TEXT NOT NULL,            -- HOLD_REQUESTED, HOLD_APPROVED, CAPTURED, SETTLED...
  payload    JSONB,
  created_at TIMESTAMPTZ DEFAULT now()
);

The interview report explicitly flagged that the candidate who struggled "should have added state as a secondary index" — the partial index on (processor_id, state) above is exactly that fix and makes the batch settlement query a fast range scan instead of a full table scan.

4. API Design

POST /v1/holds            Idempotency-Key: <uuid>
  { "account_number": "tok_...", "amount": 4000, "processor": "amex" }
  -> 201 { "payment_id": "...", "state": "AUTHORIZED" }   # or DECLINED

POST /v1/payments/{id}/capture   Idempotency-Key: <uuid>
  { "amount": 4600 }                                       # original + tip
  -> 200 { "state": "CAPTURED" }

POST /v1/payments/{id}/release   -> 200 { "state": "EXPIRED" }
GET  /v1/payments/{id}           -> current state + event history

Every mutating endpoint requires an Idempotency-Key. The server stores the key with the result; a replay returns the stored result instead of re-executing.

5. Architecture

• API Gateway — authN/Z, idempotency-key check, request validation.
• Hold Service — writes PENDING, calls the downstream processor, transitions to AUTHORIZED/DECLINED. Wrap the external call so a timeout doesn't leave an ambiguous state (reconcile via the processor's query API).
• Charge Service — validates an AUTHORIZED payment, writes CAPTURED, emits an event.
• Batch Settlement (10pm cron) — for each processor, range-scans state = CAPTURED, writes a settlement file to object storage, initiates the wire transfer, and atomically flips rows to SETTLED (mark with a batch_id so a re-run is idempotent).

6. Batch Settlement Deep Dive (the part candidates fail)

1. Cron triggers at 10pm; acquire a distributed lock per processor (only one settlement run at a time).
2. Range-scan the partial index (processor_id, 'CAPTURED') in pages; stream rows into a settlement file (group-by processor).
3. Assign a batch_id and flip each included row CAPTURED -> SETTLED in the same transaction that records the batch membership — so a crash mid-run is safely re-runnable (rows already SETTLED are skipped).
4. Upload the file, call the processor's bulk-transfer API, and persist the processor's acknowledgment. Reconcile the next morning against the processor's report.

7. Idempotency, Consistency & Failure Handling

• Exactly-once capture: the idempotency_key UNIQUE constraint + the state machine (AUTHORIZED -> CAPTURED only) prevents double capture even under retries.
• Ambiguous downstream timeouts: never assume failure; record PENDING, then reconcile using the processor's idempotency key / query API.
• DB choice: a relational store (PostgreSQL/Vitess, or DynamoDB with a state GSI) — you need strong consistency and conditional writes for the state transitions. If using DynamoDB, use conditional updates (attribute state = AUTHORIZED) and a GSI on state for the batch query.

8. Scaling & Follow-ups

• 10× traffic: shard payments by payment_id; the settlement job fans out per processor partition. Authorize path is the write hot path → scale stateless services horizontally, batch is throughput-bound not latency-bound.
• Global deployment: regional processing with local downstream processors; keep data residency per region (PCI-DSS, GDPR); settle per region.
• Security: never store raw PANs — tokenize/vault; encrypt at rest; scope PCI to the vault.
• Observability: alert on stuck PENDING/AUTHORIZED (reconciliation gaps), settlement file row-count vs captured-count parity, and per-processor wire acknowledgments.

9. Summary