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

32.1 KB
Raw Blame
#!/usr/bin/env bash
#
# PortOS Database Manager
#
# Manage PostgreSQL via Docker or native (system) installation.
# Native mode reuses an existing system PostgreSQL (e.g., Homebrew) on port 5432
# rather than running a separate instance. Docker mode runs a container on port 5561.
#
# Usage:
#   scripts/db.sh <command>
#
# Commands:
#   status       Show current database status
#   start        Start the database (auto-detects mode)
#   stop         Stop the database
#   fix          Fix common issues (stale pid files, etc.)
#   setup-native Install and configure native PostgreSQL via Homebrew
#                (provisions the PGHOST/PGPORT you select; never another cluster)
#   use-docker   Unavailable pending coordinated offline cutover
#   use-native   Unavailable pending coordinated offline cutover
#   migrate      Unavailable pending coordinated offline cutover
#   export       Export database to a SQL dump file
#   import       Import a SQL dump file into the database
#   logs         Show database logs

set -euo pipefail

SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
ROOT_DIR="$(cd "$SCRIPT_DIR/.." && pwd)"
# Read the same .env grammar as setup/PM2, without sourcing or evaluating it.
# NUL-delimited values preserve password whitespace and shell metacharacters.
# The completion marker makes a missing Node/parser or truncated pipe fail closed
# before any database command, instead of silently targeting the defaults.
read_db_config() {
  node --input-type=commonjs - "$ROOT_DIR" "$1" <<'NODE'
const { join } = require('node:path');
const { parseEnvFile } = require(join(process.argv[2], 'scripts/lib/envFile.cjs'));
const saved = parseEnvFile(join(process.argv[2], '.env'));
const { resolveDatabaseSettings, parsePort } = require(join(process.argv[2], 'scripts/lib/databaseSettings.cjs'));
const resolved = resolveDatabaseSettings({ saved });
const nativeSetup = process.argv[3] === 'setup-native';
// Ordinary CLI commands retain an explicit exported endpoint override, even in
// Docker mode (offline maintenance uses this to address a selected cluster).
const selectedPort = nativeSetup ? resolved.selectedNativePort : parsePort(process.env.PGPORT);
const settings = {
  PGMODE: resolved.mode, PGUSER: resolved.user,
  PGDATABASE: resolved.database, PGPASSWORD: resolved.password,
  PGHOST: resolved.host, SELECTED_PGHOST: resolved.selectedHost,
  PGPORT: String(selectedPort ?? (nativeSetup ? resolved.nativePort : resolved.port)),
  SELECTED_PGPORT: selectedPort === undefined ? '' : String(selectedPort),
  PGPORT_DOCKER: String(resolved.dockerPort),
};
process.stdout.write(Object.entries({ ...settings, complete: '1' })
  .flat().join('\0') + '\0');
NODE
}
_DB_CONFIG_COMPLETE=false
while IFS= read -r -d '' key && IFS= read -r -d '' value; do
  case "$key" in
    PGMODE|PGUSER|PGDATABASE|PGPASSWORD|PGHOST|PGPORT|PGPORT_DOCKER|SELECTED_PGHOST|SELECTED_PGPORT)
      printf -v "$key" '%s' "$value" ;;
    complete) _DB_CONFIG_COMPLETE=true ;;
  esac
done < <(read_db_config "${1:-}")

if [ "$_DB_CONFIG_COMPLETE" != true ]; then
  echo "❌ Could not load database configuration; no database command was run" >&2
  exit 1
fi
unset key value _DB_CONFIG_COMPLETE
# Compose interpolates docker-compose.yml with its own .env grammar (inline
# comments, `$` expansion), which differs from the literal parser above. Process
# environment outranks Compose's .env, so exporting the resolved values makes
# every `docker compose` call below provision exactly what PortOS connects with.
# Exported to child environments only — never argv or logs.
export PGUSER PGDATABASE PGPASSWORD PGPORT_DOCKER
# The maintenance coordinator names its install's dump directory explicitly
# (its data root can differ from this checkout); otherwise use this checkout.
DUMP_DIR="${PORTOS_DUMP_DIR:-$ROOT_DIR/data/db-dumps}"
ENV_FILE="$ROOT_DIR/.env"

# Colors
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m'

log()  { echo -e "${GREEN}✅ $1${NC}"; }
warn() { echo -e "${YELLOW}⚠️  $1${NC}"; }
err()  { echo -e "${RED}❌ $1${NC}"; }
info() { echo -e "${BLUE}🗄️  $1${NC}"; }

# Inherited libpq variables that choose WHERE a client connects or how the
# session behaves, independent of the -h/-p/-U/-d arguments: PGHOSTADDR
# overrides -h's network destination, PGSERVICE/PGSERVICEFILE can supply another
# endpoint, and PGOPTIONS alters session settings (search_path). Keep this list
# in step with ROUTING_ENV in scripts/setup-db.js. TLS and authentication
# variables (PGSSLMODE, PGSSLROOTCERT, PGPASSFILE, ...) are deliberately NOT
# listed: they are supported operator configuration, not endpoint identity.
PG_ROUTING_VARS="PGHOSTADDR PGSERVICE PGSERVICEFILE PGOPTIONS"

# Run a host libpq client (psql, pg_dump, pg_isready) with those variables
# removed from ITS environment only; the operator's shell is untouched. Every
# call site passes the resolved host/port/user/database as explicit arguments.
pg_bound() (
  local name
  for name in $PG_ROUTING_VARS; do
    unset "$name"
  done
  "$@"
)

get_mode() {
  printf '%s\n' "$PGMODE"
}

# Container-local tools are equivalent only for the configured local Docker
# endpoint. A shell host/port override must not silently export another DB.
uses_local_docker_endpoint() {
  [ "$PGMODE" = docker ] && [ "$PGPORT" = "$PGPORT_DOCKER" ] || return 1
  case "$PGHOST" in localhost|127.0.0.1|::1) return 0 ;; *) return 1 ;; esac
}

EXPLICIT_ENDPOINT=false

# A coordinator must bind each transfer to its recorded endpoint, not saved
# mode, inherited PGPORT, or whichever Docker container happens to be running.
# Passwords remain in the environment, never command arguments.
set_explicit_endpoint() {
  if [ "$#" -ne 4 ] || [ -z "$1" ] || [ "${#1}" -gt 255 ] ||
      [[ "$1" =~ [[:space:],=] ]] || [[ ! "$2" =~ ^[0-9]{1,5}$ ]] ||
      [ "$((10#$2))" -lt 1 ] || [ "$((10#$2))" -gt 65535 ] ||
      [ -z "$3" ] || [ "${#3}" -gt 63 ] ||
      [ -z "$4" ] || [ "${#4}" -gt 63 ] || [[ "$4" == *"="* ]] ||
      [[ "$4" == postgres://* ]] || [[ "$4" == postgresql://* ]]; then
    err "Invalid explicit endpoint: expected host, port (1-65535), user, and database name" >&2
    return 1
  fi
  PGHOST="$1"
  PGPORT="$2"
  PGUSER="$3"
  PGDATABASE="$4"
  EXPLICIT_ENDPOINT=true
}

cmd_transfer() {
  local action="$1"
  shift
  if [ "${1:-}" = "--endpoint" ]; then
    if [ "$#" -lt 5 ]; then
      err "Usage: $action --endpoint <host> <port> <user> <database> [label|file]" >&2
      return 1
    fi
    set_explicit_endpoint "$2" "$3" "$4" "$5" || return 1
    shift 5
  fi
  if [ "$#" -gt 1 ] || { [ "$action" = "import" ] && [ "$#" -ne 1 ]; }; then
    err "Expected one import file or an optional export label" >&2
    return 1
  fi
  "cmd_$action" "${1:-}"
}

# Check if Docker PostgreSQL is running
docker_running() {
  docker ps --filter name=portos-db --format '{{.Status}}' 2>/dev/null | grep -qi "up"
}

# Verify Docker and Compose plugin are available
require_docker_compose() {
  if ! command -v docker >/dev/null 2>&1; then
    err "Docker not installed"
    exit 1
  fi
  if ! docker info >/dev/null 2>&1; then
    err "Docker daemon is not running. Start Docker Desktop or the Docker service."
    exit 1
  fi
  if ! docker compose version >/dev/null 2>&1; then
    err "Docker Compose plugin not available. Install it: https://docs.docker.com/compose/install/"
    exit 1
  fi
}

# Check if native PostgreSQL is accepting connections on the expected port
native_running() {
  PGPASSWORD="$PGPASSWORD" pg_bound pg_isready -h "$PGHOST" -p "$PGPORT" -U "$PGUSER" >/dev/null 2>&1
}

# Auto-detect Homebrew PostgreSQL 17 on macOS and add to PATH
# Check both arm64 (/opt/homebrew) and Intel (/usr/local) prefixes
if [ "$(uname)" = "Darwin" ]; then
  for _prefix in /opt/homebrew/opt/postgresql@17 /usr/local/opt/postgresql@17; do
    if [ -x "$_prefix/bin/psql" ]; then
      export PATH="$_prefix/bin:$PATH"
      break
    fi
  done
fi

# Check if native PostgreSQL is installed
has_native_pg() {
  command -v psql >/dev/null 2>&1
}

# Detect an already-running system PostgreSQL and its port
detect_system_pg() {
  # Check standard port 5432 first
  if pg_bound pg_isready -h localhost -p 5432 >/dev/null 2>&1; then
    echo "5432"
    return 0
  fi
  # Check if pg_ctl reports a running server
  if command -v pg_ctl >/dev/null 2>&1; then
    local datadir=""
    # Try Homebrew default data dir
    if [ "$(uname)" = "Darwin" ] && command -v brew >/dev/null 2>&1; then
      datadir="$(brew --prefix)/var/postgresql@17"
      if [ ! -d "$datadir" ]; then
        datadir="$(brew --prefix)/var/postgres"
      fi
    fi
    if [ -n "$datadir" ] && [ -d "$datadir" ] && pg_ctl -D "$datadir" status >/dev/null 2>&1; then
      # Parse port from postgresql.conf
      local port
      port=$(grep -E '^port\s*=' "$datadir/postgresql.conf" 2>/dev/null | sed 's/.*=\s*//' | tr -d '[:space:]' || echo "5432")
      echo "${port:-5432}"
      return 0
    fi
  fi
  return 1
}

# Status command
cmd_status() {
  local mode
  mode=$(get_mode)
  info "Current mode: $mode"
  info "Port: $PGPORT"

  echo ""
  echo "Docker:"
  if ! command -v docker >/dev/null 2>&1; then
    warn "  Docker not installed"
  elif ! docker info >/dev/null 2>&1; then
    warn "  Docker daemon is not running"
  elif docker ps --filter name=portos-db --format '{{.Status}}' 2>/dev/null | grep -qi "up"; then
    log "  Container portos-db is running"
  else
    warn "  Container portos-db is not running"
  fi

  echo ""
  echo "Native:"
  if has_native_pg; then
    local sys_port
    if sys_port=$(detect_system_pg); then
      log "  System PostgreSQL is running on port $sys_port"
      # Check if portos database exists
      if PGPASSWORD="$PGPASSWORD" pg_bound psql -h "$PGHOST" -p "$sys_port" -U "$PGUSER" -d "$PGDATABASE" -c "SELECT 1" >/dev/null 2>&1; then
        log "  PortOS database exists"
      else
        warn "  PortOS database/user not configured (run: scripts/db.sh setup-native)"
      fi
    else
      warn "  Native PostgreSQL installed but not running"
    fi
  else
    warn "  Native PostgreSQL not installed"
  fi

  echo ""
  echo "Connectivity:"
  if run_psql -c "SELECT 1" >/dev/null 2>&1; then
    log "  Database is accepting connections on port $PGPORT"
    local count
    count=$(run_psql -tAc "SELECT count(*) FROM memories" 2>/dev/null || echo "N/A")
    info "  Memories table has $count rows"
  else
    warn "  Cannot connect to database on port $PGPORT"
  fi
}

# Start command
cmd_start() {
  local mode
  mode=$(get_mode)

  if [ "$mode" = "native" ]; then
    start_native
  else
    start_docker
  fi
}

start_docker() {
  info "Starting Docker PostgreSQL..."

  require_docker_compose

  if docker_running; then
    log "Already running"
    return
  fi

  cd "$ROOT_DIR"
  docker compose up -d db
  info "Waiting for PostgreSQL..."

  for i in $(seq 1 30); do
    # -h 127.0.0.1 forces TCP: during first-time init the entrypoint runs a
    # socket-only temp server, so a default (socket) probe passes too early.
    # Also require the base schema (01-init.sql) so dependents don't race it.
    if docker compose exec -T db pg_isready -h 127.0.0.1 -U "$PGUSER" >/dev/null 2>&1 &&
       [ "$(docker compose exec -T db psql -X -h 127.0.0.1 -U "$PGUSER" -d "$PGDATABASE" -tAc "SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'memories' LIMIT 1" 2>/dev/null | tr -d '[:space:]')" = "1" ]; then
      log "PostgreSQL ready on port $PGPORT"
      return
    fi
    sleep 1
  done

  # Check for stale pid issue (one auto-fix attempt only)
  if [ "${_DB_FIX_ATTEMPTED:-}" != "1" ] && docker logs portos-db --tail 5 2>&1 | grep -q "bogus data in lock file"; then
    warn "Stale postmaster.pid detected — running fix..."
    export _DB_FIX_ATTEMPTED=1
    cmd_fix
    start_docker
    return
  fi

  err "PostgreSQL did not become ready in 30s"
  echo "  Check logs: docker compose logs db"
  exit 1
}

start_native() {
  info "Starting native PostgreSQL..."

  if ! has_native_pg; then
    err "Native PostgreSQL not installed. Run: scripts/db.sh setup-native"
    exit 1
  fi

  # Check if system PostgreSQL is already running and accepting connections
  if native_running; then
    log "Native PostgreSQL already running on port $PGPORT"
    return
  fi

  # Try to start via Homebrew services (macOS)
  if [ "$(uname)" = "Darwin" ] && command -v brew >/dev/null 2>&1; then
    if brew services list 2>/dev/null | grep -q "postgresql@17"; then
      info "Starting PostgreSQL via Homebrew services..."
      brew services start postgresql@17 2>/dev/null || true
      for i in $(seq 1 15); do
        if pg_bound pg_isready -h "$PGHOST" -p "$PGPORT" >/dev/null 2>&1; then
          log "Native PostgreSQL ready on port $PGPORT"
          return
        fi
        sleep 1
      done
    fi
  fi

  # Try pg_ctl with Homebrew data directory
  if command -v pg_ctl >/dev/null 2>&1; then
    local datadir=""
    if [ "$(uname)" = "Darwin" ] && command -v brew >/dev/null 2>&1; then
      datadir="$(brew --prefix)/var/postgresql@17"
      if [ ! -d "$datadir" ]; then
        datadir="$(brew --prefix)/var/postgres"
      fi
    fi
    if [ -n "$datadir" ] && [ -d "$datadir" ]; then
      pg_ctl -D "$datadir" -l "$datadir/server.log" start 2>/dev/null || true
      for i in $(seq 1 15); do
        if pg_bound pg_isready -h "$PGHOST" -p "$PGPORT" >/dev/null 2>&1; then
          log "Native PostgreSQL ready on port $PGPORT"
          return
        fi
        sleep 1
      done
    fi
  fi

  err "Could not start PostgreSQL. Try: brew services start postgresql@17"
  exit 1
}

# Stop command
cmd_stop() {
  local mode
  mode=$(get_mode)

  if [ "$mode" = "native" ]; then
    stop_native
  else
    stop_docker
  fi
}

stop_docker() {
  info "Stopping Docker PostgreSQL..."
  require_docker_compose
  cd "$ROOT_DIR"
  # Stopping an absent or already-stopped container exits 0, so a nonzero exit
  # is a genuine failure (daemon down, permissions) and must not read as success.
  if ! docker compose stop db; then
    err "Failed to stop Docker PostgreSQL"
    exit 1
  fi
  log "Stopped"
}

# True while the selected native endpoint still accepts connections.
native_accepting() {
  pg_bound pg_isready -h "$PGHOST" -p "$PGPORT" >/dev/null 2>&1
}

stop_native() {
  info "Stopping native PostgreSQL..."
  local stop_failed=false
  # Stop via Homebrew services (macOS)
  if [ "$(uname)" = "Darwin" ] && command -v brew >/dev/null 2>&1; then
    brew services stop postgresql@17 || stop_failed=true
  elif command -v pg_ctl >/dev/null 2>&1; then
    # Fallback: pg_ctl
    local datadir=""
    if [ "$(uname)" = "Darwin" ] && command -v brew >/dev/null 2>&1; then
      datadir="$(brew --prefix)/var/postgresql@17"
    fi
    if [ -n "$datadir" ] && [ -d "$datadir" ]; then
      pg_ctl -D "$datadir" stop -m fast || stop_failed=true
    fi
  fi
  # Claim Stopped only once the endpoint stops answering. A failing stop command
  # against an endpoint that is already down stays an idempotent success.
  local tries=0
  while native_accepting && [ "$tries" -lt 8 ]; do
    sleep 0.25
    tries=$((tries + 1))
  done
  if native_accepting; then
    err "Native PostgreSQL is still accepting connections on port $PGPORT"
    exit 1
  fi
  if [ "$stop_failed" = true ]; then
    warn "Stop command failed, but nothing is listening on port $PGPORT"
  fi
  log "Stopped"
}

# Fix command — resolve common issues
cmd_fix() {
  local mode
  mode=$(get_mode)

  if [ "$mode" = "docker" ]; then
    fix_docker
  else
    fix_native
  fi
}

fix_docker() {
  info "Fixing Docker PostgreSQL..."

  require_docker_compose

  cd "$ROOT_DIR"

  local container_id data_volume configured_volume running
  # Repair removes the container, so the next `compose up` must select the SAME
  # volume. A renamed checkout/project or changed volume override can otherwise
  # free the fixed container name and boot a new, empty database after repair.
  # Resolve Compose's canonical model before any stop/removal. Its full output
  # contains credentials: emit only a validated volume name, never parser errors.
  if ! configured_volume=$(docker compose config --format json 2>/dev/null | node -e '
    let input = "";
    process.stdin.on("data", chunk => { input += chunk; });
    process.stdin.on("end", () => {
      try {
        const config = JSON.parse(input);
        const mounts = config.services?.db?.volumes;
        if (!Array.isArray(mounts)) process.exit(1);
        const matching = mounts.filter(v => v?.target === "/var/lib/postgresql/data");
        if (matching.length !== 1 || matching[0].type !== "volume") process.exit(1);
        const name = config.volumes?.[matching[0].source]?.name;
        if (typeof name !== "string" || !/^[a-z0-9][a-z0-9_.-]*$/i.test(name)) process.exit(1);
        process.stdout.write(name);
      } catch { process.exit(1); }
    });
  '); then
    err "Could not resolve the configured database volume; repair stopped"
    return 1
  fi
  # A successful list distinguishes an absent container from an unavailable
  # daemon. Never turn a failed inspection into permission to modify its volume.
  if ! container_id=$(docker ps -aq --filter 'name=^/portos-db$'); then
    err "Could not establish the database container state; repair stopped"
    return 1
  fi
  if [ -n "$container_id" ]; then
    # Retain the actual volume before removing the container, not a guessed
    # Compose project name (which may have changed since it was provisioned).
    if ! data_volume=$(docker inspect -f '{{ range .Mounts }}{{ if eq .Destination "/var/lib/postgresql/data" }}{{ .Name }}{{ end }}{{ end }}' "$container_id"); then
      err "Could not resolve the database data volume; repair stopped"
      return 1
    fi
    if [ "$data_volume" != "$configured_volume" ]; then
      err "Database container volume differs from the configured Compose volume; repair stopped"
      info "Restore the original Compose project/volume configuration before retrying. The container and data are unchanged."
      return 1
    fi
    if ! docker compose stop db; then
      err "Failed to stop Docker PostgreSQL; repair stopped"
      return 1
    fi
    if ! running=$(docker inspect -f '{{.State.Running}}' "$container_id") || [ "$running" != false ]; then
      err "Database container is still running or its state is unknown; repair stopped"
      return 1
    fi
    # Do not force-remove a container that may have restarted since inspection.
    if ! docker rm "$container_id"; then
      err "Failed to remove the database container; repair stopped"
      return 1
    fi
  else
    # No container exists. Never try guessed names, which docker run would
    # silently create; the configured volume must already exist below.
    data_volume="$configured_volume"
  fi
  # Verify absence after removal too, including any concurrent restart.
  if ! container_id=$(docker ps -aq --filter 'name=^/portos-db$') || [ -n "$container_id" ]; then
    err "Database container absence could not be verified; repair stopped"
    return 1
  fi
  if [ -z "$data_volume" ] || ! docker volume inspect "$data_volume" >/dev/null; then
    err "Database data volume could not be verified; repair stopped"
    return 1
  fi

  # Remove stale postmaster.pid from the volume. Reuse the pinned, already-pulled
  # db image from docker-compose.yml (has rm/cat) so repair works offline.
  local PG_HELPER_IMAGE
  PG_HELPER_IMAGE=$(docker compose config --images db 2>/dev/null | head -1)
  [ -n "$PG_HELPER_IMAGE" ] || PG_HELPER_IMAGE=$(grep -m1 'image:.*pgvector' "$ROOT_DIR/docker-compose.yml" | awk '{print $2}')
  if [ -z "$PG_HELPER_IMAGE" ]; then
    warn "Could not resolve the db image from docker-compose.yml — cannot remove stale lock files"
    return 1
  fi
  if ! docker run --rm --pull=never -v "${data_volume}:/data" "$PG_HELPER_IMAGE" \
      sh -c 'rm -f /data/postmaster.pid && test ! -e /data/postmaster.pid'; then
    err "Failed to remove or verify the stale database lock; repair stopped"
    return 1
  fi

  log "Stale lock files cleaned"
  info "Run 'scripts/db.sh start' to restart"
}

fix_native() {
  info "Fixing native PostgreSQL..."
  # Restart via Homebrew services
  if [ "$(uname)" = "Darwin" ] && command -v brew >/dev/null 2>&1; then
    brew services restart postgresql@17 2>/dev/null || true
    log "PostgreSQL restarted via Homebrew"
    return
  fi
  warn "Manual fix may be needed — check PostgreSQL logs"
}

# Setup native PostgreSQL — detects and reuses existing system installation
# Run one role statement as the system user without the password in argv.
# The statement arrives on stdin; psql reads the role name and password from
# the environment (\getenv, PostgreSQL 15+) and quotes them as an identifier
# (:"role") and a literal (:'pw'). The values are scoped to this one command and
# deliberately not PG* variables, so they never authenticate the connection.
provision_role() {
  local sys_user="$1" statement="$2"
  printf '%s\n%s\n%s\n' '\getenv role PORTOS_BOOTSTRAP_ROLE' '\getenv pw PORTOS_BOOTSTRAP_PASSWORD' "$statement" |
    PORTOS_BOOTSTRAP_ROLE="$PGUSER" PORTOS_BOOTSTRAP_PASSWORD="$PGPASSWORD" \
      pg_bound psql -h "$PGHOST" -p "$PGPORT" -U "$sys_user" -d postgres -v ON_ERROR_STOP=1 -X -q
}

cmd_setup_native() {
  info "Setting up native PostgreSQL for PortOS..."

  # Step 1: Ensure PostgreSQL is installed
  if [ "$(uname)" = "Darwin" ]; then
    if ! command -v brew >/dev/null 2>&1; then
      err "Homebrew not installed. Install from https://brew.sh"
      exit 1
    fi

    if ! brew list postgresql@17 >/dev/null 2>&1; then
      info "Installing PostgreSQL 17..."
      brew install postgresql@17
    else
      log "PostgreSQL 17 already installed"
    fi

    if ! brew list pgvector >/dev/null 2>&1; then
      info "Installing pgvector..."
      brew install pgvector
    else
      log "pgvector already installed"
    fi

    # Ensure pg17 binaries are on PATH
    PG_BIN="$(brew --prefix postgresql@17)/bin"
    export PATH="$PG_BIN:$PATH"
    info "Using PostgreSQL from: $PG_BIN"
  else
    if ! command -v psql >/dev/null 2>&1; then
      err "Please install PostgreSQL 17 and pgvector for your platform"
      exit 1
    fi
  fi

  # Step 2: Ensure PostgreSQL is running. A caller-selected endpoint
  # (PGHOST/PGPORT) is provisioned as-is: discovery must never redirect SQL
  # (role/password changes, schema) to a different cluster than the one chosen.
  if [ -n "$SELECTED_PGPORT" ] || [ -n "$SELECTED_PGHOST" ]; then
    PGPORT="${SELECTED_PGPORT:-5432}"
    if pg_bound pg_isready -h "$PGHOST" -p "$PGPORT" >/dev/null 2>&1; then
      log "PostgreSQL already running at $PGHOST:$PGPORT"
    elif [ "$(uname)" = "Darwin" ] && command -v brew >/dev/null 2>&1; then
      info "Starting PostgreSQL..."
      brew services start postgresql@17
      sleep 2
      if pg_bound pg_isready -h "$PGHOST" -p "$PGPORT" >/dev/null 2>&1; then
        log "PostgreSQL started at $PGHOST:$PGPORT"
      else
        err "PostgreSQL is not accepting connections at $PGHOST:$PGPORT. Start the selected cluster and try again."
        exit 1
      fi
    else
      err "PostgreSQL is not running at $PGHOST:$PGPORT. Start the selected cluster and try again."
      exit 1
    fi
  else
    local pg_port=""
    if pg_port=$(detect_system_pg); then
      log "System PostgreSQL already running on port $pg_port"
    else
      info "Starting PostgreSQL..."
      if [ "$(uname)" = "Darwin" ] && command -v brew >/dev/null 2>&1; then
        brew services start postgresql@17
        sleep 2
        if pg_port=$(detect_system_pg); then
          log "PostgreSQL started on port $pg_port"
        else
          err "PostgreSQL failed to start. Check: brew services list"
          exit 1
        fi
      else
        err "PostgreSQL is not running. Start it and try again."
        exit 1
      fi
    fi
    PGPORT="$pg_port"
  fi

  # Step 3: Create portos user if it doesn't exist
  # Connect as the current system user (default Homebrew superuser) to create the role
  local sys_user
  sys_user="$(whoami)"
  if ! pg_bound psql -h "$PGHOST" -p "$PGPORT" -U "$sys_user" -d postgres -tAc "SELECT 1 FROM pg_roles WHERE rolname='$PGUSER'" 2>/dev/null | grep -q 1; then
    info "Creating database user: $PGUSER"
    provision_role "$sys_user" 'CREATE ROLE :"role" WITH LOGIN PASSWORD :'"'"'pw'"'"' CREATEDB SUPERUSER;'
    log "User $PGUSER created"
  else
    log "User $PGUSER already exists"
    # Ensure password and superuser are set correctly (superuser needed for extension management)
    provision_role "$sys_user" 'ALTER USER :"role" WITH PASSWORD :'"'"'pw'"'"' SUPERUSER;' 2>/dev/null || true
  fi

  # Step 4: Create portos database if it doesn't exist
  if ! pg_bound psql -h "$PGHOST" -p "$PGPORT" -U "$sys_user" -d postgres -lqt 2>/dev/null | cut -d\| -f1 | grep -qw "$PGDATABASE"; then
    info "Creating database: $PGDATABASE"
    pg_bound psql -h "$PGHOST" -p "$PGPORT" -U "$sys_user" -d postgres -c "CREATE DATABASE $PGDATABASE OWNER $PGUSER;"
    log "Database $PGDATABASE created"
  else
    log "Database $PGDATABASE already exists"
  fi

  # Step 5: Enable pgvector extension and apply schema
  info "Applying schema..."
  # pgvector extension requires superuser — create as system user, then run schema as portos
  pg_bound psql -h "$PGHOST" -p "$PGPORT" -U "$sys_user" -d "$PGDATABASE" -c "CREATE EXTENSION IF NOT EXISTS vector;" 2>/dev/null || true
  pg_bound psql -h "$PGHOST" -p "$PGPORT" -U "$sys_user" -d "$PGDATABASE" -c "CREATE EXTENSION IF NOT EXISTS pgcrypto;" 2>/dev/null || true
  PGPASSWORD="$PGPASSWORD" pg_bound psql -h "$PGHOST" -p "$PGPORT" -U "$PGUSER" -d "$PGDATABASE" -v ON_ERROR_STOP=1 --single-transaction -f "$ROOT_DIR/server/scripts/init-db.sql"
  log "Schema applied"

  echo ""
  log "Native PostgreSQL is ready!"
  info "Using system PostgreSQL on port $PGPORT"
  info "Database: $PGDATABASE (user: $PGUSER)"
  info "Provisioning only: the selected mode is unchanged; Docker data has not been migrated."
  info "Coordinated backend migration is not yet available. Keep using the current backend."
}

# Scope inherited libpq settings out of explicit transfers. PGHOSTADDR takes
# precedence over -h's network destination, PGSERVICE can supply another
# endpoint, and PGOPTIONS can redirect statements (search_path). Only the
# password crosses; every connection parameter is an explicit argument.
run_explicit_pg() (
  local binary="$1" host="$PGHOST" port="$PGPORT" user="$PGUSER" database="$PGDATABASE" password="$PGPASSWORD"
  shift
  local name
  for name in $(compgen -e); do
    case "$name" in PG*) unset "$name" ;; esac
  done
  PGPASSWORD="$password" "$binary" -h "$host" -p "$port" -U "$user" -d "$database" "$@"
)

# Run psql command, using Docker exec in Docker mode if host psql is unavailable
run_psql() {
  if [ "$EXPLICIT_ENDPOINT" = true ]; then
    # Never fall back to container-local psql: that is a different endpoint.
    run_explicit_pg psql "$@"
    return $?
  fi
  if command -v psql >/dev/null 2>&1; then
    PGPASSWORD="$PGPASSWORD" pg_bound psql -h "$PGHOST" -p "$PGPORT" -U "$PGUSER" -d "$PGDATABASE" "$@"
  elif uses_local_docker_endpoint && docker_running; then
    PGPASSWORD="$PGPASSWORD" docker exec -i -e PGPASSWORD portos-db psql -U "$PGUSER" -d "$PGDATABASE" "$@"
  else
    err "psql not found on host and Docker DB is not running"
    exit 1
  fi
}

# Run pg_dump, preferring Docker exec in Docker mode to avoid version mismatch
run_pg_dump() {
  if [ "$EXPLICIT_ENDPOINT" = true ]; then
    run_explicit_pg pg_dump "$@"
    return $?
  fi
  if uses_local_docker_endpoint && docker_running; then
    PGPASSWORD="$PGPASSWORD" docker exec -e PGPASSWORD portos-db pg_dump -U "$PGUSER" -d "$PGDATABASE" "$@"
  elif command -v pg_dump >/dev/null 2>&1; then
    PGPASSWORD="$PGPASSWORD" pg_bound pg_dump -h "$PGHOST" -p "$PGPORT" -U "$PGUSER" -d "$PGDATABASE" "$@"
  else
    err "pg_dump not found on host and Docker DB is not running" >&2
    # return (not exit) so cmd_export can clean up its temp file
    return 1
  fi
}

# Export database to SQL dump. Prints the dump path on stdout ONLY on success.
# Every step is checked explicitly: cmd_migrate calls this inside a command
# substitution, where set -e is not inherited (and inherit_errexit is missing
# from older supported Bash), so a failed pg_dump must not fall through to the
# rename + success echo (#8781).
cmd_export() {
  local label="${1:-$(date +%Y%m%d-%H%M%S)}"

  # Sanitize label to prevent path traversal
  if echo "$label" | grep -qE '[^A-Za-z0-9._-]'; then
    err "Invalid label: only alphanumeric, dots, hyphens, and underscores are allowed" >&2
    return 1
  fi

  if ! mkdir -p "$DUMP_DIR"; then
    err "Could not create dump directory: $DUMP_DIR" >&2
    return 1
  fi
  local dumpfile="$DUMP_DIR/portos-$label.sql"

  info "Exporting database to $dumpfile..." >&2

  # Dump to a temp file first so a failed dump never replaces an existing one
  local tmpfile
  if ! tmpfile="$(mktemp "$DUMP_DIR/portos-export.XXXXXX")"; then
    err "Could not create a temporary dump file in $DUMP_DIR" >&2
    return 1
  fi
  if ! run_pg_dump --no-owner --no-privileges --no-comments --if-exists --clean > "$tmpfile"; then
    rm -f "$tmpfile"
    err "pg_dump failed — no dump was written" >&2
    return 1
  fi
  if ! mv "$tmpfile" "$dumpfile"; then
    rm -f "$tmpfile"
    err "Could not move the dump into place: $dumpfile" >&2
    return 1
  fi

  log "Exported to: $dumpfile" >&2
  echo "$dumpfile"
}

# Import SQL dump into database
cmd_import() (
  local dumpfile="$1"

  if [ ! -f "$dumpfile" ]; then
    err "Dump file not found: $dumpfile"
    exit 1
  fi

  info "Importing $dumpfile..."

  # Finish a private, byte-preserving replay copy before psql sees any SQL.
  # A failed source read must never become a successful end-of-script commit.
  local stage replay
  stage="$(mktemp -d "${TMPDIR:-/tmp}/portos-database-import.XXXXXX")" || return 1
  trap 'rm -rf "$stage"' EXIT
  if ! replay="$(node "$ROOT_DIR/scripts/prepare-database-replay.mjs" "$dumpfile" "$stage")"; then
    return 1
  fi
  run_psql -v ON_ERROR_STOP=1 --single-transaction < "$replay"

  log "Import complete"
)

# Do not snapshot and change mode while PortOS can still accept writes.
# A process-list probe is not a writer fence. The recoverable coordinator in
# #8805 must own admission, explicit endpoints and verified restart first.
cmd_migrate() {
  err "Database migration and switching are temporarily unavailable: coordinated shutdown and restart are required to preserve writes." >&2
  echo "Keep using the current backend. Backups remain available via scripts/db.sh export." >&2
  return 1
}

# Show logs
cmd_logs() {
  local mode
  mode=$(get_mode)

  if [ "$mode" = "docker" ]; then
    require_docker_compose
    cd "$ROOT_DIR"
    docker compose logs -f db
  else
    # Homebrew pg logs
    local logfile=""
    if [ "$(uname)" = "Darwin" ] && command -v brew >/dev/null 2>&1; then
      logfile="$(brew --prefix)/var/log/[email protected]"
    fi
    if [ -n "$logfile" ] && [ -f "$logfile" ]; then
      tail -f "$logfile"
    else
      warn "No log file found. Check: brew services info postgresql@17"
    fi
  fi
}

# Help
cmd_help() {
  cat <<'HELP'
PortOS Database Manager

Usage: scripts/db.sh <command>

Commands:
  status         Show database status (both Docker and native)
  start          Start the database (uses current mode)
  stop           Stop the database
  fix            Fix stale postmaster.pid and other issues
  logs           Tail database logs

  setup-native   Detect/install PostgreSQL, create portos database
  use-docker     Unavailable pending coordinated offline cutover
  use-native     Unavailable pending coordinated offline cutover

  migrate        Unavailable pending coordinated offline cutover
  export [label] Export database to data/db-dumps/
  import <file>  Import a SQL dump file
  export --endpoint <host> <port> <user> <database> [label]
  import --endpoint <host> <port> <user> <database> <file>
                Use host PostgreSQL tools at this exact endpoint (no Docker fallback)

Environment:
  PGMODE=docker|native   Set in .env to control default mode
  PGPORT=5432            PostgreSQL port (native=5432, docker=5561)
  PGPASSWORD=portos      Database password
HELP
}

# Main dispatch
case "${1:-help}" in
  status)       cmd_status ;;
  start)        cmd_start ;;
  stop)         cmd_stop ;;
  fix)          cmd_fix ;;
  setup-native) cmd_setup_native ;;
  use-docker)   cmd_migrate ;;
  use-native)   cmd_migrate ;;
  migrate)      cmd_migrate ;;
  export|import) action="$1"; shift; cmd_transfer "$action" "$@" ;;
  logs)         cmd_logs ;;
  help|--help|-h) cmd_help ;;
  *)            err "Unknown command: $1"; cmd_help; exit 1 ;;
esac