Designing a Payment System

In most system design interviews, "Availability" (AP) is king. In Payment Systems, Consistency (CP) is the only thing that matters. Losing data or double-charging a user is unacceptable.

1. Requirements

Functional

• Pay: User transfers money to a merchant.
• Refund: Refund a transaction.
• History: View transaction logs.

Non-Functional

• Zero Data Loss: Just use ACID databases.
• Exactly-Once Processing: Network retries should not cause double charges.
• Consistency: Account balances must always sum to zero (or the correct total).

2. The Core Problem: Network Failure

The internet is unreliable.

1. Client sends POST /pay $10.
2. Server charges credit card.
3. Server sends 200 OK. (Packets lost here)
4. Client sees "Network Error".
5. Client retries POST /pay $10.
6. Customer is charged $20. Disaster.

Solution: Idempotency Keys

Every request must include a unique client-generated ID (Idempotency Key).

• Header: Idempotency-Key: v4-uuid-gen-by-client

Server Logic:

1. Receive Request.
2. Check DB: SELECT status FROM transactions WHERE idempotency_key = 'abc'.
3. If Exists: Return the stored result. Do not execute logic again.
4. If New: Execute logic -> Write to DB -> Return Result.

• Note: The recursive lookup must be atomic. Use INSERT IGNORE or database transactions.

3. Storage Pattern: Double-Entry Ledger

Never, ever store a user's balance as a simple integer: User.balance = 100.

• Why? If two threads update it at once, you get race conditions.
• Audit? You can't prove how the balance got there.

The Ledger Table

Instead, record movements of money. Every transaction has two entries: a debit and a credit.

Calculating Balance

SELECT SUM(amount) FROM ledger WHERE account = 'Alice'

• Pros: Immutable history. Easy to audit.
• Cons: Summing millions of rows is slow.
• Optimization: Use a "Snapshot" table that caches the balance every night. Current Balance = Snapshot + Sum(Today's Ledger).

4. Architecture

The Synchronous Phase

1. Payment Service: Validates request. Checks fraud.
2. Risk Engine: Is this IP blocked? Is the velocity too high?
3. PSP Gateway: Calls Stripe/PayPal API. This is the external point of failure.

The Asynchronous Phase (Reconciliation)

What if Stripe charges the card, but our database crashes before we record it? We now have a "Ghost Charge".

The Reconciler (Cron Job):

1. Download "Yesterday's Settlement File" from Stripe.
2. Iterate through every row.
3. Match against our local database.
4. Mismatch Found?
   • If Stripe has it, but we don't: We missed a success message. Update our DB.
   • If we have it, but Stripe doesn't: The charge failed. Mark our DB as failed.

5. Database Choice

• NoSQL (Cassandra/Mongo)?: Risky. Eventual consistency is dangerous here.
• SQL (Postgres/MySQL): Yes. Strong ACID transactions are required.
• NewSQL (CockroachDB/Spanner): Good for global scale with ACID properties.

6. Distributed Transactions (Two-Phase Commit)

If you have a microservice architecture (Payment Service + Wallet Service):

• 2PC: Too slow, keys locks held too long.
• Saga Pattern: Preferred.
  1. Payment Service charges card.
  2. Wallet Service credits user.
  3. If Wallet fails? Trigger a "Compensating Transaction" (Refund card) in Payment Service.

Summary

1. Idempotency: The client generates a UUID to prevent double-processing.
2. Double-Entry: Store immutable transaction logs, not mutable balances.
3. Reconciliation: The ultimate source of truth is the external bank; always sync with them nightly.