-- A durable work queue (oxygenhub#276): work that must not be lost on a

-- restart or a failure, claimed by any worker process (SKIP LOCKED on

-- PostgreSQL), retried with backoff, kept as dead after its last attempt.

CREATE TABLE work_queue (

    id            BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    kind          TEXT NOT NULL,

    payload       TEXT NOT NULL DEFAULT '{}',

    -- One live item per kind and key: a second enqueue of the same is dropped.

    dedupe        TEXT NOT NULL DEFAULT '',

    run_after     BIGINT NOT NULL,

    attempts      BIGINT NOT NULL DEFAULT 0,

    last_error    TEXT NOT NULL DEFAULT '',

    claimed_by    TEXT NOT NULL DEFAULT '',

    claimed_until BIGINT NOT NULL DEFAULT 0,

    dead          BOOLEAN NOT NULL DEFAULT FALSE,

    created_ms    BIGINT NOT NULL

);

CREATE INDEX work_queue_due ON work_queue (dead, run_after);