Hybrid State Management: Database Triggers + Eloquent Casts

For complex state machines involving multiple boolean flags (like is_canceled, is_on_hold, etc.), using MySQL triggers to compute a single status column is a powerful pattern. This approach prevents data inconsistencies while keeping database queries fast and efficient.

The Problem: Managing Competing States

If an order has is_canceled = 1 and is_on_hold = 0, what should the status be? If you compute this logic in PHP every time you query the record, it adds overhead. Storing it redundantly in the database can lead to “drift” where the status column becomes out of sync with the boolean flags.

The Solution: MySQL Triggers

By using a database trigger, you can automatically compute the correct status based on the boolean flags whenever a record is inserted or updated. This ensures that your source of truth (the flags) is always reflected in your queryable column (the status).

Implementation

// Migration
Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->boolean('is_canceled')->default(false);
    $table->boolean('is_on_hold')->default(false);
    $table->boolean('is_archived')->default(false);
    $table->string('status')->default('active'); // Auto-computed column
    $table->datetime('status_changed_at')->nullable();
    $table->timestamps();
});

// Create trigger in migration
DB::unprepared("
    CREATE TRIGGER orders_status_update BEFORE UPDATE ON orders
    FOR EACH ROW
    BEGIN
        DECLARE status_val VARCHAR(50);
        
        SET status_val = CASE
            WHEN NEW.is_canceled = 1 THEN 'canceled'
            WHEN NEW.is_on_hold = 1 THEN 'on_hold'
            WHEN NEW.is_archived = 1 THEN 'archived'
            ELSE 'active'
        END;
        
        IF NEW.status != status_val THEN
            SET NEW.status = status_val;
            SET NEW.status_changed_at = NOW();
        END IF;
    END
");

Leveraging in Laravel

Your business logic can continue using simple, expressive boolean flags while remaining confident that the queryable status is always correct.

class Order extends Model
{
    protected $casts = [
        'status' => OrderStatus::class, // Backed Enum
        'is_canceled' => 'boolean',
        'is_on_hold' => 'boolean',
    ];
    
    public function cancel(): void
    {
        $this->is_canceled = true;
        $this->save();
        // The MySQL trigger automatically sets status='canceled'
        // and status_changed_at=NOW()
    }
}

This hybrid approach combines the best of both worlds: highly expressive business logic in PHP and consistent, high-performance querying at the database layer.