path: root/makima/migrations/20250104000000_create_mesh_chat_history.sql

-- Create mesh_chat_conversations table for storing global conversation threads
CREATE TABLE IF NOT EXISTS mesh_chat_conversations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_id UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000002',
    name VARCHAR(255),
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_mesh_chat_conversations_owner ON mesh_chat_conversations(owner_id);
CREATE INDEX idx_mesh_chat_conversations_active ON mesh_chat_conversations(is_active);

CREATE TRIGGER update_mesh_chat_conversations_updated_at
    BEFORE UPDATE ON mesh_chat_conversations
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Create mesh_chat_messages table for individual messages
CREATE TABLE IF NOT EXISTS mesh_chat_messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    conversation_id UUID NOT NULL REFERENCES mesh_chat_conversations(id) ON DELETE CASCADE,
    role VARCHAR(16) NOT NULL CHECK (role IN ('user', 'assistant', 'error')),
    content TEXT NOT NULL,
    context_type VARCHAR(32) NOT NULL DEFAULT 'mesh',  -- mesh/task/subtask
    context_task_id UUID REFERENCES tasks(id) ON DELETE SET NULL,
    tool_calls JSONB,
    pending_questions JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_mesh_chat_messages_conversation ON mesh_chat_messages(conversation_id);
CREATE INDEX idx_mesh_chat_messages_created ON mesh_chat_messages(created_at);
CREATE INDEX idx_mesh_chat_messages_context_task ON mesh_chat_messages(context_task_id);