# DATA_AUDIT.md — WP39 wp39_uqo

Phase 0 discovery and feasibility audit of the FinData OS API (https://findata.spboucher.ai/api/v1) as the primary source for The Invisible Balance Sheet. All numbers below were measured on 2026-11-02 (America/Toronto) with the owner's Claude Code key (admin scope, laptop) and then with a dedicated least-privilege key wp39_uqo (scopes read:market, read:fundamentals, query, export; stored outside git in ~/findata-secrets/api-key-wp39.txt on M3U96a and ~/Desktop/Cluster/secrets/findata/api-key-wp39.txt on the laptop, mode 600). No credential appears in this repository, in logs or in artifacts. Probe transcripts: artifacts/logs/phase0_probe.json (produced by scripts/00_probe_findata.py).

# 0. Viability conclusion

YES — viable, with two structural caveats that shape the paper.

1. A licensed, point-in-time usable cohort exists: 5,451 US-listed entities with quarterly fundamentals (67 metrics, filing_date 2012-01-03 → 2026-10-30, median 45 quarterly periods per firm), 373,326 earnings-release events with release date and (for 65 %) before-open/after-close timing, and 6.70 billion raw 1-minute bars for 16,195 stock symbols (2000-01-03 → 2026-10-30) including 8,069 -DELISTED symbols. Exports of 1-minute history are fast (≈ 1 s per symbol-decade, see §6).
2. Caveat A — survivorship in fundamentals, not in prices. The fundamentals/earnings tables contain zero delisted entities (ingestion ran for active tickers only). Minute bars do cover delisted names, so the price side is survivorship-free, but the accounting panel is conditioned on being listed on 2026-11-01. The vendor does serve fundamentals for at least some delisted tickers (probe: FMD → HTTP 200 with data; CWZ → 404), so a scoped delisted-fundamentals backfill is possible but requires owner approval and a capacity plan (not run; see §8).
3. Caveat B — PIT is a proxy. available_at = filing_date + 1 day is a policy, revision_id = 1 for every fact (observed_at = first ingestion, 2026-11-01/02). Values are the vendor's current history, so restatements that happened before ingestion are invisible. The design therefore (i) uses only prior-quarter fundamentals as inputs, (ii) uses the earnings-release date (not the filing date) as the reveal date, and (iii) reserves a stringent specification using SEC-filing-level reconstruction for a subset (Phase 5, conditional on time).

# 1. API contract — documented vs observed

Reconciliation notes: the catalog reports row_count = 0 for Postgres-backed datasets (fundamentals.*, corporate_actions, reference.instruments); that is a catalog limitation, not an absence of data. The REST interval enum is narrower than the lake (30m/1h exist only as vendor-adjusted datasets); WP39 rebuilds 5m/30m/1d bars from the raw 1m export so that every frequency shares one source and one adjustment path.

# 2. Market bars — coverage

Counts of distinct stock symbols with at least one bar on a mid-June session (SQL playground, session_date = predicate):

Finding: the vendor 1d dataset is incomplete relative to the 1m dataset (about half the symbols in 2005–2015 and no delisted names). Decision D-003: daily bars are derived from the 1m export (regular session 09:30–16:00 ET) — never taken from market_bars.stock.1d.raw. Delisted symbols are present in the 1m lake (e.g. AA.B-DELISTED 531 sessions 2014–2016; CWZ-DELISTED 2000–2014).

Bar conventions verified on XOM and SPY exports: timestamps are bar open in UTC (2012-01-03 09:01Z = 04:01 ET), extended hours 04:00–19:59 ET are present, zero-volume minutes are absent (bar counts per session 405–798 vs 391 expected regular bars), quality_flags list is empty on normal rows, revision_id = 1.

# 3. Bars schema (Arrow, schema v1)

instrument_id, symbol_as_reported, venue (null), asset_class, interval, timestamp_utc (timestamp[us, UTC], bar open), session_date (date32, exchange tz), open, high, low, close, volume (double), currency, trade_count (int32?, derived only), vwap (double?, derived only), adjustment_type, source_id, ingested_at, quality_flags (list<string>), revision_id (int32). Parquet exports carry metadata findata.schema = bars, findata.schema_version = 1, findata.timestamp_convention = bar_open.

# 4. Fundamentals — coverage and PIT fields

• Entities: 5,451 (frd:<TICKER>, security_id = firstrate:stock:<TICKER>, none delisted, none with security_id null).
• Metrics observed per period: 67 (the vendor documents 76; 9 are never populated in the API output). Names: acc_oci, assets_c, assets_nc, assets_tot, capex, cash, cash_business_acq, cash_change, cash_debt_sec, cash_div, cash_equity, cash_fin, cash_inv, cash_inv_acq, cash_op, cogs, cos_inc, debt, debt_c, debt_nc, def_rev, depr, dps, ebit, ebitda, ebt, eps, eps_d, equity, fx_impact_cash, gm, gp, intangibles, interest_exp, intr_cover, inv_c, inv_nc, inventory, investments, liab_c, liab_nc, liab_tot, net_inc, net_inc_com, net_inc_non_control, net_loss_discontinued, om, op_ex, op_inc, payables, ppe, pref_divs, receivables, research_dev, ret_earnings, rev, roa, roe, roic, sgna, share_comp, shares, shares_av, shares_av_diluted, tax_asset, tax_expense, tax_liability.
• Quarterly rev facts by fiscal year (entities / facts): 2011: 1,798 / 1,812 · 2012: 2,461 / 8,725 · 2013: 2,623 / 9,868 · 2014: 2,771 / 10,560 · 2015: 2,916 / 11,145 · 2016: 3,076 / 11,656 · 2017: 3,276 / 12,340 · 2018: 3,505 / 13,209 · 2019: 3,763 / 14,034 · 2020: 4,309 / 15,488 · 2021: 4,663 / 17,458 · 2022: 4,851 / 18,289 · 2023: 5,043 / 18,904 · 2024: 5,244 / 19,467 · 2025: 5,370 / 19,625 · 2026: 4,928 / 10,915. filing_date is never null (no pit_fallback).
• Firms by number of quarterly periods: ≥ 8: 5,018 · ≥ 20: 4,331 · ≥ 40: 2,891. First quarter year: 2011: 2,141 · 2012: 317 · 2013–2019: 148–256 per year · 2020: 551 · 2021: 353 · 2022–2025: 164–215 per year.
• Filing lag (filing_date − fiscal_period_end, quarterly): p10 = 30 d, median = 40 d, p90 = 77 d.
• Q4 values are genuine quarterly flows (AAPL FY2019 revenue 260.17 bn = 84.31 + 58.02 + 53.81 + 64.04 bn).
• PIT fields per fact: filing_date, published_at (null), available_at = filing_date + 1 d 00:00 ET, observed_at (= 2026-11-01 18:24 ET for the first ingestion), revision_id = 1, restatement_flag = false, superseded_at = null. The API as_of semantics (available_at ≤ as_of and (revision_id = 1 or observed_at ≤ as_of)) were exercised: AAPL as_of = 2020-01-01 returns 2,000 facts (limit) and reports facts_hidden_by_as_of = 2211.
• Entity master: cik, sector, industry are null for all 5,451 entities → external SEC crosswalk needed (§7).

# 5. Earnings-release events (reveal dates)

Postgres earnings_events (entity_id, event_date, eps_estimate, eps_reported, release_time, source_id, observed_at): 373,326 rows, 5,451 entities, 2000-01-01 → 2027-04-29, one row per (entity, date). release_time: after_close 124,832 · before_open 120,586 · null 127,908. For 2012–2024 the null share is 35–36 %; eps_estimate is null for 24–27 % of events (vendor estimate; vintage unknown → exploratory only). Row counts for 2025–2026 are inflated by forward-dated placeholders (2026: 35,361 events), which are dropped at the sample end (2026-10-30).

Matching quarterly filings to the first release event in [period_end, filing_date + 10 d] (213,954 filings 2012→): 96.3 % matched; 1,493 (0.7 %) have filing_date < event_date; 16,868 (7.9 %) have a lag > 30 d (likely mismatches or late filers); median filing_date − event_date = 1 day. Rule (PIT_PROTOCOL §3): reveal date A_q = matched release date; a firm-quarter is excluded when no release is matched, when filing_date < event_date, or when the lag exceeds 30 days; the prediction cutoff always satisfies c < min(A_q, F_q) in trading days.

# 6. Throughput and capacity (measured on M3U96a, API on localhost)

Capacity model for the main sample (≈ 4,300 firms with ≥ 8 quarters and 1m coverage): 2 exports × ~1.5 s ≈ 3.6 h of export wall-time (sequential; 4 concurrent streams bring it under 1 h), ≈ 30 MB raw Parquet per firm (≈ 130 GB if kept). Decision D-004: raw extracts are processed streaming (export → daily feature rows at 1m/5m/30m/1d → delete raw file, keep SHA-256 + row count in the manifest), so steady-state scratch usage stays below 5 GB. The storage node M3U96a (28 cores, 96 GB RAM, 319 GB free on the internal SSD, hard floor 60 GB for FinData) hosts the computation; the feature panel (≈ 4,300 firms × ~3,700 sessions × 4 frequencies) is < 2 GB.

# 7. External sources and exceptions

# 8. Gaps, risks and recommendations to the owner

1. Delisted fundamentals backfill (recommended, not run). A one-off fundamentals job for the 8,069 -DELISTED tickers (vendor returns data for at least part of them) would remove the survivorship conditioning of the accounting panel. Estimated cost: ≤ 8,069 vendor calls (1 req/s policy ≈ 2.3 h), a few hundred MB in Postgres. Requires: owner approval, findata ingest fundamentals --delisted (does not exist yet; connector change on M3U96a ~/apps/findata).
2. Earnings events API route — expose fundamentals.earnings through /api/v1/earnings/{entity} so that WP39 (and future projects) stop reading Postgres directly.
3. SEC EDGAR connector (fundamentals.sec_companyfacts, pending a User-Agent setting) would give filing-level as-originally-reported values for the stringent PIT specification.
4. API bug found: POST /admin/keys returns HTTP 500 (api_keys_user_id_fkey violation) when the caller is an API key whose user_id is not in users; the CLI findata create-key works. Logged for the FinData backlog, not fixed here.

# 9. License and privacy constraints applied

FirstRate Data: perpetual private use, research and derived statistics allowed with attribution, no redistribution of raw records. Consequently: no raw bars or fundamentals are committed; data/raw stays empty; the replication package ships code, manifests (hashes, versions, counts), derived aggregate tables and figures only; the manuscript attributes FirstRate Data via FinData OS. SEC and Kenneth French data are public.