3.4 Database Migration Documentation

Overview

The KMP database schema is managed through a comprehensive migration system using Phinx (via CakePHP’s Migration plugin). This documentation covers the migration history, patterns, and evolution of the database schema from initial setup to current state.

Migration Framework

Technology Stack

Migration Naming Convention

Migrations follow the format: YYYYMMDDHHIISS_DescriptiveName.php

// Example migration filename
20230511170042_Init.php           // Initial schema creation
20241204160759_Warrants.php       // Warrant system addition
20250227173909_AddScopeToMemberRoles.php  // Feature enhancement

Migration History

1. Initial Schema (20230511170042_Init.php)

Purpose: Establish core KMP database schema with foundational tables.

Key Components:

Major Tables Created:

// Core entity tables
'members'           // Member profiles and authentication
'branches'          // Organizational hierarchy (nested set model)
'roles'             // Permission grouping
'permissions'       // Granular access rights
'roles_permissions' // Many-to-many junction table
'member_roles'      // Temporal role assignments
'notes'             // Polymorphic notes system
'app_settings'      // Application configuration

Advanced Features:

2. View Members Permission (20241001141705_AddViewMembersPermission.php)

Purpose: Enhance permission system with member viewing capabilities.

Changes:

// Permission addition
INSERT INTO permissions (name, require_active_membership, ...) 
VALUES ('view members', true, ...);

// Role assignment
INSERT INTO roles_permissions (role_id, permission_id)
VALUES (getRoleId('Member'), getPermissionId('view members'));

Impact: Enables granular control over member directory access.

3. Member Profile Enhancements (20241009145957_AddTitlePronounsPronunciationToMembers.php)

Purpose: Expand member profiles with additional personal information.

Schema Changes:

ALTER TABLE members ADD COLUMN title VARCHAR(255) NULL;
ALTER TABLE members ADD COLUMN pronouns VARCHAR(255) NULL;
ALTER TABLE members ADD COLUMN pronunciation VARCHAR(255) NULL;

Features Added:

4. Settings Schema Evolution (20241024125311_ChangeAppSettingValueToText.php)

Purpose: Improve application settings storage flexibility.

Schema Change:

ALTER TABLE app_settings MODIFY COLUMN value TEXT NULL;

Rationale:

5. Comprehensive Warrant System (20241204160759_Warrants.php)

Purpose: Implement complete warrant management system for authorization tracking.

New Tables:

'warrant_periods'  // Temporal warrant validity periods
'warrants'         // Individual warrant assignments

Key Features:

6. Member Warrant Eligibility (20241207172311_AddWarrantableToMembers.php)

Purpose: Add warrant eligibility tracking to member profiles.

Schema Change:

ALTER TABLE members ADD COLUMN warrantable BOOLEAN DEFAULT false;

Usage: Controls which members are eligible for warrant assignment.

7. Association Field Refactoring (20241225192403_RefactorAgnosticJoinFields.php)

Purpose: Standardize polymorphic association field naming across the system.

Changes:

// notes table
topic_model → entity_type
topic_id → entity_id

// member_roles table  
granting_model → entity_type
granting_id → entity_id

Impact: Consistent naming pattern for polymorphic relationships improves code maintainability and reduces confusion.

8. Branch Type Classification (20241231164137_AddTypeToBranches.php)

Purpose: Add organizational classification to branch structure.

Schema Change:

ALTER TABLE branches ADD COLUMN type VARCHAR(50) NULL;

Branch Types:

9. Required Application Settings (20250108190610_AddRequiredToAppSetting.php)

Purpose: Implement validation for critical application configuration.

Schema Change:

ALTER TABLE app_settings ADD COLUMN required BOOLEAN DEFAULT false;

Impact: Ensures essential configuration values are properly maintained.

10. Member Role Scoping (20250227173909_AddScopeToMemberRoles.php)

Purpose: Add contextual scoping to role assignments and permission evaluation.

Schema Changes:

// Add branch scoping to member roles
ALTER TABLE member_roles ADD COLUMN branch_id INT(11) NULL;
ALTER TABLE member_roles ADD FOREIGN KEY (branch_id) REFERENCES branches(id);

// Add permission scoping rules  
ALTER TABLE permissions ADD COLUMN scoping_rule VARCHAR(255) NOT NULL DEFAULT 'Global';

Scope Examples:

Impact: Enables branch-specific role assignments and permission scoping for better organizational data isolation.

11. Multi-Site Domain Support (20250227230531_AddDomainToBranch.php)

Purpose: Enable multi-domain hosting for different branches.

Schema Change:

ALTER TABLE branches ADD COLUMN domain VARCHAR(255) NULL;

Use Cases:

12. Permission Policy Framework (20250328010857_PermissionPolicies.php)

Purpose: Implement fine-grained authorization policies.

New Table:

CREATE TABLE permission_policies (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    permission_id INT(11) NOT NULL,
    policy_class VARCHAR(255) NOT NULL,
    policy_method VARCHAR(255) NOT NULL,
    
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);

Features:

13. Settings Consolidation (20250415203922_ConvertAppSettingsToSingleRecord.php)

Purpose: Reserved for future application settings optimization.

Status: Migration created but functionality disabled pending further analysis.

Changes:

// Migration exists but returns early - no changes implemented
public function change(): void
{
    return; // Disabled - optimization pending
}

Note: This migration was created to optimize application settings but was disabled before deployment to allow for additional design consideration.

Migration Patterns

1. Schema Evolution Pattern

class ExampleMigration extends BaseMigration
{
    public bool $autoId = false;  // Control auto-increment behavior
    
    public function up()
    {
        // Forward migration logic
        $this->table('example_table')
            ->addColumn('new_field', 'string', [
                'limit' => 255,
                'null' => false,
                'default' => 'default_value'
            ])
            ->addIndex(['new_field'])
            ->update();
    }
    
    public function down()
    {
        // Rollback logic (when supported)
        $this->table('example_table')
            ->removeColumn('new_field')
            ->update();
    }
}

2. Data Migration Pattern

public function up()
{
    // Schema changes first
    $this->table('members')
        ->addColumn('new_status', 'string', ['limit' => 20])
        ->update();
    
    // Data transformation
    $this->execute("
        UPDATE members 
        SET new_status = CASE 
            WHEN old_field = 'active' THEN 'verified'
            WHEN old_field = 'inactive' THEN 'pending'
            ELSE 'unknown'
        END
    ");
    
    // Remove old column
    $this->table('members')
        ->removeColumn('old_field')
        ->update();
}

3. Seed Integration Pattern

require_once __DIR__ . '/../Seeds/InitWarrantsSeed.php';

class Warrants extends BaseMigration
{
    public function up()
    {
        // Create tables
        $this->createWarrantTables();
        
        // Seed initial data
        $seed = new InitWarrantsSeed();
        $seed->setAdapter($this->getAdapter());
        $seed->run();
    }
}

Migration Best Practices

1. Schema Design

2. Migration Safety

3. Performance Considerations

4. Documentation Standards

Migration Execution

Development Environment

# Generate new migration
bin/cake bake migration AddNewFeature

# Run pending migrations
bin/cake migrations migrate

# Check migration status
bin/cake migrations status

# Rollback last migration (if supported)
bin/cake migrations rollback

Production Environment

# Pre-migration backup
mysqldump kmp_production > backup_before_migration.sql

# Run migrations with confirmation
bin/cake migrations migrate --no-interaction

# Verify migration success
bin/cake migrations status

# Run post-migration verification tests
bin/cake test

Migration Dependencies

Plugin Migrations

Plugin migrations are managed separately but coordinate with core schema:

# Officers plugin migrations
bin/cake migrations migrate -p Officers

# Awards plugin migrations  
bin/cake migrations migrate -p Awards

# Activities plugin migrations
bin/cake migrations migrate -p Activities

Migration Order

  1. Core Schema: Foundation tables and relationships
  2. Plugin Tables: Plugin-specific table creation
  3. Cross-Plugin Relations: Foreign keys between core and plugin tables
  4. Data Population: Initial seed data and configuration
  5. Index Optimization: Performance indexes and constraints

Schema Validation

Automated Validation

// Schema consistency checks
bin/cake schema_check validate

// Foreign key integrity verification
bin/cake schema_check foreign_keys

// Index optimization analysis
bin/cake schema_check indexes

Manual Validation

Troubleshooting

Common Migration Issues

  1. Foreign Key Conflicts:
    • Check for orphaned records before adding constraints
    • Use data cleanup migrations to resolve inconsistencies
  2. Index Creation Failures:
    • Verify column data types and lengths
    • Check for duplicate or conflicting indexes
  3. Data Type Mismatches:
    • Ensure data compatibility before type changes
    • Use gradual migration approach for large tables
  4. Plugin Integration Issues:
    • Coordinate plugin and core migrations
    • Verify plugin loading order and dependencies

Recovery Procedures

  1. Migration Rollback: Use database backup and selective rollback
  2. Data Recovery: Restore from pre-migration backups
  3. Partial Failure: Complete manual data corrections and re-run
  4. Schema Corruption: Restore schema from migration history

Future Migration Strategy

Planned Enhancements

Migration Framework Evolution

This migration documentation reflects the complete evolution of the KMP database schema. For specific implementation details, refer to the individual migration files in app/config/Migrations/ and coordinate with the Database Schema Documentation.