One pair, three jobs

An order in the POS holds the pairs a customer drops off, and each pair holds the treatments booked for it. The most common drop-off at our counter goes like this. Someone brings in a pair of sneakers for a deep clean, the cashier offers a repaint while they're at it, and some leather care too, and the pair leaves the till booked for all three.

That's one pair of shoes, three jobs, and maybe three different workers. Each treatment has its own status, worker and price, and workers pick them up from a queue on their phones. The customer wants to know when the pair will be ready, and each worker needs to know which of the three jobs is theirs.

A pair in the middle

The pair wasn't in my first model at all. It stored each treatment as its own row, and each row carried the shoe's brand, colour, size and tag. That's fine for one treatment. With three, you get three rows describing the same shoe, three tag codes, and nothing tying them together.

When I wrote down what should replace it, I opened by quoting my old definition, "one pair of footwear receiving one Service", and then wrote "Reality disagrees."

The fix was an Item in the middle. An order holds Items, one per physical object, and each Item holds its treatments. The Item knows what the shoe is and carries its one tag. Each treatment knows its own status, its worker and its price. The drop-off above ends up shaped like this:

Order
└─ Item: Air Jordan 1, white, size 42, one tag
   ├─ Deep clean      ready for pickup
   ├─ Repaint         in progress
   └─ Leather care    queued

I looked at two other shapes too. One was a bundle, a single "full restoration" product that splits into steps behind the scenes. But that's not how the counter sells. The cashier upsells one treatment at a time, and the customer sees three prices, which a bundle would hide. The other was leaving the data alone and just grouping rows on screen. That would have made the queue easier to read, but the three copies of the shoe could still drift apart, and the pickup rule, that a pair can only be collected once every treatment on it is ready, couldn't be written at all.

I also thought about sequencing, since the work does have an order: repairs before cleaning, cleaning before paint. I put it off. Staff already sequence the work by looking at the shelf, and as I wrote at the time, "physics prevents two workers holding one object."

Who sets the status?

That raises an obvious question. If a pair has three treatments in three different states, what's the status of the pair? And what's the status of the order?

My answer is that nobody sets those. Only treatments have a status you can change. A pair is ready when every live treatment on it is ready, because you can't hand back half an object. A pair's status isn't stored anywhere. It's worked out from its treatments every time the pair is read. The order's status is a rollup A summary worked out from the lines below it, like a total at the bottom of a column. too, recalculated and saved every time one of its treatments moves.

The order still reads its status straight from the treatments, without going through the Items. The result is the same either way, and leaving it alone meant I didn't have to rewrite the code that works out an order's status.

Moving every tag in the shop

The Item was the largest migration the codebase had seen. Every query that joined orders to treatments gained a level, and every existing treatment had to be given an Item.

It was also a change drizzle-kit push would have got badly wrong. Push is Drizzle's shortcut that compares the schema with the live database and applies the difference on the spot. Pushed straight, the change would have added the new, required Item column to rows that already existed, and dropped the brand, colour, model, size and tag columns before anything had copied them out. That would have lost every tag code in the shop. So I wrote that migration by hand, as one transaction A group of database changes that either all happen or none do, so a failure halfway leaves nothing half-written. that checks its assumptions before it deletes anything, and refuses to finish if any treatment is left without an Item.

It backfills Fills a new column or table for the rows that existed before it did. one Item per existing treatment. It would have been nice to merge a pair's old rows into one Item, but one order can hold two identical pairs of white Air Force 1s, and those are two Items. There's no safe way to tell those apart after the fact, so old orders keep one Item per treatment. Old tags end in -S001 where new ones end in -I001, and both keep working, because nothing ever reads the suffix. A lookup compares the whole stored string.

The refund on the rack

There was an edge case I nearly shipped, and only found while reviewing the branch. A customer drops off a pair and gets a refund at the counter while the shoes are still on the rack. Now there's no live treatment left, so the rollup treated the pair like a finished order and called it picked up. The tracking page would have cheerfully told the customer they'd collected shoes that were sitting in our shop. And the pickup desk wouldn't have handed them over, because nothing on that pair could ever become ready.

The mistake was treating a pair's status as the order's status with the endings renamed. For an order, "completed" is right: the money is settled and nothing was called off. For a pair, a status is a claim about where the shoes physically are, and these hadn't moved.

So pairs got their own refunded ending, and a refunded pair can still be collected, since the refund settled the money and the shoes are still the customer's. Only a pickup event can say a pair actually left the shop. Trimmed a little, the rule reads like this:

export function deriveItemStatus(services: ItemStatusLine[]): DerivedItemStatus {
  const phase = classifyTreatmentWork(services);

  if (phase === "all_ready") return "ready_for_pickup";
  if (phase === "in_progress") return "processing";
  if (phase === "nothing_started") return "queued";

  // Nothing live is left, so the question is where the shoes are.
  if (services.every((service) => service.status === "cancelled")) return "cancelled";
  if (services.some((service) => service.pickup_event_id != null)) return "picked_up";
  return "refunded";
}

The last cookie

Workers tap through the queue on their phones, and sometimes two of them tap the same job at the same moment. It's the last-cookie problem: two hands reach for the plate, and one of them has to come back empty. In database terms it's a race condition Two requests changing the same row at nearly the same time, where the result depends on which one lands first..

So every status change is a conditional update. It only goes through if the row is still in the status the worker saw:

const updated = await executor
  .update(ordersServicesTable)
  .set({ status: to })
  .where(
    and(
      eq(ordersServicesTable.id, serviceId),
      eq(ordersServicesTable.status, from)
    )
  )
  .returning({ id: ordersServicesTable.id });

if (updated.length === 0) {
  throw new BadRequestException(
    "Service changed state before transition could apply. Refresh and try again."
  );
}

Say both workers saw the job as queued. The first tap turns it into processing. The second tap asks for the same change on a row that's no longer queued, matches zero rows, and that worker is asked to refresh instead of quietly overwriting the first.

Two statuses aren't in the status menu at all. picked_up only comes from the pickup desk, which needs a paid order, the customer's six-digit pickup code and a photo of the handover, and saves the handover in one transaction. refunded only comes from the refund flow. And in case I ever forget all of that, a CHECK constraint in Postgres refuses any picked_up row that has no pickup event behind it:

check(
  "order_services_picked_up_requires_event_check",
  sql`${table.status} != 'picked_up' OR ${table.pickup_event_id} IS NOT NULL`
),

Two racks

The queue is also split by kind of work. Repairs have their own specialist, and the deep cleaners handle everything else, at very different speeds. With one shared queue, each of them had to dig through the other's work to find their own.

So the queue can be filtered by category, as "only Repair" or "everything except Repair", and each worker's choice sticks. A pair in for both a repair and a clean shows up in both queues, with all its treatments still listed on its card, so nobody forgets the shoe has another job waiting.