institutional-trader/PHASE1_DATA_SOURCES.md

Phase 1 Data Sources

This document explains where each Phase 1 feature gets its data from.

Data Flow Overview

PostgreSQL Database
    ↓
OptionsFlow_monthly table (raw options flow data)
    ↓
OptionsFlowProcessor (normalizes, calculates badges, aggregates)
    ↓
PriceContextService (adds price context, VWAP)
    ↓
AlertService (matches alerts)
    ↓
Phase 1 Services (Signal Tier, Price Reaction, Checklist)
    ↓
Final Output

1. Initial Data Source

Location: backend/python_service/main.py (lines 115-123)

SELECT *
FROM "OptionsFlow_monthly"
WHERE "Premium" IS NOT NULL
  AND TRIM("Premium"::text) <> ''
  AND "StockEtf" = 'STOCK'
  AND "Symbol" NOT IN ('TSLA', 'NVDA')

What it provides:

• Raw options flow records from the OptionsFlow_monthly table
• All columns from the table (Symbol, Premium, Strike, Expiration, etc.)

2. Signal Tier Classification

Service: backend/python_service/services/signal_tier_classifier.py

Data Sources:

• Badges: From OptionsFlowProcessor (calculated from flow data)
  • badge_round: 🟢 or 🔴 (from direction and net premium)
  • badge_more: 💎⭐ (from premium thresholds, volume/OI ratios)
• Premium: From processed flow data
  • premium_num: Total premium for the signal
  • bull_total, bear_total: Bullish vs bearish premium
  • prem_cb_itm, prem_ps_itm, prem_cs_itm, prem_pb_itm: ITM premium breakdown
• Direction: From OptionsFlowProcessor
  • direction: 'BULL' or 'BEAR'
• Volume/OI: From raw flow data
  • vol_num: Volume
  • oi_num: Open Interest

Database Tables Used:

• ✅ OptionsFlow_monthly (via OptionsFlowProcessor)
• ❌ No direct database queries

3. Price Reaction Tracking

Service: backend/python_service/services/price_reaction_tracker.py

Data Sources:

• Signal Time: From processed flow data
  • flow_ts_utc: Timestamp of the signal
  • symbol_norm: Normalized symbol
• Price at Signal: From PriceContextService
  • u_close: Price at the time of the signal

Database Tables Used:

• ✅ prices_intraday_1m (queried directly)
  • Gets price at 5m, 15m, 30m after signal time
  • Query: SELECT close FROM prices_intraday_1m WHERE symbol = $1 AND ts <= $2 ORDER BY ts DESC LIMIT 1

Calculation:

reaction_5m = ((price_5m - price_at_signal) / price_at_signal) * 100
flow_led_to_move = abs(reaction_5m) > 0.5  # 0.5% threshold

4. Trade Checklist

Service: backend/python_service/services/trade_checklist.py

Data Sources:

• Badges: From OptionsFlowProcessor
  • badge_round: 🟢 or 🔴
  • badge_more: 💎⭐
• VWAP: From PriceContextService
  • vwap_at_signal: VWAP at the time of the signal
  • price_vs_vwap_pct: Percentage distance from VWAP
• Index Alignment: From processed flow data (if available)
  • index_aligned: Boolean indicating if index confirms

Database Tables Used:

• ✅ prices_intraday_1m (via PriceContextService for VWAP calculation)
• ✅ prices_daily (via PriceContextService for prior close)

Checklist Items:

1. ✅ Has direction (🟢 or 🔴)
2. ✅ Has diamond (💎)
3. ✅ Has star (⭐)
4. ✅ Price respects VWAP (within ±2%)
5. ✅ Index confirms (if available)

5. VWAP Calculation

Service: backend/python_service/services/price_context.py

Data Sources:

• RTH Open: From prices_intraday_1m
  • Gets first bar at 9:30 AM CST for the trading day
• Price Data: From prices_intraday_1m
  • Gets all 1-minute bars from RTH open to signal time
  • Calculates: VWAP = Σ(price × volume) / Σ(volume)

Database Tables Used:

• ✅ prices_intraday_1m (queried directly)
  • Query for RTH open: SELECT open FROM prices_intraday_1m WHERE symbol = $1 AND date = $2 AND time >= '09:30:00' ORDER BY ts ASC LIMIT 1
  • Query for VWAP: SELECT close, volume FROM prices_intraday_1m WHERE symbol = $1 AND ts >= $2 AND ts <= $3 ORDER BY ts ASC

Summary Table

Phase 1 Feature	Primary Data Source	Database Tables	Direct DB Queries?
Signal Tier	OptionsFlowProcessor (badges, premium)	OptionsFlow_monthly (via processor)	❌ No
Price Reaction	prices_intraday_1m	prices_intraday_1m	✅ Yes
Trade Checklist	OptionsFlowProcessor (badges) + PriceContextService (VWAP)	OptionsFlow_monthly, prices_intraday_1m	✅ Yes (via PriceContextService)
VWAP	prices_intraday_1m	prices_intraday_1m	✅ Yes

Key Dependencies

1. OptionsFlowProcessor must run first to calculate badges and normalize data
2. PriceContextService must run before Phase 1 to provide VWAP and price context
3. Price Reaction requires prices_intraday_1m to have data for the signal time + 5/15/30 minutes
4. VWAP requires prices_intraday_1m to have data from RTH open (9:30 AM CST) to signal time

Common Issues

"Not calculated" for all fields

• Cause: Python service not running, or data coming from SQL fallback
• Solution: Ensure Python service is running at http://localhost:8010

Price Reaction shows "Not calculated"

• Cause:
  • Historical data (future dates have no price data)
  • Missing price data in prices_intraday_1m for the time period
  • Signal time is invalid
• Solution: Check that prices_intraday_1m has data for the signal date and time

VWAP shows "Not calculated"

• Cause:
  • Signal occurred before RTH open (9:30 AM CST)
  • Missing price data in prices_intraday_1m for the trading day
  • Historical/future dates
• Solution: VWAP is only available during RTH hours (9:30 AM - 4:00 PM CST)

Signal Tier shows "Not calculated"

• Cause: Python service not running (this should always work if service is running)
• Solution: Check Python service logs for errors in SignalTierClassifier