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 0009: top picks snapshots (Phase 30).
2--
3-- A new `picks` table records, for each (snapshot_date, horizon), the five
4-- stocks the app's forecast-horizon picker named at end-of-day. Snapshotted by
5-- the scheduler right after `daily_close` runs (a known once-per-day moment
6-- when every symbol carries a fresh close), one row per pick.
7--
8-- The snapshot is what makes the `/backtest` page honest: without it the
9-- backtest could only replay today's algo over old data, and every algo tweak
10-- would silently rewrite history. With it, the picks the app actually made on
11-- every past trading day are immutable; the backtest reads them back and
12-- simulates following them. v1 grows the table forward from the first deploy
13-- — no retroactive backfill — so the backtest is honest about "history since".
14--
15-- `score` is the per-horizon ranker's raw value (higher is better); kept for
16-- debugging and for the backtest's stat table. `price_at_pick` is the close
17-- the pick was named at, so the backtest can compute the per-pick return at
18-- the next snapshot without re-reading `daily_prices`.
19
20CREATE TABLE picks (
21 snapshot_date TEXT NOT NULL, -- YYYY-MM-DD ET trading date
22 horizon TEXT NOT NULL, -- day | week | month | year
23 rank INTEGER NOT NULL, -- 1..5
24 ticker TEXT NOT NULL REFERENCES symbols(ticker) ON DELETE CASCADE,
25 score REAL NOT NULL, -- ranker's raw score, higher is better
26 price_at_pick REAL NOT NULL, -- close used as the entry price
27 PRIMARY KEY (snapshot_date, horizon, rank)
28);
29
30CREATE INDEX picks_ticker ON picks(ticker);
31CREATE INDEX picks_date ON picks(snapshot_date);