A practical guide to building an automated financial reporting engine based on double-entry journal entries that generates Balance Sheets, Income Statements, Cash Flow Statements, and Notes to Financial Statements in accordance with PSAK — complete with chart of accounts design, aggregation queries,
Closing the monthly books at a hospital or clinic is often still manual work: exporting journals from SIMRS to Excel, re-summarizing per account, reconciling BPJS receivables, then preparing the balance sheet and income statement in a separate spreadsheet. For a volume of 1,500–4,000 journal lines per month, this process takes 3–7 working days and is prone to hard-to-trace discrepancies. Yet PSAK 1 demands only one thing: consistent, comparable presentation and proper disclosure. This article discusses how to build an automated reporting engine that produces the Balance Sheet, Income Statement, Cash Flow, and CALK directly from double-entry journals — not from manual recaps reconstructed every month.
The most expensive mistake when building automated reports is not in the reporting engine, but in the Chart of Accounts (COA). If accounts only have a code and name, the reporting engine has no way of knowing that account 1-1210 is a contra-asset, or that account 4-4100 includes revenue recognized under PSAK 72. The solution: each account is given machine attributes, namely psak_group (aset_lancar, aset_tidak_lancar, liabilitas_jangka_pendek, liabilitas_jangka_panjang, ekuitas, pendapatan, beban_pokok, beban_operasional, pendapatan_lain, beban_lain, oci), normal_balance (D or K), is_current, is_cash_equivalent for PSAK 2, and report_order so that the line order in reports is stable across periods.
A concrete example in a hospital COA: 1-1100 Cash and Cash Equivalents (aset_lancar, D, is_cash_equivalent=true), 1-1200 Patient Receivables (aset_lancar, D), 1-1210 BPJS Receivables (aset_lancar, D), 1-1290 Allowance for Doubtful Accounts (kontra, K), 1-1500 Fixed Assets — Buildings (aset_tidak_lancar, D) with accumulated depreciation account 1-1590, 2-2100 Drug Vendor Payables (liabilitas_jangka_pendek, K), 4-4100 Inpatient Revenue, and 5-5100 Cost of Drugs. With this mapping, the Balance Sheet and Income Statement are simply the result of group by on one journal table.
Minimum table structure in PostgreSQL 16 that is sufficient for production:
Add hard constraints: CHECK (debit >= 0 AND credit >= 0), CHECK ((debit > 0) OR (credit > 0)) so that there are no zero-zero lines, and CHECK (debit = 0 OR credit = 0) so that a single line does not contain both. This is cheap to do at the database level and saves hours of discrepancy investigation later.
First principle: there is no separate recap table. Account balances are the result of aggregating journal_details, not numbers stored and updated manually. If you store running balances in the accounts table, every reversal, correction, and adjusting journal entry potentially makes reports inconsistent. For performance, simply use a materialized view refreshed after the posting process is complete, not a balance table mutated by the application.
Second principle: pay attention to normal balance. The value debit - credit produces a positive number for accounts with a normal debit balance (assets, expenses) and a negative number for accounts with a normal credit balance (liabilities, equity, revenue). For presentation, multiply by -1 if normal_balance = K, so that all report lines appear positive. This sign error is the most common cause of a balance sheet not balancing during migration from Excel.
// PHP 8.3 + Laravel 11 — saldo akun per periode (PostgreSQL 16)
$rows = DB::select(<< $start, 'end' => $end]);
// tampilkan: normal_balance === 'K' ? raw_balance * -1 : raw_balance
From the same result: Balance Sheet = aggregation of psak_group for assets, liabilities, and equity as of the cut-off date; Income Statement = revenue − cost of revenue − operating expenses + other income − other expenses; Cash Flow indirect method = net profit + depreciation adjustment + working capital changes, with cross-verification against changes in accounts flagged is_cash_equivalent. This verification is mandatory: if the increase in cash per the Balance Sheet does not equal total cash flow, there is a mis-mapped journal. For data above 5 million detail rows per year, partition journal_details by year and create a composite index on (account_id, journal_id).
Automated reports are only useful if the numbers are trusted. Implement a journal status cycle: draft → posted → locked. Journals with locked status must not be edited; corrections are made through reversal journals with a reference number to the original journal. Also lock at the period level: a database trigger rejects insert/update on journal_details if periods.status = 'closed' for that trx_date. Without this, one day you will find that last month's report has changed numbers.
Before closing, run the automatic checklist: (1) total debit = total credit per journal and per period; (2) no balance-sheet-type accounts with movements in the current period that do not go through adjusting journal entries; (3) BPJS receivables reconciliation between book balance and E-Klaim verification results; (4) cash/bank reconciliation between book balance and bank statement imported via CSV; (5) discrepancy ratio below 0.01%. All checklist results are saved as period attachments so auditors can trace them.
For the audit trail, use the journal_audits table that records user, timestamp, action, and before-after value snapshot, with chained hash (each row stores the SHA-256 of the previous row) so historical manipulation is detected. Integration to SIMRS/SIM Klinik is enough via internal API with idempotency key so one transaction is not posted twice; visit data from SatuSehat/FHIR remains in the clinical domain and must not enter the journal. For tax invoice issuance, synchronize to e-Faktur DJP Online, not retype.
Rule of thumb: if a financial figure cannot be traced from the report back to the journal number in 3 clicks, your system is not yet automated — only semi-automated.
No. PSAK is a presentation and disclosure standard, not software certification. What is assessed is consistency of accounting policies, completeness of CALK, and the ability to restate historical figures when there is a correction.
Apply a simple expected credit loss model under PSAK 71 using aging 0–30, 31–90, 91–180, and over 180 days, with reserve rates reviewed every quarter. Calculate automatically from the claim date, not from manual estimates.
Yes, to a certain extent. Excel starts to become fragile when journal lines exceed 10,000 per month, there is more than one entity, or an audit trail is needed. After that, reconciliation costs and error risk are usually greater than the cost of building a system.
For a single hospital or clinic entity with an already tidy COA, 6–10 weeks: 2 weeks for COA design and mapping, 3–4 weeks for development and API integration, 2–3 weeks for parallel testing with historical data.
Building automated reporting that meets PSAK standards is not an impossible large project — the key lies in a mapped COA design, a single source of journal truth, and strict period controls. If you are preparing a financial system for a hospital, clinic, or business unit with already running ERP and POS modules, I can help map the data flow from operational transactions to audit-ready reports, including integration to SIMRS, BPJS, and SatuSehat. Please contact Nugroho Setiawan for a technical consultation session and review of your reporting architecture.
Related solutions by PT Pilar Inovasi Informatika (Pilar Inovasi IT products): SIMRS (Hospital Information System) Clinic Management System BPJS & SatuSehat Integration. Need a free consultation?
Belum ada komentar. Jadilah yang pertama!
Baca juga artikel lainnya
