MySQL migration governance, naming, safety
YYYY_MM_DD_HHMMSS_verb_noun_table.php
Verbs: create, add, modify, drop, rename
2024_01_15_100000_create_users_table.php
2024_01_15_100001_create_projects_table.php
2024_01_15_100002_create_phases_table.php
2024_01_15_100003_add_budget_to_projects_table.php
up() must have a corresponding down()Schema::hasTable() / Schema::hasColumn() guardsutf8mb4 with utf8mb4_unicode_ci collationreturn new class extends Migration
{
public function up(): void
{
Schema::create('projects', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->text('description')->nullable();
$table->foreignId('customer_id')->constrained('users')->cascadeOnDelete();
$table->foreignId('contractor_id')->nullable()->constrained('users')->nullOnDelete();
$table->string('status')->default('draft');
$table->decimal('budget', 12, 2)->default(0);
$table->string('location');
$table->date('start_date')->nullable();
$table->date('end_date')->nullable();
$table->timestamps();
$table->softDeletes();
$table->index('status');
$table->index('customer_id');
});
}
public function down(): void
{
Schema::dropIfExists('projects');
}
};
Before generating a migration, AI must assess:
| Operation | Risk Level | Lock Duration |
|---|---|---|
| CREATE TABLE | Low | Minimal |
| ADD COLUMN (nullable) | Low | Minimal |
| ADD COLUMN (default) | Medium | Table copy on MySQL < 8 |
| ADD INDEX | Medium | Can be slow on large tables |
| MODIFY COLUMN | High | Full table rewrite |
| DROP COLUMN | Medium | Table rewrite |
| RENAME COLUMN | Medium | Brief lock |
For High risk operations on production tables with >100k rows, AI must flag and suggest online schema change or batching strategy.
$table->foreignId() with ->constrained()->cascadeOnDelete() for child records->nullOnDelete() for optional relationships->restrictOnDelete() without explicit justification