-- es_saga_instances — per-instance status projection (plan §P3.1).
--
-- Recovery (P3.3) and ops dashboards need "which instances are non-terminal"
-- without replaying every envelope stream. This denormalizes the envelope's
-- post-commit status into one row per instance, upserted inside the same
-- transaction as the envelope append + await mutations (same pattern as
-- es_saga_awaits) so it never diverges from the log across a crash.
--
-- shard_id is carried so the recovery scan partitions by owned shard exactly
-- like the timeout/signal scans (Phase 4) — a fleet recovers its own shards.
--
-- Lazy backfill: there is no SQL backfill (envelope replay isn't expressible
-- in SQL). Any instance touched by a commit upserts its row; pre-existing
-- terminal instances without a row are simply never scanned by recovery,
-- which is harmless (they're already done).
CREATE TABLE IF NOT EXISTS es_saga_instances (
    saga_name    TEXT        NOT NULL,
    instance_id  TEXT        NOT NULL,
    status       TEXT        NOT NULL,             -- SagaInstanceStatus::as_db_str
    shard_id     INTEGER     NOT NULL,
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (saga_name, instance_id)
);

-- Recovery scan: WHERE saga_name = $1 AND shard_id = ANY($owned)
--   AND status IN ('not-started','running') AND updated_at < now() - $after.
CREATE INDEX IF NOT EXISTS idx_saga_instances_recovery
    ON es_saga_instances (saga_name, shard_id, status, updated_at);
