Design Payment System

Let's look into functional requirements

FR

1.Pay in and Pay out flow

2.Duplicate payment should be avoided(idempotency should be followed)

3.Link payment methods (bank accounts, credit/debit cards, wallets, UPI, etc.).

4.Bank reconciliation (daily batch settlements)

NFR

1. System should be highly available to process millions of transactions per second.Ensure no single point of failure using multi-region deployments

2. Payments should be near real-time (<100ms for UPI-like systems, <2s for card payments).

3. System should be highly scalable to handle peak loads

4.System should be highly consistent

Estimates

Daily Transactions: 100M
Peak Load: 10× average traffic (handling surge scenarios).
Read:Write Ratio: 4:1 (reads include balance checks, transaction history, etc.).
Storage Duration: 1 year (to retain transaction records).

Average TPS= 100M/10^5=1k TPS
Peak will be =10*1kTPS=10K TPS

Storage

Transaction ID: 16 bytes
Sender & Receiver IDs: 32 bytes
Amount, Timestamp: 16 bytes
Metadata (status, method, etc.): 64 bytes
Signatures & Hashes: 128 bytes
Total per transaction: ~256 bytes

Total storage= 100M*256Bytes=25GB/day
For one years storage=25GB*400=9 TB

API’s

1.POST /api/v1/users/register

Content-Type: application/json

Authorization: Bearer <token>

request {
  "name": "John Doe",
  "email": "john@example.com",
  "phone": "+1234567890",
  "kycDetails": { "idType": "passport", "idNumber": "A12345678" }
}
response { "userId": "user_12345", "status": "registered" }

2.POST /api/v1/users/payment-methods

request {
  "userId": "user_12345",
  "methodType": "bank_account",
  "details": {
    "accountNumber": "123456789012",
    "ifsc": "BANK12345"
  }
}
response { "methodId": "pm_67890", "status": "linked" }
 

3.POST /api/v1/payments

request {
  "payerId": "user_12345",
  "payeeId": "merchant_7890",
  "amount": 500,
  "currency": "INR",
  "paymentMethod": "upi",
  "metadata": { "orderId": "ORD98765" }
}

response { "transactionId": "txn_54321", "status": "pending", "expiresIn": "300s" }

4.GET /api/v1/payments/status?transactionId=txn_54321

request { "transactionId": "txn_54321", "status": "completed", "timestamp": "2025-02-20T10:30:00Z" }

5.POST /api/v1/payments/cancel:- will be a system level internal api ,not exposed to user

request { "transactionId": "txn_54321" }

response { "transactionId": "txn_54321", "status": "cancelled" }

6.GET /api/v1/accounts/balance?userId=user_12345

{ "userId": "user_12345", "balance": 10500, "currency": "INR" }

7.GET /api/v1/accounts/transactions?userId=user_12345&limit=10

{
  "transactions": [
    { "transactionId": "txn_111", "amount": 500, "status": "completed", "timestamp": "2025-02-19T12:00:00Z" },
    { "transactionId": "txn_112", "amount": 1000, "status": "pending", "timestamp": "2025-02-18T15:30:00Z" }
  ]
}

8.POST /api/v1/payments/refund

request {
  "transactionId": "txn_54321",
  "reason": "duplicate transaction"
}

response { "refundId": "ref_98765", "status": "processing" }

9.POST /api/v1/auth/otp

request { "userId": "user_12345", "transactionId": "txn_54321" }
response { "otpSent": true }

10.POST /api/v1/merchants/payout

request {
  "merchantId": "merchant_7890",
  "amount": 10000,
  "destination": "bank",
  "bankDetails": {
    "accountNumber": "9876543210",
    "ifsc": "BANK54321"
  }
}
response { "payoutId": "payout_333", "status": "processing" }

11.POST /api/v1/webhooks

request {
  "url": "https://merchant.example.com/webhook",
  "eventTypes": ["payment_success", "payment_failed"]
}
response { "webhookId": "webhook_123" }

12.GET /api/v1/admin/audit-logs?transactionId=txn_54321

{
  "logs": [
    { "event": "initiated", "timestamp": "2025-02-19T12:00:00Z" },
    { "event": "approved", "timestamp": "2025-02-19T12:01:00Z" }
  ]
}

Database Selection

1. Distributed NoSQL (Cassandra / DynamoDB)

• Use case: High-write workloads, fast balance updates.

• Why? Handles billions of writes/day, scales horizontally, and has low-latency queries.

• What it stores?

  • Real-time transactions

  • User balances

  • Metadata for quick retrieval

2. Relational SQL (PostgreSQL / MySQL)

• Use case: Ledger, settlements, regulatory compliance.

• Why? ACID compliance, ensuring financial consistency.

• What it stores?

  • Ledger transactions

  • Merchant settlements

  • Refund & chargeback records

3. Caching (Redis / Memcached)

• Use case: Quick balance lookups and rate limiting.

• Why? Microsecond-level access to hot data.

• What it stores?

  • User balances

  • Transaction status for fast reads

Database Schema

1. Transactions Table (Cassandra/DynamoDB)

Purpose: Store high-volume transactions efficiently.

CREATE TABLE transactions (
    transaction_id UUID PRIMARY KEY,
    payer_id UUID,
    payee_id UUID,
    amount DECIMAL(10,2),
    currency VARCHAR(3),
    status ENUM('pending', 'completed', 'failed', 'refunded'),
    timestamp TIMESTAMP,
    metadata JSON
) WITH CLUSTERING ORDER BY (timestamp DESC);

🔹 Sharding Key: transaction_id (ensures even distribution).
🔹 Partitioning on payer_id for faster user-specific queries.

2. Ledger Table (PostgreSQL/MySQL)

Purpose: Ensure a reliable, ACID-compliant transaction history.

CREATE TABLE ledger (
    ledger_id SERIAL PRIMARY KEY,
    transaction_id UUID NOT NULL,
    payer_id UUID NOT NULL,
    payee_id UUID NOT NULL,
    amount DECIMAL(10,2),
    currency VARCHAR(3),
    entry_type ENUM('debit', 'credit'),
    balance_after DECIMAL(10,2),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES transactions(transaction_id)
);

🔹 Maintains an immutable record (no updates, only inserts).
🔹 Ensures double-entry accounting compliance.

3. User Balances (Cassandra / Redis)

Purpose: Track real-time balances efficiently.

CREATE TABLE user_balances (
    user_id UUID PRIMARY KEY,
    balance DECIMAL(10,2),
    last_updated TIMESTAMP
);

🔹 Reads are served via Redis for instant access.
🔹 Writes go to Cassandra for durability.

4. Payment Methods (PostgreSQL/MySQL)

Purpose: Store linked payment methods securely.

CREATE TABLE payment_methods (
    method_id UUID PRIMARY KEY,
    user_id UUID,
    method_type ENUM('card', 'bank_account', 'upi'),
    details JSONB,
    is_default BOOLEAN DEFAULT false,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

🔹 Uses JSONB for flexible payment details storage.

5. Refunds & Chargebacks (SQL - Ledger)

Purpose: Maintain records of refunds and disputes.

CREATE TABLE refunds (
    refund_id UUID PRIMARY KEY,
    transaction_id UUID NOT NULL,
    amount DECIMAL(10,2),
    reason TEXT,
    status ENUM('processing', 'completed', 'failed'),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES transactions(transaction_id)
);

6. Webhooks & Event Notifications

Purpose: Notify merchants/users of transaction updates.

CREATE TABLE webhooks (
    webhook_id UUID PRIMARY KEY,
    url TEXT NOT NULL,
    event_types JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Optimization Strategies

1. Use Redis for Balances

   • Store user balances in Redis for fast reads (1-2ms latency).

   • Background sync with Cassandra for durability.

2. Use CQRS (Command Query Responsibility Segregation)

   • Writes → NoSQL (Cassandra/DynamoDB)

   • Reads → PostgreSQL (for reporting/analytics)

3. Use Partitioning & Sharding

   • Partition transaction tables by payer_id for fast lookups.

   • Shard SQL ledger tables by transaction_id for scalability.

4. Indexing for Fast Queries

   CREATE INDEX idx_payer_transactions ON transactions (payer_id);
   CREATE INDEX idx_transaction_status ON transactions (status);
   

   Index on payer_id for fast user-based lookups.

Index on status to quickly find pending/completed transactions.

HLD