Centralize durable schema migration ownership

crates/remora-schema/migrations/deletion/0001_deletion_lifecycle.sql

@@ -0,0 +1,96 @@
+CREATE TABLE deletion_operations (
+    operation_id TEXT PRIMARY KEY,
+    job_id TEXT NOT NULL UNIQUE REFERENCES jobs (job_id),
+    state TEXT NOT NULL CHECK (
+        state IN ('queued', 'running', 'retry_wait', 'blocked', 'completed')
+    ),
+    problem_class TEXT CHECK (
+        problem_class IS NULL
+        OR problem_class IN ('authorization', 'target_unavailable', 'configuration', 'internal')
+    ),
+    problem_message TEXT,
+    created_at TIMESTAMPTZ NOT NULL,
+    updated_at TIMESTAMPTZ NOT NULL,
+    completed_at TIMESTAMPTZ,
+    CONSTRAINT deletion_operations_problem_check CHECK (
+        (state = 'blocked') = (problem_class IS NOT NULL AND problem_message IS NOT NULL)
+    ),
+    CONSTRAINT deletion_operations_completion_check CHECK (
+        (state = 'completed') = (completed_at IS NOT NULL)
+    )
+);
+
+ALTER TABLE jobs
+    ADD COLUMN deletion_operation_id TEXT,
+    ADD COLUMN hard_delete_started_at TIMESTAMPTZ,
+    ADD CONSTRAINT jobs_deletion_operation_fk
+        FOREIGN KEY (deletion_operation_id)
+        REFERENCES deletion_operations (operation_id)
+        DEFERRABLE INITIALLY DEFERRED,
+    ADD CONSTRAINT jobs_deletion_lifecycle_check CHECK (
+        (
+            lifecycle IN ('visible', 'soft_deleted')
+            AND deletion_operation_id IS NULL
+            AND hard_delete_started_at IS NULL
+        )
+        OR (
+            lifecycle IN ('hard_deleting', 'hard_deleted')
+            AND deletion_operation_id IS NOT NULL
+            AND hard_delete_started_at IS NOT NULL
+        )
+    );
+
+CREATE TABLE deletion_items (
+    item_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+    operation_id TEXT NOT NULL REFERENCES deletion_operations (operation_id),
+    kind TEXT NOT NULL CHECK (kind IN ('write_intent', 'object', 'multipart', 'manifest')),
+    priority SMALLINT NOT NULL CHECK (priority IN (0, 10, 100)),
+    target_store TEXT NOT NULL,
+    bucket TEXT NOT NULL,
+    object_key TEXT NOT NULL,
+    version_id TEXT,
+    upload_id TEXT,
+    state TEXT NOT NULL DEFAULT 'pending' CHECK (state IN ('pending', 'claimed', 'complete')),
+    claim_token TEXT,
+    claimed_until TIMESTAMPTZ,
+    failure_count BIGINT NOT NULL DEFAULT 0 CHECK (failure_count >= 0),
+    completed_at TIMESTAMPTZ,
+    created_at TIMESTAMPTZ NOT NULL,
+    CONSTRAINT deletion_items_identity_shape_check CHECK (
+        (kind IN ('write_intent', 'object', 'manifest') AND upload_id IS NULL)
+        OR (kind = 'multipart' AND version_id IS NULL AND upload_id IS NOT NULL)
+    ),
+    CONSTRAINT deletion_items_claim_check CHECK (
+        (
+            state = 'pending'
+            AND claim_token IS NULL
+            AND claimed_until IS NULL
+            AND completed_at IS NULL
+        )
+        OR (
+            state = 'claimed'
+            AND claim_token IS NOT NULL
+            AND claimed_until IS NOT NULL
+            AND completed_at IS NULL
+        )
+        OR (
+            state = 'complete'
+            AND claim_token IS NULL
+            AND claimed_until IS NULL
+            AND completed_at IS NOT NULL
+        )
+    ),
+    UNIQUE NULLS NOT DISTINCT (
+        operation_id,
+        kind,
+        target_store,
+        bucket,
+        object_key,
+        version_id,
+        upload_id
+    )
+);
+
+CREATE INDEX deletion_items_claimable_idx
+    ON deletion_items (operation_id, priority, item_id)
+    WHERE state IN ('pending', 'claimed');