Walk me through an order's journey from placement to delivery. How would you model its status?
6 min readUpdated 2026-08-15
0
Answer
Short answer
Model the journey as milestone events, each carrying both the time it happened and the time you recorded it, and derive current status by projecting that set in a fixed milestone order rather than storing a mutable column, because events arrive late, twice and in the wrong sequence.
Was this answer helpful?
Likely follow-ups
→A depot uploads yesterday's scans this morning. Which customers get notified, and of what?
→How would you model an order that is partly cancelled after one of three lines has already shipped?
→Two devices report contradictory delivery outcomes for the same parcel. Who wins, and who decides?
→Where would you put the boundary between the order management system and the warehouse system?
Milestones are commitments, not segments of a progress bar
An order's journey gets drawn as a progress bar, and that picture is where most of the bugs come from. The useful unit is not a stage the order sits in but a milestone: something that happened once, in the physical world or in a system of record, that somebody is prepared to be held to. Placed. Payment authorised. Allocated to a stock location. Picked. Packed into a carton with its own identifier. Manifested onto a load. Departed. Arrived at the delivery depot. Out for delivery. Delivered, or attempted and not delivered.
Each of those is evidence with an owner who can be asked to produce it, which is why the framing matters commercially as well as technically. When a customer disputes a delivery, or a carrier disputes a claim, you are not querying a status field, you are producing the scan that says a carton left your dock and the signature that says it arrived.
The second consequence is that milestones do not belong to the order. A three-line order splits: two lines ship from one warehouse, the third is on backorder, and the carrier splits the first parcel again across two vans. So the milestone attaches to a shipment or a carton, and the order-level view is an aggregation over children. Model status on the order header and you will ship the bug where a customer sees "delivered" while a line is still on a shelf.
Two clocks, and you need both
Every event carries the time it happened and the time you learned of it. These are not redundant, and collapsing them into one timestamp is the single change that causes the most downstream damage.
Record time is when the row landed in your store. It is the only clock you control, it is monotonic, and it is what you replay from and audit against. Event time is when the physical thing occurred, and it comes from somewhere you do not control: a handheld that was behind steel racking for twenty minutes, a van in an underground bay, a partner status file transferred on a schedule rather than an event. Event time can be hours behind record time, it can be wrong because a device clock drifted, and it is occasionally in the future for the same reason.
You need event time because it is what the customer experienced and what the service-level calculation must use. You need record time because it explains why your system said something different an hour ago, which is the question support asks. A report built on record time alone shows a quiet afternoon and a spike at six, and describes the upload schedule rather than the operation.
Status is a projection, not a column
Given the event set, current status is computed, not stored. The temptation to keep a status column and update it on each event is strong because it makes reads trivial, but it makes correctness a function of arrival order, and arrival order is exactly the thing you do not control.
-- Current milestone per shipment. Ordering by milestone_rank first makes the-- projection monotonic: a late in-transit scan cannot un-deliver a parcel.SELECTDISTINCTON (shipment_id)
shipment_id, milestone, event_time, recorded_at
FROM shipment_event
WHERE superseded_by ISNULLORDERBY shipment_id, milestone_rank DESC, event_time DESC;
The choice encoded in that ORDER BY is the interesting part. Sorting by event time alone is the obvious reading of "latest wins" and it is wrong, because a genuine departure scan captured offline at 11:20 and uploaded at 14:55 will arrive after the 11:41 delivery and, on an event-time sort, still lose. Sorting by a fixed milestone rank makes progress one-directional, so the projection is stable under any arrival order at the cost of never being able to move an order backwards. That cost is real and you should say so out loud, because there are cases that need reversal.
If deriving on every read is too expensive at your volumes, you materialise the projection into a table and rebuild it from the events. That is a cache with a defined rebuild path, which is a different thing from a mutable column with no way back.
Duplicates, late arrivals and outright contradictions
Three cases, and a good answer has one rule for each rather than a new invention per incident.
Duplicates are certain, not likely, because every mobile client retries and no network gives you exactly-once. So each event carries a key derived from the source device, the object and the business step, and inserting the same key twice is a no-op. Nothing else in the design should have to care.
Late arrivals are handled by the projection, which is the whole reason it is ordered by milestone rank. The one thing you must handle outside the projection is notification: if you have already told the customer their parcel was delivered, an event that arrives afterwards describing an earlier attempt must not trigger a message, because the message is not idempotent even though the write is.
Contradictions are the case that cannot be solved by ordering. Two devices report that the same parcel was delivered and returned to the depot. There is no timestamp comparison that makes this a data problem: one of the two is factually wrong, and deciding which is a business rule about which source is more trustworthy for which milestone, with an exception queue for the residue. Writing an event that supersedes another, and keeping both, is how you keep that decision auditable.
Cancellation races the goods
Cancellation and amendment look like order-management concerns and are really a race against physical progress. Between a customer pressing cancel and a picker reaching the shelf there is a window, and its length is an operational fact about your warehouse rather than something you choose. Before allocation, cancellation is a write. After the carton is on a load, it is a request to intercept that will often fail, and the compensating flow is a return rather than a cancellation.
So the model needs a cancellation-requested milestone distinct from cancelled, because the request is a fact you have and the outcome is not yet known. Systems that treat cancel as a terminal state discover the truck already left, and then have an order in a state the software says is final while a parcel is in the network with nobody expecting it.
Two edges carry the interesting cases: the loop back out of not-delivered, and the one where a cancellation request loses the race and the goods are manifested anyway.
Loading diagram…
The part that separates a strong answer
Adequate answers describe the happy-path state machine well and then treat late, duplicated and contradictory events as an upstream data-quality problem that someone should fix. That is the tell. Those events are not defects in the feed, they are what a network of handhelds, vans and partner files emits by construction, and no amount of cleaning upstream removes them.
The strong version inverts it: design the projection so that arrival order cannot change the answer, then ask what the customer was told and when, because that is the only part of the system where being temporarily wrong is expensive and irreversible. The recorded journey is always a lagging, lossy account of a physical one, so the engineering question is how quickly a divergence becomes visible and how defensibly it gets corrected.
Two timestamps and a derived status are not stylistic preferences, they are what makes the answer to "where is my order" independent of the order in which you happened to hear things.