A stop-ship arrives as a range. One item code, a lot or date-code window, a reason, occasionally a second code for a component that sits inside four products. The two implementations that get tried first are almost always the same pair: set the quantity to zero, or add a status to the product row. Both pass a demo. Both fail on the third day of a real event, and one of them fails silently.
The position we take at FulfillNexa by SBT is that a freeze has to be a status the counting system cannot sell against, not a quantity somebody edited. The difference shows up the first time replenishment arrives during a hold.
Zero is a quantity and frozen is a status
Setting quantity to zero conflates three states that behave differently downstream. Out of stock, deliberately withheld, and not yet received all read as zero to a marketplace feed, to your margin report, and to whatever reordering logic you have. When a pallet of the same code lands, the quantity becomes correct again and the goods are instantly sellable, freeze forgotten.
A separate table with rows is the minimum shape that survives contact with a reshipment.
create table ship_block (
id bigint primary key,
scope_kind text not null check (scope_kind in ('item','component_lot','supplier_batch')),
scope_key text not null,
lot_from text,
lot_to text,
site text references site(code),
reason_code text not null,
state text not null check (state in ('active','lifted','expired')),
effective_at timestamptz not null,
lifted_at timestamptz,
decided_by text not null,
lifted_by text
);
create index on ship_block (scope_kind, scope_key, state);
The lot range is text compared as a code, not an integer. Suppliers issue date codes, revision letters and mixed formats, and a numeric cast loses the ones that matter. Store the range as stated, plus the interpretation used, because the interpretation is what you will need to defend later.
The lot has to be captured at receipt
Nothing downstream can recover a code that was never recorded. So the receiving line, not the shipment line, carries it.
create table receipt_lot (
receipt_id bigint not null,
item_code text not null,
lot_code text not null,
qty int not null check (qty > 0),
carton_ref text,
received_at timestamptz not null,
primary key (receipt_id, item_code, lot_code)
);
A system that first sees a lot during picking can only answer the question the freeze asks about units still inside a building, which is the smaller part of the population by the time anyone cares.
The check belongs in the pick path
Two moments look like the right place to test a freeze, and neither is sufficient alone.
-- True when the unit about to be picked is blocked right now.
create function lot_is_blocked(p_item text, p_lot text, p_site text)
returns boolean
language sql stable as $$
select exists (
select 1 from ship_block b
where b.state = 'active'
and b.effective_at <= now()
and (b.site is null or b.site = p_site)
and (
(b.scope_kind = 'item' and b.scope_key = p_item)
or (b.scope_kind = 'component_lot' and b.scope_key = p_item
and p_lot between b.lot_from and b.lot_to)
)
);
$$;
Order acceptance only stops new demand. Allocation only stops units that have not been claimed yet. A cart created before the freeze and worked after it slips past both, so the check has to fire again at the moment a person confirms the pick, with a message that says which block row stopped it. A soft warning at order intake that has expired by the time the picker arrives stops nothing.
The reverse query is the one that costs money
The forward query gives you a held quantity. The reverse one gives you a list of orders and customers, which is what an event actually needs.
select o.order_id, o.placed_at, s.shipped_at, rl.lot_code
from shipment_line sl
join order_line ol on ol.id = sl.order_line_id
join orders o on o.id = ol.order_id
join shipment s on s.id = sl.shipment_id
join receipt_lot rl on rl.item_code = ol.item_code
where ol.item_code = any (array['SKU-220','SKU-224'])
and rl.lot_code between '2603' and '2607'
and s.shipped_at >= '2026-04-01';
That join only exists if the shipment line can still reach the lot its unit came from. Carton-level records that get discarded after putaway break it, which is why the lot belongs on the line and not on a note.
Log the lift too
A freeze without a recorded release becomes two things: an argument about when stock was allowed to sell again, and a story the floor retells without any dates attached. lifted_at and lifted_by are not bookkeeping, they are the reason a replay is possible, and a replay is the only answer to a customer question that arrives three weeks late.
Run one test before you need it. Take a block row, a receipt with two lots either side of the boundary, and an order for each. Assert exactly one is blocked, and that the blocked one stays blocked after a replenishment lands. If that last assertion fails in your stack, you have the quantity-zero implementation, and you will find out during an event rather than during a test.
Three sites in China, 3,000 square meters in Shenzhen, 13,000 in Suzhou and 8,000 in Dongguan, holding seller stock under the name FulfillNexa by SBT (fulfillnexa.com). We do not publish rates or transit commitments here, and the schema above is a design position rather than a service claim. Its point is narrow and it holds whatever building you run it in: a range you cannot store is a range you cannot stop, and a range you cannot stop is a range you will eventually have to explain.
Top comments (1)
The pick-time check and replenishment fixture are useful. In the reverse-query example, though,
receipt_lotjoins only onitem_code, so an order shipped from an unaffected lot can match an affected receipt of the same SKU. I'd make the shipment-to-receipt-lot link explicit, including quantities if one shipment spans lots. A minimal fixture is two receipts of one SKU, one inside the freeze range and one outside, with one order shipped from each. The reverse query should return only the affected order, regardless of when the other receipt arrived.