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-- Phase 7: rekey the fundamentals uniqueness constraint.
2--
3-- 0001 keyed `fundamentals` UNIQUE on (ticker, metric, period_end). That breaks
4-- once both annual and quarterly figures are stored: a full-year revenue and a
5-- fourth-quarter revenue can share the same period_end (the fiscal year end),
6-- so one would overwrite the other. The fiscal `period` label ('FY2024' vs
7-- 'Q4-2024') is the value that is genuinely unique per figure, so the key moves
8-- there.
9--
10-- SQLite cannot alter a table constraint in place, and `fundamentals` is still
11-- empty (Phase 7 introduces its first writer), so the table is simply recreated.
12
13DROP TABLE IF EXISTS fundamentals;
14
15CREATE TABLE fundamentals (
16 id INTEGER PRIMARY KEY AUTOINCREMENT,
17 ticker TEXT NOT NULL REFERENCES symbols(ticker) ON DELETE CASCADE,
18 metric TEXT NOT NULL, -- revenue | net_income | eps_diluted | shares_diluted
19 -- | dividends_per_share | assets | liabilities | equity
20 -- | assets_current | liabilities_current
21 period TEXT NOT NULL, -- 'FY2024' or 'Q3-2024'
22 fiscal_year INTEGER NOT NULL,
23 fiscal_qtr INTEGER, -- NULL for a full-year figure
24 period_end TEXT NOT NULL, -- YYYY-MM-DD
25 value REAL NOT NULL,
26 unit TEXT, -- USD | USD/shares | shares
27 form TEXT, -- 10-K | 10-Q
28 filed_at TEXT, -- YYYY-MM-DD
29 UNIQUE (ticker, metric, period)
30);
31CREATE INDEX fundamentals_ticker_metric ON fundamentals(ticker, metric, period_end DESC);