ALTER TABLE jobs
    ADD COLUMN current_fence BIGINT NOT NULL DEFAULT 0,
    ADD COLUMN authority_attempt_id TEXT,
    ADD CONSTRAINT jobs_current_fence_check CHECK (
        current_fence >= 0 AND current_fence <= 9007199254740991
    );

CREATE TABLE attempts (
    attempt_id TEXT PRIMARY KEY,
    job_id TEXT NOT NULL REFERENCES jobs (job_id),
    fence BIGINT NOT NULL CHECK (fence BETWEEN 1 AND 9007199254740991),
    worker_incarnation TEXT NOT NULL,
    state TEXT NOT NULL CHECK (state IN ('authorized', 'executing', 'reaped')),
    initial_progress BIGINT NOT NULL DEFAULT 0 CHECK (initial_progress >= 0),
    authorized_at TIMESTAMPTZ NOT NULL,
    executing_at TIMESTAMPTZ,
    reaped_at TIMESTAMPTZ,
    CONSTRAINT attempts_state_timestamps_check CHECK (
        (state = 'authorized' AND executing_at IS NULL AND reaped_at IS NULL)
        OR (state = 'executing' AND executing_at IS NOT NULL AND reaped_at IS NULL)
        OR (state = 'reaped' AND executing_at IS NULL AND reaped_at IS NOT NULL)
    ),
    CONSTRAINT attempts_job_fence_unique UNIQUE (job_id, fence),
    CONSTRAINT attempts_job_authority_unique UNIQUE (attempt_id, job_id, fence),
    CONSTRAINT attempts_authority_tuple_unique UNIQUE (
        attempt_id, job_id, fence, worker_incarnation
    )
);

ALTER TABLE jobs
    ADD CONSTRAINT jobs_authority_attempt_fk
    FOREIGN KEY (authority_attempt_id, job_id, current_fence)
    REFERENCES attempts (attempt_id, job_id, fence)
    DEFERRABLE INITIALLY DEFERRED;

CREATE INDEX attempts_job_authorized_idx
    ON attempts (job_id, authorized_at)
    WHERE state = 'authorized';