1# FinData OS — STATUS (updated 2026-11-01, build session)23Legend: ✅ done and verified · 🟡 done, not yet verified in production · ⛔ blocked · ⬜ not started45## Phase 0 — Discover ✅6- ✅ Cluster/SSD inventory, node choice M3U96a with rationale (`docs/DISCOVERY.md` §1)7- ✅ Vendor identified (FirstRate Data), auth model, capability matrix, license summary, sanitized probes (`docs/evidence/phase0-probes.md`)8- ✅ Minimal authorized test requests (last_update ×7 types, ticker listings, one 15.8 MB daily 1-min archive, splits/dividends, fundamentals JSON)9- ✅ Capacity model and disk budget (≤ 250 GB curated, 60 GB hard stop)1011## Phase 1 — Foundation12- ✅ Monorepo (uv workspace, 8 packages), migrations (`findata migrate`), secrets in `.env` only, redaction filter13- ✅ Authentication: owner password (argon2) + TOTP MFA, sessions, CSRF, scoped API keys; tests green14- 🟡 Deployment via `mld` to M3U96a, TLS via MacLustr Tunnel, health checks — see "Production" below15- ✅ CI-equivalent: `make test` (37 tests), `make lint` (ruff clean), `pip-audit` (no known vulnerabilities, 2026-11-01)1617## Phase 2 — Market pipeline (US stocks, 1-minute)18- ✅ Connector (discover/capabilities/fetch_range/normalize/validate/checkpoint), immutable raw archives, delta Parquet per session, atomic versioned publication, supersede without duplicates (tested), 5-minute derivation (tested vs manual aggregation and DST), coverage + quality anomalies19- 🟡 Real incremental run on the node (see Production)2021## Phase 3 — Fundamentals22- ✅ FirstRate fundamentals connector (verified live on AAPL during discovery), long-format PIT facts, `available_at` policy, revision/restatement tracking, as-of query hides later filings and later-observed restatements (tested), earnings events23- ⬜ SEC EDGAR connector: parsing implemented + fixture-tested; **pending configuration** (`SEC_EDGAR_USER_AGENT`)24- ⚠️ Limitation: vendor gives no revision history → restatements before first ingestion are unknown2526## Phase 4 — Platform27- ✅ REST API `/api/v1` (38+ routes, OpenAPI), cursor pagination, Arrow/CSV/JSON, async Parquet exports, sandboxed SQL, coverage/quality/health/admin, Prometheus metrics28- ✅ Python SDK (bars via Arrow pages, exports, fundamentals, SQL) — tested against a live uvicorn thread29- 🟡 Web console (Next.js static export) — built by a sub-agent; see Production for verification3031## Phase 5 — Scale32- 🟡 Representative multi-year backfill (bounded letter range) — to run on the node33- 🟡 Backup + restore drill — to run on the node34- ⬜ Load benchmark3536## Phase 6 — AI and polish37- ✅ Assistant endpoint implemented (Anthropic SDK, model proposes SQL only; FinData validates/executes/cites versions) — **pending configuration** until `ANTHROPIC_API_KEY` is set and a capability/cost test is recorded here; OpenAI provider not implemented38- ⬜ Security review pass on production3940## Production evidence (2026-11-01, all outputs real)4142**Deployment**: `mld deploy findata --node M3U96a` → PM2 `findata-api` (0.0.0.0:8600 behind a source-network guard) + `findata-worker`; Caddy route `https://findata.spboucher.ai → 10.67.0.10:8600`; node dir adopted as git clone (`mld adopt`, HEAD = origin/main). Public checks: `/api/v1/health` 200, console `/` 200, `/login/` 200, `/api/docs` 200; headers HSTS, CSP `default-src 'self'`, `X-Frame-Options: DENY`, `X-Request-ID`.43Gotcha fixed during deploy: the API must not bind 127.0.0.1 only (Caddy reaches the node over WireGuard), and `--proxy-headers` must be off so the guard sees the transport peer (X-Forwarded-For is read only when the peer is the 10.67.0.1 gateway).4445**Owner login**: created (`findata create-owner`), credentials in the laptop secrets folder; `POST /auth/login` 200 → `/auth/me` owner; wrong password 401; POST without CSRF 403; logout 200. TOTP not yet enrolled (owner action in Settings).4647**Console (headless Chromium against production)**: redirect to `/login/`, login, Overview rendered at 375 px (scrollWidth 375, no overflow) and 1280 px dark; 0 console/page errors; license banner present; Bars and Datasets pages render (datasets show `unsupported`/`pending_configuration` statuses).4849**Real ingestion runs (worker logs / job results)**50| Job | Result |51|---|---|52| metadata | 16 313 stock + 5 100 ETF listings → 21 243 instruments (13 209 active, 8 069 `-DELISTED`); 6 131 split rows, 243 209 dividend rows |53| incremental stock day 1min UNADJUSTED | vendor last_update 2026-10-30; archive 15 759 934 B, 6 515 files; 1 337 408 bars published (session 2026-10-30), version 1 |54| derive 5m (session) | 412 938 bars |55| fundamentals AAPL,MSFT,NVDA,JPM,XOM | 24 790 facts (quarterly + annual), earnings events; 7 s |56| backfill Z (period=full, ticker_range=Z, 1min UNADJUSTED) | 57 symbols, 24 815 063 rows, 471 base files, 67 s; 10 570 rows skipped because 2026-10-30 already lives in the delta file (no duplicates); 1 556 anomalies flagged (price_jump 1 480, outside_session 130 — flagged, not altered) |57| lake after backfill | `market_bars.stock.1m.raw` v58: 472 files, **26 152 471 rows**, 331.5 MB; sessions 2000-01-04 → 2026-10-30; 6 521 symbols (6 515 one session + 57 Z full history) |58| backup #1 | pg_dump 3.0 MB zstd + rsync to M4M64a, remote file count verified (ran before the backfill; a second backup follows the compaction) |5960Lake: 506.9 GB free, curated 0.54 GB, raw 0.02 GB (full Z archive deleted after publication, sha256 kept).6162**SDK against production** (`findata_sdk`, API key scoped read:market/read:fundamentals/query/export): health ok (SMART Verified, worker running); AAPL 1m 900 rows (first bar 08:00Z = 04:00 ET open, close 351.6499) in 0.30 s; 5m 192 rows with volume conserved; `split_div` adjustment applied at query time; fundamentals as_of 2025-01-01 → 156 rows, 536 hidden (latest period 2024-09-28 filed 2024-11-01) vs now → period 2026-06-27; SQL top-volume query 0.08 s; export of 5 symbols → Parquet 4 508 rows / 99 KB in 1.15 s.6364**Load benchmark (laptop → public URL over the tunnel)**65| Request | n / conc | p50 | p95 | rps |66|---|---|---|---|---|67| bars 1m one session (1 000 rows) | 80 / 8 | 232 ms | 800 ms | 28.7 |68| bars 1m 31-day span 2019 (multi-year base file) | 20 / 4 | 1 572 ms | 1 687 ms | 2.9 |69| bars 5m 180-day span | 20 / 4 | 2 214 ms | 2 957 ms | 1.7 |70| SQL per-year aggregate on one symbol (26 M-row lake) | 6 / 2 | 1 567 ms | 1 786 ms | 1.3 |71| SQL count(*) over the whole lake | 3 / 1 | 985 ms | — | — |72| catalog (Postgres) | 100 / 16 | 101 ms | 209 ms | 135 |73Interpretation: file selection is by `lake_files` date range, so a 31-day 1m request scans the symbol's yearly base file (≈ 100 k rows) then paginates — acceptable; compaction of the 5m deltas (6 748 session files from the Z backfill) into base files was queued to fix the derived dataset's file count.7475## Final checks (2026-11-01 evening, real outputs)7677- **Derived 5m compaction**: the invariant check caught a real defect — the first backfill-driven derivation had written a Z-only delta for 2026-10-30 next to the full-market delta (94 duplicate (instrument, ts) for ticker Z). The compaction job refused to publish (`compaction invariant violated`), the duplicated file was retired by a versioned publish (v3, note recorded), `derive` was fixed to write per-symbol base files for backfills, and the compaction then succeeded: 6 747 delta files → 6 935 base files, 7 470 593 rows, 198 MB, 29 s (v4). No duplicates reached readers at any version.78- **Backup #2** (after the backfill): pg_dump 5.9 MB zstd; rsync 14 627 files / 567.7 MB to `M4M64a:~/backups/findata`; remote curated file count 7 236 = local 7 236 → `verified: true`; 6.8 s.79- **Restore drill** (`findata restore-drill`): latest dump restored into a scratch database (472 live `lake_files` rows for the 1m dataset), dataset `market_bars.stock.1m.raw` copied back from M4M64a, **472/472 Parquet files SHA-256-verified against manifest v58, 0 missing**, scratch database dropped. Measured RTO for metadata + one dataset: < 2 min on LAN.80- **Quality report** (1m): 26 152 471 bars, 6 521 symbols, 111 956 symbol-sessions, 2000-01-04 → 2026-10-30; anomalies (30 d): price_jump 1 480 (warning), outside_session 130 (warning); mean regular-hours completeness 0.58 (honest: many micro-caps trade a fraction of the 391 regular minutes; vendor omits zero-volume bars).81- **Storage**: curated 653 MB (1m 331.5 MB in 472 files; 5m 198 MB in 6 935 files), raw 17 MB (incremental archives kept; the 258 MB full Z archive deleted after publication, sha256 kept), `_retired` 209 MB (GC after 7 days), manifests 7.4 MB. Free 506.5 GB.82- **Security scan on the node**: vendor id absent from PM2 logs, PM2 dump, app directory (except `.env` 600), lake manifests/raw names, and Postgres (`raw_archives`, jobs, audit, checkpoints); API listens `*:8600` behind the source guard, Postgres loopback only; session cookie `HttpOnly; Secure; SameSite=strict; Max-Age=43200`. A restore-drill traceback had echoed the DB password to the operator terminal → fixed (credentials via environment, redacted errors) and **the Postgres password was rotated** (`.env` on node + laptop secrets copy).83- **Dependency audit**: `pip-audit` on the exported lock → no known vulnerabilities (2026-11-01).8485## Acceptance tests86| # | Test | Result |87|---|---|---|88| 1 | Private login at https://findata.spboucher.ai on desktop and mobile | ✅ headless Chromium 375 px / 1280 px, 0 console errors; owner login verified (TOTP to enrol by owner) |89| 2 | Licensed symbol/date range fetched, validated, stored, queried, re-fetched without duplication | ✅ stock 1m session 2026-10-30 (6 515 symbols): re-run skipped by vendor date, forced re-fetch recognised by sha256; supersede path tested; Z backfill excluded the session already live (10 570 rows) |90| 3 | 1m and aggregated 5m match fixture expectations incl. session/DST | ✅ unit tests (open/high/low/close/volume/trade_count, wall-clock buckets across 2026-03-08 and 2026-11-01 DST, volume conservation on production AAPL) |91| 4 | Fundamentals as_of hides later filings/restatements | ✅ test + production: AAPL as_of 2025-01-01 → latest period 2024-09-28 (filed 2024-11-01), 536 facts hidden |92| 5 | SDK retrieves a bounded sample and a larger Parquet export | ✅ 900 bars in 0.30 s; 5-symbol export 4 508 rows Parquet in 1.15 s |93| 6 | Interrupted ingestion resumes from durable checkpoints | ✅ test (backfill checkpoint `done_symbols`); production: killed compaction requeued, orphan GC added; jobs/checkpoints in Postgres survive restarts |94| 7 | Unauthorized access fails; vendor secrets never in bundles or logs | ✅ 401/403 paths tested and verified in production; web bundle, logs, DB, manifests scanned |95| 8 | Backup restoration tested; monitoring reports SSD health and freshness | ✅ restore drill 472/472 hashes; `/metrics` + health: SMART Verified, vendor lag 0 d, backup age |96| 9 | Licensing and unsupported datasets surfaced in UI and docs | ✅ license banner, dataset statuses `unsupported` (ticks) / `pending_configuration` (SEC EDGAR) / `planned` |97| 10 | Final operator report | ✅ this file + `docs/DISCOVERY.md`, `docs/OPERATIONS.md` |9899## Full FirstRate download (started 2026-11-01 ~19:55 ET)100- Owner request: download the entire licensed catalog, using the internal SSD and the external 4 TB SSD (`/Volumes/ssd`, empty, APFS, 1.9 GB/s write). Owner approved moving `UQO_RESEARCH_DB/data` (32 GB) and `backups/autoscholar` (19 GB) to the external SSD (done, symlinks left; internal free 507 → 560 GB) and declined deleting the retired `rareindex` Postgres DB (179 GB), AI model caches (59 GB) and old git copies (21 GB); production apps stay on M3U96a.101- Architecture change: two lake roots (hot internal / cold external), 57 new dataset rows (migration 0002), parsers for index (no volume), futures contracts, option chains; `options` task; 4 bulk workers in parallel; scheduler extended (daily index/FX/crypto/ETF 1m + 1d, weekly futures/contracts/adjusted `full_update`/delisted week, monthly options). Tests: 44 green.102- Plan enqueued: 798 jobs (4 running at launch: stock 1min UNADJUSTED letters A–D, ≈ 4 GB zip each) + fundamentals for 13 209 active stocks. Representative sizes measured by HEAD: stock A 1min 3.97 GB (UNADJUSTED) / 4.15 GB (adj_splitdiv), ETF S 1min 1.2 GB, futures continuous 1min 2.4 GB per adjustment, index 1min 1.9 GB, FX 1min 4.5 GB, crypto 1min 2.3 GB, options quarter ≈ 2.3 GB (67 quarters), delisted archive 3.9 GB. Expected: several hundred GB of raw zips on the cold root and ≈ 1 TB of Parquet; several hours to a day of processing.103- Launch incident (fixed): the first jobs failed with `InterruptedError` on `mkdir /Volumes/ssd/…` — macOS TCC blocks external-volume access for PM2-launched processes (node and python have no Full Disk Access; TCC.db is SIP read-only). Fix: all PM2 processes now run through `infra/scripts/run-under-sshd.sh` (sshd has Full Disk Access), Spotlight indexing disabled on the external SSD, EINTR-safe mkdir, graceful SIGHUP. Verified 20:13 ET: four archives downloading to `/Volumes/ssd/findata/raw` at ≈ 80 MB/s aggregate, first letter (Q) converting (1.0 M rows / 4 symbols after 23 s).104- Evidence of completion will be appended below as jobs finish (`findata status`, console Storage/Ingestion).105106## Known limitations / exact next steps1071. **TOTP**: enrol the owner's authenticator in Settings → Security (one click + code). Until then login is password-only.1082. **Coverage**: production holds one full-market session (2026-10-30) + full history for the 57 Z tickers. Extend with `findata ingest backfill <letters>` (≈ 258 MB zip and 67 s per small letter; large letters such as A/S take several GB and tens of minutes; budget guard stops below 60 GB free). Daily incremental runs automatically from 02:00 ET.1093. **Fundamentals universe**: 5 tickers loaded; set tracked tickers in Settings (or `findata ingest fundamentals T1,T2,…`); the 05:00 ET job currently ingests the first 50 active stocks unless tracked tickers are configured (wire `fundamentals_tracked_tickers` into the scheduler — small follow-up).1104. **SEC EDGAR**: set `SEC_EDGAR_USER_AGENT="Name email"` in `.env` to activate the independent source (parser + PIT already implemented and tested).1115. **AI assistant**: set `ANTHROPIC_API_KEY` (server-side) then run a capability/cost test (`POST /api/v1/assistant`, 5–10 questions) and record tokens/latency here before enabling for research use; OpenAI not implemented.1126. **Bars endpoint latency** on multi-year base files (≈1.5 s for a 31-day 1m window): acceptable now; if needed, add month-level partitioning or a symbol/year row-group index.1137. **Encryption at rest**: FileVault is off by cluster policy (unattended reboots); enable on M3U96a + M4M64a if required.1148. **Fundamentals revision history** before first ingestion is unknowable from the vendor; EDGAR filing-level history fixes this once configured.1159. Daily scheduler, weekly compaction and backups are in place; verify the first automatic cycle on 2026-11-03 (Monday) in Ingestion → jobs.116