Add cross-instance claim locking for the webhook queue and scheduled backend jobs

[Cedarich]

• Complexity: Hard
• Labels: Backend, Hard, reliability, queue, automation, operations
• Overview: Every background worker in the backend guards itself with an in-process boolean or nothing at all, which is only correct while exactly one instance is running. WebhooksService.processQueue() guards on this.isProcessing, and HorizonWatcherService.pollPayments() guards on this.polling. Those fields are per-process. As soon as the API runs more than one replica — which is the normal deployment shape behind a load balancer or on a platform that scales automatically — every replica runs every cron and every poll loop concurrently, and the guards do nothing.
• Details:
  • processQueue() selects up to 50 webhookDelivery rows with status: "pending" and nextAttemptAt <= now, then delivers each one. There is no atomic claim — no transition to an in-flight status, no FOR UPDATE SKIP LOCKED, no lease. Two replicas hitting the same minute boundary select the same rows and deliver each webhook twice. The x-idempotency-key header is derived from ${delivery.id}-${delivery.attempts}, so both duplicates carry the same key and the merchant cannot distinguish a duplicate from a retry.
  • InvoicesService.handleOverdueInvoices() (@Cron("0 2 * * *")) selects overdue invoices and updates them with no claim, so concurrent replicas repeat the same status transitions and emit duplicate status-history rows, activity-feed events, and notifications.
  • NotificationsService runs a five-minute cron with the same exposure.
  • RecurringBillingService.processDueSchedules() is the one worker that is actually safe, because RecurringInvoiceRun carries a unique (scheduleId, periodKey) constraint that rejects duplicate generation at the database layer. That is the pattern the other workers should follow.
  • The practical result is that horizontal scaling is currently unsafe: it produces duplicate webhook deliveries and duplicate merchant-visible events rather than more throughput.
• Scope:
  • Introduce a shared claim mechanism for queued work — an atomic status transition with a lease and expiry, or SELECT ... FOR UPDATE SKIP LOCKED — so a delivery row is processed by exactly one replica.
  • Add cross-instance coordination for the scheduled jobs, either via a leader lock or by making each job's effects idempotent at the database layer the way recurring billing already is.
  • Make the webhook idempotency key stable per logical delivery attempt so receivers can safely deduplicate.
  • Reclaim leases abandoned by a crashed replica so work is not stranded in an in-flight state.
  • Document the supported replica count and the coordination guarantees in the backend README.
• Technical scope:
  • backend/src/webhooks/webhooks.service.ts
  • backend/src/invoices/invoices.service.ts
  • backend/src/notifications/notifications.service.ts
  • backend/src/stellar/horizon-watcher.service.ts
  • backend/src/recurring-billing/recurring-billing.service.ts
  • backend/prisma/schema.prisma
• Acceptance criteria:
  • Running two backend replicas against one database delivers each pending webhook exactly once.
  • The overdue-invoice and notification crons produce one set of status transitions, activity events, and notifications regardless of replica count.
  • A delivery claimed by a replica that then crashes is reclaimed and retried after its lease expires.
  • The webhook idempotency key identifies a logical delivery attempt and is stable across replicas.
  • Tests exercise concurrent workers against a shared database and assert single-execution behaviour.
  • The backend README states the supported replica count and the coordination model.

[Naajih09]

Hi, I'd like to work on this issue. I've gone through the description and acceptance criteria and I'm confident I can deliver it. I'll make sure every acceptance criterion is met and submit a clean, tested PR. Please assign it to me.

[grantfox-oss]

🦊 GrantFox — @Naajih09 has been assigned to this issue as part of the Third Campaign campaign!

Next steps:

1. Open a Pull Request referencing this issue (e.g., Closes #442)
2. Your PR will be reviewed by the ZyntariHQ maintainers

Good luck! Track your progress on GrantFox.