BASH migrate-to-postgres.sh

#!/usr/bin/env bash
#
# Command Sovereignty: SQLite to PostgreSQL Migration Script
#
# Usage: ./scripts/migrate-to-postgres.sh
#
# WARNING: This will stop your service, spin up a local PostgreSQL container,
# dump your SQLite data into it, and switch your app to use PostgreSQL.
# Your SQLite database will be backed up before proceeding.
#

set -euo pipefail

PROJECT_DIR="/home/vincent/projects/command-sovereignty"
DB_SQLITE="$PROJECT_DIR/instance/auth.db"
ENV_FILE="$HOME/.config/command-sovereignty/env"
BACKUP_DIR="$PROJECT_DIR/backups"
LOG_FILE="$PROJECT_DIR/logs/migration.log"

PG_USER="postgres"
PG_DB="commandsovereignty"
PG_PORT=5432
PG_CONTAINER="cs-postgres-local"
# Generate a secure random password for the local DB
PG_PASS="cs_local_pg_$(openssl rand -hex 12)"

mkdir -p "$BACKUP_DIR" "$(dirname "$LOG_FILE")"

log() {
    local msg="[$(date '+%Y-%m-%d %H:%M:%S')] $1"
    echo "$msg" | tee -a "$LOG_FILE"
}

die() {
    log "FATAL: $1"
    exit 1
}

# 1. Pre-flight checks
log "Starting migration to PostgreSQL..."

if ! command -v docker &> /dev/null; then
    die "Docker is not installed or not in PATH."
fi

if ! command -v psql &> /dev/null; then
    log "psql client is not installed. Installing postgresql-client..."
    sudo apt-get update && sudo apt-get install -y postgresql-client
fi

if ! command -v sqlite3 &> /dev/null; then
    die "sqlite3 is not installed. Run: sudo apt install sqlite3"
fi

if [ ! -f "$DB_SQLITE" ]; then
    die "SQLite database not found at $DB_SQLITE"
fi

# 2. Stop the current service
log "Stopping Command Sovereignty service..."
systemctl --user stop command-sovereignty.service || true
sleep 2

# 3. Backup the current SQLite DB
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/auth_${TIMESTAMP}_pre_pg_migration.db.gz"
log "Backing up SQLite DB to $BACKUP_FILE..."
gzip -c "$DB_SQLITE" > "$BACKUP_FILE"

# 4. Spin up PostgreSQL
log "Spinning up PostgreSQL container ($PG_CONTAINER)..."
docker rm -f "$PG_CONTAINER" > /dev/null 2>&1 || true
docker run -d \
    --name "$PG_CONTAINER" \
    -e POSTGRES_USER="$PG_USER" \
    -e POSTGRES_PASSWORD="$PG_PASS" \
    -e POSTGRES_DB="$PG_DB" \
    -p "$PG_PORT:5432" \
    postgres:16-alpine

# 5. Wait for PostgreSQL to be ready
log "Waiting for PostgreSQL to initialize..."
WAIT=0
while ! docker exec "$PG_CONTAINER" pg_isready -U "$PG_USER" > /dev/null 2>&1; do
    sleep 1
    WAIT=$((WAIT + 1))
    if [ "$WAIT" -gt 30 ]; then
        die "PostgreSQL failed to start within 30 seconds."
    fi
done
log "PostgreSQL is ready."

# 6. Dump SQLite and load into PostgreSQL
log "Dumping SQLite and importing to PostgreSQL..."
sqlite3 "$DB_SQLITE" .dump | \
    psql -h 127.0.0.1 -p "$PG_PORT" -U "$PG_USER" -d "$PG_DB" -v ON_ERROR_STOP=1 \
    --password -w <<<"$PG_PASS"

if [ $? -ne 0 ]; then
    die "Database import failed. Check $LOG_FILE for details."
fi

# 7. Verify data
PROFILES_SQLITE=$(sqlite3 "$DB_SQLITE" "SELECT count(*) FROM profiles;")
PROFILES_PG=$(psql -h 127.0.0.1 -p "$PG_PORT" -U "$PG_USER" -d "$PG_DB" -t -c "SELECT count(*) FROM profiles;" --password -w <<<"$PG_PASS")

if [ "$PROFILES_SQLITE" != "$PROFILES_PG" ]; then
    die "Data mismatch! SQLite had $PROFILES_SQLITE profiles, Postgres has $PROFILES_PG."
fi

log "Data verified: $PROFILES_PG profiles in PostgreSQL."

# 8. Update Environment Config
log "Updating environment config at $ENV_FILE..."
NEW_URL="DATABASE_URL=postgresql://$PG_USER:$PG_PASS@127.0.0.1:$PG_PORT/$PG_DB"

# Backup env file
cp "$ENV_FILE" "${ENV_FILE}.bak.${TIMESTAMP}"

# Replace DATABASE_URL line
if grep -q "^DATABASE_URL" "$ENV_FILE"; then
    sed -i "s|^DATABASE_URL=.*|$NEW_URL|" "$ENV_FILE"
else
    echo "$NEW_URL" >> "$ENV_FILE"
fi

# 9. Restart the service
log "Restarting Command Sovereignty service..."
systemctl --user restart command-sovereignty.service

# 10. Verify health
sleep 3
log "Waiting for app to start..."
HEALTH=$(curl -s http://127.0.0.1:5003/health 2>/dev/null || echo '{"status":"error"}')

echo ""
log "=========================================="
if echo "$HEALTH" | grep -q '"healthy"'; then
    log "Migration SUCCESSFUL!"
    log "App is running on PostgreSQL."
else
    log "Migration COMPLETED but app health check returned: $HEALTH"
    log "Check logs at $PROJECT_DIR/logs/app.log"
fi

log "=========================================="
log "PostgreSQL Credentials:"
log "  Host: 127.0.0.1:$PG_PORT"
log "  DB:   $PG_DB"
log "  User: $PG_USER"
log "  Pass: $PG_PASS"
log "=========================================="