Payroll scheduler has no row locking — duplicate payment execution risk across pod replicas

[Wilfred007]

What

Add row-level locking (FOR UPDATE SKIP LOCKED or equivalent) to the due-schedule query in the payroll scheduler so concurrent pods can't pick up and execute the same schedule at the same time.

Why

processDueSchedules() in backend/src/services/scheduleExecutor.ts (L44-150) runs on a node-cron('* * * * *') job initialized unconditionally in backend/src/index.ts (L30) — meaning every backend pod runs this job independently. The query selecting due schedules (SELECT ... WHERE next_run_timestamp <= NOW() AND status='active') has no row locking. k8s/base/backend-hpa.yaml sets minReplicas: 2, so at least two pods are always running this cron. The code's own comment (L95-97) acknowledges there's no real idempotency check on this path — and idempotencyMiddleware is applied to HTTP schedule-creation routes but never reaches this background executor. In production this can mean two pods executing the same due payroll schedule simultaneously — real duplicate Stellar payments to employees.

Scope

In scope:

• Add FOR UPDATE SKIP LOCKED (or a Postgres advisory lock / claim-row pattern) to the schedule-selection query so only one pod claims a given due schedule
• Ensure the claim + status transition happens atomically (single transaction)
• Test coverage simulating two concurrent "pods" processing the same due schedule

Out of scope:

• Changes to how schedules are created or their cron cadence
• The separate backend/src/jobs/part49Jobs.ts daily jobs (tracked in a different issue)

Acceptance Criteria

• Two concurrent calls to processDueSchedules() against the same due schedule result in exactly one execution
• New test demonstrates the race is closed (e.g. two parallel invocations, assert single execution)
• No regression in normal single-pod scheduling behavior

Technical Context

• backend/src/services/scheduleExecutor.ts — processDueSchedules(), L44-150, comment at L95-97
• backend/src/index.ts — L30, unconditional cron init
• k8s/base/backend-hpa.yaml — minReplicas: 2
• idempotencyMiddleware — currently only wired to HTTP routes, not this background path

[Hollujay]

Kindly assign the issues to me, I will surely get them done @Wilfred007

[rhemy-arc]

Hi, I’d like to contribute to this issue.

I understand the root cause: processDueSchedules() is executed by every backend pod through the unconditional cron job, while the query that selects due schedules does not lock the rows it returns. With multiple pods running concurrently, the same active schedule can therefore be selected and executed by more than one pod, potentially resulting in duplicate Stellar payroll payments.

I’d like to address this by implementing an atomic schedule-claiming mechanism using PostgreSQL row-level locking, such as FOR UPDATE SKIP LOCKED.

My approach would be to:

• Update the due-schedule selection query to lock rows while they are being claimed, using FOR UPDATE SKIP LOCKED or an equivalent PostgreSQL-safe approach.
• Wrap the schedule selection and claim/status transition in a single database transaction so another pod cannot claim the same schedule between selection and execution.
• Ensure locked schedules are skipped rather than causing competing scheduler instances to block each other.
• Preserve the existing scheduling behavior for normal single-pod execution.
• Add a concurrency test that runs two processDueSchedules() invocations in parallel against the same due schedule and verifies that only one execution occurs.
• Test failure and transaction behavior to ensure a schedule is not incorrectly left in a claimed state if processing fails.
• Run the existing scheduler and payroll-related tests to verify there are no regressions.

I’ll keep the change scoped to the background scheduler path and will not modify schedule creation, cron cadence, or the separate part49Jobs.ts jobs.

This issue is particularly important because the scheduler triggers payroll payments, so preventing duplicate execution is a correctness and financial-safety requirement rather than just a performance concern. I’d be happy to take this issue and submit a focused PR with the locking/transaction changes and concurrency tests.