`artifacts/RECONCILIATION.md`

85 lines
# Newmor SKU normalization reconciliation (TK-10670)

Goal: bring the 543 mfr-coded LIVE Newmor products onto the canonical `DWNM-58xxxx` series
(Steve-ruled), deduping against the existing 1,294-row `newmor_catalog` (which already holds
the DWNM codes). NO new series. NO duplicates.

## Data topology (verified 2026-08-18, cycle 1)
- `newmor_catalog`: 1,294 rows; **1,285 already carry a distinct `DWNM-58xxxx` `dw_sku`** (9 lack one).
  Keyed internally by `mfr_sku` + legacy numeric REST id (`shopify_product_id`, e.g. 7810541092915).
- `shopify_products` (live mirror): 544 Newmor rows; **543 still mfr-coded** (`Brushed Steel BST-008`),
  only 1 already DWNM. Its `dw_sku` column is ALSO mfr-coded (not the DWNM code). Keyed by
  GraphQL gid (`gid://shopify/Product/4351...`) — a DIFFERENT id space than the catalog.
- ⇒ catalog↔live can only be bridged by `mfr_sku`. The numeric-REST-id ↔ gid map is absent.

## Match quality (the blocker)
- Raw exact mfr_sku join: 126/544.
- Normalized (upper+trim) join: **126/544 = 23.2%** — no better.
- Root cause: many LIVE `mfr_sku` are malformed collection-level junk
  (`ARONA-COMMERCIAL-WALLCOVERING-ARONA`, sku `Arona -`) while catalog codes are clean (`AR-103`).

## CORRECTED SCOPE (Cody-caught + verified, cycle 1) — it's a DISPOSITION, not a rename
The 544 live products are a **2019 import** (created 2019-11-16 → 2026-04); the catalog is a
**2026 scrape**. They are largely disjoint — matching even on the clean live metafield code yields
118/544. "Fuzzy-match harder to 90%" was WRONG framing. True 3-bucket disposition (from
`newmor_sku_reconcile`):

| bucket | count | @ $4.25 | action |
|--------|-------|---------|--------|
| A_MATCHED               | 126 | 125 | maps to a DWNM code → normalize (gated rewrite) |
| B_PATTERN_ONLY          | 180 | 180 | pattern in catalog, colorway isn't → recover by pattern+color match |
| C_NO_CATALOG_COUNTERPART| 235 | 234 | no catalog match → likely discontinued 2019 line → DISPOSITION call (archive vs re-scrape) |
| JUNK                    | 3   | 3   | malformed collection rows → archive/fix |

So only ~56% (A+B) is reconcilable to the current catalog; 235 need a Steve disposition; and
**539/544 are live at the $4.25 sample price** (see the priority gated memo — separate from the SKU project).

Cycle 2 first action (Cody-mandated): for the C bucket, count how many `live_pat` appear NOWHERE
in `newmor_catalog.pattern_name`; that number is the true discontinued scope. Then: (1) B-bucket
pattern+color recovery pass, (2) assign DWNM to the 9 catalog rows lacking dw_sku, (3) draft the
A-bucket (126) gated rewrite — the only high-confidence tier — for Steve.

## Staged artifact (reversible)
`newmor_sku_reconcile` table in dw_unified (Mac2 staging). Drop-to-undo. Columns:
`shopify_id, handle, live_sku, mfr_sku, k(normalized), target_dwnm, has_target, live_already_ok`.
Current: 126 matched / 418 unmatched-flagged / 0 already-ok.

## Refresh + verify vs CURRENT live state (2026-09-09, vp-dw-commerce)
Live Newmor = 542 (116 ACTIVE / 189 DRAFT / 237 ARCHIVED). Staging `newmor_sku_reconcile` = 544 rows.
Disposition × current live status (verified by gid join to a live Shopify pull):

| bucket | count | live status | @ $4.25 |
|--------|-------|-------------|---------|
| A_MATCHED               | 126 | 116 ACTIVE (showroom) + 9 DRAFT (Arona) + 1 deleted | 125 |
| B_PATTERN_ONLY          | 180 | 180 DRAFT (the "holds")                            | 180 |
| C_NO_CATALOG_COUNTERPART| 235 | 234 ARCHIVED + 1 deleted                           | 234 |
| JUNK                    | 3   | 3 ARCHIVED                                          | 3   |

Total at $4.25 = **542/544** (2 catalog-era rows carry null price). Clean mapping: 116 active +
189 draft (9 arona + 180 holds) + 237 archived (234 C + 3 JUNK). The disposition model is fully
consistent with current live state — no re-derive needed.

Key A-bucket facts (drive the gated rewrite memo): all 125 live A-bucket products are single-variant
(Sample only, $4.25 — Newmor is a quote-only showroom line, no sellable variant by design). NO
`dw_sku` metafield exists anywhere on the line (verified live ACTIVE + DRAFT + the onboarded alacha
shape) — DWNM identity is variant-SKU-only. `custom.manufacturer_sku` already holds the clean mfr
code. All 126 targets are distinct DWNM-58xxxx; new SKUs have 0 self-dupes + 0 live collisions.

## Gated (NOT executed — pending-approval, drafted 2026-09-09)
- **A-bucket variant-SKU rewrite** (125 live products → DWNM-58xxxx-Sample), restore-mapped:
  `pending-approval/2026-09-09-TK-10670-newmor-A-bucket-sku-normalize.md`
  (restore map: `artifacts/A-BUCKET-SKU-RESTORE-MAP.json`). ZERO metafield writes.
- **Consolidated vendor reply to Jill** (Arona status + specifier terms + 180-hold status):
  `pending-approval/2026-09-09-TK-10670-newmor-consolidated-vendor-reply-jill.md`
  (supersedes the split Arona + quote-terms drafts). DRAFT ONLY.
- **C-bucket disposition** (235 rows, 17 patterns 100% absent from catalog, already ARCHIVED) —
  Steve decision ARCHIVE vs RE-SCRAPE: `pending-approval/2026-09-09-TK-10670-newmor-C-bucket-disposition-decision.md`.
- Publish of catalog rows not yet on Shopify — still gated (unchanged).
- Any live price write — BLOCKED (Newmor is a showroom/quote-only line; no material prices per Steve 2026-09-08).

## Branch status
- SKU normalization: A-bucket (125) READY — gated rewrite memo drafted; B/C need vendor confirmation / decision.
- Metafield map: DEFINED (see METAFIELD-MAP.md); no dw_sku metafield on the line — do NOT fork the schema.
- Catalog refresh: BLOCKED — newmor-agent (Norman, :9695) ABSENT from pm2 (re-provisioning, out of scope).
- Pricing: PARKED — showroom/quote-only line; LBI Boyd quotes on request (no price sheet).