Double-Entry Ledgers

Double-entry bookkeeping is a foundational accounting system where every transaction affects at least two accounts - one debit and one credit - ensuring the books always balance. The monetary extension provides the perfect foundation for building ledger systems with type-safe currency handling.

This guide is inspired by pgledger, a PostgreSQL double-entry ledger implementation by Paul Gross.

Why Use monetary for Ledgers?

1. Currency Safety: Can’t accidentally add USD and EUR
2. Precision: Integer storage avoids floating-point errors
3. Validation: Built-in currency validation on input
4. Aggregation: SUM, AVG with currency mismatch detection

Basic Ledger Schema

DuckDB

-- Accounts: the fundamental unit of a ledger CREATE TABLE accounts ( id VARCHAR PRIMARY KEY, name VARCHAR NOT NULL, account_type VARCHAR NOT NULL, -- 'asset', 'liability', 'equity', 'revenue', 'expense' currency VARCHAR(3) NOT NULL, allow_negative BOOLEAN DEFAULT false, created_at TIMESTAMP DEFAULT current_timestamp ); -- Ledger entries: every transaction has 2+ entries that sum to zero CREATE TABLE ledger_entries ( id INTEGER PRIMARY KEY, transaction_id VARCHAR NOT NULL, account_id VARCHAR NOT NULL REFERENCES accounts(id), amount monetary NOT NULL, description VARCHAR, created_at TIMESTAMP DEFAULT current_timestamp ); -- Insert sample accounts INSERT INTO accounts VALUES ('cash', 'Cash', 'asset', 'USD', false), ('revenue', 'Sales Revenue', 'revenue', 'USD', true), ('ar', 'Accounts Receivable', 'asset', 'USD', false), ('inventory', 'Inventory', 'asset', 'USD', false); SELECT * FROM accounts;

Run Reset

Recording Transactions

In double-entry bookkeeping, every transaction must balance - debits equal credits.

DuckDB

-- Record a sale: $100 cash received, $100 revenue recognized INSERT INTO ledger_entries VALUES (1, 'TXN001', 'cash', '100 USD', 'Cash received'), (2, 'TXN001', 'revenue', '-100 USD', 'Sale recorded'); -- Record inventory purchase: $50 inventory bought with cash INSERT INTO ledger_entries VALUES (3, 'TXN002', 'inventory', '50 USD', 'Inventory purchased'), (4, 'TXN002', 'cash', '-50 USD', 'Cash paid'); SELECT * FROM ledger_entries;

Run Reset

Validating Balance

A key invariant: all entries for a transaction must sum to zero.

DuckDB

-- Check that each transaction balances SELECT transaction_id, monetary_sum(amount) as net_amount FROM ledger_entries GROUP BY transaction_id;

Run Reset

Account Balances

Calculate current balance for each account:

DuckDB

-- Current balance per account SELECT a.id, a.name, a.account_type, COALESCE(monetary_sum(e.amount), make_monetary(0, a.currency)) as balance FROM accounts a LEFT JOIN ledger_entries e ON a.id = e.account_id GROUP BY a.id, a.name, a.account_type, a.currency ORDER BY a.account_type;

Run Reset

Multi-Currency Ledger

Handle multiple currencies with proper isolation:

DuckDB

-- Add EUR accounts INSERT INTO accounts VALUES ('cash_eur', 'Cash (EUR)', 'asset', 'EUR', false), ('revenue_eur', 'Sales Revenue (EUR)', 'revenue', 'EUR', true); -- EUR transaction INSERT INTO ledger_entries VALUES (5, 'TXN003', 'cash_eur', '200 EUR', 'EUR payment received'), (6, 'TXN003', 'revenue_eur', '-200 EUR', 'EUR sale recorded'); -- Balances by currency SELECT monetary_currency(amount) as currency, monetary_sum(amount) as total FROM ledger_entries GROUP BY monetary_currency(amount);

Run Reset

Currency Conversion Transactions

When converting between currencies, use a currency exchange account:

-- Add exchange account
INSERT INTO accounts VALUES
    ('fx_gains', 'FX Gains/Losses', 'revenue', 'USD', true);

-- Convert 100 EUR to USD at 1.085 rate
-- This requires 4 entries to maintain balance in both currencies:
-- 1. Reduce EUR cash
-- 2. Close EUR to exchange account
-- 3. Open USD from exchange account
-- 4. Increase USD cash

INSERT INTO ledger_entries VALUES
    -- EUR side
    (7, 'FX001', 'cash_eur', '-100 EUR', 'EUR converted'),
    -- USD side (100 EUR * 1.085 = 108.50 USD)
    (8, 'FX001', 'cash', '108.50 USD', 'USD received from FX');

Trial Balance Report

Generate a trial balance showing all accounts:

DuckDB

-- Trial balance view WITH balances AS ( SELECT a.id, a.name, a.account_type, COALESCE(monetary_sum(e.amount), make_monetary(0, a.currency)) as balance FROM accounts a LEFT JOIN ledger_entries e ON a.id = e.account_id GROUP BY a.id, a.name, a.account_type, a.currency ) SELECT name, account_type, CASE WHEN monetary_amount(balance) >= 0 THEN balance ELSE make_monetary(0, monetary_currency(balance)) END as debit, CASE WHEN monetary_amount(balance) < 0 THEN monetary_convert(balance, monetary_currency(balance), -1) ELSE make_monetary(0, monetary_currency(balance)) END as credit FROM balances ORDER BY account_type, name;

Run Reset

Immutability Pattern

Ledgers should be append-only. Use soft deletes via reversal entries:

-- Instead of deleting entry 1, create a reversal
INSERT INTO ledger_entries VALUES
    (9, 'REV001', 'cash', '-100 USD', 'Reversal of TXN001'),
    (10, 'REV001', 'revenue', '100 USD', 'Reversal of TXN001');

Performance Tips

1. Index account_id: Most queries filter by account
2. Partition by date: For large ledgers, partition by month/year
3. Materialized balances: Cache current balances for frequently accessed accounts
4. Batch inserts: Use transactions for multi-entry operations

-- Useful indexes
CREATE INDEX idx_entries_account ON ledger_entries(account_id);
CREATE INDEX idx_entries_txn ON ledger_entries(transaction_id);
CREATE INDEX idx_entries_date ON ledger_entries(created_at);

Comparison with pgledger

Feature	pgledger	monetary + custom
Database	PostgreSQL only	DuckDB + PostgreSQL
Currency handling	String-based	Type-safe monetary
Multi-currency	Manual validation	Built-in currency checks
Exchange rates	Not included	10 built-in sources
Constraints	SQL checks	Type system enforced

The monetary extension provides the currency safety layer, while you build the ledger logic on top.

Further Reading

• pgledger on GitHub - PostgreSQL ledger implementation
• Double-Entry Ledgers: The Missing Primitive - Paul Gross’s blog
• Building a Financial Ledger - Performance considerations