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…
In ecommerce backends, shipments are often modeled with a status column following a happy path: label_created → in_transit → delivered. However, when issues arise, the row remains stuck at the last status, and the problem goes unnoticed until a customer raises a query. This failure lies in the quiet stall of parcels sitting in destination depots for days, waiting for a response. To tackle this issue, a separate object called an exception is introduced, which has its own lifecycle, deduplication key, and clock.
This model, which has proven effective in a real small-parcel operation, includes a table called shipment_exception. It contains columns such as id, shipment_id, kind, dedupe_key, severity, state, opened_at, due_at, claimed_by, claimed_at, resolved_at, resolution_code, and evidence. The unique constraint on shipment_id and dedupe_key ensures idempotency without a separate deduplication service.
The dedupe_key is crucial in preventing a queue from growing new rows for repeated webhooks with different payloads. By choosing the key per exception kind, the system can effectively manage and prioritize exceptions. For instance, for a stalled_scan kind, the dedupe_key is generated using the laneCode and scanDay from the shipment.
Similarly, address_invalid kind uses the destinationCountry and a hash-normalized address, while customs_hold uses the referenceNumber and HS code root. The carrier_reject kind utilizes the statusCode, and short_pick kind uses the orderId and SKU.
To detect the absence of any meaningful scan, a scheduled sweep is necessary. If a parcel has not been scanned in a lane for longer than the lane's silence budget, a new exception is created with the kind 'stalled_scan'. The due_at column determines when the exception should be addressed, and it defaults to the current time if not explicitly set.
In case of repeated carrier webhooks, the upsert operation with on conflict do nothing ensures that only one work item is created, avoiding the creation of multiple work items. The evidence column is a jsonb field that stores any relevant information related to the exception, providing a comprehensive record for future reference.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
