-- es_saga_outbox — durable at-least-once delivery of external (Regime-B)
-- emits (plan §P3.2).
--
-- When a step emits a request to an EXTERNAL worker (no in-process handler)
-- and suspends awaiting the response, the request is written here in the SAME
-- transaction as the `StepEmitted` envelope event + the `es_saga_awaits` row.
-- A `SagaWorker` with an `EmitPublisher` configured drains unpublished rows in
-- id order (per owned shard), publishes each, then stamps `published_at`.
-- Publish-then-mark ⇒ at-least-once; consumers dedupe on (instance_id, step)
-- or the IdempotencyKey they echo back.
--
-- With no publisher configured the rows simply accumulate (a durable audit of
-- what was requested); external workers may instead poll es_saga_awaits /
-- envelopes directly (today's documented pattern). shard_id is carried so the
-- drain partitions by owned shard exactly like the timeout/recovery scans.
CREATE TABLE IF NOT EXISTS es_saga_outbox (
    id            BIGSERIAL   PRIMARY KEY,
    saga_name     TEXT        NOT NULL,
    instance_id   TEXT        NOT NULL,
    step          TEXT        NOT NULL,
    event_type    TEXT        NOT NULL,
    payload       JSONB       NOT NULL,
    shard_id      INTEGER     NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    published_at  TIMESTAMPTZ
);

-- Drain scan: unpublished rows for an owned shard, in id (FIFO) order.
-- Partial index keeps it tight — published rows drop out of the index.
CREATE INDEX IF NOT EXISTS idx_saga_outbox_unpublished
    ON es_saga_outbox (saga_name, shard_id, id)
    WHERE published_at IS NULL;
