Single-binary self-hosted market watcher for stocks, ETFs, indexes, and futures: live charts, key stats, fundamentals, SEC filings, and SSE streaming.
axumdockerfinancerustself-hostedsqlitestocksvite
1-- finance migration 0005: ETF fund profiles (Phase 18).
2--
3-- An ETF files with the SEC as a registered fund, not an operating company,
4-- so its portfolio comes from quarterly N-PORT filings rather than the XBRL
5-- companyfacts that back the stock fundamentals. These two tables hold the
6-- parsed snapshot of an ETF's latest N-PORT; a physical-commodity grantor
7-- trust (GLD, SLV) files 10-Ks instead and gets the degenerate `kind` below.
8
9-- The fund's SEC series id, e.g. S000002839. One registrant CIK can host many
10-- fund series (the Vanguard and iShares trusts host dozens), so the series id
11-- is what pins an N-PORT lookup to a single ETF. NULL for a single-fund trust
12-- (SPY, DIA) and for every non-ETF symbol.
13ALTER TABLE symbols ADD COLUMN series_id TEXT;
14-- When this ETF's fund profile was last refreshed from SEC. NULL = never.
15ALTER TABLE symbols ADD COLUMN fund_synced_at INTEGER;
16
17-- One profile row per ETF: the headline figures from its latest N-PORT, or,
18-- for a commodity trust, the AUM from its 10-K companyfacts.
19CREATE TABLE fund_profiles (
20 ticker TEXT PRIMARY KEY REFERENCES symbols(ticker) ON DELETE CASCADE,
21 kind TEXT NOT NULL, -- 'portfolio' | 'commodity_trust'
22 net_assets REAL, -- total net assets (AUM), USD
23 total_assets REAL, -- gross assets, USD
24 holdings_count INTEGER, -- positions in the full portfolio
25 report_date TEXT, -- N-PORT "as of" date, YYYY-MM-DD
26 asset_mix TEXT, -- JSON [[bucket, percent], ...]; portfolio funds only
27 updated_at INTEGER NOT NULL
28);
29
30-- The largest holdings of a portfolio fund, ranked by weight. Only the top
31-- slice is kept (a bond aggregate fund holds thousands of positions); rank 1
32-- is the largest. Replaced wholesale on each refresh.
33CREATE TABLE fund_holdings (
34 ticker TEXT NOT NULL REFERENCES symbols(ticker) ON DELETE CASCADE,
35 rank INTEGER NOT NULL, -- 1 = largest weight
36 name TEXT NOT NULL,
37 pct REAL, -- percent of net assets, e.g. 8.4
38 value_usd REAL,
39 PRIMARY KEY (ticker, rank)
40);