spb/firc0001_uqo

1# UQO Working Paper No. 0001 (FIRC-funded) — From Corporate Disclosures to a Russell 3000 Strategic Project Database23*Identification, Measurement, Validation, and Research Applications* — Manel Kammoun, Djerrycharli Tandjambianda, Simon-Pierre Boucher (order to be confirmed; see `docs/contributions.md`). Version 1.1, November 2026 — **revised after peer review; working draft, research database under validation** (v1.0 of October 28, 2026 is the reviewed baseline, tag `review-baseline-20261103`); revision branch `review-20261103` (Stages D–E, November 2026). Research funded by the FIRC (Fonds institutionnel de recherche et de création, UQO).45> **TL;DR** — SPID turns 527,548 SEC filings (10-K/10-Q/8-K, 2010–Oct 2026) of 2,564 issuers (S&P 500 ∪ iShares IWV Russell 3000 proxy, 85.3 % of the index's 2,996 constituents) into 308,065 section-linked candidate project mentions and 121,099 candidate projects under the current resolution key (21-category taxonomy, status, years, locations, technologies, partners, amounts). The paper documents the universe, the distributed acquisition/parsing architecture, the LLM extraction contract (two runs under one prompt lineage), the resolution rule and its failure modes, descriptive patterns, and a validation protocol — stating explicitly which gates are met and which are not.67## Layout8```9paper/          main.tex, sections/, appendix/, tables/ (generated), figures/ (generated), references.bib → main.pdf10code/           export_stats.py (master node, read-only DuckDB → data/) · make_tables.py (data/ + results/ → tables, figures, macros)11code/audit/     Stage D read-only audit scripts a01…a13 (M3U96b) and the frozen pipeline snapshot spid_snapshot/ (SHA256SUMS)12data/           CSV exports + stats.json (export_stats.py) · documented_inputs.json (the few non-query values, each with its source)13                datamodel_audit.csv (Appendix C rows) · example_project.json (written by make_tables.py) · metadata/ (universe artefacts + hashes, N10)14results/        Stage D audit outputs (≤ 5 MB each, with manifest.json) · inline_numbers.json · russell_coverage.json · tables_diff.txt15docs/           implementation_audit.md · universe_definition.md · universe_coverage.md · project_ontology.md · data_dictionary.md16                DATA_AVAILABILITY.md (DAS draft, provenance table, determinism paragraph) · annotation_protocol.md (N11) · contributions.md · research_log.md17tests/          test_tables.py — export reconciliation, byte-identical regeneration, no data literal in the generator18reproducibility/ validation_report.md (verdict), environment/requirements-exhibits.txt; node lock files at the root (requirements.lock, .python-version, environment.md)19run_all.sh      single entry point (exhibits → tests → paper; --with-node / --with-audit for the node steps)20```21Database and pipeline code: `uqo-research-db.git` (revision `2324b58`) on the UQO Git server; raw filings are not redistributed (`docs/DATA_AVAILABILITY.md`).2223## Rebuild the exhibits24```bash25./run_all.sh                       # laptop: python3 -I code/make_tables.py → tests → latexmk (≈ 1 min)26./run_all.sh --with-node           # master node (M3U96b): export_stats.py first (read-only DuckDB, threads=4, ≈ 10 min + bootstrap)27./run_all.sh --with-audit          # master node: Stage D audits (code/audit/run_all.sh) before the exhibits28python3 -I -m pytest -q tests      # checks only29```30`make_tables.py` never writes `data/stats.json`; the example project of Table 9 goes to `data/example_project.json`. Inline numbers quoted in the text are LaTeX macros `\SPID<name>` generated in `paper/tables/numbers.tex` (loaded in the preamble of `main.tex`); their value, source file and formula are listed in `results/inline_numbers.json`.3132## Program-to-exhibit map3334Every table is a self-contained float written by `make_tables.py` to `paper/tables/<name>.tex` (caption, label, `threeparttable` notes, generated-by comment); every figure is a vector PDF plus a float wrapper `paper/figures/<name>.tex`. The prose files only `\input` them. Labels are `tab:<name without tab_>` and `fig:<name without fig_>`.3536| exhibit (label) | generated file | script | inputs |37|---|---|---|---|38| tab:reconciliation universe reconciliation + Russell coverage | `tab_reconciliation.tex` | `make_tables.py` | `stats.json:universe`, `documented_inputs.json`, `results/russell_coverage.json`, `results/universe/universe_artefacts.json` |39| tab:filings filings by form | `tab_filings.tex` | `make_tables.py` | `filings_by_form.csv`, `parse_status.csv`, `results/universe/filings_status_missing.csv` |40| tab:sections ledger vs stored, calls | `tab_sections.tex` | `make_tables.py` | `sections_by_form.csv`, `ledger_by_form.csv`, `mentions_by_form.csv`, `results/ledger/table3_ledger_vs_stored.csv`, `ledger_collisions.csv` |41| tab:ledger_calls rows / calls / productive by form x run | `tab_ledger_calls.tex` | `make_tables.py` | `results/ledger/gate_pass_by_form_run.csv`, `gate_pass_by_run.csv`, `gate_pass_totals.json` |42| tab:outage outage reconciliation | `tab_outage.tex` | `make_tables.py` | `results/ledger/log_summary.json`, `throughput.json`, `backfill_block.json`, `resume_batch1_vs_expected.csv` |43| tab:items items in scope (+ calls, shown share, financing share) | `tab_items.tex` | `make_tables.py` | `ledger_10k_items.csv`, `ledger_10q_items.csv`, `results/ledger/gate_pass_by_form_item.csv`, `results/focus/`, `results/fp_classes/` |44| tab:fp_classes financing-class mentions | `tab_fp_classes.tex` | `make_tables.py` | `results/fp_classes/*.csv`, `summary.json` |45| tab:focus focus-window coverage | `tab_focus.tex` | `make_tables.py` | `results/focus/focus_coverage_by_form_item.csv`, `_by_form.csv`, `stats_keys.json` |46| tab:types projects by category (+ CI, excl. financing, over-merge) | `tab_types.tex` | `make_tables.py` | `projects_by_type.csv`, `projects_by_type_ci.csv`, `results/fp_classes/table5_restated_excl_financing.csv`, `results/resolver/overmerge_by_type.csv` |47| tab:sectors by sector | `tab_sectors.tex` | `make_tables.py` | `projects_by_sector.csv`, `companies_by_sector.csv` |48| tab:cohort cohort x run, issuer-level OLS | `tab_cohort.tex` | `make_tables.py` | `results/runs/table6_cohort.csv`, `table6_cohort_regression.json` |49| tab:status status / mentions / span | `tab_status.tex` | `make_tables.py` | `projects_by_status.csv`, `mentions_per_project.csv`, `duration.csv`, `results/resolver/ties_idempotence.json` |50| tab:amounts amounts and projects per issuer | `tab_amounts.tex` | `make_tables.py` | `amount_dist.csv`, `projects_per_issuer.csv`, `stats.json:amount_*`, `results/grounding/amount_tail.json` |51| tab:provenance provenance example | `tab_provenance.tex` | `make_tables.py` | `example_provenance.csv`, `documented_inputs.json:snippet_chars`, `results/resolver/composition.json` |52| tab:spec_curve specification curve of the project count | `tab_spec_curve.tex` | `make_tables.py` | `results/resolver/spec_curve.csv`, `composition.json`, `ties_idempotence.json` |53| tab:grounding mechanical grounding | `tab_grounding.tex` | `make_tables.py` | `results/grounding/grounding_by_group.csv`, `grounding_element_level.csv`, `grounding_all_structured.csv`, `fallback_bounds.json` |54| tab:benchmark_8k 8-K item-code consistency | `tab_benchmark_8k.tex` | `make_tables.py` | `results/benchmark_8k/item_vs_type.csv`, `summary.json` |55| tab:rules_agreement rules vs LLM, focus recall bound | `tab_rules_agreement.tex` | `make_tables.py` | `results/rules/agreement_by_form.csv`, `agreement_overall.csv`, `focus_recall_loss.csv` |56| tab:datamodel data-model audit (longtable, no float) | `tab_datamodel.tex` | `make_tables.py` | `data/datamodel_audit.csv` |57| tab:taxonomy, Appendix B prompt and gate regex | `tab_taxonomy.tex`, `prompt_verbatim.tex`, `gate_regex.tex` | `make_tables.py` | `code/audit/spid_snapshot/{taxonomy,llm,rules}.py` (read with `ast`, never retyped) |58| tab:june_october, tab:universe_artefacts, tab:coverage_year, tab:timeseries_cohort, tab:top_issuers, tab:confidence, tab:graph, tab:top_name_tokens, tab:overmerge (appendix) | `tab_<name>.tex` | `make_tables.py` | `results/resolver/`, `results/universe/`, `results/timeseries/`, `data/*.csv` |59| fig:coverage, fig:types, fig:confidence, fig:mentions_year, fig:grounding, fig:spec_curve, fig:focus | `fig_<name>.pdf` + `.png` + `.tex` float | `make_tables.py` (Okabe-Ito palette, markers + line styles, 9 pt serif, width = textwidth) | `data/*.csv`, `results/timeseries/`, `results/grounding/`, `results/resolver/`, `results/focus/` |60| fig:pipeline pipeline diagram | TikZ in `paper/sections/methodology.tex` | hand-drawn (not generated) | counts via `\SPID` macros |61| inline numbers (`\SPID…`) | `paper/tables/numbers.tex`, `results/inline_numbers.json` | `make_tables.py` | all of the above; `results/**.json` |62| `data/*.csv`, `data/stats.json` | 29 CSV + `ledger_collisions`, `cohort_by_run`, `calls_by_form`, `projects_by_type_ci` | `export_stats.py` (node, read-only; `IS DISTINCT FROM` amount_raw check, October wall-clock from the first October flush, deterministic ORDER BY, spill directory outside the repo) | `edgar_research.duckdb`, `spid.duckdb`, `data/metadata/*.json`, run logs |6364Exhibit numbers follow the order of `main.tex`. The `.png` copies under `paper/figures/` are written by `make_tables.py` at `documented_inputs.json:png_dpi` for the Word export (`code/tex2docx`). The render-and-inspect audit of every exhibit is recorded in `UQO-WP-REVIEW/quality_control/_FIRC-0001_{table,figure}_audit.csv`.65