Forem: Chris Lloyd Fallaria

Why I Stopped Using Raw SQL Date Functions and Switched to Carbon in Laravel

Chris Lloyd Fallaria — Tue, 05 May 2026 05:02:05 +0000

It Started With a Bug

When I was building VMMS — a voucher management system
for government offices — everything worked fine locally.

MySQL. Clean queries. Fast results.

Then I deployed to a server running MariaDB.

Half my charts broke.

The Problem

I had written date queries like this all over the codebase:

// This breaks on MariaDB
DB::table('voucher_transactions')
    ->selectRaw('MONTHNAME(created_at) as month, COUNT(*) as total')
    ->groupByRaw('MONTH(created_at), MONTHNAME(created_at)')
    ->get();

MONTH() and MONTHNAME() are MySQL functions. They work
fine on MySQL but behave differently on MariaDB — especially
when combined with GROUP BY.

The result? Months showing up in the wrong order.
Duplicate entries. Missing data.

Why This Happens

MySQL and MariaDB have diverged over the years. They share
a lot of syntax but handle certain functions differently —
especially around date grouping and ordering.

The safe rule is: if you want your app to work on both,
don't rely on database-specific date functions.

The Fix — Pull the Data and Use Carbon

Instead of doing date calculations in SQL, I pulled the
raw data and used Carbon to handle everything in PHP:

// DB-agnostic approach
$rows = VoucherTransaction::where('user_id', $userId)
    ->whereYear('created_at', $year)
    ->whereNull('deleted_at')
    ->get(['created_at', 'status']);

$byMonth = [];
foreach ($rows as $row) {
    $m = (int) $row->created_at->format('n');
    $status = strtolower($row->status);
    $byMonth[$m] = $byMonth[$m] ?? ['accomplished' => 0, 'rejected' => 0];
    if ($status === 'accomplished') $byMonth[$m]['accomplished']++;
    if ($status === 'rejected')     $byMonth[$m]['rejected']++;
}

ksort($byMonth);

Then to get the month name I used Carbon instead of
MONTHNAME():

$monthName = Carbon::create($year, $m, 1)->format('F');
// Returns "January", "February", etc.
// Works the same on MySQL and MariaDB

Building the Full Year Chart

For charts that need all 12 months — even empty ones —
I use range(1, 12) and fill in zeros for months with
no data:

return collect(range(1, 12))->map(function ($m) use ($byMonth, $year) {
    $d = $byMonth[$m] ?? ['accomplished' => 0, 'rejected' => 0];
    return [
        'month'        => Carbon::create($year, $m, 1)->format('F'),
        'accomplished' => $d['accomplished'],
        'rejected'     => $d['rejected'],
    ];
});

This guarantees all 12 months always appear in the chart
— even if there's no data for some months. Clean and
predictable.

What About Performance?

You might be thinking — isn't pulling all rows and
processing in PHP slower than doing it in SQL?

For most applications — yes, SQL aggregation is faster.

But in practice for this use case:

The data is filtered by user and year first
The result set is small (max 365 rows per year per user)
The PHP processing is negligible

If you're dealing with millions of rows this approach
needs rethinking. But for typical business applications
it's fast enough and the portability is worth it.

Other Carbon Tricks I Use in VMMS

Comparing dates without time:

// Use DATE() in SQL to avoid time comparison issues
->whereRaw('DATE(deadline) >= ?', [$now->toDateString()])
->whereRaw('DATE(deadline) <= ?', [$now->copy()->addDays(7)->toDateString()])

Calculating days left:

$deadline = Carbon::parse($t->deadline)->startOfDay();
$daysLeft = (int) $now->copy()->startOfDay()->diffInDays($deadline, false);

The false parameter makes diffInDays return negative
numbers for past dates — useful for overdue detection.

Calculating processing time:

$minutes = Carbon::parse($row->process_initiate)
    ->diffInMinutes(Carbon::parse($row->process_accomplished));

The Lesson

Don't assume your local MySQL behavior will match
production. If your app might run on MariaDB, PostgreSQL,
or any other database — keep your date logic in PHP
with Carbon and use only standard SQL for filtering.

It's a small habit that saves a lot of debugging time.

About VMMS

This and many other lessons came from building VMMS —
a complete voucher management system for government
offices, companies, and educational institutions.

🔴 Live demo: https://vmms-app-production.up.railway.app/login

Available on Gumroad:
👉 https://getvmms.gumroad.com/l/zeroqz

Happy to answer any questions in the comments! 🚀

How I Built a Multi-Department Workflow Routing System in Laravel with Inertia.js

Chris Lloyd Fallaria — Sun, 03 May 2026 13:54:47 +0000

The Problem I Was Trying to Solve

When I started building VMMS — a voucher management system
for government offices — one of the hardest parts wasn't
the UI or the database schema.

It was the workflow routing.

A voucher request doesn't just go to one office. It goes
to multiple departments in a specific order — each one
needs to process it before passing it to the next.

And at any point, a department can:

Complete their step and pass it forward
Reject the entire request
Flag it for missing documents and pause processing

I needed a system that could handle all of that cleanly.

How I Modeled It

Every voucher type has a configurable processing flow
stored in a transaction_flows table:

Schema::create('transaction_flows', function (Blueprint $table) {
    $table->id();
    $table->foreignId('voucher_id')->constrained()->cascadeOnDelete();
    $table->string('department');
    $table->integer('order_number');
    $table->timestamps();
});

When a client submits a request, the system reads the
flow for that voucher type and routes it to the first
department automatically.

Tracking Progress with Audit Trails

Every time a department processes a request, an audit
trail record is created:

Schema::create('audit_trails', function (Blueprint $table) {
    $table->id();
    $table->foreignId('transaction_id')->constrained()->cascadeOnDelete();
    $table->string('processing_offices');
    $table->timestamp('process_initiate')->nullable();
    $table->timestamp('process_accomplished')->nullable();
    $table->date('deadline')->nullable();
    $table->timestamps();
});

process_initiate is set when a department starts
processing. process_accomplished is set when they
finish. If only process_initiate is set — that's
the currently active department.

Finding the Current Department

This was trickier than I expected. Here's the logic:

$activeAudit = $auditTrails->first(
    fn($a) => $a->process_initiate && !$a->process_accomplished
);

if ($activeAudit) {
    return $activeAudit->processing_offices;
}

// If no active audit, find the next step
$doneOrders = $auditTrails
    ->filter(fn($a) => $a->process_accomplished)
    ->map(fn($a) => $flow->firstWhere('department', $a->processing_offices)?->order_number ?? 0);

$lastDone = $doneOrders->max() ?? 0;
$nextStep = $flow->firstWhere('order_number', $lastDone + 1);

return $nextStep?->department ?? 'Completed';

The tricky part was handling edge cases:

What if a department is skipped?
What if a request is returned for missing documents?
What if the last department just finished?

Each of these needed its own handling.

The Pipeline Stepper on the Frontend

On the Vue 3 side, I built a stepper component that
shows each department as a step — green for done,
pulsing for active, grey for pending.

const stepStatus = (req, step, stepIndex) => {
    const audits = req.audit_trails ?? [];
    const accomplishedCount = audits.filter(
        a => a.process_accomplished !== null
    ).length;

    if (stepIndex < accomplishedCount)  return 'done';
    if (stepIndex === accomplishedCount) return 'active';
    return 'pending';
};

Simple logic but it took a few rewrites to handle
all the edge cases correctly.

Missing Documents — Pausing the Flow

When a staff member flags a request for missing
documents, the flow pauses. The client gets notified
and needs to upload the corrected files before
processing can continue.

I handled this through a separate requirement_validations
table that tracks which transactions are waiting for
documents from which user.

$missingDocIds = RequirementValidation::where('receiver_id', $userId)
    ->whereNull('resume_transaction')
    ->pluck('transaction_id')
    ->toArray();

When resume_transaction is filled — the flow resumes
and the request goes back to the staff for continued
processing.

What I Learned

1. Model the flow separately from the transaction
Keeping transaction_flows separate from
voucher_transactions made it easy to change
the processing order for a voucher type without
affecting existing requests.

2. Audit trails are your best friend
Storing every processing event with timestamps
made it easy to calculate processing times,
track performance, and build analytics on top.

3. Edge cases will get you
The "happy path" was easy. Missing documents,
skipped departments, and rejected requests
all needed separate handling. Budget time for this.

The Result

The pipeline tracker is now one of the most useful
features in VMMS. Clients can see exactly where
their request is in real time — no more following
up in person.

If you want to see it in action:

🔴 Live demo: https://vmms-app-production.up.railway.app/login

VMMS is available on Gumroad if you want to use it
or build on top of it:
👉 https://getvmms.gumroad.com/l/zeroqz

Happy to answer any questions in the comments!

How I Built a Production-Ready Voucher Management System with Laravel 12 and Vue 3

Chris Lloyd Fallaria — Sat, 02 May 2026 06:14:06 +0000

The Problem

I'm a CS student in the Philippines. During my time observing how
offices work, I noticed something that kept bothering me.

Voucher processing is still done manually almost everywhere.
Someone fills out a paper form, hands it to the next office,
that office stamps it and passes it again, and so on — until
it either gets done or gets lost on someone's desk.

No tracking. No visibility. The client has no idea where their
request is. The admin has no data. Staff have no accountability.

I kept thinking — this is exactly the kind of problem software
should solve. So I spent the last few months building it.

What I Built

VMMS stands for Voucher Management & Monitoring System.

It handles the entire voucher request process — from the moment
a client submits a request, all the way through each department
that needs to process it, until it's finally released.

No paper. No manual routing. No "what's the status of my request?"

Tech Stack

I went with what I know best:

Laravel 12 for the backend — routing, queues, and email jobs
Vue 3 (Composition API) for the frontend — reactive and clean
Inertia.js v2 — so I don't have to build a separate API
Tailwind CSS + Vuetify 3 — for UI components and styling
MySQL 8.0 — for the relational data and complex queries
Resend — for transactional emails

What It Can Do

There are three separate panels depending on your role:

Admin
You get full control — manage users, voucher types, and see
everything through an analytics dashboard. There's a staff
performance leaderboard, rejection rate charts, audit logs,
and even custom branding options.

Staff
Each department gets their own queue of requests to process.
You can complete, reject, or flag a request for missing documents.
There's also a personal performance dashboard showing your stats
and ratings from clients.

Client
You submit requests online, track which department is currently
processing your voucher in real time, upload corrected documents
if something gets flagged, and rate the staff after it's done.

Some other things worth mentioning:

Deadlines are holiday-aware — staff don't get penalized for non-working days
Every action is logged in an audit trail
PDF and Excel exports on all reports
Automated email notifications at every stage

The Parts That Were Actually Hard

Date queries across MySQL and MariaDB
I originally wrote date queries using MySQL-specific functions
like MONTH() and MONTHNAME(). Broke on MariaDB. Had to rewrite
everything to use Carbon instead — which honestly ended up
being cleaner anyway.

The pipeline tracker
Getting the pipeline stepper to accurately show which department
is currently processing a request was trickier than I expected.
Especially edge cases — what if a department is skipped? What if
a request gets returned for missing documents mid-flow? Took a
few rewrites to get right.

Holiday-aware deadlines
I built a DateHelpers service that calculates business days
while excluding weekends and holidays. Sounds simple, took way
longer than expected. Really happy with how it turned out though.

Try It Yourself

🔴 Live demo: https://vmms-app-production.up.railway.app/login

Credentials if you want to explore:

It's Available on Gumroad

I'm selling VMMS in two versions:

🔒 Obfuscated / Protected — $99
Deploy it as-is. PHP backend is protected, Vue frontend and
config files are fully readable. Good for offices and
institutions that just want to run it.

👨‍💻 Full Source Code — $300
Every file is readable and editable. Good for developers who
want to customize it for a specific client or use case.

Both versions come with documentation, a quick start guide,
and deployment configs for Nginx, Apache, and Railway.

👉 https://getvmms.gumroad.com/l/zeroqz

Happy to answer questions in the comments — whether about
the build, the tech stack, or anything else. 🚀