Skip to main content

20. Payroll period close (pay-run preparation)

Date: 2026-06-06

Status​

Accepted

Context​

Step 10 wires the corrected pure pay engine (src/lib/pay, ADR-0012/§1 of the build plan) to real data so an admin can close a bi-monthly period and prepare contractor pay (PHP). The engine is pure and already proven by golden tests; what was missing was the orchestration: pull each contractor's rate, time entries and approved holidays for a period, run the engine, persist the result, and freeze the inputs so the snapshot can't drift.

The schema already had the right tables from migration 0002: payroll_batches (one row per company+period, with a draft → prepared → funded → closed status and a unique (company_id, period_start, period_end) guard) and payments (one row per contractor, with hours_worked / contracted_hours / ratio / amount_centavos, an idempotency_key, and Wise fields for Step 14). So this step reuses those tables rather than inventing pay_runs/payslips.

Two correctness hazards drove the design:

  1. Partial writes. Preparing a run touches three things (the batch row, N payment rows, and locking the period's time entries). Done as separate client calls, a mid-sequence failure leaves money records half-written.
  2. Status-column overloading. time_entries.status carries both the contractor lifecycle (draft → submitted) and the overage/facility-approval lifecycle (pending_approval → approved) and the payroll lock (locked). Naively locking everything would strand overage entries that the facility still needs to approve for invoicing.

Decision​

  • Money representation unchanged: the engine takes/returns integer centavos; payments.amount_centavos/payroll_batches.total_centavos are bigint. No floats anywhere (project rule, ADR-0006).
  • Atomic close via a SECURITY DEFINER RPC. public.close_payroll_period(company, period_start, period_end, total_centavos, prepared_by, assignment_ids, expected_entry_count, expected_hours, payments jsonb). In one transaction it: guards against an existing batch for the period (also enforced by the unique constraint), locks the priced entries FOR UPDATE and re-verifies they still match what was priced (count + rounded hours — see TOCTOU below), inserts the prepared batch, inserts the per-contractor payments (status pending_review), and locks the period's paid entries. It is revoked from public/anon/authenticated and granted only to service_role; the closePeriod server action calls it after an explicit owner-only check. Pay computation happens in TypeScript (the pure engine); the RPC only persists.
  • Lock semantics — scoped to the paid assignments. Lock draft/submitted/approved → locked, but only for the assignment_ids that actually produced a payment row. Entries on assignments that were not paid (a contractor's non-primary/second-facility assignment, a null-rate assignment, or a zero-pay/soft-deleted contractor) are left editable and payable rather than frozen as locked-but-unpaid. pending_approval is also left unlocked so the Step-9 facility magic-link approval can still flip it; that overage lifecycle is independent of contractor PHP pay (capped at ratio 1.0, so unapproved overage never changes the amount owed). Unresolved pending_approval days are surfaced as a warning in the preview, not a hard block.
  • TOCTOU safety (migration 0014). Pay is computed in TypeScript before the RPC runs, so a concurrent edit/submit/sync could otherwise let the locked set diverge from the priced set. The action passes the priced assignment_ids plus the priced entry count and hours sum; inside the close transaction the RPC re-reads those entries FOR UPDATE and aborts the whole close if either differs ("hours changed since preview"). The action additionally binds the owner-confirmed total (a hidden field) and refuses if a recompute moved it. Together these guarantee the persisted snapshot equals what was priced and confirmed.
  • Audit attribution (migration 0014). The RPCs run under the service role, where auth.uid() is null, so the audit trigger would record a null actor on the most sensitive writes. The RPCs set a transaction-local app.actor_id GUC and app.audit_row() prefers it over auth.uid(), so close/discard are attributed to the acting owner.
  • Reversibility. public.discard_payroll_batch(batch_id, company, actor) (owner-only, RPC) unlocks only the batch's paid assignments' locked entries back to submitted, deletes the run's payments, and deletes the batch — an undo path for a mistaken prepared (never funded/closed) run. Once a run is funded (Step 14, Wise), it can no longer be discarded.
  • Payslip transparency + audit. payments gains breakdown jsonb (the per-week WeekPayResult[] snapshot — "32.0 / 85.7 = 37.3% × allocation") and payment_date date (the 15-day-arrears payout date from paymentDateForPeriod). Existing audit triggers on payroll_batches/payments capture the writes.
  • Two-step UI. /admin/payroll previews a run (read-only computePayRun, human-meaningful counts and total) before an explicit confirm, matching the "show totals, hard-confirm high-stakes money actions" preferences.
  • One primary assignment per contractor per period. For each contractor the run uses the assignment version with the latest effective_date overlapping the period that has a non-null bimonthly_rate_centavos. Contractors with zero computed pay are skipped (no empty payment rows).

Consequences​

  • Closing a period is atomic and idempotent: the unique constraint + in-transaction guard make a double-close impossible; re-running returns a friendly "already prepared".
  • The prepared payments rows are the immutable pay snapshot and the input to Step 14 (Wise draft transfers) — funding stays owner-driven and out of code (ADR-0007). No funding call is introduced here.
  • Known limitations (deferred, consistent with the build plan):
    • Mid-period rate change is not split; the latest overlapping assignment rate is used for the whole period. Flagged here; revisit if real rate changes land mid-period.
    • Multiple concurrent assignments (one contractor at two facilities in the same period) pays only the primary; multi-assignment payroll needs a UI + model extension.
    • Statutory deductions / tax (PH BIR, SSS/PhilHealth/Pag-IBIG) and USD reference conversion are not modeled — gross PHP only. Net/deductions land with the tax decision noted in the plan's prep checklist.
  • Discard restores locked → submitted (it cannot perfectly restore the prior draft/approved distinction); acceptable for undoing a not-yet-funded run, and documented in the action.