Ledger System¶

Problem Statement¶

Design a ledger system for a financial platform that tracks all money movements with double-entry accounting. The system should be immutable, auditable, and support real-time balance queries while handling high transaction volumes.

Requirements¶

Functional Requirements¶

Record all financial transactions (credits/debits)
Implement double-entry accounting (every transaction has two sides)
Support real-time balance queries
Maintain complete audit trail (immutable)
Support multiple currencies
Handle account holds/reserves
Generate financial reports and statements
Support reconciliation with external systems

Non-Functional Requirements¶

Consistency: Strong consistency for balances (money must never be lost or created)
Durability: Zero data loss
Scalability: Handle millions of entries per day
Auditability: Complete, immutable history
Latency: Balance queries < 50ms

High-Level Architecture¶

Ledger System - High-Level Architecture

Core Concepts¶

Double-Entry Accounting¶

Double-Entry Accounting

Account Structure¶

Account Hierarchy

Core Components¶

1. Account Service¶

Create and manage accounts
Account hierarchy and relationships
Account metadata and settings
Account status (active, frozen, closed)

2. Transaction Service¶

Process financial transactions
Validate double-entry rules
Ensure atomicity
Generate transaction references

3. Balance Service¶

Real-time balance queries
Available vs pending balance
Balance validation
Hold management

4. Ledger Core¶

Enforces double-entry rules
Validates entries sum to zero
Applies business rules
Generates journal entries

5. Reporting Service¶

Account statements
Financial reports
Reconciliation reports
Audit exports

Data Models¶

Account¶

CREATE TABLE accounts (
    id                  UUID PRIMARY KEY,

    -- Hierarchy
    parent_id           UUID REFERENCES accounts(id),
    account_type        VARCHAR(50) NOT NULL,   -- asset, liability, revenue, expense
    account_subtype     VARCHAR(50),            -- cash, receivable, customer_balance

    -- Identification
    code                VARCHAR(50) UNIQUE,     -- ACC-001, for human reference
    name                VARCHAR(255) NOT NULL,
    description         TEXT,

    -- Currency
    currency            VARCHAR(3) NOT NULL,

    -- Status
    status              VARCHAR(20) DEFAULT 'active',

    -- Balance direction (for validation)
    normal_balance      VARCHAR(10) NOT NULL,   -- debit or credit

    -- Constraints
    allow_negative      BOOLEAN DEFAULT false,
    min_balance         BIGINT DEFAULT 0,

    -- Metadata
    metadata            JSONB,
    created_at          TIMESTAMP NOT NULL,
    updated_at          TIMESTAMP NOT NULL
);

CREATE INDEX idx_accounts_type ON accounts(account_type);
CREATE INDEX idx_accounts_parent ON accounts(parent_id);

Ledger Entry (Immutable)¶

CREATE TABLE ledger_entries (
    id                  UUID PRIMARY KEY,

    -- Journal reference (groups related entries)
    journal_id          UUID NOT NULL,

    -- Account
    account_id          UUID NOT NULL REFERENCES accounts(id),

    -- Entry details
    entry_type          VARCHAR(10) NOT NULL,   -- debit or credit
    amount              BIGINT NOT NULL,        -- Always positive, in smallest unit
    currency            VARCHAR(3) NOT NULL,

    -- Running balance (denormalized for performance)
    balance_after       BIGINT NOT NULL,

    -- Description
    description         TEXT,

    -- Timing
    effective_date      DATE NOT NULL,          -- When it counts for reporting
    posted_at           TIMESTAMP NOT NULL,     -- When it was recorded

    -- Immutability (no updates, only appends)
    -- To reverse: create a new reversing entry

    -- Sequence for ordering
    sequence_num        BIGSERIAL,

    CONSTRAINT positive_amount CHECK (amount > 0)
);

-- Append-only: no UPDATE or DELETE triggers
CREATE INDEX idx_entries_account ON ledger_entries(account_id, posted_at DESC);
CREATE INDEX idx_entries_journal ON ledger_entries(journal_id);
CREATE INDEX idx_entries_effective ON ledger_entries(account_id, effective_date);

Journal (Transaction Group)¶

CREATE TABLE journals (
    id                  UUID PRIMARY KEY,

    -- Reference
    reference_type      VARCHAR(50) NOT NULL,   -- payment, refund, transfer, fee
    reference_id        UUID,                   -- External reference (payment_id, etc.)

    -- Description
    description         TEXT NOT NULL,

    -- Validation
    entry_count         INT NOT NULL,
    total_debits        BIGINT NOT NULL,
    total_credits       BIGINT NOT NULL,

    -- Status
    status              VARCHAR(20) NOT NULL,   -- pending, posted, void

    -- Timing
    effective_date      DATE NOT NULL,
    posted_at           TIMESTAMP,
    created_at          TIMESTAMP NOT NULL,
    created_by          UUID,

    -- Ensure balance
    CONSTRAINT balanced CHECK (total_debits = total_credits)
);

CREATE INDEX idx_journals_reference ON journals(reference_type, reference_id);
CREATE INDEX idx_journals_effective ON journals(effective_date);

Balance Cache¶

-- Materialized balance for fast queries
CREATE TABLE account_balances (
    account_id          UUID PRIMARY KEY REFERENCES accounts(id),

    -- Current balance
    balance             BIGINT NOT NULL,
    currency            VARCHAR(3) NOT NULL,

    -- Holds/Reserves
    pending_debits      BIGINT DEFAULT 0,
    pending_credits     BIGINT DEFAULT 0,
    held_amount         BIGINT DEFAULT 0,

    -- Calculated fields
    available_balance   BIGINT GENERATED ALWAYS AS
                        (balance - pending_debits + pending_credits - held_amount) STORED,

    -- Sync tracking
    last_entry_id       UUID,
    last_entry_seq      BIGINT,
    updated_at          TIMESTAMP NOT NULL
);

Key Flows¶

Recording a Transaction¶

Recording a Transaction

Payment Transaction Example¶

public class LedgerService {

    @Transactional(isolation = Isolation.SERIALIZABLE)
    public Journal recordPayment(PaymentRequest request) {
        // 1. Create journal
        Journal journal = Journal.builder()
            .referenceType("payment")
            .referenceId(request.getPaymentId())
            .description("Payment from " + request.getCustomerId())
            .effectiveDate(LocalDate.now())
            .build();

        // 2. Get accounts
        Account customerAccount = accountService.getCustomerAccount(request.getCustomerId());
        Account merchantAccount = accountService.getMerchantAccount(request.getMerchantId());
        Account feeAccount = accountService.getFeeRevenueAccount();

        long amount = request.getAmount();
        long fee = calculateFee(amount);
        long netAmount = amount - fee;

        // 3. Create entries (must balance to zero)
        List<LedgerEntry> entries = List.of(
            // Debit customer (decrease their liability to us)
            LedgerEntry.debit(customerAccount, amount, "Payment to " + request.getMerchantId()),

            // Credit merchant (increase our liability to them)
            LedgerEntry.credit(merchantAccount, netAmount, "Payment received"),

            // Credit fee revenue
            LedgerEntry.credit(feeAccount, fee, "Transaction fee")
        );

        // 4. Validate balance
        long totalDebits = entries.stream()
            .filter(e -> e.getType() == EntryType.DEBIT)
            .mapToLong(LedgerEntry::getAmount)
            .sum();
        long totalCredits = entries.stream()
            .filter(e -> e.getType() == EntryType.CREDIT)
            .mapToLong(LedgerEntry::getAmount)
            .sum();

        if (totalDebits != totalCredits) {
            throw new UnbalancedJournalException(totalDebits, totalCredits);
        }

        // 5. Validate sufficient balance
        for (LedgerEntry entry : entries) {
            if (entry.getType() == EntryType.DEBIT) {
                Account account = entry.getAccount();
                long currentBalance = getBalance(account);
                if (currentBalance < entry.getAmount() && !account.isAllowNegative()) {
                    throw new InsufficientBalanceException(account, currentBalance, entry.getAmount());
                }
            }
        }

        // 6. Calculate balance_after for each entry and persist
        journal.setEntries(entries);
        journal.setTotalDebits(totalDebits);
        journal.setTotalCredits(totalCredits);

        return journalRepository.save(journal);
    }
}

Balance Calculation¶

Real-Time Balance¶

Balance Calculation

Balance Types¶

public class AccountBalance {
    private long balance;           // Current ledger balance
    private long pendingDebits;     // Authorized but not captured
    private long pendingCredits;    // Expected incoming
    private long heldAmount;        // Reserved for disputes, etc.

    public long getAvailableBalance() {
        return balance - pendingDebits + pendingCredits - heldAmount;
    }

    public long getTotalBalance() {
        return balance;
    }
}

Balance Cache Synchronization¶

// Update cache atomically with entry insert
@Transactional
public void postEntry(LedgerEntry entry) {
    // Insert entry
    entryRepository.save(entry);

    // Update balance cache atomically
    int delta = entry.getType() == EntryType.CREDIT ?
                entry.getAmount() : -entry.getAmount();

    balanceRepository.updateBalance(
        entry.getAccountId(),
        delta,
        entry.getId(),
        entry.getSequenceNum()
    );
}

// SQL for atomic update
UPDATE account_balances
SET balance = balance + :delta,
    last_entry_id = :entryId,
    last_entry_seq = :entrySeq,
    updated_at = NOW()
WHERE account_id = :accountId
AND last_entry_seq < :entrySeq;  -- Prevent duplicate updates

Immutability & Corrections¶

Reversal Pattern¶

Immutability and Reversals

Void vs Reversal¶

public Journal voidJournal(UUID journalId, String reason) {
    Journal original = journalRepository.findById(journalId)
        .orElseThrow(() -> new JournalNotFoundException(journalId));

    if (original.getStatus() == JournalStatus.VOID) {
        throw new AlreadyVoidedException(journalId);
    }

    // Create reversing journal
    Journal reversal = Journal.builder()
        .referenceType("reversal")
        .referenceId(original.getId())
        .description("Reversal of " + original.getId() + ": " + reason)
        .effectiveDate(LocalDate.now())
        .build();

    // Reverse each entry
    List<LedgerEntry> reversedEntries = original.getEntries().stream()
        .map(entry -> LedgerEntry.builder()
            .accountId(entry.getAccountId())
            .entryType(entry.getEntryType().opposite())  // Debit ↔ Credit
            .amount(entry.getAmount())
            .currency(entry.getCurrency())
            .description("Reversal: " + entry.getDescription())
            .build())
        .collect(toList());

    reversal.setEntries(reversedEntries);

    // Mark original as void (status only, entries remain)
    original.setStatus(JournalStatus.VOID);
    journalRepository.save(original);

    return journalRepository.save(reversal);
}

Holds and Reserves¶

Hold Management¶

CREATE TABLE account_holds (
    id                  UUID PRIMARY KEY,
    account_id          UUID NOT NULL REFERENCES accounts(id),

    -- Hold details
    amount              BIGINT NOT NULL,
    currency            VARCHAR(3) NOT NULL,
    reason              VARCHAR(50) NOT NULL,   -- authorization, dispute, reserve
    reference_type      VARCHAR(50),
    reference_id        UUID,

    -- Status
    status              VARCHAR(20) NOT NULL,   -- active, released, captured

    -- Timing
    created_at          TIMESTAMP NOT NULL,
    expires_at          TIMESTAMP,
    released_at         TIMESTAMP,

    CONSTRAINT positive_hold CHECK (amount > 0)
);

CREATE INDEX idx_holds_account ON account_holds(account_id, status);

Hold Flow¶

Hold Lifecycle

Multi-Currency Support¶

Currency Handling¶

public class MultiCurrencyLedger {

    // Each currency has separate accounts
    // User has: user_123_usd, user_123_eur, user_123_gbp

    public Journal recordCurrencyConversion(
            UUID userId,
            long sourceAmount,
            String sourceCurrency,
            long targetAmount,
            String targetCurrency,
            BigDecimal exchangeRate
    ) {
        Account sourceAccount = getAccountForCurrency(userId, sourceCurrency);
        Account targetAccount = getAccountForCurrency(userId, targetCurrency);
        Account fxGainsAccount = getFxGainsAccount();

        // Debit source currency
        // Credit target currency
        // Any difference goes to FX gains/losses

        return createJournal(List.of(
            LedgerEntry.debit(sourceAccount, sourceAmount, sourceCurrency,
                "FX conversion to " + targetCurrency),
            LedgerEntry.credit(targetAccount, targetAmount, targetCurrency,
                "FX conversion from " + sourceCurrency)
        ));
    }
}

Exchange Rate Tracking¶

CREATE TABLE exchange_rates (
    id                  UUID PRIMARY KEY,
    source_currency     VARCHAR(3) NOT NULL,
    target_currency     VARCHAR(3) NOT NULL,
    rate                DECIMAL(18, 8) NOT NULL,
    effective_at        TIMESTAMP NOT NULL,
    source              VARCHAR(50) NOT NULL,   -- Provider name

    UNIQUE(source_currency, target_currency, effective_at)
);

Reconciliation¶

Internal Reconciliation¶

-- Verify all journals balance
SELECT j.id, j.total_debits, j.total_credits,
       SUM(CASE WHEN e.entry_type = 'debit' THEN e.amount ELSE 0 END) as actual_debits,
       SUM(CASE WHEN e.entry_type = 'credit' THEN e.amount ELSE 0 END) as actual_credits
FROM journals j
JOIN ledger_entries e ON e.journal_id = j.id
GROUP BY j.id, j.total_debits, j.total_credits
HAVING SUM(CASE WHEN e.entry_type = 'debit' THEN e.amount ELSE 0 END) != j.total_debits
    OR SUM(CASE WHEN e.entry_type = 'credit' THEN e.amount ELSE 0 END) != j.total_credits;

-- Verify cached balances match entry trail
SELECT ab.account_id, ab.balance as cached,
       (SELECT SUM(CASE WHEN entry_type = 'credit' THEN amount ELSE -amount END)
        FROM ledger_entries WHERE account_id = ab.account_id) as calculated
FROM account_balances ab
WHERE ab.balance != (
    SELECT SUM(CASE WHEN entry_type = 'credit' THEN amount ELSE -amount END)
    FROM ledger_entries WHERE account_id = ab.account_id
);

External Reconciliation¶

Bank Reconciliation Process

Scalability¶

Partitioning Strategy¶

Partitioning Strategy

Read Scaling¶

Read Scaling

Technology Choices¶

Component	Technology Options
Primary Database	PostgreSQL (strong consistency)
Balance Cache	Redis
Event Streaming	Kafka (for audit events)
Time-Series Archive	TimescaleDB, ClickHouse
Cold Storage	S3 + Parquet
Reporting	PostgreSQL replicas, Snowflake

Interview Discussion Points¶

How do you ensure no money is lost or created?
Double-entry accounting, all journals must balance, database constraints
How do you handle concurrent balance updates?
Serializable transactions, optimistic locking, sequence numbers
How do you scale for high transaction volume?
Time-based partitioning, sharding, async balance cache updates
How do you handle mistakes without deleting data?
Reversing entries, void journals, complete audit trail
How do you ensure fast balance queries?
Cached balances in Redis, running balance on entries
How do you reconcile with external systems?
Automated matching, reconciliation reports, discrepancy handling