Vapora/migrations/005_kg_persistence.surql

-- Migration 005: Knowledge Graph Persistence
-- Stores execution history and analytics for learning and analysis
-- Enables Phase 5.1 (embedding-based KG) and Phase 5.5 (persistent storage)

-- KG Executions: Historical record of all agent task executions
DEFINE TABLE kg_executions SCHEMAFULL;

DEFINE FIELD execution_id ON TABLE kg_executions TYPE string ASSERT $value != NONE;
DEFINE FIELD task_description ON TABLE kg_executions TYPE string ASSERT $value != NONE;
DEFINE FIELD agent_id ON TABLE kg_executions TYPE string ASSERT $value != NONE;
DEFINE FIELD outcome ON TABLE kg_executions TYPE string ASSERT $value INSIDE ['success', 'failure'];
DEFINE FIELD duration_ms ON TABLE kg_executions TYPE int DEFAULT 0;
DEFINE FIELD input_tokens ON TABLE kg_executions TYPE int DEFAULT 0;
DEFINE FIELD output_tokens ON TABLE kg_executions TYPE int DEFAULT 0;
DEFINE FIELD task_type ON TABLE kg_executions TYPE string DEFAULT "general";
DEFINE FIELD error_message ON TABLE kg_executions TYPE option<string>;
DEFINE FIELD solution ON TABLE kg_executions TYPE option<string>;
DEFINE FIELD embedding ON TABLE kg_executions TYPE array<f32>;
DEFINE FIELD executed_at ON TABLE kg_executions TYPE datetime DEFAULT time::now();
DEFINE FIELD created_at ON TABLE kg_executions TYPE datetime DEFAULT time::now();

DEFINE INDEX idx_kg_executions_agent ON TABLE kg_executions COLUMNS agent_id;
DEFINE INDEX idx_kg_executions_task_type ON TABLE kg_executions COLUMNS task_type;
DEFINE INDEX idx_kg_executions_outcome ON TABLE kg_executions COLUMNS outcome;
DEFINE INDEX idx_kg_executions_executed_at ON TABLE kg_executions COLUMNS executed_at;
DEFINE INDEX idx_kg_executions_agent_type ON TABLE kg_executions COLUMNS agent_id, task_type;
DEFINE INDEX idx_kg_executions_task_outcome ON TABLE kg_executions COLUMNS task_description, outcome;

-- Analytics Events: Aggregated metrics for trending and analysis
DEFINE TABLE analytics_events SCHEMAFULL;

DEFINE FIELD event_id ON TABLE analytics_events TYPE string ASSERT $value != NONE;
DEFINE FIELD event_type ON TABLE analytics_events TYPE string ASSERT $value != NONE;
DEFINE FIELD agent_id ON TABLE analytics_events TYPE string ASSERT $value != NONE;
DEFINE FIELD metric_name ON TABLE analytics_events TYPE string;
DEFINE FIELD metric_value ON TABLE analytics_events TYPE float;
DEFINE FIELD task_type ON TABLE analytics_events TYPE option<string>;
DEFINE FIELD tags ON TABLE analytics_events TYPE array<string> DEFAULT [];
DEFINE FIELD recorded_at ON TABLE analytics_events TYPE datetime DEFAULT time::now();
DEFINE FIELD created_at ON TABLE analytics_events TYPE datetime DEFAULT time::now();

DEFINE INDEX idx_analytics_agent ON TABLE analytics_events COLUMNS agent_id;
DEFINE INDEX idx_analytics_event_type ON TABLE analytics_events COLUMNS event_type;
DEFINE INDEX idx_analytics_recorded_at ON TABLE analytics_events COLUMNS recorded_at;
DEFINE INDEX idx_analytics_agent_type ON TABLE analytics_events COLUMNS agent_id, event_type;

-- View: Success rate by agent (for analytics)
DEFINE VIEW agent_success_rate AS
    SELECT
        agent_id,
        math::round(count(SELECT success FROM (
            SELECT outcome = 'success' AS success FROM kg_executions WHERE outcome = 'success'
        )) * 100.0 / count(*), 2) AS success_rate_percent,
        count(*) AS total_executions,
        math::avg(duration_ms) AS avg_duration_ms
    FROM kg_executions
    GROUP BY agent_id;

-- View: Task type distribution
DEFINE VIEW task_type_distribution AS
    SELECT
        task_type,
        count(*) AS execution_count,
        math::round(count(SELECT success FROM (
            SELECT outcome = 'success' AS success FROM kg_executions WHERE outcome = 'success'
        )) * 100.0 / count(*), 2) AS success_rate_percent
    FROM kg_executions
    GROUP BY task_type;