A shipping exception queue that ages, deduplicates and can be claimed

Most ecommerce backends model shipments as a happy path with a status column. label_created → in_transit → delivered. When something goes wrong, the row gets stuck at whatever it was last stuck at, and nobody finds out until a customer asks.

That is the actual operational failure of cross-border parcels. Not the rare disaster, the quiet stall. A parcel sitting in a destination depot for nine days waiting for a phone call nobody made looks identical to a parcel in transit, as long as your only signal is a status field.

What you need is a separate object: an exception, with its own lifecycle, deduplication key and clock. Here is the model that survived contact with a real small-parcel operation.

Exceptions are rows, not flags

create table shipment_exception (
  id              bigint generated always as identity primary key,
  shipment_id     bigint not null references shipment(id),
  kind            text   not null,   -- stalled_scan | address_invalid | customs_hold | carrier_reject | short_pick
  dedupe_key      text   not null,
  severity        smallint not null default 2,
  state           text   not null default 'open',   -- open | claimed | resolved | dismissed
  opened_at       timestamptz not null default now(),
  due_at          timestamptz not null,
  claimed_by      text,
  claimed_at      timestamptz,
  resolved_at     timestamptz,
  resolution_code text,
  evidence        jsonb  not null default '{}'::jsonb,
  unique (shipment_id, dedupe_key)
);
create index on shipment_exception (state, due_at) where state in ('open','claimed');

The unique (shipment_id, dedupe_key) constraint is the whole trick. Carrier webhooks fire the same error repeatedly, sometimes with different payload shapes, and a queue that grows a new row per webhook is a queue nobody will open on Monday morning. The dedupe key is what makes ingestion idempotent without a separate dedup service.

Choose the key per kind, deliberately:

function dedupeKey(kind: Kind, ev: CarrierEvent, shipment: Shipment): string {
  switch (kind) {
    // same carrier status code on the same shipment, regardless of how many times it re-fires
    case 'stalled_scan':    return `stall:${shipment.laneCode}:${ev.scanDay}`;
    case 'address_invalid': return `addr:${shipment.destinationCountry}:${hashNormalizedAddress(shipment.address)}`;
    case 'customs_hold':    return `hold:${ev.referenceNumber ?? 'noref'}:${shipment.hsCodeRoot}`;
    case 'carrier_reject':  return `rej:${ev.statusCode}`;
    case 'short_pick':      return `short:${shipment.orderId}:${ev.sku}`;
  }
}

Upsert with on conflict do nothing and you get a stable queue. If you need the repeat count for prioritisation, add a counter column and increment it on conflict, but never let the count create a second work item.

The stall detector is a query, not an event

There is no webhook for "nothing happened". You have to detect absence, which means a scheduled sweep.

-- no meaningful scan, and the lane's own silence budget is exhausted
insert into shipment_exception (shipment_id, kind, dedupe_key, due_at, evidence)
select s.id,
       'stalled_scan',
       'stall:' || s.lane_code || ':' || to_char(now(), 'YYYY-MM-DD'),
       now() + interval '1 day',
       jsonb_build_object('lastScanAt', s.last_scan_at,
                          'silenceHours', extract(epoch from (now() - s.last_scan_at)) / 3600)
  from shipment s
 where s.state = 'in_transit'
   and s.last_scan_at < now() - (
         select lane.silence_budget
           from shipping_lane lane
          where lane.code = s.lane_code
       )
on conflict (shipment_id, dedupe_key) do nothing;

The silence_budget per lane is the part people get wrong. A premium air lane with daily scans going quiet for 48 hours is a real signal. An economy sea-air lane where the next scheduled scan is genuinely four days away is not, and alerting on it buries the queue in noise until people stop reading it. Store the budget as lane data, not as a constant in the sweep. A hardcoded threshold is a business rule that escaped review.

Same principle applies to the timezone trap. Compute silence in UTC, store the carrier's local timestamp separately for display, and never let a comparison cross the two.

Due dates, and why a queue without them rots

Every exception gets a due_at when it opens, set from a per-kind policy. The point is not blame. It is that an unbounded queue silently favours whatever is newest, so the seven-day-old customs hold loses every argument to the two-minute-old address error.

The work view is then trivial and honest:

select e.id, e.kind, e.shipment_id, e.due_at < now() as overdue,
       extract(epoch from (now() - e.opened_at)) / 3600 as age_hours,
       s.order_ref, s.destination_country
  from shipment_exception e
  join shipment s on s.id = e.shipment_id
 where e.state in ('open','claimed')
 order by severity desc, overdue desc, due_at asc;

Claiming, because two operators fixing one parcel is worse than none

The state machine is small on purpose: open → claimed → resolved, with dismissed for false positives. Claiming is a conditional update, not a read-then-write.

update shipment_exception
   set state = 'claimed', claimed_by = $2, claimed_at = now()
 where id = $1 and state = 'open'
returning id;

Zero rows returned means somebody else got it. That is the entire concurrency control, and it is enough.

Resolution codes matter more than they sound. Free-text notes cannot be aggregated, and the thing you want out of six months of exceptions is the pattern: which lanes stall, which destination countries reject which address formats, which suppliers cause short picks. Constrain the codes and you get a report for free.

const RESOLUTIONS = [
  'readdressed_and_relabelled',
  'buyer_contact_provided',
  'returned_to_local_address',
  'abandoned_below_threshold',
  'customs_docs_supplied',
  'reshipped_from_forward_stock',
  'carrier_claim_filed',
  'false_positive',
] as const;

abandoned_below_threshold deserves its own code, because the decision to stop chasing a parcel is a written policy, not an operator's judgment call at 5pm. Encode the value threshold in config and let the code prove the policy was followed.

Two things that will bite you

Event ordering. Carrier feeds arrive out of order, and a stale in_transit scan landing after a delivered event will resurrect a closed shipment if you apply events blindly. Guard transitions with a monotonic sequence or an event timestamp comparison, and reject anything older than what you already stored.

Code drift. Carriers add status codes. A code your mapping table does not know currently falls into the default branch, which in most implementations means "ignore". Invert that default. Unknown codes should open a low-severity exception, because an unmapped status is exactly the kind of thing that hides a failure.

Run this way, the queue is short, every row has an owner or a due date, and the report at the end of the quarter is a count instead of an archaeology project.

The lanes this was built around are the small-parcel and one-piece fulfillment ones FulfillNexa by SBT (fulfillnexa.com) runs out of three sites in China, 3,000 m² in Shenzhen, 13,000 m² in Suzhou and 8,000 m² in Dongguan, where the exception is usually a missing scan rather than a lost parcel, and where silence budgets differ by handoff rather than by wishful thinking.