# 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`)
| Path | Content | Mutability |
|---|---|---|
| `raw/<source>/<dataset>/<year>/*.zip` | vendor archives as received; sha256 + sanitized request in `raw_archives` | immutable (full archives deleted after publication, hash kept) |
| `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 |
| `curated/<dataset>/base/symbol=S/year=Y/part-*.parquet` | compacted / backfilled history | replaced per (symbol, year) |
| `manifests/<dataset>/vN.json` | file list + sha256 per version (snapshot id = sha256 of the list) | append-only |
| `_retired/` | files removed from the live set, kept 7 days so older snapshots remain reproducible | GC |
| `exports/` | export results, expire after 72 h | GC |

**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 binds to 127.0.0.1 (reached through the node's wg1 proxy by Caddy on BHS64). 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.