Database Change Management

Exclusive to: database-admin agent

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

Lookup latest ORM patterns before implementing:

code

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

bash

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"