Custom Enterprise Platform Build

Technical & Business Pain Points (CMS Body Copy):

The honest picture of NexaFreight's infrastructure before KawachTech stepped in was equal parts frustrating and fascinating from an engineering standpoint. Their "system" wasn't one system — it was about seven overlapping ones that nobody had the full map to.

The core operational panel was a PHP 5.6 application running on a shared hosting plan with 2GB RAM. It had no queue system, no background job processing, and no caching layer. Every time a dispatcher updated a consignment status, it triggered synchronous writes to three separate tables — one for the live tracking log, one for the billing ledger, and one for an audit trail — all in a single blocking HTTP request. Under normal load (200–300 concurrent users during morning dispatch windows), this caused page timeouts regularly exceeding 45 seconds.

The MySQL database had grown organically over 9 years with zero schema governance. Critical tables like consignment_master had 140+ columns, no proper indexing on foreign keys, and several VARCHAR(255) fields storing what were clearly enum-type values — things like "pending", "in_transit", "delivered" — queried with LIKE operators. One particularly painful query that ran on the dispatch dashboard was doing a full table scan across 2.3 million rows every 90 seconds to refresh status cards. It had no index. It had never had an index.

On the business side, invoice generation was a manual export-to-Excel process performed by the accounts team every Friday. Any disputes — which were frequent — required cross-referencing four different spreadsheets. The reconciliation lag was running at 12–17 business days on average, which was damaging their cash flow and their relationships with enterprise clients who expected GST-compliant e-invoices within 48 hours of consignment delivery.

There was also a security exposure that genuinely concerned us. User authentication was session-based with no token expiry, no role-based access control, and several admin-level routes accessible without middleware. Driver GPS location data was being stored in plaintext in a public-accessible directory on their shared server.

Why the previous architecture failed:

It didn't fail because the original developers were incompetent — they built for the scale NexaFreight was at in 2015. It failed because NexaFreight scaled 4x in 3 years and nobody paused to re-architect. The technical debt compounded silently until it didn't.

XAMPP → AWS Architecture Transition:

We kept the XAMPP local environment for developer workstations during the build phase — it's fast to spin up, reproducible, and the team was comfortable with it. But we drew a hard line: XAMPP is for development only, never for staging or production validation.

Our staging environment was an EC2 t3.medium instance (Ubuntu 22.04 LTS) behind an Application Load Balancer, closely mirroring the production setup. This meant environment parity from week one — no "works on my machine" surprises during UAT. The local .env and the staging .env were structurally identical except for credentials, which eliminated an entire category of deployment bugs we've been burned by on past projects.

For production, we moved to a three-tier architecture:

• Web/App Tier: EC2 c5.xlarge (4 vCPU, 8GB RAM) running Laravel via Nginx + PHP-FPM with OPcache enabled, behind an ALB.
• Database Tier: Amazon RDS for MySQL 8.0 (db.r5.large), Multi-AZ deployment, automated daily snapshots with 7-day retention. Separated completely from the app server — this alone resolved the shared resource contention that was throttling their old setup.
• File Storage: Amazon S3 for all POD (Proof of Delivery) document uploads, consignment invoices, and driver KYC scans. Pre-signed URLs with 15-minute expiry replaced the old public-directory exposure.
• Queue Processing: Laravel Horizon on the same EC2 instance managing background jobs via Redis (ElastiCache t3.micro). Invoice generation, SMS/email notifications, and GPS log writes all moved off the HTTP request cycle entirely.
• SSL & DNS: ACM certificates via CloudFront for the client-facing tracking portal. Route 53 for DNS management.

Why Laravel for this specific problem:

A few deliberate reasons, not just familiarity:

Laravel's Eloquent ORM with eager loading was critical for the consignment detail pages, which were pulling related data from 6–8 tables. We could control N+1 query problems explicitly with with() chains and optimize at the relationship level rather than writing raw JOIN spaghetti. The old system had raw SQL everywhere, and half the queries had no prepared statement protection.

Laravel Horizon + Queue Workers solved the core performance problem architecturally. Invoice generation, which previously blocked the HTTP response for up to 22 seconds, became a background job dispatched in under 80ms. Dispatchers got their confirmation screen instantly; the PDF was ready in their dashboard within 4–6 seconds processed asynchronously.

Laravel's Policy and Gate system gave us clean, testable RBAC without bolting on a third-party library. NexaFreight has 6 distinct user roles (Super Admin, Operations Manager, Dispatcher, Driver App, Client Portal, Accounts). Defining and enforcing these at the policy layer kept the controllers clean and auditable.

Sanctum for API token authentication on the driver mobile app integration — stateless, token-scoped, with explicit expiry — replaced the session-based free-for-all completely.

MySQL Schema Optimization:

This was honestly some of the most satisfying work on the project.

We didn't attempt a direct migration of the old schema — we redesigned it using the existing data as a reference, then wrote migration scripts. Key decisions:

• Split the 140-column consignment_master table into a normalized set: consignments, consignment_status_logs, consignment_billing, and consignment_documents. The status log table now has a composite index on (consignment_id, created_at DESC) — the most common query pattern — bringing that 45-second dashboard refresh down to under 300ms.
• Replaced all VARCHAR enum-pattern columns with proper MySQL ENUM types (or lookup tables for anything requiring admin configurability), reducing storage footprint and enabling index cardinality to actually work.
• Added covering indexes on the three highest-traffic report queries identified during profiling with EXPLAIN ANALYZE. One of those queries went from a 2.3M row full scan to a 4-row index lookup.
• Implemented soft deletes across all transactional tables using Laravel's SoftDeletes trait — NexaFreight's auditors needed full record history, and hard deletes were a compliance risk.
• Set innodb_buffer_pool_size to 6GB on the RDS instance (75% of available RAM on db.r5.large), dramatically reducing disk I/O on repeated report queries.

Technical Hurdle — The GPS Log Volume Problem:

About 6 weeks into development, we hit something we hadn't fully anticipated in scope. NexaFreight's driver app (a third-party Android app they were already using) was sending GPS pings every 30 seconds per active vehicle. With 340 active vehicles during peak hours, that's roughly 680 write operations per minute hitting a single vehicle_tracking_logs table. We'd initially designed this as a standard Eloquent model write — which immediately showed as a bottleneck in load testing.

The solution was a two-layer approach: GPS writes went to a Redis sorted set first (vehicle ID as key, coordinates + timestamp as value), persisted to MySQL in batched bulk inserts every 60 seconds via a scheduled Laravel command using DB::table()->insert() with chunked arrays — bypassing Eloquent's per-row overhead entirely. The live tracking map on the client portal reads from Redis (sub-5ms), not MySQL. MySQL is the source of truth for reporting and compliance only.

This reduced database write load from ~680/min to ~12 batch operations/min while keeping the live map experience genuinely real-time. We're particularly proud of that one.