← back to Kravet Wrongjoin Reprice 2026 07

ROOT-CAUSE.md

59 lines

# ROOT CAUSE — DWKK wrong-join MAP mispricing (2026-07-15)

**Defect:** 425 ACTIVE DWKK products live at the WRONG SKU's MAP (360 under true MAP, ~$142.6k under-gap; 65 over). Live price == `kravet_authoritative_pricing.new_map` of the SKU sitting in the corrupt mirror column `shopify_products.mfr_sku`, not the product's true SKU (`metafields->global->Item`).

## Verified chain (read-only SQL against local dw_unified mirror, 2026-07-15)

### 1. The corrupt column was stamped from kravet_catalog, keyed by dw_sku

- 593 active DWKK mirror rows have `mfr_sku != global.Item` (re-verified live this session: exactly 593).
- **524/593 of the corrupt mirror values EXACTLY equal `kravet_catalog.mfr_sku` for the same `dw_sku`** — the mirror column's provenance is kravet_catalog, joined by dw_sku.
- Example (Zina Moire): mirror row `dw_sku=DWKK-100001, mfr_sku=114/16033.CS.0, shopify_id=gid://shopify/Product/7413448179763` and `kravet_catalog` row `DWKK-100001 → 114/16033.CS.0 → 7413448179763`. But the LIVE product 7413448179763 is Zina Moire, true SKU `8012112.30.0` (per global.Item / title / barcode). kravet_catalog has NO row for 8012112.30.0 pointing there.
- So the cross-wire is in **kravet_catalog's dw_sku↔Shopify-product assignment**, and the mirror column inherited it.

### 2. How kravet_catalog got cross-wired (April 2026 sheet-sync)

Tooling: `~/Projects/kravet-sheet-sync-2026-04-20/`
- `bulk_push_v1.py` created DWKK products and inserted `shopify_products` + `kravet_catalog` rows. **dw_sku collisions occurred** — the repo contains `delete_collision_pilots.py` specifically to delete collision-pilot products, i.e. the same DWKK number ended up attached to two different physical SKUs (one pre-existing on Shopify, one new from the sheet push).
- `fix_catalog_drift.sql` then "fixed" a `wrong_product_pointer` class by rewriting `kravet_catalog.shopify_product_id := kravet_dwkk_variant_map.product_gid` **joined ON dw_sku**. For a collided dw_sku, the surviving Shopify product (e.g. Zina Moire carrying variant sku DWKK-100001) got wired to the catalog row of a DIFFERENT SKU (114/16033.CS.0, whose sheet row had been assigned the same DWKK number).
- `kravet_dwkk_variant_map` (April 21 snapshot) confirms: `DWKK-100001 → 7413448179763, status ARCHIVED, price $0.00` — the product was ARCHIVED in April and is ACTIVE today, i.e. it was later restored (see step 3).
- Any mirror `mfr_sku` stamp that read kravet_catalog by dw_sku (or via the reconciled `shopify_product_id` pointer) after this wrote the wrong SKU into `shopify_products.mfr_sku`. No local script does `UPDATE shopify_products SET mfr_sku ... FROM kravet_catalog` — the stamp happened on the **Kamatera canonical side** (the local table is a replication mirror of `dw_unified_pub`), consistent with `bulk_push_v1.py`/reconcile passes that ran over SSH on Kamatera. The 69/593 rows not matching kravet_catalog today are consistent with later catalog corrections that never re-flowed to the column.
- NOTE (separate finding): `build_dwkk_variant_map.py` and `price_update_pass.py` contain a **hardcoded `shpat_` Shopify admin token** — secret-strip candidate.

### 3. The wrong-join REPRICER (smoking gun)

**`~/Projects/Designer-Wallcoverings/DW-Programming/archive-recovery/reprice-restore-kravet.js`** (mtime 2026-06-11, "Runs on Kamatera", per-vendor):

```sql
WITH cand AS (
  SELECT sp.shopify_id, sp.mfr_sku, regexp_replace(sp.mfr_sku,'\.0$','') AS sku_norm, ...
  FROM archive_alerts a JOIN shopify_products sp ON sp.shopify_id=a.shopify_id
  WHERE a.classification='WRONG_PRICED' AND sp.vendor=$1 AND (upper(sp.status)='ARCHIVED' OR ...))
SELECT cand.shopify_id, cand.mfr_sku, max(s.map_num) AS new_retail
FROM cand JOIN kravet_sheet_import_2026_04_20 s ON s.mfr_sku_norm = cand.sku_norm ...
```

- Joins the April sheet **ON the normalized corrupt mirror column `sp.mfr_sku`** and sets EVERY non-sample variant `price = max(s.map_num)` — the wrong-join SKU's MAP — while forcing `status:'active', published:true`.
- `kravet_sheet_import_2026_04_20.map_num` is the same April-2026 Kravet sheet MAP that `kravet_authoritative_pricing.new_map` carries — matching the observed signature (12/15 live spot-checks == wrong SKU's new_map exactly).
- Corroboration: `shopify/scripts/cadence/kravet-price-monitor.js` header (2026-06-11) records that "a parallel agent (reprice-restore-kravet.js on Kamatera) pushed wrong prices ($1521) to LIVE Shopify" — the defect was seen the day it ran, but only the >2×-MAP corruption class was chased; the wrong-join-at-plausible-MAP class survived.
- The DWKK-100001 story fits end-to-end: archived at April snapshot → classified WRONG_PRICED → restored + priced at $469.35 (= MAP of 114/16033.CS.0, its corrupt mirror mfr_sku) → live today.

Secondary exposure (same bug class, fixed this session):
- `shopify/scripts/cadence/kravet-price-monitor.js` — recurring watchdog joining `kravet_authoritative_pricing ON s.mfr_sku`; its `kravet_price_mismatch` output would mis-seed any future corrective pass.
- `shopify/scripts/cadence/add-kravet-roll-variants.js` — creates sellable roll variants priced off `kravet_master_price`/`kravet_catalog` joined ON `s.mfr_sku`.
- Verified NOT culpable: `bucketA-add-roll-variants.js` (prices from worklist cost), `kravet-reprice-dryrun/apply.js` (variant-id keyed, June 15 73-row pass), `kravet-sibling-map-verify.mjs` (barcode-keyed), `kravet25-reactivate.js` (11 W-code SKUs from a confirmed worklist). `kravet25-discover.js`'s regex-over-metafields extractor is sloppy but writes nothing; for Zina-class rows the wrong SKU is not in metafields, ruling it out as the column corruptor.

## Fix applied (local, reversible — commit `acc6224a` in ~/Projects/Designer-Wallcoverings)

1. **reprice-restore-kravet.js** (one-shot archived tooling): WARNING banner + runtime guard — fetches `global.Item` per product and SKIPS any row where it disagrees with the mirror `mfr_sku`; never prices unverified rows if ever re-run.
2. **kravet-price-monitor.js** (recurring): SKU resolution changed to `COALESCE(NULLIF(trim(metafields->'global'->'Item'->>'value'),''), mfr_sku)` in the join. Read-only tested against local PG: 4,026 candidates.
3. **add-kravet-roll-variants.js** (recurring): same COALESCE on both pricing joins.

All three `node --check` clean. NO Shopify writes, NO PG writes this session (read-only SQL only).

## Remediation status

- Gated reprice memo (425 rows → true MAP, batch/rollback/verify plan, vp-dw-commerce APPROVE): `~/.claude/yolo-queue/pending-approval/2026-07-15-dwkk-wrongjoin-map-reprice.md`
- **Residual: 167 of 593** mismatched rows lack an authoritative MAP for one or both SKUs (computed this session; prior estimate ~168) — follow-up once pricing exists.
- Follow-ups (not done here): correct the 593 mirror `mfr_sku` values to `global.Item` (canonical-side write, gated); cross-check the 425 against `disco_items` (reprice-restore also force-reactivated archived products); replicate the COALESCE fix in any Kamatera-side copies of these scripts (gated deploy); secret-strip the hardcoded `shpat_` token in the April tooling.