SilverSight/scripts/appflowy_fix_migrations.py

#!/usr/bin/env python3
"""
appflowy_fix_migrations.py — Iteratively fix AppFloyo Cloud SQLx migration
ordering bugs by skipping failing migrations one at a time.

Usage:
  python3 scripts/appflowy_fix_migrations.py

Run this on neon-64gb (or SSH there).
"""

import re, subprocess, time, sys

DB_URL = "postgres://postgres:postgres@localhost:5432/appflowy?sslmode=disable"
MAX_ITER = 50  # safety limit

MIGRATION_PATTERN = re.compile(
    r'while executing migration (\d+): error returned from database: (.*?)(?:\n|$)'
)

def get_skipped() -> set[str]:
    """Get already-skipped migrations from file."""
    try:
        with open("/tmp/appflowy_skipped.txt") as f:
            return set(line.strip() for line in f if line.strip())
    except FileNotFoundError:
        return set()

def save_skipped(s: set[str]):
    with open("/tmp/appflowy_skipped.txt", "w") as f:
        f.write("\n".join(sorted(s)) + "\n")

def mark_migration_done(version: str):
    """Insert a fake successful entry in _sqlx_migrations."""
    subprocess.run(
        ["psql", "-h", "localhost", "-U", "postgres", "-d", "appflowy",
         "-c", f"INSERT INTO public._sqlx_migrations "
               f"(version, description, installed_on, success, checksum, execution_time) "
               f"SELECT '{version}', 'auto-skipped (ordering bug)', NOW(), true, "
               f"decode('00', 'hex'), 0 "
               f"WHERE NOT EXISTS (SELECT 1 FROM public._sqlx_migrations "
               f"WHERE version = '{version}')"],
        capture_output=True, env={"PGPASSWORD": "postgres"}
    )

def run_container():
    """Start appflowy_cloud and return container ID."""
    result = subprocess.run(
        ["podman", "run", "-d", "--name", "appflowy_fixer",
         "--network", "host",
         "-e", f"APPFLOWY_DATABASE_URL={DB_URL}",
         "-e", "APPFLOWY_ENVIRONMENT=production",
         "-e", "APPFLOWY_REDIS_URI=redis://localhost:6379",
         "-e", "APPFLOWY_GOTRUE_JWT_SECRET=jwt_",
         "-e", "APPFLOWY_GOTRUE_JWT_EXP=3600",
         "-e", "APPFLOWY_GOTRUE_BASE_URL=http://localhost:9999",
         "-e", "APPFLOWY_BASE_URL=https://researchstack.info/appflowy",
         "-e", "APPFLOWY_WEB_URL=https://researchstack.info/appflowy",
         "-e", "APPFLOWY_ENCRYPTION_KEY=sk_encryption_key_at_least_32_chars_abcdef1234567890",
         "-e", "APPFLOWY_JWT_SECRET=jwt_",
         "-e", "APPFLOWY_CREATE_BUCKET=false",
         "-e", "APPFLOWY_S3_ENABLED=false",
         "-e", "APPFLOWY_SEARCH_ENABLED=false",
         "-e", "RUST_LOG=info",
         "docker.io/appflowyinc/appflowy_cloud:latest"],
        capture_output=True, text=True, timeout=30
    )
    return result.stdout.strip()

def get_logs(container_id: str) -> str:
    """Get container logs."""
    result = subprocess.run(
        ["podman", "logs", container_id],
        capture_output=True, text=True, timeout=10
    )
    return result.stdout + result.stderr

def main():
    skipped = get_skipped()
    print(f"Already skipped: {len(skipped)} migrations")

    for iteration in range(MAX_ITER):
        print(f"\n--- Iteration {iteration + 1} ---")

        # Remove old container
        subprocess.run(["podman", "rm", "-f", "appflowy_fixer"],
                       capture_output=True)

        # Start container
        cid = run_container()
        print(f"Started: {cid[:12]}")

        # Wait for startup
        time.sleep(15)

        # Check logs
        logs = get_logs(cid)

        # Check health
        health = subprocess.run(
            ["curl", "-s", "-o", "/dev/null", "-w", "%{http_code}",
             "http://localhost:8000/api/health"],
            capture_output=True, text=True, timeout=5
        ).stdout.strip()

        if health == "200":
            print(f"✅ AppFloyo Cloud is UP! (health check 200)")
            subprocess.run(["podman", "rename", "appflowy_fixer", "appflowy_cloud"])
            save_skipped(skipped)
            print(f"Total migrations skipped: {len(skipped)}")
            return 0

        # Find failing migration
        match = MIGRATION_PATTERN.search(logs)
        if match:
            ver = match.group(1)
            err = match.group(2).strip()[:80]
            print(f"❌ Migration {ver} failed: {err}")
            mark_migration_done(ver)
            skipped.add(ver)
            save_skipped(skipped)
            print(f"   Skipped migration {ver}")
        else:
            # Check if there's another error
            error_lines = [l for l in logs.split("\n") if "error" in l.lower()
                          or "fail" in l.lower() or "Error" in l]
            for l in error_lines[:5]:
                print(f"   {l.strip()[:120]}")
            print("❌ Could not parse migration error. Manual intervention needed.")
            print(f"   Full logs: {logs[:2000]}")
            return 1

    print("❌ Max iterations reached without success")
    return 1

if __name__ == "__main__":
    sys.exit(main())