[object Object]

← back to Harlequin Sample Price Analysis

TK-10981: junk mfr_sku cleanup + fleet cost-gap deep-dive (read-only report + 2 gated batches)

dc38bb0bcb0e3a36d1ae121e76326ff48a6d48e0 · 2026-08-31 01:36:16 -0700 · Steve Abrams

Files touched

Diff

commit dc38bb0bcb0e3a36d1ae121e76326ff48a6d48e0
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Mon Aug 31 01:36:16 2026 -0700

    TK-10981: junk mfr_sku cleanup + fleet cost-gap deep-dive (read-only report + 2 gated batches)
---
 artifacts/JOB1-JOB2-REPORT.md                      |  92 +++++++++++++++++++
 artifacts/JOB1-innovations-realcode-backfill.csv   |  14 +++
 artifacts/JOB2-kravet-syncgap.csv                  | 101 +++++++++++++++++++++
 artifacts/tk10992/.last-ts                         |   1 +
 .../reconcile-rollback-20260831T082648Z.sql        |   9 ++
 .../reconcile-snapshot-20260831T082648Z.json       |   1 +
 6 files changed, 218 insertions(+)

diff --git a/artifacts/JOB1-JOB2-REPORT.md b/artifacts/JOB1-JOB2-REPORT.md
new file mode 100644
index 0000000..0fc33c4
--- /dev/null
+++ b/artifacts/JOB1-JOB2-REPORT.md
@@ -0,0 +1,92 @@
+# TK-10981 — junk mfr_sku cleanup + fleet cost-gap deep-dive (report)
+
+vp-dw-commerce · 2026-08-31 · READS $0 (local dw_unified mirror, `host=/tmp`). Zero writes fired.
+Codex second-opinion folded in (see "Codex caveat" below). No live scrapes. No fabrication.
+
+---
+
+## JOB 1 — junk `mfr_sku = 'TRUE'` (3,675 rows, 10 vendors)
+
+### Confirmed blast radius (matches the cycle-5 memo)
+| vendor | rows | ACTIVE | catalog table | dw_sku joins catalog? |
+|---|---:|---:|---|---|
+| Fentucci | 2,070 | 1 | fentucci/tokiwa | **NO (0)** |
+| Retro Walls | 946 | 1 | *(none exists)* | **NO** |
+| Fentucci Fabrics | 367 | 0 | — | **NO** |
+| Innovations USA | 129 | 77 | innovations_catalog | **YES (90/129)** |
+| Mind the Gap | 110 | 0 | mindthegap_catalog | **NO (0)** |
+| DW Home | 41 | 1 | *(house label)* | n/a |
+| Designer Wallcoverings | 5 | 1 | *(house label)* | n/a |
+| Designers Guild / Phillipe Romano / DW Bespoke | 7 | 3 | mixed | NO |
+
+**83 ACTIVE total (77 Innovations + 6 others). Identity intact — every row keeps a real `sku`/`dw_sku`.**
+
+### Root cause (confirmed, not guessed)
+The affected products are a **legacy import generation** whose `dw_sku` scheme (Fentucci `DWPA-*`,
+Retro Walls `DWH-*`, Mind the Gap `MIND-2020*`) **no longer exists in any current vendor catalog** —
+each catalog was re-scraped onto a new `dw_sku` scheme (Fentucci is now `DWNAT-*`/`DWFN-*`, Mind the
+Gap `DWMG-*`). So there is **no authoritative source to re-key against** for 3,490 of the 3,675 rows.
+A boolean (`has_mfr_sku`/availability flag) leaked into `mfr_sku` during those old scrapes.
+
+### Innovations USA — the one joinable vendor, but the catalog is HALF-CORRUPTED
+90 of 129 join `innovations_catalog` on `dw_sku`. The catalog's own `mfr_sku` splits into TWO classes:
+- **REAL codes** — `SOA-008`, `VEC-008`, `LAT-04`, `GRD-010` (uppercase pattern-prefix + digits). **13 rows. ALL ARCHIVED.**
+- **CORRUPTED codes** — `atra-8`, `ctor-4`, `tley-1` = the *tail* of the pattern name (sum**ATRA**,
+  ve**CTOR**, mo**TLEY**) with a lowercase suffix — a scraper truncation bug. **The 48 ACTIVE rows all map to these.**
+
+**Decision:** back-filling the 48 ACTIVE rows from a corrupted source **= propagating a scraper bug =
+fabrication → NOT ALLOWED.** Only the 13 real-shaped codes are candidates → **`JOB1-innovations-realcode-backfill.csv`** (gated).
+
+> **Codex caveat (folded in):** the 13 uppercase codes come from the *same compromised scrape* as the
+> corrupted ones, so their shape is **not proof** of correctness. Since Steve's rule forbids a live
+> re-scrape to corroborate against the original vendor page, the 13 are **SHAPE-ONLY-corroborated**.
+> Recommendation in the memo: **HOLD the 13 pending an authoritative Innovations catalog re-scrape**;
+> they are ARCHIVED (zero customer-facing urgency) so there is no reason to risk a wrong write now.
+
+### What is genuinely blocked / left as-is
+- **3,490 rows** (Fentucci, Retro Walls, Fentucci Fabrics, Mind the Gap, DW-house): **no authoritative
+  source** → left as-is. `NULL`-out is disallowed by rule, so the `'TRUE'` value stays until a real
+  mfr_sku is known (a future re-scrape/re-key project, out of scope here).
+- **48 ACTIVE Innovations**: source corrupted → left as-is.
+
+---
+
+## JOB 2 — fleet cost-gap deep-dive (ACTIVE + `cost_price` NULL/0)
+
+Raw count: **67,203 ACTIVE no-cost.** Separated into real margin-risk vs by-design:
+
+| class | rows | verdict |
+|---|---:|---|
+| **has dw_sku — *potential* sync-gap** | 21,403 | **mostly illusory** — see below |
+| private-label umbrella (Phillipe Romano / LA Fabrics) | 13,749 | quote-only by design |
+| genuine-unknown (no dw_sku key at all) | 13,054 | not recoverable (no key) |
+| contract/commercial (Koroseal, Carnegie, Wolf Gordon, Maharam, Designtex, Knoll) | 11,281 | quote-only by design |
+| flagged quote-only in registry (`do_not_price`/`display_prices=false`) | 5,778 | by design |
+| DW house-label | 1,938 | no external cost |
+
+### The "21,403 sync-gap" is almost entirely NOT recoverable
+I tested the **actual** join: ACTIVE no-cost products → each cost-bearing catalog (on `dw_sku`, cost
+via `price_trade`/`your_cost`/`net_cost`). Result across 13 cost-bearing catalogs (brunschwig, maharam,
+harlequin, kravet, newwall, osborne, thibaut, scalamandre, wolf_gordon, romo, lee_jofa, graham_brown, rebelwalls):
+
+**True recoverable sync-gap = ~102 rows total:** **Kravet 100 · Harlequin 1 · Graham Brown 1.**
+
+Same root cause as JOB 1: the catalogs carry cost, but they're keyed on a **newer `dw_sku` generation**
+than the live ACTIVE no-cost products (which mostly have a NULL/empty `dw_sku` — Thibaut 1,555/1,633
+have no dw_sku; Jeffrey Stevens 1,317/1,334; LA Fabrics 305/305). No key ⇒ nothing to sync.
+
+### The real remediation target (gated)
+**`JOB2-kravet-syncgap.csv` — 100 ACTIVE Kravet products**, `cost_price` NULL → backfill from
+`kravet_catalog.price_trade` (wholesale, $60.85–$481.95, verified 1:1 on dw_sku). Kravet is MAP-priced;
+this fills the **cost** field only (retail already MAP-floored) so margin math stops running blind.
+Harlequin + Graham Brown (1 each) ride along.
+
+**Bottom line: the fleet's ~67k no-cost ACTIVE is ~99.85% by-design or un-keyable; genuine
+recoverable margin-risk is ~102 rows.** The scary raw number is not a margin emergency.
+
+---
+
+## Artifacts
+- `artifacts/JOB1-innovations-realcode-backfill.csv` — 13 rows (SHAPE-ONLY, recommend HOLD)
+- `artifacts/JOB2-kravet-syncgap.csv` — 100 rows (clean, high-confidence)
+- Gated memos: `~/.claude/yolo-queue/pending-approval/TK-10981-{job1,job2}-*.md`
diff --git a/artifacts/JOB1-innovations-realcode-backfill.csv b/artifacts/JOB1-innovations-realcode-backfill.csv
new file mode 100644
index 0000000..42da8a9
--- /dev/null
+++ b/artifacts/JOB1-innovations-realcode-backfill.csv
@@ -0,0 +1,14 @@
+shopify_sku,dw_sku,status,old_mfr,new_mfr,title
+DWIN-15116,DWIN-15116,ARCHIVED,TRUE,VEC-008,Vector | Innovations USA
+DWIN-15180-Sample,DWIN-15180,ARCHIVED,TRUE,SOA-008,Sonar | Innovations USA
+DWIN-15186-Sample,DWIN-15186,ARCHIVED,TRUE,SOA-002,Sonar | Innovations USA
+DWIN-15187-Sample,DWIN-15187,ARCHIVED,TRUE,SOA-001,Sonar | Innovations USA
+DWIN-15295-Sample,DWIN-15295,ARCHIVED,TRUE,LAT-04,Lattice | Innovations USA
+DWIN-15296-Sample,DWIN-15296,ARCHIVED,TRUE,LAT-03,Lattice | Innovations USA
+DWIN-15297-Sample,DWIN-15297,ARCHIVED,TRUE,LAT-02,Lattice | Innovations USA
+DWIN-15298-Sample,DWIN-15298,ARCHIVED,TRUE,LAT-01,Lattice | Innovations USA
+DWIN-15346-Sample,DWIN-15346,ARCHIVED,TRUE,GRD-010,Grid Lock | Innovations USA
+DWIN-15406-Sample,DWIN-15406,ARCHIVED,TRUE,EPR-01,Empress | Innovations USA
+DWIN-15425-Sample,DWIN-15425,ARCHIVED,TRUE,CRD-01,Cordillera | Innovations USA
+DWIN-15437-Sample,DWIN-15437,ARCHIVED,TRUE,ASR-003,Ashlar | Innovations USA
+DWIN-15438-Sample,DWIN-15438,ARCHIVED,TRUE,ASR-002,Ashlar | Innovations USA
diff --git a/artifacts/JOB2-kravet-syncgap.csv b/artifacts/JOB2-kravet-syncgap.csv
new file mode 100644
index 0000000..a032249
--- /dev/null
+++ b/artifacts/JOB2-kravet-syncgap.csv
@@ -0,0 +1,101 @@
+shopify_sku,dw_sku,status,new_cost_price,dw_sell_price
+DWKK-100002,DWKK-100002,ACTIVE,134.40,235.66
+DWKK-100033,DWKK-100033,ACTIVE,94.50,171.04
+DWKK-100034,DWKK-100034,ACTIVE,94.50,171.04
+DWKK-100040,DWKK-100040,ACTIVE,99.50,180.09
+DWKK-100042,DWKK-100042,ACTIVE,181.65,319.28
+DWKK-100273,DWKK-100273,ACTIVE,75.55,132.94
+DWKK-100276,DWKK-100276,ACTIVE,102.85,180.45
+DWKK-100279,DWKK-100279,ACTIVE,102.85,180.45
+DWKK-100280,DWKK-100280,ACTIVE,102.85,180.45
+DWKK-100281,DWKK-100281,ACTIVE,102.85,180.45
+DWKK-100283,DWKK-100283,ACTIVE,102.85,180.45
+DWKK-100284,DWKK-100284,ACTIVE,102.85,180.45
+DWKK-100285,DWKK-100285,ACTIVE,102.85,180.45
+DWKK-100288,DWKK-100288,ACTIVE,102.85,180.45
+DWKK-100291,DWKK-100291,ACTIVE,102.85,180.45
+DWKK-100297,DWKK-100297,ACTIVE,102.85,180.45
+DWKK-100304,DWKK-100304,ACTIVE,181.65,319.28
+DWKK-100309,DWKK-100309,ACTIVE,186.90,357.29
+DWKK-100314,DWKK-100314,ACTIVE,203.70,357.29
+DWKK-100318,DWKK-100318,ACTIVE,153.30,269.86
+DWKK-100319,DWKK-100319,ACTIVE,204.75,359.19
+DWKK-100330,DWKK-100330,ACTIVE,242.55,425.70
+DWKK-100364,DWKK-100364,ACTIVE,192.15,338.28
+DWKK-100368,DWKK-100368,ACTIVE,192.15,338.28
+DWKK-100369,DWKK-100369,ACTIVE,192.15,338.28
+DWKK-100370,DWKK-100370,ACTIVE,192.15,338.28
+DWKK-100371,DWKK-100371,ACTIVE,192.15,338.28
+DWKK-100375,DWKK-100375,ACTIVE,192.15,338.28
+DWKK-100378,DWKK-100378,ACTIVE,207.90,376.29
+DWKK-100379,DWKK-100379,ACTIVE,207.90,376.29
+DWKK-100384,DWKK-100384,ACTIVE,232.05,408.60
+DWKK-100523,DWKK-100523,ACTIVE,140.70,246.88
+DWKK-100529,DWKK-100529,ACTIVE,181.65,319.28
+DWKK-100530,DWKK-100530,ACTIVE,270.90,473.85
+DWKK-100534,DWKK-100534,ACTIVE,270.90,473.85
+DWKK-100535,DWKK-100535,ACTIVE,270.90,473.85
+DWKK-100539,DWKK-100539,ACTIVE,160.65,275.57
+DWKK-100549,DWKK-100549,ACTIVE,253.05,444.71
+DWKK-100551,DWKK-100551,ACTIVE,253.05,444.71
+DWKK-100553,DWKK-100553,ACTIVE,261.45,459.91
+DWKK-100570,DWKK-100570,ACTIVE,207.90,376.29
+DWKK-100580,DWKK-100580,ACTIVE,134.40,235.66
+DWKK-100581,DWKK-100581,ACTIVE,134.40,235.66
+DWKK-100591,DWKK-100591,ACTIVE,118.65,209.05
+DWKK-100592,DWKK-100592,ACTIVE,118.65,209.05
+DWKK-100594,DWKK-100594,ACTIVE,118.65,209.05
+DWKK-100600,DWKK-100600,ACTIVE,207.90,376.29
+DWKK-100607,DWKK-100607,ACTIVE,206.85,374.30
+DWKK-100608,DWKK-100608,ACTIVE,207.90,376.29
+DWKK-100614,DWKK-100614,ACTIVE,169.05,296.65
+DWKK-100616,DWKK-100616,ACTIVE,131.25,228.96
+DWKK-100622,DWKK-100622,ACTIVE,131.25,228.96
+DWKK-100627,DWKK-100627,ACTIVE,226.80,399.10
+DWKK-100629,DWKK-100629,ACTIVE,226.80,399.10
+DWKK-100635,DWKK-100635,ACTIVE,181.65,319.28
+DWKK-100645,DWKK-100645,ACTIVE,208.95,351.58
+DWKK-100646,DWKK-100646,ACTIVE,208.95,351.58
+DWKK-100653,DWKK-100653,ACTIVE,207.90,376.29
+DWKK-100657,DWKK-100657,ACTIVE,244.65,428.05
+DWKK-100664,DWKK-100664,ACTIVE,267.75,471.31
+DWKK-100665,DWKK-100665,ACTIVE,243.60,471.31
+DWKK-100672,DWKK-100672,ACTIVE,179.55,314.57
+DWKK-100674,DWKK-100674,ACTIVE,160.65,283.17
+DWKK-100675,DWKK-100675,ACTIVE,160.65,283.17
+DWKK-100679,DWKK-100679,ACTIVE,152.25,266.79
+DWKK-100688,DWKK-100688,ACTIVE,150.15,262.81
+DWKK-100690,DWKK-100690,ACTIVE,152.25,266.79
+DWKK-100704,DWKK-100704,ACTIVE,261.45,459.91
+DWKK-100709,DWKK-100709,ACTIVE,207.90,376.29
+DWKK-100711,DWKK-100711,ACTIVE,242.55,425.70
+DWKK-100714,DWKK-100714,ACTIVE,204.75,359.19
+DWKK-100718,DWKK-100718,ACTIVE,268.80,473.21
+DWKK-100735,DWKK-100735,ACTIVE,481.95,846.15
+DWKK-100739,DWKK-100739,ACTIVE,200.55,338.28
+DWKK-100740,DWKK-100740,ACTIVE,200.55,338.28
+DWKK-100744,DWKK-100744,ACTIVE,193.20,330.68
+DWKK-100755,DWKK-100755,ACTIVE,192.15,338.28
+DWKK-100757,DWKK-100757,ACTIVE,192.15,338.28
+DWKK-100758,DWKK-100758,ACTIVE,278.25,487.78
+DWKK-100759,DWKK-100759,ACTIVE,278.25,487.78
+DWKK-100852,DWKK-100852,ACTIVE,140.70,254.66
+DWKK-100853,DWKK-100853,ACTIVE,140.70,254.66
+DWKK-100854,DWKK-100854,ACTIVE,140.70,254.66
+DWKK-100857,DWKK-100857,ACTIVE,140.70,254.66
+DWKK-100859,DWKK-100859,ACTIVE,140.70,254.66
+DWKK-100864,DWKK-100864,ACTIVE,140.70,254.66
+DWKK-100883,DWKK-100883,ACTIVE,140.70,254.66
+DWKK-100888,DWKK-100888,ACTIVE,140.70,254.66
+DWKK-100892,DWKK-100892,ACTIVE,140.70,254.66
+DWKK-100893,DWKK-100893,ACTIVE,140.70,254.66
+DWKK-100896,DWKK-100896,ACTIVE,140.70,254.66
+DWKK-100909,DWKK-100909,ACTIVE,140.70,254.66
+DWKK-100913,DWKK-100913,ACTIVE,140.70,254.66
+DWKK-100916,DWKK-100916,ACTIVE,140.70,254.66
+DWKK-100921,DWKK-100921,ACTIVE,140.70,254.66
+DWKK-100929,DWKK-100929,ACTIVE,140.70,254.66
+DWKK-100937,DWKK-100937,ACTIVE,140.70,254.66
+DWKK-100941,DWKK-100941,ACTIVE,140.70,254.66
+DWKK-102153,DWKK-102153,ACTIVE,60.85,113.94
+DWKK-102270,DWKK-102270,ACTIVE,74.50,134.84
diff --git a/artifacts/tk10992/.last-ts b/artifacts/tk10992/.last-ts
new file mode 100644
index 0000000..dad46cb
--- /dev/null
+++ b/artifacts/tk10992/.last-ts
@@ -0,0 +1 @@
+TS=20260831T082648Z
diff --git a/artifacts/tk10992/reconcile-rollback-20260831T082648Z.sql b/artifacts/tk10992/reconcile-rollback-20260831T082648Z.sql
new file mode 100644
index 0000000..be7f40a
--- /dev/null
+++ b/artifacts/tk10992/reconcile-rollback-20260831T082648Z.sql
@@ -0,0 +1,9 @@
+-- TK-10992 reconcile ROLLBACK — restores 3 thibaut_catalog rows to pre-reconcile state (2026-08-31)
+BEGIN;
+-- row 56018: dw_sku DWTT-74451 -> back to DWTT-72986
+UPDATE thibaut_catalog SET dw_sku='DWTT-72986' WHERE id=56018;
+-- row 58251: shopify_product_id NULL -> back to 7865272434739
+UPDATE thibaut_catalog SET shopify_product_id='7865272434739' WHERE id=58251;
+-- row 56484: shopify_product_id 7865272401971 -> back to 7865272434739
+UPDATE thibaut_catalog SET shopify_product_id='7865272434739' WHERE id=56484;
+COMMIT;
diff --git a/artifacts/tk10992/reconcile-snapshot-20260831T082648Z.json b/artifacts/tk10992/reconcile-snapshot-20260831T082648Z.json
new file mode 100644
index 0000000..f3794cc
--- /dev/null
+++ b/artifacts/tk10992/reconcile-snapshot-20260831T082648Z.json
@@ -0,0 +1 @@
+[{"id":56018,"mfr_sku":"9022","pattern_name":"Aida","color_name":"Brown on Beige","dw_sku":"DWTT-72986","shopify_product_id":"7865272434739","status":"active"}, {"id":56484,"mfr_sku":"AT1411","pattern_name":"Spot On","color_name":"Beige and Pearl","dw_sku":"DWTT-72986","shopify_product_id":"7865272434739","status":"transferred_to_anna_french"}, {"id":58251,"mfr_sku":"TWW14586","pattern_name":"Spiro Wide Width","color_name":"Neutral and Blue","dw_sku":"DWTT-74451","shopify_product_id":"7865272434739","status":"active"}]

← ea54ff5 chore: add package.json v1.0.0 (session close) — 25 tests gr  ·  back to Harlequin Sample Price Analysis  ·  cycle 3: read-only verification — 25/25 green, cost-gap clos 98be7db →