1 files changed, 39 insertions, 0 deletions
diff --git a/makima/migrations/20260501100000_tmp_directive_and_clear_orphans.sql b/makima/migrations/20260501100000_tmp_directive_and_clear_orphans.sql
new file mode 100644
index 0000000..a587288
--- /dev/null
+++ b/makima/migrations/20260501100000_tmp_directive_and_clear_orphans.sql
@@ -0,0 +1,39 @@
+-- Tmp directive system: every task must belong to a directive going forward.
+--
+-- Background: the unified-surface UI previously surfaced "orphan" tasks
+-- (tasks with directive_id NULL) under a synthetic /tmp/ folder. That
+-- accumulated stale junk over time and made the UI noisy. The new model:
+--
+--   * Add `is_tmp` to directives — at most one per owner, marks the
+--     special "scratchpad" directive that holds otherwise-orphan tasks.
+--   * Delete every existing orphan task. The user explicitly asked for
+--     a clean slate: "ALSO there are TOO MANY old tasks in tmp, we need
+--     to remove all of them as well."
+--   * Going forward, ephemeral / standalone task creation paths attach
+--     to the caller's tmp directive (auto-created on first use by the
+--     repository helper, not by this migration — owners may not exist
+--     yet at migration time, but every owner gets one as soon as a
+--     standalone task is requested).
+--   * A 30-day expiry sweep in the directive reconciler deletes tasks
+--     in tmp directives once they age out.
+
+-- 1. New flag column on directives. Default false; only set true on the
+--    auto-created scratchpad directive.
+ALTER TABLE directives
+    ADD COLUMN is_tmp BOOLEAN NOT NULL DEFAULT false;
+
+-- Partial unique index — at most ONE tmp directive per owner.
+CREATE UNIQUE INDEX idx_directives_owner_tmp_unique
+    ON directives(owner_id)
+    WHERE is_tmp;
+
+-- 2. Clear out every existing orphan task. Per the user's spec these are
+--    discardable scratch work; pre-existing valuable tasks are already
+--    attached to a directive and will not be touched.
+--
+--    Cascades: task_events delete via FK; daemon links go to NULL; nothing
+--    in the contracts/directive_steps tables references orphan tasks (a
+--    contract-backed step always has a directive_id by construction).
+DELETE FROM tasks
+ WHERE directive_id IS NULL
+   AND parent_task_id IS NULL;