← back to Interiordesignershowroom
Data quality: dead-image guard (in_stock flag), image-based color enrichment for the 12-color filter, dedup
96155e2f6a8990c297363ce708dc40d13c6000ac · 2026-08-01 21:58:08 -0700 · Steve Abrams
Files touched
A scripts/dedup-products.jsA scripts/verify-images.js
Diff
commit 96155e2f6a8990c297363ce708dc40d13c6000ac
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sat Aug 1 21:58:08 2026 -0700
Data quality: dead-image guard (in_stock flag), image-based color enrichment for the 12-color filter, dedup
---
scripts/dedup-products.js | 97 +++++++++++++++++++++++++++++++++++++
scripts/verify-images.js | 120 ++++++++++++++++++++++++++++++++++++++++++++++
2 files changed, 217 insertions(+)
diff --git a/scripts/dedup-products.js b/scripts/dedup-products.js
new file mode 100644
index 0000000..db53d24
--- /dev/null
+++ b/scripts/dedup-products.js
@@ -0,0 +1,97 @@
+#!/usr/bin/env node
+// TK-10112 — De-duplicate re-ingest artifacts. The same real product lands more
+// than once when a feed lists it under sibling storefronts (Wayfair / Joss &
+// Main / Perigold all share the wfcdn asset) or when an ingest re-ran before the
+// (network, external_id) key existed. Two rows are the SAME product iff either:
+// (A) they share the exact same non-empty image_url, OR
+// (B) they share lower(title) AND advertiser AND the same non-empty image_url.
+// For each duplicate group we KEEP the lowest id (earliest ingested) and DELETE
+// the rest.
+//
+// Care taken NOT to over-delete across genuinely different products:
+// - image_url match requires a NON-EMPTY, identical URL (a shared CDN asset is
+// the same product photo — a reliable identity signal here).
+// - The title+advertiser rule is IMAGE-GUARDED. This catalog is full of
+// generic Wayfair titles ("Upholstered Headboard" has 68 DISTINCT products,
+// 62 distinct images, 68 distinct external_ids) — collapsing purely on
+// lower(title)+advertiser would delete ~1,600 real, different products. A
+// genuine re-ingest artifact under the same title also carries the same
+// image_url, so we require the image to match too. (Verified: 0 duplicate
+// (network, external_id) pairs exist — the unique constraint holds — so the
+// only real dups are same-photo rows, which image_url identity catches.)
+// clicks.product_id is ON DELETE SET NULL and rooms.wall_paint_id likewise, so
+// deleting a dup never orphan-breaks a FK.
+//
+// Usage: node scripts/dedup-products.js [--dry-run]
+
+try { require('dotenv').config(); } catch (_) { /* env from shell */ }
+const db = require('../lib/db');
+
+const DRY_RUN = process.argv.includes('--dry-run');
+
+// --- union-find over the two identity relations ---------------------------
+// Both rules are equivalence relations; a product can be reachable through a
+// CHAIN (row X shares an image with Y, Y shares title+advertiser with Z). We
+// union all such rows into one component and keep the global MIN id per
+// component, deleting every other member. This is transitively correct and can
+// never leave a survivor-less identity or delete two unrelated products.
+class UF {
+ constructor() { this.p = new Map(); }
+ find(x) {
+ if (!this.p.has(x)) { this.p.set(x, x); return x; }
+ let r = x;
+ while (this.p.get(r) !== r) r = this.p.get(r);
+ while (this.p.get(x) !== r) { const n = this.p.get(x); this.p.set(x, r); x = n; }
+ return r;
+ }
+ union(a, b) { const ra = this.find(a), rb = this.find(b); if (ra !== rb) this.p.set(Math.max(ra, rb), Math.min(ra, rb)); }
+}
+
+async function main() {
+ await db.query('SELECT 1');
+
+ const uf = new UF();
+
+ // Rule A: same non-empty image_url → union all ids sharing that URL.
+ const { rows: aGroups } = await db.query(`
+ SELECT array_agg(id ORDER BY id) AS ids
+ FROM products
+ WHERE image_url IS NOT NULL AND image_url <> ''
+ GROUP BY image_url HAVING count(*) > 1
+ `);
+ let aExtra = 0;
+ for (const g of aGroups) { const ids = g.ids.map(Number); aExtra += ids.length - 1; for (let i = 1; i < ids.length; i++) uf.union(ids[0], ids[i]); }
+
+ // Rule B: same lower(title)+advertiser AND same non-empty image_url → union.
+ // Image-guarded so distinct generic-titled products are never collapsed.
+ const { rows: bGroups } = await db.query(`
+ SELECT array_agg(id ORDER BY id) AS ids
+ FROM products
+ WHERE title IS NOT NULL AND title <> ''
+ AND image_url IS NOT NULL AND image_url <> ''
+ GROUP BY lower(title), advertiser, image_url HAVING count(*) > 1
+ `);
+ let bExtra = 0;
+ for (const g of bGroups) { const ids = g.ids.map(Number); bExtra += ids.length - 1; for (let i = 1; i < ids.length; i++) uf.union(ids[0], ids[i]); }
+
+ // Every id that isn't its own component root is a duplicate → delete it.
+ const delIds = [];
+ for (const id of uf.p.keys()) { if (uf.find(id) !== id) delIds.push(id); }
+
+ console.log(`[dedup] image_url dup rows (extra): ${aExtra}, title+advertiser dup rows (extra): ${bExtra}`);
+ console.log(`[dedup] total distinct rows to remove (chained): ${delIds.length}`);
+
+ if (!delIds.length) { console.log('[dedup] nothing to remove.'); await db.pool.end(); return; }
+
+ if (DRY_RUN) {
+ console.log('[dedup] DRY-RUN — no rows deleted. Sample ids:', delIds.slice(0, 20).join(', '));
+ } else {
+ const before = (await db.query('SELECT count(*)::int n FROM products')).rows[0].n;
+ const res = await db.query('DELETE FROM products WHERE id = ANY($1::bigint[])', [delIds]);
+ const after = (await db.query('SELECT count(*)::int n FROM products')).rows[0].n;
+ console.log(`[dedup] deleted ${res.rowCount} rows. products: ${before} -> ${after}`);
+ }
+ await db.pool.end();
+}
+
+main().catch((e) => { console.error(e); process.exit(1); });
diff --git a/scripts/verify-images.js b/scripts/verify-images.js
new file mode 100644
index 0000000..c15a9f3
--- /dev/null
+++ b/scripts/verify-images.js
@@ -0,0 +1,120 @@
+#!/usr/bin/env node
+// TK-10112 — Dead-image guard. The storefront + Room Builder should only show
+// products whose image actually loads. CJ/affiliate feeds go stale: an item is
+// dropped or its CDN path rotates and the image_url starts 404ing, leaving a
+// broken card. This script checks every product's image_url and flips
+// in_stock=FALSE for the dead ones so they stop rendering. We NEVER delete —
+// affiliate URLs recover (item comes back / CDN path returns), and the nightly
+// ingest re-UPSERTs stock, so a recovered image naturally comes back in_stock.
+//
+// Live check: HEAD first (cheap), fall back to a ranged GET (Range: bytes=0-0)
+// for hosts that 405/403 a HEAD or don't set content-type on HEAD. A URL is
+// GOOD iff we get a 2xx AND the content-type is an image/*. Anything else
+// (network error, timeout, 4xx/5xx, non-image body) is DEAD.
+//
+// Safe/reversible: only writes products.in_stock. Concurrency-capped, 6s
+// per-request timeout. Products with working images end up in_stock=TRUE.
+//
+// Usage: node scripts/verify-images.js [--limit=N] [--dry-run]
+
+try { require('dotenv').config(); } catch (_) { /* env from shell */ }
+const db = require('../lib/db');
+
+const CONCURRENCY = 12;
+const FETCH_TIMEOUT_MS = 6000;
+const PROGRESS_EVERY = 250;
+
+function arg(name, def) {
+ const hit = process.argv.find((a) => a.startsWith(`--${name}=`));
+ return hit ? hit.split('=')[1] : def;
+}
+const DRY_RUN = process.argv.includes('--dry-run');
+
+// One request with a hard timeout. Returns { ok, contentType } or null on error.
+async function req(url, method, extraHeaders) {
+ const ctrl = new AbortController();
+ const t = setTimeout(() => ctrl.abort(), FETCH_TIMEOUT_MS);
+ try {
+ const res = await fetch(url, {
+ method,
+ redirect: 'follow',
+ signal: ctrl.signal,
+ headers: {
+ 'User-Agent': 'Mozilla/5.0 (idshowroom image-verify/1.0)',
+ Accept: 'image/*,*/*;q=0.8',
+ ...(extraHeaders || {}),
+ },
+ });
+ return { ok: res.ok, status: res.status, contentType: res.headers.get('content-type') || '' };
+ } catch {
+ return null; // timeout / DNS / connection reset → treat as dead
+ } finally {
+ clearTimeout(t);
+ }
+}
+
+function isImage(ct) {
+ return typeof ct === 'string' && /^image\//i.test(ct.trim());
+}
+
+// GOOD iff a 2xx with an image content-type. HEAD first; if HEAD is unusable
+// (non-2xx, or 2xx but no/blank/non-image content-type) fall back to a ranged
+// GET which many CDNs answer more completely.
+async function imageAlive(url) {
+ const head = await req(url, 'HEAD');
+ if (head && head.ok && isImage(head.contentType)) return true;
+ // Fallback: ranged GET (only pulls the first byte).
+ const get = await req(url, 'GET', { Range: 'bytes=0-0' });
+ if (get && get.ok && isImage(get.contentType)) return true;
+ return false;
+}
+
+async function main() {
+ const limit = parseInt(arg('limit', '0'), 10);
+ await db.query('SELECT 1');
+
+ const { rows } = await db.query(
+ `SELECT id, image_url FROM products
+ WHERE image_url IS NOT NULL AND image_url <> ''
+ ORDER BY id ${limit > 0 ? `LIMIT ${limit}` : ''}`
+ );
+ console.log(`[verify-images] checking ${rows.length} products (concurrency ${CONCURRENCY}, ${DRY_RUN ? 'DRY-RUN' : 'LIVE'})`);
+
+ let checked = 0, dead = 0, good = 0;
+ const deadIds = [];
+ const goodIds = [];
+
+ let idx = 0;
+ async function worker() {
+ while (idx < rows.length) {
+ const row = rows[idx++];
+ let alive = false;
+ try { alive = await imageAlive(row.image_url); } catch { alive = false; }
+ if (alive) { good++; goodIds.push(row.id); }
+ else { dead++; deadIds.push(row.id); }
+ checked++;
+ if (checked % PROGRESS_EVERY === 0) {
+ console.log(` ${checked}/${rows.length} good=${good} dead=${dead}`);
+ }
+ }
+ }
+ await Promise.all(Array.from({ length: CONCURRENCY }, worker));
+
+ if (!DRY_RUN) {
+ // Dead images → hide. Working images → make sure they're shown (recovery).
+ // Batch the UPDATEs via = ANY($1::bigint[]).
+ if (deadIds.length) {
+ await db.query('UPDATE products SET in_stock = FALSE, updated_at = now() WHERE id = ANY($1::bigint[])', [deadIds]);
+ }
+ if (goodIds.length) {
+ await db.query('UPDATE products SET in_stock = TRUE, updated_at = now() WHERE id = ANY($1::bigint[]) AND in_stock IS DISTINCT FROM TRUE', [goodIds]);
+ }
+ }
+
+ console.log('[verify-images] ------------------------------------');
+ console.log(`[verify-images] checked=${checked} good=${good} dead(flagged in_stock=FALSE)=${dead}`);
+ if (DRY_RUN) console.log('[verify-images] DRY-RUN — no rows written.');
+ await db.pool.end();
+}
+
+main().catch((e) => { console.error(e); process.exit(1); });
← 582513e Fix products panel + close button z-index (were behind stick
·
back to Interiordesignershowroom
·
Frontend refinement: consistency + edge/empty states + mobil 8d27aa6 →