Production Migration Recovery Guide

Problem: Superadmin Seed Constraint Violation

Root Cause

The seed_superadmin() function was attempting to update is_primary = true on conflict, which violated the unique partial index idx_user_roles_primary_per_user on subsequent application restarts.

Solution Applied

Modified backend/src/infrastructure/database/seed.rs line 60-74 to:

ON CONFLICT (user_id, role, organization_id)
DO UPDATE SET
    updated_at = NOW()
    -- REMOVED: is_primary = true (prevents constraint violation)

Rationale: is_primary should only be set on INSERT (first run), never on UPDATE (subsequent runs).

Production Deployment Checklist

✅ Pre-Deployment

  1. Backup Database: pg_dump -U koprogo koprogo_db > backup_$(date +%Y%m%d_%H%M%S).sql

  2. Test on Staging: Deploy to staging environment first

  3. Run Idempotence Tests: cargo test test_seed_superadmin_is_idempotent

✅ Deployment

  1. Stop Backend: systemctl stop koprogo-backend (or Docker: docker compose stop backend)

  2. Verify Migrations:

    SELECT version, checksum FROM _sqlx_migrations ORDER BY version DESC LIMIT 10;

  3. Apply New Code: git pull && cargo build --release

  4. Start Backend: Application will auto-seed superadmin idempotently

✅ Post-Deployment Verification

  1. Check Logs: journalctl -u koprogo-backend | grep superadmin

    • Expected: ✅ Superadmin ready: admin@koprogo.com

    • NOT expected: duplicate key value violates unique constraint

  2. Verify Database State:

    -- Should return exactly 1 row
SELECT COUNT(*) FROM user_roles
WHERE user_id = '00000000-0000-0000-0000-000000000001'
AND is_primary = true;

-- Should return 1 (superadmin role)
SELECT role, is_primary FROM user_roles
WHERE user_id = '00000000-0000-0000-0000-000000000001';

  3. Test Login: curl -X POST http://localhost:8080/api/v1/auth/login -d '{"email":"admin@koprogo.com","password":"admin123"}'

Rollback Plan

If deployment fails:

  1. Stop Backend: systemctl stop koprogo-backend

  2. Restore Backup: psql -U koprogo koprogo_db < backup_YYYYMMDD_HHMMSS.sql

  3. Revert Code: git checkout <previous-commit>

  4. Start Backend: systemctl start koprogo-backend

Migration State Recovery (If Needed)

Scenario: Migrations table accidentally modified

⚠️ WARNING: Only use this in extreme cases. Never run in production without full backup.

-- Check current state
SELECT version FROM _sqlx_migrations ORDER BY version DESC;

-- If migrations are missing (e.g., only showing 20240101000006):
-- Option 1: Restore from backup (RECOMMENDED)
-- Option 2: Re-insert migration records (DANGEROUS - only if backup unavailable)

-- DO NOT RUN THIS unless you fully understand the implications:
-- This would require manually re-inserting all missing migration records
-- with correct checksums. Contact DevOps team instead.

Testing Idempotence

Run tests with real PostgreSQL instance:

# Local dev
cargo test --test integration test_seed_superadmin_is_idempotent

# CI/CD
docker compose -f docker-compose.test.yml run --rm backend cargo test --lib test_seed_superadmin

Monitoring

Add alerting for:

  • Application startup failures (check for constraint violations in logs)

  • Unexpected user_roles record counts for superadmin

  • Failed login attempts for admin@koprogo.com

Questions?

Contact: DevOps Team | Created: 2025-12-06