db-apply-migration

name	db-apply-migration
description	Safely apply a migration with pre/post snapshots and exclusive lock
agent	architect
subtask	false

Apply Migration (with snapshot + advisory lock)

Safely apply a migration with pre/post snapshots and exclusive lock

1. YOLO Mode - Fast, Autonomous (0-1 prompts)

• Autonomous decision making with logging
• Minimal user interaction
• Best for:* Simple, deterministic tasks

2. Interactive Mode - Balanced, Educational (5-10 prompts) [DEFAULT]

• Explicit decision checkpoints
• Educational explanations
• Best for:* Learning, complex decisions

3. Pre-Flight Planning - Comprehensive Upfront Planning

• Task analysis phase (identify all ambiguities)
• Zero ambiguity execution
• Best for:* Ambiguous requirements, critical work

Parameter:* mode (optional, default: interactive)

---

Acceptance Criteria

Purpose:* Definitive pass/fail criteria for task completion

Checklist:*

acceptance-criteria:
  - [ ] Data persisted correctly; constraints respected; no orphaned data
    type: acceptance-criterion
    blocker: true
    validation: |
      Assert data persisted correctly; constraints respected; no orphaned data
    error_message: "Acceptance criterion not met: Data persisted correctly; constraints respected; no orphaned data"

---

Error Handling

Strategy:* retry

Common Errors:*

1. Error:* Connection Failed

   • Cause:* Unable to connect to Neo4j database
   • Resolution:* Check connection string, credentials, network
   • Recovery:* Retry with exponential backoff (max 3 attempts)

2. Error:* Query Syntax Error

   • Cause:* Invalid Cypher query syntax
   • Resolution:* Validate query syntax before execution
   • Recovery:* Return detailed syntax error, suggest fix

3. Error:* Transaction Rollback

   • Cause:* Query violates constraints or timeout
   • Resolution:* Review query logic and constraints
   • Recovery:* Automatic rollback, preserve data integrity

---

Inputs

• path (string): Path to SQL migration file

---

1. Pre-Flight Checks

Ask user to confirm:

• Migration file: {path}
• Database: $SUPABASE_DB_URL (redacted)
• Dry-run completed? (yes/no)
• Backup/snapshot taken? (will be done automatically)

CRITICAL*: If user says dry-run not done, stop and recommend: dry-run {path}

2. Acquire Advisory Lock

Ensure no concurrent migrations:

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -c \
"SELECT pg_try_advisory_lock(hashtext('dbsage:migrate')) AS got" \
| grep -q t || { echo "❌ Another migration is running"; exit 1; }

echo "✓ Migration lock acquired"

3. Pre-Migration Snapshot

Create schema-only snapshot before changes:

TS=$(date +%Y%m%d%H%M%S)
mkdir -p supabase/snapshots supabase/rollback

pg_dump "$SUPABASE_DB_URL" --schema-only --clean --if-exists \
  > "supabase/snapshots/${TS}_before.sql"

echo "✓ Pre-migration snapshot: supabase/snapshots/${TS}_before.sql"
echo $TS > /tmp/dbsage_migration_ts

4. Apply Migration

Run migration in transaction:

echo "Applying migration..."
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -f {path}

if [ $? -eq 0 ]; then
  echo "✓ Migration applied successfully"
else
  echo "❌ Migration failed - rolling back..."
  # Advisory lock will be released on disconnect
  exit 1
fi

5. Post-Migration Snapshot

Create snapshot after changes:

TS=$(cat /tmp/dbsage_migration_ts)

pg_dump "$SUPABASE_DB_URL" --schema-only --clean --if-exists \
  > "supabase/snapshots/${TS}_after.sql"

echo "✓ Post-migration snapshot: supabase/snapshots/${TS}_after.sql"

6. Generate Diff (Optional)

diff -u "supabase/snapshots/${TS}_before.sql" \
        "supabase/snapshots/${TS}_after.sql" \
  > "supabase/snapshots/${TS}_diff.patch" || true

echo "✓ Diff saved: supabase/snapshots/${TS}_diff.patch"

7. Release Advisory Lock

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -c \
"SELECT pg_advisory_unlock(hashtext('dbsage:migrate'));"

echo "✓ Migration lock released"

8. Post-Migration Actions

Present options to user:

1. Run smoke tests - smoke-test
2. Check RLS coverage - rls-audit
3. Verify query performance - analyze-hotpaths
4. Done for now

---

Success Output

✅ Migration Applied Successfully

Timestamp: {TS}
Migration: {path}
Snapshots:
  - Before: supabase/snapshots/{TS}_before.sql
  - After:  supabase/snapshots/{TS}_after.sql
  - Diff:   supabase/snapshots/{TS}_diff.patch

Next steps:
  smoke-test     - Validate migration
  rls-audit      - Check security
  rollback {TS}  - Undo if needed

---

Rollback Instructions

If migration needs to be undone:

rollback supabase/snapshots/{TS}_before.sql

Or create manual rollback script in supabase/rollback/{TS}_rollback.sql

---

Migration Fails Mid-Execution

1. PostgreSQL transaction is rolled back automatically
2. Advisory lock released on disconnect
3. Pre-migration snapshot still available
4. Database unchanged

Lock Already Held

❌ Another migration is running
Wait for completion or check for stuck locks:

SELECT * FROM pg_locks WHERE locktype = 'advisory';

Snapshot Creation Fails

• Check disk space
• Verify pg_dump version compatibility
• Check database permissions

---

Safety Features

✅ Advisory lock prevents concurrent migrations
✅ Pre/post snapshots for comparison
✅ ON_ERROR_STOP prevents partial application
✅ Transaction-wrapped execution
✅ Automatic diff generation
✅ Rollback instructions provided