spb/firc0001_uqo
Public
1"""N3 - resolver composition, over-merge indicators, specification curve, ties, June->October2reconciliation (cluster B). Read-only; _m/_keyed rebuilt as TEMP tables from resolve.py:17-32.3"""4from __future__ import annotations5import json, sys6from pathlib import Path7sys.path.insert(0, str(Path(__file__).resolve().parent))8from common import *9sys.path.insert(0, str(Path(__file__).resolve().parent / "spid"))10import taxonomy as tx # snapshot copy (hash-verified in N0)1112EXAMPLE_PROJECT = "3e2705ba5b2dd27408b25f0145e34250" # stats.json example_project (Cheniere Corpus Christi Stage 3)13STOP = ["project", "program", "development", "expansion", "acquisition", "phase", "initiative", "facility", "construction"]14THRESHOLDS = [0.45, 0.6, 0.7, 0.8, 0.9]1516r = Run("resolver")17con = connect(SPID_DB)18tmin = con.execute("select min(extracted_at) from extracted_sections").fetchone()[0]19con.execute(f"""CREATE TEMP TABLE runmap AS SELECT section_id, CASE WHEN extracted_at = TIMESTAMP '{tmin}' THEN 'backfilled_june' ELSE 'october' END AS run FROM extracted_sections""")2021# ---- exact rebuild of _m / _keyed (resolve.py:17-32) --------------------------------------------22con.execute("""23CREATE TEMP TABLE _m AS24SELECT *,25 lower(coalesce(try(locations[1]), '')) AS loc1,26 regexp_extract(lower(regexp_replace(project_name, '[^A-Za-z ]', '', 'g')), '([a-z]{4,})', 1) AS name_tok27FROM project_mentions""")28con.execute("""29CREATE TEMP TABLE _keyed AS30SELECT *,31 cik || '|' || project_type || '|' || CASE WHEN loc1 <> '' THEN loc1 ELSE coalesce(nullif(name_tok,''),'general') END AS gkey,32 md5(cik || '|' || project_type || '|' || CASE WHEN loc1 <> '' THEN loc1 ELSE coalesce(nullif(name_tok,''),'general') END) AS project_id33FROM _m""")34V = {}35V["rebuilt_projects"] = con.execute("select count(distinct project_id) from _keyed").fetchone()[0]36V["stored_projects"] = con.execute("select count(*) from projects").fetchone()[0]37V["project_id_set_equal"] = con.execute("select count(*) from (select project_id from _keyed except select project_id from projects) union all select count(*) from (select project_id from projects except select distinct project_id from _keyed)").fetchall()38V["mentions"] = con.execute("select count(*) from _keyed").fetchone()[0]3940# ---- (a) key composition ----------------------------------------------------------------------41V["mentions_by_key_branch"] = con.execute("""42 select case when loc1<>'' then 'location' when nullif(name_tok,'') is not null then 'name_token' else 'general' end b, count(*) from _keyed group by 1""").fetchall()43V["projects_by_key_branch"] = con.execute("""44 select case when loc1<>'' then 'location' when nullif(name_tok,'') is not null then 'name_token' else 'general' end b, count(distinct project_id) from _keyed group by 1""").fetchall()45V["projects_keyed_general"] = con.execute("select count(distinct project_id) from _keyed where gkey like '%|general'").fetchone()[0]46V["mentions_keyed_general"] = con.execute("select count(*) from _keyed where gkey like '%|general'").fetchone()[0]47states = sorted(tx.US_STATES); countries = sorted(tx.COUNTRIES)48con.execute("CREATE TEMP TABLE geo AS SELECT unnest(?) AS loc, 'us_state' AS kind UNION ALL SELECT unnest(?), 'country'", [states, countries])49V["location_keyed_projects_by_geo_kind"] = con.execute("""50 with p as (select project_id, any_value(loc1) loc1 from _keyed where loc1<>'' group by 1)51 select coalesce(g.kind,'other_place') kind, count(*) from p left join geo g on p.loc1 = g.loc group by 1""").fetchall()52V["location_keyed_mentions_by_geo_kind"] = con.execute("""53 select coalesce(g.kind,'other_place') kind, count(*) from _keyed k left join geo g on k.loc1 = g.loc where k.loc1<>'' group by 1""").fetchall()54r.dump("top_name_tokens.csv", con, """55 with t as (select name_tok, count(*) mentions, count(distinct project_id) projects from _keyed where loc1='' and nullif(name_tok,'') is not null group by 1)56 select name_tok, mentions, projects, round(100.0*mentions/(select count(*) from _keyed where loc1=''),2) pct_of_tokenkeyed_mentions,57 round(100.0*projects/(select count(distinct project_id) from _keyed where loc1=''),2) pct_of_tokenkeyed_projects58 from t order by projects desc limit 50""")59r.dump("top_location_keys.csv", con, """60 select loc1, count(*) mentions, count(distinct project_id) projects from _keyed where loc1<>'' group by 1 order by 2 desc limit 50""")61r.dump("key_branch_by_type.csv", con, """62 select project_type, count(distinct project_id) projects,63 count(distinct project_id) filter (where loc1<>'') loc_keyed,64 count(distinct project_id) filter (where loc1='' and nullif(name_tok,'') is not null) token_keyed,65 count(distinct project_id) filter (where loc1='' and nullif(name_tok,'') is null) general_keyed66 from _keyed group by 1 order by 2 desc""")6768# ---- (b) over-merge indicators ----------------------------------------------------------------69con.execute("""70CREATE TEMP TABLE pstat AS71SELECT project_id, any_value(project_type) project_type, count(*) n_mentions,72 count(DISTINCT lower(trim(project_name))) n_names,73 count(DISTINCT lower(trim(coalesce(try(partners[1]), '')))) FILTER (WHERE len(partners)>0) n_partners,74 count(DISTINCT accession_number) n_filings, count(DISTINCT cik) n_ciks,75 count(DISTINCT lower(description)) n_desc76FROM _keyed GROUP BY 1""")77bucket = "case when n_mentions=1 then '1' when n_mentions=2 then '2' when n_mentions<=5 then '3-5' when n_mentions<=10 then '6-10' else '>10' end"78r.dump("overmerge_by_type.csv", con, f"""79 select project_type, count(*) projects, sum(n_mentions) mentions,80 round(100.0*count(*) filter (where n_names>=3)/count(*),2) pct_ge3_names,81 round(100.0*count(*) filter (where n_names>=2)/count(*),2) pct_ge2_names,82 round(100.0*count(*) filter (where n_partners>=3)/nullif(count(*) filter (where n_partners>=1),0),2) pct_ge3_partners_among_with_partner,83 count(*) filter (where n_partners>=3) n_ge3_partners,84 round(100.0*count(*) filter (where n_mentions>1 and n_names = n_mentions)/nullif(count(*) filter (where n_mentions>1),0),2) pct_multi_all_names_distinct85 from pstat group by 1 order by 2 desc""")86r.dump("overmerge_by_bucket.csv", con, f"""87 select {bucket} bucket, count(*) projects,88 round(100.0*count(*) filter (where n_names>=3)/count(*),2) pct_ge3_names,89 round(100.0*count(*) filter (where n_partners>=3)/nullif(count(*) filter (where n_partners>=1),0),2) pct_ge3_partners_among_with_partner,90 round(avg(n_names),2) mean_names91 from pstat group by 1 order by min(n_mentions)""")92r.dump("overmerge_ma_by_bucket.csv", con, f"""93 select {bucket} bucket, count(*) projects,94 count(*) filter (where n_partners>=1) with_partner,95 round(100.0*count(*) filter (where n_partners>=3)/nullif(count(*) filter (where n_partners>=1),0),2) pct_ge3_partners,96 round(100.0*count(*) filter (where n_partners>=2)/nullif(count(*) filter (where n_partners>=1),0),2) pct_ge2_partners,97 round(100.0*count(*) filter (where n_names>=3)/count(*),2) pct_ge3_names98 from pstat where project_type='ma_integration' group by 1 order by min(n_mentions)""")99# amount from a mention naming a different counterparty than the representative mention100V["amount_from_other_counterparty"] = con.execute("""101 with rep as (select project_id, arg_max(lower(trim(coalesce(try(partners[1]),''))), confidence) rep_partner from _keyed group by 1),102 amt as (select project_id, arg_max(lower(trim(coalesce(try(partners[1]),''))), amount_usd) amt_partner, max(amount_usd) mx from _keyed where amount_usd is not null group by 1)103 select count(*) filter (where rep_partner<>'' and amt_partner<>'' and rep_partner<>amt_partner) differing,104 count(*) filter (where rep_partner<>'' and amt_partner<>'') both_named, count(*) with_amount105 from amt join rep using(project_id)""").fetchone()106V["amount_from_other_counterparty_ma"] = con.execute("""107 with rep as (select project_id, any_value(project_type) t, arg_max(lower(trim(coalesce(try(partners[1]),''))), confidence) rep_partner from _keyed group by 1),108 amt as (select project_id, arg_max(lower(trim(coalesce(try(partners[1]),''))), amount_usd) amt_partner from _keyed where amount_usd is not null group by 1)109 select count(*) filter (where rep_partner<>'' and amt_partner<>'' and rep_partner<>amt_partner) differing,110 count(*) filter (where rep_partner<>'' and amt_partner<>'') both_named, count(*) with_amount111 from amt join rep using(project_id) where t='ma_integration'""").fetchone()112V["amount_from_other_name"] = con.execute("""113 with rep as (select project_id, arg_max(lower(trim(project_name)), confidence) rep_name from _keyed group by 1),114 amt as (select project_id, arg_max(lower(trim(project_name)), amount_usd) amt_name from _keyed where amount_usd is not null group by 1)115 select count(*) filter (where rep_name<>amt_name) differing, count(*) with_amount from amt join rep using(project_id)""").fetchone()116r.dump("example_provenance_keys.csv", con, """117 select project_id, cik, project_type, gkey, count(*) n, count(distinct lower(project_name)) n_names, list(distinct left(project_name,60)) names118 from _keyed where project_id in (select project_id from projects where project_id in (119 select project_id from project_timeline group by 1 having count(*) between 4 and 8) and total_amount_usd > 5e8 and project_type in ('plant_construction','data_center'))120 group by 1,2,3,4 order by n desc limit 30""")121122# ---- (c) specification curve ----------------------------------------------------------------------123stop_sql = ",".join("'" + s + "'" for s in STOP)124con.execute(f"""125CREATE TEMP TABLE km AS126SELECT mention_id, cik, project_type, confidence, loc1, name_tok, gkey,127 regexp_replace(trim(regexp_replace(lower(project_name), '[^a-z0-9 ]', '', 'g')), ' +', ' ', 'g') AS name_norm,128 list_filter(string_split(regexp_replace(lower(project_name), '[^a-z ]', '', 'g'), ' '), lambda x: length(x) >= 4) AS toks,129 list_filter(string_split(regexp_replace(lower(project_name), '[^a-z ]', '', 'g'), ' '), lambda x: length(x) >= 4 AND x NOT IN ({stop_sql})) AS toks_sw,130 trim(regexp_replace(regexp_replace(regexp_replace(loc1, '\\b(u\\.s\\.|usa|u\\.s\\.a\\.|united states of america)\\b', 'united states', 'g'), '[^a-z, ]', '', 'g'), ' +', ' ', 'g')) AS loc_norm131FROM _keyed""")132con.execute("""133CREATE TEMP TABLE keys AS134SELECT mention_id, confidence, project_type,135 cik || '|' || project_type AS k_cik_type,136 gkey AS k_current,137 cik || '|' || project_type || '|' || CASE WHEN loc1 <> '' THEN loc1 ELSE coalesce(toks_sw[1], 'general') END AS k_current_stopwords,138 cik || '|' || project_type || '|' || CASE WHEN loc_norm <> '' THEN trim(string_split(loc_norm, ',')[1]) ELSE coalesce(toks_sw[1], 'general') END AS k_city_normalised,139 cik || '|' || CASE WHEN loc1 <> '' THEN loc1 ELSE coalesce(nullif(name_tok,''), 'general') END AS k_type_dropped,140 cik || '|' || project_type || '|' || CASE WHEN loc1 <> '' THEN loc1 ELSE coalesce(toks_sw[1], 'general') || '|' || coalesce(toks_sw[2], '') END AS k_two_tokens,141 cik || '|' || project_type || '|' || coalesce(toks_sw[1], 'general') AS k_name_only,142 cik || '|' || project_type || '|' || name_norm || '|' || loc1 AS k_strict,143 mention_id AS k_mention_level144FROM km""")145KEYS = ["k_cik_type", "k_type_dropped", "k_current", "k_current_stopwords", "k_city_normalised", "k_name_only", "k_two_tokens", "k_strict", "k_mention_level"]146ex_mention = con.execute("select arg_max(mention_id, confidence) from _keyed where project_id = ?", [EXAMPLE_PROJECT]).fetchone()[0]147rows = []; bytype = []148for k in KEYS:149 for t in THRESHOLDS:150 q = f"""151 with g as (select {k} key, count(*) n, max(mention_id = '{ex_mention}')::int has_ex from keys where confidence >= {t} group by 1)152 select count(*) projects, sum(n) mentions, round(100.0*count(*) filter (where n=1)/count(*),2) single_pct,153 round(100.0*count(*) filter (where n>10)/count(*),2) gt10_pct, round(avg(n),3) mean_mentions,154 max(n) max_cluster, coalesce(max(n) filter (where has_ex=1),0) example_cluster_size155 from g"""156 row = con.execute(q).fetchone()157 rows.append((k, t) + tuple(row))158 bt = con.execute(f"""159 with g as (select {k} key, any_value(project_type) project_type, count(*) n from keys where confidence >= {t} group by 1)160 select project_type, count(*) n from g group by 1""").fetchall()161 bytype += [(k, t, pt, n) for pt, n in bt]162 print(k, t, row, flush=True)163r.write_csv("spec_curve.csv", ["key", "threshold", "projects", "mentions", "single_mention_pct", "gt10_mention_pct", "mean_mentions", "max_cluster", "example_cluster_size_cheniere"], rows)164r.write_csv("spec_curve_by_type.csv", ["key", "threshold", "project_type", "projects"], bytype)165cur = [x for x in rows if x[0] == "k_current" and x[1] == 0.45][0]166V["project_count_current"] = cur[2]167V["project_count_range_045"] = {k: [x[2] for x in rows if x[0] == k and x[1] == 0.45][0] for k in KEYS}168V["project_count_strict_045"] = V["project_count_range_045"]["k_strict"]169V["project_count_loose_045_cik_type"] = V["project_count_range_045"]["k_cik_type"]170V["example_mention"] = ex_mention171172# ---- (d) ties and idempotence ----------------------------------------------------------------------173D = {}174D["projects_with_tie_at_max_confidence"] = con.execute("""175 with mx as (select project_id, max(confidence) mc from _keyed group by 1)176 select count(*) from (select k.project_id from _keyed k join mx using(project_id) where k.confidence = mx.mc group by 1 having count(*)>1)""").fetchone()[0]177D["projects_with_tie_and_distinct_names"] = con.execute("""178 with mx as (select project_id, max(confidence) mc from _keyed group by 1)179 select count(*) from (select k.project_id from _keyed k join mx using(project_id) where k.confidence = mx.mc group by 1 having count(distinct project_name)>1)""").fetchone()[0]180D["projects_with_tie_and_distinct_descriptions"] = con.execute("""181 with mx as (select project_id, max(confidence) mc from _keyed group by 1)182 select count(*) from (select k.project_id from _keyed k join mx using(project_id) where k.confidence = mx.mc group by 1 having count(distinct description)>1)""").fetchone()[0]183D["projects_with_status_tie_on_last_seen"] = con.execute("""184 with mx as (select project_id, max(filing_date) md from _keyed group by 1)185 select count(*) from (select k.project_id from _keyed k join mx using(project_id) where k.filing_date = mx.md group by 1 having count(distinct status)>1)""").fetchone()[0]186D["mentions_at_confidence_1"] = con.execute("select count(*) from project_mentions where confidence = 1.0").fetchone()[0]187# stored projects vs rebuilt with the original (tie-ambiguous) SQL: how many representative fields differ188con.execute("""189CREATE TEMP TABLE rebuilt AS190SELECT project_id, arg_max(project_name, confidence) project_name, arg_max(status, filing_date) status, arg_max(description, confidence) description,191 max(amount_usd) total_amount_usd, count(*) n_mentions192FROM _keyed GROUP BY 1""")193D["rebuilt_vs_stored_diff"] = con.execute("""194 select count(*) filter (where r.project_name is distinct from p.project_name) name_diff,195 count(*) filter (where r.status is distinct from p.status) status_diff,196 count(*) filter (where r.description is distinct from p.description) desc_diff,197 count(*) filter (where r.total_amount_usd is distinct from p.total_amount_usd) amount_diff,198 count(*) filter (where r.n_mentions is distinct from p.n_mentions) n_diff, count(*) n199 from rebuilt r join projects p using(project_id)""").fetchone()200con.execute("""201CREATE TEMP TABLE rebuilt2 AS202SELECT project_id, arg_max(project_name, confidence) project_name, arg_max(status, filing_date) status, arg_max(description, confidence) description203FROM _keyed GROUP BY 1""")204D["original_sql_run_twice_diff"] = con.execute("""205 select count(*) filter (where a.project_name is distinct from b.project_name), count(*) filter (where a.status is distinct from b.status), count(*) filter (where a.description is distinct from b.description)206 from rebuilt a join rebuilt2 b using(project_id)""").fetchone()207det = """208SELECT project_id, project_name, status, description FROM (209 SELECT project_id,210 first_value(project_name) OVER (PARTITION BY project_id ORDER BY confidence DESC, mention_id) project_name,211 first_value(description) OVER (PARTITION BY project_id ORDER BY confidence DESC, mention_id) description,212 first_value(status) OVER (PARTITION BY project_id ORDER BY filing_date DESC, mention_id) status,213 row_number() OVER (PARTITION BY project_id ORDER BY mention_id) rn214 FROM _keyed) WHERE rn = 1"""215con.execute(f"CREATE TEMP TABLE det1 AS {det}")216con.execute(f"CREATE TEMP TABLE det2 AS {det}")217D["deterministic_tiebreak_run_twice_diff"] = con.execute("""218 select count(*) filter (where a.project_name is distinct from b.project_name), count(*) filter (where a.status is distinct from b.status), count(*) filter (where a.description is distinct from b.description)219 from det1 a join det2 b using(project_id)""").fetchone()220D["deterministic_vs_stored_diff"] = con.execute("""221 select count(*) filter (where a.project_name is distinct from p.project_name), count(*) filter (where a.status is distinct from p.status), count(*) filter (where a.description is distinct from p.description)222 from det1 a join projects p using(project_id)""").fetchone()223r.write_json("ties_idempotence.json", D)224225# ---- (e) June -> October reconciliation (S&P 500 CIKs) -------------------------------------------226con.execute("""CREATE TEMP TABLE kr AS SELECT k.*, rm.run, co.in_sp500, co.in_russell3000 FROM _keyed k LEFT JOIN runmap rm USING (section_id) LEFT JOIN src_companies co ON k.cik = co.cik""")227R = {}228R["sp500_ciks"] = con.execute("select count(*) from src_companies where in_sp500").fetchone()[0]229R["mentions_in_backfilled_block"] = con.execute("select count(*) from kr where run='backfilled_june'").fetchone()[0]230R["projects_from_backfilled_block_only"] = con.execute("select count(distinct project_id) from kr where run='backfilled_june'").fetchone()[0]231R["expected_june"] = {"mentions": 39930, "projects": 19227, "timeline_rows": 39930}232R["sp500_mentions_total"] = con.execute("select count(*) from kr where in_sp500").fetchone()[0]233R["sp500_projects_total"] = con.execute("select count(distinct project_id) from kr where in_sp500").fetchone()[0]234R["paper_sp500_projects_october"] = 27766235R["sp500_projects_by_run_composition"] = con.execute("""236 with p as (select project_id, max(run='backfilled_june')::int has_june, max(run='october')::int has_oct from kr where in_sp500 group by 1)237 select case when has_june=1 and has_oct=1 then 'both runs' when has_june=1 then 'june only' else 'october only' end c, count(*) from p group by 1""").fetchall()238R["sp500_mentions_by_run_form"] = con.execute("select run, form_type, count(*), count(distinct accession_number) from kr where in_sp500 group by 1,2 order by 1,2").fetchall()239R["sp500_mentions_filing_date_le_20260525_by_run"] = con.execute("select run, count(*), count(distinct project_id) from kr where in_sp500 and filing_date <= DATE '2026-05-25' group by 1").fetchall()240R["sp500_projects_filing_date_le_20260525"] = con.execute("select count(distinct project_id) from kr where in_sp500 and filing_date <= DATE '2026-05-25'").fetchone()[0]241R["backfilled_block_mentions_not_sp500"] = con.execute("select count(*) from kr where run='backfilled_june' and not coalesce(in_sp500,false)").fetchone()[0]242R["june_10q_accessions_in_block_ledger"] = con.execute(f"""243 select count(distinct e.accession_number) from extracted_sections e join src_filings f using(accession_number) where e.extracted_at = TIMESTAMP '{tmin}' and f.form_type='10-Q'""").fetchone()[0]244R["ledger_block_accessions_by_form"] = con.execute(f"""245 select f.form_type, count(distinct e.accession_number), count(*) from extracted_sections e join src_filings f using(accession_number) where e.extracted_at = TIMESTAMP '{tmin}' group by 1 order by 1""").fetchall()246R["sp500_october_ledger_by_form"] = con.execute(f"""247 select f.form_type, count(distinct e.accession_number), count(*), sum(e.n_mentions) from extracted_sections e join src_filings f using(accession_number) join src_companies co on f.cik=co.cik248 where e.extracted_at <> TIMESTAMP '{tmin}' and co.in_sp500 group by 1 order by 1""").fetchall()249R["sp500_block_ledger_by_form"] = con.execute(f"""250 select f.form_type, count(distinct e.accession_number), count(*), sum(e.n_mentions) from extracted_sections e join src_filings f using(accession_number) join src_companies co on f.cik=co.cik251 where e.extracted_at = TIMESTAMP '{tmin}' and co.in_sp500 group by 1 order by 1""").fetchall()252r.write_json("june_october_reconciliation.json", R)253r.dump("june_october_reconciliation.csv", con, """254 select coalesce(in_sp500,false) in_sp500, run, form_type, count(*) mentions, count(distinct project_id) projects_touched, count(distinct accession_number) filings, count(distinct cik) issuers255 from kr group by 1,2,3 order by 1,2,3""")256r.write_json("composition.json", V)257r.finish()258