Regenerating Payroll Runs After a Contribution/Tax Rate Fix

Why this doc exists

We just fixed two things payroll calculations depend on:

• The SSS 2026 contribution table (SssContributionRateServiceImpl, V45__sss_2026_official_table.sql)
• The withholding tax bracket lookup (WithholdingTaxBracketRepository, V46__withholding_tax_brackets.sql) — this one had a missing repository method (wouldn't even compile) and a boundary bug (crashed for taxable income landing exactly on a bracket edge)

Existing payslips do not recompute themselves. Rates are read live at the moment generatePayslips runs (PayrollServiceImpl.calculateSssDeduction / calculatePhilhealthDeduction / calculatePagibigDeduction / calculateWithholdingTax all call ...Repository.findAll() fresh, no caching) — so the fix only affects payslips generated after today. Anything generated before still holds numbers computed from the broken/missing tables.

The one thing that will bite you if you skip it

There is no in-place recalculation anywhere in this app. Once a payroll run leaves Draft status, its payslips are permanently frozen — no endpoint edits or regenerates them. The instinct to "cancel the bad run and create a new one for the same period" does not work by itself:

• PATCH /api/payroll/{id}/status → Cancelled only changes the Payroll.status string. It does not delete the run's Payslip rows.
• Every report that reads payslip data — ReportingServiceImpl.getBirAlphalist, GovernmentFormsServiceImpl (1601-C, 2316, 13th month, remittances), and all the YTD/monthly aggregate queries in PayslipRepository — filters only by year/month, never by the parent Payroll.status.

So if you cancel a bad run and generate a fresh one for the same period, you now have two sets of payslips for that period and every report double-counts. The old, wrong payslip rows have to actually be gone, not just orphaned from a cancelled run.

Which path applies to you

Is this dev/demo data (the seeded MotorPH sample dataset)?
├── Yes → Path A: Demo Data Reset + Generate  (recommended, 2 clicks, zero SQL)
└── No, it's real payroll history you need to keep everything else about →
        Path B: Manual cancel + targeted SQL cleanup for just the affected period

If you're not sure which you have: open Settings → Demo Data in the app. If it shows "Demo data is seeded," you're almost certainly on Path A.

---

Path A — Demo Data Reset + Generate (recommended)

This is a real feature already built for exactly this: DemoDataServiceImpl.reset() wipes payroll-adjacent tables in FK-safe order, and generate() reseeds through the actual production code path (PayrollService.create() → generatePayslips() → decide() → updateStatus()), which means it automatically picks up the rate-table fix — no shortcuts, no hand-written numbers.

What it touches

reset() deletes, in this order (unconditionally, not just Draft runs):

Payslip → PayrollApproval → Payroll → OvertimeRequest → LeaveRequest → LeaveBalance → Bonus → Timesheet

generate() then reseeds, for last month relative to today:

• Timesheets, overtime requests, leave requests/balances, bonuses for all employees
• Two payroll runs: period A (1st–15th) is created → payslips generated → approved → marked Processed; period B (16th–end) is created → payslips generated → left Pending

What it does NOT do

• It does not touch Employee, Position, Department, or the contribution/tax rate tables themselves — only the payroll/attendance side.
• It replaces whatever payroll history existed with just those two fresh runs for last month. If you had manually created extra payroll runs for other months, they're gone after reset() and generate() won't recreate them — only the standard two-run demo baseline.
• It does not touch PayslipHistory, PayrollChange, or PayrollTransaction rows. If any exist for the data being wiped, reset() will hit a Postgres FK violation and fail. In a fresh dev environment this is normally empty; if it isn't, see Path B's cleanup order.

Steps

Via the UI:

1. Log in as a user with system.admin.demo.data.manage (System Administrator role).
2. Go to Settings.
3. Click Reset Demo Data → confirm. Wait for it to finish.
4. Click Generate Demo Data → confirm.
5. Go to Payroll Runs and confirm the two new runs show correct numbers (spot-check a payslip whose taxable income lands near a bracket boundary, e.g. ~₱20,833, since that's the exact bug we just fixed).

Via the API (if you'd rather script it):

TOKEN=$(curl -s -X POST http://localhost:8080/api/auth/login \
  -H "Content-Type: application/json" \
  -d '{"username":"sysadmin_demo","password":"<the seeded demo password>"}' | jq -r .token)

curl -s -X POST http://localhost:8080/api/system-admin/demo-data/reset \
  -H "Authorization: Bearer $TOKEN"

curl -s -X POST http://localhost:8080/api/system-admin/demo-data/generate \
  -H "Authorization: Bearer $TOKEN"

---

Path B — Manual: keep everything else, fix specific payroll runs

Use this only if you have real data you can't afford to wipe wholesale. There's no API for this — the app deliberately doesn't expose deleting non-Draft payroll data (that's an intentional guard, not an oversight), so this is a direct-DB operation. Treat it like the employee-101 cleanup from earlier in this project: introspect before you delete, never assume table names.

1. Identify the affected payroll run(s)

SELECT payroll_id, payroll_run_date, period_start_date, period_end_date, status
FROM payroll
ORDER BY period_start_date DESC;

Note the payroll_id(s) generated before the rate fix.

2. Check what references those payslips

SELECT p.payslip_id, p.payslip_number, p.employee_id
FROM payslip p
WHERE p.payroll_id IN (/* your payroll_id list */);

Then check dependents before deleting anything:

SELECT 'payslip_history' t, count(*) FROM payslip_history WHERE payslip_id IN (SELECT payslip_id FROM payslip WHERE payroll_id IN (/* ids */))
UNION ALL SELECT 'payroll_transactions', count(*) FROM payroll_transactions WHERE payroll_id IN (/* ids */) OR payslip_id IN (SELECT payslip_id FROM payslip WHERE payroll_id IN (/* ids */))
UNION ALL SELECT 'payroll_changes', count(*) FROM payroll_changes WHERE payroll_id IN (/* ids */)
UNION ALL SELECT 'payroll_approval', count(*) FROM payroll_approval WHERE payroll_id IN (/* ids */);

3. Delete in FK-safe order (mirrors DemoDataServiceImpl.reset(), scoped to your specific run)

DELETE FROM payroll_transactions WHERE payroll_id IN (/* ids */) OR payslip_id IN (SELECT payslip_id FROM payslip WHERE payroll_id IN (/* ids */));
DELETE FROM payslip_history WHERE payslip_id IN (SELECT payslip_id FROM payslip WHERE payroll_id IN (/* ids */));
DELETE FROM payroll_changes WHERE payroll_id IN (/* ids */);
DELETE FROM payslip WHERE payroll_id IN (/* ids */);
DELETE FROM payroll_approval WHERE payroll_id IN (/* ids */);
DELETE FROM payroll WHERE payroll_id IN (/* ids */);

4. Recreate through the app, not SQL

Once the old rows are gone, create the replacement run the normal way so it goes through the real calculation engine:

1. Payroll Runs → New Payroll Run — same periodStartDate/periodEndDate as the run you deleted.
2. Click Generate Payslips on the new Draft run — this is the step that reads the now-fixed SSS/withholding tables live.
3. Approve → Mark Processed, same as before, if that run needs to reach a terminal state.

5. Verify

Pull the Alphalist or 1601-C for the affected month and confirm:

• No duplicate employees (would indicate step 3 didn't fully clean up)
• A spot-checked employee's withholding tax matches manual calculation against the corrected brackets, especially near a bracket boundary

---

Quick reference: payroll status rules (why you can't just "fix in place")

Action	Allowed from status	Result
POST /api/payroll	—	creates Draft
POST .../generate-payslips	Draft only, and only once	→ Pending
PATCH .../decision	Pending only	→ Approved / Rejected
PATCH .../status → Processed	Approved only	→ Processed (terminal)
PATCH .../status → Cancelled	anything except Processed/Cancelled	→ Cancelled (terminal, does not delete payslips)
DELETE /api/payroll/{id}	anything except Processed, and only while no payslips have been generated	actually removes the row

Source: PayrollServiceImpl.java (create, generatePayslips, decide, updateStatus, delete).