database_init/04_add_forensic_columns.sql

-- Add columns for DeepForensic Engine (CTM) metrics
ALTER TABLE detection_metrics ADD COLUMN IF NOT EXISTS forensic_ticks INTEGER DEFAULT 0;
ALTER TABLE detection_metrics ADD COLUMN IF NOT EXISTS reasoning_path TEXT;
ALTER TABLE detection_metrics ADD COLUMN IF NOT EXISTS detection_tier TEXT DEFAULT 'Tier1_Fast';

-- Add comment explaining the new columns
COMMENT ON COLUMN detection_metrics.forensic_ticks IS 'Number of thinking steps taken by the CTM agent';
COMMENT ON COLUMN detection_metrics.reasoning_path IS 'JSON string containing the sequence of thoughts';
COMMENT ON COLUMN detection_metrics.detection_tier IS 'Tier of detection used: Tier1_Fast or Tier2_Forensic';