A double-entry ledger is not a single table. It is a relational structure designed to enforce the equation Assets = Liabilities + Equity. While a naive implementation uses a balance column on a user table, a robust schema separates the Event (The Transaction) from the Impact (The Journal Entries).
The Core Schema Artifacts
Accounts Table: Defines the entities (id, type, currency, direction: debit/credit).
Transactions Table: The parent event (id, reference, timestamp, description).
Entries (or Lines) Table: The children of the transaction.
Columns: id, transaction_id (FK), account_id (FK), amount, direction.
Constraint: A transaction insert logic must ensure SUM(entries.amount) WHERE direction='credit' == SUM(entries.amount) WHERE direction='debit'.
Locking and ACID Compliance
Writing to the ledger must be an atomic transaction.
DB Transaction: The INSERT INTO transactions and the multiple INSERT INTO entries must occur within a single BEGIN...COMMIT block. If any insert fails, the whole block rolls back.
Row Locking: When calculating a balance (SELECT SUM), use FOR UPDATE clauses if you plan to write immediately after, preventing race conditions where two processes spend the same funds.
