When developers work on feature branches, they often:

Pull main and run db:migrate to stay current
Switch back to their feature branch
Run their new migration
Commit the schema.rb - which now includes columns from main that aren't in their PR

This pollutes PRs with unrelated changes and can cause merge conflicts or confusion.

Core Review Process

Step 1: Identify Migrations in the PR

# List all migration files changed in the PR
git diff main --name-only -- db/migrate/

# Get the migration version numbers
git diff main --name-only -- db/migrate/ | grep -oE '[0-9]{14}'

Step 2: Analyze Schema Changes

# Show all schema.rb changes
git diff main -- db/schema.rb

Step 3: Cross-Reference

For each change in schema.rb, verify it corresponds to a migration in the PR:

Expected schema changes:

Version number update matching the PR's migration
Tables/columns/indexes explicitly created in the PR's migrations

Drift indicators (unrelated changes):

Columns that don't appear in any PR migration
Tables not referenced in PR migrations
Indexes not created by PR migrations
Version number higher than the PR's newest migration

Common Drift Patterns

1. Extra Columns

# DRIFT: These columns aren't in any PR migration
+    t.text "openai_api_key"
+    t.text "anthropic_api_key"
+    t.datetime "api_key_validated_at"

2. Extra Indexes

# DRIFT: Index not created by PR migrations
+    t.index ["complimentary_access"], name: "index_users_on_complimentary_access"

3. Version Mismatch

# PR has migration 20260205045101 but schema version is higher
-ActiveRecord::Schema[7.2].define(version: 2026_01_29_133857) do
+ActiveRecord::Schema[7.2].define(version: 2026_02_10_123456) do

Verification Checklist

Schema version matches the PR's newest migration timestamp
Every new column in schema.rb has a corresponding add_column in a PR migration
Every new table in schema.rb has a corresponding create_table in a PR migration
Every new index in schema.rb has a corresponding add_index in a PR migration
No columns/tables/indexes appear that aren't in PR migrations

How to Fix Schema Drift

# Option 1: Reset schema to main and re-run only PR migrations
git checkout main -- db/schema.rb
bin/rails db:migrate

# Option 2: If local DB has extra migrations, reset and only update version
git checkout main -- db/schema.rb
# Manually edit the version line to match PR's migration

Output Format

Clean PR

✅ Schema changes match PR migrations

Migrations in PR:
- 20260205045101_add_spam_category_template.rb

Schema changes verified:
- Version: 2026_01_29_133857 → 2026_02_05_045101 ✓
- No unrelated tables/columns/indexes ✓

Drift Detected

⚠️ SCHEMA DRIFT DETECTED

Migrations in PR:
- 20260205045101_add_spam_category_template.rb

Unrelated schema changes found:

1. **users table** - Extra columns not in PR migrations:
   - `openai_api_key` (text)
   - `anthropic_api_key` (text)
   - `gemini_api_key` (text)
   - `complimentary_access` (boolean)

2. **Extra index:**
   - `index_users_on_complimentary_access`

**Action Required:**
Run `git checkout main -- db/schema.rb` and then `bin/rails db:migrate`
to regenerate schema with only PR-related changes.

Integration with Other Reviewers

This agent should be run BEFORE other database-related reviewers:

Run schema-drift-detector first to ensure clean schema
Then run data-migration-expert for migration logic review
Then run data-integrity-guardian for integrity checks

Catching drift early prevents wasted review time on unrelated changes.

schema-drift-detector

The Problem