Database Standards

Migrations

Basic Structure

<?php

declare(strict_types=1);

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('tickets', function (Blueprint $table): void {
            $table->id();
            $table->string('hash_id')->nullable()->default(null)->unique();
            $table->string('title');
            $table->text('body');
            $table->boolean('is_active')->default(true);
            $table->json('metadata')->nullable()->default(null);
            $table->timestampTz('due_at')->nullable();
            $table->foreignId('user_id')->constrained()->cascadeOnDelete();
            $table->foreignId('category_id')->nullable()->constrained();
            $table->softDeletes();
            $table->timestamps();
        });
    }

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

Column Naming

Standard Columns

Boolean Columns

Always prefix with is_ or has_:

// Good
$table->boolean('is_active')->default(true);
$table->boolean('is_admin')->default(false);
$table->boolean('is_verified')->default(false);
$table->boolean('has_subscription')->default(false);
$table->boolean('has_2fa')->default(false);

// Bad
$table->boolean('active');
$table->boolean('admin');
$table->boolean('verified');

Timestamp Columns

// Standard Laravel timestamps
$table->timestamps();        // created_at, updated_at
$table->softDeletes();       // deleted_at

// Custom timestamps with timezone
$table->timestampTz('due_at')->nullable();
$table->timestampTz('published_at')->nullable();
$table->timestampTz('next_reminder_at')->nullable();
$table->timestampTz('started_at')->nullable();
$table->timestampTz('completed_at')->nullable();

Foreign Keys

With Constraints

// Standard - cascading delete
$table->foreignId('user_id')->constrained()->cascadeOnDelete();

// Nullable foreign key
$table->foreignId('category_id')->nullable()->constrained();

// With specific actions
$table->foreignId('parent_id')
    ->nullable()
    ->constrained('categories')
    ->nullOnDelete();

// Custom table reference
$table->foreignId('assigned_to')
    ->nullable()
    ->constrained('users')
    ->nullOnDelete();

Naming Convention

• Always use {related_model_singular}_id
• Use the model name, not the table name

// Good
$table->foreignId('user_id');        // references users table
$table->foreignId('category_id');    // references categories table
$table->foreignId('ticket_id');      // references tickets table

// Bad
$table->foreignId('users_id');       // Plural
$table->foreignId('userId');         // camelCase

Indexes

When to Add Indexes

// Columns frequently used in WHERE clauses
$table->index('is_active');
$table->index('status');

// Columns used in ORDER BY
$table->index('created_at');
$table->index('position');

// Composite indexes for common query patterns
$table->index(['user_id', 'is_active']);
$table->index(['user_id', 'folder_id', 'is_active']);

// Unique constraints
$table->unique('email');
$table->unique('hash_id');
$table->unique(['name', 'user_id']);  // Composite unique
$table->unique(['name', 'user_id', 'folder_id']);

JSON Columns

// Nullable JSON with default null
$table->json('settings')->nullable()->default(null);
$table->json('metadata')->nullable()->default(null);
$table->json('custom_fields')->nullable()->default(null);
$table->json('frequency_data')->nullable()->default(null);

// Cast in model
protected function casts(): array
{
    return [
        'settings' => 'array',
        'metadata' => 'array',
        'frequency_data' => RepeatingFrequencyData::class,
    ];
}

Common Patterns

Hash ID Pattern

// In migration
$table->string('hash_id')->nullable()->default(null)->unique();

// In model trait
trait HasHashIds
{
    protected static function boot(): void
    {
        parent::boot();

        static::created(function ($model): void {
            if ($model->hash_id) {
                return;
            }

            $reflect = new ReflectionClass(self::class);
            $connection = Str::lower($reflect->getShortName());

            $model->hash_id = Hashids::connection($connection)
                ->encode((string) $model->id);
            $model->save();
        });
    }
}

Soft Deletes

// In migration
$table->softDeletes();

// In model
use Illuminate\Database\Eloquent\SoftDeletes;

final class Ticket extends Model
{
    use SoftDeletes;
}

Position/Ordering

// In migration
$table->unsignedInteger('position')->default(0);

// In relationship
public function items(): HasMany
{
    return $this->hasMany(Item::class)
        ->orderBy('position')
        ->orderBy('created_at');
}

Query Patterns

Use Eloquent Over Raw Queries

// Good - Eloquent
$tickets = Ticket::query()
    ->where('is_active', true)
    ->where('user_id', $userId)
    ->orderBy('created_at', 'desc')
    ->get();

// Good - With scopes
$tickets = Ticket::active()
    ->forUser($user)
    ->dueThisWeek()
    ->get();

// Avoid unless necessary
$tickets = DB::select('SELECT * FROM tickets WHERE is_active = ?', [true]);

Eager Loading

// Good - Prevent N+1
$tickets = Ticket::with(['user', 'category', 'items'])->get();

// Good - Nested eager loading
$tickets = Ticket::with([
    'user',
    'items.tags',
    'category.parent',
])->get();

// Good - Conditional eager loading
$tickets = Ticket::with(['items' => function ($query) {
    $query->where('is_active', true);
}])->get();

Chunking for Large Datasets

// Good - Memory efficient
Ticket::query()
    ->where('is_active', true)
    ->chunk(100, function ($tickets) {
        foreach ($tickets as $ticket) {
            // Process ticket
        }
    });

// For updates
Ticket::query()
    ->where('status', 'pending')
    ->chunkById(100, function ($tickets) {
        foreach ($tickets as $ticket) {
            $ticket->update(['status' => 'processed']);
        }
    });

Migration Best Practices

Modifying Existing Tables

// Adding columns
public function up(): void
{
    Schema::table('tickets', function (Blueprint $table): void {
        $table->string('priority')->default('normal')->after('status');
        $table->boolean('is_featured')->default(false)->after('is_active');
    });
}

public function down(): void
{
    Schema::table('tickets', function (Blueprint $table): void {
        $table->dropColumn(['priority', 'is_featured']);
    });
}

Renaming Columns

public function up(): void
{
    Schema::table('tickets', function (Blueprint $table): void {
        $table->renameColumn('name', 'title');
    });
}

Adding Indexes to Existing Tables

public function up(): void
{
    Schema::table('tickets', function (Blueprint $table): void {
        $table->index('status');
        $table->index(['user_id', 'status']);
    });
}

public function down(): void
{
    Schema::table('tickets', function (Blueprint $table): void {
        $table->dropIndex(['status']);
        $table->dropIndex(['user_id', 'status']);
    });
}

Seeders

Basic Seeder

<?php

declare(strict_types=1);

namespace Database\Seeders;

use App\Models\Category;
use Illuminate\Database\Seeder;

final class CategorySeeder extends Seeder
{
    public function run(): void
    {
        $categories = [
            ['name' => 'Bug', 'slug' => 'bug'],
            ['name' => 'Feature', 'slug' => 'feature'],
            ['name' => 'Enhancement', 'slug' => 'enhancement'],
        ];

        foreach ($categories as $category) {
            Category::create($category);
        }
    }
}

With Factories

public function run(): void
{
    User::factory()
        ->count(10)
        ->has(Ticket::factory()->count(5))
        ->create();
}