add backfill-wallco-rooms.js: populate /admin/rooms gallery from the 1,421 bulk data/rooms mockups that generate_room_mockups.py never inserted into wallco_rooms (idempotent, tagged for reversal)

669fc05ae3772fbc817aa3ff5df9f7ff56d60810 · 2026-05-29 14:33:03 -0700 · Steve Abrams

Files touched

A scripts/backfill-wallco-rooms.js

Diff

commit 669fc05ae3772fbc817aa3ff5df9f7ff56d60810
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Fri May 29 14:33:03 2026 -0700

    add backfill-wallco-rooms.js: populate /admin/rooms gallery from the 1,421 bulk data/rooms mockups that generate_room_mockups.py never inserted into wallco_rooms (idempotent, tagged for reversal)
---
 scripts/backfill-wallco-rooms.js | 70 ++++++++++++++++++++++++++++++++++++++++
 1 file changed, 70 insertions(+)

diff --git a/scripts/backfill-wallco-rooms.js b/scripts/backfill-wallco-rooms.js
new file mode 100644
index 0000000..e42afd2
--- /dev/null
+++ b/scripts/backfill-wallco-rooms.js
@@ -0,0 +1,70 @@
+#!/usr/bin/env node
+/**
+ * backfill-wallco-rooms — the /admin/rooms gallery reads the wallco_rooms table,
+ * but it's only populated by the interactive "save room render" endpoint
+ * (server.js ~19435). The BULK room mockups generated by
+ * scripts/generate_room_mockups.py land in data/rooms/design_<id>_<room>.png +
+ * the spoon_all_designs.room_mockups JSONB — and were NEVER inserted into
+ * wallco_rooms. So the gallery showed ~200 of the ~1,400+ renders that exist.
+ *
+ * This scans data/rooms/design_*.png and inserts a wallco_rooms row for any not
+ * already present (matched by image_path = /designs/room/<basename>, which is
+ * the public static route that serves data/rooms/). Idempotent — re-runnable.
+ * Tagged notes='backfill:data/rooms' so it's trivially reversible:
+ *   DELETE FROM wallco_rooms WHERE notes='backfill:data/rooms';
+ *
+ * Usage:  node scripts/backfill-wallco-rooms.js [--dry]
+ */
+'use strict';
+const fs = require('fs');
+const path = require('path');
+const { execSync } = require('child_process');
+const ROOT = path.resolve(__dirname, '..');
+const ROOMS = path.join(ROOT, 'data', 'rooms');
+const DRY = process.argv.includes('--dry');
+
+function psql(sql) {
+  return execSync(`psql dw_unified -At -F'\x1f' -c ${JSON.stringify(sql)}`, {
+    encoding: 'utf8', maxBuffer: 64 * 1024 * 1024,
+  });
+}
+function esc(s) { return String(s).replace(/'/g, "''"); }
+
+// 1) existing image_paths already in wallco_rooms (idempotency guard)
+const existing = new Set(
+  psql("SELECT image_path FROM wallco_rooms WHERE image_path IS NOT NULL;")
+    .split('\n').map(s => s.trim()).filter(Boolean)
+);
+
+// 2) scan data/rooms/design_<id>_<room>.png — skip templates/masks
+const files = fs.readdirSync(ROOMS).filter(f => /^design_\d+_.+\.png$/.test(f));
+const rows = [];
+let skipExisting = 0, skipParse = 0;
+for (const f of files) {
+  const m = f.match(/^design_(\d+)_(.+)\.png$/);
+  if (!m) { skipParse++; continue; }
+  const designId = parseInt(m[1], 10);
+  const roomType = m[2];                       // e.g. living_room, bedroom, living_room_hero
+  const imagePath = `/designs/room/${f}`;
+  if (existing.has(imagePath)) { skipExisting++; continue; }
+  let bytes = 0;
+  try { bytes = fs.statSync(path.join(ROOMS, f)).size; } catch {}
+  rows.push({ designId, roomType, filename: f, imagePath, bytes });
+}
+
+console.log(`data/rooms PNGs: ${files.length} · already in wallco_rooms: ${skipExisting} · unparseable: ${skipParse} · to insert: ${rows.length}`);
+if (DRY) { console.log('--- DRY: no inserts. Sample:', rows.slice(0, 3)); process.exit(0); }
+if (!rows.length) { console.log('nothing to backfill.'); process.exit(0); }
+
+// 3) batched multi-row INSERT (chunks of 500)
+let inserted = 0;
+for (let i = 0; i < rows.length; i += 500) {
+  const chunk = rows.slice(i, i + 500);
+  const values = chunk.map(r =>
+    `(${r.designId}, '${esc(r.roomType)}', '${esc(r.filename)}', '${esc(r.imagePath)}', ${r.bytes}, FALSE, 'backfill:data/rooms')`
+  ).join(',');
+  psql(`INSERT INTO wallco_rooms (design_id, room_type, filename, image_path, file_size_bytes, selected_for_marketing, notes) VALUES ${values};`);
+  inserted += chunk.length;
+  process.stdout.write(`  inserted ${inserted}/${rows.length}\r`);
+}
+console.log(`\n✓ backfilled ${inserted} room rows into wallco_rooms (tagged notes='backfill:data/rooms').`);

← 6a83e70 admin/rooms: img src prefers /designs/room/design_<id>_<type · back to Wallco Ai · fix(colorways/promote): designs.json fallback + nullable FK 8ae5031 →