Backend

laravel-database

Creates migrations, optimizes queries, fixes N+1 problems

Overview

The laravel-database agent specializes in database operations including migration creation, schema design, query optimization, index analysis, and N+1 problem detection and resolution.

Responsibilities

  • Migration Creation - Generate migrations with proper column types, indexes, and foreign keys
  • Schema Design - Design normalized database schemas following best practices
  • Query Optimization - Analyze and optimize slow database queries
  • N+1 Detection - Identify and fix N+1 query problems with eager loading
  • Index Strategy - Recommend and implement database indexes
  • Relationship Setup - Configure Eloquent relationships correctly

What It Creates

Component Location Purpose
Migrations database/migrations/ Schema changes with up/down methods
Index Migrations database/migrations/ Performance-focused index additions
Model Scopes app/Models/ Optimized query scopes

Migration Generation Example

<?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('orders', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->constrained()->cascadeOnDelete();
            $table->foreignId('product_id')->constrained();
            $table->enum('status', ['pending', 'processing', 'shipped', 'delivered', 'cancelled'])
                  ->default('pending');
            $table->decimal('total', 10, 2);
            $table->text('notes')->nullable();
            $table->timestamp('shipped_at')->nullable();
            $table->timestamps();
            $table->softDeletes();

            // Composite indexes for common queries
            $table->index(['user_id', 'status']);
            $table->index(['status', 'created_at']);
        });
    }

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

N+1 Problem Detection

The agent identifies N+1 queries and provides fixes:

// BEFORE: N+1 problem (1 + N queries)
$posts = Post::all();
foreach ($posts as $post) {
    echo $post->author->name; // Queries for each post!
}

// AFTER: Eager loading (2 queries total)
$posts = Post::with('author')->get();
foreach ($posts as $post) {
    echo $post->author->name; // No additional queries
}

// OPTIMIZED: Select only needed columns
$posts = Post::select('id', 'title', 'user_id')
    ->with('author:id,name')
    ->get();

Index Recommendations

The agent analyzes queries and recommends indexes:

// For this query pattern:
Order::where('status', 'pending')
    ->where('created_at', '>', now()->subDays(7))
    ->orderBy('created_at', 'desc')
    ->get();

// Agent recommends this index migration:
Schema::table('orders', function (Blueprint $table) {
    $table->index(['status', 'created_at']); // Composite index
});

Query Optimization Examples

// SLOW: Loading unnecessary data
$users = User::all();
$activeCount = $users->where('active', true)->count();

// FAST: Database-level counting
$activeCount = User::where('active', true)->count();

// SLOW: N+1 with aggregation
foreach ($categories as $category) {
    echo $category->products->count();
}

// FAST: WithCount
$categories = Category::withCount('products')->get();
foreach ($categories as $category) {
    echo $category->products_count;
}

Invoked By Commands

Version Upgrades

The database agent also handles Laravel/PHP version upgrades:

Upgrade Key Changes
Laravel 10 → 11 New bootstrap/app.php, middleware configuration
Laravel 11 → 12 PHP 8.3+ required, removed deprecated facades
PHP 8.2 → 8.3 Typed class constants, json_validate(), #[\Override]

Package Integration

Integrates with analysis and optimization packages:

  • beyondcode/laravel-query-detector - N+1 detection in development
  • grazulex/laravel-devtoolbox - Comprehensive analysis
  • laravel/scout - Full-text search with Meilisearch
  • rectorphp/rector - Automated code upgrades
  • kitloong/laravel-migrations-generator - Generate migrations from existing DB

Migration Safety Protocol

# 1. Check status
php artisan migrate:status

# 2. Preview changes
php artisan migrate --pretend

# 3. Backup production
mysqldump -u user -p database > backup.sql

# 4. Run migration
php artisan migrate

# 5. Verify
php artisan migrate:status

Guardrails

The database agent enforces strict rules:

  • ALWAYS backup before migrating or upgrading
  • ALWAYS run migrate --pretend first
  • ALWAYS run tests after changes
  • NEVER upgrade multiple major versions at once
  • NEVER mass-assign tenant IDs
  • PREFER incremental upgrades (10 → 11 → 12)

See Also