# FinData OS — Architecture

                           FirstRate Data (vendor)                    SEC EDGAR (pending configuration)
                     market zip archives  │  /api-f JSON fundamentals            │
                                          ▼                                      ▼
   connectors/market/firstrate     connectors/fundamentals/{firstrate,sec_edgar}
   discover · capabilities · fetch_range · normalize · validate · checkpoint
                                          │
                                          ▼
   services/ingest  ── single worker process (lake writer lock) ──────────────────────────────────────
     queue (Postgres, SKIP LOCKED, retries/backoff/jitter, dead-letter, checkpoints, resume)
     tasks: metadata · incremental · backfill · derive(5m) · compaction · fundamentals · quality · backup · gc
     lake: raw/ (immutable zips + sha256) → curated/<dataset>/{delta|base}/… Parquet ZSTD → manifests/vN.json
                                          │
                 ┌────────────────────────┴───────────────────────┐
                 ▼                                                ▼
   PostgreSQL 17 (metadata)                              DuckDB (read side, in-process, read-only)
   users/sessions/api_keys/audit · datasets/versions/   lake_files planner → read_parquet(file list)
   lake_files · instruments/aliases · corporate_actions  sandboxed SQL (allowed_directories = lake only)
   coverage · quality · jobs/runs/checkpoints · exports
   fundamentals_facts (PIT, revisions) · earnings
                 │
                 ▼
   services/api  FastAPI :8600 (127.0.0.1) — /api/v1 (OpenAPI), /metrics, static console (apps/web/out)
                 │  session cookie + CSRF (owner, TOTP MFA) · scoped API keys · rate limit · audit · request ids
                 ▼
   MacLustr Tunnel: node wg1 10.67.0.10 ⇄ BHS64 (Caddy TLS) ⇄ https://findata.spboucher.ai

# Storage layout (M3U96a, ~/findata-lake)

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.

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.

# Why these choices

• 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.
• 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.
• 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).
• Postgres-backed queue (no Redis): one writer, a few jobs per day, durability across reboots and SKIP LOCKED are all that is needed.
• 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).

# Capacity model

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

# Data flows

1. 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.
2. 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).
3. Compaction (weekly): deltas older than 35 days → merged into base files → duplicate assertion → publish.
4. Fundamentals (daily): tracked tickers → /v1/financials/all/{t}/{quarterly|annual} + earnings → compare with latest revision → insert new revision when changed (restatement_flag).
5. 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.

# Security model

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