spb/uqo-research-db
Public
1# SPID — SEC Project Intelligence Database23Reconstructs the **strategic project portfolio of public companies directly from raw SEC4filings** and makes those projects searchable, analyzable, and investable — a home-grown5analogue of a Capital IQ / PitchBook / Gartner-style intelligence layer, but *project-centric*.67Instead of treating 10-K / 10-Q / 8-K filings as financial documents, SPID treats them as a8massive source of **project intelligence**: new products, R&D programs, AI initiatives,9manufacturing/plant expansions, data centers, cloud/ERP migrations, M&A integrations, capex10programs, geographic expansions, partnerships, energy-transition and sustainability programs,11drug pipelines, automation, and more.1213---1415## Why this repo is small but the database is large1617It is built **on top of the existing UQO EDGAR Research DB corpus** that already lives in18`../data`:1920| Asset | Size | Used by SPID |21|---|---|---|22| `data/raw/sec/` | 88 GB, 500 companies | raw 10-K HTML (ingested on demand) |23| `data/parquet/filings/` | 134,124 filings | filing metadata (cik, ticker, dates, form) |24| `data/parquet/filing_sections/` | 127,800 sections (10-Q + 8-K) | pre-segmented section text |25| `data/parquet/companies/` | company metadata | sector / SIC / state enrichment |2627SPID adds the **project layer**: extraction → resolution → timelines → knowledge graph →28embeddings → search → analytics, persisted in a single DuckDB file (`data/db/spid.duckdb`).2930> Note: the upstream corpus only sectioned **10-Q and 8-K** filings. SPID's `ingest` command31> cleans and sections the **raw 10-K HTML** (Items 1, 1A, 2, 7, 7A) so 10-Ks flow through the32> same pipeline.3334---3536## Architecture3738```39 ┌──────────────────────────────────────────────┐40 raw 10-K HTML ───────► │ ingest.py (clean + section Items 1/1A/2/7) │41 (data/raw/sec) └──────────────────────────────────────────────┘42 │43 filing_sections.parquet ───────────────┼──────────► v_sections (unified feed)44 (10-Q / 8-K, pre-parsed) │45 ▼46 ┌──────────────────────────────────────────────┐47 │ EXTRACT (pluggable backend) │48 │ • rules — deterministic, zero-cost │49 │ • llm — gpt-4o-mini, JSON mode (default) │50 └──────────────────────────────────────────────┘51 │ project_mentions52 ▼53 ┌──────────────────────────────────────────────┐54 │ RESOLVE — merge mentions across filings/years│55 │ into projects + timelines │56 └──────────────────────────────────────────────┘57 │ projects, project_timeline58 ┌───────────────┼────────────────┐59 ▼ ▼ ▼60 knowledge graph embeddings analytics61 (nodes/edges, (MiniLM 384-d, (type/sector/62 Cypher export) semantic search) geo/tech/AI)63```6465### Project schema (per mention)66```jsonc67{68 "project_name": "", "project_type": "", "description": "", "objective": "",69 "status": "planned|in_progress|completed|mentioned",70 "amount_usd": 0, "locations": [], "technologies": [], "partners": [],71 "benefits": [], "risks": [], "years": [], "confidence": 0.072}73```74`project_type` is drawn from a controlled vocabulary of 21 categories75(see `spid/taxonomy.py`): `manufacturing_expansion, plant_construction, data_center,76cloud_migration, ai_initiative, digital_transformation, erp_implementation, rd_program,77product_launch, drug_pipeline, ma_integration, geographic_expansion, capex_program,78supply_chain, automation, sustainability, energy_transition, infrastructure,79cost_reduction, partnership, technology_deployment`.8081---8283## Quick start8485```bash86cd project-db87PY=../.venv/bin/python # repo virtualenv (duckdb, httpx, sentence-transformers …)8889# 1. (optional) section raw 10-Ks for the companies you care about90PYTHONPATH=. $PY -m spid ingest --tickers NVDA,AAPL,MSFT,CAT,XOM9192# 2. extract projects with gpt-4o-mini (default backend)93PYTHONPATH=. $PY -m spid extract --tickers NVDA,AAPL,MSFT --forms 10-K,10-Q,8-K --workers 129495# …or the zero-cost deterministic baseline96PYTHONPATH=. $PY -m spid extract --backend rules --sample 509798# 3. resolve mentions → projects + timelines, build graph + embeddings99PYTHONPATH=. $PY -m spid resolve100PYTHONPATH=. $PY -m spid graph101PYTHONPATH=. $PY -m spid embed102103# 4. explore104PYTHONPATH=. $PY -m spid analytics105PYTHONPATH=. $PY -m spid search "data center" --type data_center --min-amount 5e8106PYTHONPATH=. $PY -m spid semantic "AI projects launched by banks"107PYTHONPATH=. $PY -m spid project <project_id>108PYTHONPATH=. $PY -m spid stats109```110111`spid all [extraction flags]` runs extract → resolve → graph → embed → analytics in one shot.112113### Optional HTTP API114```bash115PYTHONPATH=. ../.venv/bin/uvicorn spid.api:app --port 8088116# GET /projects?type=data_center&min_amount=5e8 GET /semantic?q=... GET /analytics117```118119---120121## Extraction backends122123| Backend | Cost | Quality | Use |124|---|---|---|---|125| `rules` | free, fast | high-precision signals, generic names | bulk first-pass / offline |126| `llm` (gpt-4o-mini) | ~$0.0008 / section | named projects, amounts, status, objectives | production extraction |127128Configured in `.env` (gitignored):129```130OPENAI_API_KEY=sk-...131SPID_LLM_BASE_URL=https://api.openai.com/v1132SPID_LLM_MODEL=gpt-4o-mini133SPID_BACKEND=llm134```135The LLM client is **OpenAI-compatible**, so the same code targets the MacLustr cluster gateway136(`https://llm.maclustr.io/v1`, `maclustr-general`) or any local model by changing two env vars.137138---139140## Scaling to the full corpus141142The pipeline is incremental (`INSERT OR REPLACE` on stable mention IDs) and resumable.143144- **Cost control:** the LLM extractor sends only project-relevant *windows* of each section145 (`_focus`), so per-call cost is bounded regardless of section size.146- **Throughput:** LLM calls are threaded (`--workers`); resolution/graph/analytics are pure147 DuckDB SQL and scale to millions of mentions.148- **Full run estimate:** ~127,800 pre-parsed sections + ~7,655 10-Ks ≈ **$100–130** of149 gpt-4o-mini at current pricing. Run per-sector or per-cohort with `--tickers` / `--ciks`.150151### Roadmap hooks already in place152- Add forms (S-1, DEF 14A, 20-F, 6-K, transcripts, press releases) → extend `ingest.py` +153 the `forms` filter; everything downstream is form-agnostic.154- Swap embeddings to pgvector / Qdrant / Weaviate → the `project_embeddings` vector column is155 the only touch point (`embed.py`).156- Load the knowledge graph into Neo4j → `data/exports/kg_import.cypher` is generated by157 `spid graph`.158159---160161## Module map162163| File | Responsibility |164|---|---|165| `spid/config.py` | paths, settings, `.env` loader |166| `spid/taxonomy.py` | project-type vocabulary, tech/status/geo/money extractors |167| `spid/store.py` | DuckDB schema + read-only views over the source parquet |168| `spid/ingest.py` | raw 10-K HTML → sectioned `spid_sections` |169| `spid/extract/rules.py` | deterministic extractor |170| `spid/extract/llm.py` | gpt-4o-mini extractor (JSON mode) |171| `spid/pipeline.py` | threaded extraction engine |172| `spid/resolve.py` | mentions → projects + timelines (SQL) |173| `spid/graph.py` | knowledge graph + Cypher/parquet export |174| `spid/embed.py` | embeddings + semantic search |175| `spid/search.py` | structured / keyword search |176| `spid/analytics.py` | dashboards |177| `spid/api.py` | optional FastAPI service |178| `spid/__main__.py` | CLI (`python -m spid …`) |179180---181182*Built on the UQO EDGAR Research DB. Academic / research use.*183