proxmox/scripts/apply-token-aggregation-fix.sh

#!/usr/bin/env bash
# Create token_aggregation DB and run migrations inside VMID 5000 (via Proxmox host).
# Fixes: /health returns "database token_aggregation does not exist"
# Usage: ./scripts/apply-token-aggregation-fix.sh [--dry-run]

set -euo pipefail

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
PROJECT_ROOT="$(cd "$SCRIPT_DIR/.." && pwd)"
cd "$PROJECT_ROOT"

source "${PROJECT_ROOT}/config/ip-addresses.conf" 2>/dev/null || true
PROXMOX_HOST="${PROXMOX_HOST_R630_02:-192.168.11.12}"
MIGRATIONS_DIR="${PROJECT_ROOT}/explorer-monorepo/backend/database/migrations"

DRY_RUN=false
for a in "$@"; do [[ "$a" == "--dry-run" ]] && DRY_RUN=true && break; done

echo "=== Token-aggregation fix (VMID 5000) ==="
echo "  dry-run=$DRY_RUN"
echo ""

for f in 0011_token_aggregation_schema.up.sql 0012_admin_config_schema.up.sql; do
  [[ -f "$MIGRATIONS_DIR/$f" ]] || { echo "Missing: $MIGRATIONS_DIR/$f"; exit 1; }
done
[[ -f "$MIGRATIONS_DIR/0013_update_token_logos_ipfs.up.sql" ]] && HAS_0013=1 || HAS_0013=0

if [[ "$DRY_RUN" == true ]]; then
  echo "Would: scp migrations to Proxmox, pct push to 5000, createdb token_aggregation, psql -f each migration, systemctl restart token-aggregation"
  exit 0
fi

if ! ssh -o ConnectTimeout=10 "root@${PROXMOX_HOST}" "pct exec 5000 -- true" 2>/dev/null; then
  echo "SSH to root@${PROXMOX_HOST} or pct exec 5000 failed. Run inside VMID 5000 manually:"
  echo "  sudo -u postgres createdb token_aggregation"
  echo "  cd $MIGRATIONS_DIR && sudo -u postgres psql -d token_aggregation -f 0011_token_aggregation_schema.up.sql -f 0012_admin_config_schema.up.sql"
  echo "  systemctl restart token-aggregation"
  exit 1
fi

TMPD=$(mktemp -d)
trap "rm -rf $TMPD" EXIT
cp "$MIGRATIONS_DIR/0011_token_aggregation_schema.up.sql" "$MIGRATIONS_DIR/0012_admin_config_schema.up.sql" "$TMPD/"
[[ "$HAS_0013" == 1 ]] && cp "$MIGRATIONS_DIR/0013_update_token_logos_ipfs.up.sql" "$TMPD/"

echo "Copying migrations to Proxmox host..."
scp -o ConnectTimeout=10 "$TMPD"/*.sql "root@${PROXMOX_HOST}:/tmp/" || { echo "scp failed"; exit 1; }

echo "Creating DB and running migrations inside VMID 5000..."
ssh "root@${PROXMOX_HOST}" "pct push 5000 /tmp/0011_token_aggregation_schema.up.sql /tmp/0011.up.sql && pct push 5000 /tmp/0012_admin_config_schema.up.sql /tmp/0012.up.sql"
[[ "$HAS_0013" == 1 ]] && ssh "root@${PROXMOX_HOST}" "pct push 5000 /tmp/0013_update_token_logos_ipfs.up.sql /tmp/0013.up.sql"

# VMID 5000 may not have postgres user; try postgres then root for createdb/psql
ssh "root@${PROXMOX_HOST}" "pct exec 5000 -- bash -c '
  if getent passwd postgres &>/dev/null; then
    sudo -u postgres createdb token_aggregation 2>/dev/null || true
    sudo -u postgres psql -d token_aggregation -f /tmp/0011.up.sql -q 2>/dev/null
    sudo -u postgres psql -d token_aggregation -f /tmp/0012.up.sql -q 2>/dev/null
    [ -f /tmp/0013.up.sql ] && sudo -u postgres psql -d token_aggregation -f /tmp/0013.up.sql -q 2>/dev/null
  else
    # If PostgreSQL runs as root or in another container, run migrations where DATABASE_URL points (e.g. explorer_db)
    createdb -U postgres token_aggregation 2>/dev/null || psql -U postgres -d postgres -c \"SELECT 1\" &>/dev/null && createdb -U postgres token_aggregation 2>/dev/null || true
    psql -U postgres -d token_aggregation -f /tmp/0011.up.sql -q 2>/dev/null || true
    psql -U postgres -d token_aggregation -f /tmp/0012.up.sql -q 2>/dev/null || true
    [ -f /tmp/0013.up.sql ] && psql -U postgres -d token_aggregation -f /tmp/0013.up.sql -q 2>/dev/null || true
  fi
  systemctl restart token-aggregation 2>/dev/null || true
  echo Done
'" && echo "✅ Token-aggregation fix applied (if DB user exists in container)" || { echo "⚠ Steps completed with warnings (container may not have postgres user; run migrations manually where PostgreSQL runs)"; exit 0; }

echo "Verify: curl -s http://192.168.11.140:3001/health | jq ."