tech-debt: Replace multi-step payroll JS flow with atomic PostgreSQL RPC to eliminate TOCTOU race condition

[coderabbitai]

Background

In lib/payroll-actions.ts, the runPayroll function currently performs a multi-step flow using separate PostgREST/Supabase client calls:

1. Idempotency check (select existing run)
2. insertPayrollRun
3. insertPayrollRecords
4. updatePayrollRun

Because each step is a separate HTTP request to PostgREST, this introduces two problems:

1. TOCTOU (Time-of-Check-to-Time-of-Use) Race Condition

Two concurrent requests could both pass the idempotency check (lines ~108–114) before either has inserted a run, resulting in duplicate payroll_run records for the same pay period.

2. Partial Failures / No Atomicity

If insertPayrollRecords fails mid-way (e.g. network error), some payroll records may have already been inserted while others have not. There is no rollback mechanism in the current JS-level orchestration.

Recommended Solution

Replace the multi-step JS flow with a single PostgreSQL stored procedure called via supabase.rpc(). PostgREST automatically wraps rpc() calls in a transaction, making the entire operation atomic.

High-level steps:

1. Create a PostgreSQL function (e.g. run_payroll(pay_period_start, pay_period_end, user_id)) that:
   • Checks for an existing PROCESSING/COMPLETED run using SELECT ... FOR UPDATE to prevent races
   • Inserts the payroll_run record
   • Inserts all payroll_records
   • Computes totals and updates the run to COMPLETED
   • Raises an exception on conflict or failure (triggering automatic rollback)
2. In lib/payroll-actions.ts, replace the orchestration logic in runPayroll with a single call: supabase.rpc('run_payroll', { pay_period_start: payPeriodStart, pay_period_end: payPeriodEnd, user_id: userId })

References

• PR: feat: Implemented core payroll logic #22
• Review comment: feat: Implemented core payroll logic #22 (comment)
• Requested by: @SamuelIVX