Problem
This is the heaviest live work the browser does. The docket Comments tab and the orchestration/fidelity panels cluster form letters live, in the browser: clean → strip contacts → skeleton → md5 → GROUP BY key, with a sorted-token near-dup tier on top. Cost scales O(rows) — a high-volume docket hashes every comment on the main thread on every view.
On top of that, the docket breakdown runs a second client-side agglomeration pass (lib/comments/templates.ts agglomerateTemplates): single-linkage merge of near-dup clusters by token-set Jaccard (≥0.8) to collapse wording variants into template families. That pass is a deliberately cheap stand-in for SimHash/MinHash + LSH.
Proposal
Precompute at ETL, on the comment rows:
Why it's safe to swap in
The transform spec lives in lib/text/normalize.ts as two parallel implementations of the same logic (TS for the browser, DuckDB SQL builders for in-WASM execution). That duality is deliberate: a precomputed column can be swapped in without changing clustering behaviour, because the pipeline runs byte-for-byte the same normalization the client does.
Payoff
skeleton_hash is global, so once it's a column, cross-docket campaign detection (same template across agencies) and per-docket cluster rollups are both one GROUP BY away.- Precomputed SimHash/MinHash makes the client-side
agglomerateTemplates pass unnecessary — template families fall out of a single GROUP BY on a baked near-dup band.
Implementation notes (from §4 of the doc)
- Keep the partition pruning: comment partitions are Hive-partitioned
agency/docket/year/month, and buildCommentsSource reads only matching part-0.parquet files. Keep derived comment columns inside those partitions; don't centralize them into one flat file the client must scan.
Dependencies
- The submitter-name ETL gap issue: recovering
first_name/last_name/organization activates the sqlStripName signature-masking stage, which sharpens this clustering (form letters identical but for who signed collapse into one cluster).
Source
§3 of the front-end compute architecture doc.
Problem
This is the heaviest live work the browser does. The docket Comments tab and the orchestration/fidelity panels cluster form letters live, in the browser: clean → strip contacts → skeleton →
md5→GROUP BY key, with a sorted-token near-dup tier on top. Cost scales O(rows) — a high-volume docket hashes every comment on the main thread on every view.On top of that, the docket breakdown runs a second client-side agglomeration pass (
lib/comments/templates.tsagglomerateTemplates): single-linkage merge of near-dup clusters by token-set Jaccard (≥0.8) to collapse wording variants into template families. That pass is a deliberately cheap stand-in for SimHash/MinHash + LSH.Proposal
Precompute at ETL, on the comment rows:
skeleton_hash(template tier),simhash/minhash(near-dup tier),word_count,is_placeholder— clustering collapses from live O(rows) hashing toGROUP BY skeleton_hashstance+confidencekeyed byskeleton_hash— today stance is a JS phrase-lexicon re-scored per viewgetCommentVolumeAndClusters/getCommentClustersMultiTierread a small artifact instead of scanning partitionsWhy it's safe to swap in
The transform spec lives in
lib/text/normalize.tsas two parallel implementations of the same logic (TS for the browser, DuckDB SQL builders for in-WASM execution). That duality is deliberate: a precomputed column can be swapped in without changing clustering behaviour, because the pipeline runs byte-for-byte the same normalization the client does.Payoff
skeleton_hashis global, so once it's a column, cross-docket campaign detection (same template across agencies) and per-docket cluster rollups are both oneGROUP BYaway.agglomerateTemplatespass unnecessary — template families fall out of a singleGROUP BYon a baked near-dup band.Implementation notes (from §4 of the doc)
agency/docket/year/month, andbuildCommentsSourcereads only matchingpart-0.parquetfiles. Keep derived comment columns inside those partitions; don't centralize them into one flat file the client must scan.Dependencies
first_name/last_name/organizationactivates thesqlStripNamesignature-masking stage, which sharpens this clustering (form letters identical but for who signed collapse into one cluster).Source
§3 of the front-end compute architecture doc.