1# FinData OS — Architecture23```4 FirstRate Data (vendor) SEC EDGAR (pending configuration)5 market zip archives │ /api-f JSON fundamentals │6 ▼ ▼7 connectors/market/firstrate connectors/fundamentals/{firstrate,sec_edgar}8 discover · capabilities · fetch_range · normalize · validate · checkpoint9 │10 ▼11 services/ingest ── single worker process (lake writer lock) ──────────────────────────────────────12 queue (Postgres, SKIP LOCKED, retries/backoff/jitter, dead-letter, checkpoints, resume)13 tasks: metadata · incremental · backfill · derive(5m) · compaction · fundamentals · quality · backup · gc14 lake: raw/ (immutable zips + sha256) → curated/<dataset>/{delta|base}/… Parquet ZSTD → manifests/vN.json15 │16 ┌────────────────────────┴───────────────────────┐17 ▼ ▼18 PostgreSQL 17 (metadata) DuckDB (read side, in-process, read-only)19 users/sessions/api_keys/audit · datasets/versions/ lake_files planner → read_parquet(file list)20 lake_files · instruments/aliases · corporate_actions sandboxed SQL (allowed_directories = lake only)21 coverage · quality · jobs/runs/checkpoints · exports22 fundamentals_facts (PIT, revisions) · earnings23 │24 ▼25 services/api FastAPI :8600 (127.0.0.1) — /api/v1 (OpenAPI), /metrics, static console (apps/web/out)26 │ session cookie + CSRF (owner, TOTP MFA) · scoped API keys · rate limit · audit · request ids27 ▼28 MacLustr Tunnel: node wg1 10.67.0.10 ⇄ BHS64 (Caddy TLS) ⇄ https://findata.spboucher.ai29```3031## Storage layout (M3U96a, `~/findata-lake`)32| Path | Content | Mutability |33|---|---|---|34| `raw/<source>/<dataset>/<year>/*.zip` | vendor archives as received; sha256 + sanitized request in `raw_archives` | immutable (full archives deleted after publication, hash kept) |35| `curated/<dataset>/delta/session_date=D/part-*.parquet` | one file per session, all symbols, sorted (symbol, ts) | replaced as a whole when the session is re-published |36| `curated/<dataset>/base/symbol=S/year=Y/part-*.parquet` | compacted / backfilled history | replaced per (symbol, year) |37| `manifests/<dataset>/vN.json` | file list + sha256 per version (snapshot id = sha256 of the list) | append-only |38| `_retired/` | files removed from the live set, kept 7 days so older snapshots remain reproducible | GC |39| `exports/` | export results, expire after 72 h | GC |4041**Invariant**: for every dataset version, each `(instrument_id, timestamp_utc)` is in exactly one live file. Enforced by: session-level supersede for deltas, delta-date exclusion in backfills, duplicate assertion in compaction, and a `duplicate` quality flag on rows inside a file.4243**Publication** is one Postgres transaction (`lake_files` add/retire, `dataset_versions` insert, `datasets.current_version`), after the Parquet files were written to `_tmp/` and atomically renamed. Readers choose files through `lake_files` (by version), so a half-written state is never visible.4445## Why these choices46- **Parquet + DuckDB + Postgres**: analytical reads over columnar files with predicate pushdown; metadata, auth and job state need transactions. DuckDB is never a multi-writer service: the API opens read-only in-memory connections, the worker writes Parquet files only.47- **Session-date delta files**: a daily update touches ~6 500 symbols; one file per session (≈12 MB) keeps the file count linear in days, not symbols × days. Monthly compaction turns deltas into per-symbol yearly files for fast single-symbol multi-year scans.48- **Adjustments at query time** from `corporate_actions` (vendor splits/dividends files): the lake stores one canonical raw series; `adjustment=split|split_div` is an explicit, reproducible transformation. Vendor-adjusted archives are therefore not duplicated on disk (they could be ingested as separate datasets if a reconciliation study needs them).49- **Postgres-backed queue** (no Redis): one writer, a few jobs per day, durability across reboots and `SKIP LOCKED` are all that is needed.50- **Fundamentals in Postgres** (long format): PIT queries are `DISTINCT ON`-style SQL with indexes on (entity, metric, period_end, available_at); volumes are modest (≈ 76 metrics × ~100 periods per entity).5152## Capacity model53See `docs/DISCOVERY.md` §5. Budget: curated ≤ 250 GB soft limit, 60 GB free hard stop (ingestion refuses to write below it). Measured: 1 session of 6 515 stocks = 66 MB CSV → ≈ 12–14 MB Parquet.5455## Data flows561. **Incremental (daily)**: scheduler → `last_update(stock)` → skip if unchanged → download `period=day` zip → raw → normalize per file → group by session → validate → one delta file per session → publish (supersede) → coverage/quality → checkpoint → enqueue `derive 5m`.572. **Backfill (operator)**: `period=full&ticker_range=L` → stream per ticker file → exclude sessions already in deltas → base files per (symbol, year) → publish per symbol (resumable via `done_symbols`).583. **Compaction (weekly)**: deltas older than 35 days → merged into base files → duplicate assertion → publish.594. **Fundamentals (daily)**: tracked tickers → `/v1/financials/all/{t}/{quarterly|annual}` + earnings → compare with latest revision → insert new revision when changed (`restatement_flag`).605. **Backup (daily)**: `pg_dump | zstd` + `rsync -a --delete` of the lake to M4M64a; remote file count verified; `last_backup` recorded. Restore drill: `findata restore-drill`.6162## Security model63Owner-only by default. Browser: HttpOnly/Secure/SameSite=Strict session cookie, CSRF double-submit header, TOTP MFA (scopes empty until the second factor passes). Programmatic: `Authorization: Bearer fd_<prefix>.<secret>` keys, HMAC-hashed at rest, scoped, expirable, revocable, per-key rate limit and daily usage accounting. Vendor credentials live only in `.env` (mode 600) on the node; every log line and job error passes through a redaction filter; the HTTP client never logs URLs. The API listens on the node interfaces behind a source-network guard (loopback, WireGuard 10.67.0.0/16, LAN) so Caddy on BHS64 reaches it over wg1. CSP `default-src 'self'`, HSTS, nosniff, frame-ancestors none. SQL playground: single read-only statement, keyword deny-list, DuckDB `allowed_directories` restricted to the lake, external access disabled, timeout and row caps. Exports are scoped to the requesting principal and expire.64