ERP
Automating Financial Reporting in a Custom ERP: From Trial Balance to Management Reports
May 202512 min read
ERP
Every financial report is ultimately an aggregation over the Chart of Accounts (CoA) and the journal entries posted against it. The four-level hierarchy — Category, Group, Sub-group, Account — determines which accounts roll up into which P&L and balance sheet lines. Changing the CoA after data has been posted is painful, so the post recommends investing time with the client's accountant to design it correctly before go-live.
The journal engine enforces a PostgreSQL CHECK constraint that rejects any INSERT where total debits do not equal total credits. This means the integrity of double-entry accounting is guaranteed at the database level, not just in the application layer. Any transaction correctly posted to the journal — AP invoice, AR payment, payroll — automatically flows into all financial reports with no additional mapping.
A period lock prevents new journal entries from being posted to a closed accounting period. Indonesian companies need to close their books monthly for PPN (VAT) reporting and annually for corporate tax, so retroactive changes that would invalidate already-submitted tax reports must be blocked. The lock is enforced by a database-level trigger rather than only in the application layer to ensure it cannot be bypassed.
All three core financial reports — P&L, Balance Sheet, and Cash Flow — are generated via NestJS endpoints and cached in Redis with a 5-minute TTL. Re-generating a full P&L from journal lines for a year with thousands of entries can take 2-3 seconds, which is acceptable for on-demand requests but too slow for a dashboard refreshing every 30 seconds. The Redis cache absorbs that repeated load.
Each journal line carries an optional cost_center_id and project_id field, enabling segmented queries across any dimension management needs — department spend, project profitability, or product margin analysis. The CFO at the client referenced in the post receives a weekly email every Monday at 6 AM WIB with a cost-center P&L attached as an Excel file generated via exceljs in NestJS.
The ERP software market reached $70 billion in 2025, driven largely by the demand for automated financial reporting. Finance teams using ERP report automation see a 70% reduction in report generation time, and Gartner projects that advanced ERPs with AI could cut financial close times by 30% by 2028. In Indonesian SMEs, I've found that financial reporting is typically the highest-value module — the one the CFO and owner look at every week. In my freelance ERP consulting work, I built a financial reporting engine that generates P&L, Balance Sheet, Cash Flow, and management-specific reports entirely from the general ledger, with no manual spreadsheet work. This post covers the architecture.
Every financial report is ultimately a view over the chart of accounts (CoA) and the journal entries posted against it. Before writing any reporting code, get the CoA structure right. I use a four-level hierarchy: Category (Assets, Liabilities, Equity, Revenue, Expense) → Group → Sub-group → Account. The hierarchy determines which accounts roll up into which P&L and balance sheet lines. Changing the CoA after data is posted is painful — invest time in designing it correctly with the client's accountant before go-live.
My journal engine enforces double-entry accounting at the database level: every journal entry must have total debits equal total credits, or the INSERT is rejected by a PostgreSQL CHECK constraint. Each journal_entry row has a header (date, reference, description, type) and multiple journal_lines (account_id, debit, credit). The financial reports are aggregations over these journal_lines, filtered by date range and grouped by account hierarchy. This means any transaction correctly posted to the journal — AP invoice, AR payment, payroll — automatically appears in all financial reports with no additional mapping.
Financial Reporting Engine — NestJS + PostgreSQL
Chart of Accounts (4-level hierarchy)
├── ASSETS
│ ├── Current Assets
│ │ ├── Cash & Bank [11000–11999]
│ │ └── Accounts Receiv. [12000–12999]
│ └── Fixed Assets [15000–15999]
├── LIABILITIES
│ └── Accounts Payable [21000–21999]
├── EQUITY [30000–39999]
├── REVENUE [40000–49999]
└── EXPENSES [50000–59999]
journal_lines (the single source of truth)
┌─────────────────────────────────────────────────┐
│ id │ journal_id │ account_id │ debit │ credit │
│ ...│ ... │ 11001 │ 1000 │ 0 │ <- Cash receipt
│ ...│ ... │ 40001 │ 0 │ 1000 │ <- Revenue
└─────────────────────────────────────────────────┘
↓ Aggregated by NestJS ReportService
P&L Report Balance Sheet Cash Flow
Revenue: 500M Assets: 2.1B Operating: +400M
COGS: (300M) Liab: 800M Investing: (50M)
Gross: 200M Equity: 1.3B Financing: (20M)
Net: 150M Net: 330MFrom my experience building ERP systems as a freelance ERP consultant: always implement a 'period lock' feature. Indonesian companies need to close their books monthly for PPN (VAT) reporting and annually for corporate tax. Once a period is locked, no new journal entries can be posted to it — this prevents retroactive changes that would invalidate already-submitted tax reports. The lock is enforced at the database level with a trigger, not just in the application layer.
The three core financial reports I build are generated on demand via NestJS endpoints. The P&L endpoint accepts a date range and returns revenue accounts summed as income, expense accounts summed as costs, with gross profit and net profit computed. The Balance Sheet endpoint computes asset, liability, and equity totals as of a specific date (cumulative from inception, not period-filtered). The Cash Flow endpoint categorizes movements into operating, investing, and financing activities based on account tags. All three return structured JSON consumed by a React report renderer.
Beyond the statutory reports, management wants cost center P&L (how much did each department spend?), project profitability (revenue vs. cost by project), and product margin analysis (which products make money?). I support this through a cost_center_id and project_id on each journal line. Segmented reports filter by these dimensions. The CFO at my client gets a weekly email (automatically generated every Monday at 6 AM WIB) with P&L by cost center attached as an Excel file generated via exceljs in NestJS.
-- PostgreSQL: P&L report query
SELECT
coa.category,
coa.group_name,
coa.account_name,
coa.account_code,
SUM(jl.debit - jl.credit) AS net_amount
FROM journal_lines jl
JOIN journal_entries je ON jl.journal_id = je.id
JOIN chart_of_accounts coa ON jl.account_id = coa.id
WHERE
je.posted_at BETWEEN :start_date AND :end_date
AND je.status = 'posted'
AND coa.category IN ('REVENUE', 'EXPENSE')
AND (je.locked_period IS NULL OR je.locked_period >= :start_date)
GROUP BY coa.category, coa.group_name, coa.account_name, coa.account_code
ORDER BY coa.account_code;
-- Period lock enforcement (trigger)
CREATE OR REPLACE FUNCTION check_period_lock()
RETURNS TRIGGER AS $$
BEGIN
IF EXISTS (
SELECT 1 FROM period_locks
WHERE period_year = EXTRACT(YEAR FROM NEW.posting_date)
AND period_month = EXTRACT(MONTH FROM NEW.posting_date)
AND locked = TRUE
) THEN
RAISE EXCEPTION 'Period % is locked — no new entries allowed',
TO_CHAR(NEW.posting_date, 'YYYY-MM');
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_check_period_lock
BEFORE INSERT ON journal_entries
FOR EACH ROW EXECUTE FUNCTION check_period_lock();The reporting engine I built is a NestJS module with four services: JournalService (posting and validation), ReportService (P&L, Balance Sheet, Cash Flow queries), SegmentService (cost center and project filtering), and ExportService (PDF via Puppeteer, Excel via exceljs). All reports are cached in Redis with a 5-minute TTL — re-generating a full P&L from journal lines for a year with thousands of entries can take 2-3 seconds, which is fine for on-demand but not for a dashboard that refreshes every 30 seconds.
If your client has multiple legal entities or transacts in foreign currencies, consolidated financial reporting requires currency translation — converting subsidiary accounts from their functional currency to the presentation currency at the appropriate exchange rates. This is a common source of errors when handled manually. I store exchange rates daily and apply transaction-date rates for income statement items and period-end rates for balance sheet items, with FASB ASC 830 or PSAK 10 as the reference standard depending on the client's reporting requirements.
Indonesian businesses must file monthly PPN (VAT) returns via e-Faktur and annual PPh (income tax) returns. The ERP system I build links each journal line to an optional tax code (PPN 11%, PPh 23 2%, etc.). Tax reports are generated by filtering journal lines by tax code and date range. The PPN report groups by tax period, shows input tax (purchases) and output tax (sales), and computes the net tax payable or credit. I export this in the format accepted by the Direktorat Jenderal Pajak's e-Faktur application, which saves the finance team several hours of monthly data entry.
For Indonesian companies subject to external audit (either statutory audit for PKP companies or client audit for large vendors), every journal entry needs supporting documentation. My system links journal entries to source documents — AP invoices, AR invoices, bank statements, payment vouchers. The auditor can click on any journal line and see the original document. This 'drill-down to source' capability is what separates a real ERP from a sophisticated spreadsheet. When BPK (Badan Pemeriksa Keuangan) or an external auditor requests support for a transaction, I can pull it up in 30 seconds.