database/schema.sql

-- ============================================================
-- Stock Signal SQLite Schema
-- Consolidated schema
--
-- This replaces the older schema.sql + phase2/phase3/phase4
-- migration chain for NEW database initialization.
--
-- Canonical lifecycle:
--   prediction -> resolution -> metrics -> retrain flag
-- ============================================================


-- ============================================================
-- 1. PREDICTIONS
-- ============================================================

CREATE TABLE IF NOT EXISTS predictions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,

    -- Model / feature provenance
    model_version TEXT NOT NULL,
    feature_version TEXT NOT NULL,

    -- Prediction timing
    prediction_date DATE NOT NULL,
    prediction_timestamp TIMESTAMP NOT NULL DEFAULT (STRFTIME('%Y-%m-%dT%H:%M:%fZ', 'now')),

    -- Stock
    symbol TEXT NOT NULL,

    -- Model output
    predicted_probability REAL NOT NULL,

    "rank" INTEGER NOT NULL,

    -- Target contract
    target_threshold REAL NOT NULL DEFAULT 0.03,
    target_horizon_days INTEGER NOT NULL DEFAULT 5,

    -- Price at prediction time
    prediction_close REAL,

    -- Resolution / forward evaluation
    entry_date DATE,
    entry_open REAL,
    max_close_5d REAL,
    evaluation_end_date DATE,

    actual_return REAL,
    actual_label INTEGER,

    resolved_at TIMESTAMP,

    created_at TIMESTAMP NOT NULL DEFAULT (STRFTIME('%Y-%m-%dT%H:%M:%fZ', 'now')),

    -- One prediction per symbol/model/date.
    CONSTRAINT uq_predictions_date_symbol_model
        UNIQUE (prediction_date, symbol, model_version),

    CONSTRAINT predictions_probability_check
        CHECK (
            predicted_probability >= 0
            AND predicted_probability <= 1
        ),

    -- Binary classification target.
    CONSTRAINT predictions_actual_label_check
        CHECK (actual_label IS NULL OR actual_label IN (0, 1)),

    -- Target contract validation.
    CONSTRAINT predictions_target_threshold_check
        CHECK (target_threshold >= 0),

    CONSTRAINT predictions_target_horizon_check
        CHECK (target_horizon_days > 0),

    -- Rank must be positive.
    CONSTRAINT predictions_rank_check
        CHECK ("rank" > 0)
);


CREATE INDEX IF NOT EXISTS idx_predictions_date
    ON predictions (prediction_date);

CREATE INDEX IF NOT EXISTS idx_predictions_symbol
    ON predictions (symbol);

CREATE INDEX IF NOT EXISTS idx_predictions_unresolved
    ON predictions (prediction_date, symbol)
    WHERE actual_label IS NULL;

CREATE INDEX IF NOT EXISTS idx_predictions_resolution
    ON predictions (prediction_date, symbol, actual_label);

CREATE INDEX IF NOT EXISTS idx_predictions_model_date
    ON predictions (model_version, prediction_date);


-- ============================================================
-- 2. JOB RUNS
-- ============================================================

CREATE TABLE IF NOT EXISTS job_runs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,

    job_name TEXT NOT NULL,
    run_date DATE NOT NULL,

    started_at TIMESTAMP NOT NULL DEFAULT (STRFTIME('%Y-%m-%dT%H:%M:%fZ', 'now')),
    finished_at TIMESTAMP,

    status TEXT NOT NULL
    CHECK (
        status IN (
            'RUNNING',
            'SUCCESS',
            'FAILED',
            'SKIPPED'
        )
    ),

    rows_processed INTEGER NOT NULL DEFAULT 0,
    total_rows INTEGER NOT NULL DEFAULT 0,
    resolved_rows INTEGER NOT NULL DEFAULT 0,
    failed_rows INTEGER NOT NULL DEFAULT 0,
    last_updated_at TIMESTAMP,

    error_message TEXT
);


CREATE INDEX IF NOT EXISTS idx_job_runs_name_date
    ON job_runs (job_name, run_date);

-- Cross-process lease locks for singleton SQLite jobs. The token prevents an
-- expired owner from releasing a newer owner's lease.
CREATE TABLE IF NOT EXISTS job_locks (
    lock_name TEXT PRIMARY KEY,
    owner_token TEXT NOT NULL,
    locked_until TIMESTAMP NOT NULL
);


-- ============================================================
-- 3. PREDICTION METRICS
-- ============================================================

CREATE TABLE IF NOT EXISTS prediction_metrics (
    id INTEGER PRIMARY KEY AUTOINCREMENT,

    window_start DATE NOT NULL,
    window_end DATE NOT NULL,

    prediction_days INTEGER NOT NULL,
    resolved_predictions INTEGER NOT NULL,

    precision REAL,
    pr_auc REAL,
    hit_rate REAL,
    positive_rate REAL,
    average_return REAL,

    metrics_json TEXT NOT NULL DEFAULT '{}',

    calculated_at TIMESTAMP NOT NULL DEFAULT (STRFTIME('%Y-%m-%dT%H:%M:%fZ', 'now')),

    model_version TEXT NOT NULL,

    CONSTRAINT prediction_metrics_window_uq
        UNIQUE (model_version, window_start, window_end),

    CONSTRAINT prediction_metrics_window_ck
        CHECK (window_start <= window_end),

    CONSTRAINT prediction_metrics_prediction_days_ck
        CHECK (prediction_days > 0),

    CONSTRAINT prediction_metrics_resolved_ck
        CHECK (resolved_predictions >= 0)
);


CREATE INDEX IF NOT EXISTS idx_prediction_metrics_window_end
    ON prediction_metrics (window_end DESC);


-- ============================================================
-- 4. RETRAIN FLAGS
-- ============================================================

CREATE TABLE IF NOT EXISTS retrain_flags (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    model_version TEXT NOT NULL,
    triggered_at TIMESTAMP NOT NULL DEFAULT (STRFTIME('%Y-%m-%dT%H:%M:%fZ', 'now')),

    metric_window_start DATE NOT NULL,
    metric_window_end DATE NOT NULL,

    baseline_precision REAL NOT NULL,
    current_precision REAL NOT NULL,
    deterioration REAL NOT NULL,

    deterioration_threshold REAL NOT NULL DEFAULT 0.15,

    status TEXT NOT NULL DEFAULT 'PENDING'
        CHECK (
            status IN (
                'PENDING',
                'ACKNOWLEDGED',
                'RESOLVED',
                'IGNORED'
            )
        ),

    reason TEXT NOT NULL,

    resolved_at TIMESTAMP,
    notes TEXT,

    CONSTRAINT retrain_flags_window_uq
        UNIQUE (model_version, metric_window_start, metric_window_end)
);


CREATE INDEX IF NOT EXISTS idx_retrain_flags_status
    ON retrain_flags (status, triggered_at DESC);


-- ============================================================
-- END OF CONSOLIDATED SCHEMA
-- ============================================================

-- ============================================================
-- 5. MARKET DATA REGISTRY AND YFINANCE OHLCV
-- ============================================================

CREATE TABLE IF NOT EXISTS market_tickers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    symbol TEXT NOT NULL UNIQUE,
    yfinance_symbol TEXT NOT NULL UNIQUE,
    market TEXT NOT NULL CHECK (market IN ('NSE', 'US', 'CONTEXT')),
    name TEXT,
    industry TEXT,
    active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT (STRFTIME('%Y-%m-%dT%H:%M:%fZ', 'now')),
    updated_at TIMESTAMP NOT NULL DEFAULT (STRFTIME('%Y-%m-%dT%H:%M:%fZ', 'now'))
);

CREATE TABLE IF NOT EXISTS market_ohlcv (
    ticker_id INTEGER NOT NULL REFERENCES market_tickers(id) ON DELETE CASCADE,
    trading_date DATE NOT NULL,
    open REAL NOT NULL,
    high REAL NOT NULL,
    low REAL NOT NULL,
    close REAL NOT NULL,
    volume INTEGER,
    source TEXT NOT NULL DEFAULT 'yfinance',
    fetched_at TIMESTAMP NOT NULL DEFAULT (STRFTIME('%Y-%m-%dT%H:%M:%fZ', 'now')),
    PRIMARY KEY (ticker_id, trading_date)
);

CREATE INDEX IF NOT EXISTS idx_market_ohlcv_ticker_date
    ON market_ohlcv (ticker_id, trading_date DESC);

-- ============================================================
-- 6. MARKET DATA BY DATASET
--
-- The legacy market_ohlcv table remains only as a migration source for an
-- existing installation.  New reads and writes use these dataset tables.
-- ============================================================

CREATE TABLE IF NOT EXISTS ohlcv (
    ticker_id INTEGER NOT NULL REFERENCES market_tickers(id) ON DELETE CASCADE,
    trading_date DATE NOT NULL,
    open REAL NOT NULL, high REAL NOT NULL, low REAL NOT NULL, close REAL NOT NULL,
    volume INTEGER, source TEXT NOT NULL DEFAULT 'yfinance',
    fetched_at TIMESTAMP NOT NULL DEFAULT (STRFTIME('%Y-%m-%dT%H:%M:%fZ', 'now')),
    PRIMARY KEY (ticker_id, trading_date)
);

CREATE TABLE IF NOT EXISTS nifty_ohlcv (
    ticker_id INTEGER NOT NULL REFERENCES market_tickers(id) ON DELETE CASCADE,
    trading_date DATE NOT NULL,
    open REAL NOT NULL, high REAL NOT NULL, low REAL NOT NULL, close REAL NOT NULL,
    volume INTEGER, source TEXT NOT NULL DEFAULT 'yfinance',
    fetched_at TIMESTAMP NOT NULL DEFAULT (STRFTIME('%Y-%m-%dT%H:%M:%fZ', 'now')),
    PRIMARY KEY (ticker_id, trading_date)
);

CREATE TABLE IF NOT EXISTS india_vix_ohlcv (
    ticker_id INTEGER NOT NULL REFERENCES market_tickers(id) ON DELETE CASCADE,
    trading_date DATE NOT NULL,
    open REAL NOT NULL, high REAL NOT NULL, low REAL NOT NULL, close REAL NOT NULL,
    volume INTEGER, source TEXT NOT NULL DEFAULT 'yfinance',
    fetched_at TIMESTAMP NOT NULL DEFAULT (STRFTIME('%Y-%m-%dT%H:%M:%fZ', 'now')),
    PRIMARY KEY (ticker_id, trading_date)
);

CREATE TABLE IF NOT EXISTS us_stocks_ohlcv (
    ticker_id INTEGER NOT NULL REFERENCES market_tickers(id) ON DELETE CASCADE,
    trading_date DATE NOT NULL,
    open REAL NOT NULL, high REAL NOT NULL, low REAL NOT NULL, close REAL NOT NULL,
    volume INTEGER, source TEXT NOT NULL DEFAULT 'yfinance',
    fetched_at TIMESTAMP NOT NULL DEFAULT (STRFTIME('%Y-%m-%dT%H:%M:%fZ', 'now')),
    PRIMARY KEY (ticker_id, trading_date)
);

CREATE TABLE IF NOT EXISTS macro_ohlcv (
    ticker_id INTEGER NOT NULL REFERENCES market_tickers(id) ON DELETE CASCADE,
    macro_type TEXT NOT NULL CHECK (macro_type IN ('gold', 'brent_crude_oil', 'usd_inr')),
    trading_date DATE NOT NULL,
    open REAL NOT NULL, high REAL NOT NULL, low REAL NOT NULL, close REAL NOT NULL,
    volume INTEGER, source TEXT NOT NULL DEFAULT 'yfinance',
    fetched_at TIMESTAMP NOT NULL DEFAULT (STRFTIME('%Y-%m-%dT%H:%M:%fZ', 'now')),
    PRIMARY KEY (ticker_id, trading_date)
);

CREATE VIEW IF NOT EXISTS all_market_ohlcv AS
SELECT ticker_id, trading_date, open, high, low, close, volume, source, fetched_at FROM ohlcv
UNION ALL SELECT ticker_id, trading_date, open, high, low, close, volume, source, fetched_at FROM nifty_ohlcv
UNION ALL SELECT ticker_id, trading_date, open, high, low, close, volume, source, fetched_at FROM india_vix_ohlcv
UNION ALL SELECT ticker_id, trading_date, open, high, low, close, volume, source, fetched_at FROM us_stocks_ohlcv
UNION ALL SELECT ticker_id, trading_date, open, high, low, close, volume, source, fetched_at FROM macro_ohlcv;

-- Copy legacy data exactly once when upgrading an existing database.
INSERT OR IGNORE INTO ohlcv SELECT o.* FROM market_ohlcv o JOIN market_tickers t ON t.id = o.ticker_id WHERE t.market = 'NSE';
INSERT OR IGNORE INTO nifty_ohlcv SELECT o.* FROM market_ohlcv o JOIN market_tickers t ON t.id = o.ticker_id WHERE t.symbol = 'NIFTY';
INSERT OR IGNORE INTO india_vix_ohlcv SELECT o.* FROM market_ohlcv o JOIN market_tickers t ON t.id = o.ticker_id WHERE t.symbol = 'INDIA_VIX';
INSERT OR IGNORE INTO us_stocks_ohlcv SELECT o.* FROM market_ohlcv o JOIN market_tickers t ON t.id = o.ticker_id WHERE t.market = 'US';
INSERT OR IGNORE INTO macro_ohlcv (ticker_id, macro_type, trading_date, open, high, low, close, volume, source, fetched_at)
SELECT o.ticker_id, CASE t.symbol WHEN 'GOLD' THEN 'gold' WHEN 'BRENT' THEN 'brent_crude_oil' WHEN 'USDINR' THEN 'usd_inr' END,
       o.trading_date, o.open, o.high, o.low, o.close, o.volume, o.source, o.fetched_at
FROM market_ohlcv o JOIN market_tickers t ON t.id = o.ticker_id WHERE t.symbol IN ('GOLD', 'BRENT', 'USDINR');

-- Derived feature data lives in Parquet beside this database, under
-- data/db/feature_store (or the equivalent directory for DATABASE_URL).