# Automating Financial Reporting in a Custom ERP: From Trial Balance to Management Reports

> How to build automated financial reports — P&L, balance sheet, cash flow, and management dashboards — in a custom ERP using PostgreSQL and NestJS.

URL: https://www.matthewswong.com/en/blog/erp-financial-reporting-automation/

- Author: Matthews Wong (https://www.matthewswong.com/en/about/)
- Published: 2025-06-10
- Updated: 2026-10-13
- Category: ERP

Gartner predicted in February 2026 that finance teams using cloud ERP applications with embedded AI assistants could see a 30% faster financial close by 2028. You do not need a cloud suite to start on that: 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.

## The Chart of Accounts Foundation

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.

### Double-Entry Journal Engine

My journal engine enforces double-entry accounting at the database level: every journal entry must have total debits equal to total credits, or its transaction is rolled back. That rule spans several rows, so it cannot be a plain CHECK constraint, which in PostgreSQL only sees the row being written; it needs a check that runs across all of the entry's lines at commit, such as a deferred constraint trigger. 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, so any transaction correctly posted to the journal — AP invoice, AR payment, payroll — automatically appears in all financial reports with no additional mapping.

> From my experience building ERP systems as a freelance ERP consultant: always implement a 'period lock' feature. A PKP files its SPT Masa PPN by the end of the following month, and a company files its annual corporate income tax return within four months of the end of the tax year (PMK 81/2024, Articles 169 and 171). Once a reported period is locked, no new journal entries can be posted to it — this prevents retroactive changes that would invalidate tax returns already submitted. The lock is enforced at the database level with a trigger, not just in the application layer.

## Automated Report Generation

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. For Indonesian entities, follow PSAK 201 (presentation) and PSAK 207 (cash flows), IAI's numbering since 1 January 2024, or SAK EP for private entities, in force since 1 January 2025.

### Management Reports and Segmentation

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.

> **Warning: Currency Conversion in Multi-Entity Reports**

> 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 follow PSAK 221 (formerly PSAK 10, based on IAS 21) or FASB ASC 830, depending on the client's reporting requirements: assets and liabilities at the closing rate, income and expenses at the rates on the transaction dates, and, under PSAK 221 paragraph 39, the translation difference in other comprehensive income rather than profit or loss.

## How I Approached This

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.

## Tax Report Integration (PPN and PPh)

Since the January 2025 tax period, tax invoices and the monthly SPT Masa PPN have gone through Coretax DJP, and annual PPh returns from tax year 2025 are filed there too. The ERP system I build links each journal line to an optional tax code (PPN 12% on a DPP Nilai Lain of 11/12 for non-luxury goods under PMK 131/2024, PPh 23 2% on services, etc.). The PPN report filters journal lines by tax code and period, shows input and output tax, and computes the net payable or credit. Coretax imports XML built to DJP's templates — tax invoices, withholding slips, some annual-return attachments — so I generate those files from the ledger. The annual return's financial statement reconciliation (Lampiran 1) varies by business sector, so map it to the chart of accounts early.

## Audit Readiness and Document Trails

For Indonesian companies subject to external audit, every journal entry needs supporting documentation. Article 68 of UU 40/2007 requires a public accountant's audit when, among other cases, a company manages public funds, is listed, is a Persero, or has assets or turnover of at least Rp50 billion. 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 an external auditor or the tax office asks for support for a transaction, I can pull it up in 30 seconds.

## Sources & Further Reading

- Gartner / CFO Dive — Advanced ERPs Could Cut Financial Close Times by 30% — https://www.cfodive.com/news/advanced-erps-cut-financial-close-times-30percent-gartner-ai/812918/
- IAI — Perubahan Penomoran PSAK dan ISAK dalam SAK Indonesia (effective 1 January 2024) — https://web.iaiglobal.or.id/assets/files/file_publikasi/Perubahan_Penomoran_PSAK_ISAK_dalam_SAK_Indonesia.pdf
- DJP Coretaxpedia — Lapor SPT Tahunan Badan — https://www.pajak.go.id/coretaxpedia/lapor-spt-tahunan-badan

> **Key takeaway:** Automated financial reporting in a custom ERP works when every report — P&L, balance sheet, cash flow, tax and management views — is a query over one double-entry journal, with reported periods locked. Nothing is re-keyed, so the trial balance is always current and month end no longer starts with a spreadsheet-to-ledger reconciliation.

## Source links

- https://www.cfodive.com/news/advanced-erps-cut-financial-close-times-30percent-gartner-ai/812918/
- https://web.iaiglobal.or.id/assets/files/file_publikasi/Perubahan_Penomoran_PSAK_ISAK_dalam_SAK_Indonesia.pdf
- https://web.iaiglobal.or.id/assets/files/file_berita/DE%20Amendemen%20PSAK%20221.pdf
- https://www.jurnal.id/id/blog/selisih-kurs-translasi/
- https://web.iaiglobal.or.id/assets/files/file_berita/Materi%20Presentasi_Sosialisasi%20SAK%20Entitas%20Privat.pdf
- https://dart.deloitte.com/USDART/home/codification/broad-transactions/asc830-10/roadmap-foreign-currency-transactions-translations/chapter-5-foreign-currency-translations/5-2-translation-process
- https://www.postgresql.org/docs/current/ddl-constraints.html
- https://www.postgresql.org/docs/current/sql-createtrigger.html
- https://www.pajak.go.id/en/node/113886
- https://www.pajak.go.id/en/node/35004
- https://peraturan.bpk.go.id/Details/306614/pmk-no-81-tahun-2024
- https://www.pajak.go.id/sites/default/files/2025-04/KT-12_2025_Perkembangan%20Informasi%20Terkini%20Coretax%20DJP.pdf
- https://www.pajak.go.id/en/node/112031
- https://www.pajak.go.id/coretaxpedia/lapor-spt-tahunan-badan
- https://peraturan.bpk.go.id/Details/39965/uu-no-40-tahun-2007

## FAQ

### Why is a well-designed Chart of Accounts so important before writing any ERP reporting code?

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.

### How does the double-entry journal engine prevent posting errors at the database level?

Every journal entry must have total debits equal to total credits before its transaction can commit. Because PostgreSQL CHECK constraints only see the row being written, the balance check runs in a deferred constraint trigger on journal_lines that sums the whole entry at commit and raises an error if it does not balance. Any transaction correctly posted to the journal — AP invoice, AR payment, payroll — then flows into all financial reports with no additional mapping.

### What is a 'period lock' and why is it critical for Indonesian companies?

A period lock prevents new journal entries from being posted to a closed accounting period. A PKP files its SPT Masa PPN by the end of the following month and a company files its annual corporate income tax return within four months of the end of the tax year, so changes to periods that have already been reported must be blocked. The lock is enforced by a database-level trigger rather than only in the application layer, so it cannot be bypassed.

### How does the system handle on-demand report performance without making dashboards slow?

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.

### How are management reports like cost-center P&L and project profitability supported?

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.

---

- HTML version: https://www.matthewswong.com/en/blog/erp-financial-reporting-automation/
- Bahasa Indonesia: https://www.matthewswong.com/id/blog/erp-financial-reporting-automation/
- 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