Webhook Dispatcher — Data Model

Status: populated Owner: Platform Engineering Last updated: 2026-04-18 Companion: DOMAIN_MODEL · APPLICATION_LOGIC

1. Schema Ownership

Schema	Owner service	Access mode
hook	webhook-dispatcher	Read + Write (full ownership)

2. hook.webhook_configs

CREATE TABLE hook.webhook_configs (
  webhook_id   UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id   UUID         NOT NULL,
  url          VARCHAR(2048) NOT NULL,
  secret_enc   BYTEA        NOT NULL,      -- AES-256-GCM encrypted secret
  description  VARCHAR(255),
  events       TEXT[]       NOT NULL DEFAULT ARRAY['DLR_DELIVERED','DLR_FAILED','DLR_UNDELIVERED','DLR_EXPIRED','DLR_REJECTED','DLR_UNKNOWN'],
  is_active    BOOLEAN      NOT NULL DEFAULT TRUE,
  created_at   TIMESTAMPTZ  NOT NULL DEFAULT now(),
  updated_at   TIMESTAMPTZ  NOT NULL DEFAULT now()
);

CREATE INDEX hook_webhook_configs_account_id_idx
  ON hook.webhook_configs (account_id)
  WHERE is_active = TRUE;

-- Enforce max 10 webhooks per account (CHECK via trigger)
CREATE OR REPLACE FUNCTION hook.enforce_max_webhooks_per_account()
RETURNS TRIGGER AS $$
BEGIN
  IF (SELECT COUNT(*) FROM hook.webhook_configs
      WHERE account_id = NEW.account_id AND is_active = TRUE) >= 10 THEN
    RAISE EXCEPTION 'Maximum 10 active webhooks per account';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER max_webhooks_per_account
  BEFORE INSERT ON hook.webhook_configs
  FOR EACH ROW EXECUTE FUNCTION hook.enforce_max_webhooks_per_account();

Secret Storage

The secret_enc column stores the signing secret encrypted with AES-256-GCM using an application-level key derived from a KMS-managed master key. The plaintext secret is decrypted in-process only during signature generation and is never logged.

3. hook.delivery_attempts

CREATE TABLE hook.delivery_attempts (
  attempt_id           UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  delivery_id          UUID         NOT NULL,    -- stable across retries
  webhook_id           UUID         NOT NULL REFERENCES hook.webhook_configs(webhook_id),
  account_id           UUID         NOT NULL,
  event_id             VARCHAR(64)  NOT NULL,    -- source eventId (dedup key)
  attempt_number       SMALLINT     NOT NULL DEFAULT 1,
  status               VARCHAR(16)  NOT NULL DEFAULT 'PENDING',
  http_status_code     SMALLINT,
  response_body_preview TEXT,                    -- first 512 chars
  error_message        TEXT,
  scheduled_at         TIMESTAMPTZ  NOT NULL,
  attempted_at         TIMESTAMPTZ,
  next_retry_at        TIMESTAMPTZ,
  payload_snapshot     JSONB        NOT NULL,    -- immutable copy of dispatch payload
  created_at           TIMESTAMPTZ  NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX hook_delivery_attempts_event_webhook_attempt_idx
  ON hook.delivery_attempts (event_id, webhook_id, attempt_number);

CREATE INDEX hook_delivery_attempts_pending_idx
  ON hook.delivery_attempts (next_retry_at ASC)
  WHERE status = 'PENDING' OR status = 'FAILED_RETRY';

CREATE INDEX hook_delivery_attempts_delivery_id_idx
  ON hook.delivery_attempts (delivery_id);

CREATE INDEX hook_delivery_attempts_account_status_idx
  ON hook.delivery_attempts (account_id, status, created_at DESC);

Retry State Machine (no Redis required)

The status + next_retry_at columns on hook.delivery_attempts form the retry queue. A scheduled in-process worker polls:

SELECT * FROM hook.delivery_attempts
WHERE (status = 'PENDING' OR status = 'FAILED_RETRY')
  AND next_retry_at <= now()
ORDER BY next_retry_at ASC
LIMIT 50
FOR UPDATE SKIP LOCKED;

4. Flyway Migration Naming

V1__create_hook_schema.sql
V2__create_webhook_configs.sql
V3__create_delivery_attempts.sql
V4__add_delivery_status_check_constraint.sql
V5__add_max_webhooks_trigger.sql
V6__add_payload_snapshot_compression.sql

5. Retention Policy

Table	Retention	Mechanism
hook.delivery_attempts (SUCCESS)	30 days	Scheduled cleanup job
hook.delivery_attempts (DEAD_LETTER)	90 days	Scheduled cleanup job
hook.webhook_configs	Indefinite (soft-delete via is_active)	Manual audit + purge