krishna-0722/equity-analytics

1-- ============================================================================2-- QUANT-EDGE INSTITUTIONAL PLATFORM SCHEMA (PRODUCTION CONFIGURATION)3-- Bolds Section 1: Transactions, Market Prices, FX Rates & Incremental Snapshots4-- ============================================================================5 6-- 1. UNIVERSAL SECURITY MASTER & SECTOR MAPPING7CREATE TABLE SYSTEM_ASSET_MASTER (8    asset_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,9    ticker_symbol VARCHAR2(15) NOT NULL UNIQUE,10    asset_name VARCHAR2(100) NOT NULL,11    asset_class VARCHAR2(30) CHECK (asset_class IN ('EQUITY', 'ETF', 'MUTUAL_FUND', 'BOND', 'CRYPTO')),12    sector_segment VARCHAR2(50) NOT NULL,13    macro_benchmark VARCHAR2(15) DEFAULT 'NIFTY50',14    CONSTRAINT uq_ticker UNIQUE (ticker_symbol)15);16 17-- 2. UNIVERSAL TRANSACTION LEDGER MULTI-BROKER/MULTI-ASSET INTEGRATION18CREATE TABLE TRANSACTION_LEDGER (19    tx_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,20    broker_source VARCHAR2(30) CHECK (broker_source IN ('FINECO', 'ZERODHA', 'GROWW', 'INTERACTIVE_BROKERS')),21    trade_date TIMESTAMP NOT NULL,22    ticker_symbol VARCHAR2(15) NOT NULL,23    tx_type VARCHAR2(10) CHECK (tx_type IN ('BUY', 'SELL', 'DIVIDEND_REINVEST')),24    quantity NUMBER(18,4) NOT NULL,25    execution_price NUMBER(14,4) NOT NULL,26    execution_currency VARCHAR2(5) DEFAULT 'INR',27    fx_rate_to_base NUMBER(14,6) DEFAULT 1.000000,28    transaction_fees NUMBER(10,2) DEFAULT 0.00,29    record_version NUMBER DEFAULT 1 NOT NULL, -- HISTORICAL VERSIONING LAYER30    last_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,31    CONSTRAINT fk_ledger_ticker FOREIGN KEY (ticker_symbol) REFERENCES SYSTEM_ASSET_MASTER(ticker_symbol)32);33 34-- 3. CENTRALIZED CASH FLOW RECONCILIATION GENERAL LEDGER35CREATE TABLE PORTFOLIO_CASH_FLOWS (36    flow_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,37    broker_source VARCHAR2(30) NOT NULL,38    flow_date TIMESTAMP NOT NULL,39    amount_base_curr NUMBER(18,2) NOT NULL, -- Positive for deposits, Negative for withdrawals40    flow_type VARCHAR2(20) CHECK (flow_type IN ('DEPOSIT', 'WITHDRAWAL', 'DIVIDEND_CASH')),41    cleared_timestamp TIMESTAMP42);43 44-- 4. CACHED TIMESERIES HISTORICAL MARKET DATA POOL45CREATE TABLE MARKET_PRICE_FEED (46    feed_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,47    ticker_symbol VARCHAR2(15) NOT NULL,48    price_date DATE NOT NULL,49    open_price NUMBER(14,4) NOT NULL,50    high_price NUMBER(14,4) NOT NULL,51    low_price NUMBER(14,4) NOT NULL,52    close_price NUMBER(14,4) NOT NULL,53    adjusted_close NUMBER(14,4) NOT NULL,54    trading_volume NUMBER(20) NOT NULL,55    fama_french_smb NUMBER(10,6), -- COMPUTE FACTORS ONBOARDING POOL56    fama_french_hml NUMBER(10,6),57    CONSTRAINT fk_feed_ticker FOREIGN KEY (ticker_symbol) REFERENCES SYSTEM_ASSET_MASTER(ticker_symbol),58    CONSTRAINT uq_ticker_date UNIQUE (ticker_symbol, price_date)59);60 61-- 5. CACHED TIME-SERIES FOREIGN EXCHANGE (FX) ENGINE DATA POOL62CREATE TABLE FX_CONVERSION_RATES (63    rate_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,64    currency_pair VARCHAR2(10) NOT NULL, -- e.g., 'USD/EUR', 'USD/INR'65    rate_date DATE NOT NULL,66    conversion_rate NUMBER(14,6) NOT NULL,67    CONSTRAINT uq_pair_date UNIQUE (currency_pair, rate_date)68);69 70-- 6. PORTFOLIO DAILY INCREMENTAL METRIC SNAPSHOTS71CREATE TABLE PORTFOLIO_DAILY_SNAPSHOTS (72    snapshot_date DATE PRIMARY KEY,73    total_market_value NUMBER(18,2) NOT NULL,74    total_asset_value NUMBER(18,2) NOT NULL,75    cash_balance_value NUMBER(18,2) NOT NULL,76    daily_twr_return NUMBER(10,6) NOT NULL,77    daily_benchmark_return NUMBER(10,6) NOT NULL,78    rolling_sharpe_180d NUMBER(8,4),79    rolling_vol_180d NUMBER(8,4),80    rolling_beta_180d NUMBER(8,4)81);82 83-- ============================================================================84-- PERFORMANCE CRITICAL COMPOSITE INDEXING LOOKUPS (SECTION 5 OPTIMIZATION)85-- ============================================================================86CREATE INDEX idx_ledger_perf ON TRANSACTION_LEDGER(trade_date, ticker_symbol);87CREATE INDEX idx_prices_perf ON MARKET_PRICE_FEED(price_date, ticker_symbol);88CREATE INDEX idx_cash_perf ON PORTFOLIO_CASH_FLOWS(flow_date);89