1 files changed, 56 insertions, 0 deletions
diff --git a/makima/migrations/20260201000001_enhanced_supervisor_state.sql b/makima/migrations/20260201000001_enhanced_supervisor_state.sql
new file mode 100644
index 0000000..5411b73
--- /dev/null
+++ b/makima/migrations/20260201000001_enhanced_supervisor_state.sql
@@ -0,0 +1,56 @@
+-- Enhanced supervisor state persistence for restoration after crashes.
+-- Adds additional fields to supervisor_states to track detailed state for recovery.
+
+-- Add state tracking field (matches SupervisorStateEnum: initializing, idle, working,
+-- waiting_for_user, waiting_for_tasks, blocked, completed, failed, interrupted)
+ALTER TABLE supervisor_states
+    ADD COLUMN IF NOT EXISTS state VARCHAR(50) NOT NULL DEFAULT 'initializing';
+
+-- Add current activity description for monitoring
+ALTER TABLE supervisor_states
+    ADD COLUMN IF NOT EXISTS current_activity TEXT;
+
+-- Add progress percentage (0-100)
+ALTER TABLE supervisor_states
+    ADD COLUMN IF NOT EXISTS progress INTEGER DEFAULT 0
+    CHECK (progress >= 0 AND progress <= 100);
+
+-- Add error message for failed states
+ALTER TABLE supervisor_states
+    ADD COLUMN IF NOT EXISTS error_message TEXT;
+
+-- Add spawned task IDs (tasks this supervisor has created)
+ALTER TABLE supervisor_states
+    ADD COLUMN IF NOT EXISTS spawned_task_ids UUID[] DEFAULT ARRAY[]::UUID[];
+
+-- Add pending questions (questions waiting for user response)
+ALTER TABLE supervisor_states
+    ADD COLUMN IF NOT EXISTS pending_questions JSONB DEFAULT '[]';
+
+-- Add restoration metadata
+ALTER TABLE supervisor_states
+    ADD COLUMN IF NOT EXISTS restoration_count INTEGER DEFAULT 0;
+
+ALTER TABLE supervisor_states
+    ADD COLUMN IF NOT EXISTS last_restored_at TIMESTAMPTZ;
+
+ALTER TABLE supervisor_states
+    ADD COLUMN IF NOT EXISTS restoration_source VARCHAR(50);
+
+-- Index for finding supervisors by state (useful for finding blocked/failed supervisors)
+CREATE INDEX IF NOT EXISTS idx_supervisor_states_state ON supervisor_states(state);
+
+-- Index for finding supervisors with pending questions
+CREATE INDEX IF NOT EXISTS idx_supervisor_states_pending_questions
+    ON supervisor_states USING gin(pending_questions)
+    WHERE pending_questions != '[]'::jsonb;
+
+COMMENT ON COLUMN supervisor_states.state IS 'Current supervisor state: initializing, idle, working, waiting_for_user, waiting_for_tasks, blocked, completed, failed, interrupted';
+COMMENT ON COLUMN supervisor_states.current_activity IS 'Human-readable description of current activity';
+COMMENT ON COLUMN supervisor_states.progress IS 'Progress percentage (0-100)';
+COMMENT ON COLUMN supervisor_states.error_message IS 'Error message when state is failed or blocked';
+COMMENT ON COLUMN supervisor_states.spawned_task_ids IS 'Array of task UUIDs spawned by this supervisor';
+COMMENT ON COLUMN supervisor_states.pending_questions IS 'Array of questions awaiting user response: [{id, question, choices, context, asked_at}]';
+COMMENT ON COLUMN supervisor_states.restoration_count IS 'Number of times this supervisor has been restored after interruption';
+COMMENT ON COLUMN supervisor_states.last_restored_at IS 'Timestamp of last restoration';
+COMMENT ON COLUMN supervisor_states.restoration_source IS 'Source of last restoration: daemon_restart, task_reassignment, manual';