📄 File: 2026-03-12-phase9-security.php

Path: /home2/outerorb/.trash/2026-03-12-phase9-security.php

Size: 2.71 KB

Permissions: 0666

<?php
/**
 * Database Migration: Phase 9 - Performance & Security
 * Runs on application startup
 */

require_once __DIR__ . '/config.php';
require_once __DIR__ . '/helpers.php';

// Initialize migration system
ensure_migration_table();
ensure_2fa_tables();

// Migration: Create database indexes
run_migration('2026-03-12-add-database-indexes', function($pdo) {
    $indexes = [
        // Users table indexes
        "ALTER TABLE users ADD INDEX idx_email (email)" => "User email lookup",
        "ALTER TABLE users ADD INDEX idx_role (role)" => "User role filtering",
        "ALTER TABLE users ADD INDEX idx_deleted (deleted_at)" => "Soft delete filtering",
        
        // Employees table indexes
        "ALTER TABLE employees ADD INDEX idx_employee_id (employee_id)" => "Employee ID lookup",
        "ALTER TABLE employees ADD INDEX idx_department (department_id)" => "Department filtering",
        "ALTER TABLE employees ADD INDEX idx_designation (designation_id)" => "Designation filtering",
        "ALTER TABLE employees ADD INDEX idx_deleted (deleted_at)" => "Soft delete filtering",
        "ALTER TABLE employees ADD INDEX idx_email (email)" => "Email lookup",
        
        // Attendance table indexes
        "ALTER TABLE attendance ADD INDEX idx_employee (employee_id)" => "Employee attendance lookup",
        "ALTER TABLE attendance ADD INDEX idx_date (date)" => "Date range queries",
        "ALTER TABLE attendance ADD INDEX idx_employee_date (employee_id, date)" => "Combined lookup",
        
        // Leave requests indexes
        "ALTER TABLE leave_requests ADD INDEX idx_employee (employee_id)" => "Employee leaves",
        "ALTER TABLE leave_requests ADD INDEX idx_status (status)" => "Status filtering",
        "ALTER TABLE leave_requests ADD INDEX idx_dates (from_date, to_date)" => "Date range",
        
        // Activity logs indexes
        "ALTER TABLE activity_logs ADD INDEX idx_user (user_id)" => "User activity",
        "ALTER TABLE activity_logs ADD INDEX idx_action (action)" => "Action filtering",
        "ALTER TABLE activity_logs ADD INDEX idx_created (created_at)" => "Date range",
        
        // Notifications indexes
        "ALTER TABLE notifications ADD INDEX idx_user_unread (user_id, is_read)" => "Unread count",
        "ALTER TABLE notifications ADD INDEX idx_created (created_at)" => "Recent notifications",
    ];
    
    foreach ($indexes as $sql => $description) {
        try {
            $pdo->exec($sql);
        } catch (Exception $e) {
            // Index might already exist
            error_log("Index migration note: " . $e->getMessage());
        }
    }
});

echo "✓ Phase 9 migration complete: Database indexes created\n";

← Back to Directory Edit File 🔒 Chmod