invest-copilot/docs/02-data-model.md

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

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

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

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)

-- 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

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

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

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

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)

{
  "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

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

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

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

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

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

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

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

-- 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

-- 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

-- 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