System Design: Designing a Financial Ledger with Double-Entry Constraints

Scenario context

Designing a financial ledger requires strict double-entry compliance, high consistency, and complete auditability. Unlike traditional databases where records are updated in-place, a financial ledger must be append-only to preserve a verifiable transaction history. This guide details how to architect a scalable ledger system using relational storage, idempotency mechanisms, and active-active isolation strategies with…

This walkthrough focuses on Trade-offs and Failure Modes: Distributed Locks vs. Optimistic Locking.

Before making a design choice, clarify:

• When designing ledger synchronization mechanics, architects must choose between optimistic concurrency control and pessimistic locking.
• Pessimistic Locking Pros: Highly secure; prevents double-spend at the database layer; easy to reason about.
• Cons: High latency; prone to deadlocks if accounts are locked out of order; limits transaction throughput.

Concepts in play: caching, system-design-hld-architectures, consistency-replication, database-indexing

Stage 1 — Practical Implementation: Ledger Database Schema and API Designs

• To implement an append-only ledger, we define three core tables in a relational database: Accounts, Transactions, and Entries.
• Database Schema Definitions The layout below defines the schema of the three essential tables, including primary keys, data types, and integrity constraints: Accounts Table: Tracks account metadata, owners, and types.
• id : VARCHAR(36) [Primary Key] - Unique UUID identifying the account.
• owner id : VARCHAR(36) [Indexed] - Identifies the customer or merchant owning the account.

Observable outcome: owner id : VARCHAR(36) [Indexed] - Identifies the customer or merchant owning the account.

Stage 2 — Related Posts

• System Design: Distributed Transactions System Design: Replication and Failover

Observable outcome: System Design: Distributed Transactions System Design: Replication and Failover

End-to-end outcome

After working through the stages, you should be able to explain:

• Never Use Floating-Point Data Types: Floating-point numbers (e.g., FLOAT, DOUBLE) suffer from binary rounding errors.
• A series of additions and subtractions can lead to minor fractional balance differences.
• Append-Only Only: Financial ledgers must be write-only logs.
• Never perform in-place updates on balances.

Alternatives considered

Decision Guide: SQL Databases vs. Event-Sourced Immutable Ledgers

The table below provides a decision matrix to help select the correct ledger storage architecture:

Failure and degraded modes

When designing ledger synchronization mechanics, architects must choose between optimistic concurrency control and pessimistic locking.

• Detection: When designing ledger synchronization mechanics, architects must choose between optimistic concurrency control and pessimistic locking.
• User or operator impact: When designing ledger synchronization mechanics, architects must choose between optimistic concurrency control and pessimistic locking.

Pessimistic Locking

• Detection: Pros: Highly secure; prevents double-spend at the database layer; easy to reason about.
• User or operator impact: Pros: Highly secure; prevents double-spend at the database layer; easy to reason about.

Optimistic Concurrency Control (OCC)

• Detection: Pros: Non-blocking reads; higher throughput under low contention.
• User or operator impact: Pros: Non-blocking reads; higher throughput under low contention.