Add missing database indexes for the webhook queue, overdue sweep, and merchant invoice lists

[Cedarich]

• Complexity: Hard
• Labels: Backend, database, performance, Hard
• Overview: The tables driving the backend's hottest queries have no indexes supporting them. WebhookDelivery declares no @@index at all, yet a cron polls it every sixty seconds with a filtered, sorted query. The Invoice model indexes merchantId, (isDraft, merchantId), and lastAutoSavedAt, none of which serve the daily overdue sweep or the merchant dashboard's status-filtered lists. These queries work fine on a demo dataset and degrade continuously as real data accumulates.
• Details:
  • WebhookDelivery has only @@map("webhook_deliveries") — no indexes whatsoever. WebhooksService.processQueue() runs every minute and issues findMany({ where: { status: "pending", nextAttemptAt: { lte: now } }, take: 50, include: { user: true }, orderBy: { createdAt: "asc" } }). With no index on (status, nextAttemptAt) and none on createdAt, Postgres sequentially scans the table and sorts the matches on every tick.
  • Delivery rows are never pruned. Successful deliveries stay success forever, so the scanned table grows monotonically with payment volume while the number of actually-pending rows stays near zero. The query gets steadily more expensive to return the same empty result.
  • WebhookDelivery.invoiceId and WebhookDelivery.userId are foreign keys, and Postgres does not create indexes for foreign keys automatically. Cascade deletes on Invoice and User therefore scan the delivery table as well.
  • InvoicesService.handleOverdueInvoices() queries where: { status: "pending", dueDate: { lt: now } } daily. There is no (status, dueDate) index, so this is another full scan that grows with total invoice count rather than with the number of genuinely overdue invoices.
  • The merchant dashboard's primary list filters by merchant and status and orders by recency. The existing @@index([merchantId]) covers only the first predicate, so the database filters by status and sorts the remainder in memory for every page load — the most frequently executed query in the product.
  • Other tables show the right instincts, which makes the gaps look like oversights rather than a deliberate strategy: RecurringSchedule has @@index([status, nextRunDate]), exactly the shape the webhook queue needs, and WebhookDeadLetter has @@index([status, exhaustedAt]).
  • There is no slow-query visibility to catch this in production. SLOW_DB_THRESHOLD_MS is validated in the Joi schema and the observability module exists, so the hook is available but these paths are not surfacing anything actionable.
• Scope:
  • Add the composite indexes the hot queries need, at minimum covering the webhook queue poll, the overdue sweep, and the merchant list-with-status-filter path.
  • Add indexes for the foreign keys on WebhookDelivery so cascade deletes and relation lookups do not scan.
  • Review every scheduled job and list endpoint against the schema and add the supporting index or fix the query, rather than only the paths named here.
  • Introduce a retention or archival policy for completed webhook deliveries so the queue table does not grow without bound; this pairs with the claim-locking work in Add cross-instance claim locking for the webhook queue and scheduled backend jobs #442, which will add status transitions to the same table.
  • Verify the chosen indexes against realistic data volumes with query plans, rather than adding them speculatively.
  • Surface slow queries through the existing observability configuration so future regressions are visible before they become incidents.
  • Ensure the migration adds indexes without a disruptive lock on tables that are actively written.
• Technical scope:
  • backend/prisma/schema.prisma
  • backend/prisma/migrations/
  • backend/src/webhooks/webhooks.service.ts
  • backend/src/invoices/invoices.service.ts
  • backend/src/observability/structured-logger.service.ts
  • backend/src/config/observability.config.ts
• Acceptance criteria:
  • The webhook queue poll uses an index and does not sequentially scan the delivery table.
  • The overdue-invoice sweep uses an index on its filter predicate.
  • The merchant invoice list filtered by status and ordered by recency is served by an index rather than an in-memory sort.
  • Foreign keys on WebhookDelivery are indexed.
  • Completed webhook deliveries are subject to a documented retention policy rather than accumulating indefinitely.
  • Query plans for the named paths are captured against a realistic dataset and show index usage.
  • Slow queries above the configured threshold are reported through the existing observability stack.
  • Index migrations apply without blocking writes on active tables.

[Rayyanah0]

hi, i will like to work on this issue

[Zarmaijemimah]

Hi maintainer I’ll love to work on this issue please kindly assign thank you

[grantfox-oss]

🦊 GrantFox — @Zarmaijemimah 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 #499)
2. Your PR will be reviewed by the ZyntariHQ maintainers

Good luck! Track your progress on GrantFox.

[grantfox-oss]

🦊 GrantFox — @Rayyanah0 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 #499)
2. Your PR will be reviewed by the ZyntariHQ maintainers

Good luck! Track your progress on GrantFox.