JavaScript 58.4%
Python 26.8%
CSS 7.5%
Objective-C 3%
R 2.6%
HTML 1.7%
1"""PDB API — explorateur web + API REST de la base SPID (SEC Project Intelligence Database).23- /v1/* : API publique, clé requise (en-tête `X-API-Key` ou `Authorization: Bearer …`).4- /app/* : mêmes ressources pour l'interface web (même origine, sans clé — la clé n'est jamais envoyée au navigateur).5- / : interface web (SPA statique dans web/).6- /docs : documentation OpenAPI interactive.7"""8from __future__ import annotations910import csv11import io12import os13import threading14import time15from collections import defaultdict, deque16from pathlib import Path17from typing import Optional1819from fastapi import APIRouter, Depends, FastAPI, HTTPException, Query, Request20from fastapi.responses import FileResponse, JSONResponse, StreamingResponse21from fastapi.staticfiles import StaticFiles22from pydantic import BaseModel, Field2324from . import db2526ROOT = Path(__file__).resolve().parent.parent27WEB = ROOT / "web"28API_KEY = os.environ.get("API_KEY", "").strip()29PUBLIC_URL = os.environ.get("PUBLIC_URL", "https://www.pdb-api.co").rstrip("/")30RATE_PER_MIN = int(os.environ.get("RATE_PER_MIN", "240"))3132TYPE_LABELS = {33 "ma_integration": "Intégration M&A", "plant_construction": "Construction d'usine", "rd_program": "Programme de R&D",34 "product_launch": "Lancement de produit", "manufacturing_expansion": "Expansion manufacturière", "partnership": "Partenariat",35 "technology_deployment": "Déploiement technologique", "digital_transformation": "Transformation numérique",36 "infrastructure": "Infrastructure", "capex_program": "Programme de capex", "ai_initiative": "Initiative IA",37 "geographic_expansion": "Expansion géographique", "cost_reduction": "Réduction des coûts", "sustainability": "Durabilité",38 "energy_transition": "Transition énergétique", "supply_chain": "Chaîne d'approvisionnement", "drug_pipeline": "Pipeline de médicaments",39 "data_center": "Centre de données", "cloud_migration": "Migration infonuagique", "automation": "Automatisation",40 "erp_implementation": "Implantation ERP",41}42STATUSES = ["planned", "in_progress", "completed", "mentioned"]43PROJECT_SORTS = {44 "amount": "coalesce(total_amount_usd,0)", "mentions": "n_mentions", "filings": "n_filings", "first_seen": "first_seen",45 "last_seen": "last_seen", "confidence": "avg_confidence", "name": "project_name", "ticker": "ticker", "type": "project_type",46}47MENTION_SORTS = {"date": "filing_date", "amount": "coalesce(amount_usd,0)", "confidence": "confidence", "ticker": "ticker"}4849app = FastAPI(50 title="PDB API — SEC Project Intelligence Database",51 version="1.0.0",52 description=(53 "API REST de la base SPID : 19 227 projets stratégiques et 39 930 mentions extraits des filings SEC "54 "(10-K, 10-Q, 8-K) de 500 sociétés du S&P 500, 2010–2026. Toutes les routes `/v1/*` exigent une clé d'API "55 "dans l'en-tête `X-API-Key` (ou `Authorization: Bearer <clé>`). Les réponses paginées renvoient "56 "`{total, limit, offset, items}`."57 ),58 docs_url="/docs", redoc_url="/redoc", openapi_url="/openapi.json",59)6061# ---------------------------------------------------------------- sécurité62_buckets: dict[str, deque] = defaultdict(deque)63_bl = threading.Lock()646566def _rate_limit(request: Request, scope: str):67 ip = request.headers.get("x-forwarded-for", request.client.host if request.client else "?").split(",")[0].strip()68 key = f"{scope}:{ip}"69 now = time.time()70 with _bl:71 q = _buckets[key]72 while q and q[0] < now - 60:73 q.popleft()74 if len(q) >= RATE_PER_MIN:75 raise HTTPException(429, "Trop de requêtes : limite de %d par minute." % RATE_PER_MIN)76 q.append(now)777879def require_key(request: Request):80 _rate_limit(request, "v1")81 if not API_KEY:82 raise HTTPException(503, "API non configurée (clé absente côté serveur).")83 key = request.headers.get("x-api-key") or ""84 auth = request.headers.get("authorization") or ""85 if not key and auth.lower().startswith("bearer "):86 key = auth[7:].strip()87 if not key:88 key = request.query_params.get("api_key", "")89 if key != API_KEY:90 raise HTTPException(401, "Clé d'API manquante ou invalide (en-tête X-API-Key).")919293def require_same_origin(request: Request):94 _rate_limit(request, "app")95 sfs = request.headers.get("sec-fetch-site")96 if sfs and sfs not in ("same-origin", "none"):97 raise HTTPException(403, "Accès réservé à l'interface web.")98 origin = request.headers.get("origin") or request.headers.get("referer")99 host = request.headers.get("x-forwarded-host") or request.headers.get("host") or ""100 if origin:101 from urllib.parse import urlparse102 if urlparse(origin).netloc.split(":")[0] != host.split(":")[0]:103 raise HTTPException(403, "Accès réservé à l'interface web.")104105106# ---------------------------------------------------------------- helpers107def _like(v: str) -> str:108 return f"%{v.lower().strip()}%"109110111def _page(limit: int, offset: int) -> tuple[int, int]:112 return max(1, min(limit, 500)), max(0, offset)113114115def _project_filters(q, type_, sector, status, ticker, cik, location, tech, partner, min_amount, max_amount,116 year_from, year_to, min_conf, has_amount):117 where, params = [], []118 if q:119 where.append("(lower(project_name) like ? or lower(description) like ? or lower(company_name) like ? or lower(ticker) = ?)")120 params += [_like(q), _like(q), _like(q), q.lower().strip()]121 if type_:122 ts = [t.strip() for t in type_.split(",") if t.strip()]123 where.append("project_type in (" + ",".join("?" * len(ts)) + ")"); params += ts124 if sector:125 where.append("lower(sector) like ?"); params.append(_like(sector))126 if status:127 ss = [s.strip() for s in status.split(",") if s.strip()]128 where.append("status in (" + ",".join("?" * len(ss)) + ")"); params += ss129 if ticker:130 where.append("upper(ticker) = ?"); params.append(ticker.upper().strip())131 if cik:132 where.append("cik = ?"); params.append(cik.zfill(10))133 if location:134 where.append("lower(canonical_location) like ?"); params.append(_like(location))135 if tech:136 where.append("exists (select 1 from unnest(technologies) as u(t) where lower(t) like ?)"); params.append(_like(tech))137 if partner:138 where.append("project_id in (select md5(cik||'|'||project_type||'|'||coalesce(nullif(lower(coalesce(try(locations[1]),'')),''), regexp_extract(lower(regexp_replace(project_name,'[^A-Za-z ]','','g')),'([a-z]{4,})',1), 'general')) from project_mentions, unnest(partners) as u(p) where lower(p) like ?)")139 params.append(_like(partner))140 if min_amount is not None:141 where.append("total_amount_usd >= ?"); params.append(min_amount)142 if max_amount is not None:143 where.append("total_amount_usd <= ?"); params.append(max_amount)144 if year_from is not None:145 where.append("year(last_seen) >= ?"); params.append(year_from)146 if year_to is not None:147 where.append("year(first_seen) <= ?"); params.append(year_to)148 if min_conf is not None:149 where.append("avg_confidence >= ?"); params.append(min_conf)150 if has_amount:151 where.append("total_amount_usd > 0")152 return (" where " + " and ".join(where)) if where else "", params153154155def _mention_filters(q, type_, sector, status, ticker, cik, form, date_from, date_to, min_amount, min_conf, accession, section):156 where, params = [], []157 if q:158 where.append("(lower(project_name) like ? or lower(description) like ? or lower(company_name) like ?)")159 params += [_like(q)] * 3160 if type_:161 ts = [t.strip() for t in type_.split(",") if t.strip()]162 where.append("project_type in (" + ",".join("?" * len(ts)) + ")"); params += ts163 if sector:164 where.append("lower(sector) like ?"); params.append(_like(sector))165 if status:166 where.append("status = ?"); params.append(status)167 if ticker:168 where.append("upper(ticker) = ?"); params.append(ticker.upper().strip())169 if cik:170 where.append("cik = ?"); params.append(cik.zfill(10))171 if form:172 fs = [f.strip() for f in form.split(",") if f.strip()]173 where.append("form_type in (" + ",".join("?" * len(fs)) + ")"); params += fs174 if date_from:175 where.append("filing_date >= ?"); params.append(date_from)176 if date_to:177 where.append("filing_date <= ?"); params.append(date_to)178 if min_amount is not None:179 where.append("amount_usd >= ?"); params.append(min_amount)180 if min_conf is not None:181 where.append("confidence >= ?"); params.append(min_conf)182 if accession:183 where.append("accession_number = ?"); params.append(accession)184 if section:185 where.append("section_id = ?"); params.append(section)186 return (" where " + " and ".join(where)) if where else "", params187188189class SqlBody(BaseModel):190 sql: str = Field(..., description="Requête SQL DuckDB de lecture (SELECT / WITH / DESCRIBE / SUMMARIZE).")191 limit: int = Field(500, ge=1, le=5000, description="Nombre maximal de lignes renvoyées.")192193194# ---------------------------------------------------------------- routes (fabrique, montée deux fois)195def make_router(tag: str) -> APIRouter:196 r = APIRouter(tags=[tag])197198 @r.get("/stats", summary="Vue d'ensemble : compteurs et répartitions")199 def stats():200 ov = db.one("""select count(*) projects, sum(n_mentions) mentions, count(distinct cik) companies,201 count(distinct sector) sectors, count(*) filter (where total_amount_usd>0) with_amount,202 median(total_amount_usd) median_amount_usd, sum(total_amount_usd) total_amount_usd,203 min(first_seen) first_seen, max(last_seen) last_seen from projects""")204 ov["mentions"] = db.scalar("select count(*) from project_mentions")205 ov["filings"] = db.scalar("select count(distinct accession_number) from project_mentions")206 ov["kg_nodes"] = db.scalar("select count(*) from kg_nodes")207 ov["kg_edges"] = db.scalar("select count(*) from kg_edges")208 ov["sections_10k"] = db.scalar("select count(*) from spid_sections")209 by_type = db.rows("""select project_type, count(*) n, sum(total_amount_usd) amount_usd, median(total_amount_usd) median_usd,210 round(avg(avg_confidence),3) confidence from projects group by 1 order by n desc""")211 for t in by_type:212 t["label"] = TYPE_LABELS.get(t["project_type"], t["project_type"])213 return {214 "overview": ov,215 "by_type": by_type,216 "by_sector": db.rows("select sector, count(*) n, count(distinct cik) companies, sum(total_amount_usd) amount_usd from projects where sector is not null group by 1 order by n desc"),217 "by_status": db.rows("select status, count(*) n from projects group by 1 order by n desc"),218 "by_form": db.rows("select form_type, count(*) n from project_mentions group by 1 order by n desc"),219 "mentions_by_year": db.rows("""select year(filing_date) as yr, form_type, count(*) n from project_mentions220 where filing_date >= '2010-01-01' group by 1,2 order by 1,2"""),221 "projects_by_year": db.rows("select year(first_seen) as yr, count(*) n, sum(total_amount_usd) amount_usd from projects where first_seen >= '2010-01-01' group by 1 order by 1"),222 "themes_by_year": db.rows("""select year(filing_date) as yr, project_type, count(*) n from project_mentions223 where project_type in ('ai_initiative','data_center','cloud_migration','digital_transformation','sustainability','energy_transition')224 and filing_date >= '2010-01-01' group by 1,2 order by 1,2"""),225 "sector_type": db.rows("select sector, project_type, count(*) n from projects where sector is not null group by 1,2"),226 "top_companies": db.rows("select ticker, any_value(company_name) company_name, any_value(sector) sector, count(*) n, sum(total_amount_usd) amount_usd from projects group by 1 order by n desc limit 20"),227 "top_technologies": db.rows("select lower(trim(t)) technology, count(*) n from projects, unnest(technologies) as u(t) where t<>'' group by 1 order by n desc limit 25"),228 "top_locations": db.rows("select canonical_location as loc, count(*) n from projects where canonical_location is not null and canonical_location<>'' group by 1 order by n desc limit 25"),229 }230231 @r.get("/taxonomy", summary="Les 21 types de projets (libellés et effectifs)")232 def taxonomy():233 counts = {x["project_type"]: x for x in db.rows("select project_type, count(*) n, sum(total_amount_usd) amount_usd from projects group by 1")}234 return [{"project_type": k, "label": v, "n": counts.get(k, {}).get("n", 0), "amount_usd": counts.get(k, {}).get("amount_usd")} for k, v in TYPE_LABELS.items()]235236 @r.get("/sectors", summary="Secteurs GICS")237 def sectors():238 return db.rows("select sector, count(*) n, count(distinct cik) companies, sum(total_amount_usd) amount_usd from projects where sector is not null group by 1 order by n desc")239240 @r.get("/technologies", summary="Technologies citées")241 def technologies(q: Optional[str] = None, limit: int = Query(50, le=500)):242 w, p = ("where lower(t) like ?", [_like(q)]) if q else ("", [])243 return db.rows(f"select lower(trim(t)) technology, count(*) n from projects, unnest(technologies) as u(t) {w} {'and' if w else 'where'} t<>'' group by 1 order by n desc limit {int(limit)}", p)244245 @r.get("/locations", summary="Localisations canoniques")246 def locations(q: Optional[str] = None, limit: int = Query(50, le=500)):247 w, p = ("and lower(canonical_location) like ?", [_like(q)]) if q else ("", [])248 return db.rows(f"select canonical_location as loc, count(*) n, sum(total_amount_usd) amount_usd from projects where canonical_location is not null and canonical_location<>'' {w} group by 1 order by n desc limit {int(limit)}", p)249250 @r.get("/partners", summary="Partenaires cités dans les mentions")251 def partners(q: Optional[str] = None, limit: int = Query(50, le=500)):252 w, p = ("and lower(p) like ?", [_like(q)]) if q else ("", [])253 return db.rows(f"select lower(trim(p)) partner, any_value(p) as lbl, count(*) n, count(distinct cik) companies from project_mentions, unnest(partners) as u(p) where p<>'' {w} group by 1 order by n desc limit {int(limit)}", p)254255 @r.get("/projects", summary="Rechercher des projets (filtres + pagination)")256 def projects(257 q: Optional[str] = Query(None, description="Texte libre : nom, description, entreprise, ticker"),258 type: Optional[str] = Query(None, description="project_type (liste séparée par des virgules)"),259 sector: Optional[str] = None, status: Optional[str] = Query(None, description="planned,in_progress,completed,mentioned"),260 ticker: Optional[str] = None, cik: Optional[str] = None, location: Optional[str] = None, tech: Optional[str] = None,261 partner: Optional[str] = None,262 min_amount: Optional[float] = None, max_amount: Optional[float] = None,263 year_from: Optional[int] = None, year_to: Optional[int] = None, min_confidence: Optional[float] = None,264 has_amount: bool = False,265 sort: str = Query("mentions", description="amount | mentions | filings | first_seen | last_seen | confidence | name | ticker | type"),266 order: str = Query("desc", pattern="^(asc|desc)$"),267 limit: int = Query(50, ge=1, le=500), offset: int = Query(0, ge=0),268 ):269 w, p = _project_filters(q, type, sector, status, ticker, cik, location, tech, partner, min_amount, max_amount, year_from, year_to, min_confidence, has_amount)270 limit, offset = _page(limit, offset)271 col = PROJECT_SORTS.get(sort, "n_mentions")272 total = db.scalar(f"select count(*) from projects{w}", p)273 items = db.rows(f"select * from projects{w} order by {col} {order} nulls last, project_id limit {limit} offset {offset}", p)274 for it in items:275 it["type_label"] = TYPE_LABELS.get(it["project_type"], it["project_type"])276 return {"total": total, "limit": limit, "offset": offset, "items": items}277278 @r.get("/projects/export.csv", summary="Exporter les projets filtrés en CSV (max 50 000 lignes)")279 def projects_csv(280 q: Optional[str] = None, type: Optional[str] = None, sector: Optional[str] = None, status: Optional[str] = None,281 ticker: Optional[str] = None, cik: Optional[str] = None, location: Optional[str] = None, tech: Optional[str] = None,282 partner: Optional[str] = None, min_amount: Optional[float] = None, max_amount: Optional[float] = None,283 year_from: Optional[int] = None, year_to: Optional[int] = None, min_confidence: Optional[float] = None, has_amount: bool = False,284 ):285 w, p = _project_filters(q, type, sector, status, ticker, cik, location, tech, partner, min_amount, max_amount, year_from, year_to, min_confidence, has_amount)286 items = db.rows(f"select * from projects{w} order by n_mentions desc limit 50000", p)287288 def gen():289 buf = io.StringIO(); wr = csv.writer(buf)290 cols = list(items[0].keys()) if items else ["project_id"]291 wr.writerow(cols); yield buf.getvalue(); buf.seek(0); buf.truncate()292 for it in items:293 wr.writerow(["|".join(map(str, v)) if isinstance(v, list) else v for v in it.values()])294 yield buf.getvalue(); buf.seek(0); buf.truncate()295 return StreamingResponse(gen(), media_type="text/csv", headers={"Content-Disposition": "attachment; filename=spid_projects.csv"})296297 @r.get("/projects/{project_id}", summary="Fiche complète d'un projet (chronologie, mentions, graphe, similaires)")298 def project(project_id: str):299 pr = db.one("select * from projects where project_id = ?", [project_id])300 if not pr:301 raise HTTPException(404, "Projet introuvable.")302 pr["type_label"] = TYPE_LABELS.get(pr["project_type"], pr["project_type"])303 timeline = db.rows("select * from project_timeline where project_id = ? order by filing_date", [project_id])304 # mentions rattachées : même clé de résolution que spid/resolve.py305 mentions = db.rows("""with m as (select *, lower(coalesce(try(locations[1]),'')) loc1,306 regexp_extract(lower(regexp_replace(project_name,'[^A-Za-z ]','','g')),'([a-z]{4,})',1) name_tok from project_mentions)307 select * exclude (loc1, name_tok) from m308 where md5(cik||'|'||project_type||'|'||coalesce(nullif(loc1,''), nullif(name_tok,''), 'general')) = ?309 order by filing_date""", [project_id])310 edges = db.rows("""select e.rel, e.src, e.dst, n.node_type, n.label from kg_edges e join kg_nodes n on n.node_id = case when e.src = ? then e.dst else e.src end311 where e.src = ? or e.dst = ?""", ["P:" + project_id] * 3)312 sims = db.similar_ids(project_id, 8)313 similar = []314 if sims:315 ids = [s[0] for s in sims]316 found = {x["project_id"]: x for x in db.rows("select project_id, ticker, company_name, project_type, project_name, status, total_amount_usd, first_seen from projects where project_id in (" + ",".join("?" * len(ids)) + ")", ids)}317 for pid, sc in sims:318 if pid in found:319 found[pid]["score"] = round(sc, 4); found[pid]["type_label"] = TYPE_LABELS.get(found[pid]["project_type"]); similar.append(found[pid])320 return {"project": pr, "timeline": timeline, "mentions": mentions, "graph": edges, "similar": similar}321322 @r.get("/projects/{project_id}/similar", summary="Projets sémantiquement proches (vecteurs MiniLM stockés)")323 def project_similar(project_id: str, k: int = Query(10, le=50)):324 sims = db.similar_ids(project_id, k)325 if not sims and not db.one("select 1 from projects where project_id=?", [project_id]):326 raise HTTPException(404, "Projet introuvable.")327 ids = [s[0] for s in sims]328 found = {x["project_id"]: x for x in db.rows("select * from projects where project_id in (" + ",".join("?" * len(ids)) + ")", ids)} if ids else {}329 return [dict(found[pid], score=round(sc, 4)) for pid, sc in sims if pid in found]330331 @r.get("/mentions", summary="Rechercher des mentions (unité d'extraction, une par section et par projet)")332 def mentions(333 q: Optional[str] = None, type: Optional[str] = None, sector: Optional[str] = None, status: Optional[str] = None,334 ticker: Optional[str] = None, cik: Optional[str] = None, form: Optional[str] = Query(None, description="10-K,10-Q,8-K"),335 date_from: Optional[str] = None, date_to: Optional[str] = None, min_amount: Optional[float] = None,336 min_confidence: Optional[float] = None, accession: Optional[str] = None, section: Optional[str] = None,337 sort: str = Query("date", description="date | amount | confidence | ticker"), order: str = Query("desc", pattern="^(asc|desc)$"),338 limit: int = Query(50, ge=1, le=500), offset: int = Query(0, ge=0),339 ):340 w, p = _mention_filters(q, type, sector, status, ticker, cik, form, date_from, date_to, min_amount, min_confidence, accession, section)341 limit, offset = _page(limit, offset)342 col = MENTION_SORTS.get(sort, "filing_date")343 total = db.scalar(f"select count(*) from project_mentions{w}", p)344 items = db.rows(f"""select *, md5(cik||'|'||project_type||'|'||coalesce(nullif(lower(coalesce(try(locations[1]),'')),''),345 nullif(regexp_extract(lower(regexp_replace(project_name,'[^A-Za-z ]','','g')),'([a-z]{{4,}})',1),''), 'general')) project_id346 from project_mentions{w} order by {col} {order} nulls last, mention_id limit {limit} offset {offset}""", p)347 for it in items:348 it["type_label"] = TYPE_LABELS.get(it["project_type"], it["project_type"])349 return {"total": total, "limit": limit, "offset": offset, "items": items}350351 @r.get("/mentions/{mention_id}", summary="Une mention et sa section source (10-K)")352 def mention(mention_id: str):353 m = db.one("select * from project_mentions where mention_id = ?", [mention_id])354 if not m:355 raise HTTPException(404, "Mention introuvable.")356 m["type_label"] = TYPE_LABELS.get(m["project_type"], m["project_type"])357 m["project_id"] = db.scalar("""select md5(cik||'|'||project_type||'|'||coalesce(nullif(lower(coalesce(try(locations[1]),'')),''),358 nullif(regexp_extract(lower(regexp_replace(project_name,'[^A-Za-z ]','','g')),'([a-z]{4,})',1),''), 'general'))359 from project_mentions where mention_id = ?""", [mention_id])360 sec = db.one("select section_id, accession_number, section_name, word_count from spid_sections where section_id = ?", [m["section_id"]])361 return {"mention": m, "section": sec, "section_text_url": f"/sections/{m['section_id']}" if sec else None}362363 @r.get("/sections/{section_id}", summary="Texte d'une section 10-K ingérée par SPID")364 def section(section_id: str, highlight: Optional[str] = Query(None, description="Mot à repérer (renvoie les positions)")):365 s = db.one("select * from spid_sections where section_id = ?", [section_id])366 if not s:367 raise HTTPException(404, "Section introuvable (seules les sections 10-K sont stockées dans la base ; les sections 10-Q/8-K restent dans le corpus parquet amont).")368 if highlight:369 import re370 s["highlights"] = [m.start() for m in re.finditer(re.escape(highlight), s["text"], re.IGNORECASE)][:200]371 return s372373 @r.get("/companies", summary="Entreprises et taille de leur portefeuille de projets")374 def companies(q: Optional[str] = None, sector: Optional[str] = None, sort: str = Query("projects", description="projects | amount | mentions | ticker"),375 order: str = Query("desc", pattern="^(asc|desc)$"), limit: int = Query(100, ge=1, le=500), offset: int = Query(0, ge=0)):376 where, p = [], []377 if q:378 where.append("(lower(company_name) like ? or lower(ticker) like ?)"); p += [_like(q), _like(q)]379 if sector:380 where.append("lower(sector) like ?"); p.append(_like(sector))381 w = (" where " + " and ".join(where)) if where else ""382 col = {"projects": "n_projects", "amount": "coalesce(amount_usd,0)", "mentions": "n_mentions", "ticker": "ticker"}.get(sort, "n_projects")383 limit, offset = _page(limit, offset)384 base = f"""with c as (select cik, any_value(ticker) ticker, any_value(company_name) company_name, any_value(sector) sector,385 count(*) n_projects, sum(n_mentions) n_mentions, sum(total_amount_usd) amount_usd, min(first_seen) first_seen, max(last_seen) last_seen,386 count(distinct project_type) n_types from projects group by cik) select * from c{w}"""387 total = db.scalar(f"select count(*) from ({base})", p)388 items = db.rows(f"{base} order by {col} {order} nulls last limit {limit} offset {offset}", p)389 return {"total": total, "limit": limit, "offset": offset, "items": items}390391 @r.get("/companies/{ident}", summary="Profil d'une entreprise (ticker ou CIK)")392 def company(ident: str):393 ident = ident.strip()394 cond, val = ("cik = ?", ident.zfill(10)) if ident.isdigit() else ("upper(ticker) = ?", ident.upper())395 prof = db.one(f"""select cik, any_value(ticker) ticker, any_value(company_name) company_name, any_value(sector) sector,396 count(*) n_projects, sum(n_mentions) n_mentions, sum(total_amount_usd) amount_usd, min(first_seen) first_seen, max(last_seen) last_seen397 from projects where {cond} group by cik""", [val])398 if not prof:399 raise HTTPException(404, "Entreprise introuvable.")400 cik = prof["cik"]401 return {402 "company": prof,403 "by_type": [dict(x, label=TYPE_LABELS.get(x["project_type"])) for x in db.rows("select project_type, count(*) n, sum(total_amount_usd) amount_usd from projects where cik=? group by 1 order by n desc", [cik])],404 "by_status": db.rows("select status, count(*) n from projects where cik=? group by 1", [cik]),405 "by_year": db.rows("select year(filing_date) as yr, count(*) n from project_mentions where cik=? and filing_date>='2010-01-01' group by 1 order by 1", [cik]),406 "by_form": db.rows("select form_type, count(*) n from project_mentions where cik=? group by 1", [cik]),407 "locations": db.rows("select canonical_location as loc, count(*) n from projects where cik=? and canonical_location<>'' group by 1 order by n desc limit 15", [cik]),408 "technologies": db.rows("select lower(t) technology, count(*) n from projects, unnest(technologies) as u(t) where cik=? group by 1 order by n desc limit 15", [cik]),409 "partners": db.rows("select any_value(p) partner, count(*) n from project_mentions, unnest(partners) as u(p) where cik=? and p<>'' group by lower(p) order by n desc limit 15", [cik]),410 "projects": [dict(x, type_label=TYPE_LABELS.get(x["project_type"])) for x in db.rows("select * from projects where cik=? order by n_mentions desc, total_amount_usd desc nulls last limit 500", [cik])],411 }412413 @r.get("/graph/search", summary="Chercher un nœud du graphe (entreprise, projet, lieu, technologie, partenaire)")414 def graph_search(q: str, type: Optional[str] = Query(None, description="Company | Project | Location | Technology | Partner"), limit: int = Query(30, le=200)):415 w, p = "where lower(label) like ?", [_like(q)]416 if type:417 w += " and node_type = ?"; p.append(type)418 return db.rows(f"""select n.node_id, n.node_type, n.label, n.props, (select count(*) from kg_edges e where e.src=n.node_id or e.dst=n.node_id) degree419 from kg_nodes n {w} order by degree desc limit {int(limit)}""", p)420421 @r.get("/graph/node/{node_id:path}", summary="Un nœud et son voisinage")422 def graph_node(node_id: str, limit: int = Query(200, le=2000)):423 n = db.one("select * from kg_nodes where node_id = ?", [node_id])424 if not n:425 raise HTTPException(404, "Nœud introuvable.")426 edges = db.rows(f"""select e.rel, e.src, e.dst, e.props, case when e.src = ? then 'out' else 'in' end direction,427 m.node_type neighbor_type, m.label neighbor_label, m.node_id neighbor_id, m.props neighbor_props428 from kg_edges e join kg_nodes m on m.node_id = case when e.src = ? then e.dst else e.src end429 where e.src = ? or e.dst = ? limit {int(limit)}""", [node_id] * 4)430 degree = db.scalar("select count(*) from kg_edges where src = ? or dst = ?", [node_id, node_id])431 return {"node": n, "degree": degree, "edges": edges}432433 @r.get("/search/semantic", summary="Recherche sémantique en texte libre (modèle all-MiniLM-L6-v2 côté serveur)")434 def semantic(q: str, k: int = Query(20, le=100), type: Optional[str] = None, sector: Optional[str] = None):435 from . import semantic as sem436 try:437 vec = sem.encode(q)438 except sem.Unavailable as e:439 raise HTTPException(501, str(e))440 hits = db.nearest_to_vector(vec, k * 5)441 ids = [h[0] for h in hits]442 where, p = ["project_id in (" + ",".join("?" * len(ids)) + ")"], list(ids)443 if type:444 where.append("project_type = ?"); p.append(type)445 if sector:446 where.append("lower(sector) like ?"); p.append(_like(sector))447 found = {x["project_id"]: x for x in db.rows("select * from projects where " + " and ".join(where), p)}448 out = []449 for pid, sc in hits:450 if pid in found:451 out.append(dict(found[pid], score=round(sc, 4), type_label=TYPE_LABELS.get(found[pid]["project_type"])))452 if len(out) >= k:453 break454 return {"query": q, "items": out}455456 @r.get("/schema", summary="Tables et colonnes de la base")457 def schema():458 return db.schema()459460 @r.post("/sql", summary="Bac à sable SQL en lecture seule (DuckDB)")461 def sql(body: SqlBody):462 try:463 return db.run_sql(body.sql, body.limit)464 except TimeoutError as e:465 raise HTTPException(408, str(e))466 except ValueError as e:467 raise HTTPException(400, str(e))468469 return r470471472app.include_router(make_router("API v1 (clé requise)"), prefix="/v1", dependencies=[Depends(require_key)])473app.include_router(make_router("Interface web (même origine)"), prefix="/app", dependencies=[Depends(require_same_origin)], include_in_schema=False)474475476@app.get("/health", include_in_schema=False)477@app.get("/v1/health", summary="État du service (sans clé)", tags=["Service"])478def health():479 try:480 n = db.scalar("select count(*) from projects")481 return {"status": "ok", "projects": n, "db": os.path.basename(db.DB_PATH), "semantic": _semantic_state()}482 except Exception as e: # noqa: BLE001483 return JSONResponse({"status": "error", "detail": str(e)[:200]}, status_code=503)484485486def _semantic_state():487 try:488 from . import semantic as sem489 return sem.state()490 except Exception:491 return "unavailable"492493494@app.get("/v1/meta", summary="Métadonnées de l'API (routes, limites, exemples)", tags=["Service"])495def meta():496 return {497 "name": "PDB API", "version": app.version, "base_url": PUBLIC_URL + "/v1",498 "auth": "En-tête X-API-Key: <clé> (ou Authorization: Bearer <clé>). La clé est fournie par l'équipe UQO ; elle n'est publiée nulle part sur le site.",499 "rate_limit_per_minute": RATE_PER_MIN, "pagination": "limit (≤500) / offset ; réponses {total, limit, offset, items}",500 "routes": [f"{r.methods and list(r.methods)[0]} {r.path}" for r in app.routes if getattr(r, "path", "").startswith("/v1")],501 "docs": PUBLIC_URL + "/docs",502 }503504505@app.on_event("startup")506def _warm():507 def w():508 try:509 db.connect(); db.schema(); db.embeddings()510 except Exception as e: # noqa: BLE001511 print("warmup:", e)512 threading.Thread(target=w, daemon=True).start()513514515# ---------------------------------------------------------------- statique516if (WEB / "report.pdf").exists():517 @app.get("/report.pdf", include_in_schema=False)518 def report():519 return FileResponse(WEB / "report.pdf", media_type="application/pdf")520521app.mount("/", StaticFiles(directory=str(WEB), html=True), name="web")522