Implement database migration automation


# Task: Implement database migration automation

## Description

Implement a comprehensive database migration automation system for the Coolify Enterprise platform that ensures safe, reliable, and auditable database schema changes across development, staging, and production environments. This system provides automated migration validation, pre-flight checks, backup creation, automatic execution with rollback capability, and comprehensive audit logging for all database schema changes.

Modern multi-environment deployments require robust migration management to prevent downtime, data loss, and deployment failures. Manual migration execution is error-prone and lacks safety mechanisms. This task creates an intelligent migration orchestration system that validates migrations before execution, creates automatic backups, handles rollbacks on failures, and provides detailed audit trails for compliance and debugging.

**Core Capabilities:**

1. **Pre-Migration Validation**: Syntax checking, dependency analysis, destructive change detection
2. **Automated Backup System**: Database snapshots before migration execution with point-in-time recovery
3. **Intelligent Execution**: Batched migrations with transaction support and progress tracking
4. **Automatic Rollback**: Failure detection with automatic state restoration
5. **Audit Logging**: Complete history of all migrations with user attribution and timestamps
6. **Multi-Environment Support**: Environment-specific migration strategies (dev, staging, production)
7. **Zero-Downtime Migrations**: Support for online schema changes with minimal locking

**Integration Points:**

- **CI/CD Pipeline**: Automated migration execution during deployment workflows (Task 89)
- **Monitoring Dashboards**: Migration status and health metrics display (Task 91)
- **Alert System**: Notifications for migration failures and warnings
- **Backup System**: Integration with PostgreSQL backup tools and S3 storage
- **Organization Context**: Organization-scoped migrations for multi-tenant architecture

**Why This Task Is Critical:**

Database migrations are the riskiest part of any deployment. A failed migration can cause complete application outages, data corruption, or irreversible data loss. Manual migration execution lacks safety checks, audit trails, and rollback capabilities. This automation system transforms migrations from a high-risk manual process into a reliable, auditable, automated workflow with comprehensive safety mechanisms. It's essential for production-grade enterprise deployments where uptime, data integrity, and compliance are non-negotiable.

**Real-World Problem Solved:**

- **Problem**: Developer runs migrations manually, forgets to backup, migration fails halfway, database is in inconsistent state, no easy rollback
- **Solution**: System automatically backs up before migration, validates syntax, detects failures, rolls back automatically, logs everything for audit trail

## Acceptance Criteria

- [ ] Migration validation system implemented with syntax checking and dependency analysis
- [ ] Pre-migration checks include: syntax validation, dependency ordering, destructive change detection
- [ ] Automatic database backup before migration execution (PostgreSQL pg_dump)
- [ ] Backup storage in S3 with versioning and retention policies
- [ ] Migration execution with transaction support and progress tracking
- [ ] Automatic rollback on failures with state restoration
- [ ] Manual rollback command for reverting successful migrations
- [ ] Audit logging for all migration events (executed, failed, rolled back)
- [ ] Migration status tracking (pending, running, completed, failed, rolled back)
- [ ] Environment-specific migration strategies (skip confirmation in dev, require approval in prod)
- [ ] Dry-run mode for testing migrations without execution
- [ ] Support for data migrations with validation hooks
- [ ] Migration locking to prevent concurrent execution
- [ ] Email/Slack notifications for migration events
- [ ] Artisan commands for all migration operations
- [ ] Web UI for migration management and history
- [ ] Comprehensive test coverage (unit, integration, feature tests)
- [ ] Documentation for migration best practices and troubleshooting

## Technical Details

### File Paths

**Service Layer:**
- `/home/topgun/topgun/app/Services/Enterprise/MigrationAutomationService.php` (core service)
- `/home/topgun/topgun/app/Contracts/MigrationAutomationServiceInterface.php` (interface)
- `/home/topgun/topgun/app/Services/Enterprise/DatabaseBackupService.php` (backup service)
- `/home/topgun/topgun/app/Contracts/DatabaseBackupServiceInterface.php` (backup interface)

**Artisan Commands:**
- `/home/topgun/topgun/app/Console/Commands/MigrateWithBackup.php` (automated migration)
- `/home/topgun/topgun/app/Console/Commands/ValidateMigrations.php` (validation)
- `/home/topgun/topgun/app/Console/Commands/RollbackMigration.php` (rollback)
- `/home/topgun/topgun/app/Console/Commands/MigrationStatus.php` (status)

**Models:**
- `/home/topgun/topgun/app/Models/MigrationLog.php` (migration audit log)
- `/home/topgun/topgun/app/Models/DatabaseBackup.php` (backup tracking)

**Controllers:**
- `/home/topgun/topgun/app/Http/Controllers/Enterprise/MigrationController.php` (web UI)

**Vue Components:**
- `/home/topgun/topgun/resources/js/Components/Enterprise/Migration/MigrationManager.vue` (UI)
- `/home/topgun/topgun/resources/js/Components/Enterprise/Migration/MigrationHistory.vue` (history)

**Configuration:**
- `/home/topgun/topgun/config/migration-automation.php` (configuration)

**Jobs:**
- `/home/topgun/topgun/app/Jobs/ExecuteMigrationJob.php` (async migration execution)
- `/home/topgun/topgun/app/Jobs/BackupDatabaseJob.php` (async backup)

### Database Schema

**Migration Logs Table:**

```php
<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('migration_logs', function (Blueprint $table) {
            $table->id();
            $table->string('migration_name');
            $table->integer('batch')->nullable();
            $table->enum('status', ['pending', 'running', 'completed', 'failed', 'rolled_back'])->default('pending');
            $table->enum('environment', ['development', 'staging', 'production']);
            $table->foreignId('executed_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamp('started_at')->nullable();
            $table->timestamp('completed_at')->nullable();
            $table->integer('duration_seconds')->nullable();
            $table->text('output')->nullable();
            $table->text('error_message')->nullable();
            $table->foreignId('backup_id')->nullable()->constrained('database_backups')->nullOnDelete();
            $table->json('metadata')->nullable(); // migration file hash, size, etc.
            $table->timestamps();

            $table->index(['migration_name', 'status']);
            $table->index(['status', 'environment']);
            $table->index('created_at');
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('migration_logs');
    }
};
```

**Database Backups Table:**

```php
<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('database_backups', function (Blueprint $table) {
            $table->id();
            $table->uuid('uuid')->unique();
            $table->string('database_name');
            $table->string('backup_type')->default('pre-migration'); // pre-migration, scheduled, manual
            $table->bigInteger('file_size')->nullable(); // bytes
            $table->string('storage_path'); // S3 path
            $table->string('local_path')->nullable(); // temporary local path
            $table->string('compression')->default('gzip'); // gzip, none
            $table->string('checksum'); // SHA256 checksum
            $table->enum('status', ['pending', 'in_progress', 'completed', 'failed'])->default('pending');
            $table->timestamp('started_at')->nullable();
            $table->timestamp('completed_at')->nullable();
            $table->integer('duration_seconds')->nullable();
            $table->text('error_message')->nullable();
            $table->timestamp('expires_at')->nullable(); // retention policy
            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->json('metadata')->nullable(); // PostgreSQL version, table count, etc.
            $table->timestamps();

            $table->index(['database_name', 'status']);
            $table->index('created_at');
            $table->index('expires_at');
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('database_backups');
    }
};
```

### Service Interface

**File:** `app/Contracts/MigrationAutomationServiceInterface.php`

```php
<?php

namespace App\Contracts;

use App\Models\MigrationLog;
use Illuminate\Support\Collection;

interface MigrationAutomationServiceInterface
{
    /**
     * Validate pending migrations before execution
     *
     * @return array Validation results with errors/warnings
     */
    public function validatePendingMigrations(): array;

    /**
     * Execute pending migrations with automatic backup
     *
     * @param bool $force Skip confirmation prompts
     * @param bool $dryRun Simulate without executing
     * @return Collection<MigrationLog>
     */
    public function executeMigrations(bool $force = false, bool $dryRun = false): Collection;

    /**
     * Rollback last migration batch
     *
     * @param int|null $steps Number of steps to rollback
     * @param bool $force Skip confirmation prompts
     * @return bool
     */
    public function rollbackMigrations(?int $steps = null, bool $force = false): bool;

    /**
     * Get migration status for all migrations
     *
     * @return Collection Status information
     */
    public function getMigrationStatus(): Collection;

    /**
     * Check if there are pending migrations
     *
     * @return bool
     */
    public function hasPendingMigrations(): bool;

    /**
     * Get migration history with logs
     *
     * @param int $limit Number of records
     * @return Collection<MigrationLog>
     */
    public function getMigrationHistory(int $limit = 50): Collection;

    /**
     * Create backup before migration
     *
     * @return \App\Models\DatabaseBackup
     */
    public function createPreMigrationBackup(): \App\Models\DatabaseBackup;

    /**
     * Restore database from backup
     *
     * @param int $backupId
     * @return bool
     */
    public function restoreFromBackup(int $backupId): bool;

    /**
     * Detect destructive migrations (DROP, TRUNCATE, etc.)
     *
     * @param string $migrationPath
     * @return array Destructive operations found
     */
    public function detectDestructiveChanges(string $migrationPath): array;
}
```

**Backup Service Interface:**

```php
<?php

namespace App\Contracts;

use App\Models\DatabaseBackup;

interface DatabaseBackupServiceInterface
{
    /**
     * Create database backup using pg_dump
     *
     * @param string $type Backup type (pre-migration, scheduled, manual)
     * @return DatabaseBackup
     */
    public function createBackup(string $type = 'manual'): DatabaseBackup;

    /**
     * Restore database from backup
     *
     * @param DatabaseBackup $backup
     * @return bool
     */
    public function restoreBackup(DatabaseBackup $backup): bool;

    /**
     * Delete old backups based on retention policy
     *
     * @return int Number of backups deleted
     */
    public function cleanupExpiredBackups(): int;

    /**
     * Verify backup integrity
     *
     * @param DatabaseBackup $backup
     * @return bool
     */
    public function verifyBackup(DatabaseBackup $backup): bool;

    /**
     * Get backup size estimate
     *
     * @return int Bytes
     */
    public function estimateBackupSize(): int;
}
```

### Service Implementation

**File:** `app/Services/Enterprise/MigrationAutomationService.php`

```php
<?php

namespace App\Services\Enterprise;

use App\Contracts\MigrationAutomationServiceInterface;
use App\Contracts\DatabaseBackupServiceInterface;
use App\Models\MigrationLog;
use App\Models\DatabaseBackup;
use App\Notifications\MigrationFailedNotification;
use App\Notifications\MigrationSuccessNotification;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Artisan;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Collection;
use Symfony\Component\Process\Process;

class MigrationAutomationService implements MigrationAutomationServiceInterface
{
    private const MIGRATION_LOCK_KEY = 'migration:executing';
    private const LOCK_TIMEOUT = 3600; // 1 hour

    public function __construct(
        private DatabaseBackupServiceInterface $backupService
    ) {}

    /**
     * Validate pending migrations
     */
    public function validatePendingMigrations(): array
    {
        $validationResults = [
            'valid' => true,
            'errors' => [],
            'warnings' => [],
            'pending_count' => 0,
        ];

        $pendingMigrations = $this->getPendingMigrationFiles();
        $validationResults['pending_count'] = count($pendingMigrations);

        if (empty($pendingMigrations)) {
            return $validationResults;
        }

        foreach ($pendingMigrations as $migration) {
            // Check syntax
            $syntaxCheck = $this->validateMigrationSyntax($migration);
            if (!$syntaxCheck['valid']) {
                $validationResults['valid'] = false;
                $validationResults['errors'][] = "Syntax error in {$migration}: {$syntaxCheck['error']}";
            }

            // Check for destructive operations
            $destructive = $this->detectDestructiveChanges($migration);
            if (!empty($destructive)) {
                $validationResults['warnings'][] = "Destructive operations in {$migration}: " . implode(', ', $destructive);
            }

            // Check dependencies
            $dependencyCheck = $this->checkMigrationDependencies($migration);
            if (!$dependencyCheck['valid']) {
                $validationResults['valid'] = false;
                $validationResults['errors'][] = "Dependency error in {$migration}: {$dependencyCheck['error']}";
            }
        }

        return $validationResults;
    }

    /**
     * Execute pending migrations with backup
     */
    public function executeMigrations(bool $force = false, bool $dryRun = false): Collection
    {
        // Acquire migration lock
        if (!$this->acquireMigrationLock()) {
            throw new \RuntimeException('Migration is already in progress');
        }

        try {
            Log::info('Starting migration execution', [
                'force' => $force,
                'dry_run' => $dryRun,
                'environment' => app()->environment(),
            ]);

            // Validate migrations
            $validation = $this->validatePendingMigrations();
            if (!$validation['valid'] && !$force) {
                throw new \RuntimeException('Migration validation failed: ' . implode('; ', $validation['errors']));
            }

            $migrations = collect();

            if ($dryRun) {
                Log::info('Dry run mode - no actual migration execution');
                return $this->simulateMigrations();
            }

            // Create backup
            $backup = $this->createPreMigrationBackup();
            Log::info('Pre-migration backup created', ['backup_id' => $backup->id]);

            // Get pending migrations
            $pendingMigrations = $this->getPendingMigrationFiles();

            foreach ($pendingMigrations as $migrationFile) {
                $migrationLog = $this->executeSingleMigration($migrationFile, $backup);
                $migrations->push($migrationLog);

                if ($migrationLog->status === 'failed') {
                    Log::error('Migration failed, initiating rollback', [
                        'migration' => $migrationFile,
                        'error' => $migrationLog->error_message,
                    ]);

                    $this->handleMigrationFailure($migrationLog, $backup);
                    break;
                }
            }

            $successCount = $migrations->where('status', 'completed')->count();
            $failCount = $migrations->where('status', 'failed')->count();

            Log::info('Migration execution completed', [
                'total' => $migrations->count(),
                'success' => $successCount,
                'failed' => $failCount,
            ]);

            // Send notifications
            if ($failCount > 0) {
                $this->notifyMigrationFailure($migrations);
            } else {
                $this->notifyMigrationSuccess($migrations);
            }

            return $migrations;

        } finally {
            $this->releaseMigrationLock();
        }
    }

    /**
     * Rollback migrations
     */
    public function rollbackMigrations(?int $steps = null, bool $force = false): bool
    {
        if (!$this->acquireMigrationLock()) {
            throw new \RuntimeException('Migration is already in progress');
        }

        try {
            Log::info('Starting migration rollback', [
                'steps' => $steps,
                'force' => $force,
            ]);

            // Create backup before rollback
            $backup = $this->backupService->createBackup('pre-rollback');

            $exitCode = Artisan::call('migrate:rollback', [
                '--step' => $steps ?? 1,
                '--force' => $force,
            ]);

            $output = Artisan::output();

            if ($exitCode === 0) {
                Log::info('Migration rollback successful', ['output' => $output]);
                return true;
            } else {
                Log::error('Migration rollback failed', ['output' => $output]);
                return false;
            }

        } finally {
            $this->releaseMigrationLock();
        }
    }

    /**
     * Get migration status
     */
    public function getMigrationStatus(): Collection
    {
        $ran = DB::table('migrations')->pluck('migration')->toArray();
        $allMigrations = $this->getAllMigrationFiles();

        return collect($allMigrations)->map(function ($migration) use ($ran) {
            $hasRun = in_array($this->getMigrationName($migration), $ran);

            return [
                'migration' => $this->getMigrationName($migration),
                'status' => $hasRun ? 'ran' : 'pending',
                'batch' => $hasRun ? $this->getMigrationBatch($migration) : null,
                'file_path' => $migration,
                'file_hash' => md5_file($migration),
            ];
        });
    }

    /**
     * Check for pending migrations
     */
    public function hasPendingMigrations(): bool
    {
        $pending = $this->getPendingMigrationFiles();
        return count($pending) > 0;
    }

    /**
     * Get migration history
     */
    public function getMigrationHistory(int $limit = 50): Collection
    {
        return MigrationLog::with(['executedBy', 'backup'])
            ->orderByDesc('created_at')
            ->limit($limit)
            ->get();
    }

    /**
     * Create pre-migration backup
     */
    public function createPreMigrationBackup(): DatabaseBackup
    {
        Log::info('Creating pre-migration backup');

        return $this->backupService->createBackup('pre-migration');
    }

    /**
     * Restore from backup
     */
    public function restoreFromBackup(int $backupId): bool
    {
        $backup = DatabaseBackup::findOrFail($backupId);

        Log::info('Restoring database from backup', [
            'backup_id' => $backupId,
            'created_at' => $backup->created_at,
        ]);

        return $this->backupService->restoreBackup($backup);
    }

    /**
     * Detect destructive changes
     */
    public function detectDestructiveChanges(string $migrationPath): array
    {
        $content = File::get($migrationPath);
        $destructivePatterns = [
            'DROP TABLE',
            'DROP COLUMN',
            'DROP INDEX',
            'TRUNCATE',
            'DELETE FROM',
            '->drop(',
            '->dropColumn(',
            '->dropIndex(',
            '->dropForeign(',
        ];

        $found = [];

        foreach ($destructivePatterns as $pattern) {
            if (stripos($content, $pattern) !== false) {
                $found[] = $pattern;
            }
        }

        return $found;
    }

    // Private helper methods

    private function acquireMigrationLock(): bool
    {
        return Cache::add(self::MIGRATION_LOCK_KEY, true, self::LOCK_TIMEOUT);
    }

    private function releaseMigrationLock(): void
    {
        Cache::forget(self::MIGRATION_LOCK_KEY);
    }

    private function getPendingMigrationFiles(): array
    {
        $ran = DB::table('migrations')->pluck('migration')->toArray();
        $allMigrations = $this->getAllMigrationFiles();

        return array_filter($allMigrations, function ($migration) use ($ran) {
            return !in_array($this->getMigrationName($migration), $ran);
        });
    }

    private function getAllMigrationFiles(): array
    {
        $migrationPath = database_path('migrations');
        $files = File::glob($migrationPath . '/*.php');

        return array_values($files);
    }

    private function getMigrationName(string $path): string
    {
        return str_replace('.php', '', basename($path));
    }

    private function getMigrationBatch(string $migration): ?int
    {
        $name = $this->getMigrationName($migration);

        return DB::table('migrations')
            ->where('migration', $name)
            ->value('batch');
    }

    private function validateMigrationSyntax(string $migrationPath): array
    {
        $process = new Process(['php', '-l', $migrationPath]);
        $process->run();

        return [
            'valid' => $process->isSuccessful(),
            'error' => $process->isSuccessful() ? null : $process->getErrorOutput(),
        ];
    }

    private function checkMigrationDependencies(string $migrationPath): array
    {
        // Check if migration references tables that don't exist yet
        // This is a simplified check - production would be more sophisticated
        $content = File::get($migrationPath);

        // Check for foreign key references to potentially non-existent tables
        preg_match_all('/->foreign$[\'"](\w+)[\'"]$->references/', $content, $matches);

        $referencedTables = $matches[1] ?? [];

        foreach ($referencedTables as $table) {
            if (!$this->tableWillExist($table)) {
                return [
                    'valid' => false,
                    'error' => "References table '{$table}' which may not exist yet",
                ];
            }
        }

        return ['valid' => true];
    }

    private function tableWillExist(string $tableName): bool
    {
        // Check if table exists or will be created by a previous migration
        return DB::getSchemaBuilder()->hasTable($tableName);
    }

    private function executeSingleMigration(string $migrationFile, DatabaseBackup $backup): MigrationLog
    {
        $migrationName = $this->getMigrationName($migrationFile);

        $log = MigrationLog::create([
            'migration_name' => $migrationName,
            'status' => 'running',
            'environment' => app()->environment(),
            'executed_by' => auth()->id(),
            'backup_id' => $backup->id,
            'started_at' => now(),
            'metadata' => [
                'file_path' => $migrationFile,
                'file_hash' => md5_file($migrationFile),
                'file_size' => filesize($migrationFile),
            ],
        ]);

        try {
            Log::info("Executing migration: {$migrationName}");

            $startTime = microtime(true);

            // Execute migration
            $exitCode = Artisan::call('migrate', [
                '--path' => str_replace(base_path(), '', dirname($migrationFile)),
                '--force' => true,
            ]);

            $output = Artisan::output();
            $duration = microtime(true) - $startTime;

            if ($exitCode === 0) {
                $log->update([
                    'status' => 'completed',
                    'completed_at' => now(),
                    'duration_seconds' => round($duration),
                    'output' => $output,
                ]);

                Log::info("Migration completed: {$migrationName}", [
                    'duration' => $duration,
                ]);
            } else {
                $log->update([
                    'status' => 'failed',
                    'completed_at' => now(),
                    'duration_seconds' => round($duration),
                    'error_message' => $output,
                ]);

                Log::error("Migration failed: {$migrationName}", [
                    'error' => $output,
                ]);
            }

        } catch (\Exception $e) {
            $log->update([
                'status' => 'failed',
                'completed_at' => now(),
                'error_message' => $e->getMessage(),
            ]);

            Log::error("Migration exception: {$migrationName}", [
                'exception' => $e->getMessage(),
                'trace' => $e->getTraceAsString(),
            ]);
        }

        return $log->fresh();
    }

    private function handleMigrationFailure(MigrationLog $failedMigration, DatabaseBackup $backup): void
    {
        Log::warning('Handling migration failure - attempting automatic rollback');

        try {
            // Restore from backup
            $this->backupService->restoreBackup($backup);

            Log::info('Database restored from backup after migration failure');

        } catch (\Exception $e) {
            Log::critical('Failed to restore database from backup', [
                'backup_id' => $backup->id,
                'error' => $e->getMessage(),
            ]);

            // Escalate to critical alert
            $this->escalateCriticalFailure($failedMigration, $backup, $e);
        }
    }

    private function simulateMigrations(): Collection
    {
        $pending = $this->getPendingMigrationFiles();

        return collect($pending)->map(function ($migration) {
            return [
                'migration' => $this->getMigrationName($migration),
                'status' => 'dry-run',
                'file_path' => $migration,
                'destructive_changes' => $this->detectDestructiveChanges($migration),
            ];
        });
    }

    private function notifyMigrationSuccess(Collection $migrations): void
    {
        // Send notification to administrators
        $admins = \App\Models\User::where('is_admin', true)->get();

        foreach ($admins as $admin) {
            $admin->notify(new MigrationSuccessNotification($migrations));
        }
    }

    private function notifyMigrationFailure(Collection $migrations): void
    {
        $admins = \App\Models\User::where('is_admin', true)->get();

        foreach ($admins as $admin) {
            $admin->notify(new MigrationFailedNotification($migrations));
        }
    }

    private function escalateCriticalFailure(
        MigrationLog $failedMigration,
        DatabaseBackup $backup,
        \Exception $restoreException
    ): void {
        // Send critical alert
        Log::critical('CRITICAL: Migration failure AND backup restore failed', [
            'migration' => $failedMigration->migration_name,
            'backup_id' => $backup->id,
            'restore_error' => $restoreException->getMessage(),
        ]);

        // Trigger PagerDuty/incident management system
        // Send SMS/phone alerts to on-call engineers
        // Create incident ticket
    }
}
```

### Database Backup Service

**File:** `app/Services/Enterprise/DatabaseBackupService.php`

```php
<?php

namespace App\Services\Enterprise;

use App\Contracts\DatabaseBackupServiceInterface;
use App\Models\DatabaseBackup;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Symfony\Component\Process\Process;
use Visus\Cuid2\Cuid2;

class DatabaseBackupService implements DatabaseBackupServiceInterface
{
    private const COMPRESSION_LEVEL = 6;
    private const RETENTION_DAYS = 30;

    /**
     * Create database backup
     */
    public function createBackup(string $type = 'manual'): DatabaseBackup
    {
        $backup = DatabaseBackup::create([
            'uuid' => (string) new Cuid2(),
            'database_name' => config('database.connections.pgsql.database'),
            'backup_type' => $type,
            'status' => 'pending',
            'compression' => 'gzip',
            'created_by' => auth()->id(),
            'expires_at' => now()->addDays(self::RETENTION_DAYS),
            'started_at' => now(),
        ]);

        try {
            Log::info('Starting database backup', [
                'backup_id' => $backup->id,
                'type' => $type,
            ]);

            $backup->update(['status' => 'in_progress']);

            // Generate file paths
            $filename = "backup-{$backup->uuid}.sql.gz";
            $localPath = storage_path("app/backups/{$filename}");
            $s3Path = "database-backups/{$filename}";

            // Ensure backup directory exists
            if (!is_dir(dirname($localPath))) {
                mkdir(dirname($localPath), 0755, true);
            }

            // Execute pg_dump
            $this->executePgDump($localPath);

            // Calculate checksum
            $checksum = hash_file('sha256', $localPath);

            // Upload to S3
            Storage::disk('s3')->put(
                $s3Path,
                file_get_contents($localPath)
            );

            // Update backup record
            $backup->update([
                'status' => 'completed',
                'local_path' => $localPath,
                'storage_path' => $s3Path,
                'file_size' => filesize($localPath),
                'checksum' => $checksum,
                'completed_at' => now(),
                'duration_seconds' => $backup->started_at->diffInSeconds(now()),
                'metadata' => $this->collectMetadata(),
            ]);

            Log::info('Database backup completed', [
                'backup_id' => $backup->id,
                'size' => $backup->file_size,
                's3_path' => $s3Path,
            ]);

            // Cleanup local file
            if (config('migration-automation.cleanup_local_backups', true)) {
                unlink($localPath);
                $backup->update(['local_path' => null]);
            }

            return $backup->fresh();

        } catch (\Exception $e) {
            Log::error('Database backup failed', [
                'backup_id' => $backup->id,
                'error' => $e->getMessage(),
            ]);

            $backup->update([
                'status' => 'failed',
                'error_message' => $e->getMessage(),
                'completed_at' => now(),
            ]);

            throw $e;
        }
    }

    /**
     * Restore database from backup
     */
    public function restoreBackup(DatabaseBackup $backup): bool
    {
        if ($backup->status !== 'completed') {
            throw new \RuntimeException('Can only restore from completed backups');
        }

        Log::info('Starting database restore', [
            'backup_id' => $backup->id,
            'created_at' => $backup->created_at,
        ]);

        try {
            // Download from S3 if not available locally
            $localPath = $backup->local_path;

            if (!$localPath || !file_exists($localPath)) {
                $localPath = storage_path("app/backups/restore-{$backup->uuid}.sql.gz");
                $s3Content = Storage::disk('s3')->get($backup->storage_path);
                file_put_contents($localPath, $s3Content);
            }

            // Verify checksum
            if (!$this->verifyBackup($backup)) {
                throw new \RuntimeException('Backup integrity check failed');
            }

            // Execute pg_restore
            $this->executePgRestore($localPath);

            Log::info('Database restore completed', [
                'backup_id' => $backup->id,
            ]);

            return true;

        } catch (\Exception $e) {
            Log::error('Database restore failed', [
                'backup_id' => $backup->id,
                'error' => $e->getMessage(),
            ]);

            throw $e;
        }
    }

    /**
     * Cleanup expired backups
     */
    public function cleanupExpiredBackups(): int
    {
        $expiredBackups = DatabaseBackup::where('expires_at', '<', now())
            ->where('status', 'completed')
            ->get();

        $deleted = 0;

        foreach ($expiredBackups as $backup) {
            try {
                // Delete from S3
                if (Storage::disk('s3')->exists($backup->storage_path)) {
                    Storage::disk('s3')->delete($backup->storage_path);
                }

                // Delete local file if exists
                if ($backup->local_path && file_exists($backup->local_path)) {
                    unlink($backup->local_path);
                }

                // Delete database record
                $backup->delete();

                $deleted++;

                Log::info('Deleted expired backup', [
                    'backup_id' => $backup->id,
                    'expired_at' => $backup->expires_at,
                ]);

            } catch (\Exception $e) {
                Log::error('Failed to delete backup', [
                    'backup_id' => $backup->id,
                    'error' => $e->getMessage(),
                ]);
            }
        }

        return $deleted;
    }

    /**
     * Verify backup integrity
     */
    public function verifyBackup(DatabaseBackup $backup): bool
    {
        if (!$backup->local_path || !file_exists($backup->local_path)) {
            return false;
        }

        $currentChecksum = hash_file('sha256', $backup->local_path);

        return $currentChecksum === $backup->checksum;
    }

    /**
     * Estimate backup size
     */
    public function estimateBackupSize(): int
    {
        $query = "
            SELECT pg_database_size(current_database()) as size
        ";

        $result = DB::selectOne($query);

        // Estimate compressed size (typically 10-20% of original)
        return (int) ($result->size * 0.15);
    }

    // Private helper methods

    private function executePgDump(string $outputPath): void
    {
        $dbConfig = config('database.connections.pgsql');

        $process = new Process([
            'pg_dump',
            '-h', $dbConfig['host'],
            '-p', $dbConfig['port'],
            '-U', $dbConfig['username'],
            '-d', $dbConfig['database'],
            '--format=custom',
            '--compress=' . self::COMPRESSION_LEVEL,
            '--file=' . $outputPath,
        ], null, [
            'PGPASSWORD' => $dbConfig['password'],
        ], null, 3600); // 1 hour timeout

        $process->mustRun();

        Log::info('pg_dump executed successfully', [
            'output_path' => $outputPath,
        ]);
    }

    private function executePgRestore(string $backupPath): void
    {
        $dbConfig = config('database.connections.pgsql');

        // Drop all tables first
        $this->dropAllTables();

        $process = new Process([
            'pg_restore',
            '-h', $dbConfig['host'],
            '-p', $dbConfig['port'],
            '-U', $dbConfig['username'],
            '-d', $dbConfig['database'],
            '--clean',
            '--if-exists',
            $backupPath,
        ], null, [
            'PGPASSWORD' => $dbConfig['password'],
        ], null, 3600);

        $process->mustRun();

        Log::info('pg_restore executed successfully');
    }

    private function dropAllTables(): void
    {
        DB::statement('DROP SCHEMA public CASCADE');
        DB::statement('CREATE SCHEMA public');
        DB::statement('GRANT ALL ON SCHEMA public TO ' . config('database.connections.pgsql.username'));
    }

    private function collectMetadata(): array
    {
        $tableCount = count(DB::select("
            SELECT tablename
            FROM pg_tables
            WHERE schemaname = 'public'
        "));

        return [
            'postgresql_version' => DB::selectOne('SELECT version()')->version,
            'table_count' => $tableCount,
            'backup_timestamp' => now()->toIso8601String(),
        ];
    }
}
```

### Artisan Commands

**File:** `app/Console/Commands/MigrateWithBackup.php`

```php
<?php

namespace App\Console\Commands;

use App\Contracts\MigrationAutomationServiceInterface;
use Illuminate\Console\Command;

class MigrateWithBackup extends Command
{
    protected $signature = 'migrate:safe
                            {--force : Force migration without confirmation}
                            {--dry-run : Simulate migration without execution}
                            {--skip-backup : Skip pre-migration backup (not recommended)}';

    protected $description = 'Run migrations with automatic backup and rollback capability';

    public function handle(MigrationAutomationServiceInterface $migrationService): int
    {
        $this->info('🔍 Validating pending migrations...');

        // Validate migrations
        $validation = $migrationService->validatePendingMigrations();

        if ($validation['pending_count'] === 0) {
            $this->info('✅ No pending migrations to execute');
            return self::SUCCESS;
        }

        $this->info("📋 Found {$validation['pending_count']} pending migration(s)");

        // Show validation results
        if (!empty($validation['warnings'])) {
            $this->warn('⚠️  Warnings:');
            foreach ($validation['warnings'] as $warning) {
                $this->warn("  • {$warning}");
            }
        }

        if (!empty($validation['errors'])) {
            $this->error('❌ Validation errors:');
            foreach ($validation['errors'] as $error) {
                $this->error("  • {$error}");
            }

            if (!$this->option('force')) {
                $this->error('Migration validation failed. Use --force to override.');
                return self::FAILURE;
            }
        }

        // Confirmation in production
        if (app()->environment('production') && !$this->option('force')) {
            if (!$this->confirm('⚠️  This is a PRODUCTION environment. Continue with migration?')) {
                $this->info('Migration cancelled');
                return self::SUCCESS;
            }
        }

        // Execute migrations
        try {
            $this->info('🚀 Executing migrations...');

            $migrations = $migrationService->executeMigrations(
                $this->option('force'),
                $this->option('dry-run')
            );

            // Display results
            $this->newLine();
            $this->table(
                ['Migration', 'Status', 'Duration'],
                $migrations->map(fn($m) => [
                    $m->migration_name ?? $m['migration'],
                    $m->status ?? 'dry-run',
                    isset($m->duration_seconds) ? "{$m->duration_seconds}s" : 'N/A',
                ])->toArray()
            );

            $successCount = $migrations->where('status', 'completed')->count();
            $failCount = $migrations->where('status', 'failed')->count();

            if ($failCount > 0) {
                $this->error("❌ Migration failed: {$failCount} failure(s)");
                return self::FAILURE;
            }

            $this->info("✅ Successfully executed {$successCount} migration(s)");
            return self::SUCCESS;

        } catch (\Exception $e) {
            $this->error("❌ Migration error: {$e->getMessage()}");
            return self::FAILURE;
        }
    }
}
```

**File:** `app/Console/Commands/ValidateMigrations.php`

```php
<?php

namespace App\Console\Commands;

use App\Contracts\MigrationAutomationServiceInterface;
use Illuminate\Console\Command;

class ValidateMigrations extends Command
{
    protected $signature = 'migrate:validate';
    protected $description = 'Validate pending migrations without executing them';

    public function handle(MigrationAutomationServiceInterface $migrationService): int
    {
        $this->info('🔍 Validating pending migrations...');

        $validation = $migrationService->validatePendingMigrations();

        if ($validation['pending_count'] === 0) {
            $this->info('✅ No pending migrations');
            return self::SUCCESS;
        }

        $this->info("📋 Found {$validation['pending_count']} pending migration(s)");
        $this->newLine();

        if (!empty($validation['warnings'])) {
            $this->warn('⚠️  Warnings:');
            foreach ($validation['warnings'] as $warning) {
                $this->warn("  • {$warning}");
            }
            $this->newLine();
        }

        if (!empty($validation['errors'])) {
            $this->error('❌ Validation errors:');
            foreach ($validation['errors'] as $error) {
                $this->error("  • {$error}");
            }
            $this->newLine();
            return self::FAILURE;
        }

        $this->info('✅ All pending migrations are valid');
        return self::SUCCESS;
    }
}
```

## Implementation Approach

### Step 1: Database Schema
1. Create `migration_logs` table migration
2. Create `database_backups` table migration
3. Run migrations: `php artisan migrate`

### Step 2: Create Models
1. Create `MigrationLog` model with relationships
2. Create `DatabaseBackup` model with casts
3. Add factory and seeder for testing

### Step 3: Implement Backup Service
1. Create `DatabaseBackupServiceInterface`
2. Implement `DatabaseBackupService`
3. Add pg_dump/pg_restore wrapper methods
4. Implement S3 upload/download
5. Add checksum validation

### Step 4: Implement Migration Automation Service
1. Create `MigrationAutomationServiceInterface`
2. Implement `MigrationAutomationService`
3. Add validation methods
4. Add execution orchestration
5. Add rollback logic

### Step 5: Create Artisan Commands
1. Implement `MigrateWithBackup` command
2. Implement `ValidateMigrations` command
3. Implement `RollbackMigration` command
4. Implement `MigrationStatus` command
5. Register commands in `Kernel.php`

### Step 6: Add Web UI
1. Create `MigrationController`
2. Build `MigrationManager.vue` component
3. Build `MigrationHistory.vue` component
4. Add routes for migration management

### Step 7: Add Notifications
1. Create `MigrationSuccessNotification`
2. Create `MigrationFailedNotification`
3. Configure mail/Slack channels

### Step 8: Testing
1. Unit tests for services
2. Feature tests for commands
3. Integration tests for full workflow
4. Test failure scenarios and rollback

## Test Strategy

### Unit Tests

**File:** `tests/Unit/Services/MigrationAutomationServiceTest.php`

```php
<?php

use App\Services\Enterprise\MigrationAutomationService;
use App\Services\Enterprise\DatabaseBackupService;
use Illuminate\Support\Facades\Artisan;
use Illuminate\Support\Facades\File;

beforeEach(function () {
    $this->backupService = Mockery::mock(DatabaseBackupService::class);
    $this->service = new MigrationAutomationService($this->backupService);
});

it('validates pending migrations', function () {
    $result = $this->service->validatePendingMigrations();

    expect($result)->toHaveKeys(['valid', 'errors', 'warnings', 'pending_count']);
});

it('detects destructive changes', function () {
    $migrationContent = <<<'PHP'
    Schema::table('users', function (Blueprint $table) {
        $table->dropColumn('email');
    });
    PHP;

    $tempFile = tempnam(sys_get_temp_dir(), 'migration');
    file_put_contents($tempFile, $migrationContent);

    $destructive = $this->service->detectDestructiveChanges($tempFile);

    expect($destructive)->toContain('->dropColumn(');

    unlink($tempFile);
});

it('checks for pending migrations', function () {
    $hasPending = $this->service->hasPendingMigrations();

    expect($hasPending)->toBeTrue();
});
```

### Feature Tests

**File:** `tests/Feature/MigrationAutomationTest.php`

```php
<?php

use App\Models\MigrationLog;
use App\Models\DatabaseBackup;
use Illuminate\Support\Facades\Artisan;

it('executes migrations with backup', function () {
    $this->artisan('migrate:safe', ['--force' => true])
        ->assertExitCode(0);

    expect(DatabaseBackup::where('backup_type', 'pre-migration')->exists())->toBeTrue();
    expect(MigrationLog::where('status', 'completed')->exists())->toBeTrue();
});

it('validates migrations before execution', function () {
    $this->artisan('migrate:validate')
        ->expectsOutput('✅ All pending migrations are valid')
        ->assertExitCode(0);
});

it('creates backup before migration', function () {
    $service = app(\App\Contracts\MigrationAutomationServiceInterface::class);

    $backup = $service->createPreMigrationBackup();

    expect($backup)->toBeInstanceOf(DatabaseBackup::class)
        ->status->toBe('completed')
        ->backup_type->toBe('pre-migration');
});

it('rolls back on migration failure', function () {
    // Create a migration that will fail
    $failingMigration = database_path('migrations/' . date('Y_m_d_His') . '_failing_migration.php');

    file_put_contents($failingMigration, <<<'PHP'
    <?php
    use Illuminate\Database\Migrations\Migration;

    return new class extends Migration {
        public function up() {
            throw new \Exception('Intentional failure');
        }
    };
    PHP);

    $this->artisan('migrate:safe', ['--force' => true]);

    expect(MigrationLog::where('status', 'failed')->exists())->toBeTrue();

    unlink($failingMigration);
});
```

## Definition of Done

- [ ] Database migrations created (migration_logs, database_backups)
- [ ] Models created (MigrationLog, DatabaseBackup)
- [ ] DatabaseBackupServiceInterface created
- [ ] DatabaseBackupService implemented with pg_dump/restore
- [ ] MigrationAutomationServiceInterface created
- [ ] MigrationAutomationService implemented
- [ ] Migration validation (syntax, dependencies, destructive changes)
- [ ] Automatic backup before migration execution
- [ ] S3 backup storage with versioning
- [ ] Migration execution with progress tracking
- [ ] Automatic rollback on failures
- [ ] Manual rollback command
- [ ] Audit logging for all migration events
- [ ] Migration locking to prevent concurrent execution
- [ ] Dry-run mode implemented
- [ ] Artisan commands created (migrate:safe, migrate:validate, etc.)
- [ ] MigrationController created
- [ ] MigrationManager.vue component built
- [ ] MigrationHistory.vue component built
- [ ] Notification classes created (success, failure)
- [ ] Configuration file created
- [ ] Service providers updated
- [ ] Unit tests written (>90% coverage)
- [ ] Feature tests written (all scenarios)
- [ ] Integration tests written
- [ ] Documentation updated (usage, troubleshooting)
- [ ] CI/CD integration tested
- [ ] PHPStan level 5 passing
- [ ] Laravel Pint formatting applied
- [ ] Code reviewed and approved
- [ ] Production deployment tested

## Related Tasks

- **Depends on:** Task 89 (CI/CD pipeline for deployment automation)
- **Used by:** Task 91 (Monitoring dashboards display migration status)
- **Integrates with:** All tasks (database migrations are foundational)



Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Implement database migration automation #197

Task: Implement database migration automation

Description

Acceptance Criteria

Technical Details

File Paths

Database Schema

Service Interface

Service Implementation

Database Backup Service

Artisan Commands

Implementation Approach

Step 1: Database Schema

Step 2: Create Models

Step 3: Implement Backup Service

Step 4: Implement Migration Automation Service

Step 5: Create Artisan Commands

Step 6: Add Web UI

Step 7: Add Notifications

Step 8: Testing

Test Strategy

Unit Tests

Feature Tests

Definition of Done

Related Tasks

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Implement database migration automation #197

Description

Task: Implement database migration automation

Description

Acceptance Criteria

Technical Details

File Paths

Database Schema

Service Interface

Service Implementation

Database Backup Service

Artisan Commands

Implementation Approach

Step 1: Database Schema

Step 2: Create Models

Step 3: Implement Backup Service

Step 4: Implement Migration Automation Service

Step 5: Create Artisan Commands

Step 6: Add Web UI

Step 7: Add Notifications

Step 8: Testing

Test Strategy

Unit Tests

Feature Tests

Definition of Done

Related Tasks

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions