harden catalog gap audit and bounded backfill

dc851ebb6807f07d31bd943a287c0d3ec9d6db81 · 2026-09-01 17:30:53 -0700 · Steve Abrams
Files touched

A data/tk11050-item3/audit-distinct-sku-gap.sh
A data/tk11050-item3/backfill-one.sh
Diff

commit dc851ebb6807f07d31bd943a287c0d3ec9d6db81
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Tue Sep 1 17:30:53 2026 -0700

    harden catalog gap audit and bounded backfill
---
 data/tk11050-item3/audit-distinct-sku-gap.sh | 47 +++++++++++++++++++++
 data/tk11050-item3/backfill-one.sh           | 62 ++++++++++++++++++++++++++++
 2 files changed, 109 insertions(+)

diff --git a/data/tk11050-item3/audit-distinct-sku-gap.sh b/data/tk11050-item3/audit-distinct-sku-gap.sh
new file mode 100755
index 0000000..25dcc31
--- /dev/null
+++ b/data/tk11050-item3/audit-distinct-sku-gap.sh
@@ -0,0 +1,47 @@
+#!/usr/bin/env bash
+# TK-11050 ITEM 3 — Kamatera->Mac2 *_catalog distinct-SKU gap audit (READ-ONLY).
+# Run on Mac2 (Mac2 = local /tmp socket; Kamatera = ssh). Steve runs this outside auto-mode.
+# HARD LESSON baked in: compare by DISTINCT mfr_sku SET, never by row count.
+# Proves whether each "Kamatera-fuller" gap is REAL missing SKUs or a koroseal-class dupe artifact.
+set -uo pipefail
+KAM="root@45.61.58.125"
+KPSQL="psql -h 127.0.0.1 -U dw_admin -d dw_unified -tA"
+WORK=/tmp/tk11050-item3; mkdir -p "$WORK"
+# Discover ALL *_catalog tables that exist on BOTH machines AND have an mfr_sku column (covers the 7 "tails" too).
+psql -h /tmp -d dw_unified -tA -c "SELECT table_name FROM information_schema.columns WHERE table_schema='public' AND column_name='mfr_sku' AND table_name LIKE '%_catalog' ORDER BY table_name" > "$WORK/mac2_tables.txt"
+if ! ssh -n "$KAM" "$KPSQL -v ON_ERROR_STOP=1 -c \"SELECT table_name FROM information_schema.columns WHERE table_schema='public' AND column_name='mfr_sku' AND table_name LIKE '%_catalog' ORDER BY table_name\"" > "$WORK/kam_tables.txt"; then
+  echo "FATAL: Kamatera catalog-table enumeration failed; refusing to report an empty/zero diff." >&2
+  exit 2
+fi
+[ -s "$WORK/kam_tables.txt" ] || { echo "FATAL: Kamatera returned no catalog tables; refusing false-success." >&2; exit 2; }
+comm -12 "$WORK/mac2_tables.txt" "$WORK/kam_tables.txt" > "$WORK/both_all.txt"
+# EXCLUSIONS (per TK-11050): competitor-intel, staff-only, excluded vendor, item-2, item-4, known-exception
+SKIP='^(connie_our_catalog|connie_competitor_catalog|schumacher_catalog|fentucci_catalog|fentucci_naturals_catalog|koroseal_catalog|anna_french_catalog|rebel_walls_catalog|rebelwalls_catalog|spoonflower_catalog)$'
+grep -vE "$SKIP" "$WORK/both_all.txt" > "$WORK/both_tables.txt"
+printf "%-32s %10s %10s %10s %10s %10s\n" table mac2_rows mac2_dsku kam_dsku KAMonly_REAL MAC2only
+printf "%-32s %10s %10s %10s %10s %10s\n" "-----" "-----" "-----" "-----" "-----" "-----"
+while read -r t; do
+  [ -z "$t" ] && continue
+  psql -h /tmp -d dw_unified -tA -c "SELECT distinct mfr_sku FROM $t WHERE coalesce(mfr_sku,'')<>''" 2>/dev/null | sort -u > "$WORK/mac2.$t"
+  # -n is mandatory inside this while-loop: without it ssh consumes the loop's stdin and
+  # silently audits only the first table while exiting 0 (the original false-success bug).
+  if ! ssh -n "$KAM" "$KPSQL -v ON_ERROR_STOP=1 -c \"SELECT distinct mfr_sku FROM $t WHERE coalesce(mfr_sku,'')<>''\"" 2>"$WORK/kam.$t.err" | sort -u > "$WORK/kam.$t"; then
+    echo "FATAL: Kamatera read failed for $t; refusing a partial audit." >&2
+    cat "$WORK/kam.$t.err" >&2
+    exit 2
+  fi
+  m2rows=$(psql -h /tmp -d dw_unified -tA -c "SELECT count(*) FROM $t" 2>/dev/null)
+  m2d=$(wc -l < "$WORK/mac2.$t" | tr -d ' ')
+  kd=$(wc -l < "$WORK/kam.$t" | tr -d ' ')
+  comm -13 "$WORK/mac2.$t" "$WORK/kam.$t" > "$WORK/kamonly.$t"   # in Kamatera, NOT in Mac2 = REAL backfill set
+  comm -23 "$WORK/mac2.$t" "$WORK/kam.$t" > "$WORK/mac2only.$t"
+  ko=$(wc -l < "$WORK/kamonly.$t" | tr -d ' ')
+  mo=$(wc -l < "$WORK/mac2only.$t" | tr -d ' ')
+  # Only print rows where there's any divergence
+  if [ "$ko" -gt 0 ] || [ "$mo" -gt 0 ]; then
+    printf "%-32s %10s %10s %10s %10s %10s\n" "$t" "$m2rows" "$m2d" "$kd" "$ko" "$mo"
+  fi
+done < "$WORK/both_tables.txt"
+echo
+echo "REAL backfill sets (Kamatera-only distinct mfr_sku) are in $WORK/kamonly.<table>"
+echo "Next: run backfill-one.sh <table> for any table with KAMonly_REAL > 0 (additive INSERT, Kamatera->Mac2)."
diff --git a/data/tk11050-item3/backfill-one.sh b/data/tk11050-item3/backfill-one.sh
new file mode 100755
index 0000000..5ef57a0
--- /dev/null
+++ b/data/tk11050-item3/backfill-one.sh
@@ -0,0 +1,62 @@
+#!/usr/bin/env bash
+# TK-11050 ITEM 3 — additive Kamatera->Mac2 backfill for ONE *_catalog table.
+# Inserts ONLY the genuinely-missing rows (distinct mfr_sku present on Kamatera, absent on Mac2).
+# Reversible: snapshots the Mac2 table first. Run AFTER audit-distinct-sku-gap.sh. Steve-gated.
+set -euo pipefail
+T="${1:?usage: backfill-one.sh <table>}"
+case "$T" in (*[!a-z0-9_]*|''|*_catalog_prebackfill_bak) echo "Invalid catalog table: $T" >&2; exit 2;; esac
+case "$T" in (*_catalog) :;; (*) echo "Refusing non-catalog table: $T" >&2; exit 2;; esac
+KAM="root@45.61.58.125"
+KPSQL="psql -h 127.0.0.1 -U dw_admin -d dw_unified -tA"
+WORK=/tmp/tk11050-item3
+KON="$WORK/kamonly.$T"
+[ -s "$KON" ] || { echo "No Kamatera-only SKUs for $T (nothing to backfill)."; exit 0; }
+N=$(wc -l < "$KON" | tr -d ' ')
+[ "$N" -le 500 ] || { echo "Refusing $N-row target; bounded auto-exec cap is 500." >&2; exit 2; }
+[ -f "$WORK/mac2only.$T" ] || { echo "Missing audit artifact for $T; rerun audit first." >&2; exit 2; }
+[ ! -s "$WORK/mac2only.$T" ] || { echo "Refusing two-way divergence for $T; reconcile manually." >&2; exit 2; }
+# Shared column set (exclude id so Mac2 assigns fresh serials; avoids PK/serial collision)
+psql -h /tmp -d dw_unified -tA -c "SELECT column_name FROM information_schema.columns WHERE table_schema='public' AND table_name='$T' AND column_name<>'id' ORDER BY column_name" | sort -u > "$WORK/m2cols.$T"
+ssh -n "$KAM" "$KPSQL -v ON_ERROR_STOP=1 -c \"SELECT column_name FROM information_schema.columns WHERE table_schema='public' AND table_name='$T' AND column_name<>'id' ORDER BY column_name\"" | sort -u > "$WORK/kcols.$T"
+comm -12 "$WORK/m2cols.$T" "$WORK/kcols.$T" | paste -sd, - > "$WORK/cols.$T"
+COLS=$(cat "$WORK/cols.$T")
+echo "$T: backfilling $N missing SKUs on shared cols: $COLS"
+# 1) Load kamonly SKU list onto Kamatera into a temp, dump exactly one row per mfr_sku.
+# Use a session-scoped approach via a single psql invocation piping the file in:
+ssh "$KAM" "psql -q -h 127.0.0.1 -U dw_admin -d dw_unified -v ON_ERROR_STOP=1 <<'REMOTE'
+CREATE TEMP TABLE _kon(mfr_sku text);
+\copy _kon FROM STDIN
+$(cat "$KON")
+\.
+\copy (SELECT $COLS FROM (SELECT DISTINCT ON (mfr_sku) * FROM $T t WHERE t.mfr_sku IN (SELECT mfr_sku FROM _kon) ORDER BY mfr_sku, id) t) TO STDOUT WITH CSV
+REMOTE" > "$WORK/rows.$T.csv"
+ROWS=$(wc -l < "$WORK/rows.$T.csv" | tr -d ' ')
+echo "$T: dumped CSV payload ($ROWS physical lines; quoted fields may contain newlines)."
+# 2/3) Validate CSV RECORD cardinality in PostgreSQL, snapshot, and insert in one transaction.
+# wc -l is intentionally not a record count because quoted catalog descriptions can contain newlines.
+BAK="${T}_prebackfill_bak_$(date +%Y%m%d%H%M%S)"
+psql -h /tmp -d dw_unified -v ON_ERROR_STOP=1 <<LOCAL
+BEGIN;
+LOCK TABLE $T IN SHARE ROW EXCLUSIVE MODE;
+CREATE TEMP TABLE _tk11050_load (LIKE $T INCLUDING DEFAULTS);
+\copy _tk11050_load ($COLS) FROM '$WORK/rows.$T.csv' WITH CSV
+DO \$\$
+DECLARE n_rows bigint; n_skus bigint;
+BEGIN
+  SELECT count(*), count(DISTINCT mfr_sku) INTO n_rows, n_skus FROM _tk11050_load;
+  IF n_rows <> $N OR n_skus <> $N THEN
+    RAISE EXCEPTION 'source cardinality mismatch: expected $N rows/SKUs, got % rows / % SKUs', n_rows, n_skus;
+  END IF;
+  IF EXISTS (SELECT 1 FROM _tk11050_load l JOIN $T t USING (mfr_sku)) THEN
+    RAISE EXCEPTION 'target drift: at least one audited SKU now exists on Mac2';
+  END IF;
+END \$\$;
+CREATE TABLE $BAK AS SELECT * FROM $T;
+SELECT setval(pg_get_serial_sequence('$T','id'), COALESCE((SELECT max(id) FROM $T), 1), true)
+WHERE pg_get_serial_sequence('$T','id') IS NOT NULL;
+INSERT INTO $T ($COLS) SELECT $COLS FROM _tk11050_load;
+COMMIT;
+LOCAL
+AFTER=$(psql -h /tmp -d dw_unified -tA -c "SELECT count(*), count(distinct mfr_sku) FROM $T")
+echo "$T: Mac2 now -> $AFTER (rows, distinct_mfr_sku)"
+echo "Undo: psql -h /tmp -d dw_unified -c 'BEGIN; TRUNCATE $T; INSERT INTO $T SELECT * FROM $BAK; COMMIT;'"
← 47f4119 TK-11050: 33 RW mural drafts, parity per-sqm metafield (83.3 · back to Dw Dead Image Recovery · exclude dead catalog lines from reconciliation c1caf71 →