`lib/db.js`

97 lines
// lib/db.js — Postgres helpers for server.js (non-marketplace surface).
//
// REFACTOR-1 (2026-05-23): extracted verbatim from server.js (lines 80-94 +
// 1482-1487 in the pre-refactor file). Behavior identical — same execSync
// path, same PSQL_CMD shape, same value escaping rules.
//
// Note: this module is intentionally not the pg.Pool client used by
// src/marketplace/db.js — server.js's main surface still shells out to
// psql via execSync. Migrating to pg.Pool is a separate refactor
// (REFACTOR-2 in the original plan, post audit 2026-05-23) — the agent
// estimated 80-120ms savings per call site × 134 call sites. For now,
// preserve current behavior so this commit ships behavior-preserving.

'use strict';
const { execSync } = require('child_process');

const DB_NAME = process.env.WALLCO_DB || 'dw_unified';
const DB_URL = process.env.DATABASE_URL || DB_NAME;

// On Linux (Kamatera) pm2 runs as root but PG has no 'root' role — sudo as
// postgres. On macOS local trust auth works for the current user.
const PSQL_CMD = (process.platform === 'linux')
  ? `sudo -n -u postgres psql ${DB_NAME} -At -q`
  : `psql ${DB_NAME} -At -q`;

// lock_timeout guard (2026-05-30): every call here is a SYNCHRONOUS execSync,
// so any psql that blocks waiting on a Postgres lock hangs the entire
// single-threaded node event loop until the lock clears. At BOOT this is fatal:
// the unconditional `ALTER TABLE all_designs ...` migrations (ensureUpscaleBytes/
// ensureUserRemoved/...) need ACCESS EXCLUSIVE, and on this busy multi-session
// box they queue behind a long catalog reader — wedging the boot BEFORE
// app.listen so :9905 never binds and the whole site looks dead (Generator/Studio
// hooks all return connection-refused). PGOPTIONS sets lock_timeout for wallco's
// OWN psql connections only (other sessions spawn their own psql, unaffected), so
// a contended statement fails fast (caught by callers' try/catch + the boot
// migrations are IF-NOT-EXISTS no-ops) instead of hanging forever. lock_timeout
// bounds only time spent WAITING for a lock — long-running reads (the 6-40s
// json_agg catalog load) are NOT affected.
const EXEC_ENV = Object.assign({}, process.env, {
  PGOPTIONS: `${process.env.PGOPTIONS ? process.env.PGOPTIONS + ' ' : ''}-c lock_timeout=${process.env.WALLCO_LOCK_TIMEOUT_MS || '8000'}`,
});

// Stdin-fed psql call. NEVER shell-pipe SQL as a -c "${sql}" argument —
// double-quote injection. (SEC-1 fix in commit cf388a2 covers this for the
// last remaining shell-pipe site.)
function psqlQuery(sql) {
  return execSync(PSQL_CMD, { input: sql, encoding: 'utf8', maxBuffer: 200_000_000, env: EXEC_ENV }).trim();
}

// PG value escaper for inline SQL — matches the project's existing inline-string style.
function pgEsc(v) {
  if (v === null || v === undefined) return 'NULL';
  if (typeof v === 'number') return Number.isFinite(v) ? String(v) : 'NULL';
  if (typeof v === 'boolean') return v ? 'TRUE' : 'FALSE';
  return "'" + String(v).replace(/'/g, "''") + "'";
}

// Stdin-fed write path (UPDATE/DELETE/INSERT...RETURNING/multi-statement).
//
// HARDENED 2026-05-29: runs psql with `ON_ERROR_STOP=1` so a SQL error makes
// psql exit non-zero and we THROW. Without it, psql's default is to print the
// error, exit 0, and the caller mistakes the failure for success. That false
// success masked a real prod incident: `all_designs` rejected EVERY UPDATE for
// lack of a replica identity (it was in an update-publishing publication), yet
// the cactus curator kept returning `applied:N` and silently persisted nothing.
// On error we surface psql's stderr (the actual `ERROR: ...` line) so callers —
// most of which already sit in try/catch — get an actionable message and the
// route returns a real 500 instead of a lie. `ON_ERROR_STOP` stops on ERROR
// only, never on NOTICE/WARNING, so boot-time `CREATE ... IF NOT EXISTS`
// (NOTICE: already exists) is unaffected. Reads (psqlQuery) stay lenient by
// design. See feedback_cactus_curator_bulk_delete_forkstorm memory.
const PSQL_CMD_STRICT = `${PSQL_CMD} -v ON_ERROR_STOP=1`;
function psqlExecLocal(sql) {
  try {
    return execSync(PSQL_CMD_STRICT, { input: sql, encoding: 'utf8', maxBuffer: 200_000_000, env: EXEC_ENV }).trim();
  } catch (e) {
    const stderr = (e.stderr ? e.stderr.toString() : '').trim();
    const stdout = (e.stdout ? e.stdout.toString() : '').trim();
    const detail = (stderr || stdout || e.message || 'unknown psql error')
      .split('\n').filter(Boolean).slice(0, 4).join(' | ').slice(0, 600);
    const err = new Error(`psql write failed (exit ${e.status == null ? '?' : e.status}): ${detail}`);
    err.isPsqlError = true;
    err.psqlStderr = stderr;
    err.exitCode = e.status;
    throw err;
  }
}

module.exports = {
  DB_NAME,
  DB_URL,
  PSQL_CMD,
  psqlQuery,
  pgEsc,
  psqlExecLocal,
};