Laravel Financial Dashboards

Financial dashboards require fast aggregations and real-time updates. PostgreSQL's analytics capabilities with Redis caching deliver responsive experiences. At ZIRA Software, we've built dashboards processing millions of financial transactions.

Database Schema

// Transactions table with partitioning support
Schema::create('transactions', function (Blueprint $table) {
    $table->id();
    $table->foreignId('account_id')->constrained();
    $table->foreignId('category_id')->nullable()->constrained();
    $table->string('type'); // income, expense, transfer
    $table->decimal('amount', 15, 2);
    $table->string('currency', 3)->default('USD');
    $table->string('description')->nullable();
    $table->string('reference')->nullable();
    $table->json('metadata')->nullable();
    $table->timestamp('transaction_date');
    $table->timestamps();

    $table->index(['account_id', 'transaction_date']);
    $table->index(['type', 'transaction_date']);
});

// Pre-aggregated daily summaries
Schema::create('daily_summaries', function (Blueprint $table) {
    $table->id();
    $table->foreignId('account_id')->constrained();
    $table->date('date');
    $table->decimal('income', 15, 2)->default(0);
    $table->decimal('expenses', 15, 2)->default(0);
    $table->decimal('balance', 15, 2)->default(0);
    $table->integer('transaction_count')->default(0);
    $table->timestamps();

    $table->unique(['account_id', 'date']);
});

Dashboard Service

// app/Services/DashboardService.php
class DashboardService
{
    public function __construct(
        private TransactionRepository $transactions,
    ) {}

    public function getOverview(User $user, string $period = '30d'): array
    {
        $cacheKey = "dashboard:overview:{$user->id}:{$period}";

        return Cache::remember($cacheKey, 300, function () use ($user, $period) {
            $dateRange = $this->getDateRange($period);

            return [
                'total_income' => $this->getTotalIncome($user, $dateRange),
                'total_expenses' => $this->getTotalExpenses($user, $dateRange),
                'net_change' => $this->getNetChange($user, $dateRange),
                'current_balance' => $this->getCurrentBalance($user),
                'transaction_count' => $this->getTransactionCount($user, $dateRange),
            ];
        });
    }

    private function getTotalIncome(User $user, array $dateRange): float
    {
        return Transaction::whereIn('account_id', $user->accounts->pluck('id'))
            ->where('type', 'income')
            ->whereBetween('transaction_date', $dateRange)
            ->sum('amount');
    }

    private function getTotalExpenses(User $user, array $dateRange): float
    {
        return Transaction::whereIn('account_id', $user->accounts->pluck('id'))
            ->where('type', 'expense')
            ->whereBetween('transaction_date', $dateRange)
            ->sum('amount');
    }
}

PostgreSQL Analytics Queries

// app/Repositories/TransactionRepository.php
class TransactionRepository
{
    public function getSpendingByCategory(User $user, Carbon $from, Carbon $to): Collection
    {
        return DB::table('transactions')
            ->join('categories', 'transactions.category_id', '=', 'categories.id')
            ->whereIn('transactions.account_id', $user->accounts->pluck('id'))
            ->where('transactions.type', 'expense')
            ->whereBetween('transactions.transaction_date', [$from, $to])
            ->select([
                'categories.name as category',
                'categories.color',
                DB::raw('SUM(transactions.amount) as total'),
                DB::raw('COUNT(*) as count'),
                DB::raw('ROUND(SUM(transactions.amount) * 100.0 / SUM(SUM(transactions.amount)) OVER (), 2) as percentage'),
            ])
            ->groupBy('categories.id', 'categories.name', 'categories.color')
            ->orderByDesc('total')
            ->get();
    }

    public function getDailyTrend(User $user, Carbon $from, Carbon $to): Collection
    {
        return DB::table('transactions')
            ->whereIn('account_id', $user->accounts->pluck('id'))
            ->whereBetween('transaction_date', [$from, $to])
            ->select([
                DB::raw("DATE(transaction_date) as date"),
                DB::raw("SUM(CASE WHEN type = 'income' THEN amount ELSE 0 END) as income"),
                DB::raw("SUM(CASE WHEN type = 'expense' THEN amount ELSE 0 END) as expenses"),
                DB::raw("SUM(CASE WHEN type = 'income' THEN amount ELSE -amount END) as net"),
            ])
            ->groupBy(DB::raw('DATE(transaction_date)'))
            ->orderBy('date')
            ->get();
    }

    public function getRunningBalance(User $user, Carbon $from, Carbon $to): Collection
    {
        // Using window functions for running total
        return DB::table('daily_summaries')
            ->whereIn('account_id', $user->accounts->pluck('id'))
            ->whereBetween('date', [$from, $to])
            ->select([
                'date',
                'income',
                'expenses',
                DB::raw('SUM(income - expenses) OVER (ORDER BY date) as running_balance'),
            ])
            ->orderBy('date')
            ->get();
    }
}

Real-Time Updates with Redis

// app/Services/RealTimeBalanceService.php
class RealTimeBalanceService
{
    public function updateBalance(Account $account, float $amount): void
    {
        $key = "balance:{$account->id}";

        Redis::incrbyfloat($key, $amount);

        // Broadcast update
        broadcast(new BalanceUpdated($account, $this->getBalance($account)));
    }

    public function getBalance(Account $account): float
    {
        $key = "balance:{$account->id}";

        $cached = Redis::get($key);

        if ($cached === null) {
            $balance = $account->transactions()->sum(
                DB::raw("CASE WHEN type = 'income' THEN amount ELSE -amount END")
            );

            Redis::set($key, $balance);
            return $balance;
        }

        return (float) $cached;
    }

    public function invalidateCache(Account $account): void
    {
        Redis::del("balance:{$account->id}");
        Cache::tags(["user:{$account->user_id}"])->flush();
    }
}

Dashboard Controller

// app/Http/Controllers/DashboardController.php
class DashboardController extends Controller
{
    public function __construct(
        private DashboardService $dashboard,
        private TransactionRepository $transactions,
    ) {}

    public function index(Request $request)
    {
        $user = $request->user();
        $period = $request->get('period', '30d');

        return response()->json([
            'overview' => $this->dashboard->getOverview($user, $period),
            'spending_by_category' => $this->transactions->getSpendingByCategory(
                $user,
                now()->subDays(30),
                now()
            ),
            'daily_trend' => $this->transactions->getDailyTrend(
                $user,
                now()->subDays(30),
                now()
            ),
        ]);
    }

    public function spending(Request $request)
    {
        $validated = $request->validate([
            'from' => 'required|date',
            'to' => 'required|date|after:from',
            'group_by' => 'in:day,week,month',
        ]);

        return response()->json(
            $this->transactions->getSpendingByCategory(
                $request->user(),
                Carbon::parse($validated['from']),
                Carbon::parse($validated['to'])
            )
        );
    }
}

Frontend Dashboard

// resources/js/components/FinancialDashboard.vue
export default {
    data() {
        return {
            overview: null,
            spendingData: [],
            trendData: [],
            period: '30d',
        };
    },

    async mounted() {
        await this.loadDashboard();
        this.setupRealTimeUpdates();
    },

    methods: {
        async loadDashboard() {
            const response = await fetch(`/api/dashboard?period=${this.period}`);
            const data = await response.json();

            this.overview = data.overview;
            this.spendingData = data.spending_by_category;
            this.trendData = data.daily_trend;

            this.renderCharts();
        },

        setupRealTimeUpdates() {
            Echo.private(`user.${this.userId}`)
                .listen('BalanceUpdated', (e) => {
                    this.overview.current_balance = e.balance;
                })
                .listen('TransactionCreated', () => {
                    this.loadDashboard();
                });
        },

        renderCharts() {
            // Spending pie chart
            new Chart(this.$refs.spendingChart, {
                type: 'doughnut',
                data: {
                    labels: this.spendingData.map(d => d.category),
                    datasets: [{
                        data: this.spendingData.map(d => d.total),
                        backgroundColor: this.spendingData.map(d => d.color),
                    }],
                },
            });

            // Trend line chart
            new Chart(this.$refs.trendChart, {
                type: 'line',
                data: {
                    labels: this.trendData.map(d => d.date),
                    datasets: [
                        {
                            label: 'Income',
                            data: this.trendData.map(d => d.income),
                            borderColor: '#10B981',
                        },
                        {
                            label: 'Expenses',
                            data: this.trendData.map(d => d.expenses),
                            borderColor: '#EF4444',
                        },
                    ],
                },
            });
        },
    },
};

Conclusion

Financial dashboards with Laravel, PostgreSQL, and Redis deliver fast, real-time insights. Pre-aggregation and caching ensure responsive experiences even with large datasets.

Building fintech applications? Contact ZIRA Software for financial platform development.