# Fix Blockscout migrations_status Table Missing Error

## Problem

Blockscout container crashes with errors like:
```
ERROR 42P01 (undefined_table) relation "migrations_status" does not exist
```

Even though we verified tables exist, Blockscout can't find `migrations_status` when it starts, causing all migrator GenServers to crash.

## Root Cause

The `migrations_status` table may exist, but Blockscout's migration system hasn't properly initialized it, OR migrations need to be run again to ensure all tables are in the correct state.

## Solution

Run migrations BEFORE starting Blockscout, or ensure migrations run on startup.

### Quick Fix Commands (From VMID 5000)

```bash
# Step 1: Start container temporarily
BLOCKSCOUT_CONTAINER=$(docker ps -a | grep blockscout | grep -v postgres | awk '{print $1}' | head -1)
docker start $BLOCKSCOUT_CONTAINER
sleep 10

# Step 2: Run migrations
docker exec -it $BLOCKSCOUT_CONTAINER bin/blockscout eval "Explorer.Release.migrate()"

# Step 3: Verify migrations_status table
docker exec blockscout-postgres psql -U blockscout -d blockscout -c "
SELECT CASE WHEN EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'migrations_status') 
  THEN '✅ migrations_status exists' 
  ELSE '❌ migrations_status MISSING' END;
"

# Step 4: Restart Blockscout
docker restart $BLOCKSCOUT_CONTAINER
sleep 30

# Step 5: Check status
docker ps | grep blockscout
docker logs blockscout 2>&1 | tail -30
```

### Alternative: Run Migrations in One-Off Container

If the main container won't start, run migrations in a temporary container:

```bash
# Get network from existing container
BLOCKSCOUT_CONTAINER=$(docker ps -a | grep blockscout | grep -v postgres | awk '{print $1}' | head -1)

# Run migrations in one-off container
docker run --rm \
  --network container:$BLOCKSCOUT_CONTAINER \
  -e DATABASE_URL=postgresql://blockscout:blockscout@postgres:5432/blockscout \
  blockscout/blockscout:latest \
  bin/blockscout eval "Explorer.Release.migrate()"
```

### Update Docker Compose to Run Migrations on Startup

Modify `/opt/blockscout/docker-compose.yml` to run migrations before starting:

```yaml
blockscout:
  image: blockscout/blockscout:latest
  container_name: blockscout
  command: sh -c "bin/blockscout eval 'Explorer.Release.migrate()' && bin/blockscout start"
  # ... rest of config
```

Or use an init container pattern:

```yaml
blockscout-migrate:
  image: blockscout/blockscout:latest
  command: bin/blockscout eval "Explorer.Release.migrate()"
  environment:
    - DATABASE_URL=postgresql://blockscout:blockscout@postgres:5432/blockscout
  depends_on:
    postgres:
      condition: service_healthy

blockscout:
  image: blockscout/blockscout:latest
  command: bin/blockscout start
  depends_on:
    blockscout-migrate:
      condition: service_completed_successfully
    postgres:
      condition: service_healthy
```

## Automated Fix Script

Run the automated fix script:

```bash
# From Proxmox host
cd /home/intlc/projects/proxmox/explorer-monorepo
./scripts/fix-blockscout-migrations.sh
```

## Verification

After running migrations, verify:

```bash
# 1. Check migrations_status table exists
docker exec blockscout-postgres psql -U blockscout -d blockscout -c "
SELECT table_name 
FROM information_schema.tables 
WHERE table_name = 'migrations_status';
"

# 2. Check if Blockscout starts without errors
docker restart blockscout
sleep 30
docker logs blockscout 2>&1 | grep -i "migrations_status\|error" | tail -10

# 3. Verify container stays running
docker ps | grep blockscout
```

## Why This Happens

1. **Migrations not run**: If Blockscout was started before migrations completed
2. **Schema mismatch**: Tables exist but migrations_status wasn't created properly
3. **Database connection issue**: Blockscout connects to different database than expected
4. **Migration order**: Some migrations depend on migrations_status existing first

## Prevention

Always ensure migrations run before Blockscout starts:

1. **Use init container** (recommended)
2. **Run migrations in command** (simple but slower startup)
3. **Manual migration step** in deployment process

## Next Steps

After fixing migrations:

1. ✅ Verify `migrations_status` table exists
2. ✅ Build static assets: `docker exec -it blockscout mix phx.digest`
3. ✅ Verify Blockscout starts and stays running
4. ✅ Test API: `curl http://localhost:4000/api/v2/stats`

