1 files changed, 31 insertions, 0 deletions
diff --git a/makima/migrations/20250114000003_supervisor_state.sql b/makima/migrations/20250114000003_supervisor_state.sql
new file mode 100644
index 0000000..bcfe5e9
--- /dev/null
+++ b/makima/migrations/20250114000003_supervisor_state.sql
@@ -0,0 +1,31 @@
+-- Supervisor state persistence for resumability
+-- Stores conversation history and pending task state for supervisor tasks
+
+CREATE TABLE IF NOT EXISTS supervisor_states (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    contract_id UUID NOT NULL REFERENCES contracts(id) ON DELETE CASCADE,
+    task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
+    conversation_history JSONB NOT NULL DEFAULT '[]',  -- Full Claude conversation for resumption
+    last_checkpoint_id UUID REFERENCES task_checkpoints(id) ON DELETE SET NULL,
+    pending_task_ids UUID[] DEFAULT ARRAY[]::UUID[],  -- Tasks supervisor is waiting on
+    phase VARCHAR(50) NOT NULL DEFAULT 'research',     -- Current contract phase
+    last_activity TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+
+    UNIQUE(contract_id)  -- One supervisor state per contract
+);
+
+CREATE INDEX idx_supervisor_states_task_id ON supervisor_states(task_id);
+CREATE INDEX idx_supervisor_states_last_activity ON supervisor_states(last_activity);
+
+-- Trigger to update updated_at
+CREATE TRIGGER update_supervisor_states_updated_at
+    BEFORE UPDATE ON supervisor_states
+    FOR EACH ROW
+    EXECUTE FUNCTION update_updated_at_column();
+
+COMMENT ON TABLE supervisor_states IS 'Persisted state for contract supervisors, enabling resumption after interruption';
+COMMENT ON COLUMN supervisor_states.conversation_history IS 'Full Claude conversation history as JSON array';
+COMMENT ON COLUMN supervisor_states.pending_task_ids IS 'Array of task UUIDs the supervisor is waiting on';
+COMMENT ON COLUMN supervisor_states.phase IS 'Current contract phase when supervisor was last active';