← back to Wallco Ai
Dedupe designs.json snapshot: DISTINCT ON (id) + dedup-aware shrink guards
f55cefcb0382c5b12b08a8735650cd3938c04903 · 2026-06-21 19:39:09 -0700 · Steve
all_designs base table physically stores ~2 byte-identical rows per id
(69,399 rows / 35,192 unique; 34,207/34,207 dup ids fully identical, 0 differ).
spoon_all_designs view is a plain SELECT FROM all_designs (no JOIN), so the
builder faithfully emitted every physical row -> designs.json doubled.
Fix:
- SELECT DISTINCT ON (id) collapses the fan-out at the point of emission
(lossless: kept copy is byte-identical to the dropped one).
- Catastrophe guard now compares UNIQUE-id coverage (not raw rows) so a
legitimate 2x->1x dedup passes while a sparse-PG regen still aborts.
- Byte-size guard now compares bytes-per-RAW-ROW (not total bytes) so a
dedup (same B/row, ~half total) passes while a field-strip clobber aborts.
- Added a WARN if any prior unique id disappears.
Validated locally (scratch dirs only, prod designs.json untouched):
53,385->27,165 raw rows, 27,165->27,165 unique ids (0 dropped/added),
0 payload mismatches, published render set 6,264->6,264 unchanged.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Files touched
M scripts/refresh_designs_snapshot.py
Diff
commit f55cefcb0382c5b12b08a8735650cd3938c04903
Author: Steve <steve@designerwallcoverings.com>
Date: Sun Jun 21 19:39:09 2026 -0700
Dedupe designs.json snapshot: DISTINCT ON (id) + dedup-aware shrink guards
all_designs base table physically stores ~2 byte-identical rows per id
(69,399 rows / 35,192 unique; 34,207/34,207 dup ids fully identical, 0 differ).
spoon_all_designs view is a plain SELECT FROM all_designs (no JOIN), so the
builder faithfully emitted every physical row -> designs.json doubled.
Fix:
- SELECT DISTINCT ON (id) collapses the fan-out at the point of emission
(lossless: kept copy is byte-identical to the dropped one).
- Catastrophe guard now compares UNIQUE-id coverage (not raw rows) so a
legitimate 2x->1x dedup passes while a sparse-PG regen still aborts.
- Byte-size guard now compares bytes-per-RAW-ROW (not total bytes) so a
dedup (same B/row, ~half total) passes while a field-strip clobber aborts.
- Added a WARN if any prior unique id disappears.
Validated locally (scratch dirs only, prod designs.json untouched):
53,385->27,165 raw rows, 27,165->27,165 unique ids (0 dropped/added),
0 payload mismatches, published render set 6,264->6,264 unchanged.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
---
scripts/refresh_designs_snapshot.py | 71 ++++++++++++++++++++++++++++++++-----
1 file changed, 63 insertions(+), 8 deletions(-)
diff --git a/scripts/refresh_designs_snapshot.py b/scripts/refresh_designs_snapshot.py
index 36522c7..c255f6c 100644
--- a/scripts/refresh_designs_snapshot.py
+++ b/scripts/refresh_designs_snapshot.py
@@ -189,7 +189,17 @@ def title_for(cat, hex_, id_, prompt=None):
rows = psql_json(
"SELECT COALESCE(json_agg(t),'[]'::json) FROM ("
- "SELECT id, kind, category, dominant_hex, prompt, generator, seed, local_path, "
+ # DEDUP AT SOURCE (2026-06-21): the base table `all_designs` (under the
+ # `spoon_all_designs` view) physically stores ~2 byte-identical rows per id
+ # (69,399 rows / 35,192 unique ids; verified 34,207/34,207 dup ids are
+ # fully identical across every column, 0 differ). The view is a plain
+ # `SELECT ... FROM all_designs` with no JOIN/UNION, so it faithfully emits
+ # every physical row → designs.json doubled (every id ~2×). DISTINCT ON (id)
+ # collapses each id to one row at the point of emission, which is correct
+ # whether the table is later de-duplicated or not. The ORDER BY (id) below
+ # makes DISTINCT ON deterministic. This is lossless: the kept copy is
+ # byte-identical to the dropped one.
+ "SELECT DISTINCT ON (id) id, kind, category, dominant_hex, prompt, generator, seed, local_path, "
"is_published, created_at, motifs, room_mockups, notes, tags, "
"settlement_verdict, ai_title, "
# 2026-05-31 — added ai_title so admin-set titles (curator rewrites,
@@ -354,19 +364,40 @@ for r in rows:
if OUT.exists() and os.environ.get('WALLCO_FORCE_SNAPSHOT') != '1':
try:
_existing = json.loads(OUT.read_text())
- _existing_n = len(_existing) if isinstance(_existing, list) else 0
+ _existing_list = _existing if isinstance(_existing, list) else []
+ _existing_n = len(_existing_list)
except Exception:
+ _existing_list = []
_existing_n = 0
- if _existing_n >= 1000 and len(out) < _existing_n * 0.5:
- print(f"ABORT: refusing to shrink designs.json {_existing_n} → {len(out)} "
- f"(<50% of existing). Almost certainly a sparse-PG regen "
- f"(this PG has {len(rows)} rows). Keeping the existing snapshot. "
+ # Compare UNIQUE-id coverage, not raw row counts (2026-06-21). The real
+ # measure of catalog loss is "did we lose designs", i.e. distinct ids — NOT
+ # raw row count. A legitimate dedup (the old file stored every id ~2×; this
+ # build now emits one row per id) halves the RAW row count while preserving
+ # 100% of unique ids — that must NOT trip the guard. A genuine sparse-PG
+ # regen, by contrast, collapses unique-id coverage too, and is still caught.
+ _existing_uids = {x.get('id') for x in _existing_list}
+ _existing_uid_n = len(_existing_uids)
+ _new_uids = {x['id'] for x in out}
+ _new_uid_n = len(_new_uids)
+ if _existing_uid_n >= 1000 and _new_uid_n < _existing_uid_n * 0.5:
+ print(f"ABORT: refusing to shrink designs.json unique-ids {_existing_uid_n} → {_new_uid_n} "
+ f"(<50% of existing UNIQUE ids). Almost certainly a sparse-PG regen "
+ f"(this PG has {len(rows)} eligible rows). Keeping the existing snapshot. "
f"Set WALLCO_FORCE_SNAPSHOT=1 to override intentionally.")
try:
(OUT.parent / 'designs.json.rejected').write_text(json.dumps(out))
except Exception:
pass
raise SystemExit(0)
+ # Belt-and-suspenders: also flag if any previously-present unique id
+ # DISAPPEARS in a large catalog — dedup can never drop an id, so a missing
+ # id means real loss. Warn (don't hard-abort) so an intentional unpublish/
+ # purge isn't blocked, but it's visible in the deploy log.
+ _dropped = _existing_uids - _new_uids
+ if _existing_uid_n >= 1000 and _dropped:
+ print(f"WARN: {len(_dropped)} unique id(s) present in prior snapshot are absent "
+ f"from this build (e.g. {sorted(x for x in _dropped if x is not None)[:5]}). "
+ f"Expected only if rows were genuinely removed/unpublished-out-of-scope.")
_new_blob = json.dumps(out, indent=2)
# Byte-size guard — the COUNT guard above misses field-shrink clobbers: the
@@ -375,9 +406,33 @@ _new_blob = json.dumps(out, indent=2)
# Refuse a materially smaller FILE unless explicitly forced.
if OUT.exists() and os.environ.get('WALLCO_FORCE_SNAPSHOT') != '1':
_old_bytes = OUT.stat().st_size
- if _old_bytes > 1000000 and len(_new_blob) < _old_bytes * 0.85:
+ # Per-RAW-ROW byte size, not total bytes (2026-06-21). The field-shrink
+ # clobber this guard catches KEEPS the row count but strips fields → fewer
+ # bytes PER ROW. A dedup (this build) drops duplicate rows but keeps every
+ # surviving row's payload intact → bytes-per-RAW-ROW is essentially
+ # UNCHANGED (verified 743.4 → 738.1, ratio 0.993) while total bytes ~halve.
+ # So gate on bytes/raw-row: a real field-strip still trips it; a benign
+ # dedup does not. (bytes/unique-id was rejected — it's fooled on the FIRST
+ # dedup run because the prior file's bytes are doubled while its unique-id
+ # count is not, inflating old bytes/unique-id ~2× and false-aborting.)
+ # Fall back to the total-byte check when we can't read the prior row count.
+ _new_rows_n = len(out)
+ _old_rows_n = _existing_n if ('_existing_n' in dir()) else 0
+ _new_bpr = (len(_new_blob) / _new_rows_n) if _new_rows_n else 0
+ _old_bpr = (_old_bytes / _old_rows_n) if _old_rows_n else 0
+ if _old_bpr and _old_bytes > 1000000 and _new_bpr < _old_bpr * 0.85:
+ print(f"ABORT: new designs.json {_new_bpr:.0f}B/row < 85%% of existing "
+ f"{_old_bpr:.0f}B/row (field-shrink clobber guard). "
+ f"Keeping existing. Override WALLCO_FORCE_SNAPSHOT=1.")
+ try: (OUT.parent / 'designs.json.rejected').write_text(_new_blob)
+ except Exception: pass
+ raise SystemExit(0)
+ elif not _old_bpr and _old_bytes > 1000000 and len(_new_blob) < _old_bytes * 0.85:
+ # Couldn't read prior row count → fall back to the original total-byte
+ # guard so we never lose the field-shrink protection.
print(f"ABORT: new designs.json {len(_new_blob)}B < 85%% of existing {_old_bytes}B "
- f"(field-shrink clobber guard). Keeping existing. Override WALLCO_FORCE_SNAPSHOT=1.")
+ f"(field-shrink clobber guard, total-byte fallback). Keeping existing. "
+ f"Override WALLCO_FORCE_SNAPSHOT=1.")
try: (OUT.parent / 'designs.json.rejected').write_text(_new_blob)
except Exception: pass
raise SystemExit(0)
← 37d9df9 marketplace/db: never pass undefined password to pg (fixes S
·
back to Wallco Ai
·
TODO: log designs.json dedup fix + gated prod rebuild step 8eb1bbe →