# Performance Optimization Plan

## Executive Summary

This document outlines a comprehensive performance optimization strategy for the CRM API backend. The plan focuses on four key areas: database indexing, query optimization, caching strategy, and read replica support.

## Current State Analysis

### Heavy Endpoints Identified

**Dashboard Endpoints**
- `POST /api/dashboard/cards-metric/fetch` - Aggregates counts across multiple tables
- `POST /api/dashboard/latest-meetings/fetch` - Complex joins with user relationships
- `POST /api/dashboard/latest-tasks/fetch` - Multiple eager-loaded relationships
- `POST /api/dashboard/latest-projects/fetch` - Project with assignees and media

**Lead Management**
- `POST /api/lead/all/fetch` - Large dataset with filters and joins
- `POST /api/lead/all/search` - Full-text search with multiple conditions
- `POST /api/lead/analytics` - Complex aggregation queries
- `POST /api/lead/report` - Comprehensive report with all related data

**User Analytics**
- `POST /api/user/analytics/fetch` - Aggregates user activity metrics
- `POST /api/user/analytics/fetch/sales-chart` - Time-series aggregation
- `POST /api/user/analytics/fetch/pipeline-chart` - Lead funnel aggregation

**Attendance**
- `POST /api/attendance/report/fetch` - Aggregated attendance data
- `POST /api/attendance/detailed/report/fetch` - Date-range queries with joins

### Database Index Gaps

**Missing Composite Indexes**

| Table | Missing Composite Index | Query Pattern |
|-------|------------------------|---------------|
| `leads` | `(status, created_by)` | Filter by status + user access |
| `leads` | `(status, assigned_to)` | Filter by status + assigned user |
| `leads` | `(company_id, status)` | Company-wise lead lists |
| `leads` | `(is_inactive, status)` | Inactive lead pool queries |
| `meetings` | `(meeting_date, lead_id)` | Date-range meeting lists |
| `meetings` | `(lead_id, is_arrived, is_started, is_completed)` | Meeting status flow |
| `tasks` | `(created_by, is_completed)` | User's active tasks |
| `tasks` | `(lead_id, is_completed)` | Lead-related tasks |
| `tasks` | `(due_date, is_completed)` | Overdue task queries |
| `tasks` | `(type, has_recurred, stop_recurrence)` | Recurring task generation |
| `attendances` | `(attendance_date, user_id)` | Date-range attendance (unique exists, but index needed for ordering) |
| `attendances` | `(user_id, is_present)` | Present users list |
| `lead_sales` | `(sale_date, lead_id)` | Sales by date range |
| `lead_sales` | `(lead_id, payment_status)` | Payment status queries |
| `lead_sales` | `(product_id, sale_date)` | Product sales analytics |
| `lead_sales` | `(sale_by, sale_date)` | Salesperson performance |
| `event_logs` | `(task_id, created_at)` | Task logs timeline |
| `event_logs` | `(meeting_id, created_at)` | Meeting logs timeline |
| `event_logs` | `(created_by, created_at)` | User activity logs |
| `lead_follow_ups` | `(lead_id, follow_up_date)` | Upcoming follow-ups |
| `lead_notes` | `(lead_id, created_at)` | Lead notes timeline |
| `projects` | `(lead_id, status)` | Lead project status |
| `projects` | `(team_leader, status)` | Team leader projects |
| `quotations` | `(lead_id, created_at)` | Lead quotations timeline |

**Missing Single Column Indexes**

| Table | Column | Reason |
|-------|--------|--------|
| `leads` | `source` | Filter by lead source |
| `leads` | `priority` | Filter by priority |
| `leads` | `call_status` | Filter by call status |
| `leads` | `cnvt_to_verified_at` | Conversion analytics |
| `leads` | `cnvt_to_client_at` | Conversion analytics |
| `meetings` | `meeting_date` | Date-range queries |
| `meetings` | `is_arrived` | Status filtering |
| `meetings` | `is_started` | Status filtering |
| `meetings` | `is_completed` | Status filtering |
| `meetings` | `is_cancelled` | Status filtering |
| `tasks` | `due_date` | Overdue queries |
| `tasks` | `priority` | Priority filtering |
| `tasks` | `type` | Recurring task queries |
| `tasks` | `is_completed` | Completion filtering |
| `lead_sales` | `sale_date` | Date-range queries |
| `lead_sales` | `approval_status` | Approval workflow |
| `lead_sales` | `payment_status` | Payment tracking |
| `lead_sales` | `bill_raised` | Billing status |
| `companies` | `status` | Active/inactive filtering |
| `companies` | `created_by` | User access control |
| `projects` | `status` | Status filtering |
| `projects` | `priority` | Priority filtering |
| `projects` | `start_date` | Date-range queries |
| `projects` | `end_date` | Date-range queries |

### Current Caching Strategy

**Issues Identified**
- Cache driver: `database` (slow, adds DB load)
- No Redis configuration (fast in-memory cache available)
- No systematic caching of:
  - User permissions
  - Settings (meeting, notification, incentive, etc.)
  - Product lists
  - Source lists
  - Role/permission catalogs
  - Dashboard metrics (short TTL)
- No cache invalidation strategy

### Read Replica Support

**Current State**
- Single MySQL connection configured
- No read replica configuration
- All queries hit primary database
- No query routing logic

## Optimization Plan

### Phase 1: Database Indexing (High Impact, Low Risk)

**Priority 1 - Critical Performance Indexes**

Create migration: `database/migrations/2026_11_13_000001_add_performance_indexes.php`

```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
    {
        // Leads table - most queried table
        Schema::table('leads', function (Blueprint $table) {
            $table->index(['status', 'created_by'], 'idx_leads_status_created_by');
            $table->index(['status', 'assigned_to'], 'idx_leads_status_assigned_to');
            $table->index(['company_id', 'status'], 'idx_leads_company_status');
            $table->index(['is_inactive', 'status'], 'idx_leads_inactive_status');
            $table->index('source', 'idx_leads_source');
            $table->index('priority', 'idx_leads_priority');
            $table->index('call_status', 'idx_leads_call_status');
            $table->index('cnvt_to_verified_at', 'idx_leads_cnvt_verified_at');
            $table->index('cnvt_to_client_at', 'idx_leads_cnvt_client_at');
        });

        // Meetings table
        Schema::table('meetings', function (Blueprint $table) {
            $table->index('meeting_date', 'idx_meetings_date');
            $table->index(['lead_id', 'is_arrived', 'is_started', 'is_completed'], 'idx_meetings_lead_status');
            $table->index('is_arrived', 'idx_meetings_arrived');
            $table->index('is_started', 'idx_meetings_started');
            $table->index('is_completed', 'idx_meetings_completed');
            $table->index('is_cancelled', 'idx_meetings_cancelled');
        });

        // Tasks table
        Schema::table('tasks', function (Blueprint $table) {
            $table->index(['created_by', 'is_completed'], 'idx_tasks_created_completed');
            $table->index(['lead_id', 'is_completed'], 'idx_tasks_lead_completed');
            $table->index(['due_date', 'is_completed'], 'idx_tasks_due_completed');
            $table->index(['type', 'has_recurred', 'stop_recurrence'], 'idx_tasks_recurring');
            $table->index('due_date', 'idx_tasks_due_date');
            $table->index('priority', 'idx_tasks_priority');
            $table->index('type', 'idx_tasks_type');
            $table->index('is_completed', 'idx_tasks_completed');
        });

        // Lead Sales table
        Schema::table('lead_sales', function (Blueprint $table) {
            $table->index(['sale_date', 'lead_id'], 'idx_sales_date_lead');
            $table->index(['lead_id', 'payment_status'], 'idx_sales_lead_payment');
            $table->index(['product_id', 'sale_date'], 'idx_sales_product_date');
            $table->index(['sale_by', 'sale_date'], 'idx_sales_by_date');
            $table->index('sale_date', 'idx_sales_date');
            $table->index('approval_status', 'idx_sales_approval');
            $table->index('payment_status', 'idx_sales_payment');
            $table->index('bill_raised', 'idx_sales_bill');
        });

        // Event Logs table
        Schema::table('event_logs', function (Blueprint $table) {
            $table->index(['task_id', 'created_at'], 'idx_event_logs_task_created');
            $table->index(['meeting_id', 'created_at'], 'idx_event_logs_meeting_created');
            $table->index(['created_by', 'created_at'], 'idx_event_logs_user_created');
        });

        // Lead Follow-ups
        Schema::table('lead_follow_ups', function (Blueprint $table) {
            $table->index(['lead_id', 'follow_up_date'], 'idx_followups_lead_date');
        });

        // Lead Notes
        Schema::table('lead_notes', function (Blueprint $table) {
            $table->index(['lead_id', 'created_at'], 'idx_notes_lead_created');
        });

        // Projects
        Schema::table('projects', function (Blueprint $table) {
            $table->index(['lead_id', 'status'], 'idx_projects_lead_status');
            $table->index(['team_leader', 'status'], 'idx_projects_leader_status');
            $table->index('status', 'idx_projects_status');
            $table->index('priority', 'idx_projects_priority');
            $table->index('start_date', 'idx_projects_start');
            $table->index('end_date', 'idx_projects_end');
        });

        // Companies
        Schema::table('companies', function (Blueprint $table) {
            $table->index('status', 'idx_companies_status');
            $table->index('created_by', 'idx_companies_created_by');
        });

        // Attendances - add index for date-range queries (unique exists but index helps ordering)
        Schema::table('attendances', function (Blueprint $table) {
            $table->index('attendance_date', 'idx_attendances_date');
            $table->index(['user_id', 'is_present'], 'idx_attendances_user_present');
        });
    }

    public function down(): void
    {
        Schema::table('leads', function (Blueprint $table) {
            $table->dropIndex('idx_leads_status_created_by');
            $table->dropIndex('idx_leads_status_assigned_to');
            $table->dropIndex('idx_leads_company_status');
            $table->dropIndex('idx_leads_inactive_status');
            $table->dropIndex('idx_leads_source');
            $table->dropIndex('idx_leads_priority');
            $table->dropIndex('idx_leads_call_status');
            $table->dropIndex('idx_leads_cnvt_verified_at');
            $table->dropIndex('idx_leads_cnvt_client_at');
        });

        Schema::table('meetings', function (Blueprint $table) {
            $table->dropIndex('idx_meetings_date');
            $table->dropIndex('idx_meetings_lead_status');
            $table->dropIndex('idx_meetings_arrived');
            $table->dropIndex('idx_meetings_started');
            $table->dropIndex('idx_meetings_completed');
            $table->dropIndex('idx_meetings_cancelled');
        });

        Schema::table('tasks', function (Blueprint $table) {
            $table->dropIndex('idx_tasks_created_completed');
            $table->dropIndex('idx_tasks_lead_completed');
            $table->dropIndex('idx_tasks_due_completed');
            $table->dropIndex('idx_tasks_recurring');
            $table->dropIndex('idx_tasks_due_date');
            $table->dropIndex('idx_tasks_priority');
            $table->dropIndex('idx_tasks_type');
            $table->dropIndex('idx_tasks_completed');
        });

        Schema::table('lead_sales', function (Blueprint $table) {
            $table->dropIndex('idx_sales_date_lead');
            $table->dropIndex('idx_sales_lead_payment');
            $table->dropIndex('idx_sales_product_date');
            $table->dropIndex('idx_sales_by_date');
            $table->dropIndex('idx_sales_date');
            $table->dropIndex('idx_sales_approval');
            $table->dropIndex('idx_sales_payment');
            $table->dropIndex('idx_sales_bill');
        });

        Schema::table('event_logs', function (Blueprint $table) {
            $table->dropIndex('idx_event_logs_task_created');
            $table->dropIndex('idx_event_logs_meeting_created');
            $table->dropIndex('idx_event_logs_user_created');
        });

        Schema::table('lead_follow_ups', function (Blueprint $table) {
            $table->dropIndex('idx_followups_lead_date');
        });

        Schema::table('lead_notes', function (Blueprint $table) {
            $table->dropIndex('idx_notes_lead_created');
        });

        Schema::table('projects', function (Blueprint $table) {
            $table->dropIndex('idx_projects_lead_status');
            $table->dropIndex('idx_projects_leader_status');
            $table->dropIndex('idx_projects_status');
            $table->dropIndex('idx_projects_priority');
            $table->dropIndex('idx_projects_start');
            $table->dropIndex('idx_projects_end');
        });

        Schema::table('companies', function (Blueprint $table) {
            $table->dropIndex('idx_companies_status');
            $table->dropIndex('idx_companies_created_by');
        });

        Schema::table('attendances', function (Blueprint $table) {
            $table->dropIndex('idx_attendances_date');
            $table->dropIndex('idx_attendances_user_present');
        });
    }
};
```

**Expected Impact**
- 40-60% reduction in query time for filtered list endpoints
- Significant improvement in dashboard load times
- Better performance for date-range queries

### Phase 2: Caching Strategy (High Impact, Medium Risk)

**2.1 Configure Redis Cache**

Update `.env`:
```env
CACHE_DRIVER=redis
REDIS_HOST=127.0.0.1
REDIS_PASSWORD=null
REDIS_PORT=6379
REDIS_DB=0
REDIS_CACHE_DB=1
```

**2.2 Create Cache Service**

Create `app/Services/TurboCacheService.php`:

```php
<?php

namespace App\Services;

use Illuminate\Support\Facades\Cache;

class TurboCacheService
{
    /**
     * Cache user permissions for 1 hour
     */
    public function getUserPermissions(int $userId): array
    {
        return Cache::remember(
            "user:{$userId}:permissions",
            now()->addHour(),
            fn() => $this->fetchUserPermissions($userId)
        );
    }

    /**
     * Cache settings by module (5 minutes)
     */
    public function getSetting(string $module, string $key, mixed $default = null): mixed
    {
        return Cache::remember(
            "settings:{$module}:{$key}",
            now()->addMinutes(5),
            fn() => $this->fetchSetting($module, $key, $default)
        );
    }

    /**
     * Cache product list (30 minutes)
     */
    public function getProducts(): array
    {
        return Cache::remember('products:all', now()->addMinutes(30), fn() => $this->fetchProducts());
    }

    /**
     * Cache source list (1 hour)
     */
    public function getSources(): array
    {
        return Cache::remember('sources:all', now()->addHour(), fn() => $this->fetchSources());
    }

    /**
     * Cache dashboard metrics (2 minutes - short TTL for near real-time)
     */
    public function getDashboardMetrics(int $userId, array $filters = []): array
    {
        $cacheKey = "dashboard:{$userId}:" . md5(json_encode($filters));
        return Cache::remember($cacheKey, now()->addMinutes(2), fn() => $this->fetchDashboardMetrics($userId, $filters));
    }

    /**
     * Invalidate user-related cache on permission/role changes
     */
    public function invalidateUserCache(int $userId): void
    {
        Cache::forget("user:{$userId}:permissions");
        Cache::forget("dashboard:{$userId}");
    }

    /**
     * Invalidate settings cache on update
     */
    public function invalidateSettingsCache(string $module): void
    {
        Cache::forget("settings:{$module}");
    }

    /**
     * Invalidate product/source cache on changes
     */
    public function invalidateLookupCache(): void
    {
        Cache::forget('products:all');
        Cache::forget('sources:all');
    }

    private function fetchUserPermissions(int $userId): array
    {
        // Implementation using Spatie permissions
        $user = \App\Models\User::find($userId);
        return $user?->getAllPermissions()->pluck('name')->toArray() ?? [];
    }

    private function fetchSetting(string $module, string $key, mixed $default): mixed
    {
        // Implementation based on setting tables
        return $default; // Placeholder
    }

    private function fetchProducts(): array
    {
        return \App\Models\Product::active()->get()->toArray();
    }

    private function fetchSources(): array
    {
        return \App\Models\Source::all()->toArray();
    }

    private function fetchDashboardMetrics(int $userId, array $filters): array
    {
        // Implementation using DashboardService
        return app(DashboardService::class)->getMetrics($userId, $filters);
    }
}
```

**2.3 Implement Cache Invalidation Hooks**

Add to model observers:

```php
// UserObserver
public function updated(User $user): void
{
    if ($user->isDirty('role_id') || $user->isDirty('permissions')) {
        app(TurboCacheService::class)->invalidateUserCache($user->id);
    }
}

// ProductObserver
public function created(Product $product): void
{
    app(TurboCacheService::class)->invalidateLookupCache();
}

public function updated(Product $product): void
{
    app(TurboCacheService::class)->invalidateLookupCache();
}

public function deleted(Product $product): void
{
    app(TurboCacheService::class)->invalidateLookupCache();
}
```

**Expected Impact**
- 70-90% reduction in lookup query time
- 50-70% reduction in dashboard load time
- Reduced database load for frequently accessed data

### Phase 3: Query Optimization (Medium Impact, Low Risk)

**3.1 Optimize DashboardController**

Current issues:
- N+1 queries in relationship loading
- Unnecessary eager loading
- Missing select constraints

Optimizations:
```php
// Before
$tasks = Task::with([
    'creator',
    'task_assignees.user',
    'lead',
    'tag.tag',
    'project',
    'project_ticket',
    'active_attachments',
    'active_attachments.uploader',
])->get();

// After - Select only needed columns
$tasks = Task::with([
    'creator:id,name,country_code,phone,email,image_path',
    'task_assignees.user:id,name,country_code,phone,email,image_path',
    'lead:id,name',
    'tag.tag:id,name',
    'project:id,project_name,status',
    'project_ticket:id,name',
    'active_attachments:id,task_id,uploaded_by,disk,path,original_name',
    'active_attachments.uploader:id,name,image_path',
])
->where('is_deleted', 0)
->whereIn('created_by', $accessibleUserIds)
->orWhereHas('task_assignees', fn($q) => $q->whereIn('user_id', $accessibleUserIds))
->limit(10)
->get();
```

**3.2 Add Query Scopes for Common Filters**

Create `app/Models/Scopes/ActiveScope.php`:

```php
<?php

namespace App\Models\Scopes;

use Illuminate\Database\Eloquent\Builder;

class ActiveScope
{
    public function __invoke(Builder $query): Builder
    {
        return $query->where('is_deleted', 0);
    }
}
```

Add to models:
```php
protected static function booted(): void
{
    static::addGlobalScope(new ActiveScope);
}
```

**Expected Impact**
- 20-30% reduction in query execution time
- Reduced memory usage for large result sets
- Cleaner, more maintainable code

### Phase 4: Read Replica Support (Medium Impact, High Risk)

**4.1 Configure Read Replica**

Update `config/database.php`:

```php
'connections' => [
    'mysql' => [
        'driver' => 'mysql',
        'read' => [
            'host' => [
                env('DB_READ_HOST', env('DB_HOST', '127.0.0.1')),
            ],
        ],
        'write' => [
            'host' => [
                env('DB_HOST', '127.0.0.1'),
            ],
        ],
        'sticky' => true,
        // ... rest of configuration
    ],
],
```

**4.2 Add Environment Variables**

```env
DB_READ_HOST=127.0.0.1
DB_WRITE_HOST=127.0.0.1
```

**4.3 Use Read-Only Connections for Read Operations**

Laravel automatically routes read operations to read hosts when configured. No code changes needed for basic Eloquent queries.

For explicit read operations:
```php
// Force read from replica
$results = Model::onReadConnection()->get();
```

**Expected Impact**
- 30-50% reduction in primary database load
- Better scalability for read-heavy workloads
- Improved performance during write operations

**Note**: This requires database replication setup (MySQL master-slave or Galera cluster). This is infrastructure-level work.

## Implementation Timeline

| Phase | Duration | Priority | Dependencies |
|-------|----------|----------|--------------|
| Phase 1: Database Indexing | 1 day | High | None |
| Phase 2: Caching Strategy | 2-3 days | High | Redis setup |
| Phase 3: Query Optimization | 2-3 days | Medium | Phase 1 |
| Phase 4: Read Replica | 1-2 weeks | Medium | DB infrastructure |

## Monitoring & Validation

### Performance Metrics to Track

1. **Query Response Time**
   - Target: < 500ms for 95th percentile
   - Monitor: Laravel Telescope / New Relic

2. **Database Load**
   - CPU utilization
   - Query throughput
   - Slow query log

3. **Cache Hit Rate**
   - Target: > 80% hit rate
   - Monitor: Redis INFO stats

4. **API Response Time**
   - Target: < 200ms for cached endpoints
   - Monitor: APM tool

### Validation Steps

1. **Before Optimization**
   - Run benchmark tests on heavy endpoints
   - Record baseline metrics
   - Enable slow query logging

2. **After Each Phase**
   - Run same benchmarks
   - Compare metrics
   - Validate no regressions

3. **Production Rollout**
   - Canary deployment
   - Monitor for 24 hours
   - Full rollout if stable

## Risk Mitigation

| Risk | Mitigation |
|------|------------|
| Index migration causes downtime | Run during low-traffic window; use online DDL if MySQL 8.0+ |
| Cache staleness | Implement proper invalidation hooks; use reasonable TTLs |
| Read replica lag | Use `sticky: true` for session consistency; monitor lag |
| Query optimization breaks functionality | Comprehensive test suite; code review |
| Redis single point of failure | Redis Sentinel or cluster for production |

## Success Criteria

- Dashboard endpoints: < 500ms response time
- Lead list endpoints: < 300ms response time
- Database CPU utilization: < 70%
- Cache hit rate: > 80%
- Zero critical bugs introduced

## Next Steps

1. Review and approve this plan
2. Set up Redis server (if not already available)
3. Begin Phase 1: Database Indexing
4. Monitor and validate each phase
5. Document any deviations from the plan
