path: root/makima/migrations/20250114000003_supervisor_state.sql

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