← back to Reid Witlin Onboarding
Build deduplicated Reid Witlin onboarding batch
de8a37226e63529015f1dd47a98af4ae16e01e39 · 2026-09-02 16:11:23 -0700 · Steve Abrams
Files touched
A archive_discontinued.pyM build_batch2.pyM create-results.jsonM create2.py
Diff
commit de8a37226e63529015f1dd47a98af4ae16e01e39
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed Sep 2 16:11:23 2026 -0700
Build deduplicated Reid Witlin onboarding batch
---
archive_discontinued.py | 41 +++++++++++++++
build_batch2.py | 101 +++++++++++++++++--------------------
create-results.json | 2 +-
create2.py | 129 +++++++++++++++++++++++++++++++++++++++++-------
4 files changed, 196 insertions(+), 77 deletions(-)
diff --git a/archive_discontinued.py b/archive_discontinued.py
new file mode 100644
index 0000000..5d29400
--- /dev/null
+++ b/archive_discontinued.py
@@ -0,0 +1,41 @@
+#!/usr/bin/env python3
+"""Archive the single Steve-approved discontinued Reid Witlin product."""
+import json, os, urllib.request
+
+PID = "gid://shopify/Product/7774951604275"
+DRY_RUN = os.environ.get("DRY_RUN", "1") != "0"
+
+def token():
+ for line in open(os.path.expanduser("~/Projects/secrets-manager/.env")):
+ if line.startswith("SHOPIFY_ADMIN_TOKEN="):
+ return line.split("=", 1)[1].strip().strip('"')
+ raise SystemExit("SHOPIFY_ADMIN_TOKEN not found")
+
+def gql(query, variables):
+ req = urllib.request.Request(
+ "https://designer-laboratory-sandbox.myshopify.com/admin/api/2024-10/graphql.json",
+ json.dumps({"query": query, "variables": variables}).encode(),
+ {"X-Shopify-Access-Token": token(), "Content-Type": "application/json"})
+ return json.load(urllib.request.urlopen(req, timeout=90))
+
+READ = """query($id: ID!) { product(id: $id) { id handle title status vendor } }"""
+ARCHIVE = """mutation($input: ProductInput!) { productUpdate(input: $input) { product { id handle title status } userErrors { field message } } }"""
+
+before = (gql(READ, {"id": PID}).get("data") or {}).get("product")
+if (not before or before.get("handle") != "pass-the-torch-concrete-architectural-fabrics"
+ or before.get("title") != "Pass the Torch Concrete"
+ or before.get("vendor") != "Architectural Fabrics"):
+ raise SystemExit(f"identity check failed: {before}")
+print("before", json.dumps(before, sort_keys=True))
+if DRY_RUN:
+ print("DRY-RUN: would archive; no write")
+else:
+ result = gql(ARCHIVE, {"input": {"id": PID, "status": "ARCHIVED"}})
+ print("mutation", json.dumps(result, sort_keys=True))
+ errors = (((result.get("data") or {}).get("productUpdate") or {}).get("userErrors") or [])
+ if result.get("errors") or errors:
+ raise SystemExit("archive failed")
+ after = (gql(READ, {"id": PID}).get("data") or {}).get("product")
+ print("after", json.dumps(after, sort_keys=True))
+ if not after or after.get("status") != "ARCHIVED":
+ raise SystemExit("archive verification failed")
diff --git a/build_batch2.py b/build_batch2.py
index 4345caf..aa558cd 100644
--- a/build_batch2.py
+++ b/build_batch2.py
@@ -5,19 +5,12 @@ Reid Witlin (rwltd.com) onboarding batch v2 — CORRECTED scope, post 2026-09-01
Supersedes build_batch.py (which targeted the stale 1,071-row/DWDQ-prefix plan,
now quarantined — see _superseded/reid-witlin-rwltd-onboarding-2026-07-30-*.md).
-Two genuine candidate pools, both confirmed still-live on rwltd.com by the
-2026-09-01 re-scrape, neither overlapping the 190 already-live products:
-
- Pool A ("reconfirmed-old", 779 rows) — original Feb-2026 full-detail scrape,
- already carries a minted DWKR-19xxxx dw_sku, full spec_* fields, town
- mapping, and gallery_images. Reuses build_batch.py's proven per-colorway
- fuzzy image-match (these already have real dw_sku — DO NOT re-mint).
-
- Pool B ("new-gap", 382 rows) — colorways first seen in the 2026-09-01
- re-scrape, feed-only data (title/handle/price/one image, no spec_* or
- town). No dw_sku yet -> mint continuing the live DWKR- band from 191394.
- Uses the row's own image_url directly (already the vendor's per-colorway
- primary image — no fuzzy matching needed).
+Reconciles the 382 new-gap colorways Steve approved: rows first seen in the
+2026-09-01 re-scrape, feed-only data (title/handle/price/one image, no spec_*
+or town), and no ids on their refreshed staging rows. Canonical dedup excludes
+already-live registry matches, reuses reserved DWKR identities, and mints only
+truly new identities after the registry maximum. The row's own image_url is the
+vendor's per-colorway primary image.
OUTPUT ONLY — writes local CSVs + summary.json. Does NOT touch Shopify or
write anything to dw_unified. The live create is a separate, Steve-gated step
@@ -28,7 +21,6 @@ import json, csv, re, subprocess, os
OUT = os.path.dirname(os.path.abspath(__file__))
VENDOR = "Architectural Fabrics"
SAMPLE_PRICE = "4.25"
-DWKR_NEXT_START = 191394 # confirmed max minted DWKR- number today is 191393
def psql_json(sql):
wrapped = f"SELECT COALESCE(json_agg(t),'[]') FROM ({sql}) t;"
@@ -38,6 +30,14 @@ def psql_json(sql):
raise SystemExit(f"psql failed: {r.stderr}")
return json.loads(r.stdout.strip() or "[]")
+def canonical_next_dwkr():
+ rows = psql_json("""
+ SELECT COALESCE(MAX((substring(dw_sku from 'DWKR-([0-9]+)'))::bigint), 0) + 1 AS n
+ FROM dw_sku_registry
+ WHERE dw_sku ~ '^DWKR-[0-9]+$'
+ """)
+ return int(rows[0]["n"])
+
def slugify(s):
return re.sub(r'-+', '-', re.sub(r'[^a-z0-9]+', '-', (s or '').lower())).strip('-')
@@ -120,50 +120,34 @@ def rec_common(mfr, dw_sku, pat, town, colorway_disp, product_type_, img, conf,
def main():
ready, held = [], []
-
- # Pool A: reconfirmed-old, already has dw_sku minted -- reuse it verbatim.
- pool_a = psql_json("""
- SELECT mfr_sku, dw_sku, pattern_name, color_name, virginia_town, spec_type,
- spec_width, spec_actual_width, spec_content, spec_repeat, spec_flamecode,
- spec_origin, spec_cleaning, spec_finish, spec_abrasion,
- gallery_images, ai_tags, ai_styles
- FROM rwltd_catalog
- WHERE dw_sku IS NOT NULL AND dw_sku <> ''
- AND (shopify_product_id IS NULL OR shopify_product_id='')
- AND (excluded IS NULL OR excluded=false)
- AND updated_at::date >= '2026-09-01'
- ORDER BY mfr_sku
- """)
seen = set()
- for row in pool_a:
- row["_pool"] = "A"
- mfr = row["mfr_sku"]
- if mfr in seen:
- continue
- seen.add(mfr)
- pat = row.get("pattern_name") or ""
- town = (row.get("virginia_town") or "").strip()
- cw = colorway_of(mfr, pat)
- gallery = row.get("gallery_images") or []
- img, conf = recover_image(cw, gallery)
- rec = rec_common(mfr, row["dw_sku"], pat, town, cw.replace('-', ' ').title(),
- product_type(row), img, conf, row)
- (ready if img and conf in ("exact", "prefix") else held).append(rec)
-
- # Pool B: new-gap, no dw_sku yet -- mint continuing the live DWKR- band.
- pool_b = psql_json("""
- SELECT mfr_sku, pattern_name, color_name, virginia_town, spec_type,
- spec_width, spec_actual_width, spec_content, spec_repeat, spec_flamecode,
- spec_origin, spec_cleaning, spec_finish, spec_abrasion,
- gallery_images, ai_tags, ai_styles, image_url
+ pool_total = psql_json("""
+ SELECT COUNT(DISTINCT mfr_sku) AS n
FROM rwltd_catalog
WHERE (dw_sku IS NULL OR dw_sku='')
AND (shopify_product_id IS NULL OR shopify_product_id='')
AND (excluded IS NULL OR excluded=false)
AND created_at::date >= '2026-09-01'
- ORDER BY mfr_sku
+ """)[0]["n"]
+ # Approved new-gap only: no legacy/reconfirmed-old pool is in scope.
+ pool_b = psql_json("""
+ SELECT c.mfr_sku, c.pattern_name, c.color_name, c.virginia_town, c.spec_type,
+ c.spec_width, c.spec_actual_width, c.spec_content, c.spec_repeat, c.spec_flamecode,
+ c.spec_origin, c.spec_cleaning, c.spec_finish, c.spec_abrasion,
+ c.gallery_images, c.ai_tags, c.ai_styles, c.image_url,
+ r.dw_sku AS reserved_dw_sku, r.shopify_product_id AS registry_product_id
+ FROM rwltd_catalog c
+ LEFT JOIN dw_sku_registry r
+ ON r.vendor_prefix='DWKR' AND r.mfr_sku=c.mfr_sku
+ WHERE (c.dw_sku IS NULL OR c.dw_sku='')
+ AND (c.shopify_product_id IS NULL OR c.shopify_product_id='')
+ AND (c.excluded IS NULL OR c.excluded=false)
+ AND c.created_at::date >= '2026-09-01'
+ AND (r.shopify_product_id IS NULL OR r.shopify_product_id='')
+ ORDER BY c.mfr_sku
""")
- n = DWKR_NEXT_START
+ n = canonical_next_dwkr()
+ start = n
for row in pool_b:
row["_pool"] = "B"
mfr = row["mfr_sku"]
@@ -175,8 +159,9 @@ def main():
cw = colorway_of(mfr, pat)
img = row.get("image_url") or ""
conf = "feed-direct" if img else "none"
- dw_sku = f"DWKR-{n}"
- n += 1
+ dw_sku = row.get("reserved_dw_sku") or f"DWKR-{n}"
+ if not row.get("reserved_dw_sku"):
+ n += 1
rec = rec_common(mfr, dw_sku, pat, town, cw.replace('-', ' ').title(),
product_type(row), img, conf, row)
(ready if img else held).append(rec)
@@ -209,13 +194,15 @@ def main():
by_pool = Counter(r["pool"] for r in ready)
summary = {
- "pool_a_reconfirmed_old_total": len(pool_a),
- "pool_b_new_gap_total": len(pool_b),
+ "approved_gap_total": int(pool_total),
+ "already_live_excluded_by_registry": int(pool_total) - len(pool_b),
+ "to_create_after_dedup": len(pool_b),
+ "reuse_reserved_dwkr": sum(1 for r in pool_b if r.get("reserved_dw_sku")),
+ "mint_new_dwkr": sum(1 for r in pool_b if not r.get("reserved_dw_sku")),
"ready_to_onboard": len(ready),
- " ready_pool_a": by_pool.get("A-reconfirmed-old", 0),
" ready_pool_b": by_pool.get("B-new-gap", 0),
"image_held_for_eyeball": len(held),
- "dw_sku_band_minted_for_pool_b": f"DWKR-{DWKR_NEXT_START}..DWKR-{n-1}" if pool_b else None,
+ "new_dw_sku_band": f"DWKR-{start}..DWKR-{n-1}" if n > start else None,
"vendor": VENDOR,
"price": f"${SAMPLE_PRICE} sample-only (quote-only, 'quotes' tag)",
"distinct_patterns": len(set(r["pattern"] for r in ready + held)),
diff --git a/create-results.json b/create-results.json
index 015b0d3..73838a3 100644
--- a/create-results.json
+++ b/create-results.json
@@ -1,5 +1,5 @@
{
- "created": 271,
+ "created": 0,
"skipped": 0,
"errors": [],
"results_sample": [],
diff --git a/create2.py b/create2.py
index 1c66c6c..5132778 100644
--- a/create2.py
+++ b/create2.py
@@ -1,28 +1,28 @@
#!/usr/bin/env python3
"""
Reid Witlin onboarding v2 — GATED live create (Steve runs this).
-Targets the CORRECTED pool (Pool A: 623 reconfirmed-old, DWKR- reused as-is;
-Pool B: 382 new-gap, DWKR- minted 191394-191775) built by build_batch2.py.
+Targets only the 382 new-gap products Steve approved, built by build_batch2.py.
Supersedes create.py (stale 1,071-row/DWDQ plan, quarantined).
Reads targets_ready_v2.csv and creates each as a DRAFT product on the LIVE DW
Shopify store via productSet (product + sample variant + primary image + tags +
-core spec metafields), then writes the assigned DWKR sku (continuing live band for Pool B; reused as-is for Pool A) + shopify id back to
+core spec metafields), then writes the assigned DWKR sku + Shopify id back to
rwltd_catalog. (DWDQ = dedicated Reid Witlin prefix, no DWRW/Rebel Walls overlap.) DRAFT status means nothing is customer-facing on create — the
existing dw-rotation-activator drips drafts live per Steve's cadence gate.
SAFETY:
* DRY_RUN=1 by default — prints what it WOULD create, writes nothing.
- * Run a 1-product smoke test first: DRY_RUN=0 LIMIT=1 python3 create.py
+ * Run a 1-product smoke test first: DRY_RUN=0 LIMIT=1 python3 create2.py
Verify the draft in Shopify admin, THEN run the full batch.
* status=DRAFT always; activation is a separate gated step (NOT this script).
- * Only reads targets_ready_v2.csv — the 156 image-held rows are NEVER created here.
+ * Only reads targets_ready_v2.csv.
"""
import os, csv, json, time, urllib.request, subprocess
HERE = os.path.dirname(os.path.abspath(__file__))
DRY_RUN = os.environ.get("DRY_RUN", "1") != "0"
LIMIT = int(os.environ.get("LIMIT", "0")) or None
+PRECHECK_ONLY = os.environ.get("PRECHECK_ONLY", "0") == "1"
def _tok():
p = os.path.expanduser("~/Projects/secrets-manager/.env")
@@ -57,6 +57,89 @@ mutation($input: ProductSetInput!) {
}
}"""
+LIVE_INDEX = """
+query($variantAfter: String, $productAfter: String) {
+ productVariants(first: 250, after: $variantAfter, query: "sku:DWKR-*") {
+ nodes { id sku product { id handle } }
+ pageInfo { hasNextPage endCursor }
+ }
+ products(first: 250, after: $productAfter, query: "vendor:'Architectural Fabrics'") {
+ nodes { id handle }
+ pageInfo { hasNextPage endCursor }
+ }
+}"""
+
+def local_product_id(mfr_sku):
+ safe = mfr_sku.replace("'", "''")
+ r = subprocess.run(["psql", "host=/tmp dbname=dw_unified", "-tA", "-c",
+ f"SELECT COALESCE(MAX(shopify_product_id),'') FROM rwltd_catalog WHERE mfr_sku='{safe}';"],
+ capture_output=True, text=True)
+ if r.returncode != 0:
+ raise RuntimeError(f"local precheck failed: {r.stderr.strip()}")
+ return r.stdout.strip()
+
+def local_sku_collision(row):
+ sku = row["sku"].replace("'", "''")
+ mfr = row["mfr_sku"].replace("'", "''")
+ r = subprocess.run(["psql", "host=/tmp dbname=dw_unified", "-tA", "-c", f"""
+ SELECT COUNT(*) FROM dw_sku_registry
+ WHERE (dw_sku='{sku}' OR (vendor_prefix='DWKR' AND mfr_sku='{mfr}'))
+ AND NOT (dw_sku='{sku}' AND mfr_sku='{mfr}' AND COALESCE(shopify_product_id,'')='');
+ """],
+ capture_output=True, text=True)
+ if r.returncode != 0:
+ raise RuntimeError(f"registry precheck failed: {r.stderr.strip()}")
+ return int(r.stdout.strip() or "0") > 0
+
+def live_index():
+ skus, handles = {}, {}
+ va = pa = None
+ while True:
+ d = gql(LIVE_INDEX, {"variantAfter": va, "productAfter": pa})
+ if d.get("errors"):
+ raise RuntimeError(f"Shopify bulk collision precheck failed: {d['errors']}")
+ data = d.get("data") or {}
+ variants = data.get("productVariants") or {}
+ products = data.get("products") or {}
+ for node in variants.get("nodes") or []:
+ if node.get("sku"):
+ skus[node["sku"]] = node
+ for node in products.get("nodes") or []:
+ if node.get("handle"):
+ handles[node["handle"]] = node
+ vpage = variants.get("pageInfo") or {}
+ ppage = products.get("pageInfo") or {}
+ vnext = bool(vpage.get("hasNextPage"))
+ pnext = bool(ppage.get("hasNextPage"))
+ if not vnext and not pnext:
+ break
+ va = vpage.get("endCursor") if vnext else None
+ pa = ppage.get("endCursor") if pnext else None
+ return skus, handles
+
+def persist_result(row, pid, handle):
+ vals = {k: str(v or "").replace("'", "''") for k, v in {
+ "pid": pid, "sku": row["sku"], "mfr": row["mfr_sku"], "handle": handle
+ }.items()}
+ sql = f"""
+ BEGIN;
+ UPDATE rwltd_catalog
+ SET shopify_product_id='{vals['pid']}', dw_sku=COALESCE(NULLIF(dw_sku,''), '{vals['sku']}')
+ WHERE mfr_sku='{vals['mfr']}' AND (shopify_product_id IS NULL OR shopify_product_id='');
+ UPDATE dw_sku_registry
+ SET shopify_product_id='{vals['pid']}', shopify_handle='{vals['handle']}', status='draft', updated_at=NOW()
+ WHERE dw_sku='{vals['sku']}' AND mfr_sku='{vals['mfr']}' AND COALESCE(shopify_product_id,'')='';
+ INSERT INTO dw_sku_registry
+ (dw_sku, vendor_prefix, vendor_name, mfr_sku, shopify_product_id, shopify_handle, status, created_at, updated_at)
+ SELECT '{vals['sku']}', 'DWKR', 'Architectural Fabrics', '{vals['mfr']}', '{vals['pid']}', '{vals['handle']}', 'draft', NOW(), NOW()
+ WHERE NOT EXISTS (SELECT 1 FROM dw_sku_registry WHERE dw_sku='{vals['sku']}' OR (vendor_prefix='DWKR' AND mfr_sku='{vals['mfr']}'));
+ COMMIT;
+ """
+ r = subprocess.run(["psql", "host=/tmp dbname=dw_unified", "-v", "ON_ERROR_STOP=1", "-c", sql],
+ capture_output=True, text=True)
+ if r.returncode != 0:
+ raise RuntimeError(f"created Shopify product {pid}, but canonical persistence failed: {r.stderr.strip()}")
+
def build_input(row):
# Verified against the LIVE metafield definitions (2026-07-30): custom.content,
# custom.finish, custom.country_of_origin are PRODUCT_REFERENCE — writing text
@@ -109,7 +192,8 @@ def main():
rows = rows[:LIMIT]
print(f"{'DRY-RUN' if DRY_RUN else 'LIVE'}: {len(rows)} DRAFT products "
f"({'no writes' if DRY_RUN else 'creating on LIVE store'})")
- created, errs, results = 0, [], []
+ existing_skus, existing_handles = ({}, {}) if DRY_RUN else live_index()
+ created, skipped, errs, results = 0, 0, [], []
for i, row in enumerate(rows):
if DRY_RUN:
if i < 3:
@@ -118,32 +202,39 @@ def main():
f"tags={len(row['tags'].split('|'))}")
created += 1
continue
+ prior_pid = local_product_id(row["mfr_sku"])
+ if prior_pid:
+ skipped += 1
+ continue
+ if local_sku_collision(row):
+ errs.append({"sku": row["sku"], "errors": [{"message": "canonical registry collision"}]})
+ continue
+ variant_sku = row["sku"] + "-Sample"
+ if variant_sku in existing_skus or row["handle"] in existing_handles:
+ errs.append({"sku": row["sku"], "errors": [{"message": "pre-existing live SKU or handle"}]})
+ continue
+ if PRECHECK_ONLY:
+ continue
d = gql(PRODUCT_SET, {"input": build_input(row)})
r = (d.get("data") or {}).get("productSet") or {}
ue = r.get("userErrors") or []
if ue:
errs.append({"sku": row["sku"], "errors": ue[:2]})
else:
- pid = (r.get("product") or {}).get("id")
+ product = r.get("product") or {}
+ pid = product.get("id")
+ handle = product.get("handle") or row["handle"]
results.append({"sku": row["sku"], "mfr_sku": row["mfr_sku"],
- "handle": row["handle"], "product_id": pid})
+ "handle": handle, "product_id": pid})
created += 1
- # IDEMPOTENCY: write id + sku back so build_batch's orphan guard
- # (WHERE shopify_product_id IS NULL) never re-creates this product.
- subprocess.run(["psql", "host=/tmp dbname=dw_unified", "-c",
- "UPDATE rwltd_catalog SET shopify_product_id=%s, dw_sku=COALESCE(NULLIF(dw_sku,''), %s) "
- "WHERE mfr_sku=%s;" % (
- "'" + (pid or "").replace("'", "") + "'",
- "'" + row["sku"].replace("'", "") + "'",
- "'" + row["mfr_sku"].replace("'", "''") + "'")],
- capture_output=True, text=True)
+ persist_result(row, pid, handle)
if i % 25 == 0:
print(f" ...{i}/{len(rows)} created={created} errs={len(errs)}")
time.sleep(0.3)
- out = {"created": created, "errors": errs[:20], "results_sample": results[:5],
+ out = {"created": created, "skipped": skipped, "errors": errs[:20], "results_sample": results[:5],
"total_results": len(results)}
json.dump(out, open(os.path.join(HERE, "create-results.json"), "w"), indent=2)
- print(f"\nDONE. created={created} userErrors={len(errs)} "
+ print(f"\nDONE. created={created} skipped={skipped} userErrors={len(errs)} "
f"({'DRY-RUN — nothing written' if DRY_RUN else 'DRAFTS on live store'})")
if errs:
print("sample errors:", json.dumps(errs[:3]))
← c95e6e2 auto-data-snapshot: 2026-09-02T16:03:42 (4 data files) — cre
·
back to Reid Witlin Onboarding
·
Record and gate Reid Witlin draft race recovery 39ea379 →