ERP Event Messaging Outbox Table SQL Generator

-- Generic names: adapt column names and keys to your own schema.
CREATE TABLE IF NOT EXISTS messaging_outbox (
  id            BIGSERIAL PRIMARY KEY,
  channel       VARCHAR(20)  NOT NULL,
  event_type    VARCHAR(60)  NOT NULL,
  dedupe_key    VARCHAR(120) NOT NULL,
  recipient     VARCHAR(120) NOT NULL,
  payload       JSONB        NOT NULL,
  status        VARCHAR(12)  NOT NULL DEFAULT 'pending',
  attempts      INT          NOT NULL DEFAULT 0,
  next_attempt  TIMESTAMPTZ  NOT NULL DEFAULT now(),
  provider_id   VARCHAR(120),
  last_error    TEXT,
  created_at    TIMESTAMPTZ  NOT NULL DEFAULT now(),
  sent_at       TIMESTAMPTZ,
  CONSTRAINT uq_messaging_outbox_dedupe UNIQUE (channel, dedupe_key)
);

CREATE INDEX IF NOT EXISTS ix_messaging_outbox_due
  ON messaging_outbox (next_attempt) WHERE status = 'pending';

INSERT INTO messaging_outbox (channel, event_type, dedupe_key, recipient, payload)
VALUES ('whatsapp', 'shipment_dispatched', 'SO-104522-dispatch', '919800000000',
        '{"order":"SO-104522","eta":"2026-10-21"}')
ON CONFLICT (channel, dedupe_key) DO NOTHING;

WITH due AS (
  SELECT id FROM messaging_outbox
  WHERE status = 'pending' AND next_attempt <= now() AND attempts < 6
  ORDER BY next_attempt
  LIMIT 20
  FOR UPDATE SKIP LOCKED
)
UPDATE messaging_outbox o
SET status = 'sending', attempts = o.attempts + 1
FROM due
WHERE o.id = due.id
RETURNING o.id, o.channel, o.recipient, o.payload;

-- On success
UPDATE messaging_outbox SET status = 'sent', sent_at = now(), provider_id = 'wamid.XXXX' WHERE id = 1;

-- On transient failure: exponential backoff, back to pending
UPDATE messaging_outbox
SET status = 'pending', last_error = 'HTTP 429',
    next_attempt = now() + (power(2, attempts) * interval '30 seconds')
WHERE id = 1;

-- Give up after the maximum attempts
UPDATE messaging_outbox SET status = 'failed' WHERE status = 'pending' AND attempts >= 6;

WITH due AS (
  SELECT TOP (20) *
  FROM messaging_outbox WITH (UPDLOCK, READPAST, ROWLOCK)
  WHERE status = 'pending' AND next_attempt <= SYSUTCDATETIME() AND attempts < 6
  ORDER BY next_attempt
)
UPDATE due
SET status = 'sending', attempts = attempts + 1
OUTPUT inserted.id, inserted.channel, inserted.recipient, inserted.payload;