`lib/db.mjs`

59 lines
// Read-only DB access for the unbuyable-recovery pilot.
//
// SAFETY MODEL (proven, not asserted — see README "Safety" section):
//   Every query is wrapped in `BEGIN READ ONLY; <sql>; ROLLBACK`. Postgres
//   rejects any INSERT/UPDATE/DELETE/DDL against a persistent object inside a
//   READ ONLY transaction with `cannot execute X in a read-only transaction`,
//   BEFORE it touches a row. The trailing ROLLBACK guarantees nothing is
//   committed even in the impossible case a write slipped through.
//
//   NB: two traps we hit + fixed:
//   (1) `SET default_transaction_read_only=on` in the same statement does NOT
//       work — it only governs transactions that START after it. Use
//       BEGIN READ ONLY, which applies to the current transaction.
//   (2) `psql -c "stmt1; stmt2; stmt3"` uses PQexec, which returns ONLY the
//       last statement's result — so BEGIN;SELECT;ROLLBACK silently drops the
//       SELECT rows. We feed the statements via STDIN instead, where psql runs
//       them sequentially and prints the SELECT while READ ONLY still persists.
//
// Connects over the local /tmp unix socket to the dw_unified MIRROR (the
// read/staging copy on this box), never Kamatera-canonical. No `pg` module
// dependency — shells to the system `psql`.

import { spawnSync } from 'node:child_process';

// ON_ERROR_STOP=1 so a bad SELECT aborts with a nonzero exit (surfaced as a
// thrown Error) instead of silently yielding an empty result set.
const PSQL_ARGS = ['-h', '/tmp', '-d', 'dw_unified', '-tA', '-v', 'ON_ERROR_STOP=1'];
const MAX_BUF = 64 * 1024 * 1024; // 64 MB — ample for any catalog-sized json_agg result

/**
 * Run a SELECT and return an array of row objects.
 * @param {string} selectSql  a single SELECT statement (no trailing semicolon)
 * @returns {object[]}
 */
export function queryRows(selectSql) {
  const script = `BEGIN READ ONLY;\nSELECT json_agg(t) FROM (${selectSql}) t;\nROLLBACK;\n`;
  const res = spawnSync('psql', PSQL_ARGS, { encoding: 'utf8', input: script, maxBuffer: MAX_BUF });
  if (res.status !== 0) {
    throw new Error(`psql failed (status ${res.status}):\n${res.stderr || res.stdout}`);
  }
  // stdout = BEGIN tag + the json_agg value (which may span multiple physical
  // lines) + ROLLBACK tag. Strip the command tags, then parse the whole blob —
  // never line-by-line, since a large JSON array wraps across lines.
  const blob = res.stdout
    .split('\n')
    .filter(l => l.trim() !== 'BEGIN' && l.trim() !== 'ROLLBACK')
    .join('\n')
    .trim();
  if (blob === '' || blob === 'null' || blob === '\\N') return [];
  const parsed = JSON.parse(blob);
  return parsed == null ? [] : parsed;
}

/** Convenience: run a SELECT expected to return exactly one row; return it. */
export function queryOne(selectSql) {
  const rows = queryRows(selectSql);
  return rows[0] ?? null;
}