← back to Enrich Local Hybrid
Add reversible Phase-3 image-less phantom-backlog reconcile (TK-phase3-phantom, staged/gated)
2b5fd9273b041bfa43a555762e31edb4b8fb9a98 · 2026-08-31 12:08:58 -0700 · Steve
Files touched
A reconcile-staging/reconcile-image-less.shA reconcile-staging/restore-image-less.sh
Diff
commit 2b5fd9273b041bfa43a555762e31edb4b8fb9a98
Author: Steve <steve@designerwallcoverings.com>
Date: Mon Aug 31 12:08:58 2026 -0700
Add reversible Phase-3 image-less phantom-backlog reconcile (TK-phase3-phantom, staged/gated)
---
reconcile-staging/reconcile-image-less.sh | 110 ++++++++++++++++++++++++++++++
reconcile-staging/restore-image-less.sh | 20 ++++++
2 files changed, 130 insertions(+)
diff --git a/reconcile-staging/reconcile-image-less.sh b/reconcile-staging/reconcile-image-less.sh
new file mode 100755
index 0000000..47fd47b
--- /dev/null
+++ b/reconcile-staging/reconcile-image-less.sh
@@ -0,0 +1,110 @@
+#!/bin/bash
+# reconcile-image-less.sh — clears the PHANTOM Phase-3 enrichment backlog.
+#
+# ROOT CAUSE (2026-08-31, TK-phase3-phantom): AI enrichment requires an image
+# (enrich-ai-tags.js:593 → `image_url IS NOT NULL`). Catalog rows with NO
+# image_url can never be enriched, so their enrichment_tracking.phase3_ai_at
+# stays NULL forever. The phase3-monitor counts phase3_ai_at IS NULL as
+# "pending" (no image check), so ~4,236 un-enrichable image-less rows are
+# phantom-counted every night → "NO drain overnight" false alarm.
+#
+# FIX: stamp those image-less pending rows with last_error='unenrichable-no-image'.
+# The monitor's EXISTING filter (`last_error NOT LIKE 'unenrichable-%'`) then
+# drops them, and the backlog reads its true (~17) count. This does NOT set
+# phase3_ai_at (that would falsely claim enrichment ran) and does NOT block
+# re-enrichment: enrich-ai-tags.js's selector ignores last_error, so if an image
+# later arrives the row is re-picked and phase3_ai_at gets stamped normally.
+#
+# SELF-HEALING: a second pass CLEARS the stamp on any row that has since gained
+# an image (Codex reversibility rider), so re-running is idempotent + correct.
+#
+# REVERSIBLE: --apply snapshots every affected (id, catalog_table) to a
+# timestamped JSON BEFORE writing. Undo = restore-image-less.sh <snapshot>.
+#
+# Usage:
+# ./reconcile-image-less.sh # DRY-RUN (default) — counts only, no writes
+# ./reconcile-image-less.sh --apply # execute (snapshots first)
+set -euo pipefail
+
+APPLY=0
+[ "${1:-}" = "--apply" ] && APPLY=1
+PSQL="sudo -u postgres psql -d dw_unified -Atc"
+SNAPDIR="$(dirname "$0")/reconcile-snapshots"
+TS="$(date -u +%Y-%m-%dT%H-%M-%SZ)"
+SNAP="$SNAPDIR/reconcile-$TS.json"
+mkdir -p "$SNAPDIR"
+
+echo "=== reconcile-image-less (mode: $([ $APPLY = 1 ] && echo APPLY || echo DRY-RUN)) ==="
+
+# The predicate for a phantom row, evaluated per catalog_table:
+# phase3_ai_at IS NULL (monitor counts it pending)
+# AND last_error IS NULL (untried — don't touch errored rows)
+# AND the mfr_sku has NO catalog row with an image (truly un-enrichable)
+# AND NOT any catalog row for that mfr_sku HAS an image (multi-row sku guard)
+build_where() { # $1 = catalog_table (aliased et)
+ cat <<SQL
+et.catalog_table = '$1'
+ AND et.phase3_ai_at IS NULL
+ AND et.last_error IS NULL
+ AND NOT EXISTS (SELECT 1 FROM "$1" c
+ WHERE c.mfr_sku = et.mfr_sku
+ AND c.image_url IS NOT NULL AND c.image_url <> '')
+SQL
+}
+
+# Tables in scope = every catalog_table that has ≥1 untried phase3-null row,
+# and whose catalog actually has an image_url column (skip if not).
+TABLES=$($PSQL "SELECT DISTINCT catalog_table FROM enrichment_tracking
+ WHERE phase3_ai_at IS NULL AND last_error IS NULL AND catalog_table IS NOT NULL
+ ORDER BY 1")
+
+TOTAL=0
+CLEAR_TOTAL=0
+echo "[" > "$SNAP.tmp"
+FIRST=1
+for T in $TABLES; do
+ # guard: table exists and has image_url column
+ HASIMG=$($PSQL "SELECT count(*) FROM information_schema.columns
+ WHERE table_name='$T' AND column_name='image_url'")
+ [ "$HASIMG" = "1" ] || { echo " skip $T (no image_url column)"; continue; }
+
+ WH=$(build_where "$T")
+ N=$($PSQL "SELECT count(*) FROM enrichment_tracking et WHERE $WH")
+ # self-heal: rows previously stamped that now HAVE an image → clear
+ C=$($PSQL "SELECT count(*) FROM enrichment_tracking et
+ WHERE et.catalog_table='$T' AND et.last_error='unenrichable-no-image'
+ AND EXISTS (SELECT 1 FROM \"$T\" c WHERE c.mfr_sku=et.mfr_sku
+ AND c.image_url IS NOT NULL AND c.image_url<>'')")
+ [ "$N" = "0" ] && [ "$C" = "0" ] && continue
+ printf " %-34s stamp=%-6s clear=%s\n" "$T" "$N" "$C"
+ TOTAL=$((TOTAL+N)); CLEAR_TOTAL=$((CLEAR_TOTAL+C))
+
+ if [ $APPLY = 1 ]; then
+ # snapshot the ids we are about to stamp (old last_error is always NULL here)
+ IDS=$($PSQL "SELECT id FROM enrichment_tracking et WHERE $WH")
+ for id in $IDS; do
+ [ $FIRST = 1 ] && FIRST=0 || echo "," >> "$SNAP.tmp"
+ printf '{"id":%s,"catalog_table":"%s","old_last_error":null}' "$id" "$T" >> "$SNAP.tmp"
+ done
+ # stamp
+ $PSQL "UPDATE enrichment_tracking et SET last_error='unenrichable-no-image', updated_at=now() WHERE $WH" >/dev/null
+ # self-heal clear
+ $PSQL "UPDATE enrichment_tracking et SET last_error=NULL, updated_at=now()
+ WHERE et.catalog_table='$T' AND et.last_error='unenrichable-no-image'
+ AND EXISTS (SELECT 1 FROM \"$T\" c WHERE c.mfr_sku=et.mfr_sku
+ AND c.image_url IS NOT NULL AND c.image_url<>'')" >/dev/null
+ fi
+done
+echo "]" >> "$SNAP.tmp"
+
+echo "----------------------------------------------"
+echo "TOTAL image-less phantom rows to stamp : $TOTAL"
+echo "TOTAL stale stamps to clear (self-heal): $CLEAR_TOTAL"
+if [ $APPLY = 1 ]; then
+ mv "$SNAP.tmp" "$SNAP"
+ echo "APPLIED. Snapshot (undo map): $SNAP"
+ echo "Undo: ./restore-image-less.sh $SNAP"
+else
+ rm -f "$SNAP.tmp"
+ echo "DRY-RUN — no writes. Re-run with --apply to execute."
+fi
diff --git a/reconcile-staging/restore-image-less.sh b/reconcile-staging/restore-image-less.sh
new file mode 100755
index 0000000..385f618
--- /dev/null
+++ b/reconcile-staging/restore-image-less.sh
@@ -0,0 +1,20 @@
+#!/bin/bash
+# restore-image-less.sh — reverses one reconcile-image-less.sh --apply run.
+# Reads the snapshot JSON and restores last_error to its recorded old value
+# (always NULL for this reconcile) for exactly the ids it stamped — and ONLY
+# if they still carry the 'unenrichable-no-image' stamp (so we never clobber a
+# row that has since been legitimately re-processed).
+#
+# Usage: ./restore-image-less.sh <snapshot.json>
+set -euo pipefail
+SNAP="${1:?usage: restore-image-less.sh <snapshot.json>}"
+[ -f "$SNAP" ] || { echo "snapshot not found: $SNAP"; exit 1; }
+PSQL="sudo -u postgres psql -d dw_unified -Atc"
+
+IDS=$(grep -oE '"id":[0-9]+' "$SNAP" | grep -oE '[0-9]+' | paste -sd, -)
+[ -z "$IDS" ] && { echo "snapshot has no ids — nothing to restore"; exit 0; }
+N=$($PSQL "SELECT count(*) FROM enrichment_tracking WHERE id IN ($IDS) AND last_error='unenrichable-no-image'")
+echo "restoring $N rows (of $(grep -c '"id":' "$SNAP") snapshotted) to last_error=NULL ..."
+$PSQL "UPDATE enrichment_tracking SET last_error=NULL, updated_at=now()
+ WHERE id IN ($IDS) AND last_error='unenrichable-no-image'" >/dev/null
+echo "done. restored $N rows."
← 11302b0 enricher: dead-image drain guard + tag-on-skip (TK-10168)
·
back to Enrich Local Hybrid
·
reconcile-image-less: guard mfr_sku col + scope to monitor's 0efb0b0 →