spb/uqo-research-db

1# ============================================================2# SPID — project resolution & timelines3#4# Merges many mentions (across filings/years) into a single5# Project record + a chronological timeline.  Pure DuckDB SQL6# so it scales to millions of mentions.7# ============================================================8from __future__ import annotations910from spid.config import get_logger11from spid.store import Store1213log = get_logger("resolve")1415# Grouping key: a project is identified by (company, type, anchor)16# where anchor = first location if present, else first salient name token.17_BUILD_PROJECTS = """18CREATE OR REPLACE TEMP TABLE _m AS19SELECT *,20   lower(coalesce(try(locations[1]), '')) AS loc1,21   regexp_extract(22       lower(regexp_replace(project_name, '[^A-Za-z ]', '', 'g')),23       '([a-z]{4,})', 1) AS name_tok24FROM project_mentions;2526CREATE OR REPLACE TEMP TABLE _keyed AS27SELECT *,28   cik || '|' || project_type || '|' ||29   CASE WHEN loc1 <> '' THEN loc1 ELSE coalesce(nullif(name_tok,''),'general') END AS gkey,30   md5(cik || '|' || project_type || '|' ||31       CASE WHEN loc1 <> '' THEN loc1 ELSE coalesce(nullif(name_tok,''),'general') END) AS project_id32FROM _m;3334DELETE FROM projects;35INSERT INTO projects36SELECT37   project_id,38   any_value(cik)            AS cik,39   any_value(ticker)         AS ticker,40   any_value(company_name)   AS company_name,41   any_value(sector)         AS sector,42   any_value(project_type)   AS project_type,43   -- representative name: the highest-confidence mention's name44   arg_max(project_name, confidence) AS project_name,45   nullif(any_value(loc1), '') AS canonical_location,46   list_distinct(flatten(list(technologies))) AS technologies,47   -- latest-by-date status48   arg_max(status, filing_date) AS status,49   max(amount_usd)           AS total_amount_usd,50   count(*)                  AS n_mentions,51   count(DISTINCT accession_number) AS n_filings,52   min(filing_date)          AS first_seen,53   max(filing_date)          AS last_seen,54   min(list_min(years))      AS first_year,55   max(list_max(years))      AS last_year,56   round(avg(confidence), 3) AS avg_confidence,57   arg_max(description, confidence) AS description58FROM _keyed59GROUP BY project_id;6061DELETE FROM project_timeline;62INSERT INTO project_timeline63SELECT project_id, accession_number, filing_date, form_type, status, amount_usd,64       confidence, left(description, 300) AS snippet65FROM _keyed66ORDER BY project_id, filing_date;67"""686970def resolve_projects(store: Store) -> dict:71    store.con.execute(_BUILD_PROJECTS)72    n_proj = store.count("projects")73    n_tl = store.count("project_timeline")74    log.info("Resolved %d projects from mentions (%d timeline rows).", n_proj, n_tl)75    return {"projects": n_proj, "timeline_rows": n_tl}76