field-service
Work Orders
Several query dimensions packed into one sort key, with no index.
Field-service orders that need filtering by status and by date. Rather than adding an index for the combination, both dimensions go into the sort key in the order they are narrowed - status first, then date - so one Query answers what a secondary index would otherwise be built for.
The model
WorkOrder
One job, keyed by the dimensions it is searched on.
- pk
- REGION#<regionId>
- sk
- STATUS#<status>#<scheduledOn>#<orderId>
Attributes: customer (S), engineer (S), scheduledAt (N)
Access patterns
- QueryOrders in a region
A region's jobs, grouped by status and ordered by date within each.
- DeleteItemClose an order
Move a job to DONE, which means writing it at a new key and removing the old one.
- GetItemFetch one order
A single work order by its full key.
Design notes
The order of the dimensions is the designwhy
Status comes before date because that is the order the reads narrow: always one status, sometimes a date range within it. Reversed, a date-first key could not answer "open jobs" without reading every status on every day. A composite sort key only serves reads that narrow left to right, and choosing that order is the whole decision.
A date range needs a bound above every idtrade-off
The key ends in an order id, so STATUS#OPEN#<date>#w-2314 sorts after the bare STATUS#OPEN#<date>. A range closing on that date therefore has to reach past it - BETWEEN "STATUS#OPEN#<from>" AND "STATUS#OPEN#<to>#\uFFFF" - or the last day silently returns nothing. Appending a discriminator to a sort key is what makes rows unique; it is also what makes the upper bound of every range awkward.
This is the index you did not buildtrade-off
A secondary index on status plus date would answer the same question and cost a second copy of every item, written on every update. The composite key gets there with one copy and no index, and the price is that the key can only be read in that one order.
What it costs when a job closestrade-off
Status is in the key, so closing a job is not an update - it is a delete and a put at a new key, because a primary key cannot be changed in place. Two writes, and between them the job sits under neither status prefix: a dispatch board refreshing at that moment sees it in no list at all, unless the pair is bound in one transaction. That is the bill for this design, and it is why a status that changes constantly belongs in a sparse index instead, where only the index entry moves. The console cannot run either write, so this page prices the design rather than demonstrating it.
Read the sort-key columntrade-off
The console reads the whole region, since the resolver handles only the partition half of a key condition. Even so the grouping is visible: DONE sorts before OPEN, and within each the dates run in order. A begins_with on STATUS#OPEN# would return exactly the second block, and nothing before it would be read.
Taught in the course
- One query, a whole collection - Items that share a partition key are read together in one Query.
These rows are real and already here. Running an operation against them downloads a DynamoDB-compatible engine, about 850 KB, and executes it in this tab.
A region's jobs, grouped by status and ordered by date within each.
Run an operation to see the raw engine response.
Transactions, streams, tags and TTL are among the operations the in-browser engine doesn't implement yet.