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)

```python
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:**
```python
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`