Merge pull request #55 from soryu-co/makima/contract-management-phase3

feat: Implement Phase 3 - Supervisor Resilience and State Management

2 files changed, 92 insertions, 0 deletions

diff --git a/makima/migrations/20260201000000_supervisor_heartbeats.sql b/makima/migrations/20260201000000_supervisor_heartbeats.sql
new file mode 100644
index 0000000..8595f71
--- /dev/null
+++ b/makima/migrations/20260201000000_supervisor_heartbeats.sql
@@ -0,0 +1,36 @@
+-- Create supervisor_heartbeats table for tracking supervisor state over time.
+-- This enables detection of dead/stale supervisors and provides audit trail.
+
+CREATE TABLE IF NOT EXISTS supervisor_heartbeats (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    supervisor_task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
+    contract_id UUID NOT NULL REFERENCES contracts(id) ON DELETE CASCADE,
+    state VARCHAR(50) NOT NULL,
+    phase VARCHAR(50) NOT NULL,
+    current_activity TEXT,
+    progress INTEGER DEFAULT 0 CHECK (progress >= 0 AND progress <= 100),
+    pending_task_ids UUID[] DEFAULT ARRAY[]::UUID[],
+    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+-- Index for finding heartbeats by supervisor task
+CREATE INDEX idx_heartbeats_supervisor ON supervisor_heartbeats(supervisor_task_id);
+
+-- Index for finding heartbeats by timestamp (for cleanup and monitoring)
+CREATE INDEX idx_heartbeats_timestamp ON supervisor_heartbeats(timestamp);
+
+-- Index for finding heartbeats by contract
+CREATE INDEX idx_heartbeats_contract ON supervisor_heartbeats(contract_id);
+
+-- Composite index for finding latest heartbeat per supervisor
+CREATE INDEX idx_heartbeats_supervisor_timestamp ON supervisor_heartbeats(supervisor_task_id, timestamp DESC);
+
+COMMENT ON TABLE supervisor_heartbeats IS 'Historical record of supervisor heartbeats for monitoring and dead supervisor detection';
+COMMENT ON COLUMN supervisor_heartbeats.state IS 'Supervisor state: initializing, idle, working, waiting_for_user, waiting_for_tasks, blocked, completed, failed, interrupted';
+COMMENT ON COLUMN supervisor_heartbeats.phase IS 'Current contract phase when heartbeat was sent';
+COMMENT ON COLUMN supervisor_heartbeats.current_activity IS 'Human-readable description of what the supervisor is doing';
+COMMENT ON COLUMN supervisor_heartbeats.progress IS 'Progress percentage (0-100)';
+COMMENT ON COLUMN supervisor_heartbeats.pending_task_ids IS 'Array of task IDs the supervisor is waiting on';
+
+-- Note: Cleanup of old heartbeats (24 hour TTL) should be done by a scheduled job
+-- or application-level cleanup, not a CHECK constraint (which can't reference NOW())
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';