Double-Entry Ledger Database Design in PostgreSQL

A double-entry ledger in PostgreSQL needs three tables: accounts, journal entries and postings. Every entry's postings must sum to zero per currency, enforced by a deferred constraint trigger. Amounts are bigint minor units, each entry carries a unique idempotency key, and nothing is ever updated or deleted, only reversed. The full schema, the checks and a TypeScript posting function are below.

If you would rather have it built for you, see how RAITHub would build this below.

This is the data model behind wallets, marketplace balances, credit limits and billing. The wider payment patterns around it, such as webhooks, server-side pricing and reconciliation, are in the payments engineering pillar, and the service overview is on the FinTech page.

What is a double-entry ledger, in database terms?

A set of accounts, and a log of transactions where each transaction moves value from at least one account to at least one other, with debits equal to credits. Square's engineering team describes the rule for its Books ledger as "each cent lost is matched with a cent gained". Modern Treasury's ledger write-up puts it as "money cannot be moved without specifying the source and destination of funds".

Three terms, defined once:

Term	What it is	Example
Account	A pool of value in one currency, with a type (asset, liability, equity, revenue, expense)	A user's wallet; your bKash clearing account; fee revenue
Journal entry	One atomic money event, with a description, an effective time and an idempotency key	"Top-up via bKash, provider ref ABC123"
Posting	One line of an entry: an account and a signed amount	Clearing +5,000; wallet −5,000 (minor units)

The payoff is that errors become detectable. If every entry sums to zero, the whole ledger sums to zero, and any account that disagrees with its postings points straight at the bug.

What does the PostgreSQL schema look like?

Below is a minimal schema. Postings use one signed column: positive is a debit, negative is a credit. That makes the balance rule a plain SUM. Each account also records its normal side, so the database can refuse overdrafts on accounts that must never go negative.

CREATE TABLE accounts (
  id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code            text    NOT NULL UNIQUE,           -- e.g. 'wallet:42:BDT'
  type            text    NOT NULL CHECK (type IN ('asset','liability','equity','revenue','expense')),
  normal_side     text    NOT NULL CHECK (normal_side IN ('debit','credit')),
  currency        char(3) NOT NULL,                  -- ISO 4217 code
  balance_minor   bigint  NOT NULL DEFAULT 0,        -- debit-positive; written only by posting
  allow_overdraft boolean NOT NULL DEFAULT false,
  UNIQUE (id, currency),
  CHECK (allow_overdraft
         OR (normal_side = 'debit'  AND balance_minor >= 0)
         OR (normal_side = 'credit' AND balance_minor <= 0))
);

CREATE TABLE journal_entries (
  id                bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  idempotency_key   text        NOT NULL UNIQUE,
  description       text        NOT NULL,
  reverses_entry_id bigint      UNIQUE REFERENCES journal_entries (id),
  effective_at      timestamptz NOT NULL DEFAULT now(),
  created_at        timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE postings (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  entry_id     bigint  NOT NULL REFERENCES journal_entries (id),
  account_id   bigint  NOT NULL,
  currency     char(3) NOT NULL,
  amount_minor bigint  NOT NULL CHECK (amount_minor <> 0),  -- + debit, - credit
  FOREIGN KEY (account_id, currency) REFERENCES accounts (id, currency)
);
CREATE INDEX postings_account_idx ON postings (account_id, entry_id);
CREATE INDEX postings_entry_idx   ON postings (entry_id);

The composite foreign key on (account_id, currency) means a posting cannot land on an account in a different currency. The UNIQUE on reverses_entry_id means an entry can be reversed at most once.

How do you guarantee every transaction balances?

With a deferred constraint trigger. A normal CHECK constraint sees one row at a time, but balance is a property of all the postings in an entry. The PostgreSQL CREATE TRIGGER documentation says constraint triggers "can be fired either at the end of the statement causing the triggering event, or at the end of the containing transaction". Declared INITIALLY DEFERRED, the check runs at COMMIT, after all postings are inserted.

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

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

Because every amount is non-zero and each currency must sum to zero, an entry automatically has at least two postings. The check is per currency, so a multi-currency entry must balance in each currency separately. The trigger fires once per inserted row; for entries with many lines, a statement-level variant or an application-side pre-check (shown below) keeps the cost down.

Why store money as bigint minor units?

Because floating-point is inexact and integers are not. PostgreSQL's numeric types documentation warns that floating-point values "are stored as approximations" and recommends exact types for monetary amounts. Its range for bigint runs to 9,223,372,036,854,775,807, which in cents is far beyond any realistic balance.

Type	Use it for	Trade-off
bigint minor units	Posted amounts: cents, poisha, pence	Fast and exact; you must know each currency's minor-unit exponent
numeric	FX rates, interest rates, fractional unit prices	Exact, slower; still round to minor units before posting
real or double precision	Never for money	Rounding drift that breaks the zero-sum check

Minor units differ by currency: most have two decimal places, some have none and some have three. Keep the exponent per currency in a reference table based on ISO 4217, and convert only at the edges: API input and display.

How do idempotency keys work in a ledger?

Every entry carries a key derived from the event that caused it, such as "topup:" plus the provider's transaction ID. The UNIQUE constraint on journal_entries.idempotency_key means the same event can be posted once, however many times a webhook or a retry delivers it. The general pattern is in idempotency in API design. Derive keys from the business event, not from a random value generated per attempt, or retries will create new keys and post twice.

How do you correct a mistake without editing history?

Post a reversal: a new entry with the same postings and opposite signs, linked through reverses_entry_id, and then post the correct entry. Square's write-up says Books has "no update statements" on its journal tables, only inserts. Enforce the same in PostgreSQL with a trigger, and revoke UPDATE and DELETE from the application's database role:

CREATE FUNCTION forbid_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION '% is append-only', TG_TABLE_NAME;
END $$;

CREATE TRIGGER journal_entries_append_only
  BEFORE UPDATE OR DELETE ON journal_entries
  FOR EACH ROW EXECUTE FUNCTION forbid_change();
CREATE TRIGGER postings_append_only
  BEFORE UPDATE OR DELETE ON postings
  FOR EACH ROW EXECUTE FUNCTION forbid_change();

REVOKE UPDATE, DELETE, TRUNCATE ON journal_entries, postings FROM app_role;

Row triggers do not fire on TRUNCATE, which is why the REVOKE matters too. A table owner or superuser can still bypass both, so keep migrations and the application on separate roles. For tamper evidence on top of this, a hash-chained audit log is described in SaaS audit log design.

Should balances be running totals or calculated on read?

Both are valid; the choice depends on read volume and how strictly you need to stop overdrafts. The schema above uses running totals updated in the same transaction as the postings, so the overdraft CHECK can act on them.

Strategy	How it works	Choose it when	Risk
Calculate on read	SUM(postings) per account on every request	Low volume, reporting, early MVPs	Slow for busy accounts; cannot enforce overdraft rules on its own
Running total column	balance_minor updated with a row lock inside the posting transaction	Wallets and credit limits that must reject overspends	Hot accounts serialise writes; drift if anything else writes the column
Materialised snapshots	Periodic balance snapshots, or a materialized view, plus postings since the snapshot	Statements and analytics over long histories	A materialized view is only as fresh as its last refresh

Whichever you choose, run the checks below on a schedule. Each query should return no rows, except the last, which should show zero for every currency.

-- 1. Every entry balances in each currency
SELECT entry_id, currency, sum(amount_minor) AS diff
FROM postings
GROUP BY entry_id, currency
HAVING sum(amount_minor) <> 0;

-- 2. Every stored balance matches its postings
SELECT a.id, a.code, a.balance_minor,
       coalesce(sum(p.amount_minor), 0) AS derived
FROM accounts a
LEFT JOIN postings p ON p.account_id = a.id
GROUP BY a.id, a.code, a.balance_minor
HAVING a.balance_minor <> coalesce(sum(p.amount_minor), 0);

-- 3. The whole ledger sums to zero per currency
SELECT currency, sum(amount_minor) AS total
FROM postings
GROUP BY currency;

How do you post a transaction from TypeScript?

In one database transaction: insert the entry under its idempotency key, insert the postings, update the running balances in a fixed account order to avoid deadlocks, and commit. The deferred trigger checks balance at COMMIT; the account CHECK rejects overdrafts at UPDATE. This uses node-postgres.

import type { Pool } from 'pg'

export type Posting = { accountId: string; currency: string; amountMinor: bigint } // + debit, - credit
export type NewEntry = { idempotencyKey: string; description: string; postings: Posting[] }

export async function postTransaction(db: Pool, entry: NewEntry): Promise<string> {
  // Fail fast in the app; the deferred trigger is the real guarantee.
  const sums = new Map<string, bigint>()
  for (const p of entry.postings) {
    if (p.amountMinor === 0n) throw new Error('Zero-amount posting')
    sums.set(p.currency, (sums.get(p.currency) ?? 0n) + p.amountMinor)
  }
  for (const [currency, total] of sums) {
    if (total !== 0n) throw new Error('Entry does not balance in ' + currency)
  }

  const client = await db.connect()
  try {
    await client.query('BEGIN')
    const inserted = await client.query(
      'INSERT INTO journal_entries (idempotency_key, description) VALUES ($1, $2) ON CONFLICT (idempotency_key) DO NOTHING RETURNING id',
      [entry.idempotencyKey, entry.description],
    )
    if (inserted.rowCount === 0) {
      // Already posted: return the original entry, move no money.
      const prior = await client.query('SELECT id FROM journal_entries WHERE idempotency_key = $1', [entry.idempotencyKey])
      await client.query('ROLLBACK')
      return prior.rows[0].id
    }
    const entryId: string = inserted.rows[0].id

    // Same lock order in every transaction, so two transfers cannot deadlock.
    const byId = (a: Posting, b: Posting) => {
      const x = BigInt(a.accountId), y = BigInt(b.accountId)
      return x < y ? -1 : x > y ? 1 : 0
    }
    const ordered = [...entry.postings].sort(byId)
    for (const p of ordered) {
      await client.query(
        'INSERT INTO postings (entry_id, account_id, currency, amount_minor) VALUES ($1, $2, $3, $4)',
        [entryId, p.accountId, p.currency, p.amountMinor.toString()],
      )
      // Takes a row lock; the CHECK on accounts rejects an overdraft here.
      await client.query(
        'UPDATE accounts SET balance_minor = balance_minor + $1 WHERE id = $2',
        [p.amountMinor.toString(), p.accountId],
      )
    }
    await client.query('COMMIT') // the deferred balance trigger runs here
    return entryId
  } catch (err) {
    await client.query('ROLLBACK')
    throw err
  } finally {
    client.release()
  }
}

Two notes. If the same account appears in two postings of one entry, the sort still gives a stable order and the second UPDATE simply reuses the lock. If two requests race on one idempotency key, PostgreSQL makes the second INSERT wait for the first to commit, and it then sees the existing row. Concurrency on shared rows is the same problem as overselling stock; see how to prevent inventory overselling.

How do you handle multiple currencies?

Never mix currencies in one account or one SUM. Give each currency its own account, require each entry to balance per currency (the trigger above does), and book a conversion through a pair of FX accounts: the source currency balances against an FX account in that currency, and the target currency against an FX account in the target currency. Store the rate used, as numeric, on the entry. Gains and losses from revaluation are then their own entries, which your accountant can define.

How those accounts map to your chart of accounts, revenue recognition and tax is an accounting question. This is general information; confirm with your accountant or adviser.

Buy, build or hire?

Option	Example and published pricing	Choose this when	Watch out for
Managed ledger API	Modern Treasury: usage-based, quoted per customer, no public price list	You also need its payment operations, or you want a vendor to own ledger scaling	An annual minimum commitment; your core money data sits in a vendor's system
Open-source ledger database	TigerBeetle: Apache-2.0, free to self-host, with built-in debit and credit primitives	Very high transaction volume and a team ready to run a specialised database	A second datastore to operate, back up and reconcile with your main database
Custom PostgreSQL ledger	The schema on this page, in the database you already run	Moderate volume, and you want the ledger inside the same transactions as orders, wallets or credit	Correctness is yours: the checks, tests and reconciliation must be built and kept running

How long does it take to build yourself?

For a backend developer comfortable with PostgreSQL transactions, the core (schema, triggers, posting function, reversal function and the three checks) is roughly 1–2 weeks. Tests that prove it under concurrency and retries add about another week. Integrating it with real top-ups, payouts and reconciliation is the larger part of the job.

The main risk is a second write path. The ledger above is correct only while every balance change goes through postTransaction. One admin script or a "quick fix" UPDATE on balance_minor, and check 2 starts returning rows. Lock it down with roles and schedule the checks with an alert.

Why RAITHub for this

• Money in the database, done carefully. Sundor Skin stores every amount as integer poisha, enforces credit limits in the database, retries transactions on serialisation conflicts and keeps a hash-chained audit log, under 530+ automated tests and 146 PostgreSQL tables with row-level security.
• Payment rails in production. TheSkinProof, the founder's own venture, takes bKash, Nagad, SSLCommerz and cash on delivery with 750+ automated tests.
• Fintech client work. RAITHub's 8 client projects include a fintech dashboard. RAITHub has shipped no regulated or licensed fintech product.
• QA first. Zero-sum, replay and concurrency tests run in CI on every change, not after launch.
• Data handling. We sign NDAs and DPAs and work inside your controls; production and financial data stay in your own cloud account; development uses synthetic data.

When you don't need us

• Your team already knows PostgreSQL well. The schema on this page is a working start; add the tests and checks and you may not need outside help.
• You only need accounting, not a product ledger. If the goal is bookkeeping and tax, accounting software and an accountant are the right tools.
• Your volume needs a specialised ledger database. At very high throughput, a purpose-built ledger or a managed ledger vendor may suit you better than PostgreSQL.

How RAITHub would build this

• Ledger core: the schema, balance trigger, append-only enforcement, posting and reversal functions, adapted to your chart of accounts.
• Integrations: postings wired to your payment providers' verified webhooks, with idempotency keys from each provider's transaction ID.
• Balances and limits: running totals or snapshots, chosen for your read and write volume, with overdraft and credit rules in the database.
• Checks and reconciliation: the three integrity queries as scheduled jobs with alerts, plus settlement matching and an exceptions queue.

Timeline: a backend and API engagement, typically 6–12 weeks with integrations and reconciliation. A ledger-only engagement inside an existing product sits at the short end; see Backend and API development.

What you receive: automated tests and CI, including zero-sum, replayed-event and concurrent-posting tests; handover documentation and runbooks for an unbalanced entry or a reconciliation gap; and full IP ownership under NDA.

Next step: book the free 15-minute technical audit. Bring your current schema, or a description of how money moves in your product. You get a written fixed quote afterwards.

Frequently asked questions

What tables does a double-entry ledger need?

Three at minimum: accounts, journal entries and postings. Each posting links one account to one entry with a signed amount, and the postings in an entry must sum to zero per currency.

How do I enforce that debits equal credits in PostgreSQL?

Use a constraint trigger declared DEFERRABLE INITIALLY DEFERRED on the postings table. It runs at commit, after all of an entry's postings exist, and raises an exception if any currency does not sum to zero.

Should a ledger use numeric or bigint for money?

Use bigint minor units for posted amounts, because it is exact and fast. Use numeric for rates and fractional prices, and round to minor units before posting. Never use floating-point types for money.

How do you fix a wrong ledger entry?

Post a reversing entry with opposite signs, linked to the original, then post the correct entry. Never update or delete journal entries or postings; block both with triggers and database permissions.

Should I store account balances or calculate them?

Calculate on read for low volume and reporting. Keep a running total, updated in the same transaction as the postings, when you must reject overdrafts. Use snapshots for long histories. Reconcile stored balances against postings on a schedule.

How does a ledger handle multiple currencies?

One account per currency, entries that balance in each currency separately, and conversions booked through FX accounts in each currency with the rate stored on the entry. Never sum different currencies together.

Has RAITHub built a regulated ledger product?

No. RAITHub has shipped no regulated or licensed fintech product. It has built money handling inside Sundor Skin and TheSkinProof, the founder's own venture, and its client work includes a fintech dashboard.