# Data Flow into Database Tables

This document explains how data flows into three key database tables: `prices_intraday_1m`, `prices_daily`, and `AlertStream_monthly`.

## 1. prices_intraday_1m Table

### Real-Time Data Source (PostgreSQL)
**Service**: `backend/src/services/bookmapWebSocketService.js`

- **Source**: Bookmap trading platform add-on
- **Method**: WebSocket server (port 3001 by default)
- **Process**:
  1. Receives real-time trade data from Bookmap add-on via WebSocket
  2. Aggregates trades into 1-minute OHLCV bars in memory
  3. Flushes completed bars (at least 1 minute old) to PostgreSQL every 30 seconds
  4. Uses `INSERT ... ON CONFLICT` to handle duplicates
- **Activation**: Enabled when `ENABLE_BOOKMAP !== 'false'` in environment
- **Direct Insert**: Writes directly to PostgreSQL `prices_intraday_1m` table

### Historical/Batch Data Source (SQLite)
**Script**: `yahhooscript.py`

- **Source**: Yahoo Finance via `yfinance` Python library
- **Method**: Batch downloads with configurable intervals
- **Process**:
  1. Fetches 1-minute intraday data for configured symbol universe (S&P 500, S&P 400, Nasdaq-100, plus custom groups)
  2. Processes data in batches (default: 110 symbols per batch)
  3. Writes to SQLite database at `C:\Users\srk47\Desktop\options_flow.db`
  4. Uses `INSERT OR REPLACE` for upserts
- **Note**: This writes to SQLite, not directly to PostgreSQL. A separate sync mechanism would be needed to transfer to PostgreSQL.

## 2. prices_daily Table

**Script**: `yahhooscript.py`

- **Source**: Yahoo Finance via `yfinance` Python library
- **Method**: Incremental daily updates
- **Process**:
  1. For each symbol, checks existing data count
  2. If < 10 records exist, performs full download (default: 2 years)
  3. Otherwise, performs incremental update from last date minus 5 days
  4. Writes to SQLite `prices_daily` table
  5. Uses `INSERT ... ON CONFLICT` for upserts
- **Scheduling**: Runs in loop mode with configurable interval (`DAILY_INTERVAL_MIN`)
- **Note**: This writes to SQLite, not directly to PostgreSQL. A separate sync mechanism would be needed to transfer to PostgreSQL.

## 3. AlertStream_monthly Table

### CSV Import Method
**Script**: `backend/scripts/importCSV.js`

- **Source**: CSV files
- **Method**: Manual/scripted CSV import
- **Process**:
  1. Parses CSV file with AlertStream data
  2. Maps columns to both CamelCase and lowercase versions for compatibility
  3. Batch inserts (100 records per batch) into PostgreSQL `AlertStream_monthly` table
  4. Handles duplicate columns (Date/date, Ticker/ticker, etc.)

### BlackBox API Method
**Script**: `watch_and_upload_blackbox_postgres.py`

- **Source**: BlackBox Stocks API (`https://api.blackboxstocks.com/api/v2/options/getFlowMobile`)
- **Method**: API fetch and sync
- **Process**:
  1. Fetches alert/flow data from BlackBox API (requires `BLACKBOX_API_TOKEN`)
  2. Maps API response to database schema (snake_case for AlertStream_monthly)
  3. Normalizes and prepares data for PostgreSQL
  4. Uses chunked upserts (default: 3000 records per chunk) with `row_hash` for deduplication
  5. Writes directly to PostgreSQL `AlertStream_monthly` table
- **Usage**: Run with `--table AlertStream_monthly` parameter
- **Default**: Script defaults to `OptionsFlow_monthly` table, but can target AlertStream_monthly

## Data Flow Diagram

```
┌─────────────────────────────────────────────────────────────┐
│                    prices_intraday_1m                        │
├─────────────────────────────────────────────────────────────┤
│                                                               │
│  Real-Time Path:                                             │
│  Bookmap Add-on → WebSocket → bookmapWebSocketService.js    │
│                    → PostgreSQL (direct insert)              │
│                                                               │
│  Historical Path:                                            │
│  Yahoo Finance → yahhooscript.py → SQLite                    │
│  (Note: Requires separate sync to PostgreSQL)                │
│                                                               │
└─────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────┐
│                      prices_daily                            │
├─────────────────────────────────────────────────────────────┤
│                                                               │
│  Yahoo Finance → yahhooscript.py → SQLite                     │
│  (Note: Requires separate sync to PostgreSQL)                │
│                                                               │
└─────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────┐
│                  AlertStream_monthly                         │
├─────────────────────────────────────────────────────────────┤
│                                                               │
│  Method 1: CSV Import                                        │
│  CSV File → importCSV.js → PostgreSQL                        │
│                                                               │
│  Method 2: API Sync                                          │
│  BlackBox API → watch_and_upload_blackbox_postgres.py       │
│                → PostgreSQL                                  │
│                                                               │
└─────────────────────────────────────────────────────────────┘
```

## Key Files

- **Real-time intraday prices**: `backend/src/services/bookmapWebSocketService.js` (lines 146-213)
- **Historical price data**: `yahhooscript.py` (lines 317-454)
- **CSV import**: `backend/scripts/importCSV.js` (lines 371-526)
- **API sync**: `watch_and_upload_blackbox_postgres.py` (lines 637-733)

## Important Notes

1. **SQLite vs PostgreSQL**: The `yahhooscript.py` script writes to SQLite, not directly to PostgreSQL. If you need this data in PostgreSQL, you'll need a separate sync mechanism.

2. **Bookmap Service**: The Bookmap WebSocket service writes directly to PostgreSQL and is the primary real-time source for `prices_intraday_1m`.

3. **AlertStream Sources**: AlertStream_monthly can be populated from either CSV files or the BlackBox API, depending on your data source preference.

## Configuration

### Bookmap WebSocket Service
- **Environment Variable**: `ENABLE_BOOKMAP` (default: enabled if not set to 'false')
- **Port**: `BOOKMAP_WS_PORT` (default: 3001)
- **Flush Interval**: Every 30 seconds (hardcoded in `bookmapWebSocketService.js`)

### Yahoo Finance Script
- **Database Path**: `DB_PATH` in `yahhooscript.py` (default: `C:\Users\srk47\Desktop\options_flow.db`)
- **Symbol Universe**: Configured via `SEGMENTS`, `EXTRA_SYMBOLS`, and `EXTRA_GROUPS` in `yahhooscript.py`
- **Batch Size**: `BATCH_SIZE` (default: 110)
- **Loop Mode**: `LOOP` (default: True)

### BlackBox API Sync
- **Environment Variable**: `BLACKBOX_API_TOKEN` (required)
- **PostgreSQL Connection**: Configured via environment variables:
  - `POSTGRES_HOST` (default: localhost)
  - `POSTGRES_PORT` (default: 5432)
  - `POSTGRES_DB` (default: institutional_trader)
  - `POSTGRES_USER` (default: postgres)
  - `POSTGRES_PASSWORD` (default: postgres)
- **Upsert Chunk Size**: `UPSERT_CHUNK` (default: 3000)

## Usage Examples

### Running Yahoo Finance Script
```bash
# One-time run (no loop)
python yahhooscript.py

# Single symbol daily data
python yahhooscript.py --only AAPL --daily

# Single symbol intraday data
python yahhooscript.py --only AAPL --intraday
```

### Running BlackBox API Sync for AlertStream
```bash
# Sync AlertStream for today
python watch_and_upload_blackbox_postgres.py --table AlertStream_monthly

# Sync for specific date range
python watch_and_upload_blackbox_postgres.py --table AlertStream_monthly --start-date 2024-01-01 --end-date 2024-01-31
```

### Importing CSV for AlertStream
```bash
# Using the import script
node backend/scripts/importCSV.js path/to/alertstream.csv
```