analytics_views_postgresql.sql

-- TradeFin Quant Intelligence analytics contract for Metabase and Power BI.
-- Run against the PostgreSQL database used by DatabaseManager.

CREATE INDEX IF NOT EXISTS idx_market_data_symbol_timestamp
    ON market_data (symbol, timestamp);
CREATE INDEX IF NOT EXISTS idx_trades_timestamp_symbol
    ON trades (timestamp, symbol);
CREATE INDEX IF NOT EXISTS idx_signals_timestamp_strategy
    ON signals (timestamp, strategy_name);
CREATE INDEX IF NOT EXISTS idx_performance_date
    ON performance (date);

CREATE OR REPLACE VIEW analytics_trade_facts AS
SELECT
    id AS trade_id,
    order_id,
    symbol,
    LOWER(side) AS side,
    quantity,
    price,
    quantity * price AS notional_value,
    CASE WHEN LOWER(side) = 'sell' THEN -quantity * price ELSE quantity * price END AS signed_notional_value,
    status,
    timestamp AT TIME ZONE 'UTC' AS executed_at_utc,
    created_at AT TIME ZONE 'UTC' AS recorded_at_utc
FROM trades;

CREATE OR REPLACE VIEW analytics_daily_performance AS
SELECT
    date,
    total_pnl,
    daily_return,
    max_drawdown,
    sharpe_ratio,
    win_rate,
    total_trades,
    CURRENT_TIMESTAMP AT TIME ZONE 'UTC' AS view_refreshed_at_utc
FROM performance;

CREATE OR REPLACE VIEW analytics_market_latest AS
SELECT DISTINCT ON (symbol)
    symbol,
    timestamp AT TIME ZONE 'UTC' AS observed_at_utc,
    open,
    high,
    low,
    close,
    volume,
    created_at AT TIME ZONE 'UTC' AS ingested_at_utc
FROM market_data
ORDER BY symbol, timestamp DESC;

CREATE OR REPLACE VIEW analytics_signal_activity AS
SELECT
    strategy_name,
    symbol,
    signal_type,
    COUNT(*) AS signal_count,
    AVG(strength) AS average_strength,
    MIN(timestamp) AT TIME ZONE 'UTC' AS first_signal_at_utc,
    MAX(timestamp) AT TIME ZONE 'UTC' AS last_signal_at_utc
FROM signals
GROUP BY strategy_name, symbol, signal_type;

CREATE OR REPLACE VIEW analytics_data_quality AS
SELECT 'market_data' AS dataset, COUNT(*) AS row_count,
       MAX(created_at) AT TIME ZONE 'UTC' AS latest_recorded_at_utc
FROM market_data
UNION ALL
SELECT 'trades', COUNT(*), MAX(created_at) AT TIME ZONE 'UTC' FROM trades
UNION ALL
SELECT 'signals', COUNT(*), MAX(created_at) AT TIME ZONE 'UTC' FROM signals
UNION ALL
SELECT 'performance', COUNT(*), MAX(created_at) AT TIME ZONE 'UTC' FROM performance;

-- Grant these views to a dedicated read-only BI role in deployment scripts.
-- Example: GRANT SELECT ON ALL TABLES IN SCHEMA public TO tradefin_bi_reader;