How an order gets watched
The notifier only watches what’s in the order list. So the first job is making sure the list actually reflects what your business has in flight, and that each order’s status stays current as the parcel moves. There are three ways an order gets in and stays up to date: somebody types or pastes a row in the Drive sheet, the carrier posts each tracking change to a small endpoint, or somebody forwards a carrier email and the notifier reads it. The first one is obvious. The other two exist because in real life nobody types a status update for the parcel that just changed depots.
Key takeaways
- Three intake lanes feed one order list: the Drive sheet, a carrier webhook, and an inbox-forwarding lane.
- The carrier webhook posts each tracking change to a Lambda Function URL the moment it happens.
- Forwarded carrier emails are read by Bedrock Haiku 4.5, which proposes the order and the new status.
- Every parsed status change goes to a team Slack for one-tap approval before it lands in the list.
- The Drive sheet stays the canonical store. The other lanes are conveniences that write into it.
Three lanes into one order list
Lane 1: the Drive sheet itself
The simplest lane. Open the order list in Drive, paste in the day’s new orders (most stores can export a CSV), and save. The columns are short: order number, customer name, customer email, carrier, tracking number, current status, expected delivery date, and a link to the order. A small Lambda — drive-sync — runs every few minutes, exports the sheet as plain CSV via the Drive API, and writes it to s3://sn-orders-source/orders.csv if the sheet has changed since the last sync. The notifier reads from S3, not Drive directly. That keeps Drive API calls predictable and gives you S3 versioning for free, so a bad bulk-edit can be rolled back in one click.
This lane covers the cases where you already have an order, you know its tracking number, and you can spend a few seconds pasting it in. Most orders go in this way at the start of their journey.
Lane 2: the carrier webhook (the lane that does the heavy lifting)
Most carriers can post a tracking update to a web address you give them every time a parcel’s status changes — picked up, in transit, out for delivery, delivered. Set up a small Lambda Function URL (a plain web address that runs a tiny piece of code, no API Gateway needed) and hand it to the carrier as the place to post updates. When a parcel changes status, the carrier posts the tracking number and the new status to that address. The Lambda matches the tracking number to an order in the list and updates the status field directly.
This lane has no human in the loop, on purpose. The carrier’s own status is the authoritative truth about where a parcel is — there’s nothing for a person to approve or correct. The Lambda verifies the request really came from the carrier (using a shared secret stored in Secrets Manager), matches the tracking number, and writes the new status. If a tracking number doesn’t match any order, the update is parked in a small “unmatched” list and surfaced in the weekly digest so somebody can fix the row. The webhook is what keeps the order list current minute to minute without anyone touching it.
Lane 3: inbox forwarding (the catch-all)
Not every carrier offers a clean webhook, and some send updates only by email. For those, set up a dedicated inbound address — something like tracking@your-company.com — via Amazon SES. Anyone on the team forwards a carrier email to that address and the notifier takes it from there. SES writes the raw email to s3://sn-raw-mime/. The S3 write triggers a parser Lambda.
The Lambda calls Bedrock Haiku 4.5 to read the email and emit a structured change: which order it’s about (matched by tracking number or order number in the text) and the new status. The model prompt is short: “Read this carrier email. Return JSON only: the tracking number and the new shipping status. Do not invent a status that isn’t stated.” The output goes to a small Slack interactive message that pings the team: the proposed change and three buttons — approve, edit, discard. On approve, a Lambda updates the status in the Drive sheet via the Sheets API. On edit, the team member gets a fillable modal pre-populated with the proposal. On discard, the message is logged and the email moved to a discarded prefix in S3 for audit.
The reason this lane keeps a human in the loop — while the webhook lane doesn’t — is that reading a free-text email is fuzzier than reading a structured webhook. A status the model misread is worse than one that never made it in: it would send the customer a “delivered” email for a parcel still in transit. So a person glances at the proposal before it goes anywhere.
Why the list stays the source of truth
Three lanes in, but only one place where the notifier actually looks. That’s a deliberate constraint. If two lanes both wrote directly to the notifier’s state, every “why did this email go out?” question would mean checking three places. Funneling everything through the Drive sheet means there is exactly one row per order, and any team member can read or edit any of it without learning a new tool. The convenience lanes are first-class for keeping status current, but they always pass through the sheet on the way.
Next post: how the notifier actually reads the order list, compares each status to what was last sent, and picks one of five moves.
All posts