codecanyon-rest-api-for-perfex-crm/api/MIGRATION_README.md

API Module Database Migrations

This directory contains scripts and tools for managing database migrations for the Perfex CRM API module.

📁 Files Overview

• run_migrations.php - Basic migration runner script
• migration_runner.php - Advanced migration runner with rollback support
• migration_web_interface.php - Web-based migration interface
• migration_template.php - Template for creating new migrations
• MIGRATION_README.md - This documentation file

🚀 Quick Start

Option 1: Web Interface (Recommended)

1. Access https://yourdomain.com/modules/api/migration_web_interface.php
2. Login as administrator
3. Click "Run Upgrade" to apply all pending migrations

Option 2: Command Line

cd /path/to/perfex/modules/api

# Run all pending migrations
php migration_runner.php

# Run migrations up to specific version
php migration_runner.php --target=205

# Rollback to specific version
php migration_runner.php --rollback --target=200

Option 3: Basic Runner

cd /path/to/perfex/modules/api
php run_migrations.php

📋 Available Migrations

Version	Description	Status
101	Initial API setup	✅ Applied
102	Basic API tables	✅ Applied
103	User authentication	✅ Applied
200	Rate limiting system	✅ Applied
201	API logging improvements	✅ Applied
202	Custom fields support	✅ Applied
203	Webhook system	✅ Applied
204	API key management	✅ Applied
205	Performance optimizations	✅ Applied
206	Security enhancements	✅ Applied
207	Error handling improvements	✅ Applied
208	Documentation updates	✅ Applied
209	Testing framework	✅ Applied
210	API metrics dashboard	✅ Applied
211	Rate limiting columns	✅ Applied

🔧 Creating New Migrations

1. Copy migration_template.php to a new file
2. Name it {version}_version_{version}.php (e.g., 212_version_212.php)
3. Replace {VERSION} with the actual version number
4. Implement the up() and down() methods
5. Test the migration on a development environment
6. Run the migration using one of the scripts above

Migration Template Structure

<?php
class Migration_Version_{VERSION} extends App_module_migration
{
    protected $db;

    public function __construct()
    {
        parent::__construct();
        $CI = &get_instance();
        $CI->load->database();
        $this->db = $CI->db;
    }

    public function up()
    {
        // Your upgrade logic here
        // - Create tables
        // - Add columns
        // - Insert data
        // - Create indexes
    }

    public function down()
    {
        // Reverse the up() changes
        // - Drop tables
        // - Remove columns
        // - Delete data
        // - Drop indexes
    }
}

🛠️ Migration Best Practices

Database Operations

• Always check if fields/tables exist before creating/altering
• Use db_prefix() for all table names
• Include both up() and down() methods
• Test migrations on development data first

Version Numbering

• Use incremental version numbers (101, 102, 103, etc.)
• Major version jumps (200, 201) for significant changes
• Keep version numbers unique across all migrations

Safety Measures

• Backup database before running migrations
• Test rollback functionality
• Keep migrations atomic (one change per migration)
• Document what each migration does

🔍 Troubleshooting

Migration Fails

1. Check PHP error logs
2. Verify database permissions
3. Ensure all dependencies are installed
4. Check if tables already exist

Rollback Issues

1. Some operations cannot be reversed (data loss)
2. Foreign key constraints may prevent drops
3. Manual intervention may be required

Permission Errors

1. Ensure PHP has database write permissions
2. Check file system permissions for migration files
3. Verify admin access for web interface

📊 Migration Tracking

Migrations are tracked in the migrations table:

SELECT * FROM tblmigrations WHERE module = 'api' ORDER BY version DESC;

Each successful migration updates this table with:

• module: 'api'
• version: migration version number

🔄 Rollback Procedures

Emergency Rollback

# Rollback to previous version
php migration_runner.php --rollback

# Rollback to specific version
php migration_runner.php --rollback --target=200

Manual Rollback

If automatic rollback fails:

1. Identify the migration that caused issues
2. Manually reverse the database changes
3. Update the migrations table to reflect the rollback
4. Test API functionality

📈 Performance Considerations

Large Datasets

• Use LIMIT clauses for large table operations
• Consider running migrations during off-peak hours
• Monitor database performance during migration

Indexes

• Add indexes for frequently queried columns
• Remove unused indexes to improve performance
• Consider composite indexes for complex queries

🔐 Security Notes

• Never commit database credentials to version control
• Use environment variables for sensitive configuration
• Test migrations on staging environments first
• Backup production databases before deployment

📞 Support

For migration issues:

1. Check this documentation first
2. Review migration error logs
3. Test on development environment
4. Contact development team with specific error details

📝 Changelog

Version 2.1.0

• Added advanced migration runner with rollback support
• Created web-based migration interface
• Improved error handling and logging
• Added migration templates and documentation

Version 2.0.8

• Initial migration system implementation
• Basic upgrade functionality
• Rate limiting and security enhancements </xai:function_call: 1