Add durable credential registry and secret lifecycle

crates/remora-credentials/migrations/0002_secret_write_intents.sql

@@ -0,0 +1,47 @@
+-- Durable intent for Vault secret writes. Secret material is never stored here.
+-- Intents make create/rotate recoverable across ambiguous PostgreSQL commits
+-- without best-effort Vault destruction that could erase a committed version.
+
+CREATE TABLE secret_write_intents (
+    intent_id TEXT PRIMARY KEY,
+    operation TEXT NOT NULL,
+    credential_id TEXT NOT NULL,
+    version_id TEXT NOT NULL,
+    owner_id TEXT,
+    label TEXT,
+    credential_kind TEXT,
+    secret_mount TEXT NOT NULL,
+    secret_path TEXT NOT NULL,
+    vault_kv_version BIGINT,
+    status TEXT NOT NULL,
+    created_at TIMESTAMPTZ NOT NULL,
+    updated_at TIMESTAMPTZ NOT NULL,
+    CONSTRAINT secret_write_intents_operation_check CHECK (
+        operation IN ('create', 'rotate')
+    ),
+    CONSTRAINT secret_write_intents_status_check CHECK (
+        status IN ('prepared', 'vault_written', 'durable_committed', 'abandoned')
+    ),
+    CONSTRAINT secret_write_intents_kv_check CHECK (
+        vault_kv_version IS NULL OR vault_kv_version >= 1
+    ),
+    CONSTRAINT secret_write_intents_create_fields_check CHECK (
+        (operation = 'create'
+            AND owner_id IS NOT NULL
+            AND label IS NOT NULL
+            AND credential_kind IS NOT NULL)
+        OR (operation = 'rotate'
+            AND owner_id IS NULL
+            AND label IS NULL
+            AND credential_kind IS NULL)
+    ),
+    CONSTRAINT secret_write_intents_written_has_version CHECK (
+        (status = 'prepared' AND vault_kv_version IS NULL)
+        OR (status IN ('vault_written', 'durable_committed')
+            AND vault_kv_version IS NOT NULL)
+        OR status = 'abandoned'
+    )
+);
+
+CREATE INDEX secret_write_intents_status_idx
+    ON secret_write_intents (status, created_at);