Caching lane mileage for freight loads with a 180-day city-pair cache

Carriers decide on a load by rate per mile, so a load without miles is a load nobody can price. SmartLoads fills mileage for every load on the board. The naive way is one routing API call per load. That gets expensive fast, because bulk imports re-read the same lanes all day: Google Sheets refresh every 5 to 30 minutes, and most shippers send the same lanes every week.

This paper covers how SmartLoads keeps paid lookups rare, and the bug that taught us what to measure.

Three tiers, cheapest first

Mileage for each load row goes through three tiers:

1. Use the source's own miles. If the shipper's sheet or email includes a mileage figure, that number wins. There's no lookup, and it's not cached either, because a shipper's number for their own lane is authoritative for their loads but shouldn't be reused for anyone else's.
2. Use the cache. Otherwise, look up the city pair in mileage_cache.
3. Call the provider. Only on a miss (or an expired entry), call the routing provider and write the result to the cache.

On top of that, each import batch is deduplicated by lane before any lookup. A sheet with 40 loads from Jewett, TX to Weimar, TX needs one answer, not 40.

The cache table

public.mileage_cache (
  origin_city, origin_state, destination_city, destination_state,
  normalized_origin, normalized_destination,   -- the lookup key
  miles, duration_minutes,
  source,                                      -- which provider produced it
  last_fetched_at, hit_count
)
UNIQUE (normalized_origin, normalized_destination)

Three design decisions matter most.

Normalize the key the same way everywhere. Cities are trimmed and lowercased. States are trimmed, uppercased and cut to two characters. Reads and writes share one function for this. A cache whose writer says Jewett|TX and whose reader says jewett |tx has a hit rate of zero.

Make it undirected. Driving distance from A to B is close enough to B to A for rate-per-mile, so the key sorts the two endpoints:

export function undirectedMileagePairKeys(oCity, oState, dCity, dState) {
  const a = `${normalizeCity(oCity)}|${normalizeState(oState)}`;
  const b = `${normalizeCity(dCity)}|${normalizeState(dState)}`;
  return a <= b ? { normalizedOrigin: a, normalizedDestination: b }
                : { normalizedOrigin: b, normalizedDestination: a };
}

A backhaul lane reuses the headhaul's entry.

Share it across the platform. Nearly everything in SmartLoads is scoped to an agency. Mileage deliberately isn't: the distance from Jewett to Weimar isn't anyone's private data, and a shared cache gets warmer faster as agencies are added.

Entries are valid for 180 days from last_fetched_at. Roads change slowly, and a stale entry is simply refreshed on the next lookup. Each hit bumps hit_count in a fire-and-forget call, which gives a "top lanes" report for free and never slows the import.

The numbers

From the batch log, May 1 to September 26, 2026, across 13,175 import batches:

Of the lookups that needed a distance at all, about 77% were served from cache. In the last 30 days it was 3,693 cache hits against 1,158 provider calls. The cache now holds 2,235 city pairs.

The log also estimates cost at $0.005 per lookup, the per-element price it was built around: about $123 saved against $37.50 spent. That's a small number, and that's the point. Without the tiers, every one of those 514,729 rows would have been a paid lookup, roughly 100,000 a month, as sheets re-import all day.

Providers change; the cache shouldn't care

SmartLoads started with Google's Distance Matrix API and moved to Mapbox (geocode each end, then Mapbox's driving Directions Matrix) in June 2026. The cache records which provider produced each row but serves them the same way, so the switch didn't cost a cold cache. Every Google-era entry kept serving hits.

The silent failure

The switch did cause one real bug, and it's the most useful part of this paper.

The mileage_cache table had a check constraint listing the allowed source values: Google, OSRM and manual. The new code wrote source = 'mapbox'. Every cache write failed the constraint. Lookups still returned miles, so loads still got mileage, and nothing looked broken. The batch summary said errors=0, because it counted lookup errors, not cache-write errors.

For about three months, every lane was looked up fresh on every miss, and the cache stayed frozen at the rows written before the switch. We found it on September 3, 2026, while chasing an unrelated "no miles" report, and fixed it with a one-line constraint change.

What changed afterwards:

• Cache writes report their own failures. Each lookup that fails to write marks it, and the batch summary line now includes cache_write_errors=.
• We check that the newest cache row is recent. If max(last_fetched_at) stops moving while imports are running, the cache isn't being written.
• Every source that imports loads runs the mileage step. Two sheet syncs turned out to skip it entirely: one behind an opt-in flag nobody had set, one with no mileage step at all.

The general lesson: a cache that fails open looks exactly like a cache that works, except on the bill. Measure writes as well as reads.

An admin view

Agency admins have a mileage stats page. It shows totals, a chart over time, recent batches, the top lanes by hit count, and the size of the cache. It reads the same mileage_lookup_log table the numbers above came from: one row per import batch, with hits, source imports, fresh lookups, stale refreshes, errors and the estimated cost saved and spent.

Frequently asked questions

Why not cache the shipper's own mileage too?

Because it's the shipper's number for their own freight. It might use a specific route or a company rule. It's the right number for that shipper's loads, but reusing it for other loads on the same lane would spread one shipper's convention across everyone.

Is 180 days too long?

For city-to-city driving distance used in rate-per-mile, no. The distances rarely move by more than a mile or two in that time, and an expired entry refreshes on its next use.

Why is the cache shared across agencies?

Distance between two cities isn't customer data. Sharing it means a new agency's first imports benefit from lanes other agencies have already looked up.

What happens when the lookup fails?

The load stays on the board without miles and the failure is counted in the batch log. The usual cause is a missing or unrecognized city, which is also flagged for location review.