-- Per-instance processing leases (review §11 / effects-vs-commit).
--
-- Guarantees effects run at-least-once but NEVER concurrently: a worker
-- must hold the lease on (saga_name, instance_id) to process that
-- instance (run its effects). A second worker finding a live lease backs
-- off (Contended). If the holder dies, `leased_until` lapses and another
-- worker takes over and re-drives the instance forward — at-least-once.
--
-- The lease provides mutual exclusion only; stale-commit fencing is the
-- event store's existing OCC (a takeover worker advances the instance's
-- stream version, so a preempted holder's commit conflicts and is
-- discarded — "if it no longer holds the lease, its effect is invalid").
-- No separate fence token is needed here, unlike es_projection_leases,
-- because saga state IS a version-checked event stream.
--
-- Append-only migration (002) — does not touch 001's tables.
CREATE TABLE IF NOT EXISTS es_saga_leases (
    saga_name     TEXT        NOT NULL,
    instance_id   TEXT        NOT NULL,
    worker_id     TEXT        NOT NULL,
    leased_until  TIMESTAMPTZ NOT NULL,
    heartbeat_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (saga_name, instance_id)
);

-- Lets a worker loop find stale leases to reap/take over (future sweep).
CREATE INDEX IF NOT EXISTS idx_saga_leases_expiry
    ON es_saga_leases (saga_name, leased_until);
