trading-platform-database-v2/migrations/2026-02-03_complete_agent_executions.sql

-- =====================================================
-- Migration: Complete agent_executions table
-- =====================================================
-- Description: Add missing columns for detailed execution tracking
-- Date: 2026-02-03
-- Task: ST-3.3, GAP-007
-- Related: OQI-004 Investment Accounts
-- =====================================================

-- =====================================================
-- ADD MISSING COLUMNS
-- =====================================================

-- Execution performance metrics
ALTER TABLE investment.agent_executions
ADD COLUMN IF NOT EXISTS execution_time_ms INTEGER,
ADD COLUMN IF NOT EXISTS slippage DECIMAL(20,8);

-- Risk metrics
ALTER TABLE investment.agent_executions
ADD COLUMN IF NOT EXISTS risk_score DECIMAL(5,4) CHECK (risk_score IS NULL OR risk_score BETWEEN 0 AND 1),
ADD COLUMN IF NOT EXISTS position_size_percent DECIMAL(5,2);

-- External references
ALTER TABLE investment.agent_executions
ADD COLUMN IF NOT EXISTS external_order_id VARCHAR(100),
ADD COLUMN IF NOT EXISTS model_id UUID;

-- Additional timestamps for lifecycle tracking
ALTER TABLE investment.agent_executions
ADD COLUMN IF NOT EXISTS closed_at TIMESTAMPTZ,
ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW();

-- Error tracking (complement existing failure_reason)
ALTER TABLE investment.agent_executions
ADD COLUMN IF NOT EXISTS error_code VARCHAR(50);

-- Notes field for manual annotations
ALTER TABLE investment.agent_executions
ADD COLUMN IF NOT EXISTS notes TEXT;

-- =====================================================
-- ADD PERFORMANCE INDEXES
-- =====================================================

-- Index for slippage analysis
CREATE INDEX IF NOT EXISTS idx_agent_exec_slippage
ON investment.agent_executions(slippage)
WHERE slippage IS NOT NULL;

-- Index for risk score analysis
CREATE INDEX IF NOT EXISTS idx_agent_exec_risk_score
ON investment.agent_executions(risk_score)
WHERE risk_score IS NOT NULL;

-- Index for external order lookups
CREATE INDEX IF NOT EXISTS idx_agent_exec_external_order
ON investment.agent_executions(external_order_id)
WHERE external_order_id IS NOT NULL;

-- Index for model performance tracking
CREATE INDEX IF NOT EXISTS idx_agent_exec_model
ON investment.agent_executions(model_id)
WHERE model_id IS NOT NULL;

-- Composite index for signal source analysis
CREATE INDEX IF NOT EXISTS idx_agent_exec_signal_confidence
ON investment.agent_executions(signal_source, confidence_score DESC NULLS LAST)
WHERE signal_source IS NOT NULL;

-- =====================================================
-- ADD TRIGGER FOR updated_at
-- =====================================================

-- Create trigger function if not exists (reuse common function)
DO $$
BEGIN
    -- Check if public.update_updated_at exists, if not use a local one
    IF NOT EXISTS (
        SELECT 1 FROM pg_proc p
        JOIN pg_namespace n ON p.pronamespace = n.oid
        WHERE n.nspname = 'public' AND p.proname = 'update_updated_at'
    ) THEN
        CREATE OR REPLACE FUNCTION public.update_updated_at()
        RETURNS TRIGGER AS $func$
        BEGIN
            NEW.updated_at = NOW();
            RETURN NEW;
        END;
        $func$ LANGUAGE plpgsql;
    END IF;
END $$;

-- Create or replace trigger
DROP TRIGGER IF EXISTS trg_agent_executions_updated_at ON investment.agent_executions;

CREATE TRIGGER trg_agent_executions_updated_at
    BEFORE UPDATE ON investment.agent_executions
    FOR EACH ROW
    EXECUTE FUNCTION public.update_updated_at();

-- =====================================================
-- ADD COMMENTS FOR NEW COLUMNS
-- =====================================================

COMMENT ON COLUMN investment.agent_executions.execution_time_ms IS 'Time taken to execute the trade in milliseconds';
COMMENT ON COLUMN investment.agent_executions.slippage IS 'Price slippage between requested and executed price';
COMMENT ON COLUMN investment.agent_executions.risk_score IS 'Risk assessment score at time of execution (0-1)';
COMMENT ON COLUMN investment.agent_executions.position_size_percent IS 'Position size as percentage of portfolio';
COMMENT ON COLUMN investment.agent_executions.external_order_id IS 'Order ID from external broker/exchange';
COMMENT ON COLUMN investment.agent_executions.model_id IS 'Reference to ML model that generated the signal';
COMMENT ON COLUMN investment.agent_executions.closed_at IS 'Timestamp when position was closed';
COMMENT ON COLUMN investment.agent_executions.updated_at IS 'Last modification timestamp';
COMMENT ON COLUMN investment.agent_executions.error_code IS 'Standardized error code for failures';
COMMENT ON COLUMN investment.agent_executions.notes IS 'Manual annotations or additional context';

-- =====================================================
-- VERIFICATION QUERY (for manual check)
-- =====================================================
-- SELECT column_name, data_type, is_nullable
-- FROM information_schema.columns
-- WHERE table_schema = 'investment'
--   AND table_name = 'agent_executions'
-- ORDER BY ordinal_position;