database-change-management

name	database-change-management
description	Plan and implement safe database schema changes including migrations, indexes, and backfills. Use when creating tables, adding columns, optimizing queries, or managing Eloquent/SQLAlchemy relationships. EXCLUSIVE to database-admin agent.
allowed-tools	Read, Edit, Bash, Grep, Glob, Write, mcp_codex-bridge, mcp_gemini-bridge, mcp_context7, mcp_playwright, mcp_zread, mcp_web-search-prime, mcp_web-reader, mcp_zai-mcp-server, mcp_open-bridge

Database Change Management

Exclusive to: database-admin agent

📚 Context7 (Memory) — Up-to-Date Docs

Lookup latest ORM patterns before implementing:

mcp_context7_resolve-library-id(libraryName="laravel", query="eloquent relationships")
mcp_context7_query-docs(libraryId="/laravel/docs", query="migrations foreign keys")

Validation Loop (MANDATORY)

Every migration MUST pass this verification sequence:

php artisan migrate        # Run migration
php artisan migrate:rollback   # Verify rollback works
php artisan migrate        # Re-run migration
composer test             # All tests still pass

Do NOT complete until all steps succeed.

Instructions

1. Audit existing migrations and models for current schema
2. Design migration with reversible down() method
3. Run migrate and rollback to validate locally
4. Update Eloquent model ($fillable, $casts, relationships)
5. Document any required backfills or deployment order

Safe Migration Patterns

Adding Columns

// ✅ Safe: nullable or with default
$table->string('field')->nullable();
$table->boolean('active')->default(true);

// ❌ Unsafe: NOT NULL without default
$table->string('field');

Adding Indexes

// Index for WHERE/ORDER BY columns
$table->index('user_id');
$table->index(['status', 'created_at']);

Zero-Downtime Strategy

1. Add — Add nullable column
2. Backfill — Populate data in chunks
3. Enforce — Make column required

Backfill Pattern

Model::query()
    ->whereNull('new_field')
    ->chunkById(1000, function ($items) {
        foreach ($items as $item) {
            $item->update(['new_field' => $value]);
        }
    });

Eloquent Relationships

One-to-Many

// User has many Posts
public function posts(): HasMany
{
    return $this->hasMany(Post::class);
}

Many-to-Many

public function tags(): BelongsToMany
{
    return $this->belongsToMany(Tag::class)
        ->withTimestamps();
}

Query Optimization

Eager Loading

// ❌ N+1 Problem
foreach (Post::all() as $post) {
    echo $post->user->name;
}

// ✅ Eager Load
Post::with('user')->get();

Index Strategy

Query Pattern	Index
WHERE user_id = ?	index('user_id')
WHERE status = ? AND date > ?	index(['status', 'date'])

Common Pitfalls

• ❌ NOT NULL without default on existing table
• ❌ Dropping columns without backup
• ❌ Missing indexes on foreign keys
• ❌ Missing down() method

Verification

php artisan migrate
php artisan migrate:rollback
php artisan migrate

Examples

• "Add an index to speed up dashboard query"
• "Add a nullable column then backfill safely"