# Fundamentals (SEC EDGAR) — architecture, schema, ingestion, point-in-time

Chantier 7 of the v2 upgrade. Module hfmarketdata/api/fundamentals/ (router /v1/fundamentals), plus stream/ (WebSocket /v1/stream) and bulk/ (/v1/bulk/fundamentals/{year}.parquet). Data source: the SEC EDGAR XBRL APIs (companyfacts, submissions), never proxied live to users. Ratio formulas: fundamentals-ratios.md. Stream protocol: asyncapi.yaml.

# 1. Architecture

        EDGAR (data.sec.gov / www.sec.gov)            ≤ 10 req/s token bucket, backoff 429/503, UA = settings.sec_user_agent
                 │ companyfacts + submissions (+ MetaLinks.json of the latest 10-K, Atom feed, daily index)
                 ▼
  data_root/edgar/raw/**.json.gz          raw cache (replayable offline, one file per document)
                 │ normalize.facts_frame
                 ▼
  data_root/edgar/facts/cik={cik}/facts.parquet     RAW FACTS LAKE (DuckDB) — every fact instance, versioned by (accn, filed)
                 │ normalize.normalize_company (mapping.py priority lists, fiscal calendar, versions, derivation)
                 ▼
  SQLite (core.db):  edgar_companies · edgar_filings · fund_statements (wide, versioned) · fund_mapping ·
                     fund_mapping_log · fund_coverage · fund_ingest_state · fund_latest (screener)
                 │                                   │
                 ▼                                   ▼
  /v1/fundamentals/* (service.py, DuckDB asof join with the price lake for ratios)      Redis `filings` → /v1/stream
  data_root/bulk/fundamentals_{year}.parquet (bulk/build.py, ETag/304, quota exempt)

Why two stores:

• Parquet + DuckDB for facts — ~25 000 facts per large filer, ~200 M for the universe; columnar, cheap to rescan when the mapping changes (re-normalisation never re-downloads), view=as_reported and /facts/{concept} read it directly with predicate pushdown. Never mutated: facts are appended by filing.
• SQLite for standardized statements — needs indexes for point-in-time lookups (ticker, period_end, filed_date), small (≈ 1–2 M rows for 7 600 companies × 15 years × 3 statements × ~1.2 versions), transactional replace per company, and the screener table lives next to it. coverage JSON per row explains every null.

# 2. Schema (SQLite, fundamentals/models.py)

Raw facts lake columns: cik, taxonomy, tag, unit, fy, fp, form, start, end, val, accn, filed, frame (de-duplicated on (taxonomy, tag, unit, start, end, accn) keeping the latest filed).

# 3. Standard chart of accounts and mapping (mapping.py)

Exactly the spec's accounts — income (16), balance (19), cash flow (12) — plus two auxiliary inputs that are stored but not part of the public chart: depreciation_amortization (for ebitda) and shares_outstanding (dei cover-page shares, all classes summed, for market_cap). Computed accounts (* in the spec): ebitda, total_debt, net_debt, working_capital, free_cash_flow — never read from a tag, formula in coverage.

Each account lists its tags in priority order; the first tag with a fact for the period wins and the chosen tag + priority are written in coverage[account]. Examples (full table: /v1/fundamentals/_mapping):

Accounting identities used as fallbacks (flagged identity: true): gross_profit = revenue − cost_of_revenue, total_liabilities = LiabilitiesAndStockholdersEquity − total_equity (else total_assets − total_equity), operating_income = revenue − CostsAndExpenses. Units: USD raw (not thousands), shares, USD/share; a non-USD filer keeps its currency in currency and coverage[account].currency (20-F: ifrs-full tags are in the lists). Extension tags (aapl:, shak:…) are logged, never guessed.

# 4. Normalisation (normalize.py)

1. Facts → frame, de-dup by (tag, unit, start, end, accn) keeping the latest filed.
2. Fiscal calendar. The fy/fp fields of a companyfacts fact describe the filing, not the fact (Apple's 10-Q for Q2 FY2025 re-reports Q2 FY2024 with fy=2025, fp=Q2). So the fiscal period of every fact is derived from its end against the fiscal year ends learnt from the 10-K report dates (FiscalCalendar, ±7-day snap for 52/53-week filers, MMDD extrapolation for years without a 10-K) and fy/fp are only used as a sanity check on the filing's own period (mismatches are counted in stats.fiscal_mismatches; 0 for the three prototypes). Apple Q2 FY2024: end=2024-03-30, FY end 2024-09-28 → 182 days → quarter 4 − round(182/91.3) = 2. calendar_quarter comes from period_end (an end in the first 7 days of a month belongs to the previous month: 2025-01-03 → 2024Q4).
3. Span classification of duration facts: 75–105 days = quarter, 165–195 = 6-month YTD, 255–290 = 9-month YTD, 340–380 = fiscal year; anything else (stub periods) is ignored.
4. Resolution per filing (accession) and period → Resolved values + coverage (section 3).
5. Versioning. Filings are replayed in filed order. A period gets a new row only when a filing changes or completes what was known (restated=true when a previously served number changed; same-accession reported + derived pieces are one version). A later filing that re-reports a period with a lower-priority concept (cash incl. restricted cash in a comparative column) does not override the better concept. Unique key (cik, statement, fiscal_year, fiscal_quarter, accn).
6. Derivation (flagged derived=true, formula in coverage[account].derived): cash-flow statements in 10-Qs are year-to-date only, income statements often carry 9-month YTD facts — Q2 = YTD6 − Q1, Q3 = YTD9 − YTD6 (or YTD9 − Q1 − Q2), Q4 = FY − YTD9 or FY − (Q1+Q2+Q3); only when every input belongs to the same fiscal year, same unit, and was known at that filing date. Weighted share counts use 4×FY − (Q1+Q2+Q3) (approx: true); EPS Q4 = FY − ΣQ (approx). Nothing is derived when a quarter is missing.
7. Computed accounts (ebitda, total_debt, net_debt, working_capital, free_cash_flow) are attached to the latest version of the period with their formula; assumptions are flagged (short_term_debt_assumed_zero, short_term_investments_assumed_zero).
8. TTM (ttm()): sum of the last four consecutive fiscal quarters for flows, latest quarter for balances and share counts, EPS = sum of four quarterly EPS (approx). Windows with a hole are skipped (missing_quarters reason).

# Point-in-time (anti look-ahead)

Every row carries the filed_date of the filing that made it known. Serving "latest" = the version with the max filed_date per (statement, fiscal_year, fiscal_quarter). as_of=D = the same selection restricted to filed_date ≤ D (normalize.select_as_of). Consequences:

• Apple's Q2 FY2024 income statement does not exist on 2024-05-02 and exists on 2024-05-03 (filing date).
• a 10-K/A or a later 10-K that restates a quarter creates a new version; as_of before the amendment returns the original numbers, after it the restated ones (restated=true).
• /ratios?as_of=D uses the fundamentals known at D and the last close at or before D; /ratios/daily builds one snapshot per filed_date and ASOF-joins it to the daily closes in DuckDB (p.date >= s.valid_from), so a 10-Q filed May 3 only affects May 3 onwards.
• frames/{concept}?as_of=D and the bulk files keep accn/filed_date for the same reason.

# 5. Worked example — Apple, Q2 FY2024 (10-Q filed 2024-05-03, quarter ended 2024-03-30)

GET /v1/fundamentals/AAPL/statements?statement=income&period=quarterly&from=2024-03-30&to=2024-03-30

Balance sheet (same period): total_assets 337 411 000 000, cash 32 695 M, short-term investments 34 455 M, short_term_debt 12 759 M (CommercialPaper + LongTermDebtCurrent, components: true), long_term_debt 91 831 M, total_debt 104 590 M (computed), total_equity 74 194 M, shares_outstanding 15 334 082 000 (cover page 2024-04-19).

Cash flow Q2 FY2024 (derived=true): operating_cash_flow 22 690 000 000 = YTD6 62 585 M − Q1 39 895 M (derived: "YTD6-Q1"), capex 1 996 M, free_cash_flow 20 694 M (computed).

Q4 FY2024 (derived from the 10-K filed 2024-11-01): revenue 94 930 M = FY 391 035 M − YTD9 296 105 M. TTM at Q2 FY2024: revenue 381 623 M, net income 100 389 M, diluted EPS 6.43 (sum of 4 quarters).

GET /v1/fundamentals/AAPL/ratios?as_of=2024-05-03 (real close 183.38 on 2024-05-03): market_cap = 183.38 × 15 334 082 000 = 2 812 T; pe = 183.38 / 6.43 = 28.5; pb = 37.9; gross_margin = 173 966 / 381 623 = 45.6 %; net_margin 26.3 %; roe = 100 389 / 74 194 = 135 %; current_ratio = 128 416 / 123 822 = 1.04; debt_to_equity 1.41; enterprise_value = market_cap + 104 590 M − 32 695 M − 34 455 M; forward_pe = null (no_estimates); interest_coverage = null (missing:interest_expense); revenue_growth_yoy = 381 623 / 385 095 − 1 = −0.9 %. (These numbers are asserted in tests/test_fundamentals_unit.py::test_ratio_formulas_on_apple_q2_fy2024 against the recorded fixture; in the test lake the price is synthetic, so the API test checks the formulas, not the absolute valuation.)

# 6. Prototype results (full companyfacts, 2026-09-04)

Shake Shack was chosen as the small cap because its income statement uses the company extension shak:OperatingMaterialsExpense (food and paper costs) plus us-gaap:LaborAndRelatedExpense / OccupancyNet instead of any cost-of-revenue concept: cost_of_revenue and gross_profit are therefore null with no_mapped_tag, the extension is logged in fund_mapping_log with hint_account=cost_of_revenue and shown in /coverage.custom_extensions — exactly the "log, don't guess" behaviour. Its 52/53-week calendar (2023-12-27, 2024-12-25, then a change to 2025-12-31) and multi-class shares also exercise the calendar and dei logic. Reconciliation against a fresh EDGAR fetch: 0 discrepancies for AAPL and SHAK.

Latency on the test lake (TestClient, 25 runs, p95): statements 4–7 ms, ratios 7 ms, ratios/daily 1 year 30 ms (2.5 years 51 ms), screener 3 ms, frames 3 ms, filings 3 ms, coverage 1 ms — far under the 300 ms / 1 s targets; the screener reads a precomputed table so it stays O(rows in fund_latest) at universe scale.

# 7. Ingestion

• Universe (ingest.sync_universe): company_tickers.json + company_tickers_exchange.json restricted to the tickers present in parquet/stock/1day/* and parquet/etf/1day/*. One CIK with several tickers (GOOG/GOOGL, BRK-A/BRK-B) = one company, tickers lists the classes and any class resolves in the API. A ticker that leaves the SEC list is appended to ticker_history and the company becomes delisted (its data keeps being served). ETFs that are not SEC operating filers (SPY…) are simply absent → FUNDAMENTALS_NOT_AVAILABLE.
• Backfill (scripts/edgar_backfill.py): per CIK companyfacts + submissions (+ MetaLinks.json of the latest 10-K, 1 request) → lake → statements → coverage → screener row; manifest data_root/edgar/backfill_manifest.json makes it resumable (ok / no_facts are skipped, error retried; --force redoes). --from-zip companyfacts.zip reads the SEC bulk archive instead of the API. Ends with the bulk Parquet files.
• Incremental (scripts/edgar_incremental.py, every 2 min): Atom getcurrent feed per form (10-K, 10-Q, 8-K, 20-F — 4 requests) + the daily master index of the last two business days as a safety net (Archives/edgar/daily-index/{year}/QTR{q}/master.YYYYMMDD.idx; the SEC answers 403 for a day that has no index — weekend, holiday — which is treated as "not published", not as an error) → new (cik, accession) pairs of TRACKED_FORMS (10-K/10-Q/8-K/20-F/40-F/6-K, transition reports 10-KT/10-QT and every /A amendment) → refetch companyfacts (cache bypass) → re-normalise → statements rewritten only if they changed (fingerprint of the rows) → publish one filing event per registrant (Redis filings pub/sub + filings:stream capped at 1 000) → lag written to fund_ingest_state; the bulk extracts are rebuilt only when rows changed. Termination guards: Atom entries are filtered by form (the type=8-K feed also returns 8-K/A — an untracked form never landed in edgar_filings and re-ingested its company every cycle); a Redis SET edgar:seen_accn (TTL 7 d) remembers every pair handled, a HASH edgar:failed_accn counts transient errors (3 attempts, then the pair is left to the reconcile job); companyfacts_404 (no XBRL facts) still records the filings and is not a failure. failure_samples accumulate (last 10, with the CIK) instead of being wiped by the next clean cycle. The efts full-text search needs a query term and is therefore not used as a "list everything" source.
• Reconcile (scripts/edgar_reconcile.py): 20 random companies refetched and re-normalised in memory, every public account of every latest period compared to the DB (--fix re-ingests the differing ones); result in fund_ingest_state.reconcile and _health. Exit code 1 on discrepancies for alerting. Ends with PRAGMA incremental_vacuum (effective once the database runs auto_vacuum=INCREMENTAL, which init_db switches on automatically for a database under 50 MB; an existing big file needs the one-off scripts/migrate_coverage.py --vacuum).
• Schema maintenance (fundamentals/migrations.py, run by init_db() at every start, idempotent): fund_statements.coverage_id column, auto_vacuum on a young DB, and the edgar_filings key accn → (cik, accn) — migrated inline only when the table has ≤ 300 000 rows; above that the API logs a warning and keeps running on the legacy key (co-registrant copies are skipped by ON CONFLICT DO NOTHING, the Redis guard stops the poller loop) until scripts/migrate_edgar_filings.py is run (one transaction, ~1.4 M rows ≈ 20–60 s; stop the ingestion jobs first, the API can keep serving).
• Coverage dictionary (scripts/migrate_coverage.py): converts the legacy inline coverage JSON of fund_statements (≈ 1.1 KB × 1.2 M rows) to fund_coverage_blob references, 2 000 rows per transaction, resumable (--max-batches, --sleep), API online. Space goes back to the OS only with --vacuum (offline).
• Monitoring: GET /v1/fundamentals/_health (hidden from OpenAPI) — companies, statement versions, screener rows, last filed date, per-job lag/failures/mapping_failure_rate (share of null public accounts in fund_latest).

# Running on production (M3U96b)

ssh M3U96b
cd ~/hfmarketdata
export HFMD_DATA_ROOT=~/firstratedata            # lake + edgar/ + bulk/ + state/hfmd.db ; Redis: redis://127.0.0.1:6379/0 (default)
venv/bin/pip install -r hfmarketdata/requirements.txt

# 1) initial backfill (resumable; ~7 600 CIKs)
nohup venv/bin/python scripts/edgar_backfill.py --workers 4 > ~/edgar_backfill.log 2>&1 &
#    faster first pass: download the SEC bulk archive once (~1.3 GB) and use it for companyfacts
#    curl -A "$HFMD_SEC_USER_AGENT" -o /tmp/companyfacts.zip https://www.sec.gov/Archives/edgar/daily-index/xbrl/companyfacts.zip
#    venv/bin/python scripts/edgar_backfill.py --from-zip /tmp/companyfacts.zip --workers 6

# 2) incremental poller under PM2 (2-minute cycle, publishes to Redis for /v1/stream)
pm2 start venv/bin/python --name edgar-incremental --cwd ~/hfmarketdata -- scripts/edgar_incremental.py
pm2 save

# 3) nightly reconciliation (cron 03:15) — exit code 1 = discrepancies
15 3 * * * cd ~/hfmarketdata && HFMD_DATA_ROOT=~/firstratedata venv/bin/python scripts/edgar_reconcile.py --json ~/edgar_reconcile.json >> ~/edgar_reconcile.log 2>&1

# health
curl -s https://www.hfmarketdata.io/v1/fundamentals/_health | jq .data.jobs

The API process needs no restart: init_db() is idempotent and every request reads SQLite/Parquet.

# Backfill duration estimate (~7 600 CIKs at 10 req/s)

Requests: 2 per CIK (companyfacts + submissions) + 1 MetaLinks + ~0.3 for paginated older submissions ≈ 3.3 × 7 600 ≈ 25 000 requests → ~42 min at the 10 req/s ceiling. Volume: companyfacts average ≈ 2–3 MB (Apple 10 MB, small caps 1 MB) → ~20 GB downloaded, gzip-cached at ~2 GB. CPU: normalisation measured at 0.7–1.2 s per company (pandas, one core) → ~2 h single-threaded, ~35 min with --workers 4 overlapping network and CPU. Expected wall clock: 1 h – 1 h 30 with 4 workers (network-bound at the bucket), ≈ 45 min with --from-zip (no companyfacts requests; the zip download itself takes a few minutes). Resulting sizes: facts lake ≈ 2.5 GB Parquet, SQLite ≈ 1.5 GB, bulk files ≈ 20 × 30 MB.

# 8. Endpoints (all format=json|csv|parquet, cursor pagination, as_of)

Error codes added to core/errors.py: FUNDAMENTALS_NOT_AVAILABLE (404), INVALID_FILTER (400), CONCEPT_NOT_FOUND (404), STREAM_CONNECTION_LIMIT (429).

# 9. Stream (stream/)

GET /v1/stream (WebSocket). Auth by ?api_key= or Authorization: Bearer (keyless → JSON error then close 4001). Client {"action":"subscribe","channel":"filings","tickers":[…]|"all","forms":[…],"resume_token":<seq>}; server hello, subscribed, filing (ticker, cik, form, period, filed_date, url, accn, seq, summary:{revenue, net_income, eps_diluted, total_assets, operating_cash_flow, fiscal_year, fiscal_quarter, yoy:{…}}), heartbeat every 20 s, pong, error. Max 5 concurrent sockets per key: Redis ZSET stream:conns:{principal} (member = connection id, score = last heartbeat, refreshed every 30 s; members silent for 90 s are pruned at every count, so a worker that dies without cleanup never leaves phantom slots; 6th live socket → STREAM_CONNECTION_LIMIT, close 4029). Buffer = Redis stream filings:stream (MAXLEN 1 000, ids <seq>-0, seq from INCR filings:seq); resume_token replays everything after it. Delivery = one blocking XREAD (1 s) per subscribed socket on its own async Redis connection — no polling; the filings pub/sub channel is published for other consumers. Full spec: docs/asyncapi.yaml.

Authentication: the rate-limit middleware authenticates every /v1/* socket before the handler and stores principal (key:<id>), principal_kind, tier in scope["state"]; stream/routes.py reads those (never a hash of the key — the billed principal is exactly the one the HTTP limiter uses). Keyless → JSON AUTH_REQUIRED then close 4001; no middleware context while HFMD_RATELIMIT is on → close 4401. With the limiter disabled, stream/auth.py::authenticate(key) looks the key up in api_keys (sha256(salt + key)); unknown keys are refused whatever their shape.

Stream accounting interface (for the ratelimit module):

from stream import accounting
accounting.set_charger(fn)            # fn(principal: str, rows: int) -> None ; called once per delivered `filing` message
accounting.charge(principal, rows=1)  # what the socket calls (REDUCED_RATE_ROWS_PER_MESSAGE = 1)
accounting.rows_charged(principal)    # default fallback counter: Redis `stream:rows:{principal}:{YYYY-MM-DD}` (24 h TTL)

principal is the same key:<id> string the HTTP limiter uses, so stream rows can be folded into the rows quota; heartbeats, hello/subscribed and errors are free.

# 10. Tests and fixtures

tests/fixtures/edgar/ holds real, trimmed EDGAR documents (≈ 80 KB gzipped: companyfacts + submissions of Apple, Microsoft, Shake Shack limited to mapped tags and filings since 2022, SHAK's MetaLinks tag list, one Atom page, one daily index) recorded by tests/fixtures/edgar/record.py. tests/conftest.py::edgar_mock serves them through respx — no test touches the network. The synthetic price lake gained the SHAK, GOOG and GOOGL tickers. Suites: test_fundamentals_unit.py (mapping, calendar incl. Apple Q2 FY2024, resolution priority/units, Q4 derivation, restatements/as_of, TTM, ratio formulas on Apple's real numbers, filter grammar, client backoff), test_fundamentals_api.py (every endpoint in json/csv/parquet, envelopes, as_of, share classes), test_bulk.py (ETag/304), test_stream.py (auth via the middleware principal, subscribe, blocking delivery, resume, heartbeat, ZSET connection limit + phantom pruning, accounting hook), test_fundamentals_ingest.py (co-registrant accessions, poller termination and retries, failure samples, 403 daily index, conditional statement rewrite, coverage dictionary, edgar_filings key migration). ./.venv/bin/python -m pytest must stay green.

# 11. Limitations and next steps

• Companyfacts only carries standard taxonomies: values reported exclusively through company extensions (SHAK cost lines, many bank/insurer line items) stay null with a reason; the fund_mapping_log hints are the input for the next mapping version (bump MAPPING_VERSION, re-run normalisation from the cached raw JSON).
• Dimensional facts (segments, share classes) are not in companyfacts; shares_outstanding sums the dei cover facts, which is right for market cap but not per class.
• forward_pe is always null (no consensus estimates). Growth/CAGR need enough history (null otherwise).
• Fiscal-year labels of Jan–Mar year ends follow the filer's convention learnt from its 10-Ks; a filer with no 10-K in the window falls back to the MMDD extrapolation.
• Restatement detection is value-based (tolerance 1e-6 relative / 0.5 absolute); a filer re-tagging the same number under a lower-priority concept does not create a version, a re-tag under a higher-priority concept does.
• 20-F/40-F filers are mapped through ifrs-full tags but were not part of the prototype; currency is kept, not converted.
• The ratelimit module must call stream.accounting.set_charger and honour request.state.requires_key / request_cost / quota_exempt set by these routes; the WebSocket itself is not rate-limited beyond the 5-connection cap.