spb/firc0001_uqo

1"""N5 - focus-window coverage: what share of every ledger section the model actually saw (cluster E).23Imports _focus / _PROJECT_GATE from the hash-verified snapshot (pure functions). For each of the41,123,098 ledger sections: length, Python gate pass, gate hits, shown characters (replicated span5logic, asserted equal to the real _focus output), offset of the last shown character, hits shown,614,000-character message cut, capex vocabulary inside/outside the shown windows. Aggregates by form x7item; n_mentions vs length (plateau); <30-word / incorporated-by-reference 10-K MD&As by year;8median words of ledger 10-K MD&A (the 9,905 figure). Read-only; per-section features kept on the node.9"""10from __future__ import annotations11import json, re, sys, time12from pathlib import Path13import multiprocessing as mp14sys.path.insert(0, str(Path(__file__).resolve().parent))15from common import *16sys.path.insert(0, str(Path(__file__).resolve().parent))17from spid.extract.llm import _focus          # snapshot copy18from spid.extract.rules import _PROJECT_GATE  # snapshot copy1920BUDGET = 900021CAPEX = re.compile(r"capital expenditure|liquidity and capital resources|commitment", re.I)2223def spans_like_focus(text: str, hits):24    """Replicates _focus (llm.py:55-80) and returns the chunks [(a,b)] actually emitted."""25    L = len(text)26    if L <= BUDGET:27        return [(0, L)], True28    spans = []29    for s, e in hits:30        spans.append((max(0, s - 220), min(L, e + 380)))31        if sum(b - a for a, b in spans) > BUDGET * 1.4:32            break33    if not spans:34        return [(0, BUDGET)], True35    spans.sort()36    merged = [spans[0]]37    for a, b in spans[1:]:38        if a <= merged[-1][1]:39            merged[-1] = (merged[-1][0], max(merged[-1][1], b))40        else:41            merged.append((a, b))42    chunks, total = [], 043    for a, b in merged:44        chunks.append((a, b)); total += b - a45        if total >= BUDGET:46            break47    return chunks, False4849def feat(item):50    sid, text = item51    L = len(text)52    hits = [(m.start(), m.end()) for m in _PROJECT_GATE.finditer(text)]53    nh = len(hits)54    chunks, whole = spans_like_focus(text, hits)55    shown = sum(b - a for a, b in chunks)56    last = chunks[-1][1]57    focus = _focus(text)58    eq = ("\n...\n".join(text[a:b] for a, b in chunks) == focus)59    # hits inside the chunks60    hs = 0; ci = 061    for s, _ in hits:62        while ci < len(chunks) and s >= chunks[ci][1]:63            ci += 164        if ci < len(chunks) and chunks[ci][0] <= s < chunks[ci][1]:65            hs += 166    cap_all = 0; cap_in = 067    if L > BUDGET:68        for m in CAPEX.finditer(text):69            cap_all += 170            s = m.start()71            if any(a <= s < b for a, b in chunks):72                cap_in += 173    return (sid, L, nh > 0, nh, shown, last, hs, len(focus), eq, cap_all, cap_in, len(chunks))7475def main():76    r = Run("focus")77    con = connect(SPID_DB)78    con.execute("CREATE TEMP TABLE led AS SELECT section_id FROM extracted_sections")79    q = """SELECT s.section_id, s.text FROM src_sections s JOIN led USING (section_id)80           UNION ALL SELECT s.section_id, s.text FROM spid_sections s JOIN led USING (section_id)"""81    cur = con.execute(q)82    rows_out = []; t0 = time.time(); done = 0; chars = 083    ctx = mp.get_context("fork")84    with ctx.Pool(4) as pool:85        while True:86            rows = cur.fetchmany(4000)87            if not rows:88                break89            chars += sum(len(t) for _, t in rows)90            rows_out.extend(pool.imap_unordered(feat, rows, chunksize=100))91            done += len(rows)92            if done % 100000 < 4000:93                print(f"  {done} sections, {chars/1e9:.2f} GB, {time.time()-t0:.0f}s", flush=True)94    r.notes["sections_streamed"] = done; r.notes["chars_streamed"] = chars95    import pyarrow as pa, pyarrow.parquet as pq96    names = ["section_id", "nchar", "gate_py", "n_hits", "shown_chars", "last_offset", "hits_shown", "focus_len", "replica_eq", "capex_all", "capex_shown", "n_chunks"]97    tbl = pa.table({n: [row[i] for row in rows_out] for i, n in enumerate(names)})98    pq.write_table(tbl, r.out / "section_features.parquet")   # stays on the node99    con.register("ft", tbl)100    tmin = con.execute("select min(extracted_at) from extracted_sections").fetchone()[0]101    con.execute(f"""CREATE TEMP TABLE F AS102    SELECT ft.*, e.n_mentions, CASE WHEN e.extracted_at = TIMESTAMP '{tmin}' THEN 'backfilled_june' ELSE 'october' END run,103           m.section_name item, m.word_count, f.form_type form, year(f.filing_date) yr,104           shown_chars*1.0/nchar AS shown_frac, last_offset*1.0/nchar AS last_frac,105           CASE WHEN n_hits > 0 THEN hits_shown*1.0/n_hits END AS hits_frac,106           (9 + length(m.section_name) + 2 + focus_len) > 14000 AS msg_truncated,107           nchar > {BUDGET} AS over_budget108    FROM ft JOIN extracted_sections e USING (section_id)109    JOIN (SELECT section_id, section_name, word_count FROM src_sections UNION ALL SELECT section_id, section_name, word_count FROM spid_sections) m USING (section_id)110    JOIN src_filings f ON f.accession_number = e.accession_number""")111    r.notes["replica_mismatches"] = con.execute("select count(*) from F where not replica_eq").fetchone()[0]112    r.notes["gate_py_pass"] = con.execute("select count(*) from F where gate_py").fetchone()[0]113    r.notes["gate_py_pass_by_form"] = con.execute("select form, count(*) filter (where gate_py), count(*) from F group by 1 order by 1").fetchall()114    r.notes["gate_fail_with_mentions"] = con.execute("select count(*) from F where not gate_py and n_mentions>0").fetchone()[0]115    agg = """count(*) n, count(*) FILTER (WHERE gate_py) gate_pass, count(*) FILTER (WHERE over_budget) over_9000_chars, round(100.0*count(*) FILTER (WHERE over_budget)/count(*),2) pct_over_9000,116      round(quantile_cont(nchar,0.1)) chars_p10, round(quantile_cont(nchar,0.5)) chars_p50, round(quantile_cont(nchar,0.9)) chars_p90,117      round(quantile_cont(shown_frac,0.1),3) shown_p10, round(quantile_cont(shown_frac,0.5),3) shown_p50, round(quantile_cont(shown_frac,0.9),3) shown_p90, round(avg(shown_frac),3) shown_mean,118      round(quantile_cont(shown_frac,0.5) FILTER (WHERE over_budget),3) shown_p50_over_budget,119      round(quantile_cont(last_frac,0.1),3) last_p10, round(quantile_cont(last_frac,0.5),3) last_p50, round(quantile_cont(last_frac,0.9),3) last_p90,120      round(100.0*count(*) FILTER (WHERE shown_frac < 0.25)/count(*),2) pct_shown_lt_25, round(100.0*count(*) FILTER (WHERE last_frac < 0.5)/count(*),2) pct_last_lt_50,121      round(100.0*count(*) FILTER (WHERE msg_truncated)/count(*),2) pct_msg_truncated_14k, round(avg(hits_frac),3) hits_shown_mean, round(quantile_cont(hits_frac,0.5),3) hits_shown_p50,122      sum(n_mentions) mentions, sum(n_mentions) FILTER (WHERE shown_frac < 0.25) mentions_from_lt25, round(100.0*sum(n_mentions) FILTER (WHERE shown_frac < 0.25)/nullif(sum(n_mentions),0),2) pct_mentions_from_lt25,123      round(100.0*sum(capex_shown)/nullif(sum(capex_all),0),2) pct_capex_vocab_shown_over_budget"""124    r.dump("focus_coverage_by_form_item.csv", con, f"SELECT form, item, {agg} FROM F GROUP BY 1,2 ORDER BY 1,2")125    r.dump("focus_coverage_by_form.csv", con, f"SELECT form, {agg} FROM F GROUP BY 1 ORDER BY 1")126    r.dump("focus_coverage_by_form_item_run.csv", con, f"SELECT form, item, run, {agg} FROM F GROUP BY 1,2,3 ORDER BY 1,2,3")127    r.dump("focus_coverage_calls_only_by_form_item.csv", con, f"SELECT form, item, {agg} FROM F WHERE gate_py AND word_count >= 30 GROUP BY 1,2 ORDER BY 1,2")128    r.dump("mentions_vs_length_by_item.csv", con, """129    SELECT form, item, CASE WHEN nchar<3000 THEN 'a<3k' WHEN nchar<6000 THEN 'b3-6k' WHEN nchar<9000 THEN 'c6-9k' WHEN nchar<12000 THEN 'd9-12k' WHEN nchar<20000 THEN 'e12-20k' WHEN nchar<40000 THEN 'f20-40k' WHEN nchar<80000 THEN 'g40-80k' ELSE 'h>=80k' END len_bin,130           count(*) n, count(*) FILTER (WHERE gate_py) calls, round(avg(n_mentions),4) mean_mentions, round(avg(n_mentions) FILTER (WHERE gate_py),4) mean_mentions_per_call,131           round(100.0*count(*) FILTER (WHERE n_mentions>0)/count(*),2) productive_pct, round(avg(shown_frac),3) shown_mean, round(avg(shown_chars)) shown_chars_mean,132           round(1e4*sum(n_mentions)/nullif(sum(shown_chars),0),4) mentions_per_10k_shown_chars133    FROM F GROUP BY 1,2,3 ORDER BY 1,2,3""")134    r.dump("shown_fraction_bins.csv", con, """135    SELECT form, CASE WHEN shown_frac >= 0.9 THEN 'a>=0.9' WHEN shown_frac >= 0.25 THEN 'b0.25-0.9' ELSE 'c<0.25' END bin, count(*) n, count(*) FILTER (WHERE gate_py AND word_count>=30) calls,136           count(*) FILTER (WHERE n_mentions>0) with_mentions, sum(n_mentions) mentions FROM F GROUP BY 1,2 ORDER BY 1,2""")137    # 10-K MD&A coverage hole by year: all parsed + legacy 10-K md_and_a sections (ledgered or not)138    r.dump("mdna_10k_short_by_year.csv", con, """139    WITH a AS (SELECT s.section_id, s.word_count, year(f.filing_date) yr, s.section_id IN (SELECT section_id FROM extracted_sections) ledgered,140                      regexp_matches(left(s.text, 3000), 'incorporated .{0,40}by reference', 'i') incorp_head141               FROM src_sections s JOIN src_filings f USING (accession_number) WHERE f.form_type='10-K' AND s.section_name='md_and_a' AND s.accession_number NOT IN (SELECT accession_number FROM spid_sections)142               UNION ALL143               SELECT s.section_id, s.word_count, year(f.filing_date), TRUE, regexp_matches(left(s.text, 3000), 'incorporated .{0,40}by reference', 'i')144               FROM spid_sections s JOIN src_filings f USING (accession_number) WHERE s.section_name='md_and_a')145    SELECT yr, count(*) mdna_sections, count(*) FILTER (WHERE word_count < 30) lt30_words, round(100.0*count(*) FILTER (WHERE word_count < 30)/count(*),2) pct_lt30,146           count(*) FILTER (WHERE word_count < 300 AND incorp_head) lt300_incorporated_by_ref, count(*) FILTER (WHERE NOT ledgered) not_ledgered, round(median(word_count)) median_words147    FROM a GROUP BY 1 ORDER BY 1""")148    K = {}149    K["ledger_10k_mdna_median_words"] = con.execute("select median(word_count) from F where form='10-K' and item='md_and_a'").fetchone()[0]150    K["ledger_10k_mdna_median_chars"] = con.execute("select median(nchar) from F where form='10-K' and item='md_and_a'").fetchone()[0]151    K["ledger_10k_mdna_n"] = con.execute("select count(*) from F where form='10-K' and item='md_and_a'").fetchone()[0]152    K["ledger_10k_mdna_shown_p50"] = con.execute("select median(shown_frac) from F where form='10-K' and item='md_and_a'").fetchone()[0]153    K["ledger_10k_mdna_last_p50"] = con.execute("select median(last_frac) from F where form='10-K' and item='md_and_a'").fetchone()[0]154    K["ledger_10k_mdna_pct_shown_lt_25"] = con.execute("select 100.0*count(*) filter (where shown_frac<0.25)/count(*) from F where form='10-K' and item='md_and_a'").fetchone()[0]155    K["ledger_sections_over_9000_chars"], K["ledger_sections"] = con.execute("select count(*) filter (where over_budget), count(*) from F").fetchone()156    K["calls_over_9000_chars"], K["calls"] = con.execute("select count(*) filter (where over_budget), count(*) from F where gate_py and word_count>=30").fetchone()157    K["mentions_from_sections_shown_lt_25"], K["mentions_total"] = con.execute("select sum(n_mentions) filter (where shown_frac<0.25), sum(n_mentions) from F").fetchone()158    K["msg_truncated_14k"] = con.execute("select count(*) from F where msg_truncated").fetchone()[0]159    K["gate_py_pass_total"] = r.notes["gate_py_pass"]; K["gate_duckdb_pass_total_N1"] = 703737160    K["mean_shown_frac_over_budget"] = con.execute("select avg(shown_frac) from F where over_budget").fetchone()[0]161    r.write_json("stats_keys.json", K)162    r.finish()163164if __name__ == "__main__":165    main()166