471 lines
18 KiB
Markdown
471 lines
18 KiB
Markdown
|
# 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 |
|