path: root/makima/migrations/20260503000000_add_document_id_to_tasks.sql

-- 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
   );