makima/migrations/20260430000000_create_directive_revisions.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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';