Skip to content

Self-hosted, Self-Improving, Federated Everything App — dev management, agent orchestration, persistent mind, digital twin, second brain, and creative pipeline from a single dashboard over Tailscale to port the user into the OS

39.7 KB
Raw Blame
import { execFile, spawn } from '../lib/childProcess.js';
import { existsSync, mkdirSync, createReadStream } from 'fs';
import { join } from 'path';
import { ServerError } from '../lib/errorHandler.js';
import { checkHealth, query, POOL_CONFIG } from '../lib/db.js';
import { PATHS } from '../lib/fileUtils.js';
import { stripAnsi } from '../lib/ansiStrip.js';
import { resolvePgDumpBinary } from '../lib/pgTools.js';
import { resolveBashBinary, toBashPath } from '../lib/bashResolver.js';
import { resolvePostgresPort } from '../lib/ports.js';
import { assertDatabaseAdmission, createDatabaseMaintenanceJournal } from '../lib/databaseMaintenanceJournal.js';

import { withDatabaseImport } from './databaseImport.js';

import { acceptDatabaseMaintenance } from './databasePreflight.js';
import { createDatabaseAuthority } from '../lib/databaseAuthority.js';

export { preflightDatabaseMaintenance } from './databasePreflight.js';

const rootDir = PATHS.root;
const dbScript = toBashPath(join(rootDir, 'scripts', 'db.sh'));
// Resolve the interpreter explicitly. A bare `bash` on Windows often resolves
// (via PM2's PATH) to WSL, which mounts drives at /mnt/h and can't see the
// `H:/...` drive path above — exit 127. resolveBashBinary() prefers Git Bash,
// which both accepts drive paths and runs db.sh against the Windows toolchain.
const bashBinary = resolveBashBinary();

// One host can receive overlapping admin requests. Reject competing mutations
// before they probe or change configuration; always release after failure.
let databaseOperationActive = false;
async function withDatabaseOperation(operation) {
  // Check synchronously before locking, probing, or launching shell writers.
  assertDatabaseAdmission();
  if (databaseOperationActive) {
    throw new ServerError('Another database operation is in progress. Wait for it to finish and retry.', { status: 409 });
  }
  databaseOperationActive = true;
  try {
    return await operation();
  } finally {
    databaseOperationActive = false;
  }
}

/**
 * Read only the durable operation, including while pool admission is fenced.
 * This is not a health probe or permission to resume writers. Even a verified
 * journal remains fenced until the coordinator explicitly releases admission.
 * Bounded: modes, stage and coordinator state — never endpoints or tokens.
 */
export function getMaintenanceStatus() {
  const journal = createDatabaseMaintenanceJournal();
  const operation = journal.read();
  if (!operation) {
    const last = createDatabaseAuthority().read();
    return { stage: 'idle', fenced: false,
      ...(last ? { lastCutover: { id: last.operationId, source: last.source.mode, target: last.target.mode, sourceRetained: true } } : {}) };
  }
  return {
    id: operation.id,
    stage: operation.stage,
    source: operation.source.mode,
    target: operation.target.mode,
    coordinator: journal.coordinatorStatus(operation.id).state,
    fenced: true,
  };
}

/**
 * Accept an offline cutover. Serialized with every other database admin
 * mutation. Returns `{ accepted, launch }`: the route sends its 202 first and
 * launches the worker afterward, because the worker then stops PortOS.
 */
export const acceptDatabaseCutover = (direction) => withDatabaseOperation(() => acceptDatabaseMaintenance(direction));

/**
 * Same-operation recovery: `{ status, launch }`, launch deferred like
 * acceptance. Runs while fenced, so it bypasses the admin lock.
 */
export async function beginDatabaseCutoverRecovery(id) {
  const { beginDatabaseCutoverRecovery: begin } = await import('./databaseMaintenanceCutover.js');
  return begin(id);
}

/**
 * Run a command and return { stdout, stderr, exitCode }.
 */
function runCmd(cmd, args, timeout = 120_000, env = process.env, { redactFailureOutput = false } = {}) {
  return new Promise((resolve) => {
    execFile(cmd, args, { cwd: rootDir, timeout, env }, (err, stdout, stderr) => {
      const exitCode = err ? (typeof err.code === 'number' ? err.code : 1) : 0;
      // Resolved Compose configuration may contain credentials even when its
      // command fails. Do not put introspection payloads in logs or API errors.
      if (exitCode !== 0 && redactFailureOutput) {
        stdout = '';
        stderr = 'Docker resource inspection failed';
      }
      if (exitCode !== 0) {
        const detail = stripAnsi(stderr || stdout || err.message || '').replace(/\s+/g, ' ').trim();
        console.error(`🗄️ ${cmd} ${args.join(' ')} exited ${exitCode}: ${detail}`);
      }
      resolve({
        stdout: stripAnsi(stdout || ''),
        stderr: stripAnsi(stderr || ''),
        exitCode
      });
    });
  });
}

const DIAGNOSTIC_LIMIT = 400;

/** Bounded, single-line, secret-free command diagnostics for an error envelope. */
function commandDetail(result) {
  let detail = (result.stderr || result.stdout || '').replace(/\s+/g, ' ').trim();
  if (pgPassword) detail = detail.split(pgPassword).join('***');
  return detail.length > DIAGNOSTIC_LIMIT ? `${detail.slice(0, DIAGNOSTIC_LIMIT)}...` : detail;
}

/** Throw a coded 500 when a lifecycle command exited nonzero; else return its result. */
function requireCommandSuccess(result, failureMessage) {
  if (result.exitCode === 0) return result;
  const detail = commandDetail(result);
  throw new ServerError(detail ? `${failureMessage}: ${detail}` : failureMessage, {
    status: 500,
    code: 'DATABASE_COMMAND_FAILED',
    context: { exitCode: result.exitCode, details: detail }
  });
}

const runDbScript = (args) => runCmd(bashBinary, [dbScript, ...args]);

function _parseDbMode(stdout) {
  const match = stdout.match(/Current mode:\s*(\w+)/);
  if (!match) console.warn('🗄️ Failed to parse mode from db.sh output');
  return match?.[1] || 'docker';
}

function emitProgress(io, event, message) {
  io?.emit('database:progress', { event, message });
}

/**
 * Get Docker container resource stats for portos-db.
 */
async function getDockerStats() {
  const result = await runCmd('docker', [
    'stats', 'portos-db', '--no-stream', '--format',
    '{{.CPUPerc}}\t{{.MemUsage}}\t{{.MemPerc}}\t{{.NetIO}}\t{{.BlockIO}}\t{{.PIDs}}'
  ], 10_000);
  if (result.exitCode !== 0) return null;
  const parts = result.stdout.trim().split('\t');
  if (parts.length < 6) return null;
  return {
    cpu: parts[0],
    memUsage: parts[1],
    memPercent: parts[2],
    netIO: parts[3],
    blockIO: parts[4],
    pids: parts[5]
  };
}

/**
 * Get Docker volume disk usage for portos-db data volume.
 */
async function getDockerDiskUsage() {
  // Get volume name and disk usage in parallel
  const [volResult, sizeResult] = await Promise.all([
    runCmd('docker', [
      'inspect', 'portos-db', '--format',
      '{{ range .Mounts }}{{ if eq .Destination "/var/lib/postgresql/data" }}{{ .Name }}{{ end }}{{ end }}'
    ], 5_000),
    runCmd('docker', ['system', 'df', '-v'], 10_000)
  ]);
  if (volResult.exitCode !== 0 || sizeResult.exitCode !== 0) return null;
  const volName = volResult.stdout.trim();
  if (!volName) return null;
  for (const line of sizeResult.stdout.split('\n')) {
    if (line.includes(volName)) {
      const match = line.match(/(\d+(?:\.\d+)?[KMGT]?B)/i);
      return match?.[1] || null;
    }
  }
  return null;
}

/**
 * Get native PostgreSQL process stats via ps.
 */
async function getNativeStats() {
  const result = await runCmd(bashBinary, ['-c',
    'ps aux | grep "[p]ostgres.*-D" | head -1'
  ], 5_000);
  if (result.exitCode !== 0 || !result.stdout.trim()) return null;
  const parts = result.stdout.trim().split(/\s+/);
  // ps aux columns: USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
  if (parts.length < 6) return null;
  const pidRaw = parseInt(parts[1], 10);
  if (!Number.isInteger(pidRaw) || pidRaw <= 0) return null;
  const pid = String(pidRaw);
  // Get all postgres child processes
  const childResult = await runCmd(bashBinary, ['-c',
    `ps -o pid=,pcpu=,pmem=,rss= -p $(pgrep -P ${pid} | tr '\\n' ',')${pid} 2>/dev/null`
  ], 5_000);
  let totalCpu = 0, totalMem = 0, totalRss = 0, pids = 0;
  if (childResult.exitCode === 0) {
    for (const line of childResult.stdout.trim().split('\n')) {
      const cols = line.trim().split(/\s+/);
      if (cols.length >= 4) {
        totalCpu += parseFloat(cols[1]) || 0;
        totalMem += parseFloat(cols[2]) || 0;
        totalRss += parseInt(cols[3], 10) || 0;
        pids++;
      }
    }
  }
  return {
    cpu: `${totalCpu.toFixed(1)}%`,
    memUsage: `${(totalRss / 1024).toFixed(1)} MiB`,
    memPercent: `${totalMem.toFixed(1)}%`,
    pids: String(pids)
  };
}

/**
 * Get native PostgreSQL database size via SQL query.
 */
async function getNativeDiskUsage() {
  const nativePort = process.env.PGPORT || '5432';
  const result = await runCmd('psql', [
    '-h', 'localhost', '-p', nativePort, '-U', pgUser, '-d', pgDb,
    '-tAc', 'SELECT pg_size_pretty(pg_database_size(current_database()))'
  ], 5_000, pgEnv(nativePort));
  if (result.exitCode !== 0) return null;
  return result.stdout.trim() || null;
}

/**
 * Return the current database mode, connectivity, row counts, and resource stats.
 */
export async function getStatus() {
  const [scriptResult, health, sizeResult] = await Promise.all([
    runDbScript(['status']),
    checkHealth(),
    query(`SELECT pg_database_size(current_database()) AS db_bytes,
                  (SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public') AS table_count`)
      .then(r => r.rows[0])
      .catch(() => null)
  ]);

  // Parse mode from script output
  const modeMatch = scriptResult.stdout.match(/Current mode:\s*(\w+)/);
  const mode = modeMatch?.[1] || 'unknown';

  // Parse Docker status
  const dockerRunning = /Container portos-db is running/.test(scriptResult.stdout);
  const dockerInstalled = !/Docker not installed/.test(scriptResult.stdout);
  const dockerDaemon = !/Docker daemon is not running/.test(scriptResult.stdout);

  // Parse native status
  const nativeInstalled = !/Native PostgreSQL not installed/.test(scriptResult.stdout);
  const nativeRunning = /System PostgreSQL is running/.test(scriptResult.stdout);
  const nativeConfigured = nativeInstalled && /PortOS database exists/.test(scriptResult.stdout);

  // Parse row count
  const rowMatch = scriptResult.stdout.match(/Memories table has (\d+|N\/A) rows/);
  const memoryCount = rowMatch?.[1] === 'N/A' ? null : parseInt(rowMatch?.[1] || '0', 10);
  const connected = /Database is accepting connections/.test(scriptResult.stdout);

  // Fetch resource stats for running backends (in parallel)
  const [dockerStats, dockerDisk, nativeStats, nativeDisk] = await Promise.all([
    dockerRunning ? getDockerStats() : Promise.resolve(null),
    dockerRunning ? getDockerDiskUsage() : Promise.resolve(null),
    nativeRunning ? getNativeStats() : Promise.resolve(null),
    nativeConfigured ? getNativeDiskUsage() : Promise.resolve(null)
  ]);

  return {
    mode,
    connected,
    memoryCount,
    dbBytes: sizeResult ? parseInt(sizeResult.db_bytes, 10) : null,
    tableCount: sizeResult ? parseInt(sizeResult.table_count, 10) : null,
    health,
    docker: {
      installed: dockerInstalled,
      daemonRunning: dockerDaemon,
      containerRunning: dockerRunning,
      stats: dockerStats,
      diskUsage: dockerDisk
    },
    native: {
      installed: nativeInstalled,
      configured: nativeConfigured,
      running: nativeRunning,
      stats: nativeStats,
      diskUsage: nativeDisk
    }
  };
}

/**
 * Backend changes require a coordinated stop and verified restart. Neither a
 * snapshot nor a mode-only switch can rebind this process's existing pool.
 */
async function switchDatabaseImpl() {
  // Re-enable only with the durable, verified offline cutover in #8805.
  throw new ServerError(
    'Database migration and switching are temporarily unavailable: a coordinated shutdown and restart is required to preserve writes. Keep using the current backend; backups remain available.',
    { status: 409, code: 'DATABASE_CUTOVER_UNAVAILABLE' }
  );
}

const pgUser = POOL_CONFIG.user;
const pgDb = POOL_CONFIG.database;
const pgPassword = POOL_CONFIG.password;

// Safely quote a PostgreSQL identifier (table name, role name, db name) by
// doubling any embedded double-quotes and wrapping in double-quotes.
const pgQuoteIdentifier = (name) => `"${String(name).replace(/"/g, '""')}"`;
// Safely escape a PostgreSQL string literal value by doubling single-quotes.
const pgEscapeString = (val) => String(val).replace(/'/g, "''");

// Native and Docker use different ports, so both can run simultaneously. Resolve
// them through the canonical port map rather than inheriting PGPORT, which names
// whichever backend this server process is actively connected to.
const NATIVE_PORT = String(resolvePostgresPort('native'));
const DOCKER_PORT = String(resolvePostgresPort('docker'));

const normalizePgHost = (host) => {
  const value = String(host || 'localhost').trim().toLowerCase();
  return ['localhost', '127.0.0.1', '::1', '[::1]'].includes(value) ? 'loopback' : value;
};

const endpointsAlias = (leftHost, leftPort, rightHost, rightPort) =>
  normalizePgHost(leftHost) === normalizePgHost(rightHost)
  && Number(leftPort) === Number(rightPort);

// Prefer Homebrew postgresql@17 binaries over system pg (avoids version mismatch)
// Check both arm64 (/opt/homebrew) and Intel (/usr/local) prefixes
const pg17Bin = (() => {
  for (const prefix of ['/opt/homebrew/opt/postgresql@17', '/usr/local/opt/postgresql@17']) {
    const bin = `${prefix}/bin`;
    if (existsSync(`${bin}/psql`)) return bin;
  }
  return null;
})();

/**
 * Build env object with PGPASSWORD for passing to child processes.
 * Avoids interpolating password into shell strings (shell metacharacter safety).
 */
const pgEnv = (port) => ({
  ...process.env,
  ...(pg17Bin ? { PATH: `${pg17Bin}:${process.env.PATH}` } : {}),
  PGPASSWORD: pgPassword,
  PGHOST: 'localhost',
  PGPORT: String(port),
  PGUSER: pgUser,
  PGDATABASE: pgDb
});

// Abort escalation bounds for importDumpFile's read-failure path: after
// SIGTERM, wait this long for the child's 'close' before escalating to
// SIGKILL, then this much longer before reporting failure without a confirmed
// close. The final bound reports failure without claiming termination was confirmed.
const ABORT_TERM_GRACE_MS = 2_000;
const ABORT_KILL_GRACE_MS = 2_000;

/**
 * Import a pg_dump SQL file into the target database WITHOUT invoking a shell.
 *
 * The legacy path built a `bash -c "sed … \"${dumpFile}\" | psql … -U ${pgUser}
 * -d ${pgDb}"` string, interpolating the dump path, port, user, and db name
 * into the shell — any shell metacharacter in those values (or in a hostile
 * export line surfacing through the pipeline) was injectable. Here psql runs via
 * spawn with an argv array (no shell), the dump is streamed on stdin, and the
 * replay is staged privately before spawning; extension metadata and version
 * directives are omitted only outside stored SQL/COPY data.
 *
 * Runs outside the Express request lifecycle (spawn/stream callbacks), so every
 * async boundary resolves rather than throws — an uncaught throw here would
 * crash the Node process (there is no next(err) to bubble to). Returns the same
 * { stdout, stderr, exitCode } shape as runCmd().
 *
 * A dump READ failure leaves the import half-delivered, so the child is
 * aborted (SIGTERM, escalating to SIGKILL) WITHOUT the normal-EOF stdin end
 * that psql would commit as a finished --single-transaction script, and the
 * promise waits for 'close' or the bounded abort deadline (see abort()).
 */
export async function importDumpFile(dumpFile, port, env, timeout = 120_000) {
  try {
    return await withDatabaseImport(dumpFile, replay => _importReplayFile(replay, port, env, timeout));
  } catch (err) {
    return { stdout: '', stderr: err.message, exitCode: 1 };
  }
}

// Separate process boundary so read-error escalation can be exercised precisely.
export function _importReplayFile(dumpFile, port, env, timeout = 120_000) {
  return new Promise((resolve) => {
    const psql = spawn('psql', [
      '-h', 'localhost', '-p', String(port), '-U', pgUser, '-d', pgDb,
      '-v', 'ON_ERROR_STOP=1', '--single-transaction'
    ], { cwd: rootDir, env });

    // The dump read stream — declared up front so finish() can tear it down. If
    // psql exits early (ON_ERROR_STOP rollback) or the timeout SIGKILLs it, the
    // `drain` we may be waiting on never fires, so we must destroy the source
    // ourselves or its file descriptor leaks.
    //
    // Read/write as latin1 (a lossless 1:1 byte↔char mapping) and split lines on
    // the raw '\n' byte only: the dump's bytes pass through psql untouched
    // regardless of the database's client_encoding, and CR bytes are preserved
    // (readline's crlfDelay would silently drop them). A utf8 decode→re-encode
    // round-trip would corrupt non-utf8 dumps — this keeps the byte-transparency
    // the old `sed | psql` pipe had.
    const src = createReadStream(dumpFile, { encoding: 'latin1' });

    // Cap diagnostic capture so a pathologically chatty psql (verbose \echo,
    // giant error text) can't exhaust the heap. psql is near-silent on success.
    const CAPTURE_LIMIT = 256 * 1024;
    let stdout = '';
    let stderr = '';
    const capture = (buf, chunk) => (buf.length >= CAPTURE_LIMIT ? buf : buf + chunk);
    let finished = false;
    // Set once a dump read failure starts tearing the child down. While an
    // abort is in flight the promise stays unsettled — it resolves only on a
    // confirmed 'close' or when the bounded escalation gives up.
    let aborting = false;
    let abortDetail = null;
    let abortTimer = null;
    let killRequested = false;
    const finish = (exitCode, extraDetail) => {
      if (finished) return;
      finished = true;
      clearTimeout(timer);
      clearTimeout(abortTimer);
      src.destroy();
      // Only a normal completion ends stdin — that EOF is what psql reads as
      // end-of-script and commits under --single-transaction. An abort never
      // sends it, including when termination could not be confirmed.
      if (!aborting && psql.stdin.writable) psql.stdin.end();
      if (exitCode !== 0) {
        const detail = stripAnsi(stderr || stdout || extraDetail || '').replace(/\s+/g, ' ').trim();
        console.error(`🗄️ psql import exited ${exitCode}: ${detail}`);
      }
      // Keep the triggering detail (read error, spawn failure, timeout) in the
      // diagnostic the caller surfaces — psql is usually killed before it can
      // write anything, so its stderr alone would report a bare failure.
      const stderrText = stripAnsi(stderr);
      const detailText = stripAnsi(extraDetail || '');
      const stderrOut = detailText && !stderrText.includes(detailText)
        ? [stderrText, detailText].filter(Boolean).join('\n')
        : stderrText;
      resolve({ stdout: stripAnsi(stdout), stderr: stderrOut, exitCode });
    };

    const timer = setTimeout(() => {
      psql.kill('SIGKILL');
      finish(1, `import timed out after ${timeout}ms`);
    }, timeout);

    // A dump read failure must NOT end stdin: end-of-script is a successful
    // run to psql, so --single-transaction would commit whatever complete
    // statements already arrived — the export's leading DROPs included
    // (scripts/db.sh --clean). Abort instead: stop pumping, leave stdin open,
    // and terminate the child so its open transaction rolls back with the
    // connection. Settlement then waits on the child's 'close' — bounded by
    // SIGTERM → SIGKILL escalation below. Even if neither signal succeeds,
    // stdin stays open so the partial prefix cannot commit on normal EOF.
    const escalateAbort = () => {
      if (finished || killRequested) return;
      // kill() can synchronously emit 'error'; arm the guard and deadline first.
      killRequested = true;
      clearTimeout(abortTimer);
      abortTimer = setTimeout(() => finish(1, abortDetail), ABORT_KILL_GRACE_MS);
      psql.kill('SIGKILL');
    };
    const abort = (detail) => {
      if (finished || aborting) return;
      aborting = true;
      abortDetail = detail;
      clearTimeout(timer); // the import timeout's bound is superseded by the abort bound
      src.destroy();
      abortTimer = setTimeout(escalateAbort, ABORT_TERM_GRACE_MS);
      psql.kill('SIGTERM');
    };

    psql.stdout.on('data', (d) => { stdout = capture(stdout, d.toString()); });
    psql.stderr.on('data', (d) => { stderr = capture(stderr, d.toString()); });
    psql.on('error', (err) => {
      if (aborting) {
        // A failed kill during teardown — the child may still be running.
        // Escalate and keep waiting on close rather than reporting failure
        // while it can still commit.
        escalateAbort();
        return;
      }
      finish(1, err.message);
    });
    psql.on('close', (code) => {
      if (aborting) finish(1, abortDetail);
      else finish(typeof code === 'number' ? code : 1);
    });
    // psql may exit early (e.g. ON_ERROR_STOP) while we're still writing — the
    // resulting EPIPE would otherwise crash the process.
    psql.stdin.on('error', () => {});

    // Byte-transparent replay pump: buffer latin1 chars, split on '\n',
    // and forward every staged line's original bytes
    // (its trailing '\n' re-appended) to psql's stdin. Backpressure pauses the
    // source; a drain resumes it and re-drains any buffered lines.
    let pending = '';
    const writeLine = (line) => {
      if (!psql.stdin.writable) return true;
      return psql.stdin.write(line + '\n', 'latin1');
    };
    const pump = () => {
      let nl;
      while ((nl = pending.indexOf('\n')) !== -1) {
        const line = pending.slice(0, nl); // excludes '\n', keeps any trailing '\r'
        pending = pending.slice(nl + 1);
        if (!writeLine(line)) {
          src.pause();
          psql.stdin.once('drain', () => {
            if (finished || aborting) return;
            src.resume();
            pump();
          });
          return;
        }
      }
    };

    src.on('data', (chunk) => {
      if (finished || aborting) return; // a queued chunk must not pump after abort
      pending += chunk;
      pump();
    });
    src.on('error', (err) => abort(err.message));
    src.on('end', () => {
      if (finished || aborting) return; // the success EOF must never follow an abort
      // Flush a final line that lacked a trailing newline, then close stdin so
      // psql runs the script and emits 'close'.
      if (pending.length && psql.stdin.writable) { psql.stdin.write(pending, 'latin1'); pending = ''; }
      if (psql.stdin.writable) psql.stdin.end();
    });
  });
}

/**
 * Read a specific backend's PostgreSQL major version by probing it on its own
 * port (native 5432 / Docker 5561 can run different majors, so the connection
 * pool's version isn't authoritative for the export target). `server_version_num`
 * is an integer like 170010 → major = floor(n / 10000). Returns null when the
 * probe fails — the shared resolver then falls back to bare `pg_dump`.
 */
async function probeServerMajor(port, env) {
  const result = await runCmd('psql', [
    '-h', 'localhost', '-p', String(port), '-U', pgUser, '-d', pgDb,
    '-tAc', 'SHOW server_version_num'
  ], 5_000, env);
  const num = parseInt(result.stdout.trim(), 10);
  if (!Number.isFinite(num)) return null;
  return Math.floor(num / 10000);
}

/**
 * Copy data from the active backend to the inactive backend.
 * Native (5432) and Docker (5561) can run simultaneously, so the active
 * backend remains running throughout the operation.
 */
async function syncDatabaseImpl(io) {
  const emit = (event, data) => io?.emit('database:progress', { event, ...data });

  const statusResult = await runDbScript(['status']);
  const currentMode = statusResult.exitCode === 0
    ? statusResult.stdout.match(/Current mode:\s*(docker|native)\b/)?.[1]
    : null;
  if (!currentMode) {
    throw new ServerError('Cannot verify the active database backend. Reconcile database settings and restart before syncing.', { status: 409 });
  }
  const targetMode = currentMode === 'docker' ? 'native' : 'docker';
  const sourcePort = currentMode === 'docker' ? DOCKER_PORT : NATIVE_PORT;
  const targetPort = targetMode === 'docker' ? DOCKER_PORT : NATIVE_PORT;

  // POOL_CONFIG is the connection captured when the running pool was created.
  // Saved mode and mutable environment values cannot establish its identity.
  // Only canonical local endpoints can participate in native/Docker transfer.
  if (!endpointsAlias('localhost', sourcePort, POOL_CONFIG.host, POOL_CONFIG.port)
      || endpointsAlias('localhost', targetPort, POOL_CONFIG.host, POOL_CONFIG.port)) {
    throw new ServerError('Database settings do not match the running connection. Reconcile database settings and restart before syncing.', { status: 409 });
  }

  emit('start', { message: 'Exporting from active database...' });
  // Explicit backend export bypasses db.sh's saved-mode/container selection.
  const { dumpFile } = await exportDatabaseImpl(currentMode);
  console.log(`🗄️ Sync: exported to ${dumpFile}`);

  // Step 2: Ensure target is running and configured
  emit('start', { message: `Checking ${targetMode} is running...` });
  const readyResult = await runCmd('pg_isready', [
    '-h', 'localhost', '-p', targetPort
  ], 5_000, pgEnv(targetPort));
  if (readyResult.exitCode !== 0) {
    emit('error', { message: `${targetMode} database not running on port ${targetPort}. Start it first.` });
    throw new ServerError(`${targetMode} database not running on port ${targetPort}. Start it first.`, { status: 400 });
  }

  // Ensure target has the portos role and database (native pg may lack the role)
  if (targetMode === 'native') {
    emit('start', { message: 'Ensuring native database role and schema exist...' });
    const sysUser = process.env.USER || 'postgres';
    const sysEnv = pgEnv(targetPort);
    // Create role if missing (connect as system superuser)
    await runCmd('psql', ['-h', 'localhost', '-p', targetPort, '-U', sysUser, '-d', 'postgres',
      '-c', `DO $$ BEGIN IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname='${pgEscapeString(pgUser)}') THEN CREATE ROLE ${pgQuoteIdentifier(pgUser)} WITH LOGIN PASSWORD '${pgEscapeString(pgPassword)}' CREATEDB SUPERUSER; END IF; END $$;`
    ], 10_000, sysEnv);
    // Create database if missing
    const dbCheck = await runCmd('psql', ['-h', 'localhost', '-p', targetPort, '-U', sysUser, '-d', 'postgres',
      '-tAc', `SELECT 1 FROM pg_database WHERE datname='${pgEscapeString(pgDb)}'`
    ], 5_000, sysEnv);
    if (!dbCheck.stdout.trim().includes('1')) {
      await runCmd('psql', ['-h', 'localhost', '-p', targetPort, '-U', sysUser, '-d', 'postgres',
        '-c', `CREATE DATABASE ${pgQuoteIdentifier(pgDb)} OWNER ${pgQuoteIdentifier(pgUser)};`
      ], 10_000, sysEnv);
    }
    // Best-effort extensions (non-fatal — dump may handle schema, and pgvector may not be installed)
    await runCmd('psql', ['-h', 'localhost', '-p', targetPort, '-U', sysUser, '-d', pgDb,
      '-c', 'CREATE EXTENSION IF NOT EXISTS vector; CREATE EXTENSION IF NOT EXISTS pgcrypto;'
    ], 10_000, sysEnv);
  }

  // Step 3: Import into target (active backend untouched — different port).
  // Streamed via spawn (no shell) with pg17-only directives stripped in-process,
  // so no user/env value is ever interpolated into a shell string.
  emit('start', { message: `Importing into ${targetMode} (port ${targetPort})...` });
  const importResult = await importDumpFile(dumpFile, targetPort, pgEnv(targetPort));

  if (importResult.exitCode !== 0) {
    emit('error', { message: `Import into ${targetMode} failed` });
    throw new ServerError(`Import into ${targetMode} failed`, { status: 500, context: { details: importResult.stderr || importResult.stdout } });
  }

  emit('complete', { message: `Data synced to ${targetMode}` });
  return { success: true, dumpFile };
}

/**
 * Child environment that pins db.sh to the native backend for one lifecycle
 * command. db.sh chooses start/stop from PGMODE and the endpoint from PGPORT, and
 * both normally describe the ACTIVE backend (inherited from this process or the
 * saved .env), so a native request under Docker would otherwise start/stop Docker.
 * The override is child-only: saved configuration and the running pool are
 * untouched. Refuses to run when the canonical native endpoint is unusable or
 * collides with the Docker endpoint, before any lifecycle effect.
 */
function nativeLifecycleEnv() {
  const port = Number(NATIVE_PORT);
  if (!Number.isInteger(port) || port < 1 || port > 65535
      || endpointsAlias('localhost', NATIVE_PORT, 'localhost', DOCKER_PORT)) {
    throw new ServerError('Cannot resolve a distinct native database endpoint. Nothing was started or stopped.', { status: 500 });
  }
  return { ...pgEnv(NATIVE_PORT), PGMODE: 'native' };
}

/** Start a specific database backend. */
async function startDatabaseImpl(backend) {
  if (backend === 'docker') {
    const result = await runCmd('docker', ['compose', 'up', '-d', 'db'], 60_000);
    return { success: true, output: requireCommandSuccess(result, 'Failed to start Docker database').stdout };
  }

  // Native: db.sh handles brew services / pg_ctl, but picks its operation from
  // the mode it loads. Bind the child to the native backend explicitly.
  const result = await runCmd(bashBinary, [dbScript, 'start'], 30_000, nativeLifecycleEnv());
  return { success: true, output: requireCommandSuccess(result, 'Failed to start native database').stdout };
}

/** Stop a specific database backend. */
async function stopDatabaseImpl(backend) {
  if (backend === 'docker') {
    const result = await runCmd('docker', ['compose', 'stop', 'db'], 30_000);
    return { success: true, output: requireCommandSuccess(result, 'Failed to stop Docker database').stdout };
  }

  // Native stop: never let the active (possibly Docker) mode choose the target.
  const result = await runCmd(bashBinary, [dbScript, 'stop'], 15_000, nativeLifecycleEnv());
  return { success: true, output: requireCommandSuccess(result, 'Failed to stop native database').stdout };
}

/** Resolve the selected Compose service's data before any destructive command. */
async function resolveDockerDataTarget() {
  const listed = requireCommandSuccess(await runCmd('docker', ['compose', 'ps', '-a', '-q', 'db'], 15_000), 'Could not identify the Docker database container');
  const ids = listed.stdout.trim().split(/\s+/).filter(Boolean);
  if (ids.length > 1 || (ids.length === 1 && !/^[a-f\d]{12,64}$/i.test(ids[0]))) {
    throw new ServerError('Cannot identify one Docker database container. No data was destroyed.', { status: 500, code: 'DATABASE_COMMAND_FAILED' });
  }
  const container = ids[0] || null;
  const result = await runCmd('docker', container
    ? ['inspect', '--type', 'container', '--format', '{{json .Mounts}}', container]
    : ['compose', 'config', '--format', 'json'], 15_000, process.env, { redactFailureOutput: true });
  requireCommandSuccess(result, 'Could not identify the Docker database data volume');
  let volume;
  try {
    const data = JSON.parse(result.stdout);
    // A mounted volume is authoritative even if Compose configuration changed.
    // With no container, use Compose's resolved name (including project/name
    // overrides), never a guessed project prefix or a historical alternative.
    const mounts = container ? data : data.services?.db?.volumes;
    if (!Array.isArray(mounts)) throw new Error('Missing mounts');
    const matching = mounts.filter(mount => (container ? mount?.Destination : mount?.target) === '/var/lib/postgresql/data');
    if (matching.length !== 1) throw new Error('Ambiguous data mount');
    const mount = matching[0];
    if ((container ? mount.Type : mount.type) !== 'volume') throw new Error('Not a named volume');
    volume = container ? mount.Name : data.volumes?.[mount.source]?.name;
    if (typeof volume !== 'string' || !/^[a-z\d][a-z\d_.-]*$/i.test(volume)) throw new Error('Invalid volume name');
  } catch {
    throw new ServerError('Cannot identify one named Docker database data volume. No data was destroyed.', { status: 500, code: 'DATABASE_COMMAND_FAILED' });
  }
  return { container, volume };
}

/** Do not delete data until the captured container is provably absent. */
async function requireDockerContainerAbsent(container) {
  const result = await runCmd('docker', ['inspect', '--type', 'container', '--format', '{{.Id}}', container], 15_000);
  if (result.exitCode !== 0 && /no such (?:object|container)/i.test(`${result.stderr} ${result.stdout}`)) return;
  requireCommandSuccess(result, 'Could not verify the Docker database container was removed');
  throw new ServerError('Docker database container still exists after removal. Data was not destroyed.', { status: 500, code: 'DATABASE_COMMAND_FAILED' });
}

/** True when `docker volume inspect` proves the volume is gone; throws on any other failure. */
async function dockerVolumeAbsent(name) {
  const result = await runCmd('docker', ['volume', 'inspect', name], 15_000);
  if (result.exitCode === 0) return false;
  if (/no such volume/i.test(`${result.stderr} ${result.stdout}`)) return true;
  requireCommandSuccess(result, `Could not verify Docker volume ${name} was removed`);
  return false;
}

/** Destroy an inactive database backend's data. */
async function destroyDatabaseImpl(backend) {
  // Safety: authorize deletion only from a successful, recognized mode probe.
  // parseDbMode intentionally has a Docker fallback for non-destructive callers,
  // so destruction validates the probe independently and fails closed.
  const statusResult = await runDbScript(['status']);
  const currentMode = statusResult.exitCode === 0
    ? statusResult.stdout.match(/Current mode:\s*(docker|native)\b/)?.[1]
    : null;
  if (!currentMode) {
    throw new ServerError('Cannot verify the active database backend. No data was destroyed.', { status: 500 });
  }
  if (backend === currentMode) {
    throw new ServerError('Cannot destroy the active backend. Switch to the other backend first.', { status: 400 });
  }

  // The requested backend owns the destructive target. PGPORT/PGHOST describe
  // the active pool and must never redirect an inactive-backend deletion. Refuse
  // the operation if the independently-resolved target still aliases that pool.
  const targetPort = backend === 'native' ? NATIVE_PORT : DOCKER_PORT;
  const activePort = process.env.PGPORT || String(resolvePostgresPort(currentMode));
  const activeHost = process.env.PGHOST || 'localhost';
  if (endpointsAlias('localhost', targetPort, activeHost, activePort)) {
    throw new ServerError('Cannot destroy a database endpoint used by the active backend.', { status: 400 });
  }

  if (backend === 'docker') {
    const { container, volume } = await resolveDockerDataTarget();
    if (container) {
      requireCommandSuccess(await runCmd('docker', ['stop', container], 15_000), 'Failed to stop Docker database before deletion');
      // Do not force-remove a container that restarted after stop. Captured IDs
      // also keep a Compose configuration change from selecting another service.
      requireCommandSuccess(await runCmd('docker', ['rm', container], 15_000), 'Failed to remove Docker database container');
      await requireDockerContainerAbsent(container);
    }
    const result = requireCommandSuccess(await runCmd('docker', ['volume', 'rm', '-f', volume], 15_000), `Failed to remove Docker volume ${volume}`);
    if (!(await dockerVolumeAbsent(volume))) {
      throw new ServerError(`Docker volume ${volume} still exists after removal. Data was not fully destroyed.`, { status: 500, code: 'DATABASE_COMMAND_FAILED' });
    }
    return { success: true, output: result.stdout };
  }

  // Native: drop the portos database (system pg stays running)
  const sysUser = process.env.USER || 'portos';
  const result = await runCmd('psql', [
    '-h', 'localhost', '-p', NATIVE_PORT, '-U', sysUser, '-d', 'postgres',
    '-c', `DROP DATABASE IF EXISTS ${pgQuoteIdentifier(pgDb)}`
  ], 15_000);
  return { success: true, output: requireCommandSuccess(result, 'Failed to delete native database').stdout };
}

/** Install and configure native PostgreSQL. */
async function setupNativeDatabaseImpl(io) {
  emitProgress(io, 'start', 'Setting up native PostgreSQL...');

  const result = await runDbScript(['setup-native']);
  if (result.exitCode !== 0) {
    emitProgress(io, 'error', 'Native setup failed');
    throw new ServerError('Native PostgreSQL setup failed', {
      status: 500,
      context: { details: result.stderr || result.stdout }
    });
  }

  emitProgress(io, 'complete', 'Native PostgreSQL ready');
  return { success: true, output: result.stdout };
}

/** Export a specific database backend, or the active backend when omitted. */
async function exportDatabaseImpl(backend) {
  const label = `backup-${Date.now()}`;

  if (backend) {
    // Export from a specific backend by connecting directly to its port
    const port = backend === 'docker' ? DOCKER_PORT : NATIVE_PORT;
    const env = pgEnv(port);
    const readyResult = await runCmd('pg_isready', [
      '-h', 'localhost', '-p', port, '-U', pgUser
    ], 5_000, env);
    if (readyResult.exitCode !== 0) {
      throw new ServerError(`${backend} database not running on port ${port}`, { status: 400 });
    }
    const dumpDir = join(rootDir, 'data', 'db-dumps');
    // Use Node's fs (not `mkdir -p`): on Windows `mkdir` is a cmd.exe builtin,
    // not a spawnable executable, and `-p` isn't a valid flag — execFile would
    // fail with ENOENT. `recursive: true` is the cross-platform equivalent.
    mkdirSync(dumpDir, { recursive: true });
    const dumpFile = join(dumpDir, `portos-${backend}-${label}.sql`);
    // Use -f flag to write output directly — no shell redirect needed, avoids shell injection.
    // Resolve a pg_dump whose major is >= the TARGET backend's server (probed
    // against this port, not the pool's 5432) via the shared resolver, which
    // also honors PORTOS_PGDUMP. Falls back to bare `pg_dump` when the probe
    // can't determine the version. (Shared with backup.js — server/lib/pgTools.js.)
    const targetMajor = await probeServerMajor(port, env);
    const { binary: pgDumpBin, satisfies } = await resolvePgDumpBinary(targetMajor);
    if (!satisfies) {
      console.warn(`🗄️ No installed pg_dump satisfies ${backend} server major ${targetMajor} (using ${pgDumpBin})`);
    }
    const result = await runCmd(pgDumpBin, [
      '-h', 'localhost', '-p', String(port), '-U', pgUser, '-d', pgDb,
      '--no-owner', '--no-privileges', '--no-comments', '--if-exists', '--clean', '-f', dumpFile
    ], 120_000, env);
    if (result.exitCode !== 0) {
      throw new ServerError('Export failed', { status: 500, context: { details: result.stderr || result.stdout } });
    }
    return { success: true, dumpFile };
  }

  // Default: export from active backend via db.sh
  const result = await runDbScript(['export']);
  if (result.exitCode !== 0) {
    throw new ServerError('Export failed', {
      status: 500,
      context: { details: result.stderr || result.stdout }
    });
  }
  const dumpLines = result.stdout.trim().split('\n');
  const dumpFile = dumpLines[dumpLines.length - 1]?.trim();
  return { success: true, dumpFile, output: result.stdout };
}

/** Fix stale PostgreSQL pid files. */
async function fixDatabaseImpl() {
  const result = await runDbScript(['fix']);
  return {
    success: result.exitCode === 0,
    output: result.stdout,
    error: result.exitCode !== 0 ? (result.stderr || result.stdout) : undefined
  };
}

export const switchDatabase = (options, io) => withDatabaseOperation(() => switchDatabaseImpl(options, io));

export const syncDatabase = (io) => withDatabaseOperation(() => syncDatabaseImpl(io));

export const destroyDatabase = (backend) => withDatabaseOperation(() => destroyDatabaseImpl(backend));

export const startDatabase = (backend) => withDatabaseOperation(() => startDatabaseImpl(backend));

export const stopDatabase = (backend) => withDatabaseOperation(() => stopDatabaseImpl(backend));

export const setupNativeDatabase = (io) => withDatabaseOperation(() => setupNativeDatabaseImpl(io));

export const fixDatabase = () => withDatabaseOperation(() => fixDatabaseImpl());

export const exportDatabase = (backend) => withDatabaseOperation(() => exportDatabaseImpl(backend));