# FinData OS — STATUS (updated 2026-11-01, build session)

Legend: ✅ done and verified · 🟡 done, not yet verified in production · ⛔ blocked · ⬜ not started

## Phase 0 — Discover ✅
- ✅ Cluster/SSD inventory, node choice M3U96a with rationale (`docs/DISCOVERY.md` §1)
- ✅ Vendor identified (FirstRate Data), auth model, capability matrix, license summary, sanitized probes (`docs/evidence/phase0-probes.md`)
- ✅ Minimal authorized test requests (last_update ×7 types, ticker listings, one 15.8 MB daily 1-min archive, splits/dividends, fundamentals JSON)
- ✅ Capacity model and disk budget (≤ 250 GB curated, 60 GB hard stop)

## Phase 1 — Foundation
- ✅ Monorepo (uv workspace, 8 packages), migrations (`findata migrate`), secrets in `.env` only, redaction filter
- ✅ Authentication: owner password (argon2) + TOTP MFA, sessions, CSRF, scoped API keys; tests green
- 🟡 Deployment via `mld` to M3U96a, TLS via MacLustr Tunnel, health checks — see "Production" below
- ✅ CI-equivalent: `make test` (37 tests), `make lint` (ruff clean), `pip-audit` (no known vulnerabilities, 2026-11-01)

## Phase 2 — Market pipeline (US stocks, 1-minute)
- ✅ 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 anomalies
- 🟡 Real incremental run on the node (see Production)

## Phase 3 — Fundamentals
- ✅ 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 events
- ⬜ SEC EDGAR connector: parsing implemented + fixture-tested; **pending configuration** (`SEC_EDGAR_USER_AGENT`)
- ⚠️ Limitation: vendor gives no revision history → restatements before first ingestion are unknown

## Phase 4 — Platform
- ✅ REST API `/api/v1` (38+ routes, OpenAPI), cursor pagination, Arrow/CSV/JSON, async Parquet exports, sandboxed SQL, coverage/quality/health/admin, Prometheus metrics
- ✅ Python SDK (bars via Arrow pages, exports, fundamentals, SQL) — tested against a live uvicorn thread
- 🟡 Web console (Next.js static export) — built by a sub-agent; see Production for verification

## Phase 5 — Scale
- 🟡 Representative multi-year backfill (bounded letter range) — to run on the node
- 🟡 Backup + restore drill — to run on the node
- ⬜ Load benchmark

## Phase 6 — AI and polish
- ✅ 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 implemented
- ⬜ Security review pass on production

## Production evidence (2026-11-01, all outputs real)

**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`.
Gotcha 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).

**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).

**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).

**Real ingestion runs (worker logs / job results)**
| Job | Result |
|---|---|
| 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 |
| 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 |
| derive 5m (session) | 412 938 bars |
| fundamentals AAPL,MSFT,NVDA,JPM,XOM | 24 790 facts (quarterly + annual), earnings events; 7 s |
| 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) |
| 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) |
| 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) |

Lake: 506.9 GB free, curated 0.54 GB, raw 0.02 GB (full Z archive deleted after publication, sha256 kept).

**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.

**Load benchmark (laptop → public URL over the tunnel)**
| Request | n / conc | p50 | p95 | rps |
|---|---|---|---|---|
| bars 1m one session (1 000 rows) | 80 / 8 | 232 ms | 800 ms | 28.7 |
| bars 1m 31-day span 2019 (multi-year base file) | 20 / 4 | 1 572 ms | 1 687 ms | 2.9 |
| bars 5m 180-day span | 20 / 4 | 2 214 ms | 2 957 ms | 1.7 |
| SQL per-year aggregate on one symbol (26 M-row lake) | 6 / 2 | 1 567 ms | 1 786 ms | 1.3 |
| SQL count(*) over the whole lake | 3 / 1 | 985 ms | — | — |
| catalog (Postgres) | 100 / 16 | 101 ms | 209 ms | 135 |
Interpretation: 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.

## Final checks (2026-11-01 evening, real outputs)

- **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.
- **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.
- **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.
- **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).
- **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.
- **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).
- **Dependency audit**: `pip-audit` on the exported lock → no known vulnerabilities (2026-11-01).

## Acceptance tests
| # | Test | Result |
|---|---|---|
| 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) |
| 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) |
| 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) |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 9 | Licensing and unsupported datasets surfaced in UI and docs | ✅ license banner, dataset statuses `unsupported` (ticks) / `pending_configuration` (SEC EDGAR) / `planned` |
| 10 | Final operator report | ✅ this file + `docs/DISCOVERY.md`, `docs/OPERATIONS.md` |

## Full FirstRate download (started 2026-11-01 ~19:55 ET)
- 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.
- 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.
- 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.
- 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).
- Incident 2026-11-01 23:50 → 2026-11-02 00:55 ET (**API unavailable ~1 h**): `/api/v1/health` walked the whole lake on disk (`rglob`, 400 k files on two volumes) and exceeded the 6 s probe timeout of `mld heal`, which then restarted every FinData process every 5 min; uvicorn hung in graceful shutdown behind the slow handler. Fix: lake usage now comes from the catalog (`lake_files`, `raw_archives`) and the health evaluation is cached 60 s; hung processes killed, API back (3 ms). Lesson recorded in OPERATIONS: probes must never depend on filesystem walks.
- Incident 2026-11-01 ~21:00: per-version manifests with full file lists reached 86 GB on the internal SSD during the bulk load → manifests are now compact deltas; the full list of any version is reconstructed from `lake_files` (`findata manifest <dataset> [--version]`).
- Incident 2026-11-01 ~21:20: PM2 restarts left orphan workers (ssh wrapper exited, remote kept running) → wrapper now forwards SIGTERM to the remote process and waits; leftovers from a previous incarnation are terminated on start.
- Progress 2026-11-02 00:45 ET: 187 archives done / 0 dead; stock & ETF 1-min raw A–Z complete (4.29 G + 0.9 G bars), ETF 1-day complete, index/FX/crypto/futures continuous 1-min + 1-day complete, options 61/68 quarters (≈ 3.1 G option rows so far), fundamentals 2 029 / 13 209 tickers (≈ half of "active" listings are warrants/units/CEFs/notes without statements → vendor 404, logged and skipped).
- **Completed 2026-11-02 ≈ 11:20 ET**: 798 / 798 archives succeeded (0 skipped, 0 unparseable files), plus fundamentals for every active stock. Totals (catalog): **564.7 GB curated Parquet, 38.81 G rows, 3.13 M files, 64 active datasets**. Raw archives kept on the external SSD: 806 files, 434 GB. Coverage: stocks 16 195 symbols (incl. delisted) 2000-01-03 → 2026-10-30; ETFs 5 161; indices 122 (2008→); options 11 582 underlyings 2010-01-04 → 2026-10-30 (4.23 G rows); futures 3 continuous series × 5 timeframes + individual contracts; FX, crypto. Fundamentals: 5 451 entities, 18.07 M PIT facts (periods 2010-12 → 2026-10), 373 326 earnings events; 2 658 active listings have no statements (warrants/units/CEFs/notes → vendor 404, recorded in the job checkpoint).
- Families (GB / rows): stocks 370.1 / 27.75 G · options 99.2 / 4.23 G · ETFs 57.7 / 3.70 G · futures continuous 12.6 / 1.21 G · futures contracts 10.4 / 0.90 G · FX 8.0 / 0.52 G · crypto 3.7 / 0.23 G · indices 3.2 / 0.26 G. Disks after load: internal 185 GB used (319 GB free after the coverage prune), external 895 GB used (2.98 TB free). Postgres 34 GB (coverage pruned 574 M → 113 M rows, 131 → 25 GB; `coverage` is now kept for the 7 primary datasets only).

## Query performance on the full lake (2026-11-02, laptop → public URL over the tunnel, warm)
| Request | Result |
|---|---|
| bars AAPL 1m one day | 55–124 ms (744 rows) |
| bars AAPL 1m 31 days (2024-03) | 370–530 ms (15 907 rows) |
| bars MSFT 1m 31 days (2008-10) | 0.9–1.6 s (12 918 rows) |
| bars AAPL 5m 6 months | 1.0–1.6 s (23 012 rows) |
| bars AAPL 1d 10 years | 170–260 ms (2 516 rows) |
| bars NVDA 1m 31 days with query-time split+dividend adjustment | 570–780 ms |
| bars TWTR-DELISTED 1m 2022-10 | 270–350 ms |
| SQL AAPL daily OHLC from 1m, year 2024 | 185 ms |
| SQL SPY option chain one day / one expiry | 98 ms (372 rows) · SPY full history count 254 ms (26.6 M rows) |
| SQL EURUSD 1m 2020 count · ES ratio-adjusted yearly counts | 30 ms · 52 ms |
| SQL 3 symbols split_div daily closes March 2020 | 47 ms |
| SQL cross-section: all stocks 1d on one date · all stocks 1m 09:30 bar on one date | 1.3 s (5 962 symbols) · 2.9 s (5 729 symbols) |
| export 5 symbols × full year 2024 1m → Parquet | 1.9 s (914 489 rows, 17 MB) |
| fundamentals MSFT as-of 2015 · instrument search | 21 ms · 41 ms |
How: the API never globs the lake — file lists come from the catalog (`lake_files`, indexed by dataset/date and dataset/symbol); the SQL playground/assistant extract symbol / underlying / year / date predicates from the statement and build the same explicit file list (fast path); statements without any such predicate fall back to a Hive directory scan (30–60 s to list a 170 k–320 k-file dataset) and are documented as slow.

## Public landing page (2026-11-02, owner decision)
- `/` and `/docs/` are readable without an account: catalog-level metrics from `GET /api/v1/public/overview` (rows, bytes, files, datasets, symbol counts and date ranges, options/fundamentals totals, freshness, free space, backup state, platform status). No data rows, no paths, no secrets; cached 120 s, rate-limited per IP.
- Every data route (bars, SQL, exports, fundamentals, symbols, coverage, quality) and all admin routes still require a session or an API key (FirstRate license: no redistribution of raw data). Verified anonymously in headless Chromium at 375 px and 1280 px: landing renders, `/bars/` redirects to `/login/?next=…`, `GET /market/bars` without auth → 401.

## Known limitations after the full load
- Whole-dataset SQL scans without a symbol or date predicate are slow (directory listing of up to 320 k files); use predicates, the `*_1d_*` datasets for cross-sections, or exports.
- The cold root (external SSD, ≈ 1.3 TB incl. raw archives) is not in the nightly backup (re-downloadable; catalog restore recreates the file list). The hot root + Postgres are backed up nightly (first full-catalog backup 04:04 → 04:41).
- `_retired/` holds files superseded during the load (duplicate processing after worker restarts); GC removes them after 7 days.

## Known limitations / exact next steps
1. **TOTP**: enrol the owner's authenticator in Settings → Security (one click + code). Until then login is password-only.
2. **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.
3. **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).
4. **SEC EDGAR**: set `SEC_EDGAR_USER_AGENT="Name email"` in `.env` to activate the independent source (parser + PIT already implemented and tested).
5. **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.
6. **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.
7. **Encryption at rest**: FileVault is off by cluster policy (unattended reboots); enable on M3U96a + M4M64a if required.
8. **Fundamentals revision history** before first ingestion is unknowable from the vendor; EDGAR filing-level history fixes this once configured.
9. Daily scheduler, weekly compaction and backups are in place; verify the first automatic cycle on 2026-11-03 (Monday) in Ingestion → jobs.