company_dns

SIC hybrid search: fusing Keyword + Semantic across every system (RRF)

Status: Built (2026-10-04) - endpoint, Combined tab, tests and the evaluation harness are in; not committed. First scoped 2026-10-01 against a single SIC table, rewritten the same week after global keyword/semantic search, four live systems (US SIC, Japan SIC, EU NACE, ISIC Rev. 4) and the 37-concept calibration landed. Section 9 records what was built and the three places the build differs from this plan. Owner: michael.hay@mediumroast.io Scope: a new fused/ranked search over all registered systems at once, exposed as its own endpoint (/V4.0/global/sic/hybrid/{query}) and its own tab, labelled Combined, in the Industry Classification Explorer. Out of scope: changing how Keyword, Semantic or Compare work, any non-SIC data source, real BM25 text scoring. Related: this is item 3 (“Hybrid global”) of sic-global-search.md; the per-system calibration question in that doc is deliberately not solved here (decision 2).

1. Why this needs nothing exotic

DataFusion already has everything Reciprocal Rank Fusion (RRF) needs as plain SQL, no custom UDF:

The one real gap is the keyword side. find_by_description_global is a flat ILIKE '%term%' filter with no relevance signal, so every match ties and the server returns them unordered (capped at 50). strpos(lower(class_desc), lower(query)) (built in; earlier match = better) is a cheap proxy that needs no UDF. A real UDF would only be needed for BM25/TF-IDF, which this doc does not scope.

2. Decisions (2026-10-04)

  1. Single-engine hits are allowed. A hit found by only one engine keeps a nonzero RRF score and can outrank a mediocre both-engine hit. That is standard RRF and wanted: an exact-term keyword match is often what the user typed, and semantic-only hits are the point of embeddings.
  2. Fuse over the merged global lists first. Rank keyword matches and semantic neighbours across all systems together, then fuse. Per-system ranks (which would sidestep the cross-system score offsets measured in sic-global-search.md: ISIC about -0.06 and NACE about -0.04 against US) are a follow-up only if measurement says they help, not an assumption.
  3. Global only. One endpoint, /V4.0/global/sic/hybrid/{query}. No /V4.0/na/... variant and no V3 alias (V3 never had this capability).
  4. A new tab labelled “Combined”, placed first: Combined | Compare | Keyword | Semantic. It is not a third Compare column (changed from the 2026-10-01 plan); Compare stays Keyword | Semantic as it is. Naming trap: the existing Compare tab’s code still uses the id combined everywhere (icModeCombined, selectIcExplorerMode('combined'), icCombined*, .ic-combined-*). The new tab’s code and ids must be hybrid (icModeHybrid, icHybrid*) so the two cannot be confused; only the visible label is “Combined”. The in-app help should say plainly that Combined shows one fused ranking and Compare shows the two result lists side by side.
  5. Paginate in the browser. The endpoint is called once with k=50 (its cap) and the tab pages the 50 results client-side; no server pagination (that stays V4.1.0).
  6. When keyword finds nothing, show the one-line note “No keyword matches; showing semantic results”.
  7. Filtering is per system only (the same checkbox-and-count sidebar as Semantic); no Both / Keyword-only / Semantic-only engine facet.
  8. The evaluation script moves into the repo as a regression check (section 5).

3. Evidence: offline check on the 37-concept set (2026-10-04)

Before writing any Rust, the planned method was simulated in Python on the same four feather files and the same MiniLM model the server uses. This is the method, not the SQL: semantic = global top-50 by raw similarity; keyword = ILIKE '%q%' on class_desc across all four systems, ranked by match position with ties sharing a rank (SQL RANK()); fused with RRF K=60; fused-score ties broken by semantic rank. The 37 concepts and their equivalent classes in each system are the calibration set (hand-picked from class text; the ISIC picks mirror the NACE ones). Scripts live in the session scratchpad (hybrid_eval.py, hybrid_diag.py) and move into the repo as the regression harness described in section 5.

Metrics, over the single merged list: any@k = a correct row from any of the four systems is in the top k; MRR = mean of 1/(rank of the first correct row); pair@10 = of the 148 concept x system pairs, the share whose correct row is in the top 10 (hard ceiling: 10 slots for 4 systems). Three query forms, because keyword behaves very differently on each: a short phrase (“coal mining”), a long natural sentence, and a single term (“coal”).

query form (keyword finds anything for) method any@1 any@3 any@10 MRR pair@10
short phrase (21 / 37) semantic .73 .95 1.00 .84 .70
  keyword .16 .49 .49 .32 .25
  hybrid .68 .92 .97 .81 .70
long sentence (0 / 37) semantic .62 .81 .92 .73 .60
  keyword 0 0 0 0 0
  hybrid .62 .81 .92 .73 .60
single term (36 / 37) semantic .59 .76 .86 .70 .57
  keyword .08 .73 .92 .44 .61
  hybrid .70 .95 1.00 .82 .72

What it says:

Caveats: 37 concepts, one author’s picks (the same limits as the calibration run); simulated in Python, so the first SQL implementation must reproduce these numbers to within noise before it is trusted; the semantic side still carries the cross-system score offsets (decision 2).

4. Design

4.1 New module: crates/sic/src/hybrid.rs

Built from the start over SicCatalog.systems, like global.rs. Joins on unique_key (<module>-<section>-<division>-<group>-<class>), not class_id: a class id such as 0111 exists in US SIC, ISIC and Japan SIC, so a class_id join would fuse unrelated rows.

#[derive(Serialize, Clone)]
pub struct HybridHit {
    pub rank: usize,
    pub rrf_score: f64,
    pub source_type: String,           // "US SIC" | "Japan SIC" | "EU NACE" | "ISIC"
    pub unique_key: String,
    pub section_desc: String,
    pub division_desc: String,
    pub group_desc: String,
    pub class_desc: String,
    pub keyword_rank: Option<usize>,   // None = did not surface via keyword
    pub semantic_rank: Option<usize>,  // None = did not surface via semantic
    pub similarity: Option<f32>,       // raw cosine, only when semantic_rank is Some
}

impl SicCatalog {
    pub async fn search_hybrid_global(&self, model_id: &str, query: &str,
                                      query_vector: &[f32], k: usize) -> Result<Vec<HybridHit>>
}

Draft SQL (per-system SELECTs joined by UNION ALL, generated in a loop exactly like global.rs; {t} = system table, {label} = source_type):

WITH kw_src AS (
  SELECT arrow_cast('{label}','LargeUtf8') AS source_type, unique_key,
         section_desc, division_desc, group_desc, class_desc,
         strpos(lower(class_desc), lower('{q}')) AS match_pos
  FROM {t} WHERE class_desc ILIKE '%{q}%'
  -- UNION ALL ... one SELECT per registered system
),
kw AS (SELECT *, RANK() OVER (ORDER BY match_pos ASC) AS kw_rank FROM kw_src),
sem_src AS (
  SELECT * FROM (
    SELECT arrow_cast('{label}','LargeUtf8') AS source_type, unique_key,
           section_desc, division_desc, group_desc, class_desc,
           array_distance({vec_col}, arrow_cast({lit}, 'FixedSizeList({dim}, Float32)')) AS distance
    FROM {t} ORDER BY distance ASC LIMIT {pool}
  ) -- UNION ALL ... one per system, each limited to its own top {pool}
),
sem AS (
  SELECT *, RANK() OVER (ORDER BY distance ASC) AS sem_rank
  FROM (SELECT * FROM sem_src ORDER BY distance ASC LIMIT {pool})   -- merged global top {pool}
)
SELECT COALESCE(kw.source_type, sem.source_type)   AS source_type,
       COALESCE(kw.unique_key,  sem.unique_key)    AS unique_key,
       COALESCE(kw.section_desc, sem.section_desc) AS section_desc,
       -- ... division_desc, group_desc, class_desc likewise
       kw.kw_rank, sem.sem_rank,
       1.0 - (sem.distance * sem.distance) / 2.0   AS similarity,
       COALESCE(1.0/({RRF_K} + kw.kw_rank), 0) + COALESCE(1.0/({RRF_K} + sem.sem_rank), 0) AS rrf_score
FROM kw FULL OUTER JOIN sem ON kw.unique_key = sem.unique_key
ORDER BY rrf_score DESC, sem.sem_rank ASC NULLS LAST, unique_key ASC
LIMIT {k}

Notes and risks:

4.2 crates/sic/src/lib.rs

pub mod hybrid; pub use hybrid::HybridHit;, same pattern as global.

4.3 crates/server/src/main.rs

New route GET /V4.0/global/sic/hybrid/{query}, handler sic_hybrid_global copying sic_similarity_global’s shape: embed the query via state.embedders, call state.sic.search_hybrid_global(...). Same k / model params, V4-only. Registered with utoipa like its siblings and shown in /docs. Envelope message follows the existing style (“N hybrid SIC entries for [query] across all systems”).

4.4 UI: the Combined tab (v4/crates/server/static/)

Tab order: Combined | Compare | Keyword | Semantic. It follows the Semantic tab’s layout (full-size cards, left sidebar with per-system facets and counts via the existing IC_SOURCES registry) and reuses what the Compare work already built. Code and ids are hybrid (see decision 4).

5. Testing and acceptance

  1. Rust unit tests for the SQL builder (dynamic across 1 and N systems), in the style of the existing global.rs tests, plus the escaping.
  2. Server test that the endpoint returns the documented fields and a 200 with the same shape for every system.
  3. Reproduce section 3 on the real server: run the 37 concepts x 3 forms through the new endpoint and confirm the table above to within noise (in particular single-term any@10 near 1.00 and long sentences identical to semantic). That check, not the Rust unit tests, is the proof the SQL does what the simulation did. It is done by the evaluation harness below.
  4. Evaluation harness in the repo (decision 8), experiments/sic-hybrid-eval/ (next to the existing spike folders), made self-contained so it does not depend on session scratch files:
    • concepts.json: the 37 concepts with their short / long / single-term query forms and the equivalent class ids in each of the four systems (the calibration picks, made explicit instead of embedded in a script).
    • eval.py with two modes: --simulate (the Python method from section 3, reading the feather files and embedding with the pinned MiniLM revision) and --server URL (calls /V4.0/global/sic/hybrid/... plus the existing keyword and similarity endpoints and scores the merged lists the same way). Prints the section 3 table.
    • Regression thresholds (single-term hybrid any@10 >= .95, hybrid MRR on single terms >= semantic MRR, long-sentence hybrid identical to semantic) so it can fail a build; a README that records the environment it needs (pyarrow + numpy + sentence-transformers, which on this machine live in two different Python installs, so a one-step dump of the vectors to .npy is part of the script’s setup).
    • Needs a self-identifying User-Agent when it calls the server (the rate limiter returns 429 to anonymous ones).
  5. Browser verification of the new tab in light of the Compare lessons: facet counts reflect the full result set, not the displayed slice; the View JSON modal matches the other tabs for the same record; both themes.

6. Explicitly out of scope

7. Dependencies and order of work

No new data and no new crates. It depends only on what is already built: SicCatalog.systems, global.rs’s per-system SQL pattern, the embedders, and the Explorer tab and IC_SOURCES machinery. Suggested order: SQL + hybrid.rs and a throwaway test, then the endpoint, then reproduce section 3, then the tab, then docs.

8. Open items (not decisions yet)

9. As built (2026-10-04)

Where the build differs from the plan

  1. The keyword predicate is strpos(lower(class_desc), lower(q)) > 0, not ILIKE '%q%'. Same matches for normal text, but % and _ in the user’s query are literal, so they cannot act as wildcards. The existing find_by_description_global (Keyword and Compare) still has that wildcard quirk; it was not changed.
  2. The page number is not kept in the URL. The plan said to mirror the Keyword tab, but the Keyword tab’s page/perPage parameters belong to that tab and the explorer does not store which tab is active in the URL (a reload lands on Keyword), so a Combined page number in the URL would restore nothing useful. Paging resets on a new search or filter change.
  3. No engine facet and no rrf percentage, as decided. (A “Per page” select was added and then removed at the owner’s request: the Keyword tab has no visible page-size control, only a fixed 10, and none was asked for.)

Observations from using it

This site is open source. Improve this page.