# UQO Working Paper No. 0001 (FIRC-funded) — From Corporate Disclosures to a Russell 3000 Strategic Project Database

*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).

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

## Layout
```
paper/          main.tex, sections/, appendix/, tables/ (generated), figures/ (generated), references.bib → main.pdf
code/           export_stats.py (master node, read-only DuckDB → data/) · make_tables.py (data/ + results/ → tables, figures, macros)
code/audit/     Stage D read-only audit scripts a01…a13 (M3U96b) and the frozen pipeline snapshot spid_snapshot/ (SHA256SUMS)
data/           CSV exports + stats.json (export_stats.py) · documented_inputs.json (the few non-query values, each with its source)
                datamodel_audit.csv (Appendix C rows) · example_project.json (written by make_tables.py) · metadata/ (universe artefacts + hashes, N10)
results/        Stage D audit outputs (≤ 5 MB each, with manifest.json) · inline_numbers.json · russell_coverage.json · tables_diff.txt
docs/           implementation_audit.md · universe_definition.md · universe_coverage.md · project_ontology.md · data_dictionary.md
                DATA_AVAILABILITY.md (DAS draft, provenance table, determinism paragraph) · annotation_protocol.md (N11) · contributions.md · research_log.md
tests/          test_tables.py — export reconciliation, byte-identical regeneration, no data literal in the generator
reproducibility/ validation_report.md (verdict), environment/requirements-exhibits.txt; node lock files at the root (requirements.lock, .python-version, environment.md)
run_all.sh      single entry point (exhibits → tests → paper; --with-node / --with-audit for the node steps)
```
Database and pipeline code: `uqo-research-db.git` (revision `2324b58`) on the UQO Git server; raw filings are not redistributed (`docs/DATA_AVAILABILITY.md`).

## Rebuild the exhibits
```bash
./run_all.sh                       # laptop: python3 -I code/make_tables.py → tests → latexmk (≈ 1 min)
./run_all.sh --with-node           # master node (M3U96b): export_stats.py first (read-only DuckDB, threads=4, ≈ 10 min + bootstrap)
./run_all.sh --with-audit          # master node: Stage D audits (code/audit/run_all.sh) before the exhibits
python3 -I -m pytest -q tests      # checks only
```
`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`.

## Program-to-exhibit map

| exhibit | generated file | script | inputs |
|---|---|---|---|
| Table 1 universe reconciliation | `paper/tables/tab_reconciliation.tex` | `make_tables.py` | `stats.json:universe`, `documented_inputs.json:iwv_equity_lines_reported` |
| Table 2 filings by form | `tab_filings.tex` | `make_tables.py` | `filings_by_form.csv`, `parse_status.csv`, `stats.json` |
| Table 3 sections and ledger (+ note) | `tab_sections.tex`, `tab_sections_note.tex` | `make_tables.py` | `sections_by_form.csv`, `ledger_by_form.csv`, `mentions_by_form.csv`, `results/ledger/table3_ledger_vs_stored.csv`, `results/ledger/ledger_collisions.csv` |
| Table 4 items in scope | `tab_items.tex` | `make_tables.py` | `ledger_10k_items.csv`, `ledger_10q_items.csv`, `results/ledger/gate_pass_by_form_item.csv` |
| Table 5 projects by type (+ note) | `tab_types.tex`, `tab_types_note.tex` | `make_tables.py` | `projects_by_type.csv`, `projects_by_type_ci.csv` (N13), `results/resolver/overmerge_by_type.csv`, `amount_dist.csv` |
| Table 6 by sector | `tab_sectors.tex` | `make_tables.py` | `projects_by_sector.csv`, `companies_by_sector.csv` |
| Table 7 status / mentions / span | `tab_status.tex` | `make_tables.py` | `projects_by_status.csv`, `mentions_per_project.csv`, `duration.csv` |
| Table 8 amounts and projects per issuer (+ note) | `tab_amounts.tex`, `tab_amounts_note.tex` | `make_tables.py` | `amount_dist.csv`, `projects_per_issuer.csv`, `stats.json:amount_*` |
| Table 9 provenance example | `tab_provenance.tex` | `make_tables.py` | `example_provenance.csv`, `documented_inputs.json:snippet_chars` |
| Table 12 data-model audit (longtable) | `tab_datamodel.tex` | `make_tables.py` | `data/datamodel_audit.csv` |
| Tables 14–17 (appendix) | `tab_coverage_year.tex`, `tab_top_issuers.tex`, `tab_confidence.tex`, `tab_graph.tex` | `make_tables.py` | `issuers_by_year.csv`, `filings_by_year_form.csv`, `mentions_by_year.csv`, `projects_by_first_seen_year.csv`, `top_issuers.csv`, `confidence_dist.csv`, `kg.csv` |
| Appendix A taxonomy | `tab_taxonomy.tex` | `make_tables.py` | `code/audit/spid_snapshot/taxonomy.py` |
| Appendix B prompt and gate regex | `prompt_verbatim.tex`, `gate_regex.tex` | `make_tables.py` | `code/audit/spid_snapshot/llm.py`, `rules.py` (read with `ast`, never retyped) |
| Stage D tables (when `results/` is present) | `tab_spec_curve.tex`, `tab_top_name_tokens.tex`, `tab_overmerge.tex`, `tab_june_october.tex`, `tab_table3_calls.tex`, `tab_grounding.tex`, `tab_focus.tex`, `tab_cohort.tex`, `tab_fp_classes.tex`, `tab_timeseries_cohort.tex`, `tab_benchmark_8k.tex`, `tab_rules_agreement.tex` | `make_tables.py` (generic CSV → tabular) | `results/<package>/*.csv` from `code/audit/a*.py` |
| Figure 1 coverage (integer year ticks) | `paper/figures/fig_coverage.pdf` | `make_tables.py` | `issuers_by_year.csv` |
| Figure 3 projects by type | `fig_types.pdf` | `make_tables.py` | `projects_by_type.csv` |
| Figure 4 confidence | `fig_confidence.pdf` | `make_tables.py` | `confidence_dist.csv` |
| Figure 5 mentions and new projects by year (y from 0; second panel per issuer; 2010 / 2026 shaded) | `fig_mentions_year.pdf` | `make_tables.py` | `mentions_by_year.csv`, `projects_by_first_seen_year.csv` |
| Figure 2 pipeline diagram | `fig_pipeline.pdf` | hand-drawn (TikZ/PDF, not generated) | — |
| inline numbers (`\SPID…`) | `paper/tables/numbers.tex`, `results/inline_numbers.json` | `make_tables.py` | all of the above; `results/**.json` (N1, N3, L5) |
| `data/*.csv`, `data/stats.json` | 29 CSV + 3 new (`ledger_collisions`, `cohort_by_run`, `calls_by_form`) + `projects_by_type_ci.csv` | `export_stats.py` (node, read-only) | `edgar_research.duckdb`, `spid.duckdb`, `data/metadata/*.json`, run logs |

Figure numbers follow the order of `main.tex`; the `.png` copies under `paper/figures/` are not produced by any script and are kept only for the Word export (`code/tex2docx`).