stonks-oracle/infra/migrations/039_signal_engine_outputs.sql

-- Migration 039: Signal Engine Outputs
-- Creates the signal_engine_outputs table for persisting dual-pipeline evaluations.

CREATE TABLE IF NOT EXISTS signal_engine_outputs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ticker TEXT NOT NULL,
    evaluated_at TIMESTAMPTZ NOT NULL,
    price NUMERIC NOT NULL,

    -- Heuristic pipeline
    heuristic_verdict TEXT NOT NULL,
    heuristic_confidence NUMERIC NOT NULL,
    heuristic_s_total NUMERIC NOT NULL,

    -- Probabilistic pipeline
    probabilistic_verdict TEXT NOT NULL,
    probabilistic_p_up NUMERIC NOT NULL,
    probabilistic_entropy NUMERIC NOT NULL,
    probabilistic_ev_r NUMERIC NOT NULL,

    -- Delta analysis
    delta_agreement BOOLEAN NOT NULL,
    delta_confidence_delta NUMERIC NOT NULL,
    delta_reasons JSONB NOT NULL DEFAULT '[]'::jsonb,

    -- Trade plan (null when no BUY verdict)
    trade_plan JSONB,

    -- Full output for audit
    full_output JSONB NOT NULL,

    -- Exit signals
    exit_signals JSONB NOT NULL DEFAULT '[]'::jsonb,

    -- Metadata
    pipeline_mode TEXT NOT NULL DEFAULT 'dual_pipeline',
    shadow_mode BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Index for per-ticker time-range queries
CREATE INDEX IF NOT EXISTS idx_signal_engine_outputs_ticker_time
    ON signal_engine_outputs (ticker, evaluated_at);

-- Index for global time-range queries
CREATE INDEX IF NOT EXISTS idx_signal_engine_outputs_evaluated
    ON signal_engine_outputs (evaluated_at);

-- Index for filtering by verdict
CREATE INDEX IF NOT EXISTS idx_signal_engine_outputs_verdicts
    ON signal_engine_outputs (heuristic_verdict, probabilistic_verdict);