agents/control-plane/migrations/010_workflow_execution_composite_indexes.sql

-- Migration: Add Composite Indexes for Workflow Execution Filtering Performance
-- Description: Add composite indexes for common filter combinations used in UI queries
-- Created: 2025-01-30

-- =============================================================================
-- COMPOSITE INDEXES FOR WORKFLOW EXECUTION FILTERING
-- =============================================================================

-- Composite index for session + status + time queries
-- Optimizes: SELECT * FROM workflow_executions WHERE session_id = ? AND status = ? ORDER BY started_at
-- This is the most common query pattern in the workflow summary filtering
CREATE INDEX IF NOT EXISTS idx_workflow_executions_session_status_time ON workflow_executions(session_id, status, started_at);

-- Composite index for actor + status + time queries
-- Optimizes: SELECT * FROM workflow_executions WHERE actor_id = ? AND status = ? ORDER BY started_at
-- Used when filtering by actor with status and time ordering
CREATE INDEX IF NOT EXISTS idx_workflow_executions_actor_status_time ON workflow_executions(actor_id, status, started_at);

-- Composite index for agent + status + time queries
-- Optimizes: SELECT * FROM workflow_executions WHERE agent_node_id = ? AND status = ? ORDER BY started_at
-- Used when filtering by agent with status and time ordering
CREATE INDEX IF NOT EXISTS idx_workflow_executions_agent_status_time ON workflow_executions(agent_node_id, status, started_at);

-- Composite index for status + time queries
-- Optimizes: SELECT * FROM workflow_executions WHERE status = ? ORDER BY started_at
-- Used for general status filtering with time-based ordering
CREATE INDEX IF NOT EXISTS idx_workflow_executions_status_time ON workflow_executions(status, started_at);

-- Composite index for session + time queries (without status filter)
-- Optimizes: SELECT * FROM workflow_executions WHERE session_id = ? ORDER BY started_at
-- Used when filtering by session without status constraint
CREATE INDEX IF NOT EXISTS idx_workflow_executions_session_time ON workflow_executions(session_id, started_at);

-- Composite index for actor + time queries (without status filter)
-- Optimizes: SELECT * FROM workflow_executions WHERE actor_id = ? ORDER BY started_at
-- Used when filtering by actor without status constraint
CREATE INDEX IF NOT EXISTS idx_workflow_executions_actor_time ON workflow_executions(actor_id, started_at);

-- =============================================================================
-- QUERY PATTERN OPTIMIZATION SUMMARY
-- =============================================================================
--
-- These composite indexes are specifically designed to optimize the query patterns
-- used in the QueryWorkflowExecutions method in storage/local.go:
--
-- 1. Session-based filtering with status and time ordering:
--    WHERE session_id = ? AND status = ? ORDER BY started_at
--    → Optimized by idx_workflow_executions_session_status_time
--
-- 2. Actor-based filtering with status and time ordering:
--    WHERE actor_id = ? AND status = ? ORDER BY started_at
--    → Optimized by idx_workflow_executions_actor_status_time
--
-- 3. Agent-based filtering with status and time ordering:
--    WHERE agent_node_id = ? AND status = ? ORDER BY started_at
--    → Optimized by idx_workflow_executions_agent_status_time
--
-- 4. Status filtering with time ordering:
--    WHERE status = ? ORDER BY started_at
--    → Optimized by idx_workflow_executions_status_time
--
-- 5. Session filtering with time ordering (no status):
--    WHERE session_id = ? ORDER BY started_at
--    → Optimized by idx_workflow_executions_session_time
--
-- 6. Actor filtering with time ordering (no status):
--    WHERE actor_id = ? ORDER BY started_at
--    → Optimized by idx_workflow_executions_actor_time
--
-- Expected Performance Impact:
-- - 60-80% reduction in query execution time for filtered queries
-- - Elimination of table scans for common filter combinations
-- - Improved ORDER BY performance through index-based sorting
-- - Better pagination performance for large result sets
--
-- These indexes directly address the performance bottlenecks identified in the
-- workflow summary and execution summary filtering operations.