finance-rust

 1-- finance migration 0008: ETF stats (Phase 28).
 2--
 3-- Layers Yahoo `quoteSummary` fund metadata onto the Phase 18 SEC N-PORT
 4-- fund profile so an ETF symbol page can read as densely as a stock's. Also
 5-- adds two N-PORT-derived aggregations (sector_mix, geography_mix) that
 6-- ride alongside the existing asset_mix on `fund_profiles`, and a curated
 7-- benchmark column on `symbols` for the relative-performance overlay.
 8--
 9-- Phase 26's stocks-only dividends path is lifted to ETFs in the same
10-- phase, but that is a code-level filter change and needs no migration:
11-- the `dividends` table already keys on (ticker, ex_date) for any ticker.
12
13-- When the Yahoo `fund_metadata` scheduler section last refreshed this
14-- ETF. NULL = never swept. ETFs only; stocks, indexes and futures keep
15-- NULL forever.
16ALTER TABLE symbols ADD COLUMN fund_metadata_synced_at INTEGER;
17
18-- Curated benchmark index (a symbol such as `^SPX` / `^IXIC` / `^DJI` /
19-- `^RUT`) that the fund aims to track. Hand-curated in
20-- `universe/starter.csv` for the broad-market ETFs; a user-added ETF
21-- simply omits it and the symbol page hides the relative-performance
22-- overlay. NULL on every non-ETF row.
23ALTER TABLE symbols ADD COLUMN benchmark TEXT;
24
25-- N-PORT sector + geography exposure aggregated from each holding's
26-- `industryCode` / issuer country at parse time. JSON of the same shape
27-- as the existing `asset_mix` column: [[bucket, percent], ...] ordered
28-- largest first, top ~10 buckets kept. NULL for a commodity-trust ETF
29-- (GLD, SLV) that files no N-PORT, the same as `asset_mix` already is.
30ALTER TABLE fund_profiles ADD COLUMN sector_mix TEXT;
31ALTER TABLE fund_profiles ADD COLUMN geography_mix TEXT;
32
33-- Yahoo `quoteSummary` fund metadata: the slow-moving figures the
34-- prospectus carries that N-PORT does not (expense ratio, yield,
35-- inception, category, fund family, the issuer's strategy paragraph),
36-- plus NAV which drifts intraday. One row per ETF, replaced wholesale by
37-- the `fund_metadata` scheduler section on each refresh. Kept separate
38-- from `fund_profiles` because the two carry different sources and
39-- staleness cadences (SEC quarterly vs Yahoo monthly).
40CREATE TABLE fund_metadata (
41    ticker             TEXT PRIMARY KEY REFERENCES symbols(ticker) ON DELETE CASCADE,
42    expense_ratio      REAL,      -- annualReportExpenseRatio, decimal (e.g. 0.0003 = 0.03%)
43    yield_pct          REAL,      -- summaryDetail.yield, decimal (e.g. 0.013 = 1.30%)
44    trailing_yield_pct REAL,      -- summaryDetail.trailingAnnualDividendYield, decimal
45    nav_price          REAL,      -- price.navPrice or fundProfile fallback, USD
46    inception_date     TEXT,      -- YYYY-MM-DD; first trade date / fund start
47    category           TEXT,      -- fundProfile.categoryName, e.g. "Large Blend"
48    fund_family        TEXT,      -- fundProfile.family, e.g. "Vanguard"
49    strategy_summary   TEXT,      -- assetProfile.longBusinessSummary (full paragraph)
50    updated_at         INTEGER NOT NULL
51);