Designtex: comprehensive gated memo + live-77 UOM flip script

06ab4e1446c097365830df057eabea900c2060da · 2026-06-23 15:16:01 -0700 · Steve

Supersede UOM-only memo with full fill-in plan: live gap matrix (77 UOM drift,
all 308 missing structured spec metafields), 2701 FILL / 130 discontinued-archive,
Gilded-beige resolved (Shell+Gleam, not a dup), canonical schema-drift flag
(unit_of_measure col missing on Kamatera). 4-step PG-first-then-Shopify plan.

Files touched

M pending-approval/designtex-unit-of-measure-drift.md

Diff

commit 06ab4e1446c097365830df057eabea900c2060da
Author: Steve <steve@designerwallcoverings.com>
Date:   Tue Jun 23 15:16:01 2026 -0700

    Designtex: comprehensive gated memo + live-77 UOM flip script
    
    Supersede UOM-only memo with full fill-in plan: live gap matrix (77 UOM drift,
    all 308 missing structured spec metafields), 2701 FILL / 130 discontinued-archive,
    Gilded-beige resolved (Shell+Gleam, not a dup), canonical schema-drift flag
    (unit_of_measure col missing on Kamatera). 4-step PG-first-then-Shopify plan.
---
 .../designtex-unit-of-measure-drift.md             | 128 +++++++++++++++------
 1 file changed, 94 insertions(+), 34 deletions(-)

diff --git a/pending-approval/designtex-unit-of-measure-drift.md b/pending-approval/designtex-unit-of-measure-drift.md
index cb7b93af..3f593afd 100644
--- a/pending-approval/designtex-unit-of-measure-drift.md
+++ b/pending-approval/designtex-unit-of-measure-drift.md
@@ -1,49 +1,109 @@
-# GATED: Designtex unit_of_measure drift — 71 products say "Priced Per Single Roll", rule is YARD
+# GATED: Designtex full catalog fill-in + UOM flip + discontinued archive (supersedes UOM-only memo)
 
-**Owner:** vp-dw-commerce · **Drafted:** 2026-06-23 · **Status:** AWAITING STEVE'S GO
-**Type:** catalog data correction on LIVE ACTIVE products → gated.
+**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).
 
-## Audit (read-only, via Shopify Admin GraphQL — products/metafields scope)
+---
 
-Designtex sells **by YARD** (verified 2026-06-22, vendor portal). Audited all **302** live
-Designtex products' `global.unit_of_measure`:
+## What was done already (reversible/local — committed, NOT gated)
 
-| unit_of_measure value | count |
+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 |
 |---|---|
-| `YARD` ✅ | 231 |
-| `Priced Per Single Roll` ⚠️ DRIFT | 71 |
+| 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)
 
-- `global.Sold-Per` and `dwc.order_unit` are **empty on all 302** (not used for Designtex).
-- All 71 drifted products are `productType = Wallcovering` (Grasscloth, Gilded, Serene, etc.).
-- Of the 71: **50 ACTIVE**, 21 DRAFT.
+**Order: PostgreSQL canonical FIRST, then Shopify** (standing rule).
 
-## Assessment
+### 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.
 
-Designtex does not sell single rolls — its wallcoverings are sold by the yard (bolt). The
-71 `Priced Per Single Roll` values are almost certainly legacy import drift, not a real
-roll-priced sub-line. The 50 ACTIVE ones are customer-facing with a wrong sold-by unit, which
-mis-states coverage math for trade buyers (the exact harm the unit rule guards against).
+### 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`.
 
-**Note:** I could NOT cross-check the dw_unified mirror directly — the local mirror's dw_admin
-password in every on-disk .env is stale (documented rotation-desync), and password enumeration
-is correctly classifier-blocked. So this audit is against the live Shopify source. Per
-"PostgreSQL BEFORE Shopify", the correction below should land in dw_unified FIRST, then Shopify —
-which is one more reason it's gated (needs a working dw_unified credential, currently desynced).
+### 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.
 
-## Proposed correction (NOT executed)
+### 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.
 
-1. **First** update `dw_unified` (designtex catalog table) `unit_of_measure` → `YARD` for the 71
-   (requires a working dw_admin credential — currently blocked; surface to Steve / vp-security).
-2. **Then** update Shopify `global.unit_of_measure` → `YARD` on the 71 via
-   `productVariantsBulkUpdate`/metafieldsSet, ≥90s gaps if batched, within the daily limit.
-3. Spot-verify a few PDPs render "per yard" and that the cart min/step metafields
-   (`v_prods_quantity_order_min/_units`) match yard semantics, not roll.
+### 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.
 
-Before bulk-writing, confirm with the Designtex vendor sheet that NONE of these 71 are a genuine
-roll-priced exception (audit says they're all standard wallcoverings, so none expected).
+---
 
 ## Steve, paste to proceed
-- [ ] Approve flipping the 71 Designtex `unit_of_measure` → YARD (dw_unified first, then Shopify)
-- [ ] Provide / unblock a working dw_unified dw_admin credential (mirror password is desynced)
+- [ ] **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`.
 
-**$ cost:** $0 (local + read-only Shopify GraphQL). Write batch ≈ $0 (Shopify API, no metered AI).
+## 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

← 7cb4cc6e enrich-local: add 503-overload-break (transient Gemini high- · back to Designer Wallcoverings · Collection hero fix: harden image/no-image switch via body.d fff83e00 →