[object Object]

← back to Wallco Ai

scripts: platform-aware psql (Linux → sudo postgres, macOS → direct)

a2999edcf0726467f993ed85a0505b6757faf477 · 2026-05-11 18:10:07 -0700 · SteveStudio2

The wallco-ai pm2 process on Kamatera runs as root, but PostgreSQL has
no 'root' role — every psql invocation from the spawned generator
scripts was silently failing. Designs would render and land on disk,
but the spoon_all_designs INSERT never happened → review lookup 404'd.

Fix: each script now detects platform.
  - Linux: sudo -n -u postgres psql dw_unified -At -q
  - macOS: psql dw_unified -At -q (local trust auth)

Files:
  scripts/generate_designs.js          (SDXL output → PG insert)
  scripts/generator_tick.js            (30-min cron driver)
  scripts/pull_spoonflower_account.py  (bulk pull upsert)

This matches the same shim already in server.js psqlQuery.
Verified: prod compose now closes the loop end-to-end —
design row inserted, file on disk, designer review fires, all 200.

Files touched

Diff

commit a2999edcf0726467f993ed85a0505b6757faf477
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date:   Mon May 11 18:10:07 2026 -0700

    scripts: platform-aware psql (Linux → sudo postgres, macOS → direct)
    
    The wallco-ai pm2 process on Kamatera runs as root, but PostgreSQL has
    no 'root' role — every psql invocation from the spawned generator
    scripts was silently failing. Designs would render and land on disk,
    but the spoon_all_designs INSERT never happened → review lookup 404'd.
    
    Fix: each script now detects platform.
      - Linux: sudo -n -u postgres psql dw_unified -At -q
      - macOS: psql dw_unified -At -q (local trust auth)
    
    Files:
      scripts/generate_designs.js          (SDXL output → PG insert)
      scripts/generator_tick.js            (30-min cron driver)
      scripts/pull_spoonflower_account.py  (bulk pull upsert)
    
    This matches the same shim already in server.js psqlQuery.
    Verified: prod compose now closes the loop end-to-end —
    design row inserted, file on disk, designer review fires, all 200.
---
 scripts/generate_designs.js         | 11 +++++++++--
 scripts/generator_tick.js           |  6 +++++-
 scripts/pull_spoonflower_account.py | 11 +++++++----
 3 files changed, 21 insertions(+), 7 deletions(-)

diff --git a/scripts/generate_designs.js b/scripts/generate_designs.js
index 251fd1d..ebe9359 100644
--- a/scripts/generate_designs.js
+++ b/scripts/generate_designs.js
@@ -268,9 +268,16 @@ function generate(prompt, seed, outPath) {
 
 // ---- persist --------------------------------------------------------------
 
+// On Linux (Kamatera) pm2 runs as root; PG has no 'root' role so we sudo
+// to postgres. On macOS local trust auth lets us call psql directly.
 function psql(sql) {
-  const r = spawnSync('psql', ['dw_unified', '-At', '-q'], { input: sql, encoding: 'utf8' });
-  if (r.status !== 0) throw new Error(r.stderr);
+  const onLinux = (process.platform === 'linux');
+  const cmd  = onLinux ? 'sudo' : 'psql';
+  const args = onLinux
+    ? ['-n', '-u', 'postgres', 'psql', 'dw_unified', '-At', '-q']
+    : ['dw_unified', '-At', '-q'];
+  const r = spawnSync(cmd, args, { input: sql, encoding: 'utf8' });
+  if (r.status !== 0) throw new Error(r.stderr || r.stdout || 'psql failed');
   return r.stdout.trim();
 }
 
diff --git a/scripts/generator_tick.js b/scripts/generator_tick.js
index 0531a8f..3656d86 100644
--- a/scripts/generator_tick.js
+++ b/scripts/generator_tick.js
@@ -20,8 +20,12 @@ const { execSync, spawnSync } = require('child_process');
 const DB = 'dw_unified';
 const ROOT = path.join(__dirname, '..');
 
+// Platform-aware psql (Linux uses sudo postgres; macOS local trust auth).
+const PSQL_CMD = (process.platform === 'linux')
+  ? `sudo -n -u postgres psql ${DB} -At -q`
+  : `psql ${DB} -At -q`;
 function psql(sql) {
-  return execSync(`psql ${DB} -At -q`, { input: sql, encoding: 'utf8' }).trim();
+  return execSync(PSQL_CMD, { input: sql, encoding: 'utf8' }).trim();
 }
 
 function pickRecipe() {
diff --git a/scripts/pull_spoonflower_account.py b/scripts/pull_spoonflower_account.py
index 0e16f65..351a4a9 100644
--- a/scripts/pull_spoonflower_account.py
+++ b/scripts/pull_spoonflower_account.py
@@ -28,10 +28,13 @@ DB = "dw_unified"
 
 
 def psql(sql: str) -> str:
-    """Run psql with sql on stdin, return trimmed output."""
-    r = subprocess.run(
-        ["psql", DB, "-At", "-q"], input=sql, text=True, capture_output=True
-    )
+    """Run psql with sql on stdin. Linux → sudo as postgres; macOS → direct."""
+    import sys as _sys
+    if _sys.platform.startswith("linux"):
+        cmd = ["sudo", "-n", "-u", "postgres", "psql", DB, "-At", "-q"]
+    else:
+        cmd = ["psql", DB, "-At", "-q"]
+    r = subprocess.run(cmd, input=sql, text=True, capture_output=True)
     if r.returncode != 0:
         raise RuntimeError(f"psql error: {r.stderr}")
     return r.stdout.strip()

← 0d9edc5 wallco.ai: live scale slider on /design/:id (4-54" repeat, p  ·  back to Wallco Ai  ·  Add 5 Playwright e2e tests for /designs review flow — dual-m d506fdf →