1 files changed, 59 insertions, 0 deletions
diff --git a/makima/migrations/20260503000000_add_document_id_to_tasks.sql b/makima/migrations/20260503000000_add_document_id_to_tasks.sql
new file mode 100644
index 0000000..71d122d
--- /dev/null
+++ b/makima/migrations/20260503000000_add_document_id_to_tasks.sql
@@ -0,0 +1,59 @@
+-- Attach tasks and directive_steps to a specific directive_document.
+--
+-- Until now, directive-driven tasks and steps were only linked to the
+-- directive itself (tasks.directive_id, directive_steps.directive_id). The
+-- document model that landed in 20260502000000_create_directive_documents.sql
+-- introduced N documents per directive, each with its own draft → active →
+-- shipped → archived lifecycle. To make the sidebar match the goal — each
+-- document carries its own tasks/ subfolder, and a shipped document's task
+-- history visually moves with it under shipped/ — we need a per-document
+-- foreign key on tasks and steps.
+--
+-- The column is nullable + ON DELETE SET NULL so:
+--   * Old, pre-document tasks can keep working with directive_document_id = NULL.
+--   * Deleting a document detaches its tasks (it does not cascade-delete them),
+--     because task output is valuable history we don't want to lose.
+--
+-- Backfill rule: every existing directive currently has exactly one document
+-- (created by the previous migration's INSERT). We attach all of that
+-- directive's existing tasks and steps to that one document. Directives with
+-- zero documents are a no-op — the subquery returns NULL, the column stays
+-- NULL, and nothing breaks.
+
+ALTER TABLE tasks
+    ADD COLUMN directive_document_id UUID
+    REFERENCES directive_documents(id) ON DELETE SET NULL;
+
+CREATE INDEX idx_tasks_directive_document_id
+    ON tasks(directive_document_id);
+
+ALTER TABLE directive_steps
+    ADD COLUMN directive_document_id UUID
+    REFERENCES directive_documents(id) ON DELETE SET NULL;
+
+CREATE INDEX idx_directive_steps_directive_document_id
+    ON directive_steps(directive_document_id);
+
+-- Backfill: each task whose directive_id is set inherits the directive's
+-- single existing document (the oldest one — the one created by the previous
+-- migration's backfill). Tasks not associated with any directive stay NULL.
+UPDATE tasks t
+   SET directive_document_id = (
+       SELECT id FROM directive_documents d
+        WHERE d.directive_id = t.directive_id
+        ORDER BY d.created_at ASC
+        LIMIT 1
+   )
+ WHERE t.directive_id IS NOT NULL;
+
+-- Same for directive_steps. directive_id is NOT NULL on this table, so we
+-- don't need an explicit guard — the LIMIT-1 subquery returns NULL for any
+-- directive that happens to have no documents (defensive against bad data),
+-- which is fine because the column is nullable.
+UPDATE directive_steps s
+   SET directive_document_id = (
+       SELECT id FROM directive_documents d
+        WHERE d.directive_id = s.directive_id
+        ORDER BY d.created_at ASC
+        LIMIT 1
+   );