How do you design a double-entry ledger in SQL?

A double-entry ledger needs three tables. ledger_accounts has one row per account per currency, and that includes your own internal accounts, such as cash at the payment processor or fee revenue. journal_entries has one row per business event, carrying an idempotency key and a pointer to the entry it reverses. postings has one row per leg: an account, a currency and a signed amount. The rule that makes it double entry is that the postings of one journal entry sum to zero in every currency. In PostgreSQL the database can enforce that rule itself with a deferred constraint trigger that checks each entry at commit.

Most schemas that call themselves a ledger are not double entry. Of the 14 schemas in the drawDB gallery that keep a money or stock ledger, only two are: banking-ledger and in-game-economy. The other twelve record one row per movement on one account, which is single entry. That is often enough, and the last section says when. This post builds the double-entry version and tests it on PostgreSQL 16 and MySQL 8.0.

What tables does a double-entry ledger need?

Here is the core, written for PostgreSQL. It runs as is.

CREATE TABLE ledger_accounts (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code varchar(40) NOT NULL UNIQUE,
  account_type varchar(10) NOT NULL
    CHECK (account_type IN ('asset', 'liability', 'equity', 'revenue', 'expense')),
  currency_code char(3) NOT NULL,
  owner_user_id bigint,
  UNIQUE (id, currency_code)
);

CREATE TABLE journal_entries (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  idempotency_key varchar(64) NOT NULL UNIQUE,
  description varchar(255) NOT NULL,
  effective_date date NOT NULL DEFAULT current_date,
  reversal_of_id bigint UNIQUE REFERENCES journal_entries (id),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE postings (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  journal_entry_id bigint NOT NULL REFERENCES journal_entries (id),
  account_id bigint NOT NULL,
  currency_code char(3) NOT NULL,
  amount_minor bigint NOT NULL CHECK (amount_minor <> 0),
  FOREIGN KEY (account_id, currency_code)
    REFERENCES ledger_accounts (id, currency_code)
);

CREATE INDEX idx_postings_journal ON postings (journal_entry_id);
CREATE INDEX idx_postings_account ON postings (account_id, journal_entry_id);

Each line is there for a reason:

• The composite foreign key on (account_id, currency_code) makes it impossible to post euros to a dollar account. The UNIQUE (id, currency_code) on accounts looks redundant, since id is already unique, but a foreign key can only point at a unique constraint covering exactly its columns. banking-ledger states the same rule in a column note ("must match the account currency"). Here the database enforces it.
• amount_minor <> 0 drops empty legs, so a balanced entry always has at least two postings in each currency it touches.
• idempotency_key is unique on the header, so a retried request finds the existing entry instead of posting twice. Both double-entry gallery schemas have this: transaction_reference in banking-ledger, idempotency_key in in-game-economy.
• reversal_of_id is unique, so an entry can be reversed once and only once.
• effective_date is separate from created_at. banking-ledger calls it value_date: the date the money counts for, which is not always the date you wrote the row.

There is no balance column here. A cached balance next to the ledger is a separate decision with its own concurrency traps, covered in should you store an account balance or calculate it. Everything below works with or without one.

Where does the other side of every entry go?

Into your own accounts. This is what separates double entry from a list of balance changes. When a customer tops up a wallet by card, the money does not come from nowhere. Your balance at the card processor goes up, and so does what you owe the customer. Both are accounts in the same table, and the entry has one leg on each.

banking-ledger keeps them in one table with account_type = 'internal', and its notes say why: "Internal accounts are the bank side of every double-entry pair." in-game-economy does the same with wallets whose player_id is null and whose account_kind is mint, sink, escrow or promo. Granting a player 100 gems debits the mint and credits the player. Spending them moves them to a sink. As the schema notes put it, currency is "never created or destroyed mid-air", and total currency in circulation is one query.

Compare venmo-p2p, which gets halfway. A payment between two users writes two wallet_entries rows that sum to zero. A top_up or a fee writes one row, because the other side, the card network or the fee income, is not an account in the schema. That is fine for a wallet product, but no query can prove that the money in the wallets matches the money at the bank.

Should postings store debit and credit, or a signed amount?

Store a signed amount. The balancing rule is then a plain SUM(amount_minor) = 0, and every balance is a sum with no CASE expression. Pick one convention and write it in the column note. In this post, debits are positive and credits are negative, the usual accounting order.

The two double-entry gallery schemas chose differently, which shows why the note matters. banking-ledger stores direction (debit or credit) beside an always-positive amount_minor, "so reports read naturally without interpreting a sign." in-game-economy stores a signed delta_minor where "positive credits the wallet": the player's point of view, the reverse of the convention above. Both are internally consistent. A query copied from one into the other would be wrong.

If accountants or auditors read the raw table, you can have both. A generated column derives the label, and it can never disagree with the sign:

ALTER TABLE postings ADD COLUMN direction varchar(6)
  GENERATED ALWAYS AS (CASE WHEN amount_minor > 0 THEN 'debit' ELSE 'credit' END) STORED;

Stored generated columns need PostgreSQL 12 or later (docs).

Why not one row with a debit account and a credit account?

Because real entries have more than two legs. A row shaped like (debit_account_id, credit_account_id, amount) balances by construction, which is why it keeps coming up, in an r/Database thread and in the accepted Stack Overflow answer on the topic. It works until Alice pays Bob $30 and pays a $0.90 card fee. That one event has three legs: Alice down $30.90, Bob up $30.00, fee revenue up $0.90. The pair model has to split it into two rows and pretend that $0.90 of Alice's payment is a separate event. Payroll is worse: one salary payment touches gross pay, several tax liabilities and the bank account.

The postings model takes any number of legs and makes the balance rule a constraint rather than a shape. Square's Books service (2019) uses the same three-table split: books, journal entries, and book entries that hold "either a debit (+) amount or a credit (-) amount".

How do you make the database reject an unbalanced entry?

With a constraint trigger that runs at commit. A CHECK constraint sees one row at a time, so it cannot compare a posting with its siblings. A normal trigger would fire after the first leg, before the second one exists. A constraint trigger declared DEFERRABLE INITIALLY DEFERRED waits until COMMIT, when every leg is in place:

CREATE FUNCTION check_journal_balanced() RETURNS trigger AS $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM postings
    WHERE journal_entry_id = NEW.journal_entry_id
    GROUP BY currency_code
    HAVING SUM(amount_minor) <> 0
  ) THEN
    RAISE EXCEPTION 'journal entry % does not balance', NEW.journal_entry_id;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER postings_balanced
AFTER INSERT ON postings
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE FUNCTION check_journal_balanced();

The GROUP BY currency_code matters: an entry with +5,000 USD and −5,000 EUR sums to zero overall, yet it takes dollars in and pays euros out with nothing in between, so it must fail. We tested this on PostgreSQL 16. An entry that posted Alice's $30.90 and Bob's $30.00 but forgot the fee leg failed at COMMIT with journal entry 2 does not balance. Nothing from that transaction was kept, and the entry committed before it was untouched.

One gap remains: a header with no postings at all passes, because there is no posting to fire the trigger. A second deferred trigger on the header closes it:

CREATE FUNCTION check_journal_has_postings() RETURNS trigger AS $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM postings WHERE journal_entry_id = NEW.id) THEN
    RAISE EXCEPTION 'journal entry % has no postings', NEW.id;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER journal_entries_not_empty
AFTER INSERT ON journal_entries
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE FUNCTION check_journal_has_postings();

Constraint triggers have to be row-level, so the check runs once per leg: three index lookups for a three-leg entry, which the idx_postings_journal index makes cheap. To fail earlier, for example before calling a payment provider, run SET CONSTRAINTS ALL IMMEDIATE inside the transaction and the pending checks fire at that point (SET CONSTRAINTS).

The checks only cover inserts. Make postings append-only too, either by revoking UPDATE and DELETE from the application's role or with a trigger. The account balance post has the trigger.

How do you post a journal entry?

Insert the header and its legs in one statement. A data-modifying CTE returns the new entry id, and the legs are looked up by account code:

WITH entry AS (
  INSERT INTO journal_entries (idempotency_key, description)
  VALUES ('pay-91c2', 'Alice pays Bob $30, $0.90 fee')
  RETURNING id
)
INSERT INTO postings (journal_entry_id, account_id, currency_code, amount_minor)
SELECT entry.id, a.id, a.currency_code, p.amount_minor
FROM entry
CROSS JOIN (VALUES
  ('wallet:alice:usd',  3090),
  ('wallet:bob:usd',   -3000),
  ('revenue:fees:usd',   -90)
) AS p (code, amount_minor)
JOIN ledger_accounts a ON a.code = p.code;

Read the signs from the business's side. A customer wallet is money you owe, a liability, so it grows with credits. Alice's wallet is debited 3,090 because you now owe her less. Bob's is credited 3,000, and the fee is credited to revenue. The legs sum to zero, so the commit succeeds.

Account codes like wallet:alice:usd are worth having even with numeric ids. They make posting code readable, and they make a trial balance something a person can check by eye.

How do you handle multiple currencies?

Give every account one currency and balance each entry per currency. Converting currency then needs an account to hold the exchange, because dollars cannot net against euros. banking-ledger's notes describe it: "FX becomes a transaction with entries in two currencies against an FX position account". Alice converting $50.00 to €46.00 is four legs:

WITH entry AS (
  INSERT INTO journal_entries (idempotency_key, description)
  VALUES ('fx-55d0', 'Alice converts $50.00 to €46.00')
  RETURNING id
)
INSERT INTO postings (journal_entry_id, account_id, currency_code, amount_minor)
SELECT entry.id, a.id, a.currency_code, p.amount_minor
FROM entry
CROSS JOIN (VALUES
  ('wallet:alice:usd',  5000),
  ('fx:position:usd',  -5000),
  ('fx:position:eur',   4600),
  ('wallet:alice:eur', -4600)
) AS p (code, amount_minor)
JOIN ledger_accounts a ON a.code = p.code;

USD sums to zero and so does EUR, so the trigger accepts it. The two FX position accounts now show what you converted, and revaluing them against a market rate is a later entry of its own.

in-game-economy takes the stricter route: "A transaction never mixes currencies; a currency exchange is two transactions linked by reference." That keeps the balance check a single SUM per entry, at the cost of an exchange being two entries that have to be read together. For an exchange with real money and a spread, prefer the four-leg entry. The exchange is one event, and it should be one row in the journal.

How do you read balances and a trial balance?

A balance is a sum of postings. To show it the way an accountant expects, flip the sign for accounts that grow with credits (liabilities, equity and revenue):

SELECT a.code, a.account_type, a.currency_code,
       SUM(p.amount_minor) AS debit_minus_credit,
       CASE WHEN a.account_type IN ('asset', 'expense')
            THEN SUM(p.amount_minor)
            ELSE -SUM(p.amount_minor) END AS balance_minor
FROM ledger_accounts a
JOIN postings p ON p.account_id = a.id
GROUP BY a.id, a.code, a.account_type, a.currency_code
ORDER BY a.currency_code, a.code;

After the $100 top-up, the payment and the exchange, this is the actual output:

Alice has $19.10 and €46.00, Bob has $30.00, you earned $0.90, and the processor holds the $100.00 that backs all of it. The trial balance, the check that the books balance at all, is then one line: SELECT currency_code, SUM(amount_minor) FROM postings GROUP BY currency_code must return zero for every currency, and it did. A balance as of a date adds a join to journal_entries and a filter on effective_date. For statements and hot balances, see the running balance and cached balance patterns.

How do you correct a posted entry?

Post a reversal: a new entry with every leg negated, pointing back at the original. The unique reversal_of_id means a second reversal of the same entry fails with a duplicate key error:

WITH entry AS (
  INSERT INTO journal_entries (idempotency_key, description, reversal_of_id)
  SELECT 'reverse:' || idempotency_key, 'Reversal: ' || description, id
  FROM journal_entries
  WHERE idempotency_key = 'pay-91c2'
  RETURNING id, reversal_of_id
)
INSERT INTO postings (journal_entry_id, account_id, currency_code, amount_minor)
SELECT entry.id, p.account_id, p.currency_code, -p.amount_minor
FROM entry
JOIN postings p ON p.journal_entry_id = entry.reversal_of_id;

Negating a balanced entry gives a balanced entry, so the trigger accepts it, and Alice's balance returned to $50.00 in our test. Never edit the original. banking-ledger (reversal_of_id) and in-game-economy (reverses_transaction_id) both keep the mistake and its correction side by side, which is what a support agent or an auditor needs to see. A partial refund is a new entry with its own legs, not a reversal.

How do you do it in MySQL?

MySQL has nothing like a constraint trigger. Triggers are row-level and fire inside the statement, and InnoDB checks even foreign keys immediately (only NDB Cluster defers them), so nothing can check an entry at commit. The tables carry over unchanged, including the composite foreign key, which MySQL 8.0.35 enforced in our test. The balance check moves into the posting transaction, before COMMIT:

START TRANSACTION;

INSERT INTO journal_entries (idempotency_key, description)
VALUES ('pay-91c2', 'Alice pays Bob $30, $0.90 fee');
SET @entry_id = LAST_INSERT_ID();

INSERT INTO postings (journal_entry_id, account_id, currency_code, amount_minor)
SELECT @entry_id, a.id, a.currency_code, p.amount_minor
FROM ledger_accounts a
JOIN (
  SELECT 'wallet:alice:usd' AS code, 3090 AS amount_minor
  UNION ALL SELECT 'wallet:bob:usd', -3000
  UNION ALL SELECT 'revenue:fees:usd', -90
) AS p ON p.code = a.code;

SELECT currency_code, SUM(amount_minor) AS imbalance
FROM postings
WHERE journal_entry_id = @entry_id
GROUP BY currency_code
HAVING SUM(amount_minor) <> 0;

COMMIT;

If the check returns any row, or the insert's ROW_COUNT() is lower than the number of legs you meant to write, roll back instead of committing. Put this in the one function that every posting goes through, and schedule the trial balance query so it catches anything that slipped past. The MySQL export of banking-ledger gives you the full set of tables.

When is single-entry enough?

When you only answer for one party's balance and the counterparties are outside your system. expense-tracker records a user's own bank accounts: the coffee shop is not an account, so a purchase is one row, and only transfers between the user's accounts get two legs linked by transfer_id. stripe-payments keeps one balance_transactions journal per merchant, where every charge, refund, fee and payout lands as one row with gross, fee and net, and the merchant balance is a single sum.

Move to double entry when you hold money that belongs to someone else, issue your own currency, or need to prove that what users hold equals what is in the bank. These are the conditions that produced banking-ledger and in-game-economy. Adding internal accounts later means re-deriving the other side of every historical row, so if any of them is on your roadmap, start with postings.

Fintech

Banking ledger database schema (double-entry)

A double-entry bank ledger: accounts in a single currency each, balanced journal entries that are never updated, card authorizations that hold funds, transfers kept separate from postings, and frozen statements.

Gaming

In-game economy and virtual currency database schema

How a game backend models virtual currency: hard and soft currencies, player wallets, an append-only double-entry ledger of every source and sink, a personalised item shop, validated store receipts, refunds and a battle pass.

Open banking-ledger in the editor for the full design with internal accounts, holds, transfers and statements, or in-game-economy for mint and sink accounts, then export the DDL for your database.

Frequently asked questions