supabase_forecasts_table.sql

-- Create Forecasts Table for AI Agent Persistence
CREATE TABLE IF NOT EXISTS public.forecasts (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    forecast_id UUID NOT NULL UNIQUE,
    supply_chain_id UUID NOT NULL REFERENCES public.supply_chains(supply_chain_id) ON DELETE CASCADE,
    node_id UUID REFERENCES public.nodes(node_id) ON DELETE CASCADE,
    user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    
    -- Core forecast data
    forecast_data JSONB NOT NULL,
    
    -- Separate components for better querying
    weather_data JSONB,
    news_data JSONB,
    market_data JSONB,
    
    -- Scenario data extracted from events for simulation usage
    scenario_json JSONB,
    
    -- Risk metrics
    risk_score FLOAT,
    confidence_score FLOAT,
    
    -- Time horizons
    forecast_period INTEGER,
    forecast_start_date TIMESTAMPTZ,
    forecast_end_date TIMESTAMPTZ,
    
    -- Metadata
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Indexes for efficient querying
CREATE INDEX IF NOT EXISTS idx_forecasts_supply_chain ON public.forecasts(supply_chain_id);
CREATE INDEX IF NOT EXISTS idx_forecasts_node ON public.forecasts(node_id);
CREATE INDEX IF NOT EXISTS idx_forecasts_created_at ON public.forecasts(created_at DESC);

-- RLS Policies
ALTER TABLE public.forecasts ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view their own forecasts"
    ON public.forecasts FOR SELECT
    USING (
        user_id = auth.uid() 
        OR 
        supply_chain_id IN (
            SELECT supply_chain_id FROM public.supply_chains WHERE user_id = auth.uid()
        )
    );

CREATE POLICY "Users can insert their own forecasts"
    ON public.forecasts FOR INSERT
    WITH CHECK (
        user_id = auth.uid() 
        OR 
        supply_chain_id IN (
            SELECT supply_chain_id FROM public.supply_chains WHERE user_id = auth.uid()
        )
    );