Payment System

/* ━━━ NAPKIN MATH: Start From Daily Transactions ━━━ */
Transactions/day: 1M
Transactions/sec (avg): 1M / 86,400 ≈ 12 TPS
Transactions/sec (peak 10x): ~120 TPS
Transactions/sec (Black Friday 30x): ~360 TPS

/* ━━━ FINANCIAL VOLUME ━━━ */
Annual volume: $10B
Average transaction: $10B / 365M = ~$27
Median transaction: ~$15 (long tail of large B2B payments)

/* ━━━ STORAGE ━━━ */
Per payment record: payment_id (16B) + idempotency_key (36B) + 
  amount (8B) + currency (3B) + status (10B) + timestamps (16B) + 
  PSP metadata (200B) + indices overhead = ~500 bytes
Per ledger entry: 2-3 entries per payment × 200 bytes = ~500 bytes
Total per transaction: ~1 KB (payment + ledger + events)
5-year storage: 1M/day × 365 × 5 × 1 KB = 1.8 TB

/* ━━━ LEDGER ENTRIES ━━━ */
Entries/day: 1M payments × 3 entries avg = 3M ledger rows/day
5-year ledger: 3M × 365 × 5 = 5.4 billion rows

/* ━━━ MESSAGE QUEUE ━━━ */
Events/day: 1M payments × 4 state transitions avg = 4M events/day
Event size: ~500 bytes (JSON payload with metadata)
Kafka throughput: 4M / 86,400 ≈ 46 events/sec (trivial for Kafka)

/* ━━━ RECONCILIATION ━━━ */
Settlement files/day: 1 per PSP (typically arrives T+1)
Rows to reconcile: 1M comparisons/day (one batch job, ~15 min runtime)

POST /api/v1/payments
Headers: Idempotency-Key: pay_a1b2c3d4-e5f6-7890-abcd-ef1234567890
Body: {
  "amount": 5000,          // $50.00 in cents
  "currency": "USD",
  "payment_method_token": "tok_visa_4242",
  "merchant_id": "merchant_headphones_inc",
  "order_id": "order_789"
}

INSERT INTO payments (id, idempotency_key, amount, currency, status, merchant_id, order_id, created_at)
VALUES ('pay_uuid_001', 'pay_a1b2c3d4...', 5000, 'USD', 'CREATED', 'merchant_headphones_inc', 'order_789', NOW())

POST https://api.stripe.com/v1/charges
Body: { amount: 5000, currency: "usd", source: "tok_visa_4242", 
        metadata: { internal_payment_id: "pay_uuid_001" } }

UPDATE payments SET status = 'COMPLETED', psp_charge_id = 'ch_1abc123', 
       completed_at = NOW() WHERE id = 'pay_uuid_001'

Entry 1: DEBIT   account="buyer:alice_123"           amount=5000  currency=USD
Entry 2: CREDIT  account="seller:headphones_inc"     amount=4850  currency=USD  (97% to seller)
Entry 3: CREDIT  account="platform:commission"       amount=150   currency=USD  (3% platform fee)

{ "payment_id": "pay_uuid_001", "status": "COMPLETED", "amount": 5000, "currency": "USD" }

POST https://api.stripe.com/v1/charges
Headers: Idempotency-Key: pay_uuid_001   ← OUR payment ID as Stripe's dedup key

/* ━━━ Payment DB (PostgreSQL) ━━━ */

CREATE TABLE payments (
    id                UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    idempotency_key   VARCHAR(64) UNIQUE NOT NULL,
    amount            BIGINT      NOT NULL,              -- in smallest currency unit (cents)
    currency          VARCHAR(3)  NOT NULL,
    status            VARCHAR(20) NOT NULL DEFAULT 'CREATED',
    merchant_id       VARCHAR(64) NOT NULL,
    order_id          VARCHAR(64),
    payment_method    VARCHAR(20) NOT NULL,              -- 'card', 'wallet', 'bank_transfer'
    psp_charge_id     VARCHAR(128),                      -- from Stripe/Adyen response
    failure_reason    TEXT,
    retry_count       INT         DEFAULT 0,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    completed_at      TIMESTAMPTZ,

    CONSTRAINT valid_status CHECK (status IN (
        'CREATED', 'PROCESSING', 'PSP_CALLED', 'COMPLETED', 
        'FAILED', 'REQUIRES_RETRY', 'REFUND_PENDING', 'REFUNDED', 'REVERSED'
    ))
);

CREATE INDEX idx_payments_status ON payments (status) WHERE status IN ('REQUIRES_RETRY', 'PROCESSING');
CREATE INDEX idx_payments_merchant ON payments (merchant_id, created_at DESC);
CREATE INDEX idx_payments_order ON payments (order_id);

/* ━━━ Ledger DB (PostgreSQL, separate instance) ━━━ */

CREATE TABLE ledger_entries (
    id              BIGSERIAL   PRIMARY KEY,
    payment_id      UUID        NOT NULL,
    account_id      VARCHAR(128) NOT NULL,       -- "buyer:alice_123", "seller:merchant_456", "platform:fees"
    entry_type      VARCHAR(6)  NOT NULL,        -- 'DEBIT' or 'CREDIT'
    amount          BIGINT      NOT NULL,        -- always positive; sign determined by entry_type
    currency        VARCHAR(3)  NOT NULL,
    description     TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT valid_entry_type CHECK (entry_type IN ('DEBIT', 'CREDIT'))
);

-- Critical: immutable table. No UPDATE or DELETE triggers allowed.
-- REVOKE UPDATE, DELETE ON ledger_entries FROM payment_service_role;

CREATE INDEX idx_ledger_account ON ledger_entries (account_id, created_at DESC);
CREATE INDEX idx_ledger_payment ON ledger_entries (payment_id);

/* ━━━ Outbox Table (same DB as payments for transactional guarantee) ━━━ */

CREATE TABLE payment_outbox (
    id              BIGSERIAL   PRIMARY KEY,
    payment_id      UUID        NOT NULL,
    event_type      VARCHAR(50) NOT NULL,       -- 'PaymentCompleted', 'PaymentFailed', etc.
    payload         JSONB       NOT NULL,
    published       BOOLEAN     DEFAULT FALSE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    published_at    TIMESTAMPTZ
);

CREATE INDEX idx_outbox_unpublished ON payment_outbox (created_at) WHERE published = FALSE;

/* ━━━ Reconciliation Results ━━━ */

CREATE TABLE reconciliation_results (
    id              BIGSERIAL   PRIMARY KEY,
    reconcile_date  DATE        NOT NULL,
    payment_id      UUID        NOT NULL,
    our_status      VARCHAR(20) NOT NULL,
    psp_status      VARCHAR(20) NOT NULL,
    our_amount      BIGINT,
    psp_amount      BIGINT,
    discrepancy_type VARCHAR(30),    -- 'STATUS_MISMATCH', 'AMOUNT_MISMATCH', 'MISSING_IN_PSP', 'MISSING_IN_US'
    resolved        BOOLEAN     DEFAULT FALSE,
    resolution_note TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

                          ┌─────────────────────────────────────────────┐
                          │                                             │
                          ▼                                             │
    ┌─────────┐    ┌────────────┐    ┌───────────┐    ┌───────────┐   │
    │ CREATED │───▶│ PROCESSING │───▶│ PSP_CALLED│───▶│ COMPLETED │   │
    └─────────┘    └────────────┘    └───────────┘    └───────────┘   │
                          │                                  │          │
                          │ timeout                          │ refund   │
                          ▼                                  ▼          │
                   ┌──────────────┐              ┌──────────────────┐  │
                   │REQUIRES_RETRY│──(max 3)───▶ │     FAILED       │──┘
                   └──────────────┘              └──────────────────┘
                          │                              (user can retry
                          │ retry                         with new key)
                          ▼
                   ┌────────────┐
                   │ PROCESSING │  (back to PSP call)
                   └────────────┘

State Transition Rules:
  CREATED → PROCESSING       : Payment accepted, about to call PSP
  PROCESSING → PSP_CALLED    : PSP responded (not yet persisted in ledger)
  PSP_CALLED → COMPLETED     : Ledger entries written, fully committed
  PROCESSING → REQUIRES_RETRY: PSP timeout or 5xx error
  REQUIRES_RETRY → PROCESSING: Retry attempt (after backoff)
  REQUIRES_RETRY → FAILED    : Max retries (3) exceeded
  COMPLETED → REFUND_PENDING : Refund requested
  REFUND_PENDING → REFUNDED  : PSP confirms refund
  COMPLETED → REVERSED       : Late webhook reversal (rare)

function processPayment(idempotencyKey, request):
    // Step 1: Atomic check-and-insert using UNIQUE constraint
    existing = db.query(
        "SELECT * FROM payments WHERE idempotency_key = $1", idempotencyKey
    )

    if existing != null:
        if existing.status == 'COMPLETED':
            return existing.toResponse()       // safe to return cached result
        if existing.status == 'PROCESSING':
            return 409("Payment in progress")  // concurrent duplicate request
        if existing.status == 'FAILED':
            return existing.toResponse()       // failed result is also cached

    // Step 2: Create new payment
    payment = db.insert(
        "INSERT INTO payments (id, idempotency_key, amount, currency, status)
         VALUES ($1, $2, $3, $4, 'CREATED')",
        newUUID(), idempotencyKey, request.amount, request.currency
    )
    // ON CONFLICT (idempotency_key) DO NOTHING handles race condition

    // Step 3: Call PSP
    payment.status = 'PROCESSING'
    db.update(payment)

    pspResult = callPSP(payment)              // 200-400ms

    // Step 4: Record result atomically with ledger
    BEGIN TRANSACTION
        payment.status = pspResult.success ? 'COMPLETED' : 'FAILED'
        payment.psp_charge_id = pspResult.chargeId
        db.update(payment)

        if pspResult.success:
            insertLedgerEntries(payment)
            insertOutboxEvent(payment, 'PaymentCompleted')
    COMMIT

    return payment.toResponse()

function calculateRetryDelay(retryCount):
    baseDelay = 2000ms        // 2 seconds
    maxDelay = 30000ms        // 30 seconds cap

    exponentialDelay = baseDelay * (2 ^ retryCount)
    cappedDelay = min(exponentialDelay, maxDelay)

    // Add jitter to prevent thundering herd
    jitter = random(0, cappedDelay * 0.3)
    return cappedDelay + jitter

// Retry schedule: 2s, 4s, 8s (then give up after 3 attempts)
// With jitter:    2.4s, 5.1s, 9.7s (approximate)

function getAccountBalance(accountId, currency):
    result = db.query("""
        SELECT 
            SUM(CASE WHEN entry_type = 'CREDIT' THEN amount ELSE 0 END) -
            SUM(CASE WHEN entry_type = 'DEBIT' THEN amount ELSE 0 END) as balance
        FROM ledger_entries
        WHERE account_id = $1 AND currency = $2
    """, accountId, currency)
    return result.balance

// For buyer:alice_123  → balance is negative (she spent money)
// For seller:merchant  → balance is positive (she received money)
// For platform:fees    → balance is positive (commission collected)
// SUM of ALL accounts  → always $0.00 (books are balanced)

function validateLedgerIntegrity():
    result = db.query("""
        SELECT currency, 
            SUM(CASE WHEN entry_type = 'CREDIT' THEN amount ELSE -amount END) as net
        FROM ledger_entries
        GROUP BY currency
    """)
    for row in result:
        assert row.net == 0, "CRITICAL: Ledger imbalance in " + row.currency

function dailyReconciliation(date):
    // Step 1: Fetch PSP settlement file
    pspRecords = stripe.getSettlement(date)      // { charge_id → {amount, status} }

    // Step 2: Fetch our records for that day
    ourRecords = db.query(
        "SELECT * FROM payments WHERE DATE(created_at) = $1 AND status != 'CREATED'",
        date
    )

    // Step 3: Compare each record
    exceptions = []

    for payment in ourRecords:
        pspRecord = pspRecords.get(payment.psp_charge_id)

        if pspRecord == null AND payment.status == 'COMPLETED':
            exceptions.add({type: 'MISSING_IN_PSP', payment: payment})
            // We think it succeeded but PSP has no record — CRITICAL

        elif pspRecord != null AND payment.status == 'FAILED':
            exceptions.add({type: 'CHARGED_BUT_MARKED_FAILED', payment: payment})
            // Customer was charged but we told them it failed — CRITICAL

        elif pspRecord != null AND abs(payment.amount - pspRecord.amount) > 1:
            exceptions.add({type: 'AMOUNT_MISMATCH', payment: payment})
            // Rounding or currency conversion issue

    // Step 4: Check for PSP records we don't have
    for chargeId, pspRecord in pspRecords:
        if not ourRecords.containsChargeId(chargeId):
            exceptions.add({type: 'MISSING_IN_US', chargeId: chargeId})
            // PSP charged someone but we have no record — orphan charge

    // Step 5: Alert and report
    if exceptions.hasCritical():
        pagerDuty.alert("CRITICAL reconciliation mismatch", exceptions)

    generateFinanceReport(date, exceptions)
    return exceptions