path: root/makima/migrations/20250102000000_create_mesh_tables.sql

-- Create tasks table for orchestrating Claude Code instances
CREATE TABLE IF NOT EXISTS tasks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_id UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000002',
    parent_task_id UUID REFERENCES tasks(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    status VARCHAR(32) NOT NULL DEFAULT 'pending',  -- pending/running/paused/blocked/done/failed/merged
    priority INTEGER NOT NULL DEFAULT 0,
    plan TEXT NOT NULL,

    -- Daemon/container info
    daemon_id UUID,
    container_id VARCHAR(255),           -- Docker/Podman container ID
    overlay_path VARCHAR(512),           -- Path to overlay upper layer on daemon

    -- Repository info
    repository_url VARCHAR(512),
    base_branch VARCHAR(255),            -- Branch overlay is based on
    target_branch VARCHAR(255),          -- Branch to merge into when complete

    -- Merge settings
    merge_mode VARCHAR(32) DEFAULT 'pr', -- 'pr' (create PR), 'auto' (auto-merge), 'manual'
    pr_url VARCHAR(512),                 -- URL if PR created

    -- Progress tracking
    progress_summary TEXT,
    last_output TEXT,
    error_message TEXT,

    -- Timestamps
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    version INTEGER NOT NULL DEFAULT 1,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Create indexes for tasks
CREATE INDEX idx_tasks_owner_id ON tasks(owner_id);
CREATE INDEX idx_tasks_parent_id ON tasks(parent_task_id);
CREATE INDEX idx_tasks_status ON tasks(status);
CREATE INDEX idx_tasks_daemon_id ON tasks(daemon_id);
CREATE INDEX idx_tasks_created_at ON tasks(created_at DESC);

-- Create trigger to auto-update updated_at (reuse existing function)
CREATE TRIGGER update_tasks_updated_at
    BEFORE UPDATE ON tasks
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Create task events log for audit and state changes
CREATE TABLE IF NOT EXISTS task_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    event_type VARCHAR(64) NOT NULL,  -- 'status_change', 'output', 'progress', 'sibling_broadcast', etc.
    previous_status VARCHAR(32),
    new_status VARCHAR(32),
    event_data JSONB,  -- Flexible event payload
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_task_events_task_id ON task_events(task_id);
CREATE INDEX idx_task_events_created_at ON task_events(created_at DESC);

-- Create connected daemons registry
CREATE TABLE IF NOT EXISTS daemons (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_id UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000002',
    connection_id VARCHAR(255) NOT NULL UNIQUE,  -- WebSocket session ID
    hostname VARCHAR(255),
    machine_id VARCHAR(255),  -- For identifying the machine
    max_concurrent_tasks INTEGER NOT NULL DEFAULT 1,
    current_task_count INTEGER NOT NULL DEFAULT 0,
    status VARCHAR(32) NOT NULL DEFAULT 'connected',  -- connected, disconnected, unhealthy
    last_heartbeat_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    connected_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    disconnected_at TIMESTAMPTZ
);

CREATE INDEX idx_daemons_owner_id ON daemons(owner_id);
CREATE INDEX idx_daemons_status ON daemons(status);
CREATE INDEX idx_daemons_connection_id ON daemons(connection_id);