# Complete Blockscout Fix - Database Connection Issue

## Problem Summary

Blockscout container crashes because it can't see database tables (`migrations_status`, `blocks`, `transactions`, etc.) even though they exist when checked from postgres directly.

## Root Cause Analysis

The issue is that **migrations were never properly run** or Blockscout is connecting to a different database/schema than expected. The tables exist in one context but Blockscout can't see them.

## Complete Fix Procedure

### Step 1: Run Migrations in One-Off Container

Since the main container crashes, run migrations in a temporary container:

```bash
# From VMID 5000

# 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()"
```

### Step 2: Verify All Tables Created

```bash
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' ELSE '❌ MISSING' END,
  CASE WHEN EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'blocks') 
    THEN '✅ blocks' ELSE '❌ MISSING' END,
  CASE WHEN EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'transactions') 
    THEN '✅ transactions' ELSE '❌ MISSING' END,
  CASE WHEN EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'addresses') 
    THEN '✅ addresses' ELSE '❌ MISSING' END,
  CASE WHEN EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'smart_contracts') 
    THEN '✅ smart_contracts' ELSE '❌ MISSING' END;
"
```

### Step 3: Update Docker Compose to Run Migrations on Startup

Edit `/opt/blockscout/docker-compose.yml`:

```bash
cd /opt/blockscout

# Backup
cp docker-compose.yml docker-compose.yml.backup

# Update command to run migrations first
sed -i 's|command:.*|command: sh -c "bin/blockscout eval '\''Explorer.Release.migrate()'\'' && bin/blockscout start"|' docker-compose.yml

# Or manually edit and change:
# command: /app/bin/blockscout start
# To:
# command: sh -c "bin/blockscout eval 'Explorer.Release.migrate()' && bin/blockscout start"
```

### Step 4: Restart Blockscout

```bash
cd /opt/blockscout
docker compose down blockscout
docker compose up -d blockscout

# Wait and check
sleep 30
docker ps | grep blockscout
docker logs blockscout 2>&1 | tail -30
```

## Alternative: Use Init Container Pattern

Update `docker-compose.yml` to use an init container:

```yaml
services:
  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
    restart: "no"

  blockscout:
    image: blockscout/blockscout:latest
    container_name: blockscout
    command: bin/blockscout start
    depends_on:
      blockscout-migrate:
        condition: service_completed_successfully
      postgres:
        condition: service_healthy
    # ... rest of config
```

## Quick One-Line Fix

```bash
# From VMID 5000 - Complete fix
BLOCKSCOUT_CONTAINER=$(docker ps -a | grep blockscout | grep -v postgres | awk '{print $1}' | head -1)
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()" && \
cd /opt/blockscout && \
sed -i 's|command:.*blockscout start|command: sh -c "bin/blockscout eval '\''Explorer.Release.migrate()'\'' \&\& bin/blockscout start"|' docker-compose.yml && \
docker compose restart blockscout
```

## Verification

After applying fixes:

```bash
# 1. Check migrations ran
docker exec blockscout-postgres psql -U blockscout -d blockscout -c "
SELECT COUNT(*) as table_count 
FROM information_schema.tables 
WHERE table_schema = 'public';
"

# 2. Check container is running
docker ps | grep blockscout

# 3. Check logs for errors
docker logs blockscout 2>&1 | grep -i "migrations_status\|error" | tail -10

# 4. Test API
curl -s http://localhost:4000/api/v2/stats | head -20
```

## Why This Happens

1. **Migrations not run**: The `Explorer.Release.migrate()` was never executed successfully
2. **Container crashes before migrations**: Container starts, tries to query tables, crashes before migrations can run
3. **Database connection mismatch**: Blockscout connecting to wrong database

## Prevention

Always ensure migrations run **before** Blockscout starts:

1. Use init container (recommended)
2. Run migrations in startup command
3. Run migrations manually before starting Blockscout