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: market-watching schema.
2-- All *_at columns are UTC epoch-milliseconds (see db::now_ms).
3-- Trading dates ("d") are TEXT YYYY-MM-DD: calendar days, not instants.
4
5-- ── symbols: the watched universe (stocks, ETFs, indexes) ──
6CREATE TABLE symbols (
7 ticker TEXT PRIMARY KEY, -- uppercase, e.g. AAPL, ^SPX
8 name TEXT NOT NULL DEFAULT '',
9 kind TEXT NOT NULL DEFAULT 'stock', -- stock | etf | index
10 exchange TEXT,
11 currency TEXT NOT NULL DEFAULT 'USD',
12 cik TEXT, -- 10-digit zero-padded SEC CIK; NULL for ETF/index
13 sector TEXT,
14 industry TEXT,
15 is_seeded INTEGER NOT NULL DEFAULT 0, -- member of the curated starter list
16 is_watched INTEGER NOT NULL DEFAULT 0, -- in at least one watchlist (denormalized)
17 history_synced_at INTEGER,
18 history_first_date TEXT,
19 history_last_date TEXT,
20 fundamentals_synced_at INTEGER,
21 filings_synced_at INTEGER,
22 -- denormalized latest snapshot for fast list rendering and SSE seeding
23 last_price REAL,
24 prev_close REAL,
25 last_quote_at INTEGER,
26 created_at INTEGER NOT NULL,
27 updated_at INTEGER NOT NULL
28);
29CREATE INDEX symbols_kind ON symbols(kind);
30CREATE INDEX symbols_is_watched ON symbols(is_watched);
31CREATE INDEX symbols_name ON symbols(name);
32
33-- ── daily_prices: deep historical OHLCV from Stooq. Permanent, never pruned. ──
34CREATE TABLE daily_prices (
35 ticker TEXT NOT NULL REFERENCES symbols(ticker) ON DELETE CASCADE,
36 d TEXT NOT NULL, -- YYYY-MM-DD trading date
37 open REAL NOT NULL,
38 high REAL NOT NULL,
39 low REAL NOT NULL,
40 close REAL NOT NULL,
41 volume INTEGER NOT NULL DEFAULT 0,
42 PRIMARY KEY (ticker, d)
43);
44CREATE INDEX daily_prices_d ON daily_prices(d);
45
46-- ── intraday_bars: today's ~15-min-delayed bars from Yahoo. Pruned to recent days. ──
47CREATE TABLE intraday_bars (
48 ticker TEXT NOT NULL REFERENCES symbols(ticker) ON DELETE CASCADE,
49 ts INTEGER NOT NULL, -- bar start, UTC epoch-ms
50 open REAL NOT NULL,
51 high REAL NOT NULL,
52 low REAL NOT NULL,
53 close REAL NOT NULL,
54 volume INTEGER NOT NULL DEFAULT 0,
55 PRIMARY KEY (ticker, ts)
56);
57CREATE INDEX intraday_bars_ts ON intraday_bars(ts);
58
59-- ── quotes: latest live quote snapshot per symbol (one row per ticker, upserted) ──
60CREATE TABLE quotes (
61 ticker TEXT PRIMARY KEY REFERENCES symbols(ticker) ON DELETE CASCADE,
62 price REAL NOT NULL,
63 prev_close REAL,
64 open REAL,
65 day_high REAL,
66 day_low REAL,
67 volume INTEGER,
68 market_state TEXT, -- PRE | REGULAR | POST | CLOSED (source-reported)
69 source TEXT NOT NULL DEFAULT 'yahoo',
70 source_time INTEGER, -- the source's own timestamp (epoch-ms)
71 fetched_at INTEGER NOT NULL
72);
73
74-- ── fundamentals: one row per (ticker, metric, fiscal period) from SEC XBRL facts ──
75-- Long/narrow so new XBRL concepts need no schema change. Stocks only.
76CREATE TABLE fundamentals (
77 id INTEGER PRIMARY KEY AUTOINCREMENT,
78 ticker TEXT NOT NULL REFERENCES symbols(ticker) ON DELETE CASCADE,
79 metric TEXT NOT NULL, -- revenue | net_income | eps_diluted | shares_diluted
80 -- | dividends_per_share | assets | liabilities | equity
81 period TEXT NOT NULL, -- 'FY2024' or 'Q3-2024'
82 fiscal_year INTEGER NOT NULL,
83 fiscal_qtr INTEGER, -- NULL for a full-year figure
84 period_end TEXT NOT NULL, -- YYYY-MM-DD
85 value REAL NOT NULL,
86 unit TEXT, -- USD | USD/shares | shares
87 form TEXT, -- 10-K | 10-Q
88 filed_at TEXT, -- YYYY-MM-DD
89 UNIQUE (ticker, metric, period_end)
90);
91CREATE INDEX fundamentals_ticker_metric ON fundamentals(ticker, metric, period_end DESC);
92
93-- ── filings: SEC filing history (10-K / 10-Q / 8-K and friends) ──
94CREATE TABLE filings (
95 id INTEGER PRIMARY KEY AUTOINCREMENT,
96 ticker TEXT NOT NULL REFERENCES symbols(ticker) ON DELETE CASCADE,
97 accession TEXT NOT NULL,
98 form TEXT NOT NULL,
99 filed_at TEXT NOT NULL, -- YYYY-MM-DD
100 period_of_report TEXT, -- YYYY-MM-DD
101 primary_doc TEXT,
102 url TEXT NOT NULL, -- full EDGAR filing-index URL
103 description TEXT,
104 UNIQUE (ticker, accession)
105);
106CREATE INDEX filings_ticker_filed ON filings(ticker, filed_at DESC);
107
108-- ── watchlists ──
109CREATE TABLE watchlists (
110 id INTEGER PRIMARY KEY AUTOINCREMENT,
111 name TEXT NOT NULL,
112 slug TEXT NOT NULL UNIQUE, -- URL-safe, e.g. tech-megacaps
113 position INTEGER NOT NULL DEFAULT 0,
114 created_at INTEGER NOT NULL,
115 updated_at INTEGER NOT NULL
116);
117
118CREATE TABLE watchlist_items (
119 watchlist_id INTEGER NOT NULL REFERENCES watchlists(id) ON DELETE CASCADE,
120 ticker TEXT NOT NULL REFERENCES symbols(ticker) ON DELETE CASCADE,
121 position INTEGER NOT NULL DEFAULT 0,
122 added_at INTEGER NOT NULL,
123 PRIMARY KEY (watchlist_id, ticker)
124);
125CREATE INDEX watchlist_items_ticker ON watchlist_items(ticker);
126
127-- ── fetch_log: append-only history of every background fetch. Drives the data-status UI. ──
128CREATE TABLE fetch_log (
129 id INTEGER PRIMARY KEY AUTOINCREMENT,
130 job TEXT NOT NULL, -- seed | history | intraday | fundamentals | filings | prune
131 provider TEXT NOT NULL, -- stooq | yahoo | sec | -
132 ticker TEXT, -- NULL for bulk jobs
133 status TEXT NOT NULL, -- ok | error | skipped
134 detail TEXT,
135 rows INTEGER,
136 duration_ms INTEGER,
137 started_at INTEGER NOT NULL,
138 finished_at INTEGER NOT NULL
139);
140CREATE INDEX fetch_log_started ON fetch_log(started_at DESC);
141CREATE INDEX fetch_log_job ON fetch_log(job, started_at DESC);
142
143-- ── data_status: one row per job, current state, for the live status pill ──
144CREATE TABLE data_status (
145 job TEXT PRIMARY KEY, -- seed | history | intraday | fundamentals | filings
146 state TEXT NOT NULL, -- idle | fetching | ok | stale | error
147 last_ok_at INTEGER,
148 last_error TEXT,
149 last_error_at INTEGER,
150 next_run_at INTEGER,
151 updated_at INTEGER NOT NULL
152);
153
154-- ── meta: one-off key-value settings (seed_completed flag, etc.) ──
155CREATE TABLE meta (
156 key TEXT PRIMARY KEY,
157 value TEXT NOT NULL
158);