-- 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            INTEGER 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     INTEGER NOT NULL,

    attempts      INTEGER NOT NULL DEFAULT 0,

    last_error    TEXT NOT NULL DEFAULT '',

    claimed_by    TEXT NOT NULL DEFAULT '',

    claimed_until INTEGER NOT NULL DEFAULT 0,

    dead          INTEGER NOT NULL DEFAULT 0,

    created_ms    INTEGER NOT NULL

);

CREATE INDEX work_queue_due ON work_queue (dead, run_after);