1 files changed, 83 insertions, 0 deletions
diff --git a/makima/migrations/20250102000000_create_mesh_tables.sql b/makima/migrations/20250102000000_create_mesh_tables.sql
new file mode 100644
index 0000000..5b01d57
--- /dev/null
+++ b/makima/migrations/20250102000000_create_mesh_tables.sql
@@ -0,0 +1,83 @@
+-- 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);