Skip to main content
Cashify uses a relational database structure with five core tables for managing user finances.

​
Entity Relationship Diagram

Users (1) ──── (∞) Accounts
  │                   │
  │                   │
  │ (1)              (∞)
  │                   │
  └──── (∞) Categories │
          │            │
         (∞)          (1)
          │            │
          └─ Transactions

Users (1) ──── (∞) NetWorths

​
Tables Overview

Core Tables

  • users - User accounts and authentication
  • accounts - Financial accounts (bank, cash, etc.)
  • categories - Transaction categories
  • transactions - Income/expense records

Supporting Tables

  • net_worths - Historical net worth snapshots
  • sessions - User session management
  • password_reset_tokens - Password resets
  • jobs - Queue management

​
Users Table

Stores user authentication and profile information.
database/migrations/0001_01_01_000000_create_users_table.php
Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('email')->unique();
    $table->timestamp('email_verified_at')->nullable();
    $table->string('password')->nullable();
    $table->rememberToken();
    $table->string('provider')->nullable();
    $table->string('provider_id')->nullable()->unique();
    $table->timestamps();
});

​
Fields

ColumnTypeDescription
idbigint unsignedPrimary key
namevarchar(255)User’s full name
emailvarchar(255)Email address (unique)
email_verified_attimestampEmail verification timestamp
passwordvarchar(255)Hashed password (nullable for OAuth users)
remember_tokenvarchar(100)Remember me token
providervarchar(255)OAuth provider name (e.g., ‘google’, ‘github’)
provider_idvarchar(255)OAuth provider user ID (unique)
created_attimestampAccount creation timestamp
updated_attimestampLast update timestamp
The password field is nullable to support OAuth authentication via Laravel Socialite. Users who sign in with Google or other providers don’t need a password.

​
Relationships

app/Models/User.php
public function accounts(): HasMany
{
    return $this->hasMany(Account::class);
}

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

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

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

​
Accounts Table

Represents financial accounts like bank accounts, wallets, or credit cards.
database/migrations/2024_05_22_071335_create_accounts_table.php
Schema::create('accounts', function (Blueprint $table) {
    $table->id();
    $table->foreignIdFor(User::class)->constrained()->cascadeOnDelete();
    $table->string('name');
    $table->decimal('balance', 15, 2)->default(0);
    $table->string('color')->default('gray');
    $table->timestamps();

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

​
Fields

ColumnTypeDescription
idbigint unsignedPrimary key
user_idbigint unsignedForeign key to users table
namevarchar(255)Account name (e.g., “Main Bank”, “Cash Wallet”)
balancedecimal(15,2)Current account balance
colorvarchar(255)Tailwind color name for UI display
created_attimestampCreation timestamp
updated_attimestampLast update timestamp

​
Indexes

  • name - For searching accounts by name
  • (user_id, balance) - Composite index for net worth calculations
Account balances are automatically updated when transactions are created, updated, or deleted. The balance is always the sum of all transaction amounts.

​
Relationships

app/Models/Account.php
public function user(): BelongsTo
{
    return $this->belongsTo(User::class);
}

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

​
Categories Table

Defines categories for classifying transactions.
database/migrations/2024_05_26_074754_create_categories_table.php
Schema::create('categories', function (Blueprint $table) {
    $table->id();
    $table->foreignIdFor(User::class)->constrained()->cascadeOnDelete();
    $table->string('name');
    $table->enum('type', ['income', 'expense', 'correction', 'transfer']);
    $table->string('color')->default('gray');
    $table->string('icon')->default('image');
    $table->timestamps();

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

​
Fields

ColumnTypeDescription
idbigint unsignedPrimary key
user_idbigint unsignedForeign key to users table
namevarchar(255)Category name (e.g., “Groceries”, “Salary”)
typeenumOne of: income, expense, correction, transfer
colorvarchar(255)Tailwind color name for UI display
iconvarchar(255)Icon filename for visual representation
created_attimestampCreation timestamp
updated_attimestampLast update timestamp

​
Category Types

Categories for money coming in (salary, gifts, freelance work, etc.)

​
Default Categories

New users automatically receive default categories via the CreateDefaultCategories listener:
config/default-categories.php
return [
    // Income
    ['name' => 'Salary', 'type' => 'income', 'color' => 'indigo', 'icon' => 'coin'],
    ['name' => 'Gift', 'type' => 'income', 'color' => 'blue', 'icon' => 'piggy-bank'],
    
    // Expenses
    ['name' => 'Groceries', 'type' => 'expense', 'color' => 'green', 'icon' => 'groceries'],
    ['name' => 'Junk Food', 'type' => 'expense', 'color' => 'sky', 'icon' => 'fast-food'],
    ['name' => 'Eating Out', 'type' => 'expense', 'color' => 'cyan', 'icon' => 'food-tray'],
    ['name' => 'Rent', 'type' => 'expense', 'color' => 'gray', 'icon' => 'rent'],
    ['name' => 'Bills', 'type' => 'expense', 'color' => 'blue', 'icon' => 'paper-bill'],
    
    // Special
    ['name' => 'Balance Correction', 'type' => 'correction', 'color' => 'gray', 'icon' => 'charts'],
    ['name' => 'Balance Transfer', 'type' => 'transfer', 'color' => 'gray', 'icon' => 'exchange-arrows'],
];

​
Indexes

  • name - For category search and autocomplete
  • type - For filtering by category type
  • user_id - For user-specific queries
  • (user_id, type) - Composite index for filtered category lists

​
Transactions Table

Records all financial transactions.
database/migrations/2024_06_03_143109_create_transactions_table.php
Schema::create('transactions', function (Blueprint $table) {
    $table->id();
    $table->foreignIdFor(User::class)->constrained()->cascadeOnDelete();
    $table->foreignIdFor(Category::class)->constrained()->cascadeOnDelete();
    $table->foreignIdFor(Account::class)->constrained()->cascadeOnDelete();
    $table->string('title');
    $table->decimal('amount', 15, 2)->default(0);
    $table->text('details')->nullable();
    $table->timestamps();
    
    $table->index('title');
    $table->index('user_id');
    $table->index('category_id');
    $table->index('account_id');
    $table->index(['user_id', 'category_id']);
    $table->index(['user_id', 'account_id']);
});

​
Fields

ColumnTypeDescription
idbigint unsignedPrimary key
user_idbigint unsignedForeign key to users table
category_idbigint unsignedForeign key to categories table
account_idbigint unsignedForeign key to accounts table
titlevarchar(255)Transaction description
amountdecimal(15,2)Transaction amount (negative for expenses)
detailstextOptional transaction notes
created_attimestampTransaction date/time
updated_attimestampLast update timestamp
Expense transactions are stored with negative amounts. This is handled automatically in the controller:
if ($category->type == 'expense') {
    $attributes['amount'] = -abs($attributes['amount']);
}

​
Indexes

  • title - For transaction search
  • user_id, category_id, account_id - Individual foreign key indexes
  • (user_id, category_id) - For category-specific transaction queries
  • (user_id, account_id) - For account-specific transaction queries

​
Cascade Deletion

All foreign keys use cascadeOnDelete(), meaning:
  • Deleting a user deletes all their transactions
  • Deleting an account deletes all its transactions (with balance correction)
  • Deleting a category deletes all transactions in that category
In practice, the TransactionController::destroy() method creates a correction transaction before deletion to maintain accurate balance history.

​
Net Worths Table

Stores historical snapshots of user net worth for tracking over time.
database/migrations/2024_06_14_110400_create_net_worths_table.php
Schema::create('net_worths', function (Blueprint $table) {
    $table->id();
    $table->foreignIdFor(User::class)->constrained()->cascadeOnDelete();
    $table->decimal('net_worth', 15, 2)->default(0);
    $table->timestamps();

    $table->index('user_id');
});

​
Fields

ColumnTypeDescription
idbigint unsignedPrimary key
user_idbigint unsignedForeign key to users table
net_worthdecimal(15,2)Total net worth snapshot
created_attimestampSnapshot timestamp
updated_attimestampLast update timestamp

​
Usage

Net worth snapshots are created automatically whenever transactions are created, updated, or deleted:
app/helpers.php
function updateNetworth(): void
{
    $netWorth = Account::where('user_id', Auth::id())->sum('balance');

    Auth::user()->netWorth()->create([
        'net_worth' => $netWorth,
    ]);
}
This creates a time-series of net worth data used for the dashboard charts and trend analysis.

​
Supporting Tables

​
Sessions Table

Schema::create('sessions', function (Blueprint $table) {
    $table->string('id')->primary();
    $table->foreignId('user_id')->nullable()->index();
    $table->string('ip_address', 45)->nullable();
    $table->text('user_agent')->nullable();
    $table->longText('payload');
    $table->integer('last_activity')->index();
});
Manages user session data for the database session driver.

​
Password Reset Tokens

Schema::create('password_reset_tokens', function (Blueprint $table) {
    $table->string('email')->primary();
    $table->string('token');
    $table->timestamp('created_at')->nullable();
});
Stores temporary password reset tokens.

​
Data Integrity

1

Foreign Key Constraints

All relationships use foreign key constraints with cascade delete to maintain referential integrity.
2

Indexes

Strategic indexes on frequently queried columns ensure fast query performance.
3

Decimal Precision

All monetary values use decimal(15,2) for accurate financial calculations without floating-point errors.
4

Automatic Timestamps

Laravel’s timestamp feature tracks creation and modification times automatically.

​
Query Examples

​
Get user’s total balance

$totalBalance = Account::where('user_id', Auth::id())->sum('balance');

​
Get transactions by date range with filters

$transactions = Transaction::query()
    ->with(['category', 'account'])
    ->where('user_id', Auth::id())
    ->whereBetween('created_at', [$startDate, $endDate])
    ->whereHas('category', function ($q) {
        $q->where('type', 'expense');
    })
    ->orderBy('created_at', 'desc')
    ->paginate(10);

​
Calculate spending by category

$spending = Transaction::query()
    ->selectRaw('category_id, SUM(amount) as total')
    ->where('user_id', Auth::id())
    ->whereHas('category', fn($q) => $q->where('type', 'expense'))
    ->groupBy('category_id')
    ->with('category')
    ->get();

Build docs developers (and LLMs) love

Get started for freeTalk to us