# Data dictionary (current tables, revision 2324b58)

## Corpus layer — `data/duckdb/edgar_research.duckdb` (68 GB) and `data/parquet/*`
| Table | Key | Fields |
|---|---|---|
| `companies` | `cik` | ticker, company_name, sector, sub_industry, sic_code, sic_description, state, state_of_incorporation, exchange, fiscal_year_end, date_added, in_sp500, in_russell3000, russell_weight_pct, shard_node |
| `filings` | `accession_number` | cik (FK), ticker, company_name, form_type, filing_date, report_date, fiscal_year, sec_url, local_path, primary_document, file_size_bytes, download_status, parse_status (complete/error/missing) |
| `filing_sections` | `section_id` (UUID) | accession_number (FK), section_name, item_number, section_order, text, word_count, char_count |
| `filing_chunks` | `chunk_id` (UUID) | section_id (FK), accession_number, chunk_text, chunk_order, word_count, char_count, start_char, end_char (offsets within the section) |
| `embeddings_metadata` / `parquet/embeddings/` | `embedding_id` | source_id = chunk_id, model all-MiniLM-L6-v2, 384-d float32 vectors in Parquet |
| `chunk_scores`, `filing_scores`, `quarterly_panel`, `annual_panel`, `*_final` | chunk / accession / firm-period | cosine scores to 8 thematic anchors and their aggregates |

## Project layer — `project-db/data/db/spid.duckdb` (3.2 GB)
| Table | Key | Fields |
|---|---|---|
| `extracted_sections` | `section_id` | accession_number, backend, n_mentions, extracted_at — ledger of every section with ≥ 30 words (1,123,098 rows), **including** the 419,361 sections rejected by the lexical gate and never sent to the model (703,725 gate-passing calls); `backend` is the constant `llm` (no model id, prompt/taxonomy hash or call status); the 154,964 June rows were backfilled at one timestamp (2026-10-27 13:12:32) without the 30-word filter; a failed call is ledgered as `n_mentions = 0` |
| `spid_sections` | `section_id` | legacy 10-K items ingested in June 2026 (27,164 rows) |
| `project_mentions` | `mention_id` (SHA-1 of accession, section_id, project_type and the first 40 characters of project_name; 412 intra-section collisions overwritten by INSERT OR REPLACE) | accession_number, cik, section_id, section_name, project_name, project_type, description, objective, amount_usd, amount_raw (**formatted copy of `amount_usd`, e.g. `$1,500,000,000`, not the source string; NULL when the amount is 0**), locations[], technologies[], partners[], suppliers[], benefits[], risks[], years[], status, confidence, backend, ticker, company_name, form_type, filing_date, fiscal_year, sector |
| `projects` | `project_id` | cik, ticker, company_name, sector, project_type, project_name, canonical_location, technologies[], status, total_amount_usd (max), n_mentions, n_filings, first_seen, last_seen, first_year, last_year, avg_confidence, description |
| `project_timeline` | (project_id, accession_number) | filing_date, form_type, status, amount_usd, confidence, snippet |
| `kg_nodes` / `kg_edges` | node_id / (src, rel, dst) | node_type ∈ {Company, Project, Location, Technology, Partner}; rel ∈ {owns, located_in, uses} |
| `project_embeddings` | `project_id` | model, dim, vector FLOAT[384] |
| views `src_filings`, `src_sections`, `src_companies`, `v_sections` | — | read-only views over the corpus Parquet (paths of the master node) |

Null conventions: unknown amounts/locations are NULL / empty lists; `fiscal_year` is NULL for 68,827 mentions (all 39,930 June-run mentions and 28,897 October mentions whose filing carries no fiscal year); `years[]` and `technologies[]` may have been back-filled by regex from `description` when the model returned none (upper bounds 39 % / 46 %); a missing `confidence` is stored as 0.6 (3,278 mentions). Stage E, 2026-11-05: corrected after the Stage D audits (`results/ledger/`, `results/grounding/`).