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

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.

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:        330M

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.

-- 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();

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.