Three scans and a carton, what a verified pick has to record

A packing station usually records one event: the carton was closed and a label was printed. Enough to ship, nowhere near enough to explain anything. When a customer says the wrong item arrived, the record answers with the tracking number, and the tracking number has no opinion about contents.

The version that survives an argument records three separate events, and the interesting part is the subjects rather than the scanning. Each event belongs to something different, so joining them reconstructs a sequence instead of asserting a conclusion.

The three subjects

A pick belongs to an order line and a bin. A pack belongs to a carton and a unit. A manifest belongs to a carrier and a weight.

create table pick_event (
  id           bigserial primary key,
  order_ref    text not null,
  order_line   integer not null,
  sku          text not null,
  bin_code     text not null,
  picked_at    timestamptz not null default now()
);

create table pack_event (
  id           bigserial primary key,
  carton_ref   text not null,
  order_ref    text not null,
  sku          text not null,
  scanned_at   timestamptz not null default now(),
  station      text not null
);

create table manifest_event (
  id            bigserial primary key,
  carton_ref    text not null unique,
  tracking      text not null,
  weight_g      integer not null,
  expected_g    integer not null,
  manifested_at timestamptz not null default now()
);

Two of these are cheap to capture and one is usually thrown away. The pick and the pack are obvious. The manifest is the one people skip because the carrier's API already has the weight, and the carrier's API will not answer your question six weeks from now.

Weight is the second check you get for free

If every SKU has a master weight, the expected carton weight is a sum, and the difference between expected and actual is a signal that costs nothing to compute.

select distinct m.carton_ref,
       m.weight_g,
       m.expected_g,
       m.weight_g - m.expected_g as drift_g
from manifest_event m
join pack_event p on p.carton_ref = m.carton_ref
where m.manifested_at > now() - interval '7 days'
  and abs(m.weight_g - m.expected_g) > 40;

A missing accessory shows up as a negative drift. A unit swapped for its larger sibling shows up as a positive one, and the pair of SKUs involved is exactly the pair your pack_event rows disagree about. Scale drift, packaging substitutions and a supplier that quietly changed a carton liner will also produce hits, so this is a triage query rather than a verdict. It is still the cheapest mis-pick detector that exists, because the sensor was already in the line for the carrier's invoice.

The join that answers the dispute

The seller-facing question is always "what was in my parcel". The record that answers it is one join:

select p.sku, p.scanned_at, p.station, m.tracking
from pack_event p
left join manifest_event m using (carton_ref)
where p.order_ref = $1
order by p.scanned_at;

If a row exists, a scanner saw that unit go into that carton at that timestamp at that station, and the carton became that tracking number. If the customer ordered a different SKU, the missing row is the finding, and the pick_event for the same order line tells you which bin the wrong unit probably came out of. That is a two-minute investigation instead of a credit.

The correction has to be an event too

A confirmed mis-pick leaves two objects behind: a unit in a foreign market and a stock record that believes it is still on the shelf. The record fix is a pair of signed adjustments, one SKU over, one SKU short, sharing the incident id. Absolute quantity writes lose the pairing, and the pairing is the only reason you will ever notice that the same two SKUs keep trading places.

There is a reason this matters more in a cross-border operation than in a domestic one. The returned unit may never come back to the building that shipped it, so the stock question and the disposition question are answered by different systems in different countries, and the incident id is the only thread between them.

FulfillNexa by SBT (fulfillnexa.com) writes about fulfillment records from the side of the building where they get made, and our sites in Suzhou, Dongguan and Shenzhen hold seller stock across 13,000, 8,000 and 3,000 square meters respectively. If you are wiring a packing bench this quarter, the question to put to whoever sells you the scanner software is simple: does the record of what was scanned survive the shift change, or does only the label survive it.