# Incident & Resolution Report: Soft-Delete Balance Discrepancy

**Scope:** Balance Calculation & Excel Mutation Export Auditing  
**Date:** 2026-05-26  
**Status:** Resolved  
**Target File:** `docs/SOFT_DELETE_FINANCIAL_AUDIT.md`

---

## 1. Case Overview & Incident Summary

An accounting discrepancy was reported on a student balance account (`balance_id = 3819`), showing three conflicting figures:
* **System Stored Balance:** `50,690`
* **User Manual Calculation (Exported Excel):** `42,690` (Difference of `8,000` from System)
* **Raw SQL Transaction Calculation:** `34,690` (Difference of `16,000` from Manual, `24,000` from System)

### Root Cause Analysis
The discrepancy was caused by the introduction of the **Soft Deletes** feature to the `transactions` table, coupled with raw SQL query exclusions:
1. **Canceled Transaction:** On May 22, transaction `557669` (an outflow of `16,000`) was canceled. The system correctly added the `16,000` back to the stored balance and soft-deleted the transaction in the database (setting `deleted_at = '2026-05-23 ...'`).
2. **Missing Scope in Raw SQL Audit:** The raw SQL query used to verify the balance did not include `AND deleted_at IS NULL`. It continued to count the canceled `16,000` outflow, outputting `34,690`.
3. **Missing Scope in Excel Export:** The `exportExcelOneBalanceMutations()` endpoint in `BalanceController.php` queried the `transactions` table using a raw `DB::select` query that lacked a `deleted_at` filter. As a result, the exported Excel file still contained the canceled `16,000` withdrawal. The user summed this Excel sheet and calculated `42,690`.

Once the `deleted_at IS NULL` filter is applied to the transaction log, the calculated balance is exactly **`50,690`**, which matches the system's stored balance perfectly.

---

## 2. Technical Context: Eloquent vs. Query Builder

In Laravel, soft deletes only work automatically when querying via **Eloquent Models** that use the `SoftDeletes` trait. 

```
┌───────────────────────────────────────┐
│     Transaction::where(...)           │ ──> Automatically appends:
│     (Eloquent Model Query)            │     "WHERE `transactions`.`deleted_at` IS NULL"
└───────────────────────────────────────┘
                                          
┌───────────────────────────────────────┐
│     DB::table('transactions')         │ ──> DOES NOT filter soft deletes!
│     DB::select("SELECT...")           │     Will return canceled/deleted records.
└───────────────────────────────────────┘
```

Any raw SQL `DB::select()`, query builder `DB::table()`, or manual `->join()` queries **completely bypass** Eloquent's scopes and must explicitly filter out soft-deleted rows.

---

## 3. Working Auditing Queries (Soft-Delete Compliant)

Use the following optimized, lightweight, and soft-delete-compliant queries when auditing financial records in production.

### Query A: Reconstruct the Chronological Running Balance
Rebuilds the ledger step-by-step to show what the balance was after every transaction, excluding canceled/deleted rows.

```sql
SELECT 
  t.id AS transaction_id,
  t.created_at,
  t.description,
  t.amount,
  CASE 
    WHEN (t.transaction_type_value = 3 AND t.balance_id = 3819) 
      OR (t.transaction_type_value != 3 AND t.to_balance_id = 3819) 
    THEN 'INFLOW (+)'
    ELSE 'OUTFLOW (-)'
  END AS direction,
  @running := @running + CASE 
    WHEN (t.transaction_type_value = 3 AND t.balance_id = 3819) 
      OR (t.transaction_type_value != 3 AND t.to_balance_id = 3819) 
    THEN t.amount
    ELSE -t.amount
  END AS running_balance
FROM transactions t
CROSS JOIN (SELECT @running := 0) vars
WHERE (t.balance_id = 3819 OR t.to_balance_id = 3819)
  AND t.deleted_at IS NULL -- Exclude soft-deleted transactions
ORDER BY t.created_at ASC, t.id ASC;
```

### Query B: Spot the Exact Drift (Calculated vs. Recorded Balance)
Compares the running transaction sum side-by-side with the recorded mutations to show exactly which transaction caused a mismatch:

```sql
SELECT 
  sub.transaction_id,
  sub.created_at,
  sub.description,
  sub.amount,
  sub.direction,
  sub.running_balance AS calculated_running_balance,
  m.balance_after AS recorded_mutation_balance,
  (m.balance_after - sub.running_balance) AS drift_amount
FROM (
  SELECT 
    t.id AS transaction_id,
    t.created_at,
    t.description,
    t.amount,
    CASE 
      WHEN (t.transaction_type_value = 3 AND t.balance_id = 3819) 
        OR (t.transaction_type_value != 3 AND t.to_balance_id = 3819) 
      THEN 'INFLOW'
      ELSE 'OUTFLOW'
    END AS direction,
    @running := @running + CASE 
      WHEN (t.transaction_type_value = 3 AND t.balance_id = 3819) 
        OR (t.transaction_type_value != 3 AND t.to_balance_id = 3819) 
      THEN t.amount
      ELSE -t.amount
    END AS running_balance
  FROM transactions t
  CROSS JOIN (SELECT @running := 0) vars
  WHERE (t.balance_id = 3819 OR t.to_balance_id = 3819)
    AND t.deleted_at IS NULL
  ORDER BY t.created_at ASC, t.id ASC
) sub
JOIN user_balance_mutations m ON m.transaction_id = sub.transaction_id AND m.balance_id = 3819
WHERE m.balance_after != sub.running_balance
ORDER BY sub.created_at ASC, sub.transaction_id ASC;
```

### Query C: Find "Ghost" Mutations (No Matching Active Transaction)
Finds balance changes that have no corresponding record in the active transaction log:

```sql
SELECT m.id AS mutation_id,
       m.created_at AS mutation_created_at,
       m.transaction_id AS missing_transaction_id,
       m.transaction_amount AS amount,
       m.balance_before,
       m.balance_after,
       m.description AS mutation_description
FROM user_balance_mutations m
LEFT JOIN transactions t ON t.id = m.transaction_id AND t.deleted_at IS NULL
WHERE m.balance_id = 3819
  AND m.description != 'Pencatatan harian saldo'
  AND t.id IS NULL
ORDER BY m.created_at DESC;
```

---

## 4. Development Guidelines & Best Practices

To prevent similar discrepancies when implementing soft deletes on financial tables:

### 1. Raw Joins Require Explicit Filters
When joining a soft-deleted table to another query, you **must** filter out deleted rows in the join constraint:
```php
// ❌ WRONG
$query->join('bill_transaction', 'transactions.id', '=', 'bill_transaction.transaction_id')

//  RIGHT
$query->join('bill_transaction', function ($join) {
    $join->on('transactions.id', '=', 'bill_transaction.transaction_id')
         ->whereNull('bill_transaction.deleted_at');
});
```

### 2. Guard Raw DB SQL and Exports
Always append `deleted_at IS NULL` when writing SQL statements for reports, exports, or audit logs:
```sql
SELECT * FROM transactions WHERE balance_id = ? AND deleted_at IS NULL;
```

### 3. Check Table Mutations
Ensure that any administrative or batch deletion of a transaction also triggers a corresponding soft-delete check on secondary logs (like `bill_transaction` or `journals`).

---

## 5. Summary of Code Fixes

The following files were updated to enforce soft-delete compliance on Query Builder / Join calls:

| File Name | Method / Context | Update |
| :--- | :--- | :--- |
| [`FinanceController.php`](file:///home/noxturne/projects/ziad/backend/ziad-laravel-template/app/Http/Controllers/FinanceController.php) | Balance Sheet & P/L endpoints (`DB::table('journals')`) | Added `->whereNull('journals.deleted_at')` to 10 reporting methods. |
| [`BalanceController.php`](file:///home/noxturne/projects/ziad/backend/ziad-laravel-template/app/Http/Controllers/BalanceController.php) | `exportExcelOneBalanceMutations()` | Added `AND deleted_at IS NULL` to raw SQL query. |
| [`BalanceController.php`](file:///home/noxturne/projects/ziad/backend/ziad-laravel-template/app/Http/Controllers/BalanceController.php) | `exportExcelAllBalanceMutations()` | Added `->whereNull('t1.deleted_at')` to Query Builder join. |
| [`TransactionController.php`](file:///home/noxturne/projects/ziad/backend/ziad-laravel-template/app/Http/Controllers/TransactionController.php) | `getSummaryOfTransaction()` | Added `->whereNull('bill_transaction.deleted_at')` to join. |
| [`RecalculateLedger.php`](file:///home/noxturne/projects/ziad/backend/ziad-laravel-template/app/Console/Commands/RecalculateLedger.php) | `audit:recalculate-ledger` command | Added `->whereNull('deleted_at')` to raw journal sum. |