← back to Wallco Ai
Add gated variant-pick export: dry-run by default, publishes kept picks to Kamatera all_designs + scp PNG on --live
78491e9c4904fcb46838b340e78f8cc717e415a8 · 2026-06-12 08:28:16 -0700 · Steve Abrams
Files touched
A scripts/publish-variant-picks.js
Diff
commit 78491e9c4904fcb46838b340e78f8cc717e415a8
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Fri Jun 12 08:28:16 2026 -0700
Add gated variant-pick export: dry-run by default, publishes kept picks to Kamatera all_designs + scp PNG on --live
---
scripts/publish-variant-picks.js | 325 +++++++++++++++++++++++++++++++++++++++
1 file changed, 325 insertions(+)
diff --git a/scripts/publish-variant-picks.js b/scripts/publish-variant-picks.js
new file mode 100644
index 0000000..89c2dc6
--- /dev/null
+++ b/scripts/publish-variant-picks.js
@@ -0,0 +1,325 @@
+#!/usr/bin/env node
+/* publish-variant-picks.js — gated export that publishes Steve's KEPT variant
+ * picks LIVE on wallco.ai (Kamatera). Built 2026-06-12 (Steve-approved "y").
+ *
+ * WHAT IT DOES
+ * Reads the kept rows (kept=TRUE) from the LOCAL sidecar wallco_variant_picks.
+ * Each pick references a CHILD variant design id (e.g. 10000029) whose parent
+ * is one of the 9 Wave-1 roots. For each kept variant the export:
+ * 1. mirrors the local all_designs row -> Kamatera all_designs (INSERT if the
+ * row is absent there, else UPDATE), carrying id / parent_design_id /
+ * generator / kind / category / prompt / negative_prompt / seed /
+ * dominant_hex / palette / width_in / height_in / panels / tags / motifs /
+ * brand / source_dw_sku / local_path / image_url, and sets is_published=TRUE.
+ * 2. scp's the variant composite PNG (the local row's local_path basename)
+ * to the remote IMG_DIR so /designs/img/by-id/<id> resolves on prod.
+ * The view spoon_all_designs (a VIEW over all_designs on prod, 10,766 rows) then
+ * exposes the row to every public surface; /design/<id> works immediately and
+ * the grid picks it up on the next guarded snapshot refresh.
+ *
+ * TOPOLOGY (memory dw-unified-kamatera-canonical)
+ * Kamatera is the SOURCE OF TRUTH; Mac2 only SUBSCRIBES (Kamatera->Mac2). Local
+ * inserts do NOT flow up — so this export WRITES to Kamatera explicitly via
+ * `ssh my-server "sudo -u postgres psql -d dw_unified"`. On Kamatera all_designs
+ * has FULL replica identity (relreplident=f) so UPDATE is legal; it has NO PK /
+ * unique constraint, so existence is checked first and we branch INSERT vs UPDATE
+ * (no ON CONFLICT). sudo -u postgres uses peer auth on the box — NO password is
+ * ever read, hardcoded, or printed.
+ *
+ * HARD CONSTRAINTS (Steve's standing rules)
+ * - PROD WRITE to the canonical box => Steve-gated. DEFAULT MODE IS --dry-run.
+ * This script must be run with --live by STEVE; the agent only proves --dry-run.
+ * - ROOTS ARE SACRED. Only ever publishes the CHILD variant rows that are kept;
+ * never touches a root, never overwrites a root, never publishes the parent.
+ * - LOCAL all_designs stays INSERT-only / untouched. The is_published=TRUE write
+ * happens on KAMATERA, not Mac2. This script never writes the local DB.
+ * - The variant must actually be a child of a Wave-1 root with the redo tags —
+ * re-validated against the LOCAL all_designs before any plan is emitted.
+ *
+ * MODES
+ * --dry-run (DEFAULT) read picks, resolve each, PRINT the exact INSERT/UPDATE +
+ * scp plan. Writes NOTHING to Kamatera, scp's NOTHING.
+ * --live actually perform the Kamatera writes + scp. STEVE RUNS THIS.
+ *
+ * Usage:
+ * node scripts/publish-variant-picks.js # dry-run (default)
+ * node scripts/publish-variant-picks.js --dry-run # explicit dry-run
+ * node scripts/publish-variant-picks.js --live # STEVE ONLY — live publish
+ */
+'use strict';
+
+const fs = require('fs');
+const path = require('path');
+const { execFileSync } = require('child_process');
+
+const ROOT = path.resolve(__dirname, '..');
+
+// ── Config ──────────────────────────────────────────────────────────────────
+const REMOTE = 'my-server'; // ~/.ssh/config alias -> root@45.61.58.125
+const REMOTE_DIR = '/root/public-projects/wallco-ai';
+const REMOTE_IMG = `${REMOTE_DIR}/data/generated`; // server IMG_DIR; /designs/img/by-id falls back to IMG_DIR+basename
+const LOCAL_IMG = path.join(ROOT, 'data', 'generated');
+const WAVE1_ROOTS = [2656, 2766, 3391, 53999, 54002, 54092, 54110, 54112, 54290];
+
+// Columns mirrored local -> Kamatera all_designs. Kept deliberately narrow to the
+// fields that exist on both sides and matter for serving the design.
+const MIRROR_COLS = [
+ 'id', 'parent_design_id', 'generator', 'kind', 'category', 'prompt',
+ 'negative_prompt', 'seed', 'dominant_hex', 'palette', 'width_in', 'height_in',
+ 'panels', 'local_path', 'image_url', 'tags', 'motifs', 'brand', 'source_dw_sku',
+];
+
+// ── DSN resolution (NEVER echo the value) ───────────────────────────────────
+// Local DATABASE_URL self-resolves from env or .env, mirroring sfv.psql / the
+// generator scripts. The Kamatera side is reached via `sudo -u postgres` over
+// ssh — peer auth, no DSN needed and none is printed.
+function resolveLocalDsn() {
+ if (process.env.DATABASE_URL) return process.env.DATABASE_URL;
+ const envPath = path.join(ROOT, '.env');
+ try {
+ for (const line of fs.readFileSync(envPath, 'utf8').split('\n')) {
+ if (line.startsWith('DATABASE_URL=')) return line.slice('DATABASE_URL='.length).trim();
+ }
+ } catch { /* fall through */ }
+ throw new Error('no DATABASE_URL (set env or .env)');
+}
+const LOCAL_DSN = resolveLocalDsn();
+
+// macOS homebrew psql first, fall back to PATH.
+function psqlBin() {
+ const brew = '/opt/homebrew/opt/postgresql@14/bin/psql';
+ return fs.existsSync(brew) ? brew : 'psql';
+}
+
+// Run a SELECT against the LOCAL db, return raw stdout. \x1f field sep / \x1e row sep
+// so prompts (full of commas/pipes) never collide with a delimiter.
+function localQuery(sql) {
+ return execFileSync(psqlBin(), [LOCAL_DSN, '-At', '-F', '\x1f', '-R', '\x1e', '-c', sql],
+ { encoding: 'utf8', maxBuffer: 64 * 1024 * 1024 });
+}
+
+// Run SQL on Kamatera via ssh + sudo -u postgres (peer auth). Returns stdout.
+function remotePsql(sql, { live }) {
+ // -At for scriptable output; pipe the SQL on stdin to avoid quoting hell.
+ const args = [REMOTE, 'sudo -u postgres psql -d dw_unified -At -v ON_ERROR_STOP=1'];
+ return execFileSync('ssh', args, { input: sql, encoding: 'utf8', maxBuffer: 16 * 1024 * 1024 });
+}
+
+// ── SQL literal helpers ─────────────────────────────────────────────────────
+function lit(v) {
+ if (v === null || v === undefined || v === '') return 'NULL';
+ return `'${String(v).replace(/'/g, "''")}'`;
+}
+function num(v) {
+ if (v === null || v === undefined || v === '') return 'NULL';
+ return String(v);
+}
+// text[] from a psql-array string like {a,b,c}
+function textArray(v) {
+ if (v === null || v === undefined || v === '' || v === '{}') return "ARRAY[]::text[]";
+ const inner = v.replace(/^\{/, '').replace(/\}$/, '');
+ if (!inner) return "ARRAY[]::text[]";
+ const parts = inner.split(',').map(s => s.replace(/^"|"$/g, ''));
+ return 'ARRAY[' + parts.map(p => lit(p)).join(',') + ']::text[]';
+}
+function jsonbLit(v) {
+ if (v === null || v === undefined || v === '') return 'NULL';
+ return `${lit(v)}::jsonb`;
+}
+
+// ── Read kept picks from the LOCAL sidecar ──────────────────────────────────
+function readKeptPicks() {
+ const raw = localQuery(
+ 'SELECT design_id, root_id FROM wallco_variant_picks WHERE kept=TRUE ' +
+ 'ORDER BY updated_at DESC NULLS LAST, design_id;'
+ );
+ return raw.split('\x1e').map(r => r.trim()).filter(Boolean).map(r => {
+ const [design_id, root_id] = r.split('\x1f');
+ return { design_id: parseInt(design_id, 10), root_id: parseInt(root_id, 10) };
+ });
+}
+
+// Fetch + validate the local child row. Re-confirms it is a variant CHILD of a
+// Wave-1 root with the redo tags (defense in depth — a sidecar row must not let us
+// publish anything that isn't one of these specific variants).
+function fetchLocalChild(designId, rootId) {
+ const cols = MIRROR_COLS.map(c => {
+ if (c === 'palette') return 'palette::text';
+ if (c === 'tags') return 'tags::text';
+ if (c === 'motifs') return 'motifs::text';
+ return c;
+ }).join(', ');
+ const raw = localQuery(
+ `SELECT ${cols} FROM all_designs WHERE id=${designId} ` +
+ `AND parent_design_id=${rootId} ` +
+ "AND generator='controlnet-anchored-redo' " +
+ "AND (user_removed IS NULL OR user_removed=FALSE) " +
+ "AND local_path IS NOT NULL " +
+ "AND EXISTS (SELECT 1 FROM unnest(tags) tg WHERE tg LIKE 'redo-sample-from-%') " +
+ 'LIMIT 1;'
+ ).split('\x1e')[0].trim();
+ if (!raw) return null;
+ const vals = raw.split('\x1f');
+ const row = {};
+ MIRROR_COLS.forEach((c, i) => { row[c] = vals[i] === undefined ? null : vals[i]; });
+ return row;
+}
+
+// Does the row already exist on Kamatera?
+function existsOnKamatera(id) {
+ const out = remotePsql(`SELECT 1 FROM all_designs WHERE id=${id} LIMIT 1;`, { live: false }).trim();
+ return out === '1';
+}
+
+// Build the INSERT or UPDATE SQL for one mirrored child row.
+function buildRowSql(row, alreadyExists) {
+ if (alreadyExists) {
+ // Row present on prod — just flip is_published=TRUE (roots untouched: we only
+ // ever reach here for a validated CHILD id).
+ return `UPDATE all_designs SET is_published=TRUE WHERE id=${row.id};`;
+ }
+ // INSERT the mirrored child with is_published=TRUE. Explicit id (prod max id is
+ // far below the local 1e7+ variant ids — no collision, no sequence touch).
+ const cols = [...MIRROR_COLS, 'is_published', 'user_removed'];
+ const vals = [
+ num(row.id), num(row.parent_design_id), lit(row.generator), lit(row.kind),
+ lit(row.category), lit(row.prompt), lit(row.negative_prompt), num(row.seed),
+ lit(row.dominant_hex), jsonbLit(row.palette), num(row.width_in), num(row.height_in),
+ num(row.panels), lit(row.local_path), lit(row.image_url), textArray(row.tags),
+ textArray(row.motifs), lit(row.brand), lit(row.source_dw_sku),
+ 'TRUE', 'FALSE',
+ ];
+ return `INSERT INTO all_designs (${cols.join(', ')}) VALUES (${vals.join(', ')});`;
+}
+
+// ── Plan assembly ───────────────────────────────────────────────────────────
+function buildPlan() {
+ const picks = readKeptPicks();
+ const plan = [];
+ for (const p of picks) {
+ if (!WAVE1_ROOTS.includes(p.root_id)) {
+ plan.push({ ...p, skip: 'root not in Wave-1 set' });
+ continue;
+ }
+ const row = fetchLocalChild(p.design_id, p.root_id);
+ if (!row) {
+ plan.push({ ...p, skip: 'not a valid Wave-1 variant child (or user_removed / missing local_path / tags)' });
+ continue;
+ }
+ const basename = path.basename(row.local_path || '');
+ const localPng = path.join(LOCAL_IMG, basename);
+ const localPngExists = fs.existsSync(row.local_path || '') || fs.existsSync(localPng);
+ const exists = existsOnKamatera(row.id);
+ plan.push({
+ variant_id: row.id,
+ parent_root: row.parent_design_id,
+ category: row.category,
+ kind: row.kind,
+ image_file: basename,
+ local_png: fs.existsSync(row.local_path || '') ? row.local_path : localPng,
+ local_png_exists: localPngExists,
+ exists_on_kamatera: exists,
+ action: exists ? 'UPDATE is_published=TRUE' : 'INSERT new row + is_published=TRUE',
+ scp: `${path.basename(row.local_path || '')} -> ${REMOTE}:${REMOTE_IMG}/`,
+ sql: buildRowSql(row, exists),
+ _row: row,
+ _exists: exists,
+ });
+ }
+ return plan;
+}
+
+// ── Dry-run printer ─────────────────────────────────────────────────────────
+function printDryRun(plan) {
+ console.log('================================================================');
+ console.log(' publish-variant-picks.js — DRY-RUN (no Kamatera writes, no scp)');
+ console.log('================================================================');
+ console.log(` Remote : ${REMOTE} (root@45.61.58.125 via ~/.ssh/config)`);
+ console.log(` Remote IMG_DIR: ${REMOTE_IMG}`);
+ console.log(` Remote table : dw_unified.all_designs (canonical; UPDATE-able, relreplident=f)`);
+ console.log(` -> public surfaces read the spoon_all_designs VIEW over it`);
+ console.log(` Local DSN : resolved from ${process.env.DATABASE_URL ? 'env DATABASE_URL' : '.env DATABASE_URL'} (value NOT printed)`);
+ console.log(` Kamatera auth : ssh ${REMOTE} -> sudo -u postgres (peer auth; no password)`);
+ console.log('----------------------------------------------------------------');
+ const actionable = plan.filter(p => !p.skip);
+ const skipped = plan.filter(p => p.skip);
+ console.log(` Kept picks read from sidecar : ${plan.length}`);
+ console.log(` Publishable variants : ${actionable.length}`);
+ console.log(` Skipped : ${skipped.length}`);
+ console.log('----------------------------------------------------------------');
+ if (!plan.length) {
+ console.log(' No kept picks in wallco_variant_picks yet — nothing to publish.');
+ console.log(' (Expected if Steve has only just started curating.)');
+ }
+ for (const p of skipped) {
+ console.log(` SKIP design ${p.design_id} (root ${p.root_id}): ${p.skip}`);
+ }
+ let i = 0;
+ for (const p of actionable) {
+ i++;
+ console.log('');
+ console.log(` [${i}] variant_id=${p.variant_id} parent_root=${p.parent_root} category=${p.category} kind=${p.kind}`);
+ console.log(` exists_on_kamatera : ${p.exists_on_kamatera}`);
+ console.log(` action : ${p.action}`);
+ console.log(` image_file : ${p.image_file}`);
+ console.log(` local png present : ${p.local_png_exists} (${p.local_png})`);
+ if (!p.local_png_exists) {
+ console.log(' !! WARNING: local PNG not found — --live would publish a row whose image 404s on prod');
+ }
+ console.log(` WOULD scp : ${p.scp}`);
+ console.log(` WOULD run SQL : ${p.sql}`);
+ console.log(` After publish : https://wallco.ai/design/${p.variant_id}`);
+ }
+ console.log('');
+ console.log('----------------------------------------------------------------');
+ console.log(' DRY-RUN complete. Nothing was written to Kamatera and nothing was scp\'d.');
+ console.log(' To go live (STEVE ONLY): node scripts/publish-variant-picks.js --live');
+ console.log('================================================================');
+}
+
+// ── Live executor (STEVE ONLY) ──────────────────────────────────────────────
+function runLive(plan) {
+ const actionable = plan.filter(p => !p.skip);
+ if (!actionable.length) {
+ console.log('[live] no publishable picks — nothing to do.');
+ return;
+ }
+ console.log(`[live] publishing ${actionable.length} variant(s) to Kamatera...`);
+ // 1. scp every PNG first so no row is ever published image-less.
+ for (const p of actionable) {
+ if (!p.local_png_exists) {
+ throw new Error(`[live] ABORT: local PNG missing for variant ${p.variant_id} (${p.local_png}) — refusing to publish an image-less row`);
+ }
+ console.log(`[live] scp ${p.image_file} -> ${REMOTE}:${REMOTE_IMG}/`);
+ execFileSync('scp', ['-p', p.local_png, `${REMOTE}:${REMOTE_IMG}/${p.image_file}`],
+ { stdio: 'inherit' });
+ }
+ // 2. Apply all row writes in ONE transaction so a mid-batch failure rolls back.
+ const sqlBatch = ['BEGIN;', ...actionable.map(p => p.sql),
+ // Canary inside the txn: every target id must end up published.
+ `SELECT id, is_published FROM all_designs WHERE id IN (${actionable.map(p => p.variant_id).join(',')}) ORDER BY id;`,
+ 'COMMIT;'].join('\n');
+ console.log('[live] applying row writes (single transaction)...');
+ const out = remotePsql(sqlBatch, { live: true });
+ console.log('[live] canary (id | is_published):');
+ console.log(out.trim());
+ console.log('[live] done. Verify: ' +
+ actionable.map(p => `https://wallco.ai/design/${p.variant_id}`).join(' '));
+ console.log('[live] NOTE: run the guarded snapshot refresh on Mac2 + ./deploy-kamatera.sh');
+ console.log(' to surface these on the public GRID (the PDP /design/<id> already works).');
+}
+
+// ── Main ────────────────────────────────────────────────────────────────────
+function main() {
+ const args = process.argv.slice(2);
+ const live = args.includes('--live');
+ // --dry-run is the default; --live must be explicit.
+ const plan = buildPlan();
+ if (!live) {
+ printDryRun(plan);
+ return;
+ }
+ runLive(plan);
+}
+
+main();
← 96285b0 Add live variant-curator for Wave-1 drunk-animal composition
·
back to Wallco Ai
·
Add self-healing supervisor for variants-9roots regen (relau 8e69f5d →