1 files changed, 35 insertions, 0 deletions
diff --git a/makima/migrations/20260430000000_create_directive_revisions.sql b/makima/migrations/20260430000000_create_directive_revisions.sql
new file mode 100644
index 0000000..49d5179
--- /dev/null
+++ b/makima/migrations/20260430000000_create_directive_revisions.sql
@@ -0,0 +1,35 @@
+-- Per-PR snapshots of a directive (contract) goal, plus the PR's lifecycle
+-- state. A new revision is created when a directive transitions from "no PR"
+-- to "has PR" (i.e. the orchestrator's completion task raises one). The
+-- revision is the immutable record of what the contract said when it
+-- produced that PR.
+--
+-- pr_state mirrors the GitHub PR state: 'open' | 'merged' | 'closed'.
+-- Updated by the reconciler when the underlying PR transitions.
+CREATE TABLE directive_revisions (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    directive_id UUID NOT NULL REFERENCES directives(id) ON DELETE CASCADE,
+    -- Snapshot of `directives.goal` (inline markdown) at PR-creation time.
+    content TEXT NOT NULL,
+    -- The PR URL this revision is attached to. Mandatory because revisions
+    -- are only frozen at PR-raise time.
+    pr_url TEXT NOT NULL,
+    -- The PR branch name; useful for diffing without a round-trip to GitHub.
+    pr_branch TEXT,
+    -- 'open' | 'merged' | 'closed'.
+    pr_state TEXT NOT NULL DEFAULT 'open'
+        CHECK (pr_state IN ('open', 'merged', 'closed')),
+    -- Per-directive monotonically increasing version starting at 1.
+    version INTEGER NOT NULL,
+    frozen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    UNIQUE (directive_id, version)
+);
+
+CREATE INDEX idx_directive_revisions_directive_id
+    ON directive_revisions(directive_id, frozen_at DESC);
+
+-- Partial index over still-open PRs so the reconciler can quickly find what
+-- to poll.
+CREATE INDEX idx_directive_revisions_open_prs
+    ON directive_revisions(pr_state)
+    WHERE pr_state = 'open';