Typesense Matching Architecture

Status: Design — accepted in principle, Phase A approved for ship-today implementation Owner: David Boone Trigger: Cleveland Kitchen WhereFour POC kickoff (2026-05-14). Giant Eagle test PO autovalidated 3/19 items. Diagnosis revealed structural xref gap + length-check rejection bug + missing name signal. Repo paths in this doc are relative to repo root.

---

1. Architecture gap (root cause)

apps/dagster/dbt/models/bronze/raw_cross_references.sql is a one-liner: SELECT * FROM iceberg_source('cross_references'). That Iceberg table is fed by transform_p21_cross_reference and only carries P21-shaped rows. The manual_cross_references Postgres table (packages/db/src/schema/manual-cross-references.ts) is documented as flowing in through a UNION, but the UNION does not exist.

Consequences:

• WhereFour (Cleveland Kitchen) exposes no xref endpoint → 0 native xref rows.
• scripts/import-upc-mapping.py wrote 347 universal UPC rows directly into Postgres via the manual table, but stg_cross_references never sees them.
• The 347 rows visible in Typesense came from a one-off direct write (likely the script itself or a now-stale path), all customer_id = __UNIVERSAL__. No code path writes customer-keyed xrefs.
• extracted_order_items has erpItemId, isValidated, isAutoValidated, customerPartNumber, itemName, itemDescription — every confirmed match in review-v2 is a perfect xref signal, but nothing harvests it.

That is the root cause: the WhereFour-shaped xref described in docs/ck-demo-backlog.md was never implemented; the dbt model and the manual table are wired only on paper.

2. Typesense capabilities we should use

Problem	Typesense feature
UPC variants (85977400713 ↔ 085977400713 ↔ 00859774007130)	Schema-level normalization — store a upc_variants: string[] of every padding/GTIN-stripped form. For known explicit equivalences, multi-way synonyms scoped per collection. Drop application-layer UPC-fallback.
Description-based matching (“DILLY GARLIC PICKLE CHIPS, SIN” ≈ “Dilly Garlic Pickle Chips, 12/3 fl oz case”)	Hybrid search with an embedding field auto-generated from name + description; queries use vector_query plus keyword query_by in one call. Rank fusion (alpha≈0.4) keeps exact text dominant while letting semantic similarity rescue noisy descriptions.
Customer-specific aliases	cross_references collection keyed by customer_id (schema supports it; population doesn’t), fed from operator-confirmed matches + PO↔order correlation.
Vendor-master UPC conflicts (Giant Eagle UPC X = product A, CK UPC X = product B)	Filter scope + precedence: customer-keyed xref > customer-keyed UPC > universal UPC > product UPC > name/description hybrid. Single multi-search with explicit precedence beats today’s chain-with-rejection.
Multiple sequential round trips	multi_search in one request: xref (customer-scoped) + xref (universal) + product (UPC-filter) + product (hybrid q on name/description). Apply precedence in app code on already-returned hits.
Manual high-value overrides	Curation/Overrides for sticky mismatches (e.g. “always pin SKU 1457 for query ‘KIMCHI VEGAN 12/16’”).
Atomic re-indexing	Collection aliases (products → products_v2) so dbt sync rebuilds without read-time downtime.

Today’s code uses query_by, num_typos, prefix, prioritize_exact_match — and nothing else. We are leaving most of Typesense on the floor.

3. Proposed architecture

3.1 Index schema changes (apps/dagster/erp_pipeline/assets/typesense.py)

products:

• Add upc_variants: string[] (computed in dim_products dbt: original, zero-stripped, GTIN-14, UPC-12, UPC-11). Make upc a derived alias of upc_variants[0].
• Add name_embedding / description_embedding configured with embed.from: [name, description, manufacturer], model ts/all-MiniLM-L12-v2 (built-in ONNX, no API key). 384 dims is cheap at 132–2k product scale.
• Default prioritize_token_position: true via the client.
• Drop search_text from query_by to avoid silent description-match false positives the existing code already complains about.

cross_references:

• Add source: string (erp_native, operator_selection, order_history, spreadsheet_upc), confidence: float, last_seen_at: int64, confirmation_count: int32.
• Add their_upc_variants: string[] so customer-keyed UPC xref doesn’t need synonyms.

3.2 Single matcher entry point

Replace findProductMatchesBatch with one multi_search per item bundling four sub-queries:

1. cross_references filtered customer_id:=${customerId} — exact their_item_id / their_upc_variants.
2. cross_references filtered customer_id:=__UNIVERSAL__ — same query.
3. products filtered upc_variants:=${normalizedUpc} when query is numeric.
4. products query_by: erp_product_id,name,description with vector_query: name_embedding:([], alpha: 0.35, k: 10) for hybrid.

App logic picks the highest-precedence non-empty bucket. No more length-comparison rejection (delete the line at typesense-search-service.ts:1093 and its counterpart in findProductMatchesBatch), no validatePrefixCompatibility blocker (those are symptoms of overly broad fuzzy queries — the new schema prevents the false positives at index time). Confidence is a function of (bucket, exact-vs-fuzzy, vector distance).

3.3 Customer-specific learning loop

New writer module (apps/webapp/src/services/matching/xref-learner.ts) called from two paths:

• auto-validate when a match is confirmed (isValidated && isAutoValidated).
• The review-v2 product-picker server action when an operator selects a product.

Each event upserts manual_cross_references keyed on (connection_id, customer_id, item_id, their_item_id). Increments confirmation_count, bumps last_seen_at, sets source='operator_selection' or 'auto_validated'. Stores extracted_order_items.item_name and item_description in a their_item_desc column for semantic recall.

Critically: add a UNION to raw_cross_references.sql reading from manual_cross_references via a Postgres source. A small Dagster asset materializes Postgres → Iceberg bronze for that table so the existing dbt graph picks it up untouched. Without this UNION, learner writes go nowhere.

Item descriptions/names from extracted_order_items should also fold into the cross_references document’s embedding source so subsequent runs for the same customer use real PO language, not just the catalog name.

3.4 Auto-validate must pass itemName

auto-validate.ts only forwards itemIds. Extend the request schema and BatchMatchResult flow to also pass itemName + itemDescription to findProductMatchesBatch. The hybrid bucket needs that text — it is literally the strongest signal and is currently discarded.

4. Migration plan

Phase A — today, hours. No schema migration. Eight action items (revised after /plan-eng-review):

1. Gate the length-check, don’t delete it. typesense-search-service.ts:1093 was added to block real erp_product_id prefix collisions (“NT100-6-100BU” matching “NT100-6-100”). Use Typesense highlights to identify the matched field; only apply the length-check when matched field is erp_product_id. UPC/name matches skip it.
2. Pass itemName + itemDescription from auto-validate through to a new name+description sub-search in findProductMatchesBatch.
3. First-class UPC-filter sub-search in a multi_search batch (so UPC matches win without needing the products text search to miss first).
4. multi_search — single round trip with 4 sub-queries (customer xref, universal xref, UPC filter, name/description hybrid). Precedence applied in app code against returned buckets.
5. Query-time UPC synonyms. Post Typesense Synonyms API entries mapping every 11-digit numeric variant to its 12-digit UPC-A form (and 13/14-digit GTIN where present). No re-index needed; synonyms apply at query time. Reuses the same sync touchpoint as import-upc-mapping.py.
6. Name-overlap Jaccard guard on UPC-only matches. When the only signal is a UPC and the matched product’s name shares < 0.5 Jaccard token overlap with the extracted itemName, reject the match and fall through to name-fuzzy. Resolves the Giant Eagle “DILLY GARLIC ≠ Classic Dill” vendor-master conflict.
7. Tests + Giant Eagle fixture. packages/eval/test-data/synthetic/auto-validation/ck-giant-eagle.json (all 19 line items), typesense-search-service.test.ts for UPC variant matrix + length-check gating + Jaccard guard, auto-validate.test.ts for the extended request schema.
8. Backfill script (scripts/backfill-ck-manual-xrefs.ts) — read extracted_order_items where isValidated=true for CK connection, write to manual_cross_references Postgres + direct-push to Typesense cross_references. --dry-run mode ships first; reviewed sample of ≥20 rows by operator before live push.

Pre-flight (before any of A.1–A.8): verify customerId resolution is reliable for Giant Eagle on CK connection — confirm the customer match step yields a stable customer_id value that matches what manual_cross_references.customer_id will hold.

Phase B — this week. Wire the learning loop properly: Postgres source asset for manual_cross_references, UNION in raw_cross_references.sql, learner writes from review-v2 + auto-validate, cross_references schema additions (source, confidence, last_seen, confirmation_count). Behind a feature flag per connection. Confidence threshold: confirmation_count >= 2 to auto-apply an xref; first occurrence stored as suggested and surfaced in review-v2 with confirm/reject prompt. Write-priority: Postgres manual_cross_references is authoritative; the dbt rebuild reads it via the new UNION rather than overwriting it.

Phase C — week+. Add name_embedding to products schema, re-index via a products_v2 alias swap, add the vector_query clause and tune alpha. Add curation overrides for the top 20 sticky CK mismatches discovered by the eval set. Retire validatePrefixCompatibility once eval shows no regression.

5. Test plan

packages/eval/scripts/run-auto-validation-eval.ts already exists — extend it with:

• A ck-giant-eagle.json fixture of the 9 Giant Eagle codes + 11-digit UPCs + extracted descriptions, expected ERP product ids.
• A ck-universal-upc.json fixture covering the 347 universal rows to assert no regression.
• A vendor-conflict.json synthetic case where the same UPC string is claimed by two customers’ xrefs, asserting customer-scoped wins.

Add eval-results/typesense-matching-<date>.json with precision/recall per bucket so we can watch the hybrid layer’s contribution as embeddings come online.

6. Critical files for implementation

• apps/webapp/src/services/search/typesense-search-service.ts
• apps/webapp/src/pages/api/erp/parts/auto-validate.ts
• apps/webapp/src/utils/fuzzy-matching.ts
• apps/webapp/src/utils/cross-reference-lookup.ts
• apps/dagster/erp_pipeline/assets/typesense.py
• apps/dagster/dbt/models/bronze/raw_cross_references.sql
• packages/db/src/schema/manual-cross-references.ts
• packages/eval/scripts/run-auto-validation-eval.ts