# Invest Copilot — Data Model

## Entity-Relationship Overview

```
┌──────────────┐     ┌──────────────┐     ┌──────────────────┐
│   User       │     │   Watchlist  │     │   Screener       │
│──────────────│     │──────────────│     │──────────────────│
│ id (PK)      │◄──┐ │ id (PK)      │     │ id (PK)          │
│ email        │   │ │ name         │     │ name             │
│ name         │   │ │ user_id (FK) │     │ user_id (FK)     │
│ avatar_url   │   │ │ created_at   │     │ created_at       │
│ timezone     │   │ │ updated_at   │     │ conditions (JSONB)│
│ settings     │   └────┬───────────┘     │ conditions_json  │
│ created_at   │        │                 │ created_at       │
└──────────────┘        │                 └──────────────────┘
                        │
                        │    ┌──────────────────┐
                        └───►│   WatchlistItem  │
                             │──────────────────│
                             │ id (PK)          │
                             │ watchlist_id (FK)│
                             │ ticker           │
                             │ type (stock/etf) │
                             │ added_at         │
                             │ custom_notes     │
                             └────────┬─────────┘
                                      │
                                      │    ┌──────────────────┐
                                      └───►│   PriceHistory     │
                                           │──────────────────│
                                           │ id (PK)            │
                                           │ ticker             │
                                           │ date (Timescale)   │
                                           │ open               │
                                           │ high               │
                                           │ low                │
                                           │ close              │
                                           │ volume             │
                                           │ adjusted_close     │
                                           └────────────────────┘

┌──────────────────┐     ┌──────────────────┐     ┌──────────────────┐
│   Strategy       │     │   WatchlistStrategy│    │   Alert          │
│──────────────────│     │──────────────────│     │──────────────────│
│ id (PK)          │◄────│ id (PK)          │     │ id (PK)          │
│ user_id (FK)     │     │ watchlist_id(FK) │     │ watchlist_id(FK) │
│ name             │     │ strategy_id(FK)  │     │ strategy_id(FK)  │
│ description      │     │ is_active        │     │ type             │
│ type (technical/ │     │ created_at       │     │ trigger_type     │
│  fundamental)    │     │ conditions (JSONB)│    │ message_template │
│ conditions (JSONB)│    │ created_at       │     │ triggered_at     │
│ backtest_result  │     │ updated_at       │     │ resolved_at      │
│ created_at       │     └────────┬─────────┘     │ resolved_at      │
│ updated_at       │              │               │ created_at       │
└──────────────────┘              │               └──────────────────┘
                                  │
                                  │    ┌──────────────────┐
                                  └───►│   SectorRotation   │
                                       │──────────────────│
                                       │ id (PK)            │
                                       │ date               │
                                       │ sector_ticker      │
                                       │ rank_now           │
                                       │ rank_previous      │
                                       │ rank_change        │
                                       │ momentum_20d       │
                                       │ momentum_50d       │
                                       │ momentum_200d      │
                                       │ relative_strength  │
                                       │ rotation_signal    │
                                       │ macro_context      │
                                       │ analysis_summary   │
                                       └────────────────────┘
```

## Detailed Schema

### users
```sql
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255),  -- NULL if OAuth-only
    name VARCHAR(255),
    avatar_url TEXT,
    timezone VARCHAR(50) DEFAULT 'UTC',
    settings JSONB DEFAULT '{}',  -- {currency: 'USD', theme: 'dark', alerts_enabled: true}
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);
```

### watchlists
```sql
CREATE TABLE watchlists (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_watchlists_user ON watchlists(user_id);
```

### watchlist_items
```sql
CREATE TABLE watchlist_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    watchlist_id UUID NOT NULL REFERENCES watchlists(id) ON DELETE CASCADE,
    ticker VARCHAR(20) NOT NULL,
    type VARCHAR(20) NOT NULL CHECK (type IN ('stock', 'etf', 'index')),
    custom_notes TEXT,
    added_at TIMESTAMPTZ DEFAULT NOW()
);

-- Prevent duplicates: a watchlist can't have the same ticker twice
CREATE UNIQUE INDEX idx_watchlist_items_unique ON watchlist_items(watchlist_id, ticker);
```

### prices (TimescaleDB hypertable)
```sql
-- Hypertable for time-series price data
CREATE TABLE prices (
    ticker VARCHAR(20) NOT NULL,
    date TIMESTAMPTZ NOT NULL,
    open DECIMAL(15,4),
    high DECIMAL(15,4),
    low DECIMAL(15,4),
    close DECIMAL(15,4),
    volume BIGINT,
    adjusted_close DECIMAL(15,4),
    PRIMARY KEY (ticker, date)
);

-- Convert to hypertable (TimescaleDB extension)
SELECT create_hypertable('prices', 'date');

-- Compression for older data (automated by Timescale policy)
CREATE POLICY prices_compress_policy ON prices
    FOR ALL
    USING (date < NOW() - INTERVAL '90 days');

-- Continuous aggregate for common timeframes
CREATE MATERIALIZED VIEW prices_daily
WITH (timescaledb.continuous) AS
SELECT ticker,
       time_bucket('1 day', date) AS bucket,
       first(open, date) AS open,
       max(high) AS high,
       min(low) AS low,
       last(close, date) AS close,
       sum(volume) AS volume
FROM prices
GROUP BY ticker, bucket;
```

### stock_profiles
```sql
CREATE TABLE stock_profiles (
    ticker VARCHAR(20) PRIMARY KEY,
    name VARCHAR(500),
    exchange VARCHAR(20),
    sector VARCHAR(100),
    industry VARCHAR(200),
    market_cap BIGINT,
    description TEXT,
    website TEXT,
    ceo VARCHAR(255),
    employees INTEGER,
    pe_ratio DECIMAL(10,2),
    eps DECIMAL(10,4),
    dividend_yield DECIMAL(8,4),
    beta DECIMAL(6,4),
    last_updated TIMESTAMPTZ DEFAULT NOW()
);
```

### sec_filings
```sql
CREATE TABLE sec_filings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ticker VARCHAR(20) NOT NULL,
    cik VARCHAR(20),
    form_type VARCHAR(10) NOT NULL,  -- 10-K, 10-Q, 8-K, 4, 13F, 13D, 13G
    filing_date DATE NOT NULL,
    report_date DATE,
    accession_number VARCHAR(50),
    url TEXT,
    content_summary TEXT,  -- AI-generated summary
    key_metrics JSONB,  -- Extracted financials from 10-K/10-Q
    sentiment_score DECIMAL(5,4),  -- NLP sentiment: -1 to +1
    tags TEXT[],  -- e.g., ['earnings', 'executive_change', 'litigation']
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_sec_filings_ticker ON sec_filings(ticker);
CREATE INDEX idx_sec_filings_form ON sec_filings(form_type);
CREATE INDEX idx_sec_filings_date ON sec_filings(filing_date);
```

### insider_trades
```sql
CREATE TABLE insider_trades (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ticker VARCHAR(20) NOT NULL,
    insider_name VARCHAR(500),
    insider_title VARCHAR(500),
    transaction_date DATE NOT NULL,
    transaction_type VARCHAR(10),  -- Buy, Sell, Gift, In-Ex
    shares INTEGER,
    price_per_share DECIMAL(10,4),
    total_value DECIMAL(15,4),
    shares_owned_after INTEGER,
    filing_date DATE,
    source_url TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_insider_trades_ticker ON insider_trades(ticker);
CREATE INDEX idx_insider_trades_type ON insider_trades(transaction_type);
```

### strategies
```sql
CREATE TABLE strategies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    type VARCHAR(20) NOT NULL CHECK (type IN ('technical', 'fundamental', 'hybrid')),
    conditions JSONB NOT NULL,  -- Structured strategy conditions
    backtest_results JSONB,     -- Last backtest result
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);
```

### strategy_conditions (JSONB schema example)
```json
{
  "type": "technical",
  "rules": [
    {
      "indicator": "rsi",
      "operator": "lt",
      "value": 30,
      "description": "RSI below 30 (oversold)"
    },
    {
      "indicator": "sma",
      "params": {"period": 200, "source": "close"},
      "operator": "gt",
      "value": null,
      "description": "Price above 200-day SMA"
    },
    {
      "indicator": "volume",
      "operator": "gt",
      "value": 1.5,
      "description": "Volume > 1.5x average 20-day volume"
    }
  ],
  "logic": "AND"
}
```

### watchlist_strategies
```sql
CREATE TABLE watchlist_strategies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    watchlist_id UUID NOT NULL REFERENCES watchlists(id) ON DELETE CASCADE,
    strategy_id UUID NOT NULL REFERENCES strategies(id) ON DELETE CASCADE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_watchlist_strategies_unique ON watchlist_strategies(watchlist_id, strategy_id);
```

### alerts
```sql
CREATE TABLE alerts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    watchlist_id UUID NOT NULL REFERENCES watchlists(id) ON DELETE CASCADE,
    strategy_id UUID REFERENCES strategies(id),
    ticker VARCHAR(20),
    type VARCHAR(30) NOT NULL,  -- strategy_trigger, sec_filing, sentiment, rotation, price
    trigger_type VARCHAR(50),
    message TEXT NOT NULL,
    severity VARCHAR(10) DEFAULT 'info' CHECK (severity IN ('info', 'warning', 'critical')),
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'resolved', 'dismissed')),
    triggered_at TIMESTAMPTZ DEFAULT NOW(),
    resolved_at TIMESTAMPTZ,
    metadata JSONB DEFAULT '{}'
);

CREATE INDEX idx_alerts_watchlist ON alerts(watchlist_id);
CREATE INDEX idx_alerts_status ON alerts(status);
CREATE INDEX idx_alerts_triggered ON alerts(triggered_at);
```

### sector_rotations
```sql
CREATE TABLE sector_rotations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    detection_date DATE NOT NULL,
    sector_ticker VARCHAR(20) NOT NULL,  -- XLK, XLF, etc.
    sector_name VARCHAR(100),
    rank_now INTEGER,
    rank_previous INTEGER,
    rank_change INTEGER,
    momentum_20d DECIMAL(8,4),
    momentum_50d DECIMAL(8,4),
    momentum_200d DECIMAL(8,4),
    relative_strength DECIMAL(8,4),
    rotation_signal VARCHAR(20),  -- 'in', 'out', 'stable', 'accelerating'
    macro_context JSONB,
    analysis_summary TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_sector_rotations_date ON sector_rotations(detection_date);
CREATE INDEX idx_sector_rotations_signal ON sector_rotations(rotation_signal);
```

### screeners
```sql
CREATE TABLE screeners (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    conditions JSONB NOT NULL,
    results_count INTEGER DEFAULT 0,
    last_run_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);
```

### screener_results
```sql
CREATE TABLE screener_results (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    screener_id UUID NOT NULL REFERENCES screeners(id) ON DELETE CASCADE,
    ticker VARCHAR(20) NOT NULL,
    match_score DECIMAL(5,4),
    ranked_position INTEGER,
    result_data JSONB,
    generated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_screener_results_screener ON screener_results(screener_id);
```

### peer_groups
```sql
CREATE TABLE peer_groups (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ticker VARCHAR(20) NOT NULL,
    peer_ticker VARCHAR(20) NOT NULL,
    similarity_score DECIMAL(5,4),  -- Based on sector, industry, market cap
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (ticker, peer_ticker)
);

CREATE INDEX idx_peer_groups_ticker ON peer_groups(ticker);
```

### rotation_alerts_preferences
```sql
CREATE TABLE rotation_alert_preferences (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    sectors TEXT[] NOT NULL DEFAULT '{}',  -- ['tech', 'healthcare', 'energy', ...]
    min_rank_change INTEGER DEFAULT 2,
    email_enabled BOOLEAN DEFAULT TRUE,
    push_enabled BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);
```

## TimescaleDB Optimization

### Compression Policy
```sql
-- Compress data older than 90 days
SELECT add_compress_policy('prices', INTERVAL '90 days');

-- Reorder by ticker for better compression
SELECT add_reorder_policy('prices', 'ticker');
```

### Continuous Aggregates
```sql
-- 1-hour aggregates for intraday analysis
CREATE MATERIALIZED VIEW prices_hourly
WITH (timescaledb.continuous) AS
SELECT ticker,
       time_bucket('1 hour', date) AS bucket,
       first(open, date) AS open,
       max(high) AS high,
       min(low) AS low,
       last(close, date) AS close,
       sum(volume) AS volume
FROM prices
GROUP BY ticker, bucket;

-- 1-day aggregates for trend analysis
CREATE MATERIALIZED VIEW prices_daily
WITH (timescaledb.continuous) AS
SELECT ticker,
       time_bucket('1 day', date) AS bucket,
       first(open, date) AS open,
       max(high) AS high,
       min(low) AS low,
       last(close, date) AS close,
       sum(volume) AS volume
FROM prices
GROUP BY ticker, bucket;
```

### Indexes for Common Queries
```sql
-- Composite indexes for frequent query patterns
CREATE INDEX idx_prices_ticker_date ON prices(ticker, date DESC);
CREATE INDEX idx_prices_date_ticker ON prices(date, ticker);
CREATE INDEX idx_sec_filings_ticker_type ON sec_filings(ticker, form_type);
CREATE INDEX idx_insider_trades_ticker_date ON insider_trades(ticker, transaction_date DESC);
```

## Data Ingestion Pipeline

```
┌─────────────────────────────────────────────────────────────┐
│                    DATA INGESTION PIPELINE                   │
│                                                              │
│  ┌─────────────┐    ┌─────────────┐    ┌─────────────────┐ │
│  │  Price      │    │  SEC        │    │  Alternative    │ │
│  │  Ingestor   │    │  Filing     │    │  Data           │ │
│  │  (hourly)   │    │  Parser     │    │  Pipeline       │ │
│  │             │    │  (daily)    │    │  (daily)        │ │
│  └──────┬──────┘    └──────┬──────┘    └────────┬────────┘ │
│         │                  │                      │          │
│         ▼                  ▼                      ▼          │
│  ┌─────────────────────────────────────────────────────┐    │
│  │              PostgreSQL + TimescaleDB                │    │
│  │         (prices, sec_filings, insider_trades)        │    │
│  └─────────────────────────────────────────────────────┘    │
└─────────────────────────────────────────────────────────────┘
```

### Ingestion Schedules
| Data | Source | Frequency | Rate Limit |
|---|---|---|---|
| OHLCV (1min) | Massive/Polygon | Every 1 min (market hours) | Unlimited (paid tier) |
| OHLCV (1day) | Massive/Polygon | Daily close | Unlimited (paid tier) |
| SEC filings | EDGAR RSS/API | Every 15 min | 10 req/sec |
| Insider trades | EDGAR | Every 6 hours | 10 req/sec |
| Institutional holdings | EDGAR 13F | Quarterly (Feb, May, Aug, Nov) | 10 req/sec |
| Company news | Finnhub | Every 5 min | 60 req/min (free) |
| Sentiment scores | Finnhub + NLP pipeline | Every hour | Internal |
| Sector rotation | Computed from prices | Daily 6 AM UTC | Internal |
| Google Trends | pytrends | Daily | Rate limited |
| Reddit sentiment | Reddit API | Every 6 hours | Rate limited |