OptiRoute.

Logistics is famously difficult. The core of it, the Travelling Salesman Problem, is a mathematical puzzle that has haunted computer scientists for decades. But standard A-to-B courier routing is child's play compared to bulk liquid haulage. Think of standard routing like a courier delivering parcels: any parcel can go in the back of any empty van.

Now imagine those parcels are liquids—specifically, fuel oil haulage. This is the industry responsible for delivering heating oil to rural homes with tanks in their gardens, as well as supplying fuel to commercial properties, farms, and busy building sites. Your truck isn't a single empty box, but a chassis containing distinct compartments (pots) of varying sizes. You can't mix standard diesel with premium unleaded. You have strict driver shift limits, minimum fill thresholds, and complex product prohibition matrices to navigate. Suddenly, you aren't just solving for distance; you are solving a massive 3D Tetris puzzle inside a moving vehicle.

Normally, this requires a monolithic enterprise software suite, heavy server-side processing, and a massive database. In the spirit of All Just Data Really, the goal was to strip this back to the atoms. Could an enterprise-grade logistics engine run entirely within the free tiers of the modern web stack?

The Problem

Bulk liquid haulage is a thin-margin industry, highly liable to profit erosion through poor or inefficient decision making. Transport managers face several core challenges daily:

• 01Single Point of Failure: Operations are often entirely reliant on one individual to manually sequence the day's deliveries.
• 02Time Intensive & Reactive: Dispatchers often have no time for delay. When under pressure, they make reactive, suboptimal choices just to "get the trucks out of the yard."
• 03Subjective Routing: Decisions can be based on gut feeling, and don't always prioritise profit (or the dispatcher lacks the tools to calculate this accurately on the fly).

Requirements

To tackle these issues, the exact requirements for the solution were mapped out. It had to be fit for purpose, highly visual, and accessible to non-tech-savvy users.

• •Adjustable Fleet & Depots: Dynamic depot locations, toggleable active vehicle flags, and fully customisable vehicle compartments (pots).
• •Amendable Product List: A known, dynamic product database with configurable mixing prohibitions.
• •Simple Input Process: A drag-and-drop Excel upload requiring only a few columns, acknowledging the industry's reliance on spreadsheets.
• •Customisable Parameters: The engine must adapt to specific company metrics (running costs, driver hours, shift limits).
• •Explainable Output: No black boxes or hallucinations. The logic must be strictly rule-based and deterministic.
• •Visual Trust: An Executive Summary for fast KPI checks and a Virtual Chassis to visualize compartment loads.
• •Excel Export: Ability to output the drop list to Excel with one truck per tab for easy driver distribution.
• •Fast, Secure, Low Cost: Data must be processed securely without storing customer PII, on a target infrastructure budget of £0.

Options & Architecture

1. Data Storage: The Hybrid Approach

Initially, storing everything, from configuration to daily drops, in a single Excel workbook was considered. While familiar, this made maintaining persistent state across a distributed team impossible. Full cloud storage (like AWS) was also evaluated, but storing transactional delivery addresses creates potential GDPR liabilities because it would mean holding sensitive, identifiable customer location data on external servers.

2. Hosting & Frontend

A frontend that was fast and could handle complex client-side state without a heavy backend was required. Processing data "client-side" means all the heavy mathematical lifting happens directly on your own computer, rather than sending it back and forth to a distant server. This keeps the tool incredibly responsive and ensures your sensitive delivery data never leaves your device. React (a tool for building interactive interfaces) was chosen because it excels at managing highly visual, instantly updating features like the 'Virtual Chassis'. It was paired with Next.js (a framework that packages those interfaces together) because it automatically optimises the code to load quickly and makes it incredibly easy to deploy. But where to host it?

3. The Engine: Heuristic vs AI

It's tempting to use modern Artificial Intelligence (like Large Language Models) for everything today. However, AI is fundamentally a "black box" that makes highly educated guesses based on probabilities. While a 99% accurate email draft is fantastic, in bulk liquid haulage, a 99% confident guess that it's safe to mix Aviation Fuel and Diesel could cause a catastrophic explosion. Furthermore, transport managers need to understand exactly why a route was chosen. If a driver questions a delivery sequence, "the computer guessed it was best" isn't an acceptable operational answer. Absolute mathematical certainty and total transparency are required.

The Development Process

Building the engine was highly iterative. As development progressed, the approach was continually refined to match real-world constraints.

• 1. Refining Input Data

  Initially, all data came from different Excel tabs. But managing a fleet via Excel is messy. The fleet, products, and depots were moved to a cloud database (Supabase) to provide a "single source of truth," leaving Excel purely for the daily, disposable drop list. To ensure a smooth user experience, we restricted raw file uploads to Excel formats specifically for the Deliveries data. We provided a downloadable template pre-configured with required column names (like Delivery ID, Postcode, Volume, etc.) to eliminate data misalignment edge cases.

  

• 2. Usability & Authentication

  Users needed to save their fleet setups securely. We built the platform with usability and security at its core. Authentication is handled via Supabase, featuring passwordless magic links to dramatically reduce friction during login. The system fully supports multiple users, and behind the scenes, Row Level Security (RLS) handles tenant isolation invisibly, ensuring each planner's workspace and fleet data remain completely isolated and secure within their own sessions.

  

• 3. Designing the Workspace

  Before any routes are processed, the user interacts with the Workspace. We designed this area to give planners complete control over the operational constraints that drive the routing engine. As development progressed, we evolved several key parameters to match real-world constraints without requiring expensive, continuous API calls to map services. Below is a high-level look at the parameters integrated into the workspace:

  Parameter	Purpose
  Circuity Factor & Speed	Adjusts straight-line coordinates into realistic road distances and calculates travel time based on Average Speed.
  Costs & Rates	Running cost per mile, driver hourly rate, and overtime rate. Crucial for the engine to financially evaluate and score routes.
  Max Driver Hours	Standard and total shift limits prevent the engine from generating illegal or physically impossible runs.
  Fill Levels	Minimum and maximum pot fill limits prevent dispatching highly inefficient, near-empty vehicles, whilst enforcing safe ullage capacity.
  Drop Time	Accounts for the physical time spent unloading volume at a customer's location.

• 4. Processing

  During the processing phase, the engine takes the workspace parameters and delivery data to calculate optimal routes. We evaluated several options—ranging from simple distance-based clustering to integrating heavy cloud-based solver APIs.

  We ultimately chose to build a custom greedy heuristic algorithm. This approach allowed us to strictly enforce complex, industry-specific rules that generic APIs couldn't handle, such as strict multi-compartment capacity logic and our chemical Prohibition Matrix (preventing incompatible products from being loaded together).

  To make this easy for the user, all processing is done seamlessly in the browser. Instead of a black box that just fails, our engine guarantees transparent feedback, generating a categorized "Unassigned Deliveries" report that tells the user exactly why an order wasn't routed (e.g., "Exceeds Max Shift Time" or "Incompatible Product").

• 5. Developing the Exec Summary

  A fast algorithm is useless if users can't easily interpret the output. The raw JSON output of the route was unreadable. An Executive Summary dashboard was built to immediately present the crucial KPIs: Total Outlay, Cost-per-Litre, and Fleet Utilisation, answering the dispatcher's main question: "Is this a good day?"

  

  Executive Summary Dashboard

  

  Geographic HGV Routing Map

• 6. Virtual Fleet

  Seeing is believing. Transport managers are visual thinkers. A spreadsheet saying "Pot 1: Diesel" doesn't inspire trust. A dynamic SVG visualization of the tanker chassis was built so dispatchers could instantly "see" the safe fill levels and ensure no prohibited products were mixed.

  

  Virtual Fleet Tanker Chassis Visualisation

The AI Co-Developer

Building a complex routing engine from scratch would traditionally require a team of software engineers. For this project, Google Gemini was utilised as an AI pair programmer directly within VS Code. By offloading boilerplate code generation, styling, and rapid refactoring to an AI assistant, human focus was kept entirely on the complex business logic and mathematical constraints of the engine.

Mission Accomplished.

The goal was to build a high-performance, complex logistics engine on a £0 infrastructure budget, and it was achieved. By breaking down the notoriously difficult Multi-Compartment Vehicle Routing Problem into its core components, an enterprise-grade routing tool was created that any small haulage business could use to dramatically cut running costs and streamline their daily operations.

• Radical Simplicity: By completely bypassing a monolithic backend and heavy enterprise software, the user gets a seamless drag-and-drop experience that just works.
• Raw Data Logic: The engine strips the notoriously difficult Multi-Compartment VRP down to its core constraints, relying on a deterministic heuristic to generate explainable, safe routes every time.
• Accessible: What is usually reserved for large corporations with massive budgets is made available to any small haulage business through simple web tools and everyday spreadsheets.
• Low/No Cost: The entire routing workspace, database, and hosting are deployed entirely on the free tiers of Vercel and Supabase, keeping infrastructure costs at exactly £0.
• Core Function: A focus on proving the math works and adhering strictly to safety regulations, before surfacing the results cleanly via an Executive Summary and Virtual Chassis.

Special Thanks to Tate Oil

A massive thank you to the team at Tate Oil for their invaluable industry insights. Uncovering a genuine business problem and ensuring this logistics engine was tested against real-world constraints would not have been possible without their expertise and support.