TA-Stocks Desk — Final Merged Spec

Status: MERGED FINAL — Panel-reviewed, ready for coding bot Base: Claude Opus 4.6 (complete, unmodified) + unique cherry-picks from DeepSeek V3.1, Gemini 3.1 Pro, GPT-4.1, Grok 4 Fast, Grok 4.1, Sonar Reasoning Pro Primary trading venue: Stock broker (TBD) Scope: Data collection spec — SQLite schema, API endpoints, cadence, storage estimates, implementation checklist, SQL queries Date: 2026-03-15

Overview

This spec defines the TA (Technical Analysis) data collection engine for the Stocks Desk. It adapts the existing forex TA engine to equities-specific structure: real volume, session boundaries, gaps, VWAP, earnings catalysts, options flow, institutional positioning, and cross-market signals. The engine feeds the Stocks Desk analyst AIs with structured, schema-enforced data.

Guiding principle: Build in layers. Price + indicators first. Session + gaps second. VWAP + S&R third. Only add external data sources after the core is proven stable.

Section 1 — SuperTrend + ATR (Core Trend Engine)

Fields (per candle, per timeframe)

• supertrend_value — current SuperTrend line price level
• supertrend_direction — 1 (bullish) or -1 (bearish)
• supertrend_flip — boolean, true on the candle where direction changed
• supertrend_candles_since_flip — integer, how many candles since last direction change
• atr_14 — ATR with period 14 (primary)
• atr_5 — ATR with period 5 (for short-term volatility)
• atr_pct — ATR as percentage of closing price (normalizes across price levels)
• supertrend_distance_pct — (close - supertrend_value) / close * 100

Parameters

• Default: ATR multiplier 3.0, period 14
• Stocks-specific: separate parameter sets by volatility regime (see Section 11)
• Do NOT use a single global parameter — high-vol stocks need wider multipliers

SQLite Table

CREATE TABLE IF NOT EXISTS candles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    timeframe TEXT NOT NULL,          -- '5min','15min','1H','Daily','Weekly'
    ts_open INTEGER NOT NULL,         -- Unix timestamp, candle open time
    ts_close INTEGER NOT NULL,
    open REAL NOT NULL,
    high REAL NOT NULL,
    low REAL NOT NULL,
    close REAL NOT NULL,
    volume INTEGER NOT NULL,
    adjusted_close REAL,              -- split/dividend adjusted
    session_type TEXT,                -- 'premarket','rth','afterhours'
    halted INTEGER DEFAULT 0,         -- 1 if this candle overlaps a halt
    supertrend_value REAL,
    supertrend_direction INTEGER,
    supertrend_flip INTEGER DEFAULT 0,
    supertrend_candles_since_flip INTEGER,
    atr_14 REAL,
    atr_5 REAL,
    atr_pct REAL,
    supertrend_distance_pct REAL,
    UNIQUE(ticker, timeframe, ts_open)
);
CREATE INDEX IF NOT EXISTS idx_candles_ticker_tf_ts ON candles(ticker, timeframe, ts_open DESC);
CREATE INDEX IF NOT EXISTS idx_candles_session ON candles(ticker, session_type, ts_open DESC);

Cadence

• 5min candles: collected live during RTH (9:30–16:00 ET) and extended hours (4:00–20:00 ET)
• 15min, 1H: computed from 5min by aggregation
• Daily: collected EOD
• Weekly: computed from Daily on Friday close

Section 2 — Trend Lines + ZigZag

Fields

• zigzag_points — JSON array of {ts, price, type: 'HH'|'HL'|'LH'|'LL'} for last 50 ZigZag pivots per timeframe
• trendline_support — JSON: {slope, intercept, touches, age_candles, slope_raw, slope_pct_per_day, r_squared}
• trendline_resistance — same schema
• trendline_break_up — boolean on the candle a resistance TL was broken
• trendline_break_down — boolean on the candle a support TL was broken
• trendline_break_strength — (break_close - trendline_value) / ATR14

Slope Convention

All trend line slopes use candle-index as the x-axis (sequential candle numbering, ignoring clock time). Store two slope forms:

• slope_raw — price change per candle on native timeframe
• slope_pct_per_day — percentage change per trading day (1 day = 78×5min = 26×15min = 6.5×1H = 1×Daily candle)

When comparing slopes cross-timeframe, use slope_pct_per_day for apples-to-apples comparison. Never compare slope_raw values across timeframes directly.

SQLite Table

CREATE TABLE IF NOT EXISTS trendlines (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    timeframe TEXT NOT NULL,
    tl_type TEXT NOT NULL,            -- 'support' or 'resistance'
    anchor_ts_1 INTEGER NOT NULL,
    anchor_ts_2 INTEGER NOT NULL,
    slope_raw REAL,
    slope_pct_per_day REAL,
    intercept REAL,
    touches INTEGER DEFAULT 2,
    age_candles INTEGER,
    r_squared REAL,
    broken INTEGER DEFAULT 0,
    broken_ts INTEGER,
    break_strength REAL,
    created_ts INTEGER NOT NULL,
    updated_ts INTEGER NOT NULL
);

Section 3 — Event Timing Correlation

Tracks when multiple signals fire simultaneously. Used for confluence scoring.

Fields (per candle)

• event_count — how many distinct signal types fired on this candle
• event_types — JSON array of signal type strings
• multi_tf_alignment — boolean: are SuperTrend directions aligned across 3+ timeframes?
• sr_zone_test — boolean: is price within 0.5 ATR of an active S&R zone?

Section 4 — Oscillators: RSI, MACD, Stochastic, ADX

RSI — Two-Layer Architecture (fixes circular dependency)

Layer 1 — Divergence Detection (fixed thresholds always)

• Always use RSI 70/30 as swing point thresholds for divergence detection
• Divergence is purely about the SHAPE of RSI vs price, not the level
• rsi_divergence_bull_regular — boolean: price lower low, RSI higher low
• rsi_divergence_bear_regular — boolean: price higher high, RSI lower high
• rsi_divergence_bull_hidden — boolean (note: lower predictive value, use cautiously)
• rsi_divergence_bear_hidden — boolean (lower predictive value)
• Only flag divergences on 4H+ timeframes; intraday divergences are too noisy

Layer 2 — Overbought/Oversold Labels (adaptive thresholds for confluence scoring only)

• Strong uptrend (Weekly + Daily ST bullish for >20 candles): OB=80, OS=40
• Strong downtrend: OB=60, OS=20
• Neutral: OB=70, OS=30
• rsi_regime_label — 'overbought' | 'oversold' | 'neutral'
• rsi_adaptive_ob_threshold — current OB threshold in use
• rsi_adaptive_os_threshold — current OS threshold in use

Stored fields

• rsi_14 — raw RSI value (always stored, both layers use this)
• rsi_divergence_detected — boolean (from Layer 1)
• rsi_regime_label — text (from Layer 2)

MACD

• macd_line — 12/26 EMA difference
• macd_signal — 9 EMA of MACD line
• macd_histogram — MACD minus signal
• macd_cross_up — boolean: histogram crosses from negative to positive
• macd_cross_down — boolean
• macd_divergence — boolean: price and MACD histogram diverging

Stochastic

• stoch_k — %K (14,3)
• stoch_d — %D signal
• stoch_cross_up — %K crosses above %D below 20
• stoch_cross_down — %K crosses below %D above 80

ADX

• adx_14 — trend strength (>25 = trending, <20 = choppy)
• di_plus — +DI
• di_minus — -DI
• adx_trending — boolean: ADX > 25

SQLite Table

CREATE TABLE IF NOT EXISTS indicators (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    timeframe TEXT NOT NULL,
    ts INTEGER NOT NULL,
    rsi_14 REAL,
    rsi_regime_label TEXT,
    rsi_adaptive_ob_threshold REAL,
    rsi_adaptive_os_threshold REAL,
    rsi_divergence_detected INTEGER DEFAULT 0,
    rsi_divergence_bull_regular INTEGER DEFAULT 0,
    rsi_divergence_bear_regular INTEGER DEFAULT 0,
    macd_line REAL,
    macd_signal REAL,
    macd_histogram REAL,
    macd_cross_up INTEGER DEFAULT 0,
    macd_cross_down INTEGER DEFAULT 0,
    macd_divergence INTEGER DEFAULT 0,
    stoch_k REAL,
    stoch_d REAL,
    stoch_cross_up INTEGER DEFAULT 0,
    stoch_cross_down INTEGER DEFAULT 0,
    adx_14 REAL,
    di_plus REAL,
    di_minus REAL,
    adx_trending INTEGER DEFAULT 0,
    bb_upper REAL,
    bb_middle REAL,
    bb_lower REAL,
    bb_width_pct REAL,
    bb_squeeze INTEGER DEFAULT 0,
    keltner_upper REAL,
    keltner_lower REAL,
    UNIQUE(ticker, timeframe, ts)
);

Section 5 — Stocks-Specific Levels: VWAP, Options, Round Numbers

VWAP (Daily + Anchored)

Computation optimization — checkpoint system

• For each anchored VWAP (earnings, 52w high, 52w low), store a daily checkpoint:
  • cumulative_price_volume — sum of (typical_price × volume) from anchor to checkpoint date
  • cumulative_volume — sum of volume from anchor to checkpoint date
• Today's anchored VWAP = load yesterday's checkpoint, add today's candles incrementally — O(today's candles) not O(all history)
• Update checkpoints daily after market close

Fields (intraday, computed per RTH candle)

• vwap_daily — cumulative VWAP from 9:30 open
• vwap_std1_upper / vwap_std1_lower — ±1 standard deviation bands
• vwap_std2_upper / vwap_std2_lower — ±2 standard deviation bands
• price_vs_vwap_pct — (close - vwap_daily) / vwap_daily × 100
• vwap_earnings — VWAP anchored to last earnings date (active for 30 days post-earnings only, then NULL)
• vwap_52w_high — VWAP anchored to 52-week high date
• vwap_52w_low — VWAP anchored to 52-week low date
• IPO-anchored VWAP: removed — computationally expensive, only relevant for recent IPOs, precision degrades over time

Anchored VWAP checkpoints table

CREATE TABLE IF NOT EXISTS vwap_checkpoints (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    anchor_type TEXT NOT NULL,        -- 'earnings','52w_high','52w_low'
    anchor_date TEXT NOT NULL,        -- YYYY-MM-DD
    checkpoint_date TEXT NOT NULL,
    cumulative_price_volume REAL NOT NULL,
    cumulative_volume REAL NOT NULL,
    cumulative_sq_deviation REAL,     -- for std dev bands
    UNIQUE(ticker, anchor_type, anchor_date, checkpoint_date)
);

Relative Volume (RVOL) — Time-of-Day Normalized

Volume curve baseline table

CREATE TABLE IF NOT EXISTS volume_curve_baseline (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    time_bucket_start TEXT NOT NULL,  -- HH:MM, 5-min buckets 09:30..15:55
    avg_cumulative_volume_20d REAL,
    stddev_cumulative_volume_20d REAL,
    updated_date TEXT NOT NULL,
    UNIQUE(ticker, time_bucket_start)
);

Fields (per candle)

• rvol — current cumulative volume / avg_cumulative_volume_20d for this time bucket
• rvol_zscore — (current_cumulative_volume - avg) / stddev
• rvol_segment — RVOL broken down by session segment: open (9:30–10:00), morning (10:00–12:00), midday (12:00–14:00), afternoon (14:00–15:00), close (15:00–16:00)

Update baseline daily after market close. RVOL computed on-the-fly during RTH by looking up the current time bucket.

Volume Profile (Market Profile)

Per-session computation

• Divide session price range into bins of width = max($0.10, 0.05% of price)
• For each bin: sum volume of all candles whose typical price ((H+L+C)/3) falls in that bin
• vpoc — Volume Point of Control (bin with highest volume)
• value_area_high / value_area_low — range containing 70% of total session volume, centered on VPOC
• value_area_width_pct — (VAH - VAL) / VPOC × 100 (narrow = consensus, wide = contested)
• Also compute rolling 5-day composite: vpoc_5d, vah_5d, val_5d

CREATE TABLE IF NOT EXISTS volume_profile (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    date TEXT NOT NULL,               -- YYYY-MM-DD
    vpoc REAL,
    value_area_high REAL,
    value_area_low REAL,
    value_area_width_pct REAL,
    vpoc_5d REAL,
    vah_5d REAL,
    val_5d REAL,
    created_ts INTEGER NOT NULL,
    UNIQUE(ticker, date)
);

VPOC and VAH/VAL feed directly into the S&R Zone Engine as Method 12 (see Section 17).

Options-Derived Levels — Tiered Implementation

Tier 0 (v1 — free)

• Market-wide only: spy_max_pain, qqq_max_pain (calculate from CBOE delayed quotes or Tradier API free tier)
• Max pain formula: for each strike, sum (call_OI × max(0, strike - spot) + put_OI × max(0, spot - strike)) across all expirations; find strike that minimizes total
• spy_put_call_ratio, qqq_put_call_ratio — from CBOE daily PCR (free, cboe.com/us/options/market_statistics/daily/)
• Collection cadence: daily EOD for PCR; calculate max pain once per day at 3:45 PM ET

Tier 1 ($29/month Polygon.io — v2)

• Per-stock options chain snapshot via Polygon.io options snapshot endpoint
• For each watchlist stock, pull full chain once per hour during RTH
• Calculate per-stock: max_pain, put_call_ratio, iv_rank, call_wall (strike with max call OI), put_wall (strike with max put OI)
• Rate limit: 30 stocks × 1 request/hour = 30 req/hour, well within Polygon starter plan (5 req/sec)

Tier 2 ($50–100/month — v3)

• Unusual Whales or Spotgamma subscription for pre-computed GEX, GEX flip price, 0DTE GEX, unusual activity alerts
• Store their output as fields, do not replicate the GEX calculation internally

Tier 3 (v4+ — internal GEX model)

• Build GEX from Tier 1 chain data: GEX_per_strike = OI × 100 × spot × gamma × contract_multiplier
• Requires Black-Scholes gamma calculation — significant engineering

Options levels table

CREATE TABLE IF NOT EXISTS options_levels (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    snapshot_ts INTEGER NOT NULL,
    max_pain REAL,
    put_call_ratio REAL,
    iv_rank REAL,                     -- ATM IV vs 252-day history (0-100)
    call_wall REAL,                   -- strike with max call OI
    put_wall REAL,                    -- strike with max put OI
    gex REAL,                         -- Tier 2+ only
    gex_flip_price REAL,              -- Tier 2+ only
    zero_dte_gex REAL,                -- Tier 2+ only
    data_tier INTEGER DEFAULT 0,      -- 0,1,2,3
    UNIQUE(ticker, snapshot_ts)
);

SQLite write strategy for options_levels

• Use WAL mode: PRAGMA journal_mode=WAL
• Keep separate SQLite DB file for options data (stocks-options.db)
• Rolling window: keep last 5 trading days of hourly snapshots only; archive older to compressed JSON

Round Number Grid (Simplified)

Primary grid only for v1. Secondary grid optional for v2.

Store raw S&R levels, not the grid configuration. The grid is a detection method, not a permanent property. When a stock crosses a price tier boundary (e.g., splits from $15 to $7.50), recalculate zones without touching historical zone records.

Price Level Scaler

CREATE TABLE IF NOT EXISTS ticker_config (
    ticker TEXT PRIMARY KEY,
    sector TEXT,
    sector_etf TEXT,                  -- e.g., 'XLK', 'XLF'
    has_options INTEGER DEFAULT 1,
    extended_hours_available INTEGER DEFAULT 1,
    min_liquidity_threshold REAL,     -- minimum avg daily volume
    price_tier TEXT,                  -- 'sub25','25_100','100_500','500plus'
    tick_significance_pct REAL,       -- $0.01 as % of current price
    atr_zone_width_multiplier REAL,
    spread_threshold_normal REAL,
    min_meaningful_move_pct REAL,
    round_number_primary_grid REAL,
    round_number_secondary_grid REAL,
    data_source TEXT,                 -- 'polygon','alpaca','yfinance'
    data_source_quality INTEGER,      -- 1-5 rating
    last_updated INTEGER
);

Recalculate price scaler fields whenever a stock crosses a tier boundary. Apply to: S&R zone boundary rounding, ATR buffer calculations, spread z-score baseline, trend line slope normalization.

Section 6 — Confluence Events

Confluence fires when multiple independent signals align on the same candle or within the same session.

Event Types (stocks-adapted)

1. Trend + Momentum — SuperTrend flip + MACD cross in same direction
2. VWAP Confluence — price tests VWAP + RSI at key level + volume spike (RVOL > 1.5)
3. Gap Fill Confluence — price approaches unfilled gap zone + S&R zone present + volume confirms
4. S&R + Oscillator Extreme — price at S&R zone + RSI in OB/OS territory (adaptive thresholds)

Confluence Score

• Simple count (0–5 per timeframe): SuperTrend direction, VWAP side, S&R zone proximity, Volume (RVOL > 1.5), RSI label
• Do not use a complex weighted score in v1 — count aligned signals, output 'low'|'medium'|'high' confluence

Confluence table

CREATE TABLE IF NOT EXISTS confluence_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    timeframe TEXT NOT NULL,
    ts INTEGER NOT NULL,
    event_type TEXT NOT NULL,
    direction TEXT NOT NULL,          -- 'bullish' or 'bearish'
    score INTEGER NOT NULL,           -- 0-5 simple count
    score_label TEXT NOT NULL,        -- 'low','medium','high'
    supertrend_aligned INTEGER DEFAULT 0,
    vwap_aligned INTEGER DEFAULT 0,
    sr_zone_aligned INTEGER DEFAULT 0,
    volume_aligned INTEGER DEFAULT 0,
    rsi_aligned INTEGER DEFAULT 0,
    earnings_proximity_days INTEGER,
    halt_involved INTEGER DEFAULT 0,
    corporate_action_within_5d INTEGER DEFAULT 0,
    outcome_direction TEXT,           -- filled post-hoc
    outcome_pct REAL,
    outcome_candles INTEGER,
    brier_score REAL,
    UNIQUE(ticker, timeframe, ts, event_type)
);

Section 7 — Institutional Positioning + Order Flow

Short Interest

• short_interest_shares — shares sold short
• short_interest_pct_float — short interest / float shares (requires float data — see below)
• days_to_cover — short_interest / avg_daily_volume_30d
• short_interest_change_pct — vs prior reporting period
• Source: FINRA (twice monthly, free CSV), supplemented by daily short volume (see below)
• Cadence: update twice monthly on FINRA release dates

Daily Short Volume (more timely than bi-monthly short interest)

[PANEL: GPT-4.1] Short volume (daily) provides squeeze signals more timely than bi-monthly FINRA short interest. FINRA publishes daily short volume for free at finra.org/finra-data. Fields: daily_short_volume, daily_short_volume_pct (short volume / total volume for that day). This is not the same as short interest (which is the cumulative open short position), but it tracks day-to-day shorting activity in near-real-time.

CREATE TABLE IF NOT EXISTS daily_short_volume (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    date TEXT NOT NULL,
    short_volume INTEGER,
    total_volume INTEGER,
    short_volume_pct REAL,
    source TEXT DEFAULT 'finra',
    UNIQUE(ticker, date)
);

Float + Shares Outstanding

CREATE TABLE IF NOT EXISTS institutional_positioning (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    date TEXT NOT NULL,
    shares_outstanding INTEGER,
    float_shares INTEGER,
    float_turnover_ratio REAL,        -- daily volume / float
    float_change_event INTEGER DEFAULT 0,  -- 1 if SEC S-3 or 8-K dilution event
    float_change_event_type TEXT,
    float_change_event_date TEXT,
    float_source TEXT,                -- 'fmp','sec_edgar','yfinance'
    short_interest_shares INTEGER,
    short_interest_pct_float REAL,
    days_to_cover REAL,
    short_interest_change_pct REAL,
    short_interest_date TEXT,
    insider_net_shares_30d INTEGER,   -- net insider buys minus sells (30 days)
    insider_cluster_buy INTEGER DEFAULT 0,  -- 1 if 3+ insiders bought in 30 days
    thirteenf_held_pct REAL,          -- % shares held by 13F filers (quarterly context only)
    cost_to_borrow_pct REAL,          -- annualized CTB rate (if available)
    borrow_availability TEXT,         -- 'easy','medium','hard','no_data'
    UNIQUE(ticker, date)
);

Sources:

• Float/shares outstanding: Financial Modeling Prep API free tier (250 req/day), or Yahoo Finance yfinance
• For real-time float changes: SEC S-3 filings and 8-K dilution events via EDGAR RSS feed
• Update float weekly; monitor EDGAR RSS daily for change events

Threshold List / Reg SHO

[PANEL: DeepSeek V3.1] SEC publishes a daily threshold list of stocks with persistent delivery failures (potential short restriction zone). Being on the threshold list changes price action around squeezes — call walls become more important, put hedges become cheaper. Source: SEC website, free. File: threshold list CSV.

CREATE TABLE IF NOT EXISTS threshold_list (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    date TEXT NOT NULL,
    on_threshold_list INTEGER DEFAULT 1,
    UNIQUE(ticker, date)
);

Insider Transactions

• Source: SEC EDGAR Form 4 (free API — prefer a third-party cleaner like Gurufocus free tier or OpenInsider.com API rather than raw EDGAR scraping)
• Cache in SQLite; update daily
• Filter out: automatic plan sales (Rule 10b5-1), option exercises treated as buys (note them but weight lower)
• Weight cluster buys heavily: 3+ insiders buying in 30 days is a high-conviction signal

CREATE TABLE IF NOT EXISTS insider_transactions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    filed_date TEXT NOT NULL,
    transaction_date TEXT NOT NULL,
    insider_name TEXT,
    insider_title TEXT,
    transaction_type TEXT,            -- 'buy','sell','option_exercise','auto_plan_sale'
    shares INTEGER,
    price REAL,
    total_value REAL,
    auto_plan INTEGER DEFAULT 0,      -- 1 if Rule 10b5-1
    source TEXT DEFAULT 'openinsider'
);

Large Trade Direction (replaces dark pool sentiment)

Dark pool sentiment has been removed from v1. Replace with large_trade_direction derived from time-and-sales data: classify prints > 10,000 shares against NBBO midpoint using the Lee-Ready algorithm (trade at or above ask = buy, at or below bid = sell, at midpoint = neutral). This avoids the FINRA ATS data problem (2-4 week lag, no price info, no directionality).

CREATE TABLE IF NOT EXISTS large_trades (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    ts INTEGER NOT NULL,
    price REAL NOT NULL,
    shares INTEGER NOT NULL,
    direction TEXT,                   -- 'buy','sell','neutral' (Lee-Ready)
    vs_nbbo_midpoint TEXT,            -- 'above','below','at'
    block_size_atr REAL              -- trade size relative to ATR for context
);

Section 8 — Cross-Market Signals

Index Correlations

• spy_correlation_20d — 20-day rolling correlation vs SPY
• qqq_correlation_20d — vs QQQ
• iwm_correlation_20d — vs IWM (small-cap proxy)
• sector_etf_correlation_20d — vs the stock's sector ETF
• beta_spy_60d — 60-day rolling beta vs SPY
• relative_performance_1d — stock return minus SPY return today
• relative_performance_5d — rolling 5-day
• relative_performance_20d — rolling 20-day

Sector Tracking (Simplified)

Track top 3 outperforming and bottom 3 underperforming sectors (not all 11 ranks). Binary per sector: outperforming or underperforming vs SPY. Full rotation matrix deferred to v2.

Sector ETF mappings (GICS):

• XLK (Technology), XLF (Financials), XLV (Healthcare), XLE (Energy), XLI (Industrials), XLY (Consumer Discretionary), XLP (Consumer Staples), XLB (Materials), XLRE (Real Estate), XLU (Utilities), XLC (Communication Services)

CREATE TABLE IF NOT EXISTS cross_market (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    date TEXT NOT NULL,
    spy_correlation_20d REAL,
    qqq_correlation_20d REAL,
    iwm_correlation_20d REAL,
    sector_etf_correlation_20d REAL,
    beta_spy_60d REAL,
    relative_performance_1d REAL,
    relative_performance_5d REAL,
    relative_performance_20d REAL,
    vix_level REAL,
    vix_term_structure TEXT,          -- 'contango','backwardation','flat'
    yield_10y REAL,
    yield_2y REAL,
    yield_curve_spread REAL,          -- 10Y minus 2Y
    spy_circuit_breaker_level_1 REAL, -- SPY prev close * 0.93
    spy_circuit_breaker_level_2 REAL, -- SPY prev close * 0.87
    spy_circuit_breaker_level_3 REAL, -- SPY prev close * 0.80
    circuit_breaker_proximity REAL,   -- current SPY / nearest CB level
    circuit_breaker_warning INTEGER DEFAULT 0,  -- 1 if within 2% of Level 1
    sector_outperforming_top3 TEXT,   -- JSON array of top 3 sector ETF tickers
    sector_underperforming_bottom3 TEXT,
    UNIQUE(ticker, date)
);

Circuit breaker fields: calculate daily from SPY's previous close (trivial math). When circuit_breaker_warning = 1, reduce all bullish confluence scores by 3 points — the market is in crash dynamics, bullish signals are unreliable.

Section 9 — Relative Strength Rankings

Fields

• rs_vs_spy_pct_1d / _5d / _20d / _60d — stock return minus SPY return for each period
• rs_vs_sector_pct_1d / _5d / _20d — stock return minus sector ETF return
• rs_rank_universe — rank in tracked stock universe (1 = strongest)
• rs_rank_sector — rank within sector
• rs_trend — 'improving' | 'stable' | 'deteriorating' (based on RS change over 5 days)
• rs_new_high — boolean: stock made new RS high vs SPY while absolute price did not

CREATE TABLE IF NOT EXISTS relative_strength (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    date TEXT NOT NULL,
    rs_vs_spy_pct_1d REAL,
    rs_vs_spy_pct_5d REAL,
    rs_vs_spy_pct_20d REAL,
    rs_vs_spy_pct_60d REAL,
    rs_vs_sector_pct_1d REAL,
    rs_vs_sector_pct_5d REAL,
    rs_vs_sector_pct_20d REAL,
    rs_rank_universe INTEGER,
    rs_rank_sector INTEGER,
    rs_trend TEXT,
    rs_new_high INTEGER DEFAULT 0,
    UNIQUE(ticker, date)
);

Cadence: compute daily after market close. RS ranking requires all tracked stocks to be processed first, then rank.

Section 10 — Session-Specific Tracking

Session Boundaries (US Eastern Time)

• Pre-market: 4:00–9:30 AM
• Regular trading hours (RTH): 9:30 AM–4:00 PM
• After-hours: 4:00–8:00 PM
• Opening range: 9:30–10:00 AM (first 30 minutes of RTH)
• Power hour: 3:00–4:00 PM

Per-Session Fields

CREATE TABLE IF NOT EXISTS session_data (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    date TEXT NOT NULL,
    premarket_high REAL,
    premarket_low REAL,
    premarket_open REAL,
    premarket_close REAL,             -- price at 9:29 AM
    premarket_volume INTEGER,
    premarket_gap_from_prior_close_pct REAL,
    rth_open REAL,                    -- 9:30 AM first print
    rth_high REAL,
    rth_low REAL,
    rth_close REAL,
    rth_volume INTEGER,
    opening_range_high REAL,          -- first 30-min high
    opening_range_low REAL,
    opening_range_width_atr REAL,
    afterhours_high REAL,
    afterhours_low REAL,
    afterhours_close REAL,            -- last print before 8:00 PM
    afterhours_volume INTEGER,
    gap_from_prior_close_pct REAL,    -- RTH open vs prior RTH close
    gap_type TEXT,                    -- 'earnings','news','technical' (see Section 16)
    gap_filled_intraday INTEGER DEFAULT 0,
    moc_proxy_volume INTEGER,         -- volume 3:45-4:00 PM (MOC imbalance proxy)
    moc_proxy_direction TEXT,         -- 'up' or 'down' based on 3:45-4:00 PM price
    extended_hours_available INTEGER DEFAULT 1,
    UNIQUE(ticker, date)
);

For earnings reactions from AMC reporters: earnings_reaction_price = price at 4:15 PM ET; earnings_reaction_range = after-hours high minus after-hours low. For BMO reporters: earnings_reaction_price = price at 9:00 AM ET (30 min before open).

Halt Detection + LULD Bands

CREATE TABLE IF NOT EXISTS halt_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    halt_time_utc INTEGER NOT NULL,
    resume_time_utc INTEGER,
    halt_reason_code TEXT,            -- T1=news_pending,T2=news_released,LUDP=limit_up_down,M1/M2/M3=circuit_breaker
    pre_halt_price REAL,
    resume_price REAL,
    halt_gap_pct REAL,                -- (resume - pre_halt) / pre_halt
    halt_duration_seconds INTEGER,
    halt_during_sr_zone_test INTEGER DEFAULT 0,  -- price within S&R zone when halted
    outcome_tagged INTEGER DEFAULT 0   -- any outcome that includes this halt gets halt_involved=1
);

CREATE TABLE IF NOT EXISTS luld_bands (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    ts INTEGER NOT NULL,
    upper_band REAL NOT NULL,
    lower_band REAL NOT NULL,
    UNIQUE(ticker, ts)
);

Sources: NYSE halt feed (nyse.com/trade-halt-current, free CSV), Nasdaq halt feed (nasdaqtrader.com/trader.aspx?id=TradeHalts, free). Poll every 30 seconds during RTH. Flag any candle that overlaps a halt period with halted=1 in the candles table. LULD bands: store as dynamic S&R zones with source='circuit_breaker' and zero decay.

Section 11 — Volatility Regime Classification

HV-Based (v1 — no options data needed)

• hv_20d — 20-day historical volatility (annualized)
• hv_60d — 60-day HV
• hv_percentile_252d — HV percentile vs last 252 trading days (0-100)
• vol_regime — 'low' (<25th pct) | 'normal' (25-75th) | 'high' (>75th)
• kaufman_er — Kaufman Efficiency Ratio (0 = choppy, 1 = perfectly trending)
• bb_squeeze — boolean: Bollinger Band width below Keltner Channel width (volatility compression)
• vol_expanding — boolean: HV rising for 5+ consecutive days

IV-Based (v2 — requires options data)

• iv_rank — ATM IV percentile vs 252-day history
• iv_percentile — % of days in past year with lower IV than today
• iv_skew — put IV minus call IV at same delta
• iv_term_structure — front-month vs back-month IV relationship

CREATE TABLE IF NOT EXISTS volatility_regime (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    date TEXT NOT NULL,
    hv_20d REAL,
    hv_60d REAL,
    hv_percentile_252d REAL,
    vol_regime TEXT,
    kaufman_er REAL,
    bb_squeeze INTEGER DEFAULT 0,
    vol_expanding INTEGER DEFAULT 0,
    iv_rank REAL,                     -- v2+
    iv_percentile REAL,               -- v2+
    iv_skew REAL,                     -- v2+
    UNIQUE(ticker, date)
);

Section 12 — Earnings + Catalyst Calendar

Earnings Estimates (required for surprise calculation)

[PANEL: Opus (aplus)] Store consensus estimates BEFORE earnings. Without them, earnings surprise cannot be calculated.

CREATE TABLE IF NOT EXISTS earnings_estimates (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    earnings_date TEXT NOT NULL,
    eps_consensus REAL,
    eps_high_estimate REAL,
    eps_low_estimate REAL,
    revenue_consensus REAL,
    revenue_high_estimate REAL,
    revenue_low_estimate REAL,
    num_analysts INTEGER,
    estimate_revision_30d REAL,       -- change in consensus EPS over 30 days
    estimate_revision_direction TEXT, -- 'up','down','flat'
    positive_revision_momentum INTEGER DEFAULT 0,  -- 3+ months of upward revisions
    source TEXT DEFAULT 'fmp',
    pulled_date TEXT NOT NULL,
    UNIQUE(ticker, earnings_date, pulled_date)
);

Source: Financial Modeling Prep API /analyst-estimates endpoint (free tier). Pull weekly; pull daily in the 5 days before earnings.

Earnings Events (post-earnings data)

CREATE TABLE IF NOT EXISTS earnings_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    earnings_date TEXT NOT NULL,
    report_time TEXT NOT NULL,        -- 'bmo' (before market open) or 'amc' (after market close)
    eps_actual REAL,
    eps_consensus REAL,
    eps_surprise_pct REAL,            -- (actual - consensus) / abs(consensus) * 100
    revenue_actual REAL,
    revenue_consensus REAL,
    revenue_surprise_pct REAL,
    implied_move_pct REAL,            -- pre-earnings ATM straddle / stock price (v2, needs options)
    actual_move_pct REAL,             -- gap from prior close
    reaction_price REAL,              -- 4:15 PM for AMC, 9:00 AM for BMO
    reaction_range REAL,              -- extended-hours H-L
    post_earnings_gap_pct REAL,       -- RTH open vs prior RTH close
    gap_filled_within_5d INTEGER,
    gap_filled_within_20d INTEGER,
    price_1d_post REAL,               -- for multi-day tail tracking
    price_5d_post REAL,
    price_10d_post REAL,
    price_20d_post REAL,              -- 4-week post-earnings drift
    historical_beat_rate REAL,        -- % of last 8 quarters with positive surprise
    historical_avg_move_pct REAL,     -- avg absolute move on earnings day (last 8 quarters)
    UNIQUE(ticker, earnings_date)
);

[PANEL: Sonar Reasoning Pro] Track the 2-week post-earnings tail — not just the gap. Pattern matters: some stocks gap and hold, others gap and fill, others have a 5-day reaction. Fields price_1d_post through price_20d_post above capture this.

Economic Calendar

• FOMC dates, CPI, NFP, PPI, retail sales, GDP — shared with forex desk pipeline
• Fields: event_date, event_time, event_name, prior, forecast, actual, surprise_direction
• Source: already collected on forex desk — reuse that pipeline

Corporate Actions

CREATE TABLE IF NOT EXISTS corporate_actions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    action_date TEXT NOT NULL,
    action_type TEXT NOT NULL,        -- 'secondary_offering','buyback_announcement','buyback_execution','merger_announcement','spinoff','debt_offering','shelf_registration','stock_split','reverse_split','dividend_ex_date','special_dividend'
    action_details TEXT,              -- JSON: dollar amounts, share counts, terms
    price_impact_expected TEXT,       -- 'dilutive','accretive','neutral','unknown'
    source_filing TEXT,               -- SEC filing URL or news source
    price_at_announcement REAL,
    price_1d_after REAL,
    price_5d_after REAL
);

Sources: SEC EDGAR 8-K RSS feed (free, real-time — filter by form type 8-K, parse for offering/buyback/M&A keywords); Financial Modeling Prep corporate actions endpoint (free tier).

Cross-references:

• Secondary offering: immediately flag all S&R zones below current price as potentially invalid
• Active buyback: note that the company is a mechanical buyer — creates soft floor
• Add corporate_action_within_5d boolean to all confluence_events and outcome tracking

Buyback Blackout Windows

[PANEL: DeepSeek V3.1] Companies are prohibited from repurchasing shares in the ~30 days before earnings. During blackout, mechanical buyback support disappears. Track blackout windows as derived from earnings dates (earnings_date minus 30 days = start of blackout). No external data source needed beyond the earnings calendar.

CREATE TABLE IF NOT EXISTS buyback_blackout (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    blackout_start TEXT NOT NULL,
    blackout_end TEXT NOT NULL,       -- earnings_date
    UNIQUE(ticker, blackout_start)
);

Section 13 — Seasonal + Time-Based Patterns

Deferred to v2. Patterns are real but weak (~0.5% edge per pattern) and require years of data to identify reliably. They do not generalize well across market regimes.

Exception: collect the following flags now (zero engineering cost, just derive from dates):

• opex_week — boolean: third Friday options expiration week
• opex_day — boolean: third Friday
• quarter_end — boolean: last 5 trading days of quarter (window dressing flows)
• santa_rally_window — boolean: last 5 trading days of December + first 2 days of January
• earnings_season_active — boolean: majority of S&P 500 reporting (approx weeks 2-5 of each quarter)

Section 14 — Derived Metrics + Forward Returns

CREATE TABLE IF NOT EXISTS derived_metrics (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    date TEXT NOT NULL,
    forward_return_1d REAL,           -- next day's close vs today's close (filled post-hoc)
    forward_return_5d REAL,
    forward_return_20d REAL,
    gap_adjusted_return_1d REAL,      -- accounts for gaps
    max_adverse_excursion_5d REAL,    -- worst drawdown over next 5 days
    max_favorable_excursion_5d REAL,  -- best move over next 5 days
    brier_score_st_flip REAL,         -- prediction accuracy for ST flips on this ticker
    brier_score_sr_test REAL,         -- prediction accuracy for S&R zone tests
    UNIQUE(ticker, date)
);

Section 15 — Data Storage Architecture

Database Files (separate DBs to avoid write contention)

• stocks-core.db — candles, indicators, session_data, trendlines
• stocks-levels.db — sr_zones, vwap_checkpoints, volume_profile, luld_bands
• stocks-events.db — earnings_events, earnings_estimates, corporate_actions, halt_events, gap_events, confluence_events
• stocks-positioning.db — institutional_positioning, insider_transactions, large_trades, daily_short_volume, threshold_list
• stocks-options.db — options_levels (rolling 5-day window)
• stocks-crossmarket.db — cross_market, relative_strength, volatility_regime, volume_curve_baseline

SQLite Configuration (apply to all DB files)

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA cache_size=-64000;    -- 64MB cache
PRAGMA temp_store=memory;
PRAGMA mmap_size=536870912;  -- 512MB mmap

Write Rules

• NEVER write row-by-row. Always batch into transactions
• All writes go through a transaction wrapper: BEGIN; ... multiple INSERTs ...; COMMIT
• Queue writes during RTH, flush in batches every 30 seconds
• For options_levels: rolling window — delete records older than 5 trading days before inserting new ones

Sampling Frequencies

Storage Estimates (per stock, per year)

For 50 stocks: ~750MB/year. Well within the 15GB disk available. Archive daily candles older than 2 years to compressed JSON.

Section 16 — Closed Period Price Structure

HH/HL/LH/LL Analysis

CREATE TABLE IF NOT EXISTS price_structure (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    timeframe TEXT NOT NULL,
    ts INTEGER NOT NULL,
    swing_type TEXT,                  -- 'HH','HL','LH','LL'
    swing_price REAL,
    prior_swing_price REAL,
    trend_structure TEXT,             -- 'uptrend','downtrend','ranging'
    candle_body_pct REAL,             -- (close-open)/range *100 (body as % of H-L range)
    candle_type TEXT,                 -- 'bullish_engulf','bearish_engulf','doji','hammer','shooting_star','inside_bar'
    UNIQUE(ticker, timeframe, ts)
);

Gap Events + Classification (Two-Phase System)

Phase 1 — at gap creation (real-time)

• Classify as: earnings_gap (earnings within 1 trading day), news_gap (halt preceded the gap — cross-reference halt_events), or technical_gap (no known catalyst)
• Store: gap_size_atr, gap_direction, gap_vs_trend, gap_open_vs_prior_range

Phase 2 — retrospective (run daily at market close for gaps 1-20 days old)

• gap_subtype: 'breakaway' | 'exhaustion' | 'common' | 'continuation' | 'unresolved'
  • breakaway: price moved further in gap direction by >1.5 ATR AND gap is unfilled
  • exhaustion: gap filled AND price reversed beyond gap origin by >0.5 ATR
  • common: gap filled within same session or next session with no trend change
  • continuation: gap occurred mid-trend, price continued, gap eventually filled
  • unresolved: none of the above criteria met yet
• gap_subtype_confidence: starts at 0.3 on day 1, increases to 0.7 by day 5 if criteria consistently met, reaches 0.9 by day 10

Gap magnetic strength decay

• gap_magnetic_strength: starts at 1.0 on gap day
• Standard gaps (<2x ATR): decay 0.03/day (reaches 0 at day ~33)
• Large gaps (2-4x ATR): decay 0.02/day
• Earnings gaps (>4x ATR): decay 0.015/day (tracked up to 60 days)
• Used as multiplier on gap-fill S&R zone score

Unfilled gap tracking window

• Standard gaps: 20 trading days
• Gaps > 2x ATR: 40 days
• Gaps > 4x ATR (earnings, major news): 60 days
• Archive older gaps; do not delete — keep for historical pattern analysis

CREATE TABLE IF NOT EXISTS gap_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    gap_date TEXT NOT NULL,
    gap_direction TEXT NOT NULL,      -- 'up' or 'down'
    gap_open REAL,
    gap_close REAL,                   -- prior day's close (bottom of gap for up-gap)
    gap_size_pct REAL,
    gap_size_atr REAL,
    gap_type TEXT,                    -- phase 1: 'earnings_gap','news_gap','technical_gap'
    gap_vs_trend TEXT,                -- 'with_trend','counter_trend'
    gap_open_vs_prior_range TEXT,     -- 'inside','outside'
    gap_subtype TEXT,                 -- phase 2: 'breakaway','exhaustion','common','continuation','unresolved'
    gap_subtype_confidence REAL,
    gap_magnetic_strength REAL,
    filled INTEGER DEFAULT 0,
    fill_date TEXT,
    fill_days INTEGER,
    earnings_event_id INTEGER,        -- FK to earnings_events if earnings_gap
    halt_event_id INTEGER,            -- FK to halt_events if news_gap
    corporate_action_within_1d INTEGER DEFAULT 0,
    UNIQUE(ticker, gap_date)
);

Section 17 — S&R Zone Engine

Asset-class agnostic core from the forex engine, adapted for stocks.

Detection Methods

Removed: Pre-market pivot points (thin volume, noisy, not widely used). Removed: Options-derived S&R zones (Tier 2+ only — not built as permanent S&R in v1). Deferred to v2: Full options method with GEX flip zones.

Zone Scoring, Decay, and Clustering

• Composite zone score = sum of individual method scores for all methods that identify the same price level (within 0.5 ATR tolerance)
• Decay: zone score multiplied by 1 - (0.02 × days_since_last_touch); reset to base score on each new touch
• Clustering: merge zones within 0.5 ATR of each other into a single zone; use the midpoint of all contributing levels as the zone center
• Zone width: scaled by price level scaler (Section 5, atr_zone_width_multiplier)
• Gap-fill zones: multiply by gap_magnetic_strength decay from gap_events table

CREATE TABLE IF NOT EXISTS sr_zones (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    timeframe TEXT NOT NULL,
    zone_type TEXT NOT NULL,          -- 'support' or 'resistance'
    zone_center REAL NOT NULL,
    zone_upper REAL NOT NULL,
    zone_lower REAL NOT NULL,
    composite_score REAL NOT NULL,
    contributing_methods TEXT,        -- JSON array of method numbers
    touch_count INTEGER DEFAULT 1,
    first_detected_ts INTEGER,
    last_touch_ts INTEGER,
    days_since_last_touch INTEGER,
    active INTEGER DEFAULT 1,
    source TEXT,                      -- 'multi_touch','fib','ma','pivot','prior_hl','trendline','round_number','zigzag','gap','opening_range','vwap','volume_profile','circuit_breaker'
    decay_multiplier REAL DEFAULT 1.0,
    luld_static INTEGER DEFAULT 0,    -- 1 = LULD band, no decay
    UNIQUE(ticker, timeframe, zone_center, zone_type)
);
CREATE INDEX IF NOT EXISTS idx_sr_zones_active ON sr_zones(ticker, timeframe, active, composite_score DESC);

Key SQL Queries

Get top active S&R zones near current price

SELECT *
FROM sr_zones
WHERE ticker = ?
  AND timeframe = ?
  AND active = 1
  AND ABS(zone_center - ?) / ? < 2.0   -- within 2 ATR of current price
ORDER BY composite_score * decay_multiplier DESC
LIMIT 10;

Check if price is inside a zone

SELECT id, zone_type, composite_score, decay_multiplier
FROM sr_zones
WHERE ticker = ?
  AND timeframe = ?
  AND active = 1
  AND ? BETWEEN zone_lower AND zone_upper;

Section 18 — Cross-Section Intelligence Flags

Implement only flags that use data already built in Steps 1–8. Defer flags requiring options/institutional data until those sources are validated.

Flag 18.2 — Index vs Stock Direction (v1)

• flag_index_divergence — boolean: stock SuperTrend is bullish while SPY is bearish (or vice versa)
• High-value signal: stocks diverging from index direction tend to be trending strongly or reversing strongly

Flag 18.7 — Session + Volume + S&R (v1)

• flag_session_vol_sr — boolean: price tested an S&R zone during power hour (3–4 PM) with RVOL > 2.0
• Late-session high-volume S&R tests have strong directional follow-through the next day

Flag 18.1 — Institutional + RS + ST (v2, after short interest validated)

• flag_institutional_rs_st — short interest > 15% of float AND RS vs SPY positive AND SuperTrend bullish
• Short squeeze setup: high short interest + stock outperforming + trend up

Flag 18.3 — Options + S&R Confluence (v2, after Tier 1 options built)

• flag_options_sr_confluence — price at a major S&R zone AND that zone coincides with a call wall or put wall

Flag 18.5 — Earnings Volatility Setup (v2)

• flag_earnings_iv_setup — IV rank > 70 AND earnings within 5 trading days AND historical avg move > 5%
• Premium selling opportunity or directional play

Flag 18.6 — Short Squeeze Proximity (v2)

• flag_short_squeeze — short_interest_pct_float > 20% AND days_to_cover > 5 AND RVOL > 2.0 AND price above prior week's high

CREATE TABLE IF NOT EXISTS cross_section_flags (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    date TEXT NOT NULL,
    flag_index_divergence INTEGER DEFAULT 0,
    flag_session_vol_sr INTEGER DEFAULT 0,
    flag_institutional_rs_st INTEGER DEFAULT 0,
    flag_options_sr_confluence INTEGER DEFAULT 0,
    flag_earnings_iv_setup INTEGER DEFAULT 0,
    flag_short_squeeze INTEGER DEFAULT 0,
    UNIQUE(ticker, date)
);

Section 19 — Future Additions (Tiered)

Tier 1 (v2 — next build phase)

• Per-stock options chain via Polygon.io ($29/month)
• Full options cross-section flags (18.3, 18.5, 18.6)
• MOC imbalance from Interactive Brokers API (if account available; otherwise keep proxy)
• Full sector rotation matrix (all 11 GICS sectors with rank tracking)
• 13F institutional holdings (quarterly context, not real-time signals — use Form 4 for timing)

Tier 2 (v3)

• Unusual Whales / Spotgamma subscription for pre-computed GEX
• Options flow direction (buy vs sell, opening vs closing) via Tradier or Polygon options flow
• ETF composition data for index rebalancing plays

Deferred Indefinitely (remove from v1 scope)

• Alternative data (satellite imagery, geolocation, web traffic) — $100–500+/month, unproven for systematic use
• Fama-French factor decomposition — research project, not data collection
• GARCH/DCC-GARCH models — computationally expensive, questionable value over HV percentile
• Dark pool sentiment — data not available at useful granularity within budget

Section 20 — Fundamental Valuation Context (light touch)

Not a fundamental analysis engine. One slow-moving backdrop table that feeds one confluence score modifier.

CREATE TABLE IF NOT EXISTS fundamentals (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    date TEXT NOT NULL,               -- last update date
    pe_ratio_ttm REAL,
    pe_ratio_forward REAL,
    ps_ratio_ttm REAL,
    pb_ratio REAL,
    sector_median_pe REAL,
    sector_median_ps REAL,
    pe_vs_sector REAL,                -- stock PE / sector median PE
    revenue_growth_yoy REAL,
    earnings_growth_yoy REAL,
    profit_margin REAL,
    UNIQUE(ticker, date)
);

Source: Financial Modeling Prep API free tier (250 req/day). Pull once daily for each stock (30 stocks = 30 requests). Integration: if Mean Reversion Extreme confluence fires AND pe_vs_sector < 0.7, add +1 confluence score. If pe_vs_sector > 2.0, subtract 1. That is the only place fundamentals touch the technical system.

Section 21 — Real-Time News Headlines (Catalyst Awareness)

[PANEL: GPT-4.1] Real-time news headlines are a MUST-HAVE. Price-moving news (earnings, M&A, FDA, executive changes, SEC actions) causes major gaps and regime shifts. Without structured news data, sudden moves may be misattributed to technical signals.

[PANEL: Grok 4.1] Analyst upgrades/downgrades and price target changes often cause immediate price reactions due to algorithmic trading responses.

CREATE TABLE IF NOT EXISTS news_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    published_ts INTEGER NOT NULL,
    headline TEXT NOT NULL,
    source TEXT,
    category TEXT,                    -- 'earnings','analyst_action','ma','regulatory','executive','macro','other'
    sentiment_label TEXT,             -- 'positive','negative','neutral' (FinBERT or similar)
    sentiment_score REAL,             -- -1.0 to 1.0
    analyst_action TEXT,              -- 'upgrade','downgrade','initiation','price_target_raise','price_target_cut' (NULL if not analyst)
    analyst_firm TEXT,
    price_target_new REAL,
    price_target_prior REAL,
    price_at_event REAL,
    price_1h_after REAL,
    price_1d_after REAL
);
CREATE INDEX IF NOT EXISTS idx_news_ticker_ts ON news_events(ticker, published_ts DESC);

Sources: Benzinga (limited free tier), Yahoo Finance headlines RSS (scraping), Finviz RSS, Google Finance news. For sentiment: run free Hugging Face FinBERT model locally — no API cost.

Cadence: poll every 5 minutes during RTH for tracked tickers. Store within 5 minutes of publication.

Implementation Checklist

Phase 1 — Foundation (1–2 weeks)

• Set up SQLite databases (6 DB files) with PRAGMA settings
• Implement candle ingestion pipeline for 20–30 stocks across 5 timeframes
• Implement adjusted_close tracking (splits/dividends)
• Calculate: SuperTrend, ATR (5+14), RSI, MACD, Stochastic, ADX, Bollinger Bands
• Build ticker_config table with price scaler fields
• Data source selection: Alpaca (free, includes extended hours) OR Polygon.io ($29/month)

Phase 2 — Sessions + Gaps (3–5 days)

• Implement session_data table with RTH/premarket/afterhours boundaries
• Build gap detection at 9:30 open (Phase 1 gap classification: earnings/news/technical)
• Integrate halt feed polling (NYSE + Nasdaq, 30s interval)
• Flag halted candles in candles table
• Implement LULD band tracking (5-min update cycle)
• Calculate circuit breaker levels from SPY prior close daily

Phase 3 — VWAP + Volume Profile (2–3 days)

• Implement daily VWAP with ±1σ and ±2σ bands
• Build volume_curve_baseline table (78 time buckets per stock)
• Implement RVOL with time-of-day normalization
• Build Volume Profile (VPOC, VAH, VAL) per session + 5-day composite
• Implement VWAP checkpoint system for anchored VWAPs
• Calculate earnings-anchored VWAP (active 30 days post-earnings)
• Calculate 52w-high and 52w-low anchored VWAPs

Phase 4 — S&R Zone Engine (1–2 weeks)

• Port S&R engine from forex desk (asset-class agnostic core)
• Implement stock-specific methods: opening range (Method 10), VWAP (Method 11), Volume Profile (Method 12)
• Apply round number grid with price scaler
• Implement zone clustering (merge zones within 0.5 ATR)
• Implement decay function (0.02/day, reset on touch)
• Add gap edges as S&R zones with magnetic strength decay
• Add LULD bands as Method 13 zones (zero decay, max score)

Phase 5 — Cross-Market + Relative Strength (3–5 days)

• Collect SPY, QQQ, IWM, and all 11 sector ETF price data via same pipeline
• Calculate rolling correlations (20d, 60d) for each stock vs index/sector
• Calculate beta vs SPY (60d rolling)
• Build relative_strength table: RS vs SPY, RS vs sector, RS rank
• Build cross_market table: VIX, yields, circuit breakers, sector outperformers/underperformers
• Implement circuit_breaker_warning flag

Phase 6 — Relative Strength + Confluence (2–3 days)

• Implement RS rank across entire tracked universe (requires all stocks processed first)
• Build confluence_events table and scoring logic
• Implement flag 18.2 (index divergence) and flag 18.7 (session+vol+SR)
• Wire corporate_action_within_5d and halt_involved into confluence records

Phase 7 — Earnings + Catalysts (3–5 days)

• Integrate Financial Modeling Prep API for earnings dates and consensus estimates
• Build earnings_estimates table (pull weekly + daily near earnings)
• Build earnings_events table with post-earnings tracking fields
• Implement post-earnings tail tracking (price 1d/5d/10d/20d post)
• Set up EDGAR 8-K RSS feed for corporate action detection
• Build corporate_actions table
• Build buyback_blackout derived table from earnings calendar

Phase 8 — Volatility Regime (2–3 days)

• Calculate HV (20d, 60d), HV percentile (252d)
• Implement Kaufman Efficiency Ratio
• Detect Bollinger Band squeeze (BB width < Keltner Channel width)
• Build volatility_regime table

Phase 9 — Trend Lines + ZigZag + Divergences (1–2 weeks)

• Implement ZigZag pivot detection
• Implement automated trend line detection (2-touch minimum)
• Apply candle-index slope convention + slope_pct_per_day normalization
• Detect RSI divergences (Layer 1, fixed 70/30 thresholds, 4H+ only)
• Build trendlines table

Phase 10 — Price Structure + Full Gap Classification (1 week)

• Build price_structure table (HH/HL/LH/LL, candle types)
• Implement Phase 2 gap reclassification (daily job, runs at market close)
• Implement gap_subtype_confidence scoring
• Implement gap_magnetic_strength decay

Phase 11 — Short Interest + Insiders (3–5 days)

• Set up FINRA twice-monthly short interest download
• Set up FINRA daily short volume download
• Integrate OpenInsider.com API or Gurufocus for Form 4 data
• Build insider_transactions table with auto_plan flag
• Calculate float from Financial Modeling Prep API
• Set up SEC S-3/8-K RSS monitoring for float change events
• Set up SEC threshold list daily download (Reg SHO)
• Build institutional_positioning table

Phase 12 — Seasonal + News + Fundamentals (3–5 days)

• Implement opex_week, quarter_end, earnings_season_active flags
• Set up news headline collection (Yahoo Finance RSS + Benzinga free)
• Deploy FinBERT locally for sentiment scoring
• Build analyst action parser (upgrade/downgrade/price target)
• Build fundamentals table with FMP API pull (once daily)

Phase 13 — Options (Tier 0, v1)

• Calculate SPY/QQQ max pain from CBOE delayed quotes or Tradier free tier (daily at 3:45 PM)
• Collect CBOE daily PCR for SPY/QQQ
• Build options_levels table (v0 schema, market-wide only)

Phase 14 — Cross-Section Flags + Backtesting

• Implement flags 18.1, 18.3, 18.5, 18.6 (after Phase 11 data validated)
• Build derived_metrics table with forward returns
• Build backtesting harness measuring Brier scores per signal type
• Backtest validation gate: only promote signals to live if they add >1% accuracy improvement

Phase 15 — Options Tier 1 (v2, separate project)

• Subscribe to Polygon.io ($29/month)
• Build per-stock options chain collection (hourly, RTH)
• Calculate per-stock: max_pain, put_call_ratio, iv_rank, call_wall, put_wall
• Wire into S&R zone engine as temporary (non-decaying) options levels
• Enable flags 18.3, 18.5

API Endpoints Reference

Cross-Desk Reusability

Notes for Coding Bot

1. Start with Phase 1. Do not skip ahead. Every other phase depends on clean candle data.
2. Use Alpaca API first (free, includes extended hours). Switch to Polygon.io only if rate limits become a constraint.
3. SQLite WAL mode is mandatory. Enable it on every DB file before any writes.
4. Use separate DB files for different data categories to avoid write contention.
5. Never write row-by-row. Always batch into transactions.
6. The price level scaler must be applied everywhere: S&R zone widths, ATR buffers, spread thresholds. A $3 stock and a $300 stock are not the same.
7. Gap classification is two-phase. Do not try to classify breakaway/exhaustion at creation time. Assign 'unclassified' at creation, reclassify daily.
8. RSI divergence detection uses fixed 70/30. Adaptive thresholds are only for confluence labels, not divergence detection.
9. RVOL requires the volume_curve_baseline table. Build it before computing RVOL.
10. Options data is Tier 0 (SPY/QQQ max pain only) in v1. Do not attempt per-stock options collection in v1.
11. Halt detection is not optional. Without it, outcome tracking is poisoned by halt-driven moves attributed to technical signals.
12. The ticker_config table is the master configuration. Populate it before starting data collection for any stock.
13. Stock broker (TBD) will be the primary execution venue. When the broker is selected, verify which data endpoints their API exposes (many brokers include real-time Level 1, some expose Level 2, a few expose options chains). Update the API endpoints table accordingly before Phase 13.

Build Status (Updated 2026-03-29)

Data Sources

Code

• src/pipeline/ta/plugins/stocks-metrics.ts -- 8 proprietary metrics
• src/pipeline/ta/plugins/stocks-overlay.ts -- gap analysis, RVOL, sector lag

Proprietary Metrics

Instruments

SPY, QQQ (equity ETFs via Polygon)

What's NOT Built Yet

• Options-derived levels (max pain, GEX, unusual OI)
• Earnings calendar integration
• 6 stubbed metrics awaiting external data feeds

Foundation Models (shared sidecar, port 5050, systemd auto-restart)

Forecasts persisted to ta_model_forecasts with Kronos OHLCV + derived candle shape. Stubbed: TTM (Python 3.11), FinCast (no weights).

Shared Core (all desks, computed every 15 min per instrument)

Pipeline Wiring

• Collector: src/pipeline/ta/collector.ts (wired into main.ts Step 9d)
• Index: src/pipeline/ta/index.ts (barrel export)
• Crypto data: every 5 min | Futures data: every 15 min | Indicators: every 15 min | Plugins: every 30 min
• Graceful shutdown wired | systemd service for model sidecar