-- 001_relay_api_data_model.sql
-- First-party starter migration for webhook relay API data model (CC BY 4.0)
-- InfiniSynapse desk composite · 2026-07-31 · not a vendor SLA

CREATE TABLE IF NOT EXISTS relay_endpoints (
  id uuid PRIMARY KEY,
  tenant_id uuid NOT NULL,
  url text NOT NULL,
  secret text NOT NULL,
  status text NOT NULL DEFAULT 'active',
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS relay_subscriptions (
  id uuid PRIMARY KEY,
  endpoint_id uuid NOT NULL REFERENCES relay_endpoints(id),
  event_types text[] NOT NULL DEFAULT '{}',
  enabled boolean NOT NULL DEFAULT true,
  max_attempts int NOT NULL DEFAULT 8,
  backoff_seconds int[] NOT NULL DEFAULT '{60,300,900,3600}'
);

CREATE TABLE IF NOT EXISTS relay_events (
  id uuid PRIMARY KEY,
  source text NOT NULL,
  type text NOT NULL,
  idempotency_key text NOT NULL,
  payload jsonb NOT NULL,
  payload_hash text NOT NULL,
  signature_verified boolean NOT NULL DEFAULT false,
  signature_algorithm text,
  received_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (source, idempotency_key)
);

CREATE TABLE IF NOT EXISTS relay_deliveries (
  id uuid PRIMARY KEY,
  event_id uuid NOT NULL REFERENCES relay_events(id),
  endpoint_id uuid NOT NULL REFERENCES relay_endpoints(id),
  state text NOT NULL,
  next_attempt_at timestamptz,
  attempt_count int NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS relay_deliveries_poll_idx
  ON relay_deliveries (state, next_attempt_at);

CREATE TABLE IF NOT EXISTS relay_attempts (
  id uuid PRIMARY KEY,
  delivery_id uuid NOT NULL REFERENCES relay_deliveries(id),
  status_code int,
  duration_ms int,
  error text,
  body_snippet text,
  created_at timestamptz NOT NULL DEFAULT now()
);
