1 files changed, 118 insertions, 0 deletions
diff --git a/makima/src/db/repository.rs b/makima/src/db/repository.rs
index cec9a82..1021c35 100644
--- a/makima/src/db/repository.rs
+++ b/makima/src/db/repository.rs
@@ -5691,6 +5691,124 @@ pub async fn update_directive_goal_keep_orchestrator(
     .await
 }
 
+// =============================================================================
+// Directive Revisions — per-PR snapshots of the contract content.
+// =============================================================================
+
+/// Snapshot the directive's current goal as a revision attached to the given
+/// PR URL. The version is auto-assigned as MAX(existing) + 1 per directive.
+/// Idempotent on (directive_id, pr_url): if a revision already exists for
+/// this directive+pr_url combo, returns the existing row instead of creating
+/// a duplicate.
+pub async fn create_directive_revision(
+    pool: &PgPool,
+    directive_id: Uuid,
+    content: &str,
+    pr_url: &str,
+    pr_branch: Option<&str>,
+) -> Result<crate::db::models::DirectiveRevision, sqlx::Error> {
+    // Idempotency: don't double-snapshot if the orchestrator's completion task
+    // re-runs and re-sets the same pr_url.
+    if let Some(existing) = sqlx::query_as::<_, crate::db::models::DirectiveRevision>(
+        r#"
+        SELECT * FROM directive_revisions
+        WHERE directive_id = $1 AND pr_url = $2
+        ORDER BY frozen_at DESC LIMIT 1
+        "#,
+    )
+    .bind(directive_id)
+    .bind(pr_url)
+    .fetch_optional(pool)
+    .await?
+    {
+        return Ok(existing);
+    }
+
+    sqlx::query_as::<_, crate::db::models::DirectiveRevision>(
+        r#"
+        INSERT INTO directive_revisions
+            (directive_id, content, pr_url, pr_branch, pr_state, version, frozen_at)
+        SELECT
+            $1,
+            $2,
+            $3,
+            $4,
+            'open',
+            COALESCE(MAX(version), 0) + 1,
+            NOW()
+        FROM directive_revisions
+        WHERE directive_id = $1
+        RETURNING *
+        "#,
+    )
+    .bind(directive_id)
+    .bind(content)
+    .bind(pr_url)
+    .bind(pr_branch)
+    .fetch_one(pool)
+    .await
+}
+
+/// List all revisions for a directive, newest first. Scoped by owner via the
+/// directive join so callers don't accidentally surface other users' history.
+pub async fn list_directive_revisions_for_owner(
+    pool: &PgPool,
+    owner_id: Uuid,
+    directive_id: Uuid,
+) -> Result<Vec<crate::db::models::DirectiveRevision>, sqlx::Error> {
+    sqlx::query_as::<_, crate::db::models::DirectiveRevision>(
+        r#"
+        SELECT r.*
+        FROM directive_revisions r
+        JOIN directives d ON d.id = r.directive_id
+        WHERE r.directive_id = $1 AND d.owner_id = $2
+        ORDER BY r.frozen_at DESC
+        "#,
+    )
+    .bind(directive_id)
+    .bind(owner_id)
+    .fetch_all(pool)
+    .await
+}
+
+/// Update the pr_state on a revision (called by the reconciler when it
+/// detects a PR transitioned to merged/closed). New state must be one of
+/// 'open' | 'merged' | 'closed' to satisfy the table's CHECK constraint.
+pub async fn update_directive_revision_pr_state(
+    pool: &PgPool,
+    revision_id: Uuid,
+    new_state: &str,
+) -> Result<(), sqlx::Error> {
+    sqlx::query(
+        r#"UPDATE directive_revisions SET pr_state = $2 WHERE id = $1"#,
+    )
+    .bind(revision_id)
+    .bind(new_state)
+    .execute(pool)
+    .await?;
+    Ok(())
+}
+
+/// Find the most recent merged revision for a directive — used when planning
+/// an amendment to know what the previous "frozen" content was so the diff
+/// can be passed to the orchestrator.
+pub async fn get_latest_merged_revision(
+    pool: &PgPool,
+    directive_id: Uuid,
+) -> Result<Option<crate::db::models::DirectiveRevision>, sqlx::Error> {
+    sqlx::query_as::<_, crate::db::models::DirectiveRevision>(
+        r#"
+        SELECT * FROM directive_revisions
+        WHERE directive_id = $1 AND pr_state = 'merged'
+        ORDER BY frozen_at DESC
+        LIMIT 1
+        "#,
+    )
+    .bind(directive_id)
+    .fetch_optional(pool)
+    .await
+}
+
 /// Save a goal to the directive goal history.
 pub async fn save_directive_goal_history(
     pool: &PgPool,