Part 1: OLTP – Transactional Backbone

Goal: Design a high-throughput, ACID-compliant system to handle Stripe’s millions of daily transactions (payments, refunds, chargebacks, subscriptions) with minimal latency, real-time replication, and disaster recovery.

---

🎯 Core Objectives

• ACID Compliance: Ensure atomicity, consistency, isolation and durability for all transactions.
• High Throughput: Support millions of transactions/day with <100ms latency.
• Real-Time Sync: Synchronize data with OLAP/NoSQL in near real-time (via CDC).
• Disaster Recovery: Multi-region failover with RTO < 1 minute.

---

Data Model

We use a Normalized 3NF Schema.

Entities & Relationships

• Transactions: transaction_id (PK), merchant_id (FK), customer_id (FK), amount, currency, payment_method, timestamp, status, fraud_indicator.
• Customers: customer_id (PK), name, payment_methods, transaction_history, risk_profile.
• Merchants: merchant_id (PK), business_name, industry, transaction_volume, compliance_status.
• Disputes: dispute_id (PK), transaction_id (FK), status, resolution_time, metadata.
• Subscriptions: subscription_id (PK), customer_id (FK), merchant_id (FK), billing_cycle, status.

ERD (Mermaid)

erDiagram
    TRANSACTIONS ||--o{ CUSTOMERS : "belongs_to"
    TRANSACTIONS ||--o{ MERCHANTS : "belongs_to"
    TRANSACTIONS ||--o{ DISPUTES : "may_have"
    TRANSACTIONS ||--o{ SUBSCRIPTIONS : "linked_to"
    TRANSACTIONS {
        string transaction_id PK
        string merchant_id FK
        string customer_id FK
        decimal amount
        string currency
        string payment_method
        timestamp timestamp
        string status
        boolean fraud_indicator
    }
    CUSTOMERS {
        string customer_id PK
        string name
        string payment_method
        string risk_profile
    }
    MERCHANTS {
        string merchant_id PK
        string business_name
        string industry
        decimal transaction_volume
        string compliance_status
    }
    DISPUTES {
        string dispute_id PK
        string transaction_id FK
        string status
        timestamp resolution_time
    }
    SUBSCRIPTIONS {
        string subscription_id PK
        string customer_id FK
        string merchant_id FK
        timestamp billing_date
        string status
    }

Properties

Scalability, replication

we use PostgreSQL benefits :

Feature/Strategy	Implementation	Benefit
ACID Compliance	Ensures transactional integrity	Reliable multi-row updates (e.g., refunds)
Multi-Table Transactions	Native support for transactions spanning multiple tables	Consistent updates across related data
Horizontal Scalability	Add nodes to handle increasing load	Scale read/write operations
Distributed SQL	Use PostgreSQL for distributed queries	Handle high-volume workloads
Replication	PostgreSQL Logical Replication	Multi-region sync.

Disaster Recovery

Automated Backups (point-in-time recovery) can be established in PostgreSQL using WAL (Write-Ahead Log) archiving combined with periodic base backups. Tools like pgBackRest, Barman, or WAL-G are commonly used to automate this.

Performance

Strategy	Implementation	Tools	Benefit
Indexing	Indexes on timestamp, customer_id, merchant_id.	PostgreSQL	Faster queries.
Partitioning	Partition Transactions by merchant_id + date.	PostgreSQL	Distribute load, improve query performance.
Caching	Redis for read-through/write-through caching.	Redis	Reduce latency for frequent queries.

Monitoring

Prometheus and Grafana can be used to track performance and failures.

Sample SQL Queries

1.Atomic Transaction (ACID Example)

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A1';
  UPDATE accounts SET balance = balance + 100 WHERE account_id = 'A2';
COMMIT;

2.Fraudulent Transactions for a Merchant

SELECT t.transaction_id, t.amount, t.timestamp, c.name AS customer_name
FROM Transactions t
JOIN Customers c ON t.customer_id = c.customer_id
WHERE t.merchant_id = 'm_67890' AND t.fraud_indicator = TRUE;

3.Daily Revenue by Merchant

SELECT
    merchant_id,
    DATE_TRUNC('day', timestamp) AS day,
    SUM(amount) AS revenue
FROM Transactions
GROUP BY merchant_id, day;