Improve Interactive Explorer full-text search substrate

[rdhyee]

Context

Follow-up to #84 and PR #95. The DuckDB FTS spike answered the first question: native DuckDB FTS works, including BM25 ranking, but the generated browser-delivered index is too large to ship by default.

As of 2026-05-08, the live Interactive Explorer at https://isamples.org/explorer.html does not have production full-text search in the search-engine sense. It has browser-side DuckDB-WASM substring search over Parquet.

Current behavior

• The live page is backed by isamplesorg.github.io/explorer.qmd.
• Search runs against https://data.isamples.org/isamples_202601_samples_map_lite.parquet (~60 MB).
• The live search predicate covers only:
  • label
  • place_name
• Search uses ILIKE '%term%', with multi-word queries split into terms and combined with AND.
• Result ordering is a simple score:
  • label match = 3
  • place_name match = 2
• Search composes with active source and facet filters.
• Search does not currently cover description in the live explorer because samples_map_lite.parquet does not carry it.
• Search does not cover the canonical Solr searchText equivalent described in query-spec.qmd.

Relevant code/docs:

• explorer.qmd: searchTerms, textSearchWhere, textSearchScore
• explorer.qmd: search handler around doSearch()
• query-spec.qmd: intended text MATCHES semantics
• tools/build_fts_index.py: preserved DuckDB FTS spike artifact

Mismatch to resolve

query-spec.qmd currently says the web Explorer subset is label + description + place_name, but the live explorer searches only label + place_name.

Either:

1. Update the docs/UI to state the actual live subset, or
2. Change the live search implementation to include description, probably via sample_facets_v2.parquet or another search-optimized projection.

Measured behavior

Native DuckDB measurements against the live hosted Parquet files, run on 2026-05-08. These are not browser timings, but they show the rough scan cost.

Live-style samples_map_lite search over label + place_name:

• pottery: 50 results, ~1.8s cold
• basalt: 50 results, ~0.5s
• unlikely no-hit term: 0 results, ~0.2s

sample_facets_v2 search over label + description + place_name:

• pottery: 50 results, ~3.0s
• cyprus: 50 results, ~0.7s
• basalt: 50 results, ~0.5s

DuckDB FTS spike findings from PR #95:

• Full index (label + description + place_name): ~358 MB
• Lite index (label + place_name): ~211 MB
• ATTACH over HTTP in DuckDB-WASM works, but downloading 200-358 MB is too large for an interactive default page.

Recommended path

Build a small, browser-friendly, pre-tokenized inverted-index Parquet substrate rather than shipping a DuckDB .duckdb FTS database by default.

Sketch:

• Offline pipeline creates token rows such as:
  • token
  • pid
  • field
  • weight
  • optional term_frequency
  • optional source
  • optional partition/hash/prefix columns
• Host it under https://data.isamples.org/ as versioned Parquet.
• Partition by token prefix/hash so browser queries touch only relevant byte ranges.
• For multi-term queries, fetch/intersect PID sets, score by field/term weights, then join back to samples_map_lite or sample_facets_v2 for display fields.
• Keep DuckDB FTS as an optional "enhanced search" experiment only if users explicitly accept a large download.

Acceptance criteria

• Decide and document current search semantics: side-panel lookup vs global filter. Coordinate with Explorer state contract: URL/DOM/widget-state inventory + search-as-global-filter decision #164.
• Resolve the query-spec.qmd mismatch for the current live subset.
• Add a search substrate design note covering data shape, partitioning, update cadence, and expected size.
• Prototype an inverted-index Parquet file and benchmark:
  • first search latency
  • repeated search latency
  • bytes transferred
  • result quality for pottery, pottery Cyprus, basalt, and source/facet-filtered searches
• Update the Explorer UI copy so users know which fields are searched.
• Add Playwright or equivalent smoke coverage for:
  • multi-term AND behavior
  • literal wildcard characters such as % and _
  • source/facet-filter composition
  • no-result behavior

Related

• Explore DuckDB FTS extension for full-text search in Explorer #84
• Explorer state contract: URL/DOM/widget-state inventory + search-as-global-filter decision #164
• PR Improve search: multi-term AND + relevance ranking (FTS spike) #95

[rdhyee]

Good framing. Picking up the threads from #164 (PR #166) and sharpening before the substrate prototype starts.

Coordinating with #164

Option B (side-panel lookup) is locked in PR #166 with rationale (cluster-mode H3 summaries are not text-indexable; consistency with the #158 facet-count contract). The decision is intentionally orthogonal to the search backend — the EXPLORER_STATE.md doc explicitly says option B "cares only about which UI surface displays results, not how matches are computed." So the substrate work here can proceed without re-opening semantics. If the substrate analysis surfaces a reason to flip to option A (e.g., a ranking signal that benefits from also filtering the map), we revise the doc; the inventory does not change.

On the query-spec.qmd mismatch — recommend a two-step

Short term (today, quick win): flip the live doSearch() to read from sample_facets_v2.parquet so description becomes searchable, resolving the mismatch by expanding live to match docs rather than narrowing docs. Your measurements show pottery at ~3.0s cold against sample_facets_v2 server-side — an interim, not a destination, but it closes the gap honestly while the substrate matures. (Browser-side via HTTP range will be slower, but the existing UI already shows a "Searching..." state and a 50-row LIMIT caps the worst case.)

Long term: the substrate replaces both. Once it lands, the field set is whatever the substrate indexes; query-spec.qmd describes the substrate's coverage.

This way #165 doesn't block on the substrate to fix the doc/UI mismatch, and the substrate's acceptance bar is "matches or beats the interim," which is concrete.

Sharpening the substrate sketch — questions worth pinning before prototyping

1. Tokenization contract. Build-time and query-time must agree. Commit explicitly to:

   • case folding (lowercase ASCII?),
   • Unicode normalization (NFKC?),
   • diacritic stripping (yes/no — affects archaeological place names like Çatalhöyük),
   • stemming (none / Porter / snowball),
   • stopwords (none, or a small list — but stopwords interact badly with pottery Cyprus-style queries).
     Recommend: lowercase + NFKC + diacritic-strip + no stemming + no stopwords for v1. Cheap, reversible, and the query rewrite is trivial.

2. Bytes-transferred budget. The FTS spike showed 200-358 MB is too much. What's the target ceiling? Suggest naming a number (e.g., ≤ 5 MB cold + ≤ 1 MB per query warm), since that target shapes partitioning decisions before a line of pipeline is written.

3. Latency targets. Live samples_map_lite ILIKE is probably 5-10s cold over HTTP range for a wildcard token like pottery. Suggest:

   • cold first search ≤ 2 s (P50),
   • warm repeat ≤ 500 ms,
   • filter-composed search ≤ 3 s cold.
     These are guesses — worth calibrating against measured live-explorer behavior with ?perf=1 instrumentation before committing.

4. Prefix-partition shape. Token frequency is power-law. A naive 2-char prefix partition gives a multi-hundred-MB "th" shard. Either (a) cap shard size and split high-frequency tokens by hash, or (b) use a global hash-partition. Worth deciding before pipeline work.

5. Multi-term query plan. For pottery Cyprus:

   • fetch pottery PID set, fetch Cyprus PID set, intersect, then score and join back to samples_map_lite for display fields.
   • Browser cost: 2 partition fetches + 1 intersect + 1 keyed join. The keyed join is the expensive part — samples_map_lite is 60 MB, and we want a pid IN (...) lookup to hit only the relevant row groups. Worth checking that DuckDB-WASM can prune row groups by pid (it should, given the parquet has min/max stats; needs verification).

6. Facet-filter composition — the load-bearing risk. Current search composes via inline SQL AND (sourceFilterSQL + facetFilterSQL). Substrate-based search produces a PID set; combining it with facetFilterSQL()'s pid IN (SELECT pid FROM facets WHERE ...) works but doubles the keyed lookups. Worth prototyping early with a worst-case query (rare token + multiple facet selections + source filter) and measuring, since a hostile composition is what'll break the latency target if anything does.

7. Update cadence + URL versioning. Match the existing monthly Zenodo cycle (isamples_202601_*). Suggest: isamples_YYYYMM_search_index_v1.parquet (or partitioned dir), with the explorer pinned to a specific YYYYMM so a dataset rebuild can't break a deployed site mid-flight.

Acceptance — small additions

A couple of checks worth adding to the acceptance list:

• Tokenization round-trip test. Build-time tokenizer and query-time tokenizer must produce identical tokens for a curated regression set (place names with diacritics, mixed case, punctuation, hyphenated compounds).
• Worst-case composition benchmark. Rare token + 2 facets + source filter, cold. The "boring case" benchmarks (pottery, basalt) are necessary but not sufficient.
• Wildcard-character handling. Already in your acceptance list — flagging that the existing escapeIlikePattern (explorer.qmd:479) handles % and _ for ILIKE; the substrate query path needs equivalent escaping at the tokenizer boundary.

Existing infra worth noting

• tests/test_globe.py already uses Playwright (playwright.config.js at repo root); search smoke tests slot in next to it.
• tools/build_fts_index.py is the FTS spike artifact; the substrate pipeline is a sibling tool, not a replacement.

[rdhyee]

This lines up with how I want to proceed. I want to be explicit about the goal, though: I do want good FTS, not just a faster version of the current ILIKE scan.

So I would separate the action plan into three tracks with decision gates.

0. Keep the UX decision stable

Agree that #164 / PR #166 settles the UI semantics for now:

• search is a side-panel lookup, not a global map/table/facet filter;
• the backend is intentionally orthogonal;
• if a later FTS implementation gives us a compelling reason to make search global, we reopen that explicitly rather than smuggling it into the substrate work.

That lets #165 focus on search quality and performance.

1. Baseline and close the current honesty gap

Before changing the substrate, we need a measured baseline from the actual browser path, not only native DuckDB.

Concrete first PR:

• add a Playwright/perf smoke path for searches like:
  • pottery
  • pottery Cyprus
  • basalt
  • a no-hit query
  • wildcard-ish input like 100% / _
• record:
  • first-search latency,
  • repeat-search latency,
  • transferred bytes if available,
  • whether active source/facet filters still compose correctly.

Then make the small honesty fix:

• either switch interim search to sample_facets_v2.parquet so description is actually searched, or
• update query-spec.qmd / UI copy to say the live explorer currently searches label + place_name.

My preference is your two-step: expand interim search to label + description + place_name if the browser measurement is tolerable. But I do not want that to be framed as the FTS solution; it is just a truthful interim state.

2. Define the FTS contract before building the index

I think the next design artifact should be a short search_index_v1 contract, with these decisions made up front:

• Fields indexed for v1: label, description, place_name; optionally keep per-field weights so we can later expand toward the canonical searchText field list.
• Tokenizer v1: lowercase + NFKC + diacritic stripping + no stemming + no stopwords. This is intentionally conservative and testable. If we later add stemming, it should be a v2 index format change.
• Ranking: field weights plus token frequency is enough for v1; do not pretend it is BM25 unless we actually compute the needed document-frequency/length signals.
• Budgets: choose explicit targets before tuning:
  • cold first search target: <= 2s P50,
  • warm repeat target: <= 500ms,
  • filter-composed cold search: <= 3s,
  • shipped/downloaded search data: far below the 211-358 MB DuckDB FTS artifact; I like your proposed <= 5 MB cold / <= 1 MB warm as an initial target.
• Versioning: pinned versioned URLs, e.g. isamples_YYYYMM_search_index_v1/..., not a mutable current alias in the deployed explorer.

Acceptance should include the tokenizer round-trip regression set you called out. That is especially important for place names and archaeological data.

3. Prototype static search, but keep a real-search fallback

The static Parquet inverted index is the right first prototype because it preserves the current zero-backend deployment model.

Prototype requirements:

• partition so common tokens do not create giant shards;
• prove multi-term intersection works for pottery Cyprus;
• prove result lookup does not force a large scan of samples_map_lite;
• prove source + facet composition does not blow the latency budget;
• compare result quality against the current ILIKE behavior and against the DuckDB FTS spike where possible.

But the decision gate should be honest:

• If the static index meets latency, byte, and quality targets, ship it.
• If it cannot, then the next step should be a real hosted search service/API, likely using the existing Solr searchText model as the semantic reference. The static-site constraint should not be allowed to permanently cap search quality.

In other words: static Parquet inverted index is the preferred implementation path, but good FTS is the requirement. If the static path cannot satisfy it, we should choose a search backend rather than keeping substring search indefinitely.

Proposed issue breakdown

1. Browser baseline + interim honesty fix (label + description + place_name or docs/UI correction).
2. search_index_v1 contract: fields, tokenizer, ranking, budgets, versioning.
3. Offline index builder prototype + tokenization tests.
4. Browser query prototype + Playwright perf/quality benchmarks.
5. Decision: ship static index or open hosted-search implementation issue.

That gives us forward motion without treating the PR #95 FTS spike as the end of the story.

[rdhyee]

Settling the plan. Three tracks, mechanical decision gate at the end, six sub-issues.

Locked-in design decisions

• UX semantics: side-panel lookup (option B from Explorer state contract: URL/DOM/widget-state inventory + search-as-global-filter decision #164 / PR docs: explorer state contract (closes #164) #166). Backend orthogonal. Re-opened only if a later FTS implementation gives a compelling reason to flip to global.
• v1 indexed fields: label + description + place_name. Parity with the current Explorer field set (after the honesty fix in track 1b).
• v2 expansion vector (named, not built): Solr searchText field set per query-spec.qmd:213-221 — source label, producedBy_*, samplingSite_*, curation_*. Out of scope for v1; in scope for the contract doc as a forward-compat constraint.
• Tokenizer v1: lowercase + NFKC + diacritic strip + whitespace split + length filter. No stemming. No stopwords. Parallel JS + Python implementations with a shared regression test set.
• Ranking v1: BM25 with per-token DF and per-doc length precomputed in the substrate. Field weights live in query code. Not "ILIKE+weights"; actual BM25.
• Substrate format constraint (the one that makes v1→v2 a content change, not a format change): each token row carries a field column. Field weights are query-side code, not substrate data. Adding v2 fields = re-running the build pipeline, no schema migration.

Why v1-first is a learning lap, not a one-way door

description is long-tail Zipf (natural English). place_name[] is narrow-vocab high-DF (every sample at a site shares the label). They behave differently under partitioning. v1 measures both shapes separately before v2 adds 6 more narrow-vocab fields. The keyed pid IN (...) lookup against samples_map_lite is the most expensive step and doesn't depend on field count — v1 measures it; v2 doesn't change it.

Rough size projection: v2 ≈ 2-3× v1 substrate (most v2 additions are short labels, not paragraphs). If v1 lands at e.g. 4 MB cold, v2 is plausibly 8-12 MB. Still well under any reasonable static-deploy ceiling. v1→v2 pivot is unlikely to bust budgets.

Track 5 decision gate is mechanical

"Does the prototype meet ALL budgets named in the v1 contract on the canonical benchmark?" Yes → ship. No → file the hosted-search-backend issue with the specific budget miss data attached. No budget renegotiation at the gate.

Sub-issue breakdown

#165 (this issue) — umbrella tracking issue, stays open until track 5 fires
├── #N1  Track 1a — Browser perf-smoke instrumentation + Playwright benchmark
│         artifact: extended ?perf=1 logging + tests/search_perf_*.py + recorded
│                   baseline against samples_map_lite (current live)
│         no upstream deps
├── #N2  Track 1b — Honesty fix (gated by #N1 numbers)
│         direction decided by measurements:
│           if browser cold ≤ ~5s on sample_facets_v2 → swap live to it
│           else → update query-spec.qmd + UI copy to match label+place_name
│         depends on #N1
├── #N3  Track 2  — search_index_v1 contract doc
│         artifact: SEARCH_INDEX_V1.md
│           - fields (v1) + named v2 expansion path
│           - tokenizer spec
│           - BM25 ranking spec (k1, b, DF/length signals)
│           - per-token-row {token, pid, field, weight_hint, tf} schema
│           - partition shape (hash by token, per-shard size cap, sub-shard
│             rule for high-frequency tokens)
│           - budgets: cold ≤ 2s P50, warm ≤ 500ms, filter-composed ≤ 3s,
│                     bytes ≤ 5 MB cold + ≤ 1 MB warm
│           - versioning: isamples_YYYYMM_search_index_v1/...
│           - curated benchmark JSON (8-12 queries, hand-labeled top-10)
│         no code deps; can start in parallel with #N1
├── #N4  Track 3  — Offline index builder + tokenizer regression set
│         artifact: tools/build_search_index.py + tests/test_tokenizer.py
│         tokenizer regression set ≥30 strings: diacritics, mixed case,
│           hyphenated compounds, archaeological place names, IGSN ids,
│           numerics. CI runs against both Python + JS implementations.
│         depends on #N3
├── #N5  Track 4  — Browser query prototype + benchmark run
│         artifact: explorer.qmd search path swap behind feature flag
│                   + recorded results vs v1 contract budgets and the
│                   curated benchmark from #N3
│         depends on #N4
└── #N6  Track 5  — GO/NO-GO decision gate
          mechanical: meets ALL contract budgets on benchmark → ship;
          misses any → file hosted-search-backend implementation issue
          with budget-miss data attached
          depends on #N5

Filing the six sub-issues now. Starting work on #N1 (perf-smoke baseline) since it has no upstream dependencies.

[rdhyee]

Sub-issues filed:

• Explorer FTS Track 1a: Browser perf-smoke baseline #167 — Track 1a: Browser perf-smoke baseline
• Explorer FTS Track 1b: Honesty fix for query-spec / live mismatch #168 — Track 1b: Honesty fix (gated by Explorer FTS Track 1a: Browser perf-smoke baseline #167)
• Explorer FTS Track 2: search_index_v1 contract doc #169 — Track 2: search_index_v1 contract doc
• Explorer FTS Track 3: Offline index builder + tokenizer regression set #170 — Track 3: Offline index builder + tokenizer regression set
• Explorer FTS Track 4: Browser query prototype + benchmark #171 — Track 4: Browser query prototype + benchmark
• Explorer FTS Track 5: GO/NO-GO decision gate #172 — Track 5: GO/NO-GO decision gate

Starting #167 next.