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

15.2 KB
Raw Blame
#!/usr/bin/env node

/**
 * Database Setup Script
 *
 * Ensures PostgreSQL is available — either via Docker Compose (docker mode)
 * or the system PostgreSQL (native mode).
 *
 * Called by: npm run setup, npm run update, npm start, npm run dev
 */

import { execFileSync } from 'child_process';
import { createRequire } from 'module';
import { dirname, join } from 'path';
import { fileURLToPath } from 'url';
import { parseEnvFile } from './lib/envFile.js';
import databaseSettings from './lib/databaseSettings.cjs';
import { resolveBashBinary } from '../server/lib/bashResolver.js';

const __dirname = dirname(fileURLToPath(import.meta.url));
const rootDir = join(__dirname, '..');

const envFile = parseEnvFile(join(rootDir, '.env'));
const settings = databaseSettings.resolveDatabaseSettings({ env: process.env, saved: envFile });
const PG_USER = settings.user;
const PG_DATABASE = settings.database;
const PG_PASSWORD = settings.password;
const PG_HOST = settings.host;
const PG_PORT_NATIVE = settings.nativePort;
const PG_PORT_DOCKER = settings.dockerPort;

// Environment to pass to `db.sh` and other psql-wrapping subprocesses, so
// values configured in .env (but not exported into the shell) reach the
// child process. Without this, db.sh setup-native would provision the
// default `portos`/`portos` while isPortOSDbReady() probes with the
// customized creds — leaving setup looping forever.
//
// Inherited libpq routing variables are dropped: PGHOSTADDR overrides the
// network destination of the explicit PGHOST, PGSERVICE/PGSERVICEFILE can name
// another endpoint, and PGOPTIONS alters session settings. A shell left over
// from another PostgreSQL workflow must not redirect the readiness probe or
// role provisioning to a different cluster. TLS/auth variables stay. Keep in
// step with PG_ROUTING_VARS in db.sh.
const ROUTING_ENV = ['PGHOSTADDR', 'PGSERVICE', 'PGSERVICEFILE', 'PGOPTIONS'];
const inheritedEnv = Object.fromEntries(
  Object.entries(process.env).filter(([name]) => !ROUTING_ENV.includes(name))
);
const PG_CHILD_ENV = {
  ...inheritedEnv,
  PGUSER: PG_USER,
  PGDATABASE: PG_DATABASE,
  PGPASSWORD: PG_PASSWORD,
  PGHOST: PG_HOST,
  PGPORT: String(PG_PORT_NATIVE)
};

// Environment for every `docker compose` subprocess. Compose interpolates
// docker-compose.yml from its own .env grammar (inline comments, `$` expansion),
// which differs from the literal parser PortOS and PM2 share. Process
// environment outranks Compose's .env, so forwarding the values resolved above
// makes the container provision exactly what PortOS connects with. Child
// environment only — the password never reaches argv or logs.
const COMPOSE_ENV = {
  ...process.env,
  PGUSER: PG_USER,
  PGDATABASE: PG_DATABASE,
  PGPASSWORD: PG_PASSWORD,
  PGPORT_DOCKER: String(PG_PORT_DOCKER)
};

function getMode() {
  return settings.mode;
}

function hasDocker() {
  try {
    execFileSync('docker', ['--version'], { stdio: 'pipe' });
    return true;
  } catch {
    return false;
  }
}

function isDockerRunning() {
  try {
    execFileSync('docker', ['info'], { stdio: 'pipe' });
    return true;
  } catch {
    return false;
  }
}

function hasCompose() {
  try {
    execFileSync('docker', ['compose', 'version'], { stdio: 'pipe' });
    return true;
  } catch {
    return false;
  }
}

// Domain-level readiness check: the configured PG_USER role can authenticate
// to PG_DATABASE AND the `memories` table from init-db.sql exists in the
// public schema. This is what `db.sh setup-native` produces. The probe
// itself is a single cheap psql round-trip; passing it lets us skip the
// full setup-native path (brew checks, ALTER USER, schema reapply) on
// every `npm start`/`npm run dev`, which would otherwise reset the role's
// password+SUPERUSER privileges on every invocation.
//
// `psql -tAc` exits 0 even when the SELECT returns no rows, so we capture
// stdout and require the literal "1" — without this, an empty result (db
// exists but schema not yet applied) would falsely report "ready" and the
// app would boot against an unmigrated database. `-X` skips the user's
// .psqlrc so a custom prompt or echo setting can't pollute stdout.
function isPortOSDbReady(port = PG_PORT_NATIVE) {
  try {
    const output = execFileSync(
      'psql',
      [
        '-X',
        '-h', PG_HOST,
        '-p', String(port),
        '-U', PG_USER,
        '-d', PG_DATABASE,
        '-tAc',
        "SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'memories' LIMIT 1"
      ],
      { stdio: 'pipe', env: PG_CHILD_ENV }
    ).toString();
    return output.trim() === '1';
  } catch {
    return false;
  }
}

// Whether the Docker container's base schema exists. We probe `memories` (the
// first, always-present base table) — NOT a newest fresh-install table: an
// install whose volume was initialized before a later table (e.g.
// writers_room_exercises, #1017) existed is a valid UPGRADEABLE install, and
// `npm start` runs this BEFORE the server's idempotent boot-time ensureSchema()
// that adds the missing tables. Requiring the newest table here would wedge
// every pre-existing Docker install (the probe would never go true). The
// half-applied-init race that motivated checking the schema at all is handled
// by the TCP readiness gate in waitForHealth() below — the postgres image runs
// docker-entrypoint-initdb.d against a SOCKET-ONLY temp server and only opens
// the TCP listener after init finishes, so a TCP probe is false mid-init.
// `psql -tAc` exits 0 even on an empty result, so we require the literal "1".
function isDockerSchemaReady() {
  try {
    const output = execFileSync(
      'docker',
      ['compose', 'exec', '-T', 'db', 'psql', '-X', '-U', PG_USER, '-d', PG_DATABASE, '-tAc',
        "SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'memories' LIMIT 1"],
      { stdio: 'pipe', cwd: rootDir, env: COMPOSE_ENV }
    ).toString();
    return output.trim() === '1';
  } catch {
    return false;
  }
}

// Wait for PostgreSQL to accept TCP connections AND for the base schema to be
// in place. Both must hold before we report success, since PortOS now
// fail-fasts at boot when the schema is missing. pg_isready is forced over TCP
// (-h 127.0.0.1): during first-time init the entrypoint runs the schema load
// against a socket-only temp server (listen_addresses=''), so a default
// socket-based pg_isready would pass mid-init and let `npm start` race a
// half-applied schema. The TCP listener opens only after init completes, so a
// TCP probe cleanly distinguishes mid-init from done — for both fresh and
// upgraded volumes (an upgraded volume skips init entirely and is TCP-ready at
// once, then ensureSchema() backfills any newer tables at boot).
function waitForHealth(maxAttempts = 30) {
  for (let i = 0; i < maxAttempts; i++) {
    try {
      execFileSync('docker', ['compose', 'exec', '-T', 'db', 'pg_isready', '-h', '127.0.0.1', '-U', PG_USER], {
        stdio: 'pipe',
        cwd: rootDir,
        env: COMPOSE_ENV
      });
      if (isDockerSchemaReady()) return true;
    } catch {
      // not accepting TCP connections yet (mid-init or starting) — wait below
    }
    if (i < maxAttempts - 1) {
      Atomics.wait(new Int32Array(new SharedArrayBuffer(4)), 0, 0, 1000);
    }
  }
  return false;
}

// Authenticated query through the exact host endpoint the server and PM2 use
// (PGHOST + PGPORT_DOCKER + PGUSER/PGDATABASE/PGPASSWORD). Container-local
// probes (pg_isready ignores credentials; the in-container psql uses the local
// socket) cannot catch a persisted role whose password no longer matches the
// configured one, or a host endpoint the application cannot reach. `pg` is
// resolved from the server workspace so no host psql is required. Returns null
// on success or a failure message (never containing the password).
async function verifyDockerHostConnection() {
  let Client;
  try {
    ({ Client } = createRequire(join(rootDir, 'server', 'package.json'))('pg'));
  } catch (err) {
    return `the pg driver is unavailable (${err.message.split('\n')[0]}) — run: npm run install:all`;
  }
  const client = new Client({
    host: PG_HOST,
    port: PG_PORT_DOCKER,
    user: PG_USER,
    database: PG_DATABASE,
    password: PG_PASSWORD,
    connectionTimeoutMillis: 5000,
    query_timeout: 5000
  });
  try {
    await client.connect();
    const { rows } = await client.query(
      "SELECT 1 AS ok FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'memories' LIMIT 1"
    );
    return rows.length === 1 ? null : 'the base schema (memories table) is missing on that endpoint';
  } catch (err) {
    return err.message;
  } finally {
    await client.end().catch(() => {});
  }
}

// Platform-specific Docker install/start hints
function getDockerHints(issue) {
  const platform = process.platform;
  const hints = { install: '', start: '' };

  if (platform === 'darwin') {
    hints.install = 'Install Docker Desktop: https://www.docker.com/products/docker-desktop/';
    hints.start = 'Open Docker Desktop or run: open -a Docker';
  } else if (platform === 'win32') {
    hints.install = 'Install Docker Desktop: https://www.docker.com/products/docker-desktop/';
    hints.start = 'Start Docker Desktop from the Start menu';
  } else {
    hints.install = 'Install Docker Engine: https://docs.docker.com/engine/install/';
    hints.start = 'Start Docker: sudo systemctl start docker';
  }

  return issue === 'not_installed' ? hints.install : hints.start;
}

// Attempt full native PostgreSQL setup via db.sh setup-native. Idempotent:
// db.sh setup-native re-checks each step (brew install, role, db, extensions,
// schema). Verifies success at the *domain* level — the role can auth and the
// schema is in place — not just that the port is listening, since the whole
// point of this PR is that port-listening isn't proof of usable PortOS state.
function setupNativePostgres() {
  const dbScript = join(rootDir, 'scripts', 'db.sh');
  try {
    console.log('🍺 Running native PostgreSQL setup...');
    // Pass resolved PG_* settings via env so db.sh provisions the same
    // role/db/port that isPortOSDbReady() probes. db.sh itself doesn't
    // source .env — without this, customized creds in .env would mismatch.
    execFileSync(resolveBashBinary(), [dbScript, 'setup-native'], {
      stdio: 'inherit',
      cwd: rootDir,
      env: PG_CHILD_ENV
    });
    if (isPortOSDbReady()) {
      console.log(`✅ PortOS database ready on port ${PG_PORT_NATIVE}`);
      return true;
    }
  } catch (err) {
    console.error(`⚠️  Native setup error: ${err.message}`);
  }
  return false;
}

// Exit with error when native PostgreSQL setup fails
function exitNativeSetupFailed() {
  if (process.platform === 'darwin') {
    console.error('❌ Native PostgreSQL setup failed — try manually: brew install postgresql@17 && brew services start postgresql@17');
  } else {
    console.error('❌ Native PostgreSQL setup failed — install and start PostgreSQL');
  }
  console.log('   Then re-run: npm run setup');
  process.exit(1);
}

function handleDockerUnavailable(message, issue) {
  console.error(`❌ ${message}`);
  console.error(`   ${getDockerHints(issue)}`);
  console.error('   Restore Docker and re-run: npm run setup:db');
  console.error('   Keeping the selected database unchanged. Setup never switches backends.');
  console.error('   For a fresh native install, select PGMODE=native in .env before setup.');
  console.error('   Existing installs require coordinated maintenance cutover; see docs/STORAGE.md.');
  process.exit(1);
}

const mode = getMode();

if (mode === 'file') {
  // Advanced/unsupported escape hatch: PGMODE=file is honored ONLY when a user
  // has explicitly set it in .env. It is not a normal setup choice — PostgreSQL
  // is a mandatory dependency for production installs and the creative catalog
  // has no file-backed equivalent. Kept for development/tests.
  console.error('🚫 PGMODE=file is UNSUPPORTED for production — PostgreSQL is required.');
  console.error('   File-based storage has no creative-catalog or vector-search support.');
  console.log('   For a fresh/development install, set PGMODE=native or PGMODE=docker in the');
  console.log('   repository-root .env, preserving other settings. Unset a conflicting exported');
  console.log('   PGMODE, then re-run: npm run setup:db');
  console.log('   Existing PostgreSQL installs must retain the backend holding their records;');
  console.log('   an intentional move requires coordinated maintenance cutover.');
  console.log('   See docs/STORAGE.md#setup-path-npm-run-setupdb.');
  process.exit(0);
}

console.log(`🗄️  Setting up PostgreSQL (mode: ${mode})...`);

if (mode === 'native') {
  // Fast path: portos role can already authenticate to portos db and the
  // schema is in place. Skip setup-native to avoid re-ALTERing credentials
  // and the brew/psql startup-time hit on every `npm start`.
  if (isPortOSDbReady()) {
    console.log(`✅ PortOS database ready on port ${PG_PORT_NATIVE}`);
    process.exit(0);
  }
  // Otherwise (fresh checkout, missing role, missing schema, wrong password)
  // run the full bootstrap.
  if (setupNativePostgres()) {
    process.exit(0);
  }

  exitNativeSetupFailed();
}

// Docker mode
if (!hasDocker()) {
  handleDockerUnavailable('Docker not found — database setup failed', 'not_installed');
}

if (!isDockerRunning()) {
  handleDockerUnavailable('Docker daemon not running — database setup failed', 'not_running');
}

if (!hasCompose()) {
  handleDockerUnavailable('docker compose not available — database setup failed', 'not_installed');
}

// Compose reconciles changed bindings/ports for existing containers while
// preserving the named data volume, and reuses unchanged running containers.
// Readiness inside a container cannot prove its host publication is current.
console.log('🐳 Reconciling PostgreSQL container configuration...');
try {
  execFileSync('docker', ['compose', 'up', '-d', 'db'], {
    stdio: 'inherit',
    cwd: rootDir,
    env: COMPOSE_ENV
  });
} catch (err) {
  console.error(`❌ Failed to reconcile PostgreSQL container: ${err.message}`);
  console.error('   Keep the selected backend; check Docker and run: docker compose logs db');
  process.exit(1);
}

// Wait for health
console.log('⏳ Waiting for PostgreSQL to be ready...');
if (waitForHealth()) {
  const hostFailure = await verifyDockerHostConnection();
  if (hostFailure) {
    console.error(`❌ PostgreSQL container is up but ${PG_USER}@${PG_HOST}:${PG_PORT_DOCKER}/${PG_DATABASE} is not usable: ${hostFailure}`);
    console.error('   The container-local checks passed, so the configured PGPASSWORD/PGHOST/PGPORT_DOCKER');
    console.error('   likely differ from the persisted role or host publication. Setup leaves the volume and role untouched.');
    console.error('   Align .env with the existing role (see .env.example) and re-run: npm run setup:db');
    process.exit(1);
  }
  console.log(`✅ PostgreSQL ready on port ${PG_PORT_DOCKER}`);
} else {
  // PG is mandatory and boot fail-fasts — a started-but-unresponsive container
  // must fail setup, not warn-and-continue, so the &&-chained `npm start` halts
  // here instead of crash-looping under PM2 against an unready DB.
  console.error('❌ PostgreSQL started but never became ready');
  console.error('   Check status: docker compose logs db');
  process.exit(1);
}