Migration Guide: Run from Local Machine¶

Since you have direct access to Azure PostgreSQL, you can run migrations from your local machine.

Prerequisites¶

Direct network access to slidefactory-db.postgres.database.azure.com
Credentials (already in your .env.local)
Alembic installed locally

Quick Migration Commands¶

Preview Database¶

# Run migration on preview database
DATABASE_URL="postgresql+psycopg2://dbadmin:esLVoH6UVweWRVU9yC*u@slidefactory-db.postgres.database.azure.com:5432/slidefactory-preview?sslmode=require&connect_timeout=30" \
  alembic upgrade head

Expected output:

INFO  [alembic.runtime.migration] Running upgrade 9c7deff4ac4a -> 453c1befe40, add workflow_engines table

Main Database¶

# Run migration on main database
DATABASE_URL="postgresql+psycopg2://dbadmin:esLVoH6UVweWRVU9yC*u@slidefactory-db.postgres.database.azure.com:5432/slidefactory?sslmode=require&connect_timeout=30" \
  alembic upgrade head

Verification Commands¶

Check Preview Database¶

# Check migration version
PGPASSWORD="esLVoH6UVweWRVU9yC*u" psql \
  -h slidefactory-db.postgres.database.azure.com \
  -U dbadmin \
  -d slidefactory-preview \
  -c "SELECT version_num FROM alembic_version;"

# Check table exists and is empty
PGPASSWORD="esLVoH6UVweWRVU9yC*u" psql \
  -h slidefactory-db.postgres.database.azure.com \
  -U dbadmin \
  -d slidefactory-preview \
  -c "SELECT COUNT(*) FROM workflow_engines;"

Expected: - Version: 453c1befe40 - Count: 0

Check Main Database¶

# Check migration version
PGPASSWORD="esLVoH6UVweWRVU9yC*u" psql \
  -h slidefactory-db.postgres.database.azure.com \
  -U dbadmin \
  -d slidefactory \
  -c "SELECT version_num FROM alembic_version;"

# Check table exists and is empty
PGPASSWORD="esLVoH6UVweWRVU9yC*u" psql \
  -h slidefactory-db.postgres.database.azure.com \
  -U dbadmin \
  -d slidefactory \
  -c "SELECT COUNT(*) FROM workflow_engines;"

Step-by-Step Deployment Process¶

Step 1: Backup (Optional but Recommended)¶

# Backup preview database schema
PGPASSWORD="esLVoH6UVweWRVU9yC*u" pg_dump \
  -h slidefactory-db.postgres.database.azure.com \
  -U dbadmin \
  -d slidefactory-preview \
  --schema-only \
  > backup_preview_schema_$(date +%Y%m%d_%H%M%S).sql

# Backup main database schema
PGPASSWORD="esLVoH6UVweWRVU9yC*u" pg_dump \
  -h slidefactory-db.postgres.database.azure.com \
  -U dbadmin \
  -d slidefactory \
  --schema-only \
  > backup_main_schema_$(date +%Y%m%d_%H%M%S).sql

Step 2: Check Current Status¶

# Preview current version
DATABASE_URL="postgresql+psycopg2://dbadmin:esLVoH6UVweWRVU9yC*u@slidefactory-db.postgres.database.azure.com:5432/slidefactory-preview?sslmode=require&connect_timeout=30" \
  alembic current

# Main current version
DATABASE_URL="postgresql+psycopg2://dbadmin:esLVoH6UVweWRVU9yC*u@slidefactory-db.postgres.database.azure.com:5432/slidefactory?sslmode=require&connect_timeout=30" \
  alembic current

Expected: Both should show 9c7deff4ac4a (head)

Step 3: Apply Migration to Preview¶

# Apply to preview
DATABASE_URL="postgresql+psycopg2://dbadmin:esLVoH6UVweWRVU9yC*u@slidefactory-db.postgres.database.azure.com:5432/slidefactory-preview?sslmode=require&connect_timeout=30" \
  alembic upgrade head

# Verify
DATABASE_URL="postgresql+psycopg2://dbadmin:esLVoH6UVweWRVU9yC*u@slidefactory-db.postgres.database.azure.com:5432/slidefactory-preview?sslmode=require&connect_timeout=30" \
  alembic current

# Check table
PGPASSWORD="esLVoH6UVweWRVU9yC*u" psql \
  -h slidefactory-db.postgres.database.azure.com \
  -U dbadmin \
  -d slidefactory-preview \
  -c "\d workflow_engines"

Step 4: Test Preview Application¶

After migration: 1. Restart preview web container (or wait for next deployment) 2. Check logs for successful startup 3. Verify no errors

# Check preview logs
az containerapp logs show \
  --name slidefactory-web-preview \
  --resource-group <your-resource-group> \
  --tail 50 | grep -i workflow

Expected logs:

INFO: Initializing workflow engine registry (multi-source)
INFO: Loading workflow engines from environment variables
INFO: Workflow registry initialized with X engines

Step 5: Apply Migration to Main¶

Only after preview verified working!

# Apply to main
DATABASE_URL="postgresql+psycopg2://dbadmin:esLVoH6UVweWRVU9yC*u@slidefactory-db.postgres.database.azure.com:5432/slidefactory?sslmode=require&connect_timeout=30" \
  alembic upgrade head

# Verify
DATABASE_URL="postgresql+psycopg2://dbadmin:esLVoH6UVweWRVU9yC*u@slidefactory-db.postgres.database.azure.com:5432/slidefactory?sslmode=require&connect_timeout=30" \
  alembic current

# Check table
PGPASSWORD="esLVoH6UVweWRVU9yC*u" psql \
  -h slidefactory-db.postgres.database.azure.com \
  -U dbadmin \
  -d slidefactory \
  -c "\d workflow_engines"

Step 6: Git Commit & Push¶

Now that databases are migrated, deploy the code:

# Add all changes
git add .

# Commit
git commit -m "feat: implement workflow engine registration system (Phase 1)

- Add database model for workflow engine registration
- Implement API key encryption (Fernet)
- Add CRUD operations with validation
- Implement multi-source engine loading (database + environment)
- Add Alembic migration (453c1befe40)
- Maintain backward compatibility with environment variables
- Update registry to support string-based engine keys

Database migration applied manually to preview and main:
- workflow_engines table created
- Indexes created
- Alembic version updated to 453c1befe40

✅ Zero breaking changes - Azure deployment unchanged
✅ Environment variable fallback working
✅ Foundation for multi-engine support

See ../reports/technical/2025-11-04_workflow_engine_registration_system.md

🤖 Generated with Claude Code
Co-Authored-By: Claude <noreply@anthropic.com>"

# Push to preview
git push origin preview

# Wait for deployment, verify, then merge to main
git checkout main
git merge preview
git push origin main

Rollback (If Needed)¶

Rollback Preview Database¶

DATABASE_URL="postgresql+psycopg2://dbadmin:esLVoH6UVweWRVU9yC*u@slidefactory-db.postgres.database.azure.com:5432/slidefactory-preview?sslmode=require&connect_timeout=30" \
  alembic downgrade -1

Rollback Main Database¶

DATABASE_URL="postgresql+psycopg2://dbadmin:esLVoH6UVweWRVU9yC*u@slidefactory-db.postgres.database.azure.com:5432/slidefactory?sslmode=require&connect_timeout=30" \
  alembic downgrade -1

Manual Rollback (If Alembic fails)¶

# Preview
PGPASSWORD="esLVoH6UVweWRVU9yC*u" psql \
  -h slidefactory-db.postgres.database.azure.com \
  -U dbadmin \
  -d slidefactory-preview << 'EOF'
BEGIN;
DROP TABLE IF EXISTS workflow_engines CASCADE;
UPDATE alembic_version SET version_num = '9c7deff4ac4a';
COMMIT;
EOF

# Main
PGPASSWORD="esLVoH6UVweWRVU9yC*u" psql \
  -h slidefactory-db.postgres.database.azure.com \
  -U dbadmin \
  -d slidefactory << 'EOF'
BEGIN;
DROP TABLE IF EXISTS workflow_engines CASCADE;
UPDATE alembic_version SET version_num = '9c7deff4ac4a';
COMMIT;
EOF

Troubleshooting¶

Connection Timeout¶

If you get connection timeouts:

# Check if you can connect
PGPASSWORD="esLVoH6UVweWRVU9yC*u" psql \
  -h slidefactory-db.postgres.database.azure.com \
  -U dbadmin \
  -d slidefactory-preview \
  -c "SELECT version();"

If connection fails: - Check firewall rules in Azure Portal - Verify your IP is whitelisted - Check VPN connection if required

SSL Certificate Issues¶

If you get SSL errors, try:

# Disable SSL verification (not recommended for production)
DATABASE_URL="postgresql+psycopg2://dbadmin:esLVoH6UVweWRVU9yC*u@slidefactory-db.postgres.database.azure.com:5432/slidefactory-preview?sslmode=require&sslcert=/dev/null" \
  alembic upgrade head

Alembic Not Found¶

# Install alembic if not available
pip install alembic

# Or use docker
docker compose exec web alembic upgrade head

Summary¶

Simplified workflow from local machine:

✅ Apply migration to preview: DATABASE_URL=<preview-url> alembic upgrade head
✅ Verify preview: Check logs, test application
✅ Apply migration to main: DATABASE_URL=<main-url> alembic upgrade head
✅ Commit and push code: Git workflow
✅ Verify deployments: Monitor logs

Key advantage: No need to exec into Azure containers - run everything from your local machine! 🚀