`pending-approval/done/designtex-unit-of-measure-drift.DONE.md`

122 lines
# GATED: Designtex full catalog fill-in + UOM flip + discontinued archive (supersedes UOM-only memo)

**Owner:** vp-dw-commerce · **Drafted:** 2026-06-23 (supersedes the 2026-06-23 UOM-only draft) · **Status:** AWAITING STEVE'S GO
**Type:** catalog data correction on LIVE products + canonical dw_unified writes → fully gated.
**$ cost:** $0 — crawl is local; Shopify writes are unmetered API; NO Gemini/AI used (all data is real vendor data). If any future description regen uses Gemini it will be costed separately (~$0.0006/img).

---

## What was done already (reversible/local — committed, NOT gated)

1. **Fixed the crawl.** The old `designtex-headless-scraper.js` (puppeteer, hardcoded portal
   password, `/product/`+`?page=N` selectors) silently produced 2-byte empty output. Root cause:
   wrong site model. shop.designtex.com is BigCommerce Stencil where each PATTERN is ONE public
   product page (e.g. `/gilded/`) carrying all colorways + full specs in static HTML — **no login
   needed.** DTD 3/3 → wrote a new lightweight plain-HTTP + cheerio parser:
   `scripts/designtex-bigcommerce-enrich.js`.
2. **Ran the full crawl** (234 pattern pages, $0 local). Result: **218 ok + 2 renamed-slug
   recoveries (bocce, kindred) = 220 patterns, 2,629 colorways**, each with 34-39 specs + per-colorway
   gallery images. **14 patterns 404 + absent from the vendor sitemap = genuinely discontinued.**
   Sidecars in `enrich-cache/designtex/` (gitignored, reproducible).
3. **Fresh live Shopify audit** (`audits/designtex-uom-fix/designtex-live-gap-2026-06-23.json`) and
   **fill-in plan** (`designtex-fillin-plan.json`) built and committed.

## Live gap matrix (308 live Designtex products, fresh 2026-06-23)

| Dimension | Finding |
|---|---|
| Status | 281 ACTIVE / 27 DRAFT / 0 archived |
| Prefix | 299 DWDX (Designtex) + 9 DWAH (Rocket — EXCLUDED, distinct line) |
| `unit_of_measure` | **231 YARD ✅ / 77 DRIFT "Priced Per Single Roll" ⚠️** (50 ACTIVE + 27 DRAFT). 0 blank. |
| Has image / width / description / sample | 308 / 308 / 308 / 308 ✅ (these are complete) |
| **Structured spec metafields** | **repeat, content, flammability, cleaning-code MISSING on ~all 308** — only `global.width`, `global.material`, `manufacturer_sku`, `pattern_name`, `color`, `dw_sku`, `custom.cost` are set. Specs currently live only as prose inside the description body, not as queryable metafields. |
| Title format | e.g. `Gilded, Plaster Wallcoverings \| Designtex` — has an extra `, ` + trailing word; house format is `Pattern Color \| Brand`. Minor; flagged, not blocking. |
| Description prose | Says "Priced per single roll" on the 77 drifted — contradicts YARD, must be corrected in the fill-in. |

## dw_unified catalog state (designtex_catalog, 2,831 rows)

- Local Mac2 mirror: UOM=YARD on all, width/fire_rating/primary-image/ai_description populated, but
  **`all_images` EMPTY on all 2,831, `maintenance`/care EMPTY on all 2,831, `pattern_repeat` empty on 2,001.**
- **Schema drift (flag):** canonical Kamatera `designtex_catalog` (2,831 rows, same count) is MISSING
  the columns local added: `unit_of_measure, our_price, net_cost, price_updated_at, roll_length`.
  Canonical DOES have `maintenance, pattern_repeat, all_images` (also empty). The dw_unified-first
  write must ADD `unit_of_measure` (+ the other 4) to canonical before/with the fill-in.

## Fill-in plan (`designtex-fillin-plan.json`) — 2,831 catalog rows

| Action | Count | Detail |
|---|---|---|
| **FILL** | **2,701** | set pattern_repeat (801; rest are genuine solids/textures w/ no repeat), maintenance/care (2,701), fire_rating (2,701), material/content, finish, width, **all_images ≥1 (2,701)**, **unit_of_measure=YARD**. 440 also carry a color-name correction from the live swatch label. |
| └ parent rows | 105 | rows whose mfr_sku was the URL slug (e.g. `gilded`,`betwixt`) — **DTD 3/3 (2026-06-23): these are REAL products (85 are LIVE ACTIVE); FILL them + backfill mfr_sku to the pattern's first real colorway SKU. NEVER archive a live product over a mfr_sku-hygiene issue.** Their existing color_name is preserved (not overwritten). |
| **ARCHIVE** | **130** | rows on the **14 discontinued patterns** (404 + not in vendor sitemap): burnish, imprint, kith, layer, plexus, tacit, talula, twinkle, zipper, circulate, zip-code, zip-line, silicone-element-celliant, spark. Per `Discontinued = ARCHIVE` rule (never draft, never delete). NONE of these 14 are currently live on Shopify, so this is catalog-only hygiene. |
| REVIEW | 0 | — |

### Dedupe finding (the "Gilded, Beige" double)
NOT a true duplicate. DB had two rows both named "beige" (mfr_sku 8380251 + 8380101). The live page
proves they are two DISTINCT colorways: **8380251 = Shell, 8380101 = Gleam**. The fill-in corrects
both names from the live swatch labels (part of the 440 color-corrections). The only genuine orphan
is `DWDX-220010` mfr_sku=`gilded` color "Charcoal Gray" — handled as a parent row (FILL + mfr_sku
backfill, since it's live ACTIVE), not archived.

---

## Proposed gated execution (NOT executed — for Steve's go)

**Order: PostgreSQL canonical FIRST, then Shopify** (standing rule).

### Step 1 — canonical dw_unified (Kamatera) — schema + fill-in
Add missing columns, then load the 2,701 FILL + flag 130 ARCHIVE. Run via the documented sudo path:
`ssh my-server "sudo -u postgres psql -d dw_unified -v ON_ERROR_STOP=1 -f <load.sql>"`.
(load.sql will be generated from `designtex-fillin-plan.json` once approved — it is a deterministic
UPDATE-by-id set; preview row count = 2,831.) Mirror the same to local Mac2.

### Step 2 — Shopify UOM flip on the 77 still-drifted (relabel-only, price untouched)
`node audits/designtex-uom-fix/flip-uom-live-77.mjs --execute`
Sets `global.unit_of_measure`→`YARD`, variant title `Single Roll`→`Yard`, swaps the
`Priced Per Single Roll` TAG→`Priced Per Yard`. Batches of 50, ≥90s gap. Dry-run verified (77 rows,
0 DWAH). Reads `designtex-uom-drift-live-77.json`.

### Step 3 — Shopify spec metafield fill on the live 308 (from dw_unified)
Write the structured spec metafields the live products lack (`global.repeat`, `global.Content`,
`global.FLAMMABILITY`, `global.Cleaning-Code`, length/weight/origin) from the now-complete
designtex_catalog, attach any missing colorway images, and rewrite the "Priced per single roll"
prose in descriptions. Script to be generated from the plan; daily-variant-cap aware, ≥90s batch gaps.

### Step 4 — Shopify archive the 14 discontinued patterns
Only if/when any become live (currently none are live) — `status: ARCHIVED`. Catalog rows flagged
`discontinued=true` in Step 1.

### Out of scope (separate gated follow-on)
Vendor sitemap shows **288 slugs not in our catalog** (mostly the `3m-di-noc-*` line). NET-NEW
ingestion is a different task (goes through the metered cadence + activation gate), NOT this fill-in.

---

## Steve, paste to proceed
- [ ] **Approve Step 1** — add `unit_of_measure`(+4 cols) to canonical dw_unified.designtex_catalog and load the 2,701-row fill-in + flag 130 discontinued (I'll generate load.sql from the committed plan for your review first).
- [ ] **Approve Step 2** — run `flip-uom-live-77.mjs --execute` (77 live products → YARD, relabel-only, price untouched).
- [ ] **Approve Step 3** — write structured spec metafields + fix "single roll" description prose on the live 308 from dw_unified.
- [ ] **Approve Step 4** — archive the 14 discontinued patterns if any go live (none currently live).
- [ ] (optional) Approve the title-format normalization `Gilded, Plaster Wallcoverings | Designtex` → `Gilded Plaster | Designtex`.

## Files
- `scripts/designtex-bigcommerce-enrich.js` — the working crawler (committed)
- `enrich-cache/designtex/*.json` — 219 sidecars + manifest (reproducible)
- `audits/designtex-uom-fix/designtex-live-gap-2026-06-23.json` — live gap matrix
- `audits/designtex-uom-fix/designtex-fillin-plan.json` — the 2,831-row fill-in/archive plan
- `audits/designtex-uom-fix/designtex-uom-drift-live-77.json` — the 77 still-drifted SKUs
- `audits/designtex-uom-fix/flip-uom-live-77.mjs` — gated UOM flip (dry-run by default)
- `audits/designtex-uom-fix/build-fillin-dataset.mjs` / `live-gap-audit.mjs` — regenerators

---

## ✅ EXECUTION COMPLETE — 2026-06-23 (vp-dw-commerce, Steve-approved batch)

All 4 active steps landed (verified):
- **Step 1** — canonical dw_unified fill-in loaded (2,701 FILL + 130 discontinued flagged). Commit c2703df4.
- **Step 2** — UOM flip to YARD on 77 drifted live + 25 tag residue + 3 Pebble metafield = 0 'Priced Per Single Roll' remaining live; relabel-only, price untouched. Commit b41a8b1c.
- **Step 3** — structured spec metafields (global.* + specs.*) + 'single roll'→'per yard' body prose on ALL 302 live DWDX (102 written this resume + 200 prior; 0 failed; DWAH Rocket excluded). Verified live: DWDX-220000 6/6 specs, body 'per yard'=true. Commit 6e1101f5.
- **Step 4** — 0/130 discontinued colorways live on Shopify → no archive action needed (catalog-only flag in Step 1). Commit 66c00d46.

$ cost: $0 (Shopify Admin API unmetered; no AI). Out-of-scope net-new ingestion (288 sitemap slugs incl. 3m-di-noc-*) remains a separate gated follow-on.