allanninal.dev/build

Part 2 of 7 · Delivery route planner series ~7 min read

How the day’s stops get gathered

Before anything can be put in order, the system has to know exactly where each stop is and what it demands. This post is about that first step alone: how the morning build gathers the day’s stops from the place you already keep them, turns each address into a real coordinate without paying to geocode the same street twice, and throws out the bad rows before they can poison the route.

Key takeaways

  • A scheduled morning job reads the day’s stops from the orders mirror — no one has to press a button.
  • Each address is turned into a coordinate; a geocode cache means repeat streets are looked up once, not daily.
  • Every row is validated — address, time window, and size — and bad ones are pulled out and flagged, not routed.
  • The output is a clean, geocoded stop list the optimiser can trust; nothing downstream has to guess.
  • The cut-off time, the cache, and the validation rules are settings — changing them never needs a deploy.

The morning build

Everything starts with a clock. At a time you set — say 06:00, well before the vans load — EventBridge Scheduler fires the gather function. It doesn’t wait for anyone to open a laptop; by the time the first driver arrives, the day’s round is already planned. The job’s only purpose is to produce one thing the rest of the system can rely on: a clean list of stops, each with a real coordinate, each known to fit a van and a window. Garbage here becomes a broken route three steps later, so the gather is deliberately fussy.

It reads the day’s stops from the mirror — the orders sheet, copied into AWS every few minutes by a small sync function so the planner never depends on a live Google call at 6am. From the settings doc it picks up the depot address, the working hours, and the average time spent at a stop, because the optimiser will need all three. Then it works through the rows one at a time.

The morning gather: schedule, read the stops, geocode against a cache, validate, and emit a clean list Across the top, two external boxes. Left, the stops source — the orders sheet mirrored into AWS. Right, the geocoding provider, an external service. Inside a dotted AWS account container sit a row of four pieces. First, on the far left and outside the row, EventBridge Scheduler fires the gather at six in the morning. It triggers the first box, drp-gather, which reads the day’s rows from the stops mirror above it. An arrow leads right to the second box, drp-geocode, which turns each address into a latitude and longitude; below it sits a geocode cache in S3 that it reads first, and only on a cache miss does it call the external geocoding provider shown top right. An arrow leads right to the third box, validate, which checks each stop’s address, time window, and size against the van capacities, and drops a bad row down to a flagged-stops list rather than passing it on. An arrow leads right to the fourth box, the clean stop list, which is written to DynamoDB and handed to the optimiser in Part 3. A note reads: a row with no usable address, an impossible window, or a load too big for any van is flagged, never silently routed. Stops source orders sheet, mirrored to AWS Geocoding provider external — called on a miss AWS account Scheduler 06:00 daily drp-gather read the day’s rows rows drp-geocode address → lat/long Geocode cache (S3) hit miss Validate window, size, address Flagged stops bad row Clean stop list to the optimiser A row with no usable address, an impossible window, or a load too big for any van is flagged — never silently routed.
Fig 2. The morning gather. A schedule wakes the job; it reads the rows, geocodes each address against a cache (calling the provider only on a miss), validates window and size, and hands a clean, coordinate-tagged list to the optimiser. Bad rows drop out to a flagged list.

Turning addresses into coordinates

The optimiser thinks in coordinates, not in “14 Mill Lane.” So every stop’s address has to become a latitude and longitude, and that’s what geocoding does. The naive version calls a geocoding API for every stop, every morning — which means paying to look up the same regular customer’s address five times a week. The planner doesn’t do that.

Instead, geocoding goes through a cache. The address is normalised (trimmed, upper-cased, punctuation stripped) into a stable key, and that key is looked up first in an S3-backed cache. A hit returns the stored coordinate for nothing; only a miss — a genuinely new address — calls the external provider, using an API key kept in Secrets Manager, and the result is written back to the cache for next time. On a typical round most stops are regulars or repeat streets, so the cache hit rate sits high and the provider is called only a handful of times a day. That single decision is what keeps the system’s biggest variable cost small, and Part 6 returns to it with numbers.

A coordinate that comes back as low-confidence — the provider matched the town but not the street — isn’t silently trusted. It’s treated like any other bad row: pulled out and flagged, so a person can fix the address rather than have the van sent to the middle of a postcode district.

Throwing out the bad rows

Validation is the unglamorous half of this step and the half that earns its keep. Each row is checked against three things before it’s allowed into the route:

  • A usable address. Blank, obviously incomplete, or geocoded to low confidence — flagged. There’s no point routing a van to a place the system can’t locate.
  • A sane time window. A window that has already passed, that’s narrower than the time it takes to do the stop, or that falls outside the working day — flagged. A “deliver 06:00–06:30” on a round that starts at 08:00 is a mistake, not a constraint.
  • A load that fits. A stop whose size or weight is larger than any single van can carry — flagged. The optimiser can split a day across vans, but it can’t split one stop that no vehicle can hold.

Flagged rows don’t vanish. They’re written to a flagged-stops list with the exact reason — “address not found,” “window already past,” “exceeds van capacity” — and surfaced to whoever runs the round, so the fix happens before the vans leave rather than as a confused driver phoning in from a lay-by. Everything that passes all three checks becomes a clean stop record — reference, coordinate, window, size, access note — written to DynamoDB and handed to the optimiser. From here on, nothing downstream has to wonder whether a stop is real.

Why this shape

  • Plan before anyone arrives. A morning schedule means the round is ready when the first driver walks in.
  • Geocode once, not daily. The cache turns repeat streets into free lookups and keeps the biggest variable cost down.
  • Validate hard, fail loud. Bad rows are flagged with a reason, never routed into a van’s afternoon.
  • Read from a mirror, not a live call. The orders sheet is copied into AWS so 6am never depends on a third-party API being up.
  • Hand on a clean list. Everything downstream trusts that a stop has a real coordinate and fits a window and a van.
All posts