DEV Community

MidnightEcho794261
MidnightEcho794261

Posted on

Postgres Scheduled Cleanup and Support Webhook Retries (and the Small SaaS Boundary)

The operational constraint is simple: a support webhook may be delivered twice, while a cleanup job may run late. My choice is to make the database the record of intent, then let a scheduler and a worker compete safely for bounded batches. That decision makes BullMQ, a hosted queue, and RabbitMQ implementation details rather than the source of truth.

Short answer: for a small SaaS serving EU and US customers, start with a Postgres job ledger plus a modest worker; add a queue when delay, throughput, or isolation is a measured problem. The ledger's idempotency key is what prevents duplicate webhook effects, and the cleanup schedule only creates work.

This is less glamorous than comparing monthly plans. It is easier to audit at 02:00 when a customer asks why their ticket update arrived twice.

The failure is a missing record, not a missing timer

Cron is a trigger. It does not remember which customer rows were deleted, nor whether a webhook consumer acknowledged an attempt. A process restart between sending an HTTP request and marking it complete creates the classic ambiguity: retry and risk a duplicate, or skip and lose the event.

For both scheduled data cleanup and outbound support notifications, I use a durable row with an immutable key. A cleanup row can identify a tenant and a cutoff timestamp. A webhook row can use ticket_id:event_version as its idempotency key. The worker claims a small batch, performs an idempotent effect, and records the result. The same input can be attempted again without changing the final state.

Postgres provides the locking primitive needed for competing workers. FOR UPDATE SKIP LOCKED lets one worker claim available rows while another skips rows already claimed, instead of waiting behind a long transaction. Keep the claim transaction short; the network call belongs outside it.

import { Pool } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

export async function claimWebhookBatch(limit = 50) {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    const result = await client.query(
      `UPDATE webhook_delivery
       SET status = 'processing', claimed_at = now(), attempts = attempts + 1
       WHERE id IN (
         SELECT id
         FROM webhook_delivery
         WHERE status = 'pending'
           AND next_attempt_at <= now()
         ORDER BY next_attempt_at, id
         FOR UPDATE SKIP LOCKED
         LIMIT $1
       )
       RETURNING id, endpoint, payload, idempotency_key, attempts`,
      [limit],
    );
    await client.query("COMMIT");
    return result.rows;
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release();
  }
}
Enter fullscreen mode Exit fullscreen mode

The unique constraint on idempotency_key is the backstop. A receiving service should also honor that key, but your own database must not enqueue the same logical event twice. “Exactly once” is not a property an HTTP client can promise end to end; it is an application effect you design and test.

Where does queue choice stop and delivery policy start?

The right split follows failure boundaries. A scheduler wakes the system and inserts due work. A queue carries work away from the web process. A worker owns retries and timeouts. Postgres records the state transitions that make those pieces observable.

BullMQ is a Node.js queue built on Redis. It brings delayed jobs, concurrency controls, and familiar TypeScript workers when Redis is already part of the system. The trade-off is another durable system to monitor: Redis persistence, failover, memory pressure, and recovery become part of the delivery story.

A hosted queue moves that operational surface to a provider. You trade infrastructure work for provider-specific limits, regional placement decisions, and an external control plane. Check EU data residency, dead-letter behavior, maximum message size, and how exports work before committing. “Hosted” describes ownership, not a delivery guarantee.

RabbitMQ offers explicit acknowledgements, routing, and dead-letter exchanges. Those primitives matter when several services need broker-level routing or an operations team already knows its recovery procedures. For a small SaaS, the cost is usually the operational ceremony: cluster upgrades, durable queues, consumer back-pressure, and alarm tuning.

Approach Operational boundary Useful signal Main limitation
Postgres table plus worker Your database and process Low volume, one ownership team Manual scaling and retry policy
BullMQ with Redis Your Redis and workers Delays and high concurrency in Node.js Redis recovery is your responsibility
Hosted queue Provider control plane plus your consumers Less broker maintenance Residency, retention, and export limits vary
RabbitMQ Broker cluster and acknowledgements Many routing relationships More operations and tuning

None of these systems deduplicates the business effect for you. A broker can redeliver an unacknowledged message by design. That is healthy at-least-once behavior; the handler must make it harmless.

The queue is not the ledger.

How can a bounded retry loop make cleanup and delivery auditable?

I keep retry policy in data, not in a pile of timer callbacks. Each row has attempts, next_attempt_at, last_error_class, and completed_at. Retry transient DNS failures and 5xx responses with exponential backoff and jitter. Stop on a permanent 4xx response after recording the response class, then place the row in a reviewable dead-letter state.

The cleanup worker uses the same shape. It selects a tenant's expired records in pages, commits each page, and advances a checkpoint. A five-minute schedule that finds no rows is a successful run, not an incident. A run that exceeds its time budget records a continuation row for the next window instead of holding a transaction open overnight.

One compact handler illustrates the boundary:

type Delivery = {
  id: string;
  endpoint: string;
  payload: unknown;
  idempotency_key: string;
  attempts: number;
};

export async function deliver(item: Delivery) {
  const response = await fetch(item.endpoint, {
    method: "POST",
    headers: {
      "content-type": "application/json",
      "idempotency-key": item.idempotency_key,
    },
    body: JSON.stringify(item.payload),
    signal: AbortSignal.timeout(8_000),
  });

  if (response.ok) return { state: "complete" as const };
  if (response.status >= 400 && response.status < 500) {
    return { state: "dead_letter" as const, reason: `http_${response.status}` };
  }
  return { state: "retry" as const, reason: `http_${response.status}` };
}
Enter fullscreen mode Exit fullscreen mode

The important detail is what happens after a timeout. The sender cannot know whether the receiver applied the event before the connection died. It records an uncertain attempt and retries with the same key. The receiver's deduplication table turns that uncertainty into one effect.

Retries need an owner.

I once treated a cleanup timeout as proof that no rows had changed. That assumption made the next run repeat work and inflated webhook traffic. The fix was boring: measure rows affected and checkpoint only after the commit, retain the previous checkpoint with the run identifier, compare the affected-row count to the tenant's expected range, and alert only when the discrepancy persists across two runs. This creates enough evidence to distinguish a slow query from a duplicate delivery without guessing from queue depth.

Boring is good here.

How should a small SaaS schedule data cleanup across EU and US regions?

Measure the behavior that customers experience, not just queue depth. Track schedule lateness (time from due timestamp to claim), delivery age, retry count by error class, duplicate suppression count, dead-letter age, and cleanup rows per committed batch. Slice each metric by region and tenant so a noisy US tenant cannot hide an EU residency or latency issue.

Run failure drills in staging: kill a worker after the HTTP response but before the database update; pause the scheduler for an hour; fill the queue; revoke an endpoint credential; and restore a database snapshot. The expected result is recoverable pending work and zero duplicate business effects. Your runbook should name the person who replays a dead-letter row and the evidence they record. Measure twice.

For cost, count operator-hours and recovery complexity alongside compute. BullMQ, a hosted queue, and RabbitMQ can each be inexpensive at low volume, then become costly in different places. Your mileage may vary because regional egress, retention, and support contracts differ. I’m not sure a price worksheet can predict the winning option until you know your peak batch size and incident budget.

The catch: when this design is the wrong fit

Do not put a broker in front of a single short cleanup query just to feel distributed. A database scheduler and one worker are easier to reason about when the job is bounded, idempotent, and below your maintenance window. Conversely, stick with a managed workflow or a broker when you need multi-step compensation, millions of delayed messages, strict tenant isolation, or independent teams publishing to many routing keys.

This design is also unsuitable when the receiver cannot accept an idempotency key and the side effect is inherently non-repeatable, such as a one-time charge without a provider-side idempotency contract. In that case, change the integration or require a reconciliation process; changing queue brands will not create exactly-once delivery.

The decision rule is deliberately plain: keep scheduling outside the effect and keep state in a durable ledger. Introduce a broker only when a measured latency, throughput, routing, or isolation requirement justifies its operational boundary. That is how a small SaaS can clean data on schedule while retrying support webhooks without making customers pay for the same event twice.

References

Top comments (0)