ERP
ERP General Ledger: Designing a Double-Entry Core
July 202611 min read
Photo by Keith Williamson on flickr
Double-entry bookkeeping records every transaction as at least two line items, one debit and one credit, that must sum to zero. An ERP general ledger needs it because it is the only way to guarantee, mathematically, that the accounting core stays internally consistent no matter how many modules write to it, from sales to payroll to inventory.
A plain check constraint cannot see other rows, so it cannot confirm that all the lines on one journal entry sum to zero on its own. The reliable approach is a constraint trigger declared deferrable and initially deferred, which lets an application insert a header and all its line rows in one transaction and only checks the balance once, right before commit.
The five types, asset, liability, equity, revenue, and expense, determine whether a debit increases or decreases a given account's balance, since assets and expenses grow with debits while liabilities, equity, and revenue grow with credits. Without that classification on the chart of accounts, reports like a balance sheet or income statement cannot be generated correctly from raw journal lines.
No, closed-period entries should be treated as immutable rather than edited or deleted in place. Corrections belong in a new reversing entry dated inside a still-open period, which preserves a complete audit trail and keeps every previously reported trial balance reproducible exactly as it was filed.
A correct trial balance always sums to zero across every posted entry in a period, since every entry that went through the normal insert path was individually balanced by the constraint trigger. A nonzero result almost always means something bypassed that trigger, commonly a bulk data import or a manual fix, and should be investigated as a data integrity incident rather than dismissed as rounding.
Photo by Keith Williamson on flickr
Every ERP module eventually settles into the same place: the general ledger. Sales orders, purchase invoices, payroll runs, and inventory movements all end up as rows in one shared accounting core, and if that core is wrong, every downstream report is wrong with it. Dashboards, tax filings, and investor updates are all just different views over the same underlying journal, so the schema underneath them cannot afford to be an afterthought bolted on after the rest of the ERP is built.
This post walks through designing that core from scratch in PostgreSQL: the chart of accounts, the double-entry rule that keeps every transaction balanced, fiscal periods and period close, and the trial balance query that proves the whole system adds up to zero. The goal throughout is to push as much correctness as possible down into the database itself, using constraints and triggers, so that a bug in one module's application code cannot silently corrupt the shared ledger every other module depends on.
Double-entry accounting rests on a simple idea, first formalized centuries ago and still the backbone of every modern accounting system: every transaction touches at least two accounts, one debited and one credited, and the two sides always net to zero. Before writing any transaction logic, an ERP needs a chart of accounts that classifies every account into one of five types, because that classification decides whether a debit increases or decreases the account's balance. Get this classification wrong and every financial statement generated from the ledger, balance sheet, income statement, and cash flow alike, will quietly misrepresent the business even though every individual entry is internally balanced.
| Account type | Normal balance | Increased by |
|---|---|---|
| Asset | Debit | Debit |
| Liability | Credit | Credit |
| Equity | Credit | Credit |
| Revenue | Credit | Credit |
| Expense | Debit | Debit |
The chart of accounts is a hierarchical table: header accounts group postable accounts for reporting, so a schema needs a self-referencing parent column and a flag that marks whether an account can actually receive postings. Fiscal periods get their own table so the ledger can track which months are open for posting, which are closed for normal entries but still open for adjustments, and which are fully locked after an audit. Storing currency on the account itself, rather than assuming a single company-wide currency, also matters early: a multi-entity ERP will eventually need accounts denominated in more than one currency, and retrofitting that later means touching every posting query already written against the table.
-- chart_of_accounts: hierarchical, one row per GL account
CREATE TABLE chart_of_accounts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
code TEXT NOT NULL UNIQUE, -- e.g. '1100', '2100', '4000'
name TEXT NOT NULL,
account_type TEXT NOT NULL, -- ASSET|LIABILITY|EQUITY|REVENUE|EXPENSE
parent_id UUID REFERENCES chart_of_accounts(id),
is_postable BOOLEAN NOT NULL DEFAULT true, -- false for header/summary accounts
currency CHAR(3) NOT NULL DEFAULT 'IDR',
active BOOLEAN NOT NULL DEFAULT true,
CHECK (account_type IN ('ASSET','LIABILITY','EQUITY','REVENUE','EXPENSE'))
);
-- fiscal_periods: one row per open/closed accounting period
CREATE TABLE fiscal_periods (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
period_code TEXT NOT NULL UNIQUE, -- e.g. '2026-03'
start_date DATE NOT NULL,
end_date DATE NOT NULL,
status TEXT NOT NULL DEFAULT 'OPEN', -- OPEN|CLOSED|LOCKED
CHECK (status IN ('OPEN','CLOSED','LOCKED'))
);Give every account a stable numeric code, not just a name, and sort by code everywhere. Renaming an account should never break a report, and finance teams read account ranges by number out of habit long before they read the label next to it.
A journal entry is the atomic unit of an accounting system: a header row plus two or more line rows, where every line is either a debit or a credit, never both, and the sum of all debit lines on an entry must equal the sum of all credit lines. Modeling this correctly in the schema means encoding three rules directly as database constraints rather than trusting application code to get them right on every code path. Any module that posts to the ledger, an invoicing service, a payroll job, a manual adjustment screen, ends up going through the exact same journal_entries and journal_lines tables, which is what makes the ledger a single source of truth instead of five slightly different implementations of the same idea.
-- journal_entries: one row per transaction header
CREATE TABLE journal_entries (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
entry_number TEXT NOT NULL UNIQUE,
period_id UUID NOT NULL REFERENCES fiscal_periods(id),
entry_date DATE NOT NULL,
memo TEXT,
posted BOOLEAN NOT NULL DEFAULT false,
posted_at TIMESTAMPTZ,
created_by UUID NOT NULL REFERENCES employees(id)
);
-- journal_lines: two or more rows per entry, debit XOR credit
CREATE TABLE journal_lines (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
entry_id UUID NOT NULL REFERENCES journal_entries(id) ON DELETE CASCADE,
account_id UUID NOT NULL REFERENCES chart_of_accounts(id),
debit NUMERIC(18,2) NOT NULL DEFAULT 0,
credit NUMERIC(18,2) NOT NULL DEFAULT 0,
line_memo TEXT,
CHECK (debit >= 0 AND credit >= 0),
CHECK ((debit > 0 AND credit = 0) OR (debit = 0 AND credit > 0))
);
-- Trigger: reject a posted entry whose lines do not sum to zero
CREATE OR REPLACE FUNCTION assert_entry_balanced() RETURNS trigger AS $$
DECLARE
imbalance NUMERIC(18,2);
BEGIN
SELECT COALESCE(SUM(debit) - SUM(credit), 0) INTO imbalance
FROM journal_lines WHERE entry_id = NEW.id;
IF NEW.posted AND imbalance <> 0 THEN
RAISE EXCEPTION 'journal entry % is not balanced (diff=%)', NEW.id, imbalance;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE CONSTRAINT TRIGGER trg_entry_balanced
AFTER INSERT OR UPDATE ON journal_entries
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE FUNCTION assert_entry_balanced();A plain CHECK constraint cannot see other rows, so it cannot verify that an entry's lines sum to zero on its own. PostgreSQL evaluates CHECK constraints per row, immediately, with no way to defer them past the statement. Balance validation across the line rows of one entry has to live in a constraint trigger instead, and that trigger should be deferrable so a multi-statement insert can complete before the balance check runs.
Because PostgreSQL cannot defer a CHECK constraint past a single row, the standard way to enforce a cross-row invariant like a balanced entry is a constraint trigger declared DEFERRABLE INITIALLY DEFERRED. That lets the application insert a header and all of its line rows inside one transaction, then have the database verify the sum only once, right before commit, instead of failing partway through the insert. Setting the trigger to fire on posting rather than on every line insert also keeps drafts usable: an unposted entry can sit half-built, get edited, and even be deleted outright, without ever tripping the balance check meant for entries that are about to become permanent.
The trial balance is the classic sanity check of any accounting system: list every account with its total debits and total credits for a period, and if every entry that touched the period was genuinely balanced, the grand total of all debits across every account must equal the grand total of all credits. Running this as a single aggregate query over posted entries in a period is both the reporting artifact accountants expect and a live integrity check on the ledger itself. Filtering the query on e.posted keeps unfinished drafts out of the report entirely, which matters because a draft entry is, by definition, allowed to be temporarily unbalanced while someone is still editing it.
-- Trial balance: every account's debit/credit total for a period
SELECT
a.code,
a.name,
SUM(l.debit) AS total_debit,
SUM(l.credit) AS total_credit,
SUM(l.debit) - SUM(l.credit) AS net_balance
FROM chart_of_accounts a
JOIN journal_lines l ON l.account_id = a.id
JOIN journal_entries e ON e.id = l.entry_id AND e.posted
WHERE e.period_id = $1
GROUP BY a.code, a.name
ORDER BY a.code;
-- Sanity check: grand total of a correct trial balance is always zero
SELECT SUM(total_debit) - SUM(total_credit) AS should_be_zero
FROM (
SELECT SUM(l.debit) AS total_debit, SUM(l.credit) AS total_credit
FROM journal_lines l
JOIN journal_entries e ON e.id = l.entry_id AND e.posted
WHERE e.period_id = $1
) sub;If that trial balance query ever returns anything other than zero, something bypassed the constraint trigger, most likely a bulk import or a manual data fix that skipped the normal insert path. Treat a nonzero trial balance as a production incident, not a rounding quirk.
Once a fiscal period closes, its posted entries should become effectively immutable. Rather than allowing updates or deletes on closed-period rows, an ERP ledger should require corrections to be new reversing entries dated in a still-open period, which preserves a complete, append-only audit trail and keeps every historical trial balance reproducible exactly as it was reported. This is also where the design pays off outside of pure bookkeeping correctness: auditors, tax authorities, and future engineers debugging a discrepancy all rely on being able to replay exactly what was posted and when, without wondering whether a row was quietly edited after the fact.