CRM-to-Billing Dead-Letter Queue (DLQ) & Exponential Retry Playbook

Out of 105,000 monthly events (~1.5% transient HTTP 429/502/503 rate). HubSpot 100-requests/10s burst rate limits (HTTP 429) during billing cycles cause lost deal stage updates and failed license provisioning without exponential backoff.

Eliminates manual CSV exports, Stripe-to-CRM ledger diffs, and hand-replayed webhook payloads.

Included in Vault #01, #06, #19 & #48 (Stripe-to-CRM Sync + DLQ Replay Worker) inside the $79 StackROI 50-Blueprint Vault.

Uses FOR UPDATE SKIP LOCKED so multiple n8n workers can poll every 5 minutes without double-replaying a single customer event:

-- PostgreSQL Dead-Letter Queue (DLQ) + Concurrent Worker Lock Schema
CREATE TABLE IF NOT EXISTS stackroi_dlq_events (
  id BIGSERIAL PRIMARY KEY,
  event_id TEXT NOT NULL UNIQUE,
  workflow_slug TEXT NOT NULL DEFAULT 'stripe-hubspot',
  payload JSONB NOT NULL,
  http_status INT NOT NULL DEFAULT 500,
  last_error TEXT NOT NULL,
  attempt_count INT NOT NULL DEFAULT 1,
  max_attempts INT NOT NULL DEFAULT 6,
  next_retry_at TIMESTAMPTZ NOT NULL DEFAULT (NOW() + INTERVAL '30 seconds'),
  status TEXT NOT NULL DEFAULT 'queued', -- queued | replaying | resolved | dead
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Atomic 5-Minute Cron Poll Query (Safe for Multi-Worker n8n Instances)
WITH due_batch AS (
  SELECT id
  FROM stackroi_dlq_events
  WHERE status = 'queued'
    AND next_retry_at <= NOW()
    AND attempt_count <= max_attempts
  ORDER BY next_retry_at ASC
  LIMIT 25
  FOR UPDATE SKIP LOCKED
)
UPDATE stackroi_dlq_events d
SET status = 'replaying',
    attempt_count = d.attempt_count + 1,
    next_retry_at = NOW() + (INTERVAL '30 seconds' * POWER(2, d.attempt_count)),
    updated_at = NOW()
FROM due_batch
WHERE d.id = due_batch.id
RETURNING d.id, d.event_id, d.workflow_slug, d.payload, d.attempt_count;

Separates retryable 429/5xx network errors from permanent 400/422 payload errors and routes permanent dead letters to Slack:

// n8n Error Trigger Classifier: Transient Backoff vs. Permanent Dead-Letter
const item = $input.first().json;
const status = Number(item.httpStatus || item.error?.httpCode || 500);
const attempts = Number(item.attempt_count || 1);
const retryableCodes = [408, 425, 429, 500, 502, 503, 504];

const isTransient = retryableCodes.includes(status);
const exhausted = attempts >= 6;
const backoffSeconds = Math.min(3600, 30 * Math.pow(2, attempts));

return [{
  json: {
    event_id: item.event_id || ('dlq_' + Date.now()),
    workflow_slug: 'stripe-hubspot',
    http_status: status,
    attempt_count: attempts,
    next_status: (!isTransient || exhausted) ? 'dead' : 'queued',
    backoff_seconds: backoffSeconds,
    escalate_to_slack: (!isTransient || exhausted),
    diagnostic_summary: (!isTransient)
      ? 'Permanent 4xx schema/auth rejection — routed to Slack #revops-alerts'
      : ('Scheduled exponential retry #' + String(attempts) + ' in ' + String(backoffSeconds) + 's')
  }
}];