1  -- Migration 041: Create Agent System Tables
 2  -- Creates the database schema for the multi-agent collaboration system
 3  -- Adds: agent_tasks, agent_messages, agent_logs, agent_state tables
 4  
 5  -- Agent task queue
 6  -- Central table for all agent work items with task routing and status tracking
 7  CREATE TABLE IF NOT EXISTS agent_tasks (
 8      id INTEGER PRIMARY KEY AUTOINCREMENT,
 9      task_type TEXT NOT NULL,  -- classify_error, fix_bug, verify_fix, scan_logs, etc.
10      assigned_to TEXT NOT NULL CHECK(assigned_to IN ('developer', 'qa', 'security', 'architect', 'triage', 'monitor')),
11      created_by TEXT,  -- Agent that created this task (or 'system' or 'user')
12      status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'running', 'completed', 'failed', 'blocked')),
13      priority INTEGER DEFAULT 5 CHECK(priority >= 1 AND priority <= 10),  -- 10 = highest
14      context_json TEXT,  -- JSON with task-specific data (file paths, error messages, etc.)
15      result_json TEXT,  -- JSON with task results (for handoffs to next agent)
16      parent_task_id INTEGER REFERENCES agent_tasks(id),  -- For workflow chains
17      error_message TEXT,  -- Error details if status='failed'
18      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
19      started_at DATETIME,
20      completed_at DATETIME,
21      retry_count INTEGER DEFAULT 0  -- Track retry attempts (max 3)
22  );
23  
24  -- Inter-agent messaging
25  -- Allows agents to ask questions, send notifications, and coordinate
26  CREATE TABLE IF NOT EXISTS agent_messages (
27      id INTEGER PRIMARY KEY AUTOINCREMENT,
28      task_id INTEGER REFERENCES agent_tasks(id),
29      from_agent TEXT NOT NULL,
30      to_agent TEXT NOT NULL,
31      message_type TEXT CHECK(message_type IN ('question', 'answer', 'handoff', 'notification')),
32      content TEXT NOT NULL,
33      metadata_json TEXT,  -- Additional context (severity, urgency, etc.)
34      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
35      read_at DATETIME
36  );
37  
38  -- Agent execution logs
39  -- Audit trail of all agent actions for debugging and metrics
40  CREATE TABLE IF NOT EXISTS agent_logs (
41      id INTEGER PRIMARY KEY AUTOINCREMENT,
42      task_id INTEGER REFERENCES agent_tasks(id),
43      agent_name TEXT NOT NULL,
44      log_level TEXT CHECK(log_level IN ('debug', 'info', 'warn', 'error')),
45      message TEXT NOT NULL,
46      data_json TEXT,  -- Structured log data (files read, commands run, etc.)
47      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
48  );
49  
50  -- Agent status tracking
51  -- Real-time agent health and performance metrics
52  CREATE TABLE IF NOT EXISTS agent_state (
53      agent_name TEXT PRIMARY KEY CHECK(agent_name IN ('developer', 'qa', 'security', 'architect', 'triage', 'monitor')),
54      last_active DATETIME DEFAULT CURRENT_TIMESTAMP,
55      current_task_id INTEGER REFERENCES agent_tasks(id),
56      status TEXT DEFAULT 'idle' CHECK(status IN ('idle', 'working', 'blocked')),
57      metrics_json TEXT  -- Success rate, avg task time, token usage, etc.
58  );
59  
60  -- Performance indexes
61  -- Optimized for agent polling and task routing
62  CREATE INDEX IF NOT EXISTS idx_agent_tasks_assigned_to ON agent_tasks(assigned_to, status);
63  CREATE INDEX IF NOT EXISTS idx_agent_tasks_parent ON agent_tasks(parent_task_id);
64  CREATE INDEX IF NOT EXISTS idx_agent_tasks_priority ON agent_tasks(priority DESC, created_at ASC);
65  CREATE INDEX IF NOT EXISTS idx_agent_messages_task ON agent_messages(task_id);
66  CREATE INDEX IF NOT EXISTS idx_agent_messages_to ON agent_messages(to_agent, read_at);
67  CREATE INDEX IF NOT EXISTS idx_agent_logs_task ON agent_logs(task_id);
68  CREATE INDEX IF NOT EXISTS idx_agent_logs_agent ON agent_logs(agent_name, created_at);
69  
70  -- Initialize agent state for all agents
71  INSERT OR IGNORE INTO agent_state (agent_name, status, metrics_json) VALUES
72  ('triage', 'idle', '{}'),
73  ('developer', 'idle', '{}'),
74  ('qa', 'idle', '{}'),
75  ('security', 'idle', '{}'),
76  ('architect', 'idle', '{}'),
77  ('monitor', 'idle', '{}');