maiobarbero.dev
arrow_back Back to Course |

Personal finance with Laravel and Filament

Lesson 2 / 3
Lesson 2

Creating the database

Creating the Database

Every great application stands on the shoulders of a solid data model. If the database schema isn’t right, everything else, from your eloquently written controllers to your beautiful Filament resources, will feel like fighting gravity.

In this lesson, we are going to define the core entities of our Personal Finance application. We aren’t just creating tables; we are defining the vocabulary of our domain.

The Entities

Our application needs to track money moving in and out. To do that effectively, we need four primary concepts:

  1. Bank Accounts: Where the money lives.
  2. Categories: How we classify the money (e.g., “Utilities”, “Dining Out”).
  3. Budgets: Our financial goals or limits.
  4. Transactions: The heart of the system, the actual record of spending or earning.

Generating the Artifacts

Laravel makes scaffolding these incredible easy. We need a Model, a Migration, a Factory, and a Seeder for each of our entities. Instead of running four commands per entity, we can use the -mfs flags.

Run the following commands in your terminal:

bash
php artisan make:model Category -mfs
php artisan make:model Budget -mfs
php artisan make:model BankAccount -mfs
php artisan make:model Transaction -mfs

You should see a flurry of green success messages. We now have our files ready to be sculpted.

A Note on “Down” Methods

Open up one of your new migration files. You’ll see an up method and a down method.

I’m going to ask you to do something that might feel rebellious: delete the down method.

Why? In this project, we are adopting a Fix Forward strategy. In a production environment with real data, rolling back a migration (especially one that drops columns or tables) is destructive and risky. If we make a mistake in a migration that has already run, we don’t roll it back, we create a new migration to fix the issue. This keeps our database history linear and truthful.

So, go ahead and remove public function down(): void from all your new migration files. It clarifies our intent: we only move forward.

Defining the Schema

Let’s define the structure of our tables.

1. Enums

Before we get to the tables, we need a way to define the type of a Budget. Is it a fixed budget that we reset every month? Or is it a rolling budget that we keep track of over time? Let’s use a PHP Enum for this to ensure type safety.

bash
php artisan make:enum Enums/BudgetType

Edit app/Enums/BudgetType.php:

php
<?php

namespace App\Enums;

enum BudgetType: string
{
    case Reset = 'reset';
    case Rollover = 'rollover';

    public function getLabel(): string
    {
        return match ($this) {
            self::Reset => 'Reset',
            self::Rollover => 'Rollover',
        };
    }
}

2. Migrations

Now, let’s fill in our up methods.

create_categories_table.php Categories are simple. They have a name and belong to a user.

php
Schema::create('categories', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->cascadeOnDelete();
    $table->string('name');
    $table->timestamps();

    $table->unique(['user_id', 'name']);
});

create_budgets_table.php A budget tracks a limit for a specific period.

php
Schema::create('budgets', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->cascadeOnDelete();
    $table->string('name');
    $table->integer('amount')->default(0);
    $table->string('type')->default('fixed');
    $table->timestamps();
    $table->unique(['user_id', 'name']);
});

create_bank_accounts_table.php Represents a physical or digital account.

php
Schema::create('bank_accounts', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->cascadeOnDelete();
    $table->string('name');
    $table->unsignedBigInteger('balance')->default(0);
    $table->timestamps();

    $table->unique(['user_id', 'name']);
});

create_transactions_table.php The center of our universe. Links everything together.

php
Schema::create('transactions', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->cascadeOnDelete();
    $table->foreignId('bank_account_id')->constrained()->cascadeOnDelete();
    $table->string('description');
    $table->foreignId('category_id')->constrained()->cascadeOnDelete();
    $table->foreignId('budget_id')->constrained()->cascadeOnDelete();
    $table->date('date');
    $table->text('note')->nullable();
    $table->unsignedInteger('amount');
    $table->timestamps();
});

[!TIP] Notice we store money as integers (cents) rather than floats or decimals. Floating point math can be imprecise. storing $10.00 as 1000 is deeply robust.

3. The Money Cast

Since we are storing money as integers (cents) but want to work with it as standard units (dollars/euros) in our code, let’s create a custom Cast. This encourages consistency across our application.

Run:

bash
php artisan make:cast MoneyCast

Update app/Casts/MoneyCast.php:

php
<?php

namespace App\Casts;

use Illuminate\Contracts\Database\Eloquent\CastsAttributes;
use Illuminate\Database\Eloquent\Model;

class MoneyCast implements CastsAttributes
{
    /**
     * Cast the given value.
     *
     * @param  array<string, mixed>  $attributes
     */
    public function get(Model $model, string $key, mixed $value, array $attributes): mixed
    {
        return $value / 100;
    }

    /**
     * Prepare the given value for storage.
     *
     * @param  array<string, mixed>  $attributes
     */
    public function set(Model $model, string $key, mixed $value, array $attributes): mixed
    {
        return (int) round($value * 100);
    }
}

4. The Models

Now we breathe life into our schemas by defining relationships and behaviors in our Models.

User.php The user owns everything.

php
// ... imports
use Illuminate\Database\Eloquent\Relations\HasMany;

class User extends Authenticatable
{
    // ... traits

    // ... fillable & hidden

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

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

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

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

Category.php

php
class Category extends Model
{
    /** @use HasFactory<\Database\Factories\CategoryFactory> */
    use HasFactory;

    protected $fillable = [
        'user_id',
        'name',
    ];

    public function user(): \Illuminate\Database\Eloquent\Relations\BelongsTo
    {
        return $this->belongsTo(User::class);
    }

    public function transactions(): \Illuminate\Database\Eloquent\Relations\HasMany
    {
        return $this->hasMany(Transaction::class);
    }
}

Budget.php Here we treat our type as an Enum and amount with our new MoneyCast.

php
class Budget extends Model
{
    /** @use HasFactory<\Database\Factories\BudgetFactory> */
    use HasFactory;

    protected $fillable = [
        'user_id',
        'name',
        'amount',
        'type',
    ];

    protected function casts(): array
    {
        return [
            'type' => \App\Enums\BudgetType::class,
            'amount' => \App\Casts\MoneyCast::class,
        ];
    }

    public function user(): \Illuminate\Database\Eloquent\Relations\BelongsTo
    {
        return $this->belongsTo(User::class);
    }

    public function transactions(): \Illuminate\Database\Eloquent\Relations\HasMany
    {
        return $this->hasMany(Transaction::class);
    }
}

BankAccount.php

php
class BankAccount extends Model
{
    /** @use HasFactory<\Database\Factories\BankAccountFactory> */
    use HasFactory;

    protected $fillable = [
        'user_id',
        'name',
        'balance',
    ];

    protected function casts(): array
    {
        return [
            'balance' => \App\Casts\MoneyCast::class,
        ];
    }

    public function user(): \Illuminate\Database\Eloquent\Relations\BelongsTo
    {
        return $this->belongsTo(User::class);
    }

    public function transactions(): \Illuminate\Database\Eloquent\Relations\HasMany
    {
        return $this->hasMany(Transaction::class);
    }
}

Transaction.php The nexus. Note the mass assignment protection and casting.

php
class Transaction extends Model
{
    /** @use HasFactory<\Database\Factories\TransactionFactory> */
    use HasFactory;

    protected $fillable = [
        'user_id',
        'bank_account_id',
        'category_id',
        'budget_id',
        'description',
        'amount',
        'note',
        'date',
    ];

    protected function casts(): array
    {
        return [
            'amount' => \App\Casts\MoneyCast::class,
        ];
    }

    public function user(): \Illuminate\Database\Eloquent\Relations\BelongsTo
    {
        return $this->belongsTo(User::class);
    }

    public function bankAccount(): \Illuminate\Database\Eloquent\Relations\BelongsTo
    {
        return $this->belongsTo(BankAccount::class);
    }

    public function category(): \Illuminate\Database\Eloquent\Relations\BelongsTo
    {
        return $this->belongsTo(Category::class);
    }

    public function budget(): \Illuminate\Database\Eloquent\Relations\BelongsTo
    {
        return $this->belongsTo(Budget::class);
    }
}

Seeding and Verification

With our structure defined, let’s spin up the database.

First, update your DatabaseSeeder.php to call the new seeders:

php
class DatabaseSeeder extends Seeder
{
    use WithoutModelEvents;

    /**
     * Seed the application's database.
     */
    public function run(): void
    {
        $this->call(UserSeeder::class);
        $this->call(CategorySeeder::class);
        $this->call(BudgetSeeder::class);
        $this->call(BankAccountSeeder::class);
        $this->call(TransactionSeeder::class);
    }
}

(We’ll cover Factories in depth in a future lesson, but for now, ensure your factories create dummy data).

Then, run:

bash
php artisan migrate:fresh --seeder

Now, let’s verify our work using Tinker, the best tool for checking your data reality.

bash
php artisan tinker

Try fetching a user and their transactions:

php
$user = \App\Models\User::first();
$user->transactions()->get();

If you see a Collection of Transaction models, congratulations. You have successfully mapped the physical world of finance into your digital domain.

Finally, before we commit, let’s make sure our code style is impeccable.

bash
./vendor/bin/pint

Keep moving forward.

quiz

Knowledge Check

1. Why might we choose to remove the `down` method from our migrations?

2. Which command creates a Model, Migration, Factory, and Seeder all at once?

3. When defining a foreign key for a `user_id` column in a migration, representing a User model, what is the standard Laravel way?