spb/uqo-research-db
Public
1# ============================================================2# SPID — command line interface3#4# python -m spid extract [--backend llm|rules] [--tickers NVDA,AAPL]5# [--ciks ...] [--forms 10-K,10-Q,8-K]6# [--limit-sections N] [--workers 8]7# python -m spid resolve8# python -m spid graph9# python -m spid embed10# python -m spid analytics11# python -m spid search "data center" --type data_center --min-amount 5e812# python -m spid semantic "AI projects launched by banks"13# python -m spid project <project_id>14# python -m spid all [extraction flags] # full pipeline15# python -m spid stats16# ============================================================17from __future__ import annotations1819import argparse20import json21import sys2223from spid.config import get_settings24from spid.store import Store252627def _fmt_usd(v) -> str:28 if not v:29 return "-"30 for unit, div in (("B", 1e9), ("M", 1e6), ("K", 1e3)):31 if v >= div:32 return f"${v/div:.2f}{unit}"33 return f"${v:.0f}"343536def _tickers_to_ciks(store: Store, tickers: list[str]) -> list[str]:37 up = [t.upper() for t in tickers]38 q = ",".join("'" + t + "'" for t in up)39 rows = store.con.execute(40 f"SELECT DISTINCT cik FROM src_filings WHERE upper(ticker) IN ({q})").fetchall()41 return [r[0] for r in rows]424344def _print_table(rows: list[dict], cols: list[str], money_cols=()):45 if not rows:46 print(" (no rows)"); return47 widths = {c: max(len(c), *(len(_cell(r.get(c), c, money_cols)) for r in rows)) for c in cols}48 print(" " + " ".join(c.ljust(widths[c]) for c in cols))49 print(" " + " ".join("-" * widths[c] for c in cols))50 for r in rows:51 print(" " + " ".join(_cell(r.get(c), c, money_cols).ljust(widths[c]) for c in cols))525354def _cell(v, col, money_cols) -> str:55 if col in money_cols:56 return _fmt_usd(v or 0)57 if isinstance(v, list):58 return ", ".join(str(x) for x in v[:3])59 if v is None:60 return "-"61 return str(v)[:48]626364# ---- commands ---------------------------------------------------------65def cmd_extract(args, store):66 from spid.pipeline import run_extraction67 ciks = None68 if args.tickers:69 ciks = _tickers_to_ciks(store, args.tickers.split(","))70 elif args.ciks:71 ciks = [c.zfill(10) for c in args.ciks.split(",")]72 elif args.sample:73 rows = store.con.execute(74 f"SELECT DISTINCT cik FROM src_filings ORDER BY cik LIMIT {int(args.sample)}").fetchall()75 ciks = [r[0] for r in rows]76 forms = args.forms.split(",") if args.forms else None77 res = run_extraction(store, backend=args.backend, ciks=ciks, forms=forms,78 limit_sections=args.limit_sections, workers=args.workers,79 min_confidence=args.min_conf)80 print(json.dumps(res, indent=2))818283def cmd_ingest(args, store):84 from spid.ingest import ingest_10k85 ciks = None86 if args.tickers:87 ciks = _tickers_to_ciks(store, args.tickers.split(","))88 elif args.ciks:89 ciks = [c.zfill(10) for c in args.ciks.split(",")]90 print(json.dumps(ingest_10k(store, ciks=ciks, limit=args.limit), indent=2))919293def cmd_backfill(args, store):94 n = store.backfill_extracted(backend=args.backend or "llm")95 print(json.dumps({"extracted_sections": n}, indent=2))969798def cmd_pending(args, store):99 """How many sections still need extraction (and the estimated gpt-4o-mini cost)."""100 rows = store.con.execute("""101 SELECT form_type, COUNT(*) AS n, SUM(word_count) AS words102 FROM v_sections WHERE word_count >= 30103 AND section_id NOT IN (SELECT section_id FROM extracted_sections)104 GROUP BY form_type ORDER BY form_type""").fetchall()105 total = sum(r[1] for r in rows)106 print(json.dumps({"pending_by_form": [{"form": r[0], "sections": r[1], "words": int(r[2] or 0)} for r in rows],107 "pending_total": total,108 "est_cost_usd_gpt4o_mini": round(total * 0.0008, 2)}, indent=2))109110111def cmd_resolve(args, store):112 from spid.resolve import resolve_projects113 print(json.dumps(resolve_projects(store), indent=2))114115116def cmd_graph(args, store):117 from spid.graph import build_graph, export118 res = build_graph(store)119 res.update(export(store, get_settings().out_dir / "exports"))120 print(json.dumps(res, indent=2, default=str))121122123def cmd_embed(args, store):124 from spid.embed import build_embeddings125 print(json.dumps(build_embeddings(store), indent=2))126127128def cmd_analytics(args, store):129 from spid import analytics130 rep = analytics.full_report(store)131 print("\n=== OVERVIEW ===")132 for k, v in rep["overview"].items():133 print(f" {k:28s}: {_fmt_usd(v) if 'usd' in k else v}")134 print("\n=== PROJECTS BY TYPE ==="); _print_table(rep["by_type"], ["project_type","projects","total_usd","avg_conf"], ("total_usd",))135 print("\n=== BY SECTOR ==="); _print_table(rep["by_sector"][:12], ["sector","projects","total_usd"], ("total_usd",))136 print("\n=== BY TECHNOLOGY ==="); _print_table(rep["by_technology"][:12], ["technology","projects"])137 print("\n=== BY GEOGRAPHY ==="); _print_table(rep["by_geography"][:12], ["location","projects","total_usd"], ("total_usd",))138 print("\n=== CAPEX BY YEAR ==="); _print_table(rep["capex_by_year"], ["year","projects_started","announced_usd"], ("announced_usd",))139 print("\n=== AI ADOPTION (top) ==="); _print_table(rep["ai_adoption"][:12], ["ticker","sector","ai_projects","total_usd"], ("total_usd",))140 print("\n=== TOP COMPANIES ==="); _print_table(rep["top_companies"][:12], ["ticker","company_name","projects","total_usd"], ("total_usd",))141 if args.json:142 (get_settings().out_dir / "exports" / "analytics.json").write_text(json.dumps(rep, indent=2, default=str))143 print("\n(written exports/analytics.json)")144145146def cmd_search(args, store):147 from spid.search import search_projects148 rows = search_projects(store, keyword=args.query, project_type=args.type,149 sector=args.sector, technology=args.tech, location=args.location,150 ticker=args.ticker, min_amount=args.min_amount,151 year_from=args.year_from, year_to=args.year_to,152 status=args.status, limit=args.limit)153 print(f"{len(rows)} projects:")154 _print_table(rows, ["ticker","project_type","project_name","status","total_amount_usd","n_filings","canonical_location"], ("total_amount_usd",))155156157def cmd_semantic(args, store):158 from spid.embed import semantic_search159 rows = semantic_search(store, args.query, k=args.limit)160 print(f"Top {len(rows)} semantic matches for: {args.query!r}")161 _print_table(rows, ["score","ticker","project_type","project_name","status","total_amount_usd"], ("total_amount_usd",))162163164def cmd_project(args, store):165 from spid.search import get_project166 p = get_project(store, args.project_id)167 if not p:168 print("not found"); return169 print(json.dumps({k: v for k, v in p.items() if k != "timeline"}, indent=2, default=str))170 print("\nTIMELINE:")171 _print_table(p["timeline"], ["filing_date","form_type","status","amount_usd","confidence","snippet"], ("amount_usd",))172173174def cmd_stats(args, store):175 for t in ["project_mentions","projects","project_timeline","kg_nodes","kg_edges","project_embeddings"]:176 try:177 print(f" {t:22s}: {store.count(t):,}")178 except Exception:179 print(f" {t:22s}: (missing)")180181182def cmd_all(args, store):183 cmd_extract(args, store)184 cmd_resolve(args, store)185 cmd_graph(args, store)186 try:187 cmd_embed(args, store)188 except Exception as e: # noqa: BLE001189 print(f"[embed skipped: {e}]")190 cmd_analytics(args, store)191192193def main(argv=None):194 ap = argparse.ArgumentParser(prog="spid", description="SEC Project Intelligence Database")195 sub = ap.add_subparsers(dest="cmd", required=True)196197 def add_extract_flags(p):198 p.add_argument("--backend", choices=["rules", "llm"], default=None)199 p.add_argument("--tickers", help="comma list e.g. NVDA,AAPL")200 p.add_argument("--ciks", help="comma list of CIKs")201 p.add_argument("--sample", type=int, help="first N companies by CIK")202 p.add_argument("--forms", help="comma list e.g. 10-K,10-Q,8-K")203 p.add_argument("--limit-sections", type=int, default=None)204 p.add_argument("--workers", type=int, default=8)205 p.add_argument("--min-conf", type=float, default=None)206207 pe = sub.add_parser("extract"); add_extract_flags(pe)208 pb = sub.add_parser("backfill-extracted"); pb.add_argument("--backend", default="llm")209 sub.add_parser("pending")210 pin = sub.add_parser("ingest")211 pin.add_argument("--tickers"); pin.add_argument("--ciks"); pin.add_argument("--limit", type=int, default=None)212 sub.add_parser("resolve")213 sub.add_parser("graph")214 sub.add_parser("embed")215 pa = sub.add_parser("analytics"); pa.add_argument("--json", action="store_true")216 pall = sub.add_parser("all"); add_extract_flags(pall); pall.add_argument("--json", action="store_true")217218 ps = sub.add_parser("search")219 ps.add_argument("query", nargs="?", default=None)220 ps.add_argument("--type"); ps.add_argument("--sector"); ps.add_argument("--tech")221 ps.add_argument("--location"); ps.add_argument("--ticker"); ps.add_argument("--status")222 ps.add_argument("--min-amount", type=float, dest="min_amount")223 ps.add_argument("--year-from", type=int, dest="year_from")224 ps.add_argument("--year-to", type=int, dest="year_to")225 ps.add_argument("--limit", type=int, default=25)226227 psem = sub.add_parser("semantic"); psem.add_argument("query"); psem.add_argument("--limit", type=int, default=10)228 pp = sub.add_parser("project"); pp.add_argument("project_id")229 sub.add_parser("stats")230231 args = ap.parse_args(argv)232 store = Store()233 try:234 {235 "extract": cmd_extract, "ingest": cmd_ingest, "resolve": cmd_resolve, "graph": cmd_graph,236 "embed": cmd_embed, "analytics": cmd_analytics, "search": cmd_search,237 "semantic": cmd_semantic, "project": cmd_project, "stats": cmd_stats,238 "all": cmd_all, "backfill-extracted": cmd_backfill, "pending": cmd_pending,239 }[args.cmd](args, store)240 finally:241 store.close()242243244if __name__ == "__main__":245 sys.exit(main())246