Cleveland Kitchen — duplicate products + $0 price: root cause & fix plan

Status: IMPLEMENTED 2026-06-11 — A shipped (webapp search merge); B + C built in dbt, gated and auto-enabled for WhereFour, inert until a CK connection is re-materialized. · Owner: TBD

What landed (see “Implementation status” at the bottom): A — mergeDuplicateGoods() in /api/erp/parts (live for connection.type==='wherefour'). C + B — gated wherefour_collapse_products branch in stg_products.sql (price projection + collapse + intermediate drop). Verified by dbt compile (both var states) and an in-DuckDB fixture test. Activation = re-materialize a CK connection (start a sandbox connection). Surfaced by: CSR searching UPC 86998200005 on PO #747017 (Harris Teeter) in review-v2 support-access, staging connection 4f234677-7bc5-43c1-9059-bd616b33ef75.

Related: typesense-matching-architecture.md · ../poc/ck-product-matching-remaining.md · ../runbooks/ck-pricing-prod-rollout.md

---

TL;DR

The ERP search modal returns two rows for the same finished good because WhereFour keeps each CK product as two separate records and our pipeline indexes both with no cross-series dedup:

• a sellable “product” (id 1xxxxx) — carries the price, but no SKU and a plain/outdated UPC
• a stockable “inventory item” (id 77xxxx) — carries the correct SKU + UPC (incl. the CK upc_case override), but no price → renders $0.00

The two cannot be auto-joined on SKU (the priced series has none) or UPC (the values disagree — the underlying CK UPC-quality problem). Name is the only bridge in the current data. The record the CSR actually wants (SKU 1616RG, correct UPC, matches the PO line) is the $0 one; the price is stranded on its name-twin.

Scale on staging conn 4f234677: 1522 active products, only 88 carry a price, 1434 render $0.

Recommended sequence: (A) collapse duplicates in the search API now → (spike) find the authoritative product↔inventory link → (B/C) attach price to the SKU/UPC record in gold and drop the price-only twin.

---

Root cause (verified on live staging)

The two series

All under one connection (4f234677). Searching Roasted Garlic Kraut:

name	sellable “product” (priced)	stockable “inventory” (SKU + UPC)
6/16 oz Pack	109031 list $18, sku «none», upc 086998200005	775252 list $0, sku 1616RG, upc 869982000053 + upc_case 10869982000050
5 gallon pail	109030 list $26, sku «none»	775251 list $0, sku 105GRG, upc 859774007100
2 gallon pail	109029 list $25.50, sku «none»	775250 list $0, sku 102GRG, upc 859774007094
1/16 oz pouch	348311 list $0, sku «none»	775249 list $0, sku 0116RG
Puree (intermediate)	—	774263 sku RG-P (no sellable twin)
Drained (intermediate)	—	775253 sku RG-D (no sellable twin)

Two observations that shape the fix:

1. The inventory series is the canonical one — it carries the SKU the PO actually quotes (1616RG) and the corrected UPC. The priced series has neither.
2. The inventory series also contains non-sellable intermediates (Puree, Drained) that should never appear in a product-match catalog.

The pipeline chain (symptom → source)

1. Bronze union, no dedup — raw_products.sql:70 does SELECT * FROM products UNION ALL BY NAME SELECT * FROM wherefour under var('raw_wherefour_union'). The WhereFour branch (lines 19–63) reads iceberg_source('wherefour_inventory') and hard-codes list_price = NULL (lines 37–40): “WhereFour’s /inventory API exposes no price field; product pricing comes from the tier path.” The priced 1xxxxx rows come from the other branch, iceberg_source('products').
2. Silver dedup is too narrow — stg_products.sql:19 dedups by (connection_id, erp_product_id) only. 109031 ≠ 775252, so the two survive as separate rows. Pricing is a LEFT JOIN raw_product_pricing ON erp_product_id (lines 106–108), so the inventory rows (no matching pricing row) stay $0.
3. Gold passthrough — dim_products.sql emits one Typesense doc per row. The CK upc_case override (applied in the Typesense sync, not dbt) only enriches the inventory rows, so price and corrected-UPC end up on different documents.
4. Search returns both unmerged — parts/index.ts:336 (Layer 1.5) maps every Typesense hit straight to the response; ERPSearchModal.tsx renders each. Query 86998200005 matches both: it zero-pads to 086998200005 (hits 109031) and is a prefix of 869982000053 (hits 775252), via the query_by/UPC-fallback in typesense-search-service.ts:419.

Why one is $0

Price is pricing.price1 || pricing.list_price || 0 (typesense-search-service.ts:906); the inventory record has neither. This is structural, not a one-off: 1434 / 1522 active CK products render $0.

Logs confirm the live path

Staging OTel (Processing parts search request → 🔍 [TYPESENSE] product search → Found products via Typesense search) at 19:02–19:04 on 2026-06-11 — Layer 1.5 returning both docs, consistent with the data.

The join-key problem (critical for the durable fix)

To merge the price onto the SKU/UPC record we need a reliable link between the two series. Current options:

• SKU — ❌ unusable: the priced 1xxxxx rows have no SKU.
• UPC — ❌ unusable: the values disagree across the two series (the CK UPC-quality issue).
• Name — ⚠️ the only bridge that exists today. Exact for sellable variants, but a heuristic (collisions, pack-size variants, the intermediates have no twin).
• WhereFour-native link — ❓ unknown. The raw WhereFour product/pricing-scrape payload may carry an inventory id or SKU that was dropped in ingestion. This is the spike (below). If it exists, it is the authoritative join key and the durable fix is clean.

---

Why this matters

CK is a live POC. Today CSRs see a duplicate where the priced row carries an outdated UPC and the correct row shows $0 — they cannot tell which to trust, and either choice is wrong in one dimension. “Delete the $0 duplicate” is not a valid fix: that row holds the correct SKU and UPC. The fix has to keep the SKU/UPC record and give it the price.

---

Fix options

A. Search-API merge — ship now, low risk, reversible

Collapse same-good duplicates inside parts/index.ts after the Typesense layer returns, before responding:

• Group hits by a normalized key: name (+ unit_of_measure/pack token).
• Within a group, emit one row: prefer the record that has a SKU and/or upc_case (the canonical inventory record), and graft the price from its priced name-twin (max(list_price) across the group).
• Drop records whose name marks them non-sellable intermediates (optional, e.g. -Puree / -Drained / SKU starting RG-), or just let them rank below.

Pros: fixes the CSR experience today; contained to the webapp; reversible; no pipeline re-materialize. Cons: presentation-layer only — gold stays duplicated, so the batch auto-validator (findProductMatchesBatch) still sees two records and can still auto-bind the $0 one. Name-key merge is a heuristic.

B. dbt gold reconciliation — durable, source-correct, needs the spike

In stg_products (or a new int_products_reconciled), merge the WhereFour product (price) and inventory (SKU/UPC) rows into one canonical product, then re-materialize CK.

• Join on the key the spike identifies (WhereFour product↔inventory link, else SKU once the scrape preserves it, else name as a last resort).
• Keep the inventory row as the spine (SKU + UPC + correct identifiers); coalesce list_price/tiers from the product row.
• Filter the price-only 1xxxxx rows out of the output once their price has been absorbed, and exclude non-sellable intermediates from the product catalog.

Pros: correct for every consumer (search + auto-validate + discounts). Cons: re-materializes the live CK POC (real risk); depends on a reliable join key; touches shared dbt models.

C. Pricing coverage — the deeper $0 fix

The root of the $0 is that price never reaches the SKU/UPC inventory records. Per [project_ck_pricing_tier_gold_gap], the WhereFour tier→gold projection is orphaned. If list/tier prices were projected onto the inventory products (keyed by inventory id / SKU), the $0 disappears across all 1434 records, and the price-only 1xxxxx twins become redundant — which also resolves the duplicate when paired with B’s drop step.

Pros: kills the $0 at its source, catalog-wide; makes the price-only series deletable. Cons: largest scope; separate initiative; on its own (without B’s drop) it does not remove the duplicate row.

Recommended sequence

1. A now — immediate correct CSR experience, low blast radius, reversible.
2. Spike — provenance of the 1xxxxx priced rows + whether the source carries an inventory link or SKU.
3. C then B — project price onto the inventory records, then drop the price-only twins + intermediates in dbt. Removes the duplicate and the $0 structurally.

---

Implementation sketch — option A (the ship-now piece)

In parts/index.ts, between the Typesense map (line 338) and return:

// Collapse WhereFour's product/inventory duplicates: one row per finished good.
// Prefer the SKU/UPC-bearing record (canonical), graft the price from its twin.
function mergeDuplicateGoods(rows: FormattedProduct[]): FormattedProduct[] {
  const groups = new Map<string, FormattedProduct[]>();
  for (const r of rows) {
    const key = `${(r.name ?? '').trim().toLowerCase()}|${(r.unitOfMeasure ?? '').toLowerCase()}`;
    (groups.get(key) ?? groups.set(key, []).get(key)!).push(r);
  }
  return [...groups.values()].map(group => {
    if (group.length === 1) return group[0];
    const canonical = group.find(r => (r.metadata as any)?.sku || (r as any).upc_case) ?? group[0];
    const bestPrice = Math.max(...group.map(r => r.price || 0), 0);
    return { ...canonical, price: canonical.price || bestPrice };
  });
}

Gate it behind a per-connection flag or the WhereFour vendor so only CK-shaped catalogs are affected. Add a unit test with the 109031/775252 pair asserting one merged row at $18 keyed by the inventory record. (Note: sku/upc_case must be carried through mapProductDocument for the canonical-pick to work — they are not in SearchResult today; thread them through or pick canonical on upc_case presence in the raw hit.)

---

The spike (prerequisite for B/C)

1. Where do CK’s 1xxxxx priced rows originate? Trace iceberg_source('products') for conn 4f234677 back through ingestion (apps/dagster/erp_pipeline/ops/wherefour_pricing_scrape.py, sources/wherefour/). Confirm whether they’re the price-list scrape output.
2. Does the raw payload carry a SKU or inventory id on the priced rows? If the scrape sees the SKU but drops it before the iceberg table, preserving it converts B into a clean SKU join — the single highest-leverage change.
3. Does WhereFour link product→inventory natively? Check the inventory and product API shapes for a cross-reference id.

---

Verification plan

• A: unit test on mergeDuplicateGoods (the 109031/775252 fixture → one $18 row); manual check that searching 86998200005 / 1616RG returns a single priced row in the modal.
• B/C: after re-materializing a CK staging connection, assert via Typesense that each finished good has exactly one active sellable doc with both a non-zero price and the corrected UPC; assert intermediates (RG-P, RG-D) are excluded; re-run the auto-validation eval to confirm no regression on CK fixtures.

Risks

• Name-key merge (A) can over-collapse genuinely distinct products that share a name — scope to WhereFour/CK and log merges.
• Re-materializing CK (B/C) on a live POC; stage on a sandbox connection first, diff doc counts before/after.
• The CK UPC-quality work and discount-resolver SKU bridge touch the same sku/upc lineage — coordinate so the changes compose.

Appendix — evidence

• Typesense (shared prod+staging cluster), collection products, conn 4f234677: the table above is live data as of 2026-06-11.
• Counts: total_active=1522, list_price>0 = 88, price1=0 && list_price=0 → 1434.
• The PO line in question quotes item code 1616RG (= inventory SKU on 775252) and UPC 86998200005.

---

Implementation status (2026-06-11)

The join-key spike resolved to “no native key; normalized name is the only bridge” — the priced rows are P21-origin (iceberg_source('products')) and carry no SKU; the SKU/UPC rows are WhereFour /inventory with list_price=NULL. The codebase already name-matches for WhereFour pricing (_norm_item_name), so the fix uses the same bridge.

A — shipped (live). mergeDuplicateGoods() (apps/webapp/src/utils/merge-duplicate-goods.ts) collapses same name+pack hits in /api/erp/parts for connection.type==='wherefour', keeping the SKU/UPC record and grafting the priced twin’s price. SearchResult now carries sku/upc/upcCase. 7 unit tests.

C + B — built, gated off by default. New wherefour_collapse_products dbt var (default false in dbt_project.yml; auto-set true for wherefour/mock-wherefour in dbt_assets.py) drives a gated branch in stg_products.sql:

• C — price_by_name overlays the list price across the normalized-name bridge (new macro wf_norm_name.sql) onto every $0 row.
• B — drops non-sellable intermediates (-Puree/-Drained name + -P/-D SKU suffix) and collapses each (connection_id, norm_name) to one row, preferring the SKU-bearing inventory record.
• P21 tenants and full-refresh runs hit the unchanged {% raw %}{% else %}{% endraw %} branch.

Verified: dbt compile passes with the var on and off; the rendered SQL runs in DuckDB against a CK fixture with the expected collapse/projection (apps/dagster/tests/test_wherefour_collapse_sql.py, 3 tests).

Activation (when ready to go live on CK)

1. Re-materialize a CK sandbox connection first (the var auto-enables for WhereFour). Diff products doc counts before/after (expect roughly the 88-priced + intermediates removed; ~1434 $0 rows should collapse/price up).
2. Spot-check via Typesense: each finished good has exactly one active doc carrying both a non-zero list_price and the corrected upc/upc_case; RG-P/RG-D intermediates absent.
3. Residual-$0 check (the name-bridge is only as good as name equality across the two series):

   # count inventory rows (sku != '') still at list_price 0 after collapse — should be near zero
   filter_by=connection_id:=<conn> && delete_flag:=false && sku:!= && list_price:=0

   Any stragglers are finished goods whose P21 and WhereFour names differ beyond whitespace — fix by aligning the name or adding a targeted alias.
4. Once CK sandbox looks right, re-materialize the live CK connection.

Note: once B is active, A becomes a no-op (no duplicates reach the search), but A stays as the cheap belt-and-suspenders for any un-re-materialized connection.