Add crash-safe attempt authority lifecycle

crates/remora-attempt/migrations/0001_attempt_authority.sql

@@ -0,0 +1,38 @@
+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';