Skip to content
Ordermatic Docs

Cleveland Kitchen — Product Matching & Cross-Reference: Remaining Work

What’s Done (this commit)

Section titled “What’s Done (this commit)”

WhereFour Auth & Company ID

Section titled “WhereFour Auth & Company ID”
  • Fixed WhereFourService missing Authorization headers (Basic auth)
  • Replaced all hardcoded 'GENFIT' company IDs with ERPConnection.extraConfig.default_company_id
  • Added default_company_id to ExtraConfig interface, set on dev + prod Neon

Search Restructuring

Section titled “Search Restructuring”
  • Typesense is now the primary search engine; direct ERP only fires when “Force direct ERP” checkbox is checked
  • Item search modal now uses item description as the primary search signal (not SKU/UPC)
  • Searchable terms show item name first, then numeric IDs as clickable pills

UPC Pipeline Fix

Section titled “UPC Pipeline Fix”
  • Added upc column through all three dbt layers (bronze → silver → gold) for WhereFour products
  • UPC now included in Typesense search_text for full-text matching

Product Enrichments & Cross-References (Path 1)

Section titled “Product Enrichments & Cross-References (Path 1)”
  • DB tables: product_enrichments + manual_cross_references on dev + prod Neon
  • Import script: scripts/import-upc-mapping.py — reads XLSX, resolves via Typesense, writes to Neon + Typesense
  • Data loaded: 92 product UPC corrections + 347 universal cross-references (GTIN-12, GTIN-14, 00-padded, SKU)
  • Search updated: All 3 cross-reference search paths include __UNIVERSAL__ filter alongside customer-specific
  • Result: Searching 00859774007285 (Armada’s GTIN-14) now finds “Classic Kimchi - 2 gallon pail”

What Remains

Section titled “What Remains”

1. dbt Pipeline Integration (durability)

Section titled “1. dbt Pipeline Integration (durability)”

The Typesense sync was done directly from the import script for immediacy. For durability:

  • dim_products.sql should LEFT JOIN product_enrichments to override bad UPCs at the gold layer
  • raw_cross_references.sql should UNION manual_cross_references (from Neon via DuckDB postgres_scan or export to Parquet)
  • This ensures dbt rebuilds don’t lose the enrichments

2. Path 2: Order History → Cross-References

Section titled “2. Path 2: Order History → Cross-References”

Auto-build customer-specific product mappings from PO history + WhereFour fulfilled orders:

  • Extend WhereFour sync: Add wherefour_order_lines DLT resource (currently only headers synced, no line items)
  • Matching script: Correlate PO extractions (customer + PO# + items) with WhereFour orders (customer + PO# + line items)
  • Confidence scoring: PO# match (0.30) + customer match (0.20) + quantity match (0.20) + UPC match (0.25) + date proximity (0.05)
  • Output: Same manual_cross_references records, source=‘order_history’
  • Impact: Solves “Kimchi Vegan” → “Classic Kimchi - 2 gallon pail” by learning Armada’s product names from past orders

3. Operator Selection Learning

Section titled “3. Operator Selection Learning”

When operator manually selects a product in ItemSearchModal, record the mapping:

  • Insert into manual_cross_references with source=‘operator_selection’, confidence=1.00
  • Customer-specific (not UNIVERSAL) — uses the PO’s customer ID
  • Next time same customer uses that name/ID, cross-reference search finds it automatically

4. Admin UI for Cross-Reference Management

Section titled “4. Admin UI for Cross-Reference Management”
  • View/edit/delete cross-references per connection
  • Import new spreadsheets
  • Review auto-generated cross-references (confidence < 0.90)
  • Not blocking for CK POC

5. Connection ID Mismatch

Section titled “5. Connection ID Mismatch”
  • Dev Neon: CK WhereFour = 093e4161-3310-4559-ab5e-24723d93a6aa
  • Prod Neon: CK WhereFour = a29ce675-c7c5-40c6-a6aa-d3dde2106410
  • Typesense indexed under dev ID (093e4161...)
  • Import script handles both via --db dev|prod flag
  • Long-term: Dagster pipeline should use the prod connection ID for Typesense indexing

6. One Unresolved Product

Section titled “6. One Unresolved Product”

“Classic Kimchi - 8/16 oz Pack” (SKU: 0816KMCC) — spreadsheet name doesn’t match any WhereFour product name in Typesense. Likely a slight naming difference. Manual resolution needed.