Skip to main content
Back to ScopeForged

ScopeForged Documentation

Technical documentation, guides, and feature references for the ScopeForged client portal.

Architecture Patterns/Database Patterns

Database Patterns Guide

Last Updated: 2026-01-08 Status: Active Audience: Developers

This guide documents Eloquent ORM patterns and database best practices for the Client Portal application.

Table of Contents

  1. Model Conventions
  2. Relationships
  3. Query Optimization
  4. Scopes
  5. Accessors and Mutators
  6. Migrations
  7. Transactions
  8. Soft Deletes

Model Conventions

Basic Model Structure

<?php

namespace App\Models;

use App\Enums\InvoiceStatus;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Illuminate\Database\Eloquent\SoftDeletes;

class Invoice extends Model
{
    use HasFactory, SoftDeletes;

    /* =======================
     * Configuration
     * ======================= */

    protected $fillable = [
        'client_id',
        'invoice_number',
        'status',
        'subtotal',
        'tax',
        'total',
        'due_date',
        'paid_at',
        'notes',
    ];

    protected function casts(): array
    {
        return [
            'status' => InvoiceStatus::class,
            'subtotal' => 'decimal:2',
            'tax' => 'decimal:2',
            'total' => 'decimal:2',
            'due_date' => 'date',
            'paid_at' => 'datetime',
        ];
    }

    /* =======================
     * Relationships
     * ======================= */

    public function client(): BelongsTo
    {
        return $this->belongsTo(Client::class);
    }

    public function items(): HasMany
    {
        return $this->hasMany(InvoiceItem::class);
    }

    /* =======================
     * Scopes
     * ======================= */

    public function scopeOverdue($query)
    {
        return $query->where('status', InvoiceStatus::Sent)
                     ->where('due_date', '<', now());
    }

    public function scopeForClient($query, Client $client)
    {
        return $query->where('client_id', $client->id);
    }

    /* =======================
     * Accessors & Mutators
     * ======================= */

    protected function formattedTotal(): Attribute
    {
        return Attribute::get(fn () => '$' . number_format($this->total, 2));
    }

    /* =======================
     * Helpers
     * ======================= */

    public function isOverdue(): bool
    {
        return $this->status === InvoiceStatus::Sent
            && $this->due_date < now();
    }

    public function markAsPaid(): void
    {
        $this->update([
            'status' => InvoiceStatus::Paid,
            'paid_at' => now(),
        ]);
    }
}

Fillable vs Guarded

Prefer $fillable over $guarded for explicit control:

// Good - Explicit list of mass-assignable fields
protected $fillable = [
    'company_name',
    'email',
    'phone',
];

// Avoid - Less explicit, can be risky
protected $guarded = [];

Attribute Casting

protected function casts(): array
{
    return [
        // Enums
        'status' => InvoiceStatus::class,

        // Dates
        'due_date' => 'date',
        'paid_at' => 'datetime',
        'email_verified_at' => 'datetime',

        // Numbers
        'total' => 'decimal:2',
        'quantity' => 'integer',

        // Booleans
        'is_active' => 'boolean',

        // JSON
        'metadata' => 'array',
        'settings' => 'collection',

        // Encrypted
        'secret_key' => 'encrypted',
    ];
}

Relationships

Defining Relationships

// One to Many
class Client extends Model
{
    public function projects(): HasMany
    {
        return $this->hasMany(Project::class);
    }

    public function invoices(): HasMany
    {
        return $this->hasMany(Invoice::class);
    }
}

class Project extends Model
{
    public function client(): BelongsTo
    {
        return $this->belongsTo(Client::class);
    }
}

// Many to Many
class User extends Model
{
    public function clients(): BelongsToMany
    {
        return $this->belongsToMany(Client::class)
            ->withTimestamps();
    }
}

// Has Many Through
class Client extends Model
{
    public function projectFiles(): HasManyThrough
    {
        return $this->hasManyThrough(ProjectFile::class, Project::class);
    }
}

// Polymorphic
class ActivityLog extends Model
{
    public function subject(): MorphTo
    {
        return $this->morphTo();
    }
}

Relationship with Custom Keys

public function invoices(): HasMany
{
    return $this->hasMany(Invoice::class, 'client_id', 'id');
}

Pivot Table Data

// Define relationship with pivot data
public function users(): BelongsToMany
{
    return $this->belongsToMany(User::class)
        ->withPivot('role', 'permissions')
        ->withTimestamps();
}

// Access pivot data
foreach ($client->users as $user) {
    echo $user->pivot->role;
    echo $user->pivot->created_at;
}

// Attach with pivot data
$client->users()->attach($userId, ['role' => 'manager']);

// Sync with pivot data
$client->users()->sync([
    $userId1 => ['role' => 'manager'],
    $userId2 => ['role' => 'viewer'],
]);

Query Optimization

Eager Loading (Prevent N+1)

// Bad - N+1 queries
$clients = Client::all();
foreach ($clients as $client) {
    echo $client->projects->count(); // Query for each client
}

// Good - Eager load relationships
$clients = Client::with('projects')->get();
foreach ($clients as $client) {
    echo $client->projects->count(); // No additional queries
}

// Nested eager loading
$clients = Client::with([
    'projects' => function ($query) {
        $query->where('status', 'active');
    },
    'projects.files',
    'invoices',
])->get();

// Eager load counts
$clients = Client::withCount('projects')
    ->withCount(['invoices' => fn ($q) => $q->where('status', 'paid')])
    ->get();

// Access counts
echo $client->projects_count;
echo $client->invoices_count;

Lazy Eager Loading

// Load relationships after initial query
$clients = Client::all();

if ($needsProjects) {
    $clients->load('projects');
}

Select Specific Columns

// Bad - Fetches all columns
$clients = Client::all();

// Good - Only fetch needed columns
$clients = Client::select(['id', 'company_name', 'email'])->get();

// With relationships
$clients = Client::select(['id', 'company_name'])
    ->with(['projects' => fn ($q) => $q->select(['id', 'client_id', 'name'])])
    ->get();

Chunking Large Datasets

// Process large datasets in chunks
Client::chunk(100, function ($clients) {
    foreach ($clients as $client) {
        // Process each client
    }
});

// With lazy collections (memory efficient)
Client::lazy()->each(function ($client) {
    // Process each client
});

// Chunking by ID (for updates)
Client::chunkById(100, function ($clients) {
    $clients->each->update(['processed' => true]);
});

Pagination

// Simple pagination
$clients = Client::paginate(15);

// With eager loading
$clients = Client::with('projects')
    ->orderBy('company_name')
    ->paginate(15);

// Cursor pagination (better for large datasets)
$clients = Client::orderBy('id')->cursorPaginate(15);

Scopes

Local Scopes

class Invoice extends Model
{
    // Simple scope
    public function scopeUnpaid($query)
    {
        return $query->where('status', InvoiceStatus::Sent);
    }

    // Scope with parameters
    public function scopeForClient($query, Client $client)
    {
        return $query->where('client_id', $client->id);
    }

    // Scope with optional parameters
    public function scopeStatus($query, ?InvoiceStatus $status = null)
    {
        return $status
            ? $query->where('status', $status)
            : $query;
    }
}

// Usage
$invoices = Invoice::unpaid()->get();
$invoices = Invoice::forClient($client)->get();
$invoices = Invoice::status($status)->paginate();

Global Scopes

// app/Models/Scopes/ActiveScope.php
namespace App\Models\Scopes;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Scope;

class ActiveScope implements Scope
{
    public function apply(Builder $builder, Model $model): void
    {
        $builder->where('is_active', true);
    }
}

// Apply to model
class Client extends Model
{
    protected static function booted(): void
    {
        static::addGlobalScope(new ActiveScope);
    }
}

// Disable global scope
Client::withoutGlobalScope(ActiveScope::class)->get();
Client::withoutGlobalScopes()->get();

Accessors and Mutators

Modern Syntax (Laravel 9+)

use Illuminate\Database\Eloquent\Casts\Attribute;

class User extends Model
{
    // Accessor only
    protected function fullName(): Attribute
    {
        return Attribute::get(
            fn () => "{$this->first_name} {$this->last_name}"
        );
    }

    // Mutator only
    protected function password(): Attribute
    {
        return Attribute::set(
            fn (string $value) => bcrypt($value)
        );
    }

    // Both accessor and mutator
    protected function email(): Attribute
    {
        return Attribute::make(
            get: fn (string $value) => strtolower($value),
            set: fn (string $value) => strtolower($value),
        );
    }
}

// Usage
$user->full_name;  // "John Doe"
$user->email = 'TEST@EXAMPLE.COM'; // Stored as "test@example.com"

Appending Attributes to JSON

class Invoice extends Model
{
    protected $appends = ['formatted_total', 'is_overdue'];

    protected function formattedTotal(): Attribute
    {
        return Attribute::get(fn () => '$' . number_format($this->total, 2));
    }

    protected function isOverdue(): Attribute
    {
        return Attribute::get(fn () => $this->due_date < now());
    }
}

// JSON output includes formatted_total and is_overdue
$invoice->toArray();

Migrations

Creating Migrations

# Create table
php artisan make:migration create_invoices_table

# Add column
php artisan make:migration add_notes_to_invoices_table

# Modify column
php artisan make:migration modify_status_in_invoices_table

Migration Best Practices

// database/migrations/2024_01_01_000001_create_invoices_table.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('invoices', function (Blueprint $table) {
            $table->id();

            // Foreign key with constraint
            $table->foreignId('client_id')
                  ->constrained()
                  ->onDelete('cascade');

            // Unique constraint
            $table->string('invoice_number')->unique();

            // Enum/string for status
            $table->string('status')->default('draft');

            // Decimal for money
            $table->decimal('subtotal', 10, 2)->default(0);
            $table->decimal('tax', 10, 2)->default(0);
            $table->decimal('total', 10, 2)->default(0);

            // Dates
            $table->date('due_date');
            $table->timestamp('paid_at')->nullable();

            // Optional text
            $table->text('notes')->nullable();

            // Standard timestamps
            $table->timestamps();
            $table->softDeletes();

            // Indexes for commonly queried columns
            $table->index('status');
            $table->index('due_date');
            $table->index(['client_id', 'status']);
        });
    }

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

Adding Columns Safely

// Add column with default (no data migration needed)
Schema::table('invoices', function (Blueprint $table) {
    $table->boolean('is_recurring')->default(false)->after('status');
});

// Add nullable column
Schema::table('invoices', function (Blueprint $table) {
    $table->string('reference')->nullable()->after('invoice_number');
});

Indexing Guidelines

// Index foreign keys (usually automatic with constrained())
$table->index('client_id');

// Index columns used in WHERE clauses
$table->index('status');
$table->index('due_date');

// Composite index for combined queries
$table->index(['client_id', 'status']);
$table->index(['status', 'due_date']);

// Unique index
$table->unique('invoice_number');
$table->unique(['client_id', 'invoice_number']); // Composite unique

Transactions

Basic Transactions

use Illuminate\Support\Facades\DB;

// Automatic rollback on exception
DB::transaction(function () {
    $invoice = Invoice::create([...]);

    foreach ($items as $item) {
        $invoice->items()->create($item);
    }

    // Exception here rolls back everything
});

// With manual control
DB::beginTransaction();

try {
    $invoice = Invoice::create([...]);
    // ... more operations

    DB::commit();
} catch (\Exception $e) {
    DB::rollBack();
    throw $e;
}

Transaction Callbacks

DB::transaction(function () use ($invoice) {
    $invoice->update(['status' => 'paid']);

    // This runs AFTER commit
    DB::afterCommit(function () use ($invoice) {
        Mail::send(new InvoicePaidNotification($invoice));
    });
});

Deadlock Retry

// Retry transaction on deadlock
DB::transaction(function () {
    // ... operations
}, 5); // Retry up to 5 times

Soft Deletes

Setup

use Illuminate\Database\Eloquent\SoftDeletes;

class Client extends Model
{
    use SoftDeletes;
}

// Migration
$table->softDeletes(); // Adds deleted_at column

Querying Soft Deleted Records

// Normal query (excludes soft deleted)
$clients = Client::all();

// Include soft deleted
$clients = Client::withTrashed()->get();

// Only soft deleted
$clients = Client::onlyTrashed()->get();

// Check if soft deleted
if ($client->trashed()) {
    // ...
}

Restoring and Force Deleting

// Restore soft deleted record
$client->restore();

// Permanently delete
$client->forceDelete();

// Permanently delete all trashed records
Client::onlyTrashed()
    ->where('deleted_at', '<', now()->subYear())
    ->forceDelete();

Cascade Soft Deletes

class Client extends Model
{
    use SoftDeletes;

    protected static function booted(): void
    {
        static::deleting(function (Client $client) {
            // Soft delete related records
            $client->projects()->delete();
            $client->invoices()->delete();
        });

        static::restoring(function (Client $client) {
            // Restore related records
            $client->projects()->withTrashed()->restore();
            $client->invoices()->withTrashed()->restore();
        });
    }
}

Best Practices Summary

Do

  • Use Eloquent relationships and eager loading
  • Use $fillable for mass assignment protection
  • Use attribute casting for type consistency
  • Use scopes for reusable query logic
  • Use transactions for multi-step operations
  • Add indexes for frequently queried columns
  • Use soft deletes for recoverable data

Avoid

  • N+1 queries (use eager loading)
  • Raw SQL unless absolutely necessary
  • Fetching all columns when only a few are needed
  • Large queries without pagination or chunking
  • Hard deletes for important business data