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:

bash

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

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

Safe Migration Patterns

Adding Columns

php

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

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

Adding Indexes

php

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

Zero-Downtime Strategy

Add — Add nullable column
Backfill — Populate data in chunks
Enforce — Make column required

Backfill Pattern

php

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

Eloquent Relationships

One-to-Many

php

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

Many-to-Many

php

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

Query Optimization

Eager Loading

php

// ❌ 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