← back to Kravet Sheet Sync 2026 04 20
kravet IMG-FIX: add ksi->all_images backfill SQL + scoped re-crawl helper
a5e168abb9234a0a99dd5073c581b4aa4bce5ad4 · 2026-06-10 09:52:06 -0700 · Steve Abrams
Files touched
A backfill_all_images_from_sku_images.sqlA recrawl_worklist.sh
Diff
commit a5e168abb9234a0a99dd5073c581b4aa4bce5ad4
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed Jun 10 09:52:06 2026 -0700
kravet IMG-FIX: add ksi->all_images backfill SQL + scoped re-crawl helper
---
backfill_all_images_from_sku_images.sql | 61 +++++++++++++++++++++++++++++++++
recrawl_worklist.sh | 35 +++++++++++++++++++
2 files changed, 96 insertions(+)
diff --git a/backfill_all_images_from_sku_images.sql b/backfill_all_images_from_sku_images.sql
new file mode 100644
index 0000000..c21c06d
--- /dev/null
+++ b/backfill_all_images_from_sku_images.sql
@@ -0,0 +1,61 @@
+-- backfill_all_images_from_sku_images.sql
+-- Fill kravet_catalog.all_images from already-captured kravet_sku_images rows.
+-- NO crawl required for any SKU already present in kravet_sku_images.
+--
+-- Match key: kravet_sku_images.mfr_sku_norm strips the trailing ".0" that
+-- kravet_catalog.mfr_sku carries, so we normalize the catalog side the same way:
+-- regexp_replace(upper(btrim(mfr_sku)), '\.0$', '')
+--
+-- SAFETY: only touches rows whose all_images is currently empty/null — never
+-- overwrites an already-populated all_images. Idempotent. all_images is written
+-- as a JSON array string to match the existing column convention
+-- (e.g. ["https://cdn.brandfolder.io/..."]).
+--
+-- Built 2026-06-10 (yolo IMG-FIX kravet). Reversible: a pre-image is captured
+-- below into kravet_catalog_allimages_backfill_bak_20260610 before the UPDATE.
+-- Run inside a transaction; review the rowcount before COMMIT.
+
+BEGIN;
+
+-- 1) pre-image of every row we are about to touch (for easy revert)
+CREATE TABLE IF NOT EXISTS kravet_catalog_allimages_backfill_bak_20260610 AS
+SELECT id, mfr_sku, all_images
+FROM kravet_catalog
+WHERE 1=0;
+
+INSERT INTO kravet_catalog_allimages_backfill_bak_20260610 (id, mfr_sku, all_images)
+SELECT k.id, k.mfr_sku, k.all_images
+FROM kravet_catalog k
+WHERE (k.all_images IS NULL OR btrim(k.all_images) = '' OR k.all_images IN ('[]','null','{}'))
+ AND EXISTS (
+ SELECT 1 FROM kravet_sku_images i
+ WHERE i.mfr_sku_norm = regexp_replace(upper(btrim(k.mfr_sku)), '\.0$', '')
+ );
+
+-- 2) the backfill
+WITH agg AS (
+ SELECT i.mfr_sku_norm AS k,
+ to_json(array_agg(DISTINCT COALESCE(i.url_canonical, i.url)))::text AS imgs
+ FROM kravet_sku_images i
+ WHERE COALESCE(i.url_canonical, i.url) IS NOT NULL
+ AND btrim(COALESCE(i.url_canonical, i.url)) <> ''
+ GROUP BY i.mfr_sku_norm
+)
+UPDATE kravet_catalog k
+SET all_images = a.imgs,
+ updated_at = now()
+FROM agg a
+WHERE a.k = regexp_replace(upper(btrim(k.mfr_sku)), '\.0$', '')
+ AND (k.all_images IS NULL OR btrim(k.all_images) = '' OR k.all_images IN ('[]','null','{}'));
+
+-- 3) report
+SELECT 'backed_up' lbl, count(*) FROM kravet_catalog_allimages_backfill_bak_20260610;
+
+-- Review the UPDATE rowcount printed by psql, then:
+-- COMMIT; -- to keep
+-- ROLLBACK; -- to discard
+-- Revert later if needed:
+-- UPDATE kravet_catalog k SET all_images = b.all_images
+-- FROM kravet_catalog_allimages_backfill_bak_20260610 b WHERE b.id = k.id;
+
+COMMIT;
diff --git a/recrawl_worklist.sh b/recrawl_worklist.sh
new file mode 100755
index 0000000..f4d276f
--- /dev/null
+++ b/recrawl_worklist.sh
@@ -0,0 +1,35 @@
+#!/usr/bin/env bash
+# recrawl_worklist.sh — scoped all_images re-crawl for kravet from the yolo worklist.
+#
+# Reads the dedup-tagged worklist built 2026-06-10 and runs kravet_image_catalog.py
+# only for the SKUs you select. Produces a JSONL of captured image rows; LOADING
+# that JSONL into dw_unified.kravet_sku_images and backfilling kravet_catalog.all_images
+# are SEPARATE, GATED prod writes (see pending-approval/kravet-recrawl.md).
+#
+# Usage:
+# ./recrawl_worklist.sh netnet # crawl ONLY the 3 genuinely net-net-new SKUs (default)
+# ./recrawl_worklist.sh all # crawl ALL 5576 net-new-missing SKUs (heavy)
+# ./recrawl_worklist.sh <SKU...> # crawl explicit SKUs
+#
+# This script ONLY runs the crawl (network read + local JSONL write). It does NOT
+# write to any database.
+
+set -euo pipefail
+HERE="$(cd "$(dirname "$0")" && pwd)"
+WORKLIST="$HOME/.claude/yolo-queue/output/kravet-recrawl-worklist.tsv"
+MODE="${1:-netnet}"; shift || true
+
+case "$MODE" in
+ netnet) SKUS=$(awk -F'\t' 'NR>1 && $5=="NET_NET_NEW"{print $2}' "$WORKLIST") ;;
+ all) SKUS=$(awk -F'\t' 'NR>1{print $2}' "$WORKLIST") ;;
+ *) SKUS="$MODE $*" ;;
+esac
+
+N=$(printf '%s\n' $SKUS | grep -c . || true)
+echo "[recrawl] mode=$MODE skus=$N"
+[ "$N" -eq 0 ] && { echo "nothing to crawl"; exit 0; }
+
+OUT="$HERE/kravet_recrawl_$(date +%Y%m%d_%H%M%S).jsonl"
+python3 "$HERE/kravet_image_catalog.py" --test-skus $SKUS --output "$OUT" --threads 8
+echo "[recrawl] wrote $OUT"
+echo "[recrawl] NEXT (gated): load JSONL -> kravet_sku_images, then run backfill_all_images_from_sku_images.sql"
← b687afd initial scaffold (gitify-all 2026-05-06)
·
back to Kravet Sheet Sync 2026 04 20
·
kravet: local crawl + staged additive import CSV for 3 net-n 5a9322e →