← back to Designerwallcoverings
Daily DWPW→GRS auto-migrate job: sheet-rebuild batch + runner + launchd plist
733d369e1d9f0f111884f13ffd18a8f161520f65 · 2026-09-08 14:20:57 -0700 · Steve Abrams
build_batch_from_sheet.py rebuilds scripts/batch_daily.json from the live
Google Sheet (real CSV parse, header-name column map, all 207 GRS rows) so new
images + brand-new rows get picked up. dwpw-grs-daily.sh snapshots the ledger's
published/archived GRS sets, rebuilds the batch, runs dwpw-grs-migrate.py --apply,
set-diffs the ledger to find what NEWLY landed (immune to the idempotent
re-publish), and emails Steve a digest via George only when something landed.
com.steve.dwpw-grs-daily.plist runs it daily at 09:00 (RunAtLoad/KeepAlive false).
DRY-RUN tested only; not installed.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01C6KGNY395sd4PKXbEzXbgV
Files touched
M .gitignoreA scripts/build_batch_from_sheet.pyA scripts/dwpw-grs-daily.sh
Diff
commit 733d369e1d9f0f111884f13ffd18a8f161520f65
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Sep 8 14:20:57 2026 -0700
Daily DWPW→GRS auto-migrate job: sheet-rebuild batch + runner + launchd plist
build_batch_from_sheet.py rebuilds scripts/batch_daily.json from the live
Google Sheet (real CSV parse, header-name column map, all 207 GRS rows) so new
images + brand-new rows get picked up. dwpw-grs-daily.sh snapshots the ledger's
published/archived GRS sets, rebuilds the batch, runs dwpw-grs-migrate.py --apply,
set-diffs the ledger to find what NEWLY landed (immune to the idempotent
re-publish), and emails Steve a digest via George only when something landed.
com.steve.dwpw-grs-daily.plist runs it daily at 09:00 (RunAtLoad/KeepAlive false).
DRY-RUN tested only; not installed.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01C6KGNY395sd4PKXbEzXbgV
---
.gitignore | 4 +
scripts/build_batch_from_sheet.py | 150 ++++++++++++++++++++++++++
scripts/dwpw-grs-daily.sh | 214 ++++++++++++++++++++++++++++++++++++++
3 files changed, 368 insertions(+)
diff --git a/.gitignore b/.gitignore
index 17ff1ea..2bad31b 100644
--- a/.gitignore
+++ b/.gitignore
@@ -17,3 +17,7 @@ data/google-feed/*.tsv
today-viewer/data/*.tsv
today-viewer/data/*.csv
scripts/rigo-onboard/images/
+
+# dwpw-grs daily auto-migrate runtime artifacts (regenerated every run)
+scripts/batch_daily.json
+scripts/dwpw-grs-daily.launchd.*.log
diff --git a/scripts/build_batch_from_sheet.py b/scripts/build_batch_from_sheet.py
new file mode 100755
index 0000000..168ff3f
--- /dev/null
+++ b/scripts/build_batch_from_sheet.py
@@ -0,0 +1,150 @@
+#!/usr/bin/env python3
+"""
+build_batch_from_sheet.py — rebuild the DWPW->GRS migration batch from Steve's
+LIVE Google Sheet so the daily auto-migrate job picks up newly-added images AND
+brand-new rows.
+
+Downloads the sheet as CSV (public export URL, follows the 307 redirect), parses
+it with a REAL csv parser (the Description column contains commas), maps the
+messy/duplicate-header columns by HEADER NAME, and emits scripts/batch_daily.json
+in the exact shape dwpw-grs-migrate.py consumes:
+
+ { mfr, grs, pattern, title, desc, color, dw_price, cost_yd, image, width, length }
+
+Every row that carries a "Shopify SKU" starting GRS- is included (the migrate
+script is idempotent + image-gated, so already-published rows just skip).
+
+SAFETY: this ONLY reads the sheet and writes a local JSON file. Zero Shopify /
+Postgres / network writes. $0 (local + one public GET).
+
+Usage:
+ python3 build_batch_from_sheet.py # download live sheet -> batch_daily.json
+ python3 build_batch_from_sheet.py --csv FILE # parse a local CSV instead (offline test)
+ python3 build_batch_from_sheet.py --out PATH # write somewhere else
+ python3 build_batch_from_sheet.py --url URL # override the sheet export URL
+"""
+import csv, os, sys, json, argparse, subprocess, tempfile
+
+SHEET_ID = "1trKNm-ymqlbs96XJfndDUz19mzH8A11ktaw2lfQ2VQo"
+SHEET_URL = f"https://docs.google.com/spreadsheets/d/{SHEET_ID}/export?format=csv"
+HERE = os.path.dirname(os.path.abspath(__file__))
+OUT_DEFAULT = os.path.join(HERE, "batch_daily.json")
+
+# target field -> the sheet header name (lowercased, stripped) it lives under.
+# Mapped BY NAME (first exact match) because the sheet has many blank/duplicate
+# header cells; positional mapping would be brittle.
+COLMAP = {
+ "mfr": "mfr sku",
+ "pattern": "pattern",
+ "title": "alt tags",
+ "desc": "description",
+ "color": "color",
+ "dw_price": "dw price",
+ "cost_yd": "cost/yd",
+ "image": "image 1",
+ "width": "width",
+ "length": "length",
+ "grs": "shopify sku",
+}
+
+
+def download_csv(url, dest):
+ """Follow the 307 redirect with curl -L (proven path). Falls back to urllib."""
+ try:
+ subprocess.run(
+ ["curl", "-sL", "--fail", "--max-time", "60", "-o", dest, url],
+ check=True)
+ if os.path.getsize(dest) > 0:
+ return
+ except Exception as e:
+ sys.stderr.write(f"[build-batch] curl failed ({e}); trying urllib\n")
+ import urllib.request
+ req = urllib.request.Request(url, headers={"User-Agent": "Mozilla/5.0"})
+ with urllib.request.urlopen(req, timeout=60) as r, open(dest, "wb") as f:
+ f.write(r.read())
+
+
+def norm_num(v):
+ """' 36.45 ' / '$36.45' / '1,234.5' -> '36.45'. Blank stays ''."""
+ v = (v or "").strip().replace("$", "").replace(",", "").strip()
+ return v
+
+
+def build(csv_path):
+ with open(csv_path, newline="", encoding="utf-8") as fh:
+ rows = list(csv.reader(fh))
+ if not rows:
+ raise RuntimeError("empty CSV")
+ header = [h.strip().lower() for h in rows[0]]
+
+ def col_idx(name):
+ for i, h in enumerate(header):
+ if h == name:
+ return i
+ raise RuntimeError(f"sheet header missing column {name!r} "
+ f"(have: {[h for h in header if h]})")
+
+ idx = {field: col_idx(hdr) for field, hdr in COLMAP.items()}
+
+ def cell(r, field):
+ i = idx[field]
+ return (r[i] if i < len(r) else "").strip()
+
+ out, skipped_no_grs = [], 0
+ for r in rows[1:]:
+ grs = cell(r, "grs")
+ if not grs or not grs.upper().startswith("GRS-"):
+ skipped_no_grs += 1
+ continue
+ out.append({
+ "mfr": cell(r, "mfr"),
+ "grs": grs,
+ "pattern": cell(r, "pattern"),
+ "title": cell(r, "title"),
+ "desc": cell(r, "desc"),
+ "color": cell(r, "color"),
+ "dw_price": norm_num(cell(r, "dw_price")),
+ "cost_yd": norm_num(cell(r, "cost_yd")),
+ "image": cell(r, "image"),
+ "width": cell(r, "width"),
+ "length": cell(r, "length"),
+ })
+ return out, skipped_no_grs, len(rows) - 1
+
+
+def main():
+ ap = argparse.ArgumentParser()
+ ap.add_argument("--out", default=OUT_DEFAULT)
+ ap.add_argument("--url", default=SHEET_URL)
+ ap.add_argument("--csv", default=None, help="parse a local CSV instead of downloading")
+ args = ap.parse_args()
+
+ if args.csv:
+ csv_path = args.csv
+ cleanup = None
+ else:
+ fd, csv_path = tempfile.mkstemp(prefix="dwpw-grs-sheet-", suffix=".csv")
+ os.close(fd)
+ cleanup = csv_path
+ download_csv(args.url, csv_path)
+
+ try:
+ rows, skipped, total = build(csv_path)
+ finally:
+ if cleanup and os.path.exists(cleanup):
+ os.remove(cleanup)
+
+ with open(args.out, "w", encoding="utf-8") as f:
+ json.dump(rows, f, indent=1, ensure_ascii=False)
+
+ # quick integrity counts
+ missing_price = sum(1 for r in rows if not r["dw_price"] or not r["cost_yd"])
+ missing_img = sum(1 for r in rows if not r["image"])
+ print(f"[build-batch] sheet rows={total} -> GRS rows={len(rows)} "
+ f"(skipped {skipped} non-GRS)")
+ print(f"[build-batch] missing price/cost: {missing_price} missing image: {missing_img}")
+ print(f"[build-batch] wrote {args.out}")
+
+
+if __name__ == "__main__":
+ main()
diff --git a/scripts/dwpw-grs-daily.sh b/scripts/dwpw-grs-daily.sh
new file mode 100755
index 0000000..b343146
--- /dev/null
+++ b/scripts/dwpw-grs-daily.sh
@@ -0,0 +1,214 @@
+#!/usr/bin/env bash
+# dwpw-grs-daily.sh — once-a-day DWPW->GRS grasscloth auto-migrate.
+#
+# FLOW (safe to run repeatedly):
+# 1. Snapshot the set of already-published GRS + already-archived DWPW twins
+# from the executed-reversible ledger (action publish_grs_active /
+# archive_dwpw_twin, ticket dwpw-grs-migrate) — the BEFORE snapshot.
+# 2. Rebuild scripts/batch_daily.json from the LIVE Google Sheet
+# (build_batch_from_sheet.py) so new images + brand-new rows are picked up.
+# 3. Run dwpw-grs-migrate.py --apply --batch scripts/batch_daily.json.
+# The migrate script is image-gated (no-image rows stay DRAFT), interlocked
+# (publishes+verifies GRS before archiving the DWPW twin, never darks), and
+# idempotent (already-published rows skip).
+# 4. Re-read the ledger (AFTER) and set-diff vs BEFORE to find what NEWLY
+# published / newly archived THIS run. The set-diff is deliberate: the
+# migrate re-appends publish_grs_active for already-live GRS on every run,
+# so a naive "new ledger lines" diff would false-report every live GRS
+# daily — but a GRS already in BEFORE is never counted as new.
+# 5. If anything landed, email Steve a digest via the LOCAL George bridge
+# (:9850 /api/send, internal recipient, Basic auth). Nothing landed ->
+# send NOTHING (low-noise by design).
+# 6. Append a one-line run record to scripts/dwpw-grs-daily.log.
+#
+# DRY-RUN: set DWPW_GRS_DRY=1 (or pass --dry) to run the migrate in DRY-RUN
+# (zero Shopify/PG writes) AND skip the George email (prints what it would send).
+# This is how the job is TESTED without going live.
+set -uo pipefail
+
+HERE="$(cd "$(dirname "$0")" && pwd)"
+BATCH="$HERE/batch_daily.json"
+LOG="$HERE/dwpw-grs-daily.log"
+LEDGER="$HOME/.claude/yolo-queue/executed-reversible/ledger.jsonl"
+LASTRUN="$HERE/dwpw-grs-migrate.lastrun.json"
+PY="$(command -v python3 || echo /usr/bin/python3)"
+
+DRY=0
+[[ "${DWPW_GRS_DRY:-0}" == "1" || "${1:-}" == "--dry" ]] && DRY=1
+
+TS="$(date -u +%Y-%m-%dT%H:%M:%SZ)"
+TMP="$(mktemp -d "${TMPDIR:-/tmp}/dwpw-grs-daily.XXXXXX")"
+trap 'rm -rf "$TMP"' EXIT
+BEFORE="$TMP/before.json"
+BODY="$TMP/body.txt"
+
+echo "== dwpw-grs-daily $TS (dry=$DRY) =="
+
+# --- snapshot helper: dumps {published:[grs...], archived:[{grs,handle}...]} ---
+snap() {
+ "$PY" - "$LEDGER" <<'PY'
+import json, sys
+led = sys.argv[1]
+pub, arch = set(), {}
+try:
+ for line in open(led, encoding="utf-8"):
+ line = line.strip()
+ if not line:
+ continue
+ try:
+ e = json.loads(line)
+ except Exception:
+ continue
+ if e.get("ticket") != "dwpw-grs-migrate":
+ continue
+ act = e.get("action")
+ grs = e.get("grs")
+ if act == "publish_grs_active" and grs:
+ pub.add(grs)
+ elif act == "archive_dwpw_twin" and grs:
+ arch[grs] = e.get("dwpw_handle", "")
+except FileNotFoundError:
+ pass
+json.dump({"published": sorted(pub), "archived": arch}, sys.stdout)
+PY
+}
+
+# 1) BEFORE snapshot
+snap > "$BEFORE"
+
+# 2) rebuild batch from the live sheet
+echo "-- rebuilding batch from sheet"
+if ! "$PY" "$HERE/build_batch_from_sheet.py" --out "$BATCH"; then
+ echo "$TS ERROR build_batch_from_sheet.py failed — aborting run" >> "$LOG"
+ echo "!! build_batch_from_sheet.py failed; aborting"
+ exit 1
+fi
+ROWS="$("$PY" -c "import json;print(len(json.load(open('$BATCH'))))" 2>/dev/null || echo '?')"
+
+# 3) run the migrate.
+# DWPW_GRS_LIMIT (optional) caps the rows the migrate processes — used for fast
+# testing; unset in production so the full batch runs.
+LIMIT_ARGS=()
+[[ -n "${DWPW_GRS_LIMIT:-}" ]] && LIMIT_ARGS=(--limit "$DWPW_GRS_LIMIT")
+if [[ "$DRY" == "1" ]]; then
+ echo "-- migrate DRY-RUN (zero writes)"
+ "$PY" "$HERE/dwpw-grs-migrate.py" --batch "$BATCH" "${LIMIT_ARGS[@]+"${LIMIT_ARGS[@]}"}" >/dev/null 2>&1 || true
+else
+ echo "-- migrate APPLY (LIVE writes)"
+ "$PY" "$HERE/dwpw-grs-migrate.py" --apply --batch "$BATCH" "${LIMIT_ARGS[@]+"${LIMIT_ARGS[@]}"}" || true
+fi
+
+# 4) AFTER snapshot + diff -> body + counts
+DIFF="$("$PY" - "$BEFORE" "$LASTRUN" <<'PY'
+import json, sys
+before = json.load(open(sys.argv[1]))
+lastrun_path = sys.argv[2]
+
+# re-read the ledger AFTER (same logic as snap)
+import os
+led = os.path.expanduser("~/.claude/yolo-queue/executed-reversible/ledger.jsonl")
+pub, arch = set(), {}
+try:
+ for line in open(led, encoding="utf-8"):
+ line = line.strip()
+ if not line:
+ continue
+ try:
+ e = json.loads(line)
+ except Exception:
+ continue
+ if e.get("ticket") != "dwpw-grs-migrate":
+ continue
+ if e.get("action") == "publish_grs_active" and e.get("grs"):
+ pub.add(e["grs"])
+ elif e.get("action") == "archive_dwpw_twin" and e.get("grs"):
+ arch[e["grs"]] = e.get("dwpw_handle", "")
+except FileNotFoundError:
+ pass
+
+new_pub = sorted(pub - set(before["published"]))
+prev_arch = set(before["archived"].keys())
+new_arch = {g: h for g, h in arch.items() if g not in prev_arch}
+
+# enrich GRS with title/handle from the migrate lastrun.json when available
+meta = {}
+try:
+ lr = json.load(open(lastrun_path))
+ for r in lr.get("results", []):
+ if r.get("grs"):
+ meta[r["grs"]] = {"title": r.get("title", ""), "handle": r.get("handle", "")}
+except Exception:
+ pass
+
+lines = []
+if new_pub:
+ lines.append(f"GRS now LIVE ({len(new_pub)}):")
+ for g in new_pub:
+ m = meta.get(g, {})
+ t = m.get("title") or ""
+ h = m.get("handle") or ""
+ suffix = f" {t}" if t else ""
+ suffix += f" (/products/{h})" if h else ""
+ lines.append(f" • {g}{suffix}")
+if new_arch:
+ lines.append("")
+ lines.append(f"DWPW twins RETIRED ({len(new_arch)}):")
+ for g, h in sorted(new_arch.items()):
+ lines.append(f" • {h or '(handle?)'} <- replaced by {g}")
+
+body = "\n".join(lines) if lines else ""
+# machine line for the shell: <n_pub>\t<n_arch>\t<body-path-written?>
+import tempfile
+open(sys.argv[1] + ".body", "w", encoding="utf-8").write(body)
+print(f"{len(new_pub)}\t{len(new_arch)}")
+PY
+)"
+cp "$BEFORE.body" "$BODY" 2>/dev/null || : > "$BODY"
+N_PUB="$(printf '%s' "$DIFF" | cut -f1)"
+N_ARCH="$(printf '%s' "$DIFF" | cut -f2)"
+N_PUB="${N_PUB:-0}"; N_ARCH="${N_ARCH:-0}"
+
+echo "-- landed this run: published=$N_PUB archived=$N_ARCH"
+
+# 5) email Steve ONLY if something landed
+SENT="no"
+if [[ "$N_PUB" -gt 0 || "$N_ARCH" -gt 0 ]]; then
+ SUBJECT="DWPW→GRS: $N_PUB live / $N_ARCH retired today"
+ EMAIL_BODY="$(cat "$BODY")
+--
+Batch rebuilt from the Google Sheet ($ROWS GRS rows). Ledger + undo:
+~/.claude/yolo-queue/executed-reversible/ledger.jsonl (ticket dwpw-grs-migrate)."
+ # George Basic auth: prefer GEORGE_AUTH from the secrets master, else fleet default.
+ GAUTH="$(grep -m1 '^GEORGE_AUTH=' "$HOME/Projects/secrets-manager/.env" 2>/dev/null | cut -d= -f2- | tr -d '\r')"
+ GAUTH="${GAUTH:-admin:DWSecure2024!}"
+ # Build the JSON payload via env vars (NOT source interpolation) so a quote or
+ # backslash in a title/handle can never break the payload.
+ PAYLOAD="$(SUBJECT="$SUBJECT" EMAIL_BODY="$EMAIL_BODY" "$PY" - <<'PY'
+import json, os
+print(json.dumps({
+ "account": "steve-office",
+ "to": "steve@designerwallcoverings.com",
+ "subject": os.environ["SUBJECT"],
+ "body": os.environ["EMAIL_BODY"],
+ "source": "dwpw-grs-daily",
+}))
+PY
+)"
+ if [[ "$DRY" == "1" ]]; then
+ echo "-- DRY: WOULD email steve@designerwallcoverings.com"
+ echo " subject: $SUBJECT"
+ printf ' body:\n%s\n' "$EMAIL_BODY" | sed 's/^/ /'
+ SENT="dry"
+ else
+ RESP="$(curl -sS --fail -m 15 -X POST 'http://127.0.0.1:9850/api/send' \
+ -u "$GAUTH" \
+ -H 'Content-Type: application/json' \
+ -d "$PAYLOAD" 2>&1)"
+ if [[ $? -eq 0 ]]; then SENT="yes"; else SENT="FAILED:$RESP"; fi
+ echo "-- George send: $SENT"
+ fi
+fi
+
+# 6) append run record
+echo "$TS dry=$DRY batch_rows=$ROWS published=$N_PUB archived=$N_ARCH email=$SENT" >> "$LOG"
+echo "== done =="
← 7f40543 auto-data-snapshot: 2026-09-08T14:18:02 (4 data files) — scr
·
back to Designerwallcoverings
·
chore: ruff safe-fixes (import ordering) on dwpw-grs migrati b77df3e →