# Post-Mortem Report: General Ledger Imbalance & Report Mismatches

## 1. Executive Summary

This report documents the investigation, root causes, and resolutions of a critical general ledger imbalance and balance sheet report mismatch in the ERP financial modules. The issues affected the `almuhajirin_db` database and were resolved on **June 5, 2026**.

Two distinct types of imbalances were resolved:

1. **General Ledger Imbalance (`-86,299,410` IDR):** An actual discrepancy in the double-entry accounting records where total credits exceeded total debits.
2. **Frontend UI Balance Sheet Mismatch (`2,159,293,400` IDR):** A reporting mismatch where the Balance Sheet report failed to balance because of unmapped historical Chart of Accounts (COA) records and an incorrect profit calculation logic.

All imbalances have been corrected, and the balance sheet matches down to the single Rupiah:
$$\text{Total Assets} = \text{Total Liabilities} + \text{Total Equity} + \text{Laba} = \mathbf{26,817,191,616 \text{ IDR}}$$

---

## 2. Root Cause Analysis

We identified five distinct culprits that caused the financial discrepancies.

### Culprit A: Bracket Typo in Balance Transfer API

* **Imbalance impact:** `-89,229,410` IDR (Credits exceed Debits)
* **Target File:** [BalanceController.php](file:///home/noxturne/projects/ziad/backend/ziad-laravel-template/app/Http/Controllers/BalanceController.php#L1482-L1501)
* **Root Cause:** In the student balance transfer endpoint (`transferBalance`), the code passed two separate array arguments to `Journal::insert()` instead of wrapping them in a nested multi-dimensional array:

  ```php
  // Buggy Code:
  Journal::insert(
    [ 'transaction_id' => $id, 'coa_id' => $receiverCoa, 'debit' => 0, 'credit' => $amount ],
    [ 'transaction_id' => $id, 'coa_id' => $senderCoa, 'debit' => $amount, 'credit' => 0 ]
  );
  ```

  Laravel's query builder only inserts the first argument and ignores the second. This resulted in the Credit row (recipient) being written, while the Debit row (sender) was completely omitted, leaving 980 balance transfers single-legged.

### Culprit B: Webhook Concurrent Race Condition on VA Payments

* **Imbalance impact:** `+2,580,000` IDR (Excess Debits)
* **Target File:** `TransactionService@updateBillByPaymentV2` via `/api/payment-notification`
* **Root Cause:** Due to a race condition, two duplicate payment webhook notifications hit the server concurrently for transactions `17433` and `932667`. Both threads processed the unpaid bill. One resolved successfully, but the second resolved the pay amount to `0` IDR (since the bill was already paid). However, the bank settlement handler still debited the full `1,290,000` IDR to `BJB Syariah`, while the bill payment handler credited the Piutang account with `0`, creating a debit-heavy imbalance of `1,290,000` IDR per transaction.

### Culprit C: Orphan Journal Entry

* **Imbalance impact:** `+350,000` IDR (Excess Debits)
* **Root Cause:** Transaction record `681657` was deleted or lost from the `transactions` table, but its associated journal lines (including a debit of `350,000` IDR) were left orphaned in the `journals` table.

### Culprit D: Unmapped Historical Chart of Accounts (COA)

* **Report impact:** `2,157,523,400` IDR (Omitted Assets/Receivables in UI)
* **Root Cause:** 29,669 historical journal rows referenced old class/SPP receivables accounts (such as *Piutang SPP TINGKAT SMPI*). These accounts were deleted from the `coas` table during database imports or cleanups. Because the report queries join the `coas` table to group and filter transactions, these 29,669 entries were completely excluded from all financial reports, leaving the visible data unbalanced.

### Culprit E: Incorrect Profit (`Laba`) Summing Logic

* **Report impact:** `1,770,000` IDR (Overstated Laba in UI)
* **Target File:** [FinanceController.php](file:///home/noxturne/projects/ziad/backend/ziad-laravel-template/app/Http/Controllers/FinanceController.php#L301-L337)
* **Root Cause:** The backend methods `getTotalPendapatan` and `getTotalBiaya` calculated totals using only `sum(journals.credit)` for revenue and `sum(journals.debit)` for expenses. They failed to subtract the opposite side (debit adjustments on revenue or credit adjustments on expenses), overstating the net profit (`Laba`) by `1,770,000` IDR due to debit adjustments on revenue accounts.

---

## 3. Implemented Solutions

The following fixes were executed to repair the data and resolve the calculations:

### 1. Database Repair Script

We ran a transactional, set-based database script ([repair_ledger.sql](file:///home/noxturne/.antigravity-personal/.gemini/antigravity-cli/scratch/repair_ledger.sql)) on `almuhajirin_db` that:

* Dynamically inserted the missing Debit journal entries for the 980 balance transfers by joining the sender student's school to identify the correct `Tabungan Siswa` account.
* Purged the duplicate race-condition transaction records and their journals.
* Purged orphan journal lines for transaction `681657`.

### 2. COA Metadata Restoration

We ran a restoration script ([restore_coa.sql](file:///home/noxturne/.antigravity-personal/.gemini/antigravity-cli/scratch/restore_coa.sql)) to dynamically rebuild and insert the deleted COA records back into the `coas` table. The script parsed the unmapped `coa_name`s to determine their proper groups (e.g. `PIUTANG`, `PENDAPATAN`) and school associations, resolving the unmapped journals count to `0`.

### 3. Backend Profit Calculation Correction

We updated [FinanceController.php](file:///home/noxturne/projects/ziad/backend/ziad-laravel-template/app/Http/Controllers/FinanceController.php) to calculate net values:

* **Revenue:** `sum(credit) - sum(debit)`
* **Expenses:** `sum(debit) - sum(credit)`

---

## 4. Future Risks & Long-Term Mitigations

To prevent data corruption and report mismatches in the future, we recommend implementing the following constraints:

### Risk 1: Accidental Creation of Single-Sided Entries

* **Description:** Developers or API scripts can write unbalanced journals to the database.
* **Mitigation:**
  1. **Eloquent Observers:** Implement a Laravel Observer on the `Transaction` or `Journal` models to validate that `debit === credit` before committing any transaction.
  2. **DB Constraints:** Add database triggers that block writes to `journals` if a transaction is out of balance.

### Risk 2: Webhook Concurrency Race Conditions

* **Description:** Duplicate webhook callbacks executing at the same millisecond create duplicate records.
* **Mitigation:**
  1. **Idempotency Keys:** Enforce idempotency on `/api/payment-notification` using bank payment reference numbers.
  2. **Pessimistic Locks:** Use database-level locking (`lockForUpdate` or Redis-based locks) to serialize concurrent webhook notifications for the same invoice/bill.

### Risk 3: Deletion of Chart of Accounts (COA) Records

* **Description:** Deleting a COA record orphans historical journal entries, breaking all financial reports.
* **Mitigation:**
  1. **Foreign Key Constraints:** Add a foreign key constraint on `journals.coa_id` referencing `coas.id` with `ON DELETE RESTRICT`. This blocks any attempts to delete a COA if it has associated historical journal entries.
  2. **Soft Deletes:** Ensure that `coas` only supports soft deletes and that report queries include soft-deleted COAs for historical records.