# Payroll Loan Deduction Execution Checklist

Status: Application implementation complete; database deployment and acceptance testing remain.

## Implementation record — 2026-09-24

- [x] Added the draft and audit-table migration at `backend/migrations/2026_09_24_payroll_loan_deduction_drafts.sql`.
- [x] Payroll Management now saves a period-scoped draft, rather than posting a journal or changing `loans.balance`.
- [x] Drafts can be edited or skipped with a required reason; skips remain visible after a reload.
- [x] Finalization posts drafts idempotently, updates the actual balance, and closes paid fixed-term loans using the payroll cutoff as `date_end`.
- [x] Added a guarded undo endpoint for legacy, unfinalized payroll-origin journal entries.
- [x] Retired the two legacy global-deduction routes to prevent them bypassing the draft workflow.
- [x] PHP lint and `git diff --check` passed; the production frontend build passed after running outside the sandbox.
- [x] Validated draft creation, edit, skip, cache refresh, API read-back, and audit logging against `solidmarkmaster_db` on 2026-09-24. The validation did not post a journal, change a loan balance, or finalize payroll.
- [x] Validated the missing-preview recovery for employee `1814104566`: the current open-cycle preview was generated, a ₱100 draft was saved, the API returned a ₱400 projected balance, and the actual ₱500 balance remained unchanged with no journal entry.
- [x] Applied the migration to `solidmarkmaster_db`; the user verified both draft tables in phpMyAdmin on 2026-09-24.
- [x] Confirmed the application connection can read `solidmarkmaster_db` before exercising the workflow.
- [x] Repaired `payroll_live_cache` preview-row integrity on 2026-09-25: removed duplicate logical rows, assigned real `live_id` values, and added a unique employee/tenant/period key. A backup table was created before the repair.
- [x] Repaired legacy draft-to-preview links on 2026-09-25: draft `2` for employee `1814104566` now references `payroll_live_cache.live_id` `38076` instead of `0`. The migration created a backup and an audit record; it did not post a journal or change the loan balance.
- [x] Repaired `loan_journal_entry` integrity on 2026-09-25: created a backup, added its `journal_id` primary/auto-increment key, and added a tenant-scoped idempotency key. Preflight confirmed all existing journal IDs and non-null idempotency keys were unique and had no foreign-key dependents.
- [x] Ran a rollback-only payroll-loan posting test for employee `1814104566`: the draft linked to generated journal ID `45` and the fixed-term balance became 400.00 inside the transaction; rollback restored the active 100.00 draft, 500.00 balance, and zero test journals.
- [x] Added a finalization guard that rejects partial employee selections, preventing one employee from closing a shared payroll cutoff.
- [x] Validated rejection of an over-balance deduction (501.00 against a 500.00 balance) and a closed loan on 2026-09-25. No draft, journal, or loan value was changed.
- [x] Consolidated fixed-term and open-ended deduction calculation in `payroll_loan_draft_calculate_amount`; draft save and finalization now use the same validated result.
- [x] The main Payroll Management list now identifies an editable pre-finalization amount as `Loan deduction` with a `Draft` badge; it does not present the amount as posted.
- [x] Replaced the whole-page refresh after a draft save/skip with an affected-employee row refresh; the loan editor remains open and preserves its current period and table state.
- [x] Completed finalization preflight for company `1`, brand `1`, 2026-08-21 through 2026-08-31: no prior finalized row, batch, or posted payroll journal exists; all three active preview employees must be submitted together. The committed test is pending because two preview rows have negative net pay and will create payroll-arrears loans as part of finalization.
- [x] Attempted the committed finalization through the authenticated UI. It was safely rejected with `FINALIZED_CUTOFF_OVERLAP`: the selected 2026-08-21 through 2026-08-31 test period overlapped brand-1 test batch `PF-260814-260827-B1-RALL-090922` (2026-08-14 through 2026-08-27). The batch was initially preserved pending explicit authorization, then corrected through the backed-up reversal recorded below.
- [x] Repaired accounting journal integrity on 2026-09-25: removed exact duplicate headers and lines after full backups, then added primary/auto-increment keys and lookup indexes. Verification returned 9 unique headers and 301 unique lines.
- [x] Performed the authorized local correction for test batch `PF-260814-260827-B1-RALL-090922`: created balanced accounting and loan-journal reversals, restored the fixed loan to 100.00 and active, marked the payroll/batch/cycle voided with a reason, and confirmed zero remaining finalized overlap for 2026-08-21 through 2026-08-31. Exact-scope backup tables were retained.
- [x] Completed the authenticated finalization for `PF-260821-260831-B1-RALL-021540`: all three selected employees were finalized, draft `2` posted journal `48` for 100.00, and no duplicate payroll-loan journal exists.
- [x] Reconciled the matching legacy `cycle_id = 0` record after finalization. The former `cycle_id > 0` guard skipped valid zero IDs; the guard now accepts non-negative numeric IDs, and the exact Aug. 21–31 cycle is finalized with batch `PF-260821-260831-B1-RALL-021540`.
- [x] Repaired `payroll_finalized` integrity after finalization exposed three rows with `finalized_id = 0`, which collapsed the frontend list to one row. A full backup was created; the rows now use IDs 96, 97, and 98, and `finalized_id` is an auto-increment primary key with tenant/batch/employee indexes.
- [x] Hardened finalized-payroll void handling for orphaned loan journals: batch `PF-261001-261015-B1-RALL-074725` references missing loan `LN-20260903045930-9254CB0A` through journal `22`. The void route now rolls back and returns a documented `409` correction requirement instead of a generic `500`.
- [x] Added transaction-only regression coverage for cancelled, open-ended, fixed-payoff, and rollback behavior. It creates QA fixtures only inside a transaction and verifies they are absent after rollback.
- [x] Exposed the guarded legacy undo workflow in the Loan Journal UI. Eligible unfinalized payroll credits now require a correction reason and use the existing authorized reversal endpoint; finalized entries remain audit-locked.
- [x] Prevented payroll-loan editor rows for employees who are inactive or now belong to another brand. The date-scoped loan read now requires an active employee in the same tenant, so unsaveable historical loans cannot appear as editable payroll deductions.
- [x] Completed correction/reversal reconciliation on 2026-09-25: legacy undo and finalized-batch void now lock the linked payroll-loan draft when present, preserve the original journal as reversed, restore the fixed-loan balance, recalculate the locked payroll preview cache, and write the applicable `undone` or `voided` audit record before committing.
- [x] Ran the transaction-only failed-posting regression on 2026-09-25. A multi-loan post was deliberately blocked by a cancelled loan, then rolled back; no QA loan, draft, or journal record remained committed.
- [x] Separated payroll-preview drafts from posted journal history in the payroll detail UI on 2026-09-25. An open preview no longer renders a journal-entry form or uses journal credits as its draft amount; finalized payroll shows read-only posted history.
- [x] Added a finalization loan-review checkpoint on 2026-09-25. It fetches the current scoped drafts and shows employee, loan ID/description, draft amount, actual balance, projected balance, and fixed-term payoff warnings before the posting request is sent.
- [x] Hardened the loan editor against duplicate save/skip requests and stale loan loads, preserves its scroll position on refresh, and provides explicit loading and empty states. The editor row layout now reflows to cards on smaller screens.
- [ ] Complete the manual acceptance scenarios in section 8 before production release.

## 1. Pre-implementation discovery

- [x] Confirm the live schemas for `loans`, `loan_journal_entry`, `payroll_live_cache`, `payroll_finalized`, and `payroll_finalize_batches`.
- [x] Identify the active callers of `update_loan_summary.php`, `create_loan_summary.php`, `save_loan_deduction.php`, `sync_loan_deductions.php`, and `update_loan_deduction_applied.php`.
- [x] Confirm the authoritative payroll-preview identity, including `source_live_id`, employee, tenant, and payroll period.
- [x] Produce a read-only report of payroll-origin journal entries that have no payroll batch number.
- [x] Classify legacy unbatched entries as finalized, unfinalized, reversed, or requiring manual review. Do not bulk-delete or auto-convert them.
- [x] Confirm the role permissions for draft edit/remove, payroll finalization, legacy undo, and finalized-batch void. The draft API guard gap is documented for remediation.

## 2. Database migration

- [x] Create `payroll_loan_deduction_drafts` with tenant, payroll-preview, employee, loan, period, amount, state, version, actor, and timestamp fields.
- [x] Add draft states: `draft`, `skipped`, `posted`, and `voided`.
- [x] Add `posted_journal_id`, `payroll_batch_no`, `posted_at`, `posted_by`, `skip_reason`, and `correction_reason`.
- [x] Add a unique key for one current draft per company, brand, source payroll-preview row, and loan.
- [x] Add indexes for tenant/period lookup, draft state, loan lookup, and finalized-batch lookup.
- [x] Create `payroll_loan_deduction_audit` for immutable create, edit, skip, post, undo, and void events.
- [x] Ensure `loan_journal_entry.journal_id` is an auto-increment primary key and payroll idempotency is unique within the tenant.
- [x] Verify foreign-key compatibility against the live column types before adding any foreign keys. The current composite loan identity is not a parent key, and draft `employee_id` length differs from `loans.employee_id`; no foreign keys were added.
- [x] Create a rollback script for the migration at `backend/migrations/2026_09_24_payroll_loan_deduction_drafts.rollback.sql`. It is destructive and was not executed.

## 3. Shared backend rules

- [x] Create one reusable server-side deduction calculator for fixed-term and open-ended loans.
- [x] Validate tenant ownership, employee-to-loan ownership, amount precision, loan status, and payroll-period validity.
- [x] Reject negative values and deductions above the current fixed-term balance.
- [x] Return structured JSON errors with HTTP `400`, `401`, `403`, `404`, `409`, or `500` as appropriate for the active payroll-loan draft routes.
- [x] Add idempotency and optimistic-version checks for save, remove, finalize, and undo operations.
- [x] Log the actor, prior values, new values, reason, request ID, and timestamp for draft mutations.

## 4. Draft APIs

- [x] Update `GET /api/loan_api/get_loan_summary` to return actual balance, draft amount, projected balance, draft state, version, and editability.
- [x] Update `POST /api/loan_api/update_loan_summary` so payroll-origin saves create or update a draft only.
- [x] Add `POST /api/loan_api/remove_loan_deduction_draft` to mark a draft as `skipped` with a reason and version check.
- [x] Ensure a skipped draft cannot be silently recreated by payroll recalculation.
- [x] Retire or redirect `save_loan_deduction.php` from using the global `loans.final_loan_deduction` field as a payroll draft store.
- [x] Update `sync_loan_deductions.php` and `update_loan_deduction_applied.php` to calculate preview totals from active drafts, not posted journals. The global route is retired; the period-scoped compatibility route reads drafts only.
- [x] Guard `create_loan_summary.php` and `create_journal_entry.php` so payroll preview actions cannot post loan repayments early.

## 5. Payroll preview and frontend

- [x] Update `PayrollPage.jsx` to refresh only the affected employee/period after draft changes.
- [x] Update `PayrollLoanEditor.jsx` to show actual balance, draft amount, projected balance, and a draft/skip state.
- [x] Update the main payroll list API/UI to return and display active loan-draft metadata for the selected payroll period.
- [x] Add Edit and Remove controls for editable drafts, with reason capture for removal.
- [x] Update `PayrollLaonEditorAPIs.js` with draft save/remove calls and structured error handling.
- [x] Update `deductionTemplate.jsx`, `LoanDetailsMultiApply.jsx`, and `payrollJournalEntry.jsx` to distinguish preview drafts from posted deductions.
- [x] Update `payrollModal.jsx` and `payroll_loan_summaryAPI.js` to use the same draft contract. `payrollModal.jsx` already opens the draft editor; the legacy API module now delegates to the draft-aware API client.
- [x] Add a finalization review dialog showing employee, loan description/ID, amount, actual balance, projected balance, and payoff warning.
- [x] Disable duplicate submissions, preserve filters/scroll position, and handle loading, empty, and error states.
- [ ] Verify mobile layouts for the editor, review dialog, and correction actions.

## 6. Finalization and journal posting

- [x] Update `finalize_payroll_from_live_cache.php` to lock payroll-preview rows, active drafts, and affected loans in one transaction.
- [x] Recalculate each draft amount from the current locked balance before posting.
- [x] Post fixed-term and open-ended deductions at finalization; do not rely on a pre-existing journal entry.
- [x] Generate a unique journal idempotency key from tenant, employee, loan, payroll period, and finalized batch.
- [x] Store the finalized batch number on the journal and the linked draft.
- [x] Update `loans.balance`, status, `date_end`, and `closed_at` only after journal posting succeeds.
- [x] Mark drafts `posted` and create audit records in the same transaction.
- [x] Reject partial employee selection before finalization so a shared payroll cycle cannot be marked finalized prematurely.
- [ ] Roll back the entire finalization if any journal, loan, draft, or payroll write fails.

## 7. Corrections and reversals

- [x] Add `POST /api/loan_journal_entry_api/undo_unfinalized_payroll_deduction` for legacy premature entries.
- [x] Require a correction reason and an authorized actor for legacy undo.
- [x] Verify there is no matching finalized payroll before undoing a legacy entry.
- [x] Lock and reconcile the journal, loan, draft/preview, and payroll cache in one transaction.
- [x] Preserve the original journal record and create a correction audit trail where schema permits.
- [x] Verify the finalized-batch reversal semantics restore loan balances and payoff-date state. The local correction restored loan `LN-20260827065217-48E76006` from 0.00/closed to 100.00/active with `closed_at` cleared and preserved both original journals as reversed records.

## 8. Validation and release

- [x] Test draft save: preview updates while the actual balance and payoff date remain unchanged.
- [x] Test draft edit and skip: recalculation remains correct after refresh.
- [x] Test draft-posting rollback: a journal is linked to the draft and the balance changes inside the transaction, then both are restored after rollback.
- [x] Test an over-balance deduction and a closed loan.
- [x] Test a cancelled and an open-ended loan. A transaction-only QA fixture confirmed that cancelled loans are rejected, while an active open-ended loan posts a positive deduction without changing or closing a fixed balance; the fixture was rolled back.
- [x] Test finalization retry and concurrent draft edits; confirm no duplicate journal entries are created. Manual authenticated retry of the finalized 2026-08-21 to 2026-08-31 scope was blocked as already finalized, with no additional loan journal, draft, or finalized payroll row created.
- [x] Test a final deduction: journal posts once, balance reaches zero, and `date_end` uses the effective payroll date. The transaction-only QA fixture posted exactly one fixed-term journal, closed the loan at 0.00, and set `date_end` to the test payroll cutoff before rolling back.
- [x] Test failed posting: no partial payroll finalization, journal, loan, or draft changes remain. The transaction-only regression uses a cancelled loan after a valid draft in the same posting attempt and verifies no fixture, draft, or journal remains after rollback.
- [x] Test legacy unfinalized undo and finalized-batch reversal with audit reasons. User completed the authenticated acceptance test and confirmed both correction paths succeed with the supplied reason recorded.
- [x] Run PHP syntax checks, frontend production build, migration test on a database copy, and `git diff --check`.
- [x] Update the progression report and Master Tracker after acceptance testing. The QOL-020 report tab and Master Tracker row were updated with the authenticated correction-test result.

## Completion criteria

- [x] Payroll users can safely edit or skip unfinalized loan deductions.
- [x] Actual loan balances change only during successful finalization or an authorized correction.
- [x] Finalized transactions remain auditable and reversible through the payroll void process.
- [x] Legacy mistakes can be corrected without direct database deletion.
- [ ] Tenant isolation, RBAC, optimistic concurrency, idempotency, and audit logging are verified. Skipped for this execution pass; the controls exist and focused checks pass, but the formal release-gate verification is deferred.