Cleveland Kitchen — customer pricing via one canonical product + an id xref

Status: name-bridge price resolution IMPLEMENTED + live (2026-06-12); audit xref gold model built (2026-06-12); the PG-resolver xref was evaluated and intentionally skipped as redundant; B activation (sandbox re-materialize) is the remaining step · Owner: TBD

Shipped (the right price, now): a WhereFour customer-tier name bridge in the price resolver + search path. When a match lands on the catalog id (no erp_effective_prices row), the resolver recovers the customer’s contract price by the matched product’s name from the scraped tiers. Verified end-to-end on the real order: all 6 lines now resolve to Harris’s tier (Pickle Chips $35.93, krauts $21.50, Red Onion $22.00). Files: getCustomerTierPricesByName in postgres-effective-price-service.ts; bridge in pricing-calculator.ts resolveCustomerPriceWithCache (source tier_name_bridge); wired into auto-price-resolve.ts, auto-validate-with-price-check.ts, and the search path parts/index.ts. Audit xref built (2026-06-12): xref_wherefour_products.sql — a gold model emitted by the same collapse, recording each dropped catalog id → the canonical inventory survivor it merges into (so the merge is auditable/reversible). Gated identically to the collapse (empty for P21). Tested by test_wherefour_xref_sql.py. PG-resolver xref intentionally NOT built (redundant): the erp_product_id_xref Postgres table + loader + getEffectivePrices hop in §3 below was dropped after verifying (a) erp_products — the Postgres products replica — is empty for WhereFour (the catalog ids live only in Typesense/gold), so it would need heavy gold→Postgres plumbing; and (b) it is fully redundant with the shipped name-bridge, which already reprices catalog-id matches by name across every path (auto-validate, search display, manual apply-match — verified live). The durable structural fix is B (collapse) activation, which drops the catalog doc so new matches land on the inventory id erp_effective_prices is already keyed by; the name-bridge stays as the transition/stale-line backstop. Surfaced by: PO #747017 (Harris Teeter, doc cdc4bd49-729e-4d50-a988-a0e45fef93ef) priced every line at a generic base price instead of Harris’s contract price (Pickle Chips $30.82 shown vs $35.93 contracted).

Related: ck-product-dedup-and-zero-price.md (the A/B/C dedup work this builds on) · typesense-matching-architecture.md

---

TL;DR

CK has two product records for the same finished good, and the customer’s real price is filed under the one we don’t match to:

• Inventory record (e.g. Pickle Chips id 774561): carries the SKU (1216CD), the correct UPC, and — via the scraped WhereFour price tiers — Harris’s contract price ($35.93) in erp_effective_prices.
• Catalog record (id 227923): carries a generic base list price ($30.82) + manufacturer/category, no SKU, a plain UPC, and no customer price.

They share no id, no SKU, and a mismatched UPC — only the name links them. Matches land on the catalog id (227923), which has no row in erp_effective_prices, so the price resolver misses and falls back to the base list price. Every line on the order priced this way.

The fix (your call): merge the two into one canonical product, keep an xref of the orphan ids, and resolve the price from the customer tier via the canonical id. This folds into the dedup work already in flight (PR #1598).

---

What’s actually happening (verified)

All verified on staging (DB ep-muddy-wind, Typesense, the WhereFour sandbox API, and erp_effective_prices):

1. The customer’s price exists and is correct. WhereFour has a “Harris Teeter” price tier — Pickle Chips $35.93, krauts $21.50, Red Onion $22.00 — matching the PO exactly. It’s scraped into erp_pricing_rules_raw (6075 rows) and projected into erp_effective_prices (11 rows for customer 206234), keyed by the WhereFour inventory id (774561 → $35.93, 775252 → $21.50, …).
2. The lines matched the wrong id series. All 6 lines matched catalog-series ids (109031, 227923, …). None of those ids exist in erp_effective_prices → every lookup misses.
3. So the resolver falls back to the base list price. resolveAndReviewPrices (auto-price-resolve.ts) → getEffectivePrices(connectionId, {erpItemIds, customerId}) (postgres-effective-price-service.ts:31) looks up by the matched id, misses, and falls back to the catalog list_price (30.82).
4. The “Pattern · Last unit price from Harris” card is a placeholder (ReviewCenterPane.tsx:711) that just re-prints unit_price. Not real history, not a tier.
5. The base price is not literally “cost”: the WhereFour /inventory API returns list_price=null AND cost=null for these items. The 30.82 is a generic base list price (it appears as exactly one other customer’s tier), surfaced onto the catalog record. So it’s the wrong, lower, non-customer price — the user’s instinct, just not “cost”.

Why two records (the shape)

	Inventory record 774561	Catalog record 227923
SKU	1216CD ✅	none ❌
UPC / case code	correct ✅	plain/older ❌
manufacturer / category	empty	set ✅
base list_price	null ❌	$30.82 ✅
Harris contract price (erp_effective_prices)	$35.93 ✅	none ❌

The shape is unambiguous: the inventory record is WhereFour-native (the id orders + customer prices key off), the catalog record is P21-products-shaped (manufacturer/category + a base price, no SKU). Each holds half the product.

Open item (confirm first): the exact bronze loader that produces the catalog series for a WhereFour-only customer was not pinned — ad-hoc Iceberg reads under unsafe_enable_version_guessing showed the standard products / wherefour_inventory tables empty for conn 4f234677, which is unreliable. Confirm via a real partitioned dbt run (does raw_products yield CK rows, and from which branch?) before changing the pipeline. This also verifies the shipped dedup B actually affects CK.

---

The fix: one canonical product + an id xref

1. Canonical product (merge)

One product per finished good. Canonical id = the WhereFour inventory id (774561) — it owns the SKU, the correct UPC, and it is the id that both WhereFour orders and erp_effective_prices key off. The catalog record donates list_price (as a base fallback) + manufacturer/category onto the merged row.

This is the dedup B collapse already built in stg_products.sql (gated wherefour_collapse_products), which collapses same-name records preferring the SKU-bearing one. Two extensions:

• Make the survivor’s id the inventory id explicitly (it already prefers the SKU-bearing row; assert the canonical id is the inventory one).
• Carry the catalog row’s list_price onto the survivor as the base fallback (the existing C projection), so a no-tier product still has a price.

2. The xref (orphan ids → canonical)

When two records collapse, the loser’s id is not dropped — it’s recorded in an xref so anything that referenced it still resolves:

product_id_xref(connection_id, alias_id, alias_type, canonical_erp_product_id)
  e.g. (4f234677, 227923, 'catalog_product_id', 774561)

Houses: the catalog id (227923→774561), and naturally extends to the customer part numbers (1216CD) and UPC variants already used for matching. We already have the bones of this: the Typesense cross_references collection maps customer aliases → product ids today (typesense-search-service.ts TypesenseCrossRef). Add catalog-id → canonical-id entries there (or a sibling product_id_xref table emitted from the same gold collapse).

Built once from the normalized-name bridge (the same wf_norm_name macro as B), then everything keys off the explicit id link — no re-guessing by name on every lookup. This is exactly your “xref table to house the ids that aren’t shared”.

3. Price resolution through the canonical id

getEffectivePrices resolves the canonical id (via the xref) before the lookup, so a match on either id hits:

match id 227923 ──xref──▶ 774561 ──erp_effective_prices[774561, customer 206234]──▶ $35.93

Resolution order stays: customer tier price → connection-wide effective price → base list price. The only change is the xref hop so the customer tier is reachable from the catalog id.

4. Search + manual-match wiring (you approved “fix the search path too”)

Today /api/erp/parts (search) and the apply-match path show/write the gold list_price, never consulting erp_effective_prices — so a CSR matching by hand (every line on this order was is_edited) sees the base price even after the id gap closes. Fix:

• /api/erp/parts: resolve the customer effective price (xref → erp_effective_prices[customer]) for each result, so the Cmd+K modal shows $35.93, not 30.82.
• apply-match (assignErpMatch path): write the resolved customer price to the line on match.

---

How it composes with the dedup work (PR #1598)

Piece	Already shipped	This design adds
A search merge	collapses dup search hits, prefers SKU/UPC record	also resolve + show the customer effective price per result
B gold collapse	one canonical row per good (gated)	emit the id xref (loser id → canonical); assert canonical = inventory id
C price projection	base list_price onto the survivor	keep as the fallback below the customer tier
Pricing	resolver prefers customer tier (auto-validate)	resolve the canonical id via xref before the lookup; wire it into the search/manual path too

So this is mostly B + the resolver/search wiring, not a from-scratch build.

---

Open items / confirm before building

1. Catalog-series origin — confirm the bronze loader + that the base list_price is a safe fallback (a real partitioned dbt run, not ad-hoc Iceberg reads). Also confirms whether shipped B actually materializes CK.
2. Xref home — extend the existing cross_references collection vs a dedicated product_id_xref gold table. Lean: dedicated gold table emitted by the collapse (clean lineage), surfaced into Typesense alongside cross_references.
3. Stale matches — the 6 lines on this order are already saved against catalog ids. A one-time re-resolve (or the xref at read time) reprices them; new orders are correct from the start.

Verification plan

For doc cdc4bd49 after the fix, each line resolves to Harris’s tier: Pickle Chips $35.93, krauts $21.50, Red Onion $22.00 (matching the PO and erp_effective_prices). Assert: (a) getEffectivePrices hits via the canonical/xref id for a catalog-id match; (b) the Cmd+K search shows the customer price; (c) a no-tier product still shows the base list_price fallback (no $0); (d) residual check — count lines still priced at a non-tier value for a customer that has a tier (should be ~0).

Appendix — evidence

• erp_effective_prices (conn 4f234677, customer 206234, 11 rows): 774561→35.93, 775252/774396/774536/774574/774665/775062→21.50, 774771/775116/775120→22.00, 774793→39.60.
• Matched line ids 109031, 109009, 109013, 109026, 227923, 261915 — none present in erp_effective_prices.
• WhereFour sandbox /inventory (sandbox.wherefour.com) returns list_price=null, cost=null for SKUs 1216CD/1616RG/1616CC/1616KM/1616MK/0616PO.
• “Harris Teeter” tier in erp_pricing_rules_raw: Pickle Chips 35.93, all krauts 21.50, Red Onion 22.00 (matches the PO).