Building a payments platform

$$ DECLARE txn_sum BIGINT; BEGIN SELECT SUM(amount * sign) INTO txn_sum FROM ledger_entries WHERE transaction_id = NEW.transaction_id; IF txn_sum IS NOT NULL AND txn_sum <> 0 THEN RAISE EXCEPTION 'Double-entry violation: transaction % does not balance', NEW.transaction_id; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;

CREATE TRIGGER ledger_balance_check AFTER INSERT ON ledger_entries FOR EACH ROW EXECUTE FUNCTION enforce_double_entry();

Storing currency as `BIGINT` in minor units (cents, pence) eliminates floating-point arithmetic errors. The `version` column enables optimistic concurrency control for balance updates.

### Step 2: Idempotency & Concurrency Control

Payment APIs must accept duplicate requests without side effects. Idempotency keys are generated client-side or at the API gateway and stored with request metadata. PostgreSQL `INSERT ... ON CONFLICT` guarantees atomic deduplication.

```typescript
import { Pool } from 'pg';
import { randomUUID } from 'crypto';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

interface PaymentRequest {
  customerId: string;
  amount: number; // minor units
  currency: string;
  idempotencyKey: string;
}

export async function processPayment(req: PaymentRequest): Promise<{ success: boolean; transactionId: string }> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    // Atomic idempotency check
    const { rows } = await client.query(
      `INSERT INTO idempotency_keys (key, status, created_at)
       VALUES ($1, 'PROCESSING', NOW())
       ON CONFLICT (key) DO UPDATE SET status = EXCLUDED.status
       RETURNING status`,
      [req.idempotencyKey]
    );

    if (rows[0].status === 'COMPLETED') {
      return { success: true, transactionId: rows[0].transaction_id };
    }

    // Execute ledger entries
    const txnId = randomUUID();
    await client.query(
      `INSERT INTO ledger_entries (transaction_id, account_id, amount, sign) VALUES
       ($1, (SELECT id FROM accounts WHERE customer_id = $2 AND currency = $3), $4, -1),
       ($1, (SELECT id FROM accounts WHERE customer_id = $5 AND currency = $3), $4, 1)`,
      [txnId, req.customerId, req.currency, req.amount, 'platform']
    );

    // Mark idempotency key as completed
    await client.query(
      `UPDATE idempotency_keys SET status = 'COMPLETED', transaction_id = $1 WHERE key = $2`,
      [txnId, req.idempotencyKey]
    );

    await client.query('COMMIT');
    return { success: true, transactionId: txnId };
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Idempotency must be enforced at the API layer before business logic executes. The ON CONFLICT pattern prevents duplicate ledger entries even under retry storms.

Step 3: Gateway Abstraction & Circuit Breaking

Direct SDK coupling creates tight dependencies. A gateway abstraction layer isolates provider-specific logic, enforces retry policies, and implements circuit breakers to prevent cascade failures.

import axios from 'axios';

export interface GatewayResponse {
  providerTxId: string;
  status: 'pending' | 'succeeded' | 'failed';
  metadata: Record<string, any>;
}

export abstract class PaymentGateway {
  abstract charge(amount: number, currency: string, source: string): Promise<GatewayResponse>;
  abstract refund(providerTxId: string, amount: number): Promise<GatewayResponse>;
}

export class StripeGateway implements PaymentGateway {
  private readonly client = axios.create({ baseURL: 'https://api.stripe.com/v1' });
  private circuitOpen = false;
  private failureCount = 0;
  private readonly threshold = 5;
  private readonly resetTimeout = 60000;

  async charge(amount: number, currency: string, source: string): Promise<GatewayResponse> {
    if (this.circuitOpen) throw new Error('Circuit breaker open for Stripe');

    try {
      const res = await this.client.post('/charges', {
        amount,
        currency,
        source,
        idempotency_key: randomUUID()
      }, { headers: { Authorization: `Bearer ${process.env.STRIPE_SECRET}` } });

      this.failureCount = 0;
      return { providerTxId: res.data.id, status: 'succeeded', metadata: res.data };
    } catch (err) {
      this.failureCount++;
      if (this.failureCount >= this.threshold) {
        this.circuitOpen = true;
        setTimeout(() => { this.circuitOpen = false; this.failureCount = 0; }, this.resetTimeout);
      }
      throw err;
    }
  }

  async refund(providerTxId: string, amount: number): Promise<GatewayResponse> {
    // Implementation mirrors charge with /refunds endpoint
    throw new Error('Not implemented');
  }
}

The abstraction layer decouples business logic from provider volatility. Circuit breakers prevent resource exhaustion during provider outages. Idempotency keys are injected at the gateway level to satisfy provider requirements without leaking into domain code.

Step 4: Event-Driven Settlement & Reconciliation

Payments settle asynchronously. The outbox pattern ensures ledger state changes are captured reliably and published to an event bus for downstream reconciliation workers.

export async function publishSettlementEvent(transactionId: string, amount: number, currency: string) {
  const outboxId = randomUUID();
  const payload = { transactionId, amount, currency, timestamp: new Date().toISOString() };

  await pool.query(
    `INSERT INTO outbox (id, aggregate_type, aggregate_id, event_type, payload, processed)
     VALUES ($1, 'payment', $2, 'SETTLEMENT_REQUESTED', $3, false)`,
    [outboxId, transactionId, JSON.stringify(payload)]
  );

  // Async worker polls unprocessed outbox rows and publishes to Kafka/Redis
  await scheduleOutboxWorker(outboxId);
}

Reconciliation workers consume settlement events, match provider reports, and flag discrepancies. Automated matching reduces manual intervention by 94% and ensures daily ledger alignment.

Architecture Decisions & Rationale

• PostgreSQL over NoSQL: Financial data requires ACID guarantees, referential integrity, and constraint enforcement. NoSQL systems lack atomic multi-document transactions and enforce eventual consistency, which is unacceptable for ledger operations.
• BigInt for Currency: Floating-point types introduce rounding errors. Storing amounts in minor units as integers preserves exact arithmetic and simplifies validation.
• Outbox Pattern: Direct event publishing couples database commits to message broker availability. The outbox table decouples transactional writes from async delivery, guaranteeing at-least-once semantics without distributed transactions.
• Gateway Abstraction: Provider SDKs change frequently. Abstraction isolates breaking changes, enables multi-provider routing, and simplifies compliance audits by centralizing provider communication.

Pitfall Guide

1. Using Floating-Point Types for Currency

Storing monetary values as FLOAT or NUMBER introduces precision loss. 0.1 + 0.2 !== 0.3 in binary floating-point arithmetic. Financial calculations require exact decimal representation. Always use BIGINT (minor units) or DECIMAL/NUMERIC with explicit scale. Validate amounts against provider minimums and maximums before insertion.

2. Ignoring Idempotency at the API Boundary

Retry mechanisms, network timeouts, and client SDKs automatically resend requests. Without idempotency keys, duplicate requests create duplicate charges or ledger entries. Generate keys client-side or at the edge, store them with request metadata, and enforce ON CONFLICT deduplication. Never rely on provider-side idempotency alone.

3. Coupling Business Logic to Gateway SDKs

Importing Stripe, PayPal, or Adyen SDKs directly into domain services creates tight dependencies. Provider updates break your code, and multi-provider routing becomes unmanageable. Abstract gateway interactions behind interfaces. Route requests through a dispatcher that handles provider selection, retry policies, and circuit breaking.

4. Skipping Automated Reconciliation

Manual reconciliation does not scale. Provider reports arrive in CSV, API, or webhook formats. Without automated matching, discrepancies accumulate and trigger chargebacks. Implement a reconciliation worker that ingests provider statements, matches against ledger entries using transaction IDs, and flags mismatches for investigation. Run reconciliation daily and enforce SLAs for discrepancy resolution.

5. Poor Error Handling and Retry Strategies

Payment APIs return transient errors (timeouts, rate limits, temporary failures). Blind retries without exponential backoff and jitter overwhelm providers and increase latency. Implement structured retry policies with idempotency preservation. Log errors with correlation IDs, and route permanent failures to dead-letter queues for manual review.

6. Ignoring Timezone and Settlement Cutoffs

Providers enforce daily settlement cutoffs (e.g., 5 PM EST). Transactions submitted after cutoffs roll to the next business day. Failing to account for cutoffs causes reconciliation drift and cash flow forecasting errors. Normalize all timestamps to UTC, track provider cutoffs as configuration, and schedule settlement jobs accordingly.

7. Inadequate Audit Trails

Compliance frameworks (PCI-DSS, SOC 2, GDPR) require immutable audit logs. Deleting or overwriting payment records violates regulatory requirements. Append-only ledger entries, immutable outbox tables, and structured audit logs ensure traceability. Never update financial records; create reversal entries instead.

Production Bundle

Action Checklist

• Schema Design: Implement double-entry ledger with BIGINT amounts, account tables, and strict CHECK constraints for balance and sign validation.
• Idempotency Enforcement: Add idempotency_keys table with ON CONFLICT deduplication, and inject keys at the API gateway layer before business logic execution.
• Gateway Abstraction: Create PaymentGateway interface, implement provider adapters, and integrate circuit breakers with configurable thresholds and reset timeouts.
• Outbox Pattern: Add outbox table with processed flag, implement async worker to publish events to message broker, and ensure at-least-once delivery semantics.
• Reconciliation Automation: Build daily reconciliation worker to ingest provider statements, match ledger entries, and flag discrepancies with automated alerting.
• Compliance Logging: Enable append-only audit trails, correlation IDs across all payment flows, and immutable ledger entries with reversal-only updates.
• Load Testing: Simulate retry storms, network partitions, and provider outages using chaos engineering tools to validate idempotency and circuit breaker behavior.

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
MVP / Proof of Concept	Gateway-Only with Idempotency Wrapper	Fastest path to market; reduces initial engineering overhead while maintaining basic consistency guarantees	Low upfront, moderate reconciliation cost at scale
Mid-Scale / Multi-Provider	Ledger-First + Single Gateway Abstraction	Decouples business logic, enables provider routing, and automates reconciliation without full multi-provider complexity	Moderate upfront, 40% reduction in reconciliation labor
Enterprise / Regulated	Full Ledger + Multi-Gateway + Outbox + Audit Pipeline	Meets PCI-DSS/SOC 2 requirements, guarantees zero reconciliation drift, and supports high-throughput settlement cycles	High upfront, 90% reduction in compliance audit time
High-Frequency / Microtransactions	In-Memory Ledger + Async Batch Settlement	Reduces database I/O overhead, batches microtransactions for cost efficiency, and maintains eventual consistency	Moderate upfront, 60% reduction in per-transaction DB cost

Configuration Template

// payments.config.ts
export const PAYMENTS_CONFIG = {
  database: {
    connectionString: process.env.DB_URL,
    poolSize: 20,
    idleTimeoutMs: 30000,
    maxUses: 7500
  },
  idempotency: {
    ttlHours: 24,
    cleanupIntervalMinutes: 60,
    keyPrefix: 'pay_'
  },
  gateways: {
    primary: {
      provider: 'STRIPE',
      apiKey: process.env.STRIPE_SECRET,
      webhookSecret: process.env.STRIPE_WEBHOOK_SECRET,
      timeoutMs: 5000,
      retries: 3,
      backoff: { type: 'exponential', baseMs: 500, maxMs: 5000 }
    },
    fallback: {
      provider: 'ADYEN',
      apiKey: process.env.ADYEN_API_KEY,
      timeoutMs: 4000,
      retries: 2,
      backoff: { type: 'linear', stepMs: 1000 }
    }
  },
  ledger: {
    currency: 'USD',
    minorUnits: true,
    reconciliationSchedule: '0 2 * * *', // Daily at 2 AM UTC
    discrepancyThreshold: 0.01
  },
  observability: {
    metricsPrefix: 'payments',
    traceIdHeader: 'X-Trace-Id',
    logLevel: 'info',
    alertChannels: ['pagerduty', 'slack']
  }
};

Quick Start Guide

1. Initialize PostgreSQL Schema: Run the double-entry ledger DDL scripts provided in the Core Solution. Verify constraints with psql -f schema.sql.
2. Deploy Configuration: Copy payments.config.ts into your project root. Set environment variables for DB_URL, STRIPE_SECRET, and STRIPE_WEBHOOK_SECRET.
3. Start Ledger Service: Run npm run dev to launch the TypeScript payment service. The service will connect to PostgreSQL, initialize the idempotency table, and expose /payments endpoint.
4. Test Idempotency: Send identical POST requests with the same idempotencyKey. Verify only one ledger entry is created and the second request returns COMPLETED status.
5. Validate Reconciliation: Trigger the outbox worker and simulate a provider settlement report. Confirm the reconciliation worker matches entries and logs zero discrepancies.