# SPID — SEC Project Intelligence Database

Reconstructs the **strategic project portfolio of public companies directly from raw SEC
filings** and makes those projects searchable, analyzable, and investable — a home-grown
analogue of a Capital IQ / PitchBook / Gartner-style intelligence layer, but *project-centric*.

Instead of treating 10-K / 10-Q / 8-K filings as financial documents, SPID treats them as a
massive source of **project intelligence**: new products, R&D programs, AI initiatives,
manufacturing/plant expansions, data centers, cloud/ERP migrations, M&A integrations, capex
programs, geographic expansions, partnerships, energy-transition and sustainability programs,
drug pipelines, automation, and more.

---

## Why this repo is small but the database is large

It is built **on top of the existing UQO EDGAR Research DB corpus** that already lives in
`../data`:

| Asset | Size | Used by SPID |
|---|---|---|
| `data/raw/sec/` | 88 GB, 500 companies | raw 10-K HTML (ingested on demand) |
| `data/parquet/filings/` | 134,124 filings | filing metadata (cik, ticker, dates, form) |
| `data/parquet/filing_sections/` | 127,800 sections (10-Q + 8-K) | pre-segmented section text |
| `data/parquet/companies/` | company metadata | sector / SIC / state enrichment |

SPID adds the **project layer**: extraction → resolution → timelines → knowledge graph →
embeddings → search → analytics, persisted in a single DuckDB file (`data/db/spid.duckdb`).

> Note: the upstream corpus only sectioned **10-Q and 8-K** filings. SPID's `ingest` command
> cleans and sections the **raw 10-K HTML** (Items 1, 1A, 2, 7, 7A) so 10-Ks flow through the
> same pipeline.

---

## Architecture

```
                         ┌──────────────────────────────────────────────┐
  raw 10-K HTML ───────► │  ingest.py   (clean + section Items 1/1A/2/7)  │
  (data/raw/sec)         └──────────────────────────────────────────────┘
                                          │
  filing_sections.parquet ───────────────┼──────────►  v_sections  (unified feed)
  (10-Q / 8-K, pre-parsed)                │
                                          ▼
                         ┌──────────────────────────────────────────────┐
                         │  EXTRACT  (pluggable backend)                  │
                         │   • rules  — deterministic, zero-cost          │
                         │   • llm    — gpt-4o-mini, JSON mode (default)  │
                         └──────────────────────────────────────────────┘
                                          │  project_mentions
                                          ▼
                         ┌──────────────────────────────────────────────┐
                         │  RESOLVE  — merge mentions across filings/years│
                         │            into projects + timelines           │
                         └──────────────────────────────────────────────┘
                                          │  projects, project_timeline
                          ┌───────────────┼────────────────┐
                          ▼               ▼                ▼
                   knowledge graph   embeddings        analytics
                   (nodes/edges,     (MiniLM 384-d,    (type/sector/
                    Cypher export)   semantic search)   geo/tech/AI)
```

### Project schema (per mention)
```jsonc
{
  "project_name": "", "project_type": "", "description": "", "objective": "",
  "status": "planned|in_progress|completed|mentioned",
  "amount_usd": 0, "locations": [], "technologies": [], "partners": [],
  "benefits": [], "risks": [], "years": [], "confidence": 0.0
}
```
`project_type` is drawn from a controlled vocabulary of 21 categories
(see `spid/taxonomy.py`): `manufacturing_expansion, plant_construction, data_center,
cloud_migration, ai_initiative, digital_transformation, erp_implementation, rd_program,
product_launch, drug_pipeline, ma_integration, geographic_expansion, capex_program,
supply_chain, automation, sustainability, energy_transition, infrastructure,
cost_reduction, partnership, technology_deployment`.

---

## Quick start

```bash
cd project-db
PY=../.venv/bin/python            # repo virtualenv (duckdb, httpx, sentence-transformers …)

# 1. (optional) section raw 10-Ks for the companies you care about
PYTHONPATH=. $PY -m spid ingest --tickers NVDA,AAPL,MSFT,CAT,XOM

# 2. extract projects with gpt-4o-mini (default backend)
PYTHONPATH=. $PY -m spid extract --tickers NVDA,AAPL,MSFT --forms 10-K,10-Q,8-K --workers 12

#    …or the zero-cost deterministic baseline
PYTHONPATH=. $PY -m spid extract --backend rules --sample 50

# 3. resolve mentions → projects + timelines, build graph + embeddings
PYTHONPATH=. $PY -m spid resolve
PYTHONPATH=. $PY -m spid graph
PYTHONPATH=. $PY -m spid embed

# 4. explore
PYTHONPATH=. $PY -m spid analytics
PYTHONPATH=. $PY -m spid search "data center" --type data_center --min-amount 5e8
PYTHONPATH=. $PY -m spid semantic "AI projects launched by banks"
PYTHONPATH=. $PY -m spid project <project_id>
PYTHONPATH=. $PY -m spid stats
```

`spid all [extraction flags]` runs extract → resolve → graph → embed → analytics in one shot.

### Optional HTTP API
```bash
PYTHONPATH=. ../.venv/bin/uvicorn spid.api:app --port 8088
# GET /projects?type=data_center&min_amount=5e8   GET /semantic?q=...   GET /analytics
```

---

## Extraction backends

| Backend | Cost | Quality | Use |
|---|---|---|---|
| `rules` | free, fast | high-precision signals, generic names | bulk first-pass / offline |
| `llm` (gpt-4o-mini) | ~$0.0008 / section | named projects, amounts, status, objectives | production extraction |

Configured in `.env` (gitignored):
```
OPENAI_API_KEY=sk-...
SPID_LLM_BASE_URL=https://api.openai.com/v1
SPID_LLM_MODEL=gpt-4o-mini
SPID_BACKEND=llm
```
The LLM client is **OpenAI-compatible**, so the same code targets the MacLustr cluster gateway
(`https://llm.maclustr.io/v1`, `maclustr-general`) or any local model by changing two env vars.

---

## Scaling to the full corpus

The pipeline is incremental (`INSERT OR REPLACE` on stable mention IDs) and resumable.

- **Cost control:** the LLM extractor sends only project-relevant *windows* of each section
  (`_focus`), so per-call cost is bounded regardless of section size.
- **Throughput:** LLM calls are threaded (`--workers`); resolution/graph/analytics are pure
  DuckDB SQL and scale to millions of mentions.
- **Full run estimate:** ~127,800 pre-parsed sections + ~7,655 10-Ks ≈ **$100–130** of
  gpt-4o-mini at current pricing. Run per-sector or per-cohort with `--tickers` / `--ciks`.

### Roadmap hooks already in place
- Add forms (S-1, DEF 14A, 20-F, 6-K, transcripts, press releases) → extend `ingest.py` +
  the `forms` filter; everything downstream is form-agnostic.
- Swap embeddings to pgvector / Qdrant / Weaviate → the `project_embeddings` vector column is
  the only touch point (`embed.py`).
- Load the knowledge graph into Neo4j → `data/exports/kg_import.cypher` is generated by
  `spid graph`.

---

## Module map

| File | Responsibility |
|---|---|
| `spid/config.py` | paths, settings, `.env` loader |
| `spid/taxonomy.py` | project-type vocabulary, tech/status/geo/money extractors |
| `spid/store.py` | DuckDB schema + read-only views over the source parquet |
| `spid/ingest.py` | raw 10-K HTML → sectioned `spid_sections` |
| `spid/extract/rules.py` | deterministic extractor |
| `spid/extract/llm.py` | gpt-4o-mini extractor (JSON mode) |
| `spid/pipeline.py` | threaded extraction engine |
| `spid/resolve.py` | mentions → projects + timelines (SQL) |
| `spid/graph.py` | knowledge graph + Cypher/parquet export |
| `spid/embed.py` | embeddings + semantic search |
| `spid/search.py` | structured / keyword search |
| `spid/analytics.py` | dashboards |
| `spid/api.py` | optional FastAPI service |
| `spid/__main__.py` | CLI (`python -m spid …`) |

---

*Built on the UQO EDGAR Research DB. Academic / research use.*