database-change-management

majiayu000 的头像作者 majiayu000

规划和实施安全的数据库架构更改,包括迁移、索引和回填。在创建表、添加列、优化查询或管理 Eloquent 关系时使用。数据库管理代理专有。

📖 中文导读:规划和实施安全的数据库架构更改,包括迁移、索引和回填。在创建表、添加列、优化查询或管理 Eloquent 关系时使用。数据库管理代理专有。 以下正文为该技能的原始 SKILL.md 内容(英文),上方安装命令可直接使用。

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_context7

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"