← back to Dw Color Image Audit
v2 background-suppressed full-catalog re-extract + staging loader + analysis SQL
8c159815194b8af4bfc965fd71955a57f45b2c1a · 2026-06-20 10:32:05 -0700 · SteveStudio2
Files touched
A full/analyze.sqlA full/run_all.shA scripts/load_staging.pyA scripts/reextract2.py
Diff
commit 8c159815194b8af4bfc965fd71955a57f45b2c1a
Author: SteveStudio2 <steve@designerwallcoverings.com>
Date: Sat Jun 20 10:32:05 2026 -0700
v2 background-suppressed full-catalog re-extract + staging loader + analysis SQL
---
full/analyze.sql | 70 ++++++++++++++++++++++++++
full/run_all.sh | 10 ++++
scripts/load_staging.py | 37 ++++++++++++++
scripts/reextract2.py | 129 ++++++++++++++++++++++++++++++++++++++++++++++++
4 files changed, 246 insertions(+)
diff --git a/full/analyze.sql b/full/analyze.sql
new file mode 100644
index 0000000..61bb51a
--- /dev/null
+++ b/full/analyze.sql
@@ -0,0 +1,70 @@
+\echo '================ product_colors_v2 STAGING SUMMARY ================'
+\echo '--- total rows loaded ---'
+select count(*) as v2_rows from product_colors_v2;
+
+\echo '--- verdict distribution (all statuses) ---'
+select verdict, count(*), round(100.0*count(*)/sum(count(*)) over (),1) as pct
+from product_colors_v2 group by verdict order by 2 desc;
+
+\echo '--- verdict distribution (ACTIVE only: ACTIVE+active) ---'
+select verdict, count(*)
+from product_colors_v2 where lower(status)='active' group by verdict order by 2 desc;
+
+\echo '================ V2 EXTRACTION QUALITY vs CACHED ================'
+\echo '--- cached_hex near-white capture rate (the documented defect) ---'
+select
+ count(*) total,
+ count(*) filter (where upper(cached_hex) in ('#F2F2F2','#FFFFFF','#FAFAFA','#F5F5F5','#FFFFFE','#FEFEFE')) as cached_exact_white,
+ count(*) filter (where cached_hex ~* '^#F[0-9A-F]F[0-9A-F]F[0-9A-F]$') as cached_nearwhite_pattern
+from product_colors_v2;
+
+\echo '--- v2 background-suppression: how often v2 differs from naive v1 ---'
+select
+ count(*) filter (where real_hex_v1 <> real_hex) as bg_suppressed_changed,
+ count(*) as total,
+ round(100.0*count(*) filter (where real_hex_v1 <> real_hex)/count(*),1) as pct_changed
+from product_colors_v2 where verdict <> 'ERROR';
+
+\echo '--- v2 real_hex still landing on white/neutral (extraction ceiling) ---'
+select image_family, count(*) from product_colors_v2 where verdict<>'ERROR'
+group by image_family order by 2 desc;
+
+\echo '================ DEFECT-SET RE-CONFIRMATION (prior 625 vs v2) ================'
+\echo '--- match rate of prior defects into v2 by image_url ---'
+select pd.tier,
+ count(*) as prior,
+ count(v2.shopify_product_id) as matched_in_v2,
+ count(*) filter (where v2.shopify_product_id is null) as unmatched
+from prior_defects pd
+left join product_colors_v2 v2 on v2.image_url = pd.image_url
+group by pd.tier order by 1;
+
+\echo '--- how prior verdicts shift under v2 (matched rows) ---'
+select pd.tier, pd.prior_verdict, v2.verdict as v2_verdict, count(*)
+from prior_defects pd
+join product_colors_v2 v2 on v2.image_url = pd.image_url
+group by pd.tier, pd.prior_verdict, v2.verdict
+order by pd.tier, pd.prior_verdict, 4 desc;
+
+\echo '--- net: prior defects CONFIRMED (still HIGH/REVIEW) vs CLEARED (now OK) under v2 ---'
+select pd.tier,
+ count(*) filter (where v2.verdict in ('HIGH','REVIEW')) as still_defect,
+ count(*) filter (where v2.verdict='OK') as cleared_ok,
+ count(*) filter (where v2.verdict='UNKNOWN') as now_unknown,
+ count(*) filter (where v2.verdict='ERROR') as v2_error
+from prior_defects pd
+join product_colors_v2 v2 on v2.image_url = pd.image_url
+group by pd.tier order by 1;
+
+\echo '--- NEW HIGH-severity defects v2 surfaces beyond the prior set (ACTIVE only) ---'
+select count(*) as new_high_active
+from product_colors_v2 v2
+left join prior_defects pd on pd.image_url = v2.image_url
+where v2.verdict='HIGH' and lower(v2.status)='active' and pd.image_url is null;
+
+\echo '--- top vendors by v2 HIGH (ACTIVE only) ---'
+select vendor, count(*) filter (where verdict='HIGH') as high,
+ count(*) filter (where verdict='REVIEW') as review, count(*) as scanned
+from product_colors_v2 where lower(status)='active'
+group by vendor having count(*) filter (where verdict='HIGH')>0
+order by high desc limit 20;
diff --git a/full/run_all.sh b/full/run_all.sh
new file mode 100755
index 0000000..53c23ee
--- /dev/null
+++ b/full/run_all.sh
@@ -0,0 +1,10 @@
+#!/bin/bash
+cd /Users/stevestudio2/Projects/dw-color-image-audit
+export PY=/opt/homebrew/bin/python3
+ls full/chunks/ch_* | xargs -P 32 -I{} bash -c '
+ out="{}.out.jsonl"
+ [ -s "$out" ] && exit 0
+ $PY scripts/reextract2.py scripts/name_family.json < "{}" > "$out" 2>/dev/null
+'
+cat full/chunks/ch_*.out.jsonl > full/reextracted_full.jsonl
+echo "DONE_FULL $(wc -l < full/reextracted_full.jsonl)" > full/.done
diff --git a/scripts/load_staging.py b/scripts/load_staging.py
new file mode 100644
index 0000000..6c4462a
--- /dev/null
+++ b/scripts/load_staging.py
@@ -0,0 +1,37 @@
+#!/usr/bin/env python3
+"""Load reextracted JSONL into local-only dw_color_staging.product_colors_v2 via COPY.
+Local, reversible (table is in an isolated non-replicated DB)."""
+import sys, json, subprocess, io
+
+src = sys.argv[1] if len(sys.argv) > 1 else "full/reextracted_full.jsonl"
+cols = ["shopify_product_id","vendor","status","title","declared","declared_family",
+ "cached_hex","real_hex_v1","real_hex","image_family","verdict","reason","image_url"]
+
+def esc(v):
+ if v is None: return r"\N"
+ s = str(v)
+ return s.replace("\\","\\\\").replace("\t"," ").replace("\n"," ").replace("\r"," ")
+
+buf = io.StringIO()
+n = 0
+seen = set()
+for line in open(src):
+ line = line.strip()
+ if not line: continue
+ r = json.loads(line)
+ spid = r.get("shopify_product_id")
+ if not spid or spid in seen: continue # PK dedupe (last-write would conflict)
+ seen.add(spid)
+ row = [r.get("shopify_product_id"), r.get("vendor"), r.get("status"), r.get("title"),
+ r.get("declared"), r.get("declaredFamily"), r.get("cached_hex"),
+ r.get("real_hex_v1"), r.get("real_hex"), r.get("imageFamily"),
+ r.get("verdict"), r.get("reason"), r.get("image_url")]
+ buf.write("\t".join(esc(x) for x in row) + "\n")
+ n += 1
+
+p = subprocess.run(
+ ["psql","-d","dw_color_staging","-c",
+ f"TRUNCATE product_colors_v2; COPY product_colors_v2 ({','.join(cols)}) FROM STDIN"],
+ input=buf.getvalue(), text=True, capture_output=True)
+sys.stderr.write(p.stdout + p.stderr)
+print(f"prepared {n} rows")
diff --git a/scripts/reextract2.py b/scripts/reextract2.py
new file mode 100644
index 0000000..f4542b7
--- /dev/null
+++ b/scripts/reextract2.py
@@ -0,0 +1,129 @@
+#!/usr/bin/env python3
+"""Full-catalog TRUE dominant-color re-extraction (v2), local + free (PIL only).
+Reads TSV on stdin: vendor, handle(=shopify_product_id), dwsku(=status), mfrsku, title, cached_hex, image_url
+Writes JSON-lines on stdout.
+
+Two extractions per (single) downloaded image:
+ real_hex_v1 : original center-crop + saturation-weighted dominant (scripts/reextract.py method)
+ real_hex : v2 = same, but near-white/near-black PAPER/BACKGROUND pixels are dropped
+ before choosing the swatch color (falls back to v1 only when the image is
+ essentially all-neutral). Directly attacks the '#F2F2F2 white-bg capture' defect.
+Classification (declared color name vs real image family) uses the v2 hex.
+"""
+import sys, json, io, urllib.request, colorsys
+from collections import Counter
+try:
+ from PIL import Image
+except Exception:
+ sys.stderr.write("PIL missing\n"); sys.exit(1)
+
+CHROM = ["red","orange","yellow","chartreuse","green","teal","cyan","blue","indigo","violet","magenta","pink"]
+WHEEL = CHROM
+
+def fam(r,g,b):
+ h,l,s = colorsys.rgb_to_hls(r/255,g/255,b/255)
+ spread = (max(r,g,b)-min(r,g,b))/255
+ if l>0.86 and spread<0.22: return "white", s, l
+ if l<0.16 and spread<0.22: return "black", s, l
+ if s<0.15: return "gray", s, l
+ H = h*360
+ if s<0.45 and 18<=H<=65: return ("beige" if l>0.55 else "brown"), s, l
+ bands=[(345,361,"red"),(0,15,"red"),(15,40,"orange"),(40,67,"yellow"),(67,95,"chartreuse"),
+ (95,150,"green"),(150,175,"teal"),(175,195,"cyan"),(195,240,"blue"),(240,265,"indigo"),
+ (265,290,"violet"),(290,320,"magenta"),(320,345,"pink")]
+ for a,b2,f in bands:
+ if a<=H<b2: return f, s, l
+ return "red", s, l
+
+NAME_FAMILY = json.loads(open(sys.argv[1]).read()) if len(sys.argv)>1 else {}
+
+def name_family(name):
+ fam_=None
+ for t in ''.join(c.lower() if c.isalpha() or c==' ' else ' ' for c in name).split():
+ if t in NAME_FAMILY: fam_=NAME_FAMILY[t]
+ return fam_
+
+def is_chrom(f): return f in CHROM
+def hue_dist(a,b):
+ ia,ib=WHEEL.index(a),WHEEL.index(b); d=abs(ia-ib); return min(d,12-d)
+
+def classify(df, imf, chroma, l):
+ if not df: return "UNKNOWN","name-unmapped"
+ if df==imf: return "OK","same-family"
+ dC, iC = is_chrom(df), is_chrom(imf)
+ if dC and iC:
+ d=hue_dist(df,imf)
+ if d>=2: return "HIGH", f"non-adjacent hue (dist {d})"
+ return "REVIEW", f"adjacent hue (dist {d})"
+ if dC != iC:
+ if dC and imf=="white": return "REVIEW","chromatic name vs pale image (light-ground)"
+ if dC and imf in ("gray","black"): return "HIGH","chromatic name, dark-neutral image"
+ if dC and imf in ("beige","brown"):
+ if df in ("blue","indigo","violet","cyan","teal","green"): return "HIGH","cool color vs warm-neutral image"
+ return "REVIEW","warm color vs warm-neutral image"
+ if not dC and iC and chroma>0.45: return "HIGH","neutral name, strongly chromatic image"
+ return "REVIEW","chromatic/neutral boundary"
+ NG={"white":"light","gray":"mid","black":"dark","beige":"warm","brown":"warm"}
+ if NG.get(df)==NG.get(imf): return "OK","same neutral group"
+ if {df,imf}=={"white","black"}: return "HIGH","opposite-value neutrals"
+ return "REVIEW",f"neutral shift {df}->{imf}"
+
+def _weighted_dominant(buckets):
+ """Pick the saturation-boosted most-frequent bucket among the top 6."""
+ top = buckets.most_common(6)
+ best = top[0][0]; best_score = -1
+ for (col, cnt) in top:
+ s = (max(col)-min(col))/255.0
+ score = cnt*(0.4+s)
+ if score > best_score: best_score = score; best = col
+ return best
+
+def _is_paper(col):
+ """Near-white paper/background or near-black: not a real swatch color."""
+ r,g,b = col
+ spread = (max(col)-min(col))/255.0
+ mx = max(col)/255.0; mn = min(col)/255.0
+ if mx > 0.90 and spread < 0.10: return True # paper white
+ if mn < 0.08 and spread < 0.10: return True # ink black
+ return False
+
+def extract(url):
+ req = urllib.request.Request(url, headers={"User-Agent":"Mozilla/5.0"})
+ data = urllib.request.urlopen(req, timeout=25).read()
+ im = Image.open(io.BytesIO(data)).convert("RGB")
+ w,h = im.size; cw,ch = int(w*0.3), int(h*0.3)
+ im2 = im.crop((cw,ch,w-cw,h-ch)).resize((60,60))
+ px = list(im2.getdata())
+ q = Counter(tuple(c//20*20 for c in p) for p in px)
+ # v1: original method (background-naive)
+ dom_v1 = _weighted_dominant(q)
+ # v2: drop paper/ink background buckets, then pick swatch dominant
+ q2 = Counter({c:n for c,n in q.items() if not _is_paper(c)})
+ total = sum(q.values()); kept = sum(q2.values())
+ if kept >= max(1, int(total*0.12)): # enough non-background content to trust
+ dom_v2 = _weighted_dominant(q2)
+ else:
+ dom_v2 = dom_v1 # essentially all white/black -> keep v1
+ return dom_v1, dom_v2
+
+for line in sys.stdin:
+ line = line.rstrip("\n")
+ if not line.strip(): continue
+ parts = line.split("\t")
+ if len(parts) < 7: continue
+ vendor,spid,status,mfrsku,title,cached,url = parts[:7]
+ declared = title.split("|")[0].strip()
+ df = name_family(declared)
+ rec = {"shopify_product_id":spid, "vendor":vendor, "status":status, "title":title,
+ "declared":declared, "cached_hex":cached, "image_url":url, "declaredFamily":df}
+ try:
+ v1, v2 = extract(url)
+ imf, s, l = fam(*v2)
+ rec["real_hex_v1"] = "#%02X%02X%02X" % v1
+ rec["real_hex"] = "#%02X%02X%02X" % v2
+ rec["imageFamily"] = imf
+ verdict, reason = classify(df, imf, s, l)
+ rec["verdict"] = verdict; rec["reason"] = reason
+ except Exception as e:
+ rec["verdict"] = "ERROR"; rec["reason"] = f"{type(e).__name__}:{str(e)[:50]}"
+ sys.stdout.write(json.dumps(rec)+"\n")
← c8bbfd3 DW color-image integrity audit: classifier, parallel re-extr
·
back to Dw Color Image Audit
·
vision-gate non-PR Tier-1 color-image set: 105->29 survivors 07d2c04 →