allanninal.dev/build

Part 2 of 7 · Order status responder series ~6 min read

How a “where’s my order?” message gets matched

Before anyone can be told where their order is, the system has to know which order they mean. This post is about that step alone: how a message that arrived by email, SMS, or chat is turned into one confident order — and what happens, deliberately, when it can’t be.

Key takeaways

  • Three channels arrive in different shapes; the first job is to flatten them into one normalised message record.
  • Matching tries the order number in the text first — the cleanest signal — then falls back to the sender’s email or phone.
  • The orders are read from a DynamoDB mirror of your Drive sheet, refreshed every 15 minutes, so matching never waits on Drive.
  • A single confident hit moves on; several open orders for one sender, or none, is escalated rather than guessed.
  • Matching is plain Python. No model decides which order a customer means.

Three shapes, one record

A message can arrive three ways, and they look nothing alike on the wire. An email comes through SES as a raw MIME blob dropped in S3 — headers, subject, a body that might be HTML, maybe a signature and three quoted replies underneath. An SMS arrives as a small JSON payload posted by your SMS provider to a Lambda Function URL — a from-number, a to-number, and a short body. A web-chat message is another small JSON post from the widget on your site, carrying whatever identifier the visitor gave (often an email, sometimes nothing).

The intake Lambda’s first job is to make these the same. Each becomes one normalised record: the channel it came in on, a sender identity (email address or phone number), the plain-text body with quoted history and signatures stripped, and a timestamp. From here on, nothing downstream cares whether the question came by text or email — it’s just a message with a sender and some words.

Number first, then sender

With a clean record, matching runs in a fixed order, cheapest and most certain signal first.

  • Order number in the text. Most people quote it — “any update on 10482?”, “order #10482”, “ref 10482”. A small set of patterns pulls candidate numbers out of the body and subject, and each is looked up directly in the orders mirror. A hit on a live order is the strongest match there is, because the customer told you exactly what they meant.
  • Sender email or phone. When there’s no number — “hi, where’s my stuff?” — the matcher looks up the sender instead: the from email for email and chat, the phone number for SMS, against the customer columns in the mirror. If that sender has exactly one open order, that’s the match.
  • Neither, or too many. If nothing matches, or the sender has several open orders and the text doesn’t say which, the matcher does not pick one. It escalates with everything it tried, so a person resolves it in one reply rather than the system guessing wrong.

The phone and email are normalised before they’re compared — numbers to E.164, emails lower-cased — so “+44 7700 900123” and “07700 900123” land on the same customer. The match is plain Python comparing strings; there is no model anywhere on this path, because “which order does this person mean” is a lookup, not a judgement call.

The matching flow: three channels normalise into one record, then a two-step match against the orders mirror On the left, three small boxes stacked vertically — Email via SES, SMS via webhook, and Web chat — each with an arrow into a single box labelled Normalise, which flattens them into one message record with sender, channel, and text. An arrow leads right into a decision labelled “Order number in text?” If yes, an arrow goes to “Look up number in orders mirror”. If no, an arrow goes down to “Look up sender email or phone”. Both lookups read from a cylinder on the right labelled “Orders mirror — DynamoDB, refreshed from the Drive sheet every 15 minutes”. Both lookups feed a decision labelled “Exactly one open order?” If yes, an arrow goes to “Matched — on to live tracking”. If no — none or several — an arrow goes to “Escalate with what was tried”. A dotted AWS account container surrounds the normalise, match, and mirror elements. A note reads: matching is plain Python; a confident single hit moves on, anything ambiguous goes to a person. AWS account Email via SES SMS via webhook Web chat Normalise sender, channel, plain-text body Order number in text? look up number in orders mirror yes look up sender email or phone no Orders mirror DynamoDB, synced 15-min Exactly one open order? Matched → tracking yes Escalate no Matching is plain Python — a confident single hit moves on; anything ambiguous goes to a person.
Fig 2. Three channels normalise into one record, then a two-step match — order number first, sender second — against a DynamoDB mirror of the Drive sheet. One open order moves on; none or several escalates.

Why a mirror, not the sheet

The orders live in your Drive sheet, but the matcher never reads Drive directly. A small osr-drive-sync Lambda, on an EventBridge schedule every 15 minutes, pulls the sheet and writes each order as a row in a DynamoDB table keyed by order number, with secondary lookups by email and phone. Matching then reads from DynamoDB, which is fast, predictable, and doesn’t burn through Drive API quota every time a customer texts. It also means a burst of fifty enquiries at once doesn’t hammer Google — they all read the same warm mirror.

Fifteen minutes of staleness is fine here, because the thing that actually moves — the parcel — isn’t in the sheet at all. The sheet says which carrier and tracking number an order has; whether it’s moved is the carrier’s job to answer, live, which is Part 3. The mirror only needs to be fresh enough to know an order exists and who it belongs to.

Design rules that shaped the matcher

  • Normalise first. Channel differences die at the door; everything downstream sees one message shape.
  • Strongest signal first. An order number the customer typed beats an inferred sender match every time.
  • Read a mirror, not the source. DynamoDB keeps matching fast and keeps Drive quota out of the hot path.
  • One hit or escalate. The matcher never breaks a tie between two orders; a person does.
  • No model on the match. Which order someone means is a lookup, and lookups should be deterministic.
All posts