# ERP General Ledger: Designing a Double-Entry Core > How to design the double-entry general ledger at the heart of an ERP system, from chart of accounts to trial balance, using PostgreSQL constraints and triggers to make unbalanced entries impossible. URL: https://www.matthewswong.com/en/blog/erp-general-ledger-double-entry-design/ - Author: Matthews Wong (https://www.matthewswong.com/en/about/) - Published: 2026-07-03 - Updated: 2026-07-03 - Category: ERP 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. ## The chart of accounts and the five account types 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 ## Modeling the chart of accounts and fiscal periods 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. > 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. ## Journal entries: the unit of work that must always balance 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. - Each line row must be strictly a debit or a credit, enforced with a check constraint so a row can never carry a nonzero value in both columns at once. - Every entry needs at least two lines, since a single-sided posting is not a valid double-entry transaction by definition. - The sum of debits must equal the sum of credits across all lines belonging to one entry before that entry is allowed to post. > 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. ## Enforcing the balance rule with a deferred constraint trigger 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 application opens a transaction and inserts one row into journal_entries with posted left false. - It inserts every line row into journal_lines, each one a pure debit or a pure credit against a postable account. - It updates the entry to posted true, which fires the constraint trigger and sums every line tied to that entry id. - If the sum of debits and credits does not match, the trigger raises an exception and the whole transaction rolls back atomically. ## The trial balance: proving the ledger adds up 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. > 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. ## Period close and immutability 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. - Flip the fiscal_periods.status column to CLOSED and add a database-level rule or trigger that rejects new postings dated inside a closed period. - Require corrections to closed periods to be reversing journal entries booked in the current open period, never in-place edits to historical rows. - Keep a LOCKED status for periods that have passed an external audit, with even tighter restrictions than CLOSED, so historical filings can never silently change. ## Sources - Double-entry bookkeeping — Wikipedia - General ledger — Wikipedia - PostgreSQL Documentation: Constraints - PostgreSQL Documentation: SET CONSTRAINTS > **Key takeaway:** Every ERP module eventually settles into the general ledger: sales orders, purchase invoices, payroll runs and inventory movements all become rows in one shared accounting core. If that core is wrong every report above it is wrong too, which is why double-entry belongs in the design from the start. ## Source links - https://en.wikipedia.org/wiki/Double-entry_bookkeeping - https://en.wikipedia.org/wiki/General_ledger - https://www.postgresql.org/docs/current/ddl-constraints.html - https://www.postgresql.org/docs/current/sql-set-constraints.html ## FAQ ### What is double-entry bookkeeping and why does an ERP general ledger need it? 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. ### How do you enforce balanced journal entries in PostgreSQL? 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. ### Why use five account types instead of a simpler debit and credit flag? 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. ### Can I edit a journal entry after the fiscal period has closed? 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. ### What does it mean if a trial balance query returns a nonzero total? 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. --- - HTML version: https://www.matthewswong.com/en/blog/erp-general-ledger-double-entry-design/ - Bahasa Indonesia: https://www.matthewswong.com/id/blog/erp-general-ledger-double-entry-design/ - Site index for agents: https://www.matthewswong.com/llms.txt - Sitemap: https://www.matthewswong.com/sitemap.xml - API description (OpenAPI): https://www.matthewswong.com/openapi.json