# Payroll Loan, Journal Entry, and Payroll Void Security

## Purpose

This update prevents duplicate payroll loan deductions, protects payroll-generated
journal entries from manual alteration, and makes payroll voiding reversible and
auditable.

The update does **not** change payroll computation formulas, rates, deductions, or
net-pay calculations.

## Deployment Priority

### 1. Apply the SQL migration first

Database changes are required before deploying the updated PHP and React code.

Migration:

`backend/database/2026_07_30_payroll_loan_idempotency_and_void_security.sql`

Before applying it:

1. Back up the application database.
2. Confirm that the selected database is the intended company environment.
3. Run the duplicate review query below.
4. Apply the migration during a controlled deployment window.

Pre-migration duplicate review:

```sql
SELECT
    company_id,
    brand_id,
    loan_id,
    employee_id,
    payroll_cutoff,
    COUNT(*) AS entry_count
FROM loan_journal_entry
WHERE LOWER(COALESCE(origin, '')) = 'payroll'
  AND LOWER(COALESCE(entry_type, '')) = 'credit'
GROUP BY
    company_id,
    brand_id,
    loan_id,
    employee_id,
    payroll_cutoff
HAVING COUNT(*) > 1;
```

The migration:

- Adds a scoped idempotency key to loan journal entries.
- Adds payroll batch linkage.
- Adds posted/reversed entry status.
- Adds reversal linkage and audit fields.
- Backfills payroll idempotency keys.
- Preserves historical duplicate rows while assigning them unique legacy keys.
- Adds unique indexes to prevent concurrent duplicate insertion.
- Makes accounting journal reversals unique.
- Adds the `void_payroll` permission.
- Grants the permission to `ADMIN` and `SUPER ADMIN`.

Do not remove the new columns or indexes after deploying the updated application.
The application relies on them for duplicate protection and void processing.

Post-migration verification:

```sql
SHOW COLUMNS FROM loan_journal_entry
WHERE Field IN (
    'idempotency_key',
    'payroll_batch_no',
    'entry_status',
    'reversal_of_journal_id',
    'reversed_at',
    'reversed_by',
    'reverse_reason'
);

SHOW INDEX FROM loan_journal_entry
WHERE Key_name IN (
    'uniq_lje_scope_idempotency',
    'uniq_lje_reversal_of',
    'idx_lje_payroll_batch_status'
);

SHOW INDEX FROM accounting_journal_entries
WHERE Key_name = 'uniq_aje_reversal_of';

SELECT permission_key, module, is_active
FROM permissions
WHERE permission_key = 'void_payroll';

SELECT company_id, brand_id, idempotency_key, COUNT(*) AS duplicate_count
FROM loan_journal_entry
WHERE idempotency_key IS NOT NULL
  AND idempotency_key <> ''
GROUP BY company_id, brand_id, idempotency_key
HAVING COUNT(*) > 1;
```

The last query must return no rows.

### 2. Deploy the backend

Deploy the updated loan journal, payroll finalization, reporting, and payroll void
endpoints.

Important backend behavior:

- Payroll credits use deterministic idempotency keys.
- Duplicate requests return or update the existing entry instead of inserting a
  second credit.
- Employee, loan, amount, entry type, period, and cutoff are validated.
- Payroll-generated entries cannot be manually edited, converted, or deleted.
- Finalized payroll links matching loan credits to the payroll batch.
- Only posted credits affect active loan balances and payroll reports.
- Voiding is authorized from the authenticated user, not a client-supplied user
  name.
- Voiding creates a linked debit reversal, restores the loan balance, and marks
  the original credit reversed in one database transaction.

### 3. Deploy the frontend

The frontend adds:

- Immediate save locking to prevent double-click submissions.
- Payroll period and cutoff validation.
- Warnings before updating an already-applied deduction.
- Stable idempotency keys for manual journal requests.
- Disabled editing and deletion for payroll-generated or reversed entries.
- Role-controlled payroll void actions.
- Reversal and restored-loan information in the void result.

### 4. Validate after deployment

Perform these tests in a non-production or controlled payroll cycle:

1. Submit the same payroll loan deduction twice.
2. Confirm that only one posted credit exists.
3. Finalize payroll and confirm `payroll_batch_no` is populated on the credit.
4. Confirm a normal user cannot access payroll voiding.
5. Void the finalized batch as an authorized administrator.
6. Confirm a linked debit reversal was created.
7. Confirm the original credit is marked `reversed`.
8. Confirm the loan balance was restored.
9. Confirm reports ignore the reversed credit.
10. Create the corrected payroll and confirm its new deduction posts once.

Useful validation query:

```sql
SELECT
    journal_id,
    loan_id,
    employee_id,
    entry_type,
    amount,
    origin,
    payroll_batch_no,
    entry_status,
    reversal_of_journal_id,
    reference_no,
    idempotency_key
FROM loan_journal_entry
WHERE payroll_batch_no = ?
   OR reversal_of_journal_id IS NOT NULL
ORDER BY journal_id;
```

Replace `?` with the finalized payroll batch number when running the query in a
database client.

## Security and Accounting Rules

- The database unique indexes are the final protection against simultaneous
  duplicate requests.
- Frontend save locks improve usability but are not treated as the security
  boundary.
- Reversed financial entries are retained for audit history.
- Payroll entries must be reversed through the payroll void process, not deleted.
- A corrected payroll produces a new posted credit; it does not reactivate the
  reversed credit.
- Company and brand scope are enforced during finalization and voiding.

## Existing Historical Item

One previously voided payroll batch was identified with a legacy posted loan
credit of **PHP 200.00**.

It was not automatically modified because it is existing financial data. The
secured void endpoint can repair it through an authorized, transactional, audited
reversal. Perform that remediation only after confirming the affected batch,
employee, loan, and expected balance with Accounting or Payroll.

## Verification Completed During Implementation

- Migration applied successfully to the connected development database.
- Required columns, unique indexes, and permission were verified.
- No duplicate scoped idempotency keys remained.
- All changed PHP files passed syntax checking.
- The frontend production build completed successfully.
- Payroll computation formulas were not changed.