generate_designs: committed read-back in persistDesign — phantom-insert fix

84ccb964c1029f7ee7bf91be9ae79f4826e2bf58 · 2026-06-04 08:13:08 -0700 · Steve Abrams

Root cause (6-agent workflow, high confidence): all_designs.id defaults to a
shared sequence (spoon_all_designs_id_seq). When an INSERT fails/rolls back
after nextval fires (settlement-trigger rejection or concurrent-generator
interleave), persistDesign() returned the burned id as success without
verifying the row committed — so batches reported phantom ids (PNG on disk,
no DB row), which looked like silent deletions. persistDesign now does a
fresh-connection SELECT read-back and throws if the row isn't visible; the
batch loop catch logs ✗ and continues, keeping the Created N/M count truthful.
Reversible: remove the read-back block.

Files touched

M scripts/generate_designs.js

Diff

commit 84ccb964c1029f7ee7bf91be9ae79f4826e2bf58
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Thu Jun 4 08:13:08 2026 -0700

    generate_designs: committed read-back in persistDesign — phantom-insert fix
    
    Root cause (6-agent workflow, high confidence): all_designs.id defaults to a
    shared sequence (spoon_all_designs_id_seq). When an INSERT fails/rolls back
    after nextval fires (settlement-trigger rejection or concurrent-generator
    interleave), persistDesign() returned the burned id as success without
    verifying the row committed — so batches reported phantom ids (PNG on disk,
    no DB row), which looked like silent deletions. persistDesign now does a
    fresh-connection SELECT read-back and throws if the row isn't visible; the
    batch loop catch logs ✗ and continues, keeping the Created N/M count truthful.
    Reversible: remove the read-back block.
---
 scripts/generate_designs.js | 20 +++++++++++++++++---
 1 file changed, 17 insertions(+), 3 deletions(-)

diff --git a/scripts/generate_designs.js b/scripts/generate_designs.js
index 7b8d86f..3f90722 100644
--- a/scripts/generate_designs.js
+++ b/scripts/generate_designs.js
@@ -730,11 +730,25 @@ VALUES (
 ) RETURNING id;`;
   const idStr = psql(sql);
   const newId = parseInt(idStr, 10);
+  if (!Number.isFinite(newId)) {
+    throw new Error('persistDesign: INSERT returned no id (insert failed or rolled back before RETURNING)');
+  }
+  // Committed read-back (2026-06-04 phantom-insert fix). all_designs.id defaults
+  // to nextval('spoon_all_designs_id_seq') — a value burned even when the INSERT
+  // later fails/rolls back (settlement-trigger rejection, or a concurrent
+  // generator interleaving on the shared sequence). Without this check, a phantom
+  // id whose row never committed was returned + logged as a ✓ success and pushed
+  // to created[], so the batch count lied and the "design" had only a PNG on disk
+  // and no DB row. Each psql() spawns a fresh connection, so this SELECT only sees
+  // the row if the INSERT actually committed — a phantom throws here instead, and
+  // main()'s per-design catch logs it + continues, keeping the count truthful.
+  const check = psql(`SELECT id FROM all_designs WHERE id=${newId};`).trim();
+  if (check !== String(newId)) {
+    throw new Error(`persistDesign: id ${newId} reported by RETURNING but NOT committed/visible — sequence burned, no row persisted`);
+  }
   // image_url can't be known until the row exists — set it to the by-id image
   // route so every grid / hero / search renderer has a working <img> src.
-  if (Number.isFinite(newId)) {
-    try { psql(`UPDATE spoon_all_designs SET image_url='/designs/img/by-id/${newId}' WHERE id=${newId};`); } catch {}
-  }
+  try { psql(`UPDATE spoon_all_designs SET image_url='/designs/img/by-id/${newId}' WHERE id=${newId};`); } catch {}
   return idStr;
 }

← 4e561f7 TODO: log staged Monterey/California-coast scenic mural batc · back to Wallco Ai · damask line batch-3: +3 heritage damasks live (noir-rose, sl 8537412 →