CakePHP Migration Checker

A specialized skill for validating CakePHP migration files and ensuring schema consistency across migrations, fixtures, and actual database schemas.

Core Validation Areas

1. Migration File Structure Validation

Check migration files for:

• Proper namespace structure
• Table creation/modification syntax
• Column definitions with correct types
• Index and foreign key definitions

Migration namespace structure (multi-tier pattern):

config/Migrations/
├── [ProjectDefault]/    # Project account schema
├── [AppDefault]/        # Application account schema
└── [AppClient]/         # Application client schema (multi-tenant)

2. Migration-Fixture Consistency

Validate that fixtures match migration schemas:

• All migration columns exist in fixtures
• Fixture column types match migration definitions
• Required (NOT NULL) columns have values
• Foreign key references are valid

Example validation:

// Migration defines:
$table->addColumn('login_name', 'text', ['null' => false]);

// Fixture must have:
'login_name' => 'test_user',  // NOT NULL, so value required

3. Multi-Tenant Database Patterns

For multi-tenant projects:

• Database naming pattern: [app_prefix]_company_%d
• Connection pattern validation
• Dynamic connection component usage

Check for correct patterns:

// ✅ CORRECT: Dynamic connection
$conn = $this->MessageDeliveryDbAccessor->getUserMessageDeliveryDbConnection($companyId);

// ❌ WRONG: Hardcoded database name
$conn = ConnectionManager::get('[app_prefix]_company_9999');

4. Schema Cache Management

Detect when schema cache clearing is needed:

• After migration modifications
• When fixture-migration mismatches occur
• Following database structure changes

Suggest cache clearing:

rm -rf tmp/cache/models/*

5. Test Database Configuration

Validate test database setup:

• Test database naming conventions
• Proper fixture namespacing
• Connection configuration

Expected test databases:

test_default_test_session_001  # Account schema
test_client_9999001            # Client schema
test_account                   # Core account

Validation Process

When checking migrations:

1. Parse migration files - Extract table and column definitions
2. Locate corresponding fixtures - Find fixtures for same tables
3. Compare schemas - Identify mismatches
4. Check multi-tenant patterns - Validate dynamic connections
5. Verify test setup - Ensure test databases configured

Output Format

Migration: [Migration file path]
Table: [Table name]
Status: [✅ CONSISTENT | ❌ INCONSISTENT | ⚠️ WARNING]

Issues found:
- Missing in fixture: [column_name]
- Type mismatch: [column] expects [type1], fixture has [type2]
- Null constraint violation: [column]

Recommendations:
- Clear schema cache: rm -rf tmp/cache/models/*
- Update fixture: [specific changes needed]

Examples

Example 1: Column missing in fixture

Migration: config/Migrations/[AppClient]/20210201000000_ClientInitial.php
Table: applications
Status: ❌ INCONSISTENT

Issues found:
- Missing in fixture: approved_at
- Missing in fixture: approved_by

Recommendations:
- Add to ApplicationsFixture.php:
  'approved_at' => null,
  'approved_by' => null,

Example 2: Type mismatch

Migration: config/Migrations/[AppDefault]/20210201000000_DefaultInitial.php
Table: company_users
Status: ❌ INCONSISTENT

Issues found:
- Type mismatch: login_name expects TEXT, fixture has VARCHAR(255)

Recommendations:
- Update fixture to use text type
- Clear schema cache after fix

Example 3: Multi-tenant pattern violation

File: src/Model/Table/ApplicationsTable.php
Status: ⚠️ WARNING

Issues found:
- Hardcoded database connection found
- Should use MessageDeliveryDbAccessorComponent

Recommendations:
- Use dynamic connection:
  $conn = $this->MessageDeliveryDbAccessor->getUserMessageDeliveryDbConnection($companyId);

CakePHP-Specific Rules

Column Type Mappings

Migration Type -> Fixture Type
text          -> string (unlimited)
string        -> string (with length)
integer       -> integer
datetime      -> string (ISO format)
boolean       -> boolean (0/1 in MySQL)
decimal       -> string or float

Naming Conventions

• Table names: plural, snake_case
• Foreign keys: singular_id
• Join tables: first_table_second_table
• Timestamps: created, modified

Reserved Columns

• id - Primary key (integer)
• created - Creation timestamp
• modified - Update timestamp
• del_flg - Soft delete flag

Integration Notes

This skill works best with:

• fixture-generator skill for creating compliant fixtures
• migration-validator agent for comprehensive checks
• Projects using CakePHP 4.x migrations
• Multi-database architectures

Usage Scenarios

1. Before running tests - Ensure schema consistency
2. After modifying migrations - Validate fixture updates
3. During PR reviews - Check migration-fixture alignment
4. Version upgrades - Verify schema compatibility