← back to Ticket System
snapshot before ticket run/group/dtd/stop feature
dfef161b168476b4ee0ac37dfd9ec264561402ec · 2026-08-13 08:08:59 -0700 · Steve Abrams
Files touched
A tk10524/build_commercial.shA tk10524/dedup.pyA tk10524/scrub.py
Diff
commit dfef161b168476b4ee0ac37dfd9ec264561402ec
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu Aug 13 08:08:59 2026 -0700
snapshot before ticket run/group/dtd/stop feature
---
tk10524/build_commercial.sh | 128 +++++++++++++++++++++++++++++++++++
tk10524/dedup.py | 62 +++++++++++++++++
tk10524/scrub.py | 160 ++++++++++++++++++++++++++++++++++++++++++++
3 files changed, 350 insertions(+)
diff --git a/tk10524/build_commercial.sh b/tk10524/build_commercial.sh
new file mode 100644
index 00000000..ea37ba3c
--- /dev/null
+++ b/tk10524/build_commercial.sh
@@ -0,0 +1,128 @@
+#!/bin/bash
+# TK-10524 — build Commercial Wallcovering aggregate NDJSON from dw_unified vendor catalogs.
+# Emits per-table NDJSON (one JSON object per row) into /tmp/tk10524_parts/<table>.jsonl
+# then a Python pass dedups across all tables by dw_sku (keep first) -> commercial.jsonl
+set -euo pipefail
+PSQL="psql -h /tmp -d dw_unified -tA -X"
+OUT=/tmp/tk10524_parts
+rm -rf "$OUT"; mkdir -p "$OUT"
+
+# Standard wallcovering type predicate (roll/wallpaper/mural)
+WC="(product_type ILIKE '%wallcover%' OR product_type ILIKE '%wallpaper%' OR product_type ILIKE '%mural%')"
+# Non-wallcovering junk types to drop when we KEEP null-typed rows (leed/mdc/koroseal exception)
+NONWC="product_type ILIKE '%fabric%' OR product_type ILIKE '%furniture%' OR product_type ILIKE '%hardware%' OR product_type ILIKE '%drapery%' OR product_type ILIKE '%film%' OR product_type ILIKE '%divider%' OR product_type ILIKE '%museum%' OR product_type ILIKE '%outdoor%'"
+IMG="image_url IS NOT NULL AND image_url <> ''"
+
+json_obj() {
+ # $1 = table, remaining fields inlined per-table via the SELECT below
+ :
+}
+
+# ---------- koroseal_catalog (exception: keep null/empty product_type; no price/in_stock/discontinued) ----------
+$PSQL -c "
+SELECT row_to_json(t) FROM (
+ SELECT id, mfr_sku, dw_sku, pattern_name, color_name, collection,
+ product_type,
+ material, width, roll_length AS length, repeat_v, repeat_h,
+ NULL::numeric AS price_retail, NULL::numeric AS price_trade,
+ NULL::boolean AS in_stock, NULL::boolean AS discontinued,
+ image_url, all_images, color_hex, ai_colors
+ FROM koroseal_catalog
+ WHERE ($IMG)
+ AND ( $WC OR product_type IS NULL OR product_type = '' )
+ AND NOT ( ($NONWC) )
+) t
+" > "$OUT/koroseal_catalog.jsonl"
+
+# ---------- innovations_catalog (koroseal-schema sibling; keep null-typed; has price_retail/trade) ----------
+$PSQL -c "
+SELECT row_to_json(t) FROM (
+ SELECT id, mfr_sku, dw_sku, pattern_name, color_name, collection,
+ product_type,
+ material, width, roll_length AS length, repeat_v, repeat_h,
+ price_retail, price_trade,
+ NULL::boolean AS in_stock, NULL::boolean AS discontinued,
+ image_url, all_images, color_hex, ai_colors
+ FROM innovations_catalog
+ WHERE ($IMG)
+ AND ( $WC OR product_type IS NULL OR product_type = '' )
+ AND NOT ( ($NONWC) )
+) t
+" > "$OUT/innovations_catalog.jsonl"
+
+# ---------- wolf_gordon / designtex / newmor / knoll (full standard schema, drop discontinued=true) ----------
+for T in wolf_gordon_catalog designtex_catalog newmor_catalog knoll_catalog; do
+$PSQL -c "
+SELECT row_to_json(t) FROM (
+ SELECT id, mfr_sku, dw_sku, pattern_name, color_name, collection,
+ product_type,
+ material, width, length, repeat_v, repeat_h,
+ price_retail, price_trade,
+ in_stock, discontinued,
+ image_url, all_images, color_hex, ai_colors
+ FROM $T
+ WHERE ($IMG)
+ AND (discontinued IS NOT TRUE)
+ AND $WC
+) t
+" > "$OUT/$T.jsonl"
+done
+
+# ---------- maharam (STRICT: only product_type ILIKE %wallcover%; drop discontinued) ----------
+$PSQL -c "
+SELECT row_to_json(t) FROM (
+ SELECT id, mfr_sku, dw_sku, pattern_name, color_name, collection,
+ product_type,
+ material, width, length, repeat_v, repeat_h,
+ price_retail, price_trade,
+ in_stock, discontinued,
+ image_url, all_images, color_hex, ai_colors
+ FROM maharam_catalog
+ WHERE ($IMG)
+ AND (discontinued IS NOT TRUE)
+ AND product_type ILIKE '%wallcover%'
+) t
+" > "$OUT/maharam_catalog.jsonl"
+
+# ---------- york_contract (large clean commercial line; product_type='Commercial Wallcovering'/'Wallcovering'/'Mural';
+# roll_length->length; retail_price->price_retail; no discontinued col (use image_rejected);
+# DO NOT emit the 'brand' col — it carries upstream identifiers (MDC/York/Magnolia/Stacy Garcia/etc). ----------
+$PSQL -c "
+SELECT row_to_json(t) FROM (
+ SELECT id, mfr_sku, dw_sku, pattern_name, color_name, collection,
+ product_type,
+ material, width, roll_length AS length, repeat_v, repeat_h,
+ retail_price AS price_retail, NULL::numeric AS price_trade,
+ on_shopify AS in_stock, NULL::boolean AS discontinued,
+ image_url, all_images, color_hex, ai_colors
+ FROM york_contract_catalog
+ WHERE ($IMG)
+ AND (image_rejected IS NOT TRUE)
+ AND ( $WC OR product_type = 'Commercial Wallcovering' OR product_type ILIKE '%mural%' )
+) t
+" > "$OUT/york_contract_catalog.jsonl"
+
+# ---------- mdc_catalog (RE-ADDED per Steve TK-10524; private-label as Phillipe Romano.
+# MDC is a wallcovering-only vendor: all rows product_type='wallcovering'.
+# Schema lacks length/repeat_v/repeat_h/all_images/price_trade/in_stock/discontinued -> NULL them.
+# CRITICAL: never emit private_label_vendor / private_label_collection / manufacturer_book /
+# original_title / dw_title / description — those carry the real MDC + upstream-designer
+# identity (Phillipe Romano, Candice Olson, Thom Filicia, Taniya Nayak, Vycon, Genon, etc.).
+# Accept null/empty product_type too (MDC is wallcovering-only). No discontinued col exists. ----------
+$PSQL -c "
+SELECT row_to_json(t) FROM (
+ SELECT id, mfr_sku, dw_sku, pattern_name, color_name, collection,
+ product_type,
+ material, width, NULL::text AS length, NULL::text AS repeat_v, NULL::text AS repeat_h,
+ price_retail, NULL::numeric AS price_trade,
+ on_shopify AS in_stock, NULL::boolean AS discontinued,
+ image_url, NULL::jsonb AS all_images, color_hex, ai_colors
+ FROM mdc_catalog
+ WHERE ($IMG)
+ AND ( product_type ILIKE '%wallcover%' OR product_type IS NULL OR product_type = '' )
+ AND NOT ( ($NONWC) )
+) t
+" > "$OUT/mdc_catalog.jsonl"
+
+echo "Per-table raw counts (post-filter, pre-dedup):"
+wc -l "$OUT"/*.jsonl
diff --git a/tk10524/dedup.py b/tk10524/dedup.py
new file mode 100644
index 00000000..3ba33b3c
--- /dev/null
+++ b/tk10524/dedup.py
@@ -0,0 +1,62 @@
+#!/usr/bin/env python3
+# TK-10524 — dedup per-table NDJSON parts across all tables by dw_sku (keep first),
+# emit combined commercial.jsonl. Reports per-source contributed counts.
+import json, os, sys
+
+PARTS = "/tmp/tk10524_parts"
+OUT = "/tmp/commercial.jsonl"
+
+# Deterministic table order for "keep first" on dw_sku collision.
+# TK-10524 contrarian-audit revision: dropped carnegie/leed_walls; added york_contract.
+# TK-10524 (Steve, 2026-08-13): mdc_catalog RE-ADDED (private-label as Phillipe Romano);
+# placed last so any dw_sku collision keeps the earlier table's row (MDC uses DWPP- prefix
+# so collisions are not expected, but keep-first is safe).
+ORDER = [
+ "koroseal_catalog", "wolf_gordon_catalog", "designtex_catalog", "newmor_catalog",
+ "knoll_catalog", "innovations_catalog", "maharam_catalog", "york_contract_catalog",
+ "mdc_catalog",
+]
+
+seen = set()
+contrib = {t: 0 for t in ORDER}
+dropped_dupe = {t: 0 for t in ORDER}
+dropped_nokey = 0
+total = 0
+
+with open(OUT, "w") as out:
+ for t in ORDER:
+ p = os.path.join(PARTS, f"{t}.jsonl")
+ if not os.path.exists(p):
+ continue
+ with open(p) as f:
+ for line in f:
+ line = line.strip()
+ if not line:
+ continue
+ row = json.loads(line)
+ # scrub any vendor-identifying fields defensively (belt-and-suspenders)
+ for bad in ("vendor", "vendor_name", "vendor_code", "private_label_vendor",
+ "private_label_collection", "manufacturer_book", "about_vendor",
+ "us_distributor"):
+ row.pop(bad, None)
+ key = (row.get("dw_sku") or "").strip()
+ if not key:
+ # no dw_sku -> can't dedup; keep it (unique enough) but track
+ dropped_nokey += 1
+ out.write(json.dumps(row, separators=(",", ":")) + "\n")
+ contrib[t] += 1
+ total += 1
+ continue
+ if key in seen:
+ dropped_dupe[t] += 1
+ continue
+ seen.add(key)
+ out.write(json.dumps(row, separators=(",", ":")) + "\n")
+ contrib[t] += 1
+ total += 1
+
+print(f"TOTAL rows written: {total}")
+print(f"(rows with no dw_sku, kept anyway: {dropped_nokey})")
+print("\nPer-source contributed (kept) / dropped-as-dupe:")
+for t in ORDER:
+ print(f" {t:24s} kept={contrib[t]:6d} dropped_dupe={dropped_dupe[t]}")
diff --git a/tk10524/scrub.py b/tk10524/scrub.py
new file mode 100644
index 00000000..7f5e1cf9
--- /dev/null
+++ b/tk10524/scrub.py
@@ -0,0 +1,160 @@
+#!/usr/bin/env python3
+# TK-10524 — vendor-name scrub pass over /tmp/commercial.jsonl.
+# Removes upstream-vendor names from public-visible fields (pattern_name, collection).
+import json, re
+
+SRC = "/tmp/commercial.jsonl"
+DST = "/tmp/commercial.scrubbed.jsonl"
+
+# Vendor tokens that must never be customer-facing.
+# TK-10524 contrarian-audit revision: carnegie/mdc/leed dropped as aggregated tables but
+# KEPT here as scrub tokens (mdc/leed still leak via york_contract's brand-derived data);
+# added york_contract + upstream York sub-brands (york/brewster/wallquest + designer lines).
+VENDOR_WORDS = [
+ "koroseal", "wolf gordon", "wolf-gordon", "wolfgordon", "designtex",
+ "newmor", "knoll", "innovations", "leed", "mdc", "carnegie", "maharam",
+ "versa", "momentum",
+ # MDC private-label identity (re-added TK-10524, Steve 2026-08-13). MDC resells as
+ # "Phillipe Romano"; both the real vendor "MDC" and the private-label name must be
+ # stripped from any customer-visible field. Cover both the correct + misspelled forms.
+ "mdc wallcoverings", "mdc contract", "mdc walls",
+ "phillipe romano", "philippe romano", # PL name — never customer-visible on this line
+ # Upstream designer sub-brands MDC resells (per vendor_registry / manufacturer_book).
+ # NOTE: these are MULTI-WORD on purpose so a bare flower color "Magnolia" survives —
+ # only the brand phrase "Magnolia Home" is scrubbed, never the standalone flower.
+ "thom filicia", "taniya nayak", "jonathan mark", "jonathan mark designs",
+ "candice olson couture", # base "candice olson" already listed in the York block below
+ # York-family upstream identifiers (york_contract onboarding)
+ "york contract", "york wallcoverings", "yorkwall", "brewster", "wallquest",
+ "chesapeake", "nextwall", "seabrook",
+ # York sub-brands / licensed designer lines that appear in collection/brand
+ "magnolia home", "joanna gaines", "stacy garcia", "candice olson",
+ "barclay butera", "aviva stanoff", "carol benson-cobb", "carol benson",
+ "hytex", # upstream commercial-vinyl brand (leaks as collection 'Hytex'/'Hytex Textiles')
+]
+
+# "york" is scrubbed as a standalone vendor word too, but ONLY when it is not part
+# of a legit multi-word value like the color "New York" / "Yorkshire". We handle
+# it via a dedicated guarded regex rather than the blanket VENDOR_WORDS loop.
+YORK_GUARD = re.compile(r"(?i)(?<![a-z])york(?:\s+contract|\s+wallcoverings)?(?![a-z])")
+YORK_SAFE = re.compile(r"(?i)\b(new\s+york|yorkshire)\b")
+
+# Strip a standalone "york" vendor token while preserving legit values that merely
+# contain it ("New York", "Yorkshire"). Returns the possibly-modified string.
+def strip_york(v):
+ if not v or YORK_SAFE.search(v):
+ return v
+ return YORK_GUARD.sub("", v)
+
+# collection values (case-insensitive, whole-value) that are just a vendor name -> null out.
+# We null the collection entirely when it's vendor-branded; genericizing to "Commercial
+# Wallcovering" would be redundant with the viewer title. A NULL collection just means
+# "uncollectioned" which the facet UI handles fine.
+def clean_collection(v):
+ if not v:
+ return v
+ low = v.lower()
+ for w in VENDOR_WORDS:
+ if w in low:
+ return None
+ # standalone "york" (not "New York"/"Yorkshire") -> vendor-branded collection, null it
+ if not YORK_SAFE.search(v) and YORK_GUARD.search(v):
+ return None
+ return v
+
+# pattern_name: strip a leading vendor phrase / "by <Vendor>" and any embedded vendor word.
+def clean_pattern(v):
+ if not v:
+ return v
+ orig = v
+ # kill "Koroseal Type 2 Vinyl " style leading vendor+material boilerplate
+ v = re.sub(r"(?i)^\s*koroseal\s+type\s*\d*\s*vinyl\s*", "", v)
+ # kill "by <Vendor>" / "<Vendor> " tokens anywhere
+ for w in VENDOR_WORDS:
+ v = re.sub(r"(?i)\bby\s+" + re.escape(w) + r"(\s+usa)?\b", "", v)
+ v = re.sub(r"(?i)\b" + re.escape(w) + r"(\s+usa)?\b", "", v)
+ v = strip_york(v)
+ v = re.sub(r"\s{2,}", " ", v).strip(" -,")
+ # if we scrubbed it to empty, fall back to a neutral label
+ if not v:
+ v = "Commercial Wallcovering"
+ return v
+
+# mfr_sku reaches the client via /api/skus + the search blob, so scrub vendor
+# prefixes there too (e.g. 'NEWMOR-ORRIN-CHEVRON' -> 'ORRIN-CHEVRON').
+def clean_mfr(v):
+ if not v:
+ return v
+ out = v
+ for w in VENDOR_WORDS:
+ out = re.sub(r"(?i)\b" + re.escape(w.replace(" ", "[ -]?")) + r"\b[-_ ]*", "", out)
+ out = strip_york(out)
+ out = re.sub(r"[-_ ]{2,}", "-", out).strip(" -_")
+ return out or v # never blank it out entirely (need a searchable ref)
+
+# color_name is displayed on cards AND in the search blob -> scrub vendor tokens.
+# 'Newmor Custom Chamomile' -> 'Chamomile'; a bare vendor word ('momentum') -> null.
+def clean_color(v):
+ if not v:
+ return v
+ out = v
+ for w in VENDOR_WORDS:
+ out = re.sub(r"(?i)\b" + re.escape(w) + r"\b", "", out)
+ # NOTE: do NOT strip_york on color_name — "New York" is a legit color (YORK_SAFE guards it,
+ # but even a bare "York" color is safer left intact than mangled). Vendor "york" never
+ # appears as a color value in the source, so skipping the guard here is correct.
+ out = re.sub(r"(?i)\bcustom\b", "", out) if out.lower() != v.lower() else out
+ out = re.sub(r"\s{2,}", " ", out).strip(" -,")
+ return out or None # blank after scrub -> null (better than a fake color)
+
+cnt = 0
+changed_pat = 0
+changed_col = 0
+changed_mfr = 0
+changed_color = 0
+with open(SRC) as f, open(DST, "w") as out:
+ for line in f:
+ line = line.strip()
+ if not line:
+ continue
+ r = json.loads(line)
+ p0, c0, m0, cn0 = r.get("pattern_name"), r.get("collection"), r.get("mfr_sku"), r.get("color_name")
+ r["pattern_name"] = clean_pattern(p0)
+ r["collection"] = clean_collection(c0)
+ r["mfr_sku"] = clean_mfr(m0)
+ r["color_name"] = clean_color(cn0)
+ if r["pattern_name"] != p0:
+ changed_pat += 1
+ if r["collection"] != c0:
+ changed_col += 1
+ if r["mfr_sku"] != m0:
+ changed_mfr += 1
+ if r["color_name"] != cn0:
+ changed_color += 1
+ out.write(json.dumps(r, separators=(",", ":")) + "\n")
+ cnt += 1
+
+print(f"scrubbed {cnt} rows; pattern_name changed={changed_pat}, collection changed={changed_col}, mfr_sku changed={changed_mfr}, color_name changed={changed_color}")
+
+# re-scan for residual leaks
+residual = 0
+with open(DST) as f:
+ for line in f:
+ r = json.loads(line)
+ fields = [str(r.get(k) or "") for k in ("pattern_name", "collection", "mfr_sku", "color_name")]
+ blob = " ".join(fields).lower()
+ for w in VENDOR_WORDS:
+ if w in blob:
+ # allow false positives like 'versailles'/'universal' that merely contain 'versa'
+ if w == "versa" and ("versail" in blob or "universal" in blob or "reversa" in blob or "conversa" in blob):
+ continue
+ residual += 1
+ print("RESIDUAL", w, "->", r.get("dw_sku"), repr(r.get("pattern_name")), repr(r.get("collection")))
+ break
+ else:
+ # bare-"york" residual check (guarded so "New York"/"Yorkshire" don't false-fire)
+ joined = " ".join(fields)
+ if YORK_GUARD.search(joined) and not YORK_SAFE.search(joined):
+ residual += 1
+ print("RESIDUAL york ->", r.get("dw_sku"), repr(r.get("pattern_name")), repr(r.get("collection")), repr(r.get("mfr_sku")))
+print(f"residual leaks: {residual}")
← a603071f auto-data-snapshot: 2026-08-12T08:54:38 (2 data files) — tk1
·
back to Ticket System
·
ticket board: group + multi-select, batched-DTD run-now yes/ a1c6c32e →