DEV Community: life

A Damage Claim Is a Table With Evidence Columns and a Clock

life — Sat, 10 Oct 2026 17:00:38 +0000

A claim does not fail because the product broke. It fails because the record of what was packed was never stored anywhere retrievable, and by the time anyone wants it, the only remaining source is the memory of whoever was on that bench eleven days ago.

So the feature is a table with the right columns, written at the moment the carton closes, plus a clock attached to it. Not a nicer support workflow.

What has to be captured at pack-out

The scan that seals a carton is already producing most of this. It is usually thrown away.

create table pack_record (
  carton_id        text primary key,
  order_ref        text not null,
  station_id       text not null,
  operator_id      text not null,
  packed_at        timestamptz not null,
  scale_weight_g   integer not null,
  label_id         text,
  label_voided_at  timestamptz,
  manifest_scan_at timestamptz,
  dunnage_profile  text not null,   -- resolved from the product record, not typed
  building         text not null
);

create table pack_item (
  carton_id   text not null references pack_record,
  sku         text not null,
  qty         integer not null,
  unit_scan   boolean not null default false,
  primary key (carton_id, sku)
);

The column people skip is dunnage_profile. Packaging choice is a property of the product, and if it is not recorded per carton then a claim cannot distinguish "this bottle was packed wrong" from "this bottle was packed to spec and still broke", which is exactly the distinction the carrier and the supplier both want.

unit_scan matters for a different reason. A carton packed by weight alone can contain the wrong unit and still be correct on every other field.

The claim row, and the clock that lives on it

create table damage_claim (
  id             bigserial primary key,
  carton_id      text references pack_record,
  opened_at      timestamptz not null default now(),
  filed_by       text not null,           -- shipper_of_record | seller | partner
  evidence       jsonb not null default '[]',
  disposition    text not null default 'pending',
  written_off_qty integer not null default 0,
  deadline_at    timestamptz,             -- carrier window, from the dated rule row
  closed_at      timestamptz
);

Two design notes that decide whether this works.

The first is filed_by. The party with standing is normally whoever is named on the label, and in a third-party setup that is a real question with a real answer that has to be written down before the parcel moves. Left blank, both sides wait for the other to file and the claim never exists. This column is the difference between a process and a rumor.

The second is deadline_at, and where its value comes from. Carrier claim windows and default liability ceilings are published terms that change, and declared value coverage is bought when the label is purchased rather than retrofitted afterwards. Store the deadline as data with a source date, in the same dated-rule-table shape you would use for any other external constraint, and never compute it from a constant in code. A hard-coded window is a claim that quietly expires six months before anybody notices the terms moved.

The bug that outlives the claim

Here is the part that keeps costing money after the refund is issued. A damaged unit that is refunded and never dispositioned is still sitting in your available quantity.

The write-off has to be a transaction, not a status on the claim row:

begin
  update damage_claim set disposition='written_off', closed_at=now() where id=$1
  insert into stock_adjustment (sku, building, qty, reason, claim_id)
       values ($2, $3, -$4, 'damage', $1)
  -- available recomputes from on_hand + committed + adjustment
commit

If the adjustment is a note instead of a row, the storefront will sell the phantom unit, the next order will fail at pick, and the exception will be filed under a completely different problem. Every damage claim needs a disposition with a quantity effect, and the quantity effect has to reach the number your channels are allowed to sell.

What to ask a partner, in order

Can you pull the pack record for this carton, by carton id, ten days later. Does it include the dunnage profile and the weight the scale read. Who is named as shipper of record on my labels. Who files, and what does the evidence pack contain when it leaves your building. And when a unit is written off, which system's available number changes.

Those five questions map one to one onto the columns above, which is the point. A partner who can answer them is describing a schema. A partner who answers with a promise is describing a habit, and habits do not survive a busy week or a staff change.

FulfillNexa by SBT (fulfillnexa.com) keeps the pack record with the carton and the write-off with the claim, on floors in Suzhou with 13,000 square meters, Shenzhen with 3,000 and Dongguan with 8,000. We do not publish rates or transit commitments, and we do not decide disposition policy on a seller's behalf. What we will commit to is that the row exists, that it names the building the carton left, and that a damaged unit does not stay sellable because nobody pressed a button.

A Cancel Is a State Transition, Not a Boolean

life — Sat, 10 Oct 2026 17:00:11 +0000

Almost every fulfillment integration I have seen handles a cancellation by flipping a flag on the order row and hoping the warehouse worker process polls often enough. It works in demos and in quiet months. It fails in the specific way that costs money. The flag says canceled, the parcel leaves anyway, and both sides of the integration are technically correct about what they did.

The fix is not a faster poll. It is admitting that a cancel is an intent competing with a state machine, and that the state machine has to be the thing that resolves the race.

The two tables

The order record already has a status, and Shopify's own vocabulary for it is open, closed, canceled and archived, with a separate fulfillment axis of unfulfilled, partially fulfilled and fulfilled. Copying those into your database is fine. Treating them as one field is the bug, because they change for different reasons and at different speeds.

What is missing is the intent record:

create table cancel_intent (
  id              bigserial primary key,
  order_ref       text not null,
  source          text not null,          -- storefront | support | fraud | customer
  reason_code     text not null,
  received_at     timestamptz not null default now(),
  observed_state  text,                   -- what the resolver found
  outcome         text not null default 'pending',
                                    -- stopped | too_late | unwind_required | failed
  resolved_at     timestamptz
);

The column that makes the whole design honest is observed_state. It forces the system to record what was true at the moment the cancel arrived, instead of pretending that a cancel is a command. A cancel is a request to move a parcel backwards through states that may already be behind you.

Resolving the race

The resolver runs once per intent, takes a lock on the fulfillment row, and branches on the current state. The important detail is that the pick confirmation and the cancel have to serialize on the same row, otherwise you get the classic interleaving where the picker reads "not canceled", the cancel writes, the picker writes "picked", and the parcel is on a truck with a canceled order attached to it.

lock fulfillment_row
switch row.state:
  case 'queued':        state = 'canceled'; outcome = 'stopped'
  case 'picked':        emit walk_back_task;  state = 'canceled';
                        outcome = 'unwind_required'
  case 'packed':        void_label; emit unpack_task;
                        state = 'canceled'; outcome = 'unwind_required'
  case 'manifested':    outcome = 'too_late';  -> return_path
  case 'shipped':       outcome = 'too_late';  -> return_path

Two of these branches are not cancellations at all. They are unwind operations that happen to have been triggered by a cancellation, and they need their own tasks, their own completion signal, and their own effect on the stock record. Modeling them as the same event is how you end up with a canceled order and an on-hand quantity that never came back.

The last two branches are where the honest system says no. Once a carton is on a manifest, the parcel belongs to a carrier schedule, and the remaining option is whatever intercept product that carrier sells, which is time-boxed and explicitly not guaranteed. Your state machine should not pretend otherwise, because the next person to read that record will be reconciling money.

The acknowledgment is the product

Support tickets die here. The storefront gets 200 OK from a cancel endpoint and reports "canceled" to the customer, while the warehouse got the message at 14:38 and the carton was sealed at 14:36. Both are true.

An ack that carries the observed state turns that argument into a log line. "Cancel received at pack-out. Label voided, unit walked back to the shelf, count reconciled." That is a sentence a support agent can send, and it cannot be written unless the resolver actually ran. The difference between those two replies is the entire service level.

The same event should carry a reason code. Cancels arrive from customers, from fraud review, from support goodwill and from marketplace rules, and they have different follow-up obligations. A cancel that came from a marketplace deadline has to be visible to whoever watches channel compliance, while a customer change of mind does not.

What this fixes that a flag does not

Three things show up in the data afterwards. Late notices stop being invisible, because too_late is a countable outcome with a stage attached. The unwind tasks stop being forgotten, because they have a row that is either complete or not. And the cost of a cancel becomes measurable per stage rather than per month, which is the only version of that number that changes anybody's behavior.

We run this shape across our Chinese sites, 13,000 square meters in Suzhou, 3,000 in Shenzhen and 8,000 in Dongguan, under the FulfillNexa by SBT (fulfillnexa.com) name, and the reason it is modeled as transitions rather than flags is not architectural taste. It is that a cancel can arrive while a human is holding the unit, and the record has to say what happened next in a language that survives the shift change.

The status names and the rule that a partially fulfilled order has to have its fulfillment canceled before the order itself becomes eligible are Shopify's published documentation. Check their current version the day you build, because the field that saves you is the one that still exists.

A Fulfillment Agreement Is Three Documents, Not a Dashboard

life — Wed, 07 Oct 2026 19:10:02 +0000

Most fulfillment conversations end with somebody sharing a dashboard. It shows orders, inventory, maybe a shipment status column, and it feels like the deliverable. It is not the deliverable. A dashboard is a view, and a view can be changed, re-pointed or switched off by whoever owns the underlying system.

What you actually signed up for is three documents. If they do not exist as documents, the service has no interface, and every disagreement later becomes a negotiation about memory.

Document one: a dated rule sheet for the channel you ship into

This is the one that sounds like paperwork and behaves like engineering. The rule sheet says: for channel X, item class Y, here is what must be true before the parcel leaves our building, and here is the date the rule was read from the source.

The reason the date is the load-bearing part is that these rules move. Amazon's bagging requirements, expiration-date presentation, carton envelope and case-pack caps are all published by Amazon and all subject to change without notice to you. A rule sheet without dates is a claim about the present that will silently become false.

A shape that works:

rule_code	channel	applies_to	expected	source_seen
suffocation_warning	amazon-fba	bag opening >= 5in	prescribed warning printed, film >= 1.5 mil	2026-10-01
expiration_format	amazon-fba	shelf-life items	MM-DD-YYYY or MM-YYYY, 36pt+, unit and box	2026-10-01
carton_envelope	amazon-fba	standard box	36.00 x 25.00 x 25.00 in, 50.00 lb	2026-10-01
preexisting_barcode	amazon-fba	inbound carton	prior carrier barcode removed or rendered unscannable	2026-10-01

Two properties make this a document rather than a wiki page. Closing a rule never changes what an old shipment was judged against, because the version is dated. And every row names a source, so when the platform changes the requirement, the fix is one row, not an archaeology project.

We keep our prep rules in exactly this shape, and the reason is not tidiness. It is that an operator answering "why was this treated differently from last month" needs a version, not a recollection.

Document two: an exception log with an owner and a disposition

Anything that did not go as planned should exist as a row with four fields filled in: what triggered it, what the decision was, who owns the next action, and what it cost.

The field people skip is the owner, and it is the only one that determines whether the exception closes. "Held pending documentation" with no name attached is a status, not a work item. Compare:

2026-10-07  hold        carton 88213  rule: expiration_format
            owner: <named person>     next: request re-code from supplier
            cost: null                close: null

2026-10-07  hold        carton 88213  rule: expiration_format
            owner: nobody             next: waiting
            cost: unknown             close: unknown

The second one is what most provider relationships actually look like, and it is why sellers discover a problem three weeks late rather than three days late.

A useful property to ask for: the log must be exportable and readable after you stop being a customer. If the only copy lives in a dashboard you lose access to on termination, the log is a feature of the vendor's product rather than a record of your operation.

Document three: named responsibilities

Three questions, answered in writing, with a party named for each:

Who is importer of record on a direct-to-consumer parcel? For a parcel leaving a Chinese warehouse addressed to a consumer in Germany, the party accountable at the border is often the recipient. Whoever arranged the shipment should be explicit about who is named and why, because that is the difference between a tax line and a customer service incident.

Who pays a preparation defect fee when the defect was created on the provider's side? A rule sheet with no cost owner is advice. The answer does not have to be generous, it has to exist.

Who holds the evidence, and for how long? If a carton is reworked, the only surviving proof of what arrived is what was captured before the rework. Ask where those files live and whether you can get them.

What deliberately is not in these documents

Rates and transit promises, for one. We do not publish either, and no rule above depends on them. A quoted per-parcel number and a delivery window are both forecasts, and forecasts do not belong in the same artifact as a dated rule that determines whether a shipment is compliant.

The other thing that does not belong is a service-level number nobody can recompute. If a provider states a percentage, ask which denominator and which window, and ask to see the same query run on last month's data. A metric you cannot reproduce is decoration.

How to read all three in ten minutes

Ask for one closed exception from last quarter and follow it. The rule sheet should tell you which version applied on that date. The log should tell you who owned it and how long it stayed open. Named responsibilities should tell you who paid and who still has the photographs.

If those three line up, the dashboard is a convenience. If they do not, the dashboard is the whole product, and you are buying someone else's memory of your business.

FulfillNexa by SBT (fulfillnexa.com) runs inbound, storage and outbound across three warehouses in China, with 3,000 square meters in Shenzhen, 13,000 in Suzhou and 8,000 in Dongguan, and the three documents above are the shape we think a fulfillment relationship should be auditable through. We do not publish rates or transit commitments, and none of the above depends on either.

A Prep Defect Should Come Back as a Record, Not a Story

life — Wed, 07 Oct 2026 19:07:24 +0000

A carton gets pulled aside at a receiving center. Two days later someone asks what happened. The answer arrives as a sentence: "the bags were wrong." Nobody can say which bag, against which rule, or what it cost, and the conversation quietly becomes about who to believe.

That is the failure mode I want to write down, because it is not a packing failure. The packing failure already happened and was fixable in ninety seconds. The failure that costs money is that the evidence was destroyed by the fix.

What a defect actually is

Almost every prep defect on goods coming from China is a measurement, not a judgment. A bag opening of five inches or more needs a suffocation warning printed on it, and the film has to be at least 1.5 mil. Expiration dates have to read MM-DD-YYYY or MM-YYYY, at 36 point or larger, on the unit and on the box. A standard carton stops at 36.00 by 25.00 by 25.00 inches and 50.00 pounds. Case packs cap at 150 units. A supplier carton whose previous carrier barcode is still scannable is a defect even when everything inside it is perfect.

Each of those is a number, an observed value, and a source. That is a record. A record can be replayed, audited and argued with. "The bags were wrong" cannot.

The record has to be captured before the rework

This is the part that is easy to get wrong for an understandable reason: fixing the item feels like the job, so the operator fixes it and then reports that it was fixed.

Rework is irreversible. The moment the opaque poly bag is cut open and the item goes into a compliant one, the only surviving proof of what arrived is whatever was captured first. So the capture has to be a step that gates the rework, not a form filled in afterwards.

The shape we keep it in is deliberately boring. One row per finding, and the row is written before anything is touched:

{
  "findingId": "f-2026-10-07-0142",
  "cartonRef": "inbound-carton-88213",
  "ruleCode": "suffocation_warning",
  "channel": "amazon-fba",
  "expected": "warning text present on bags with opening >= 5in, film >= 1.5 mil",
  "observed": "opaque bag, no printed warning, opening 8in",
  "sourceUrl": "https://sellercentral.example/help/prep/bagging",
  "sourceSeen": "2026-10-01",
  "evidence": ["88213-bag-front.jpg", "88213-bag-scale.jpg"],
  "measured": { "openingIn": 8.0, "filmMil": null, "weightLb": 41.2 },
  "disposition": "rework",
  "reworkedAt": null,
  "costBearer": null
}

Three fields are the ones people leave out and later need.

sourceSeen is the date somebody actually read the rule. Without it, a rule change silently rewrites history and you cannot say what standard the item was judged against at the time.

evidence is an array, not a boolean. One photo of a bag on a bench proves the bag exists. The second photo is the one that shows the tape measure across the opening. If the finding is a measurement, the evidence has to contain the measurement.

costBearer stays null until someone with authority decides who pays a preparation defect fee. Leaving it blank is a fact. Guessing is a policy you did not agree with anybody.

The test that makes this real

Write it as a dispute test rather than a unit test:

Someone claims a carton was rejected on 14 March. Open the record for that carton. Can you state the rule, the observed value, the version of the rule that was in force on 14 March, and what was done, without asking anyone who was on shift that day?

If the answer is no, your prep process is a memory system with a database attached. The database stores the outcome. The memory stores why, and memory does not survive staff turnover.

The corollary is uncomfortable but useful: a fix you cannot see is a service you cannot audit. Any provider who reports "handled" without a row behind it is asking you to take the relationship on trust, and trust is the wrong instrument for a per-carton process.

What this changes upstream

Once defects are records instead of stories, the aggregate becomes readable. Five rows with ruleCode: "expiration_date_format" from the same manufacturer is not a prep problem, it is a coding-convention problem at the factory, and the fix belongs in the purchase order rather than at a packing bench. A row with ruleCode: "preexisting_barcode" on every carton from one supplier means the inbound is arriving in retail-ready packaging that has to be broken down, which is a decision to make before you buy, not after.

That is the actual value of the record. It is not that you can win an argument, although you can. It is that the defect log tells you which upstream decision to change, and a log of "bags were wrong" tells you nothing.

What to ask your provider

Ask what they capture before reworking, and ask to see one row. Ask whether the rule they applied is versioned with the date they read it. Ask who is named as the payer when the defect is theirs, and whether that decision is in the row or in an email. Ask what happens to the evidence file if you leave after six months.

Four questions, and the answers are either records or stories.

FulfillNexa by SBT (fulfillnexa.com) runs inbound, storage and outbound across three warehouses in China, with 3,000 square meters in Shenzhen, 13,000 in Suzhou and 8,000 in Dongguan, and the reason we keep writing these shapes down is that a prep dispute is settled by the record and by nothing else. We do not publish rates or transit commitments, and none of the above depends on either.

A Shopify Order From a China Warehouse Is Six Decision Points, Not One API Call

life — Tue, 06 Oct 2026 18:08:58 +0000

The mental model most sellers carry into a China fulfillment arrangement is a single arrow: an order lands in Shopify, a webhook fires, a warehouse prints a label. Everything after that is a tracking number.

That arrow hides six decisions. The reason it is worth naming them one by one is that each one has a place where it can be made automatically, a place where it has to be made by a person, and a place where it is currently being made by nobody. The third category is where parcels get stuck.

One: intake, keyed on something stable

An order arrives. Before anything physical happens, the question is whether this is a new instruction or a repeat of one you already acted on. Any intake path that can deliver the same event twice has to be keyed on a stable identifier rather than on arrival time, and the key has to be unique in the store, not just in the queue.

The failure looks like this: two warehouse tasks for one order, two labels, two charges, and a customer who receives the same item twice and is charged once. The fix is a constraint, not a check. If the intake table has a unique index on the order identifier, a duplicate cannot be created; if the only protection is a worker noticing, it will eventually not be noticed.

We subscribe to Shopify's orders/create topic, and the topic name is the easy part. The part that decides whether the integration survives is what happens to the second delivery of the same event.

Two: the address, before anything is printed

An address that will fail at the destination should fail at the origin. Shopify already models this as a first-class reason: INCORRECT_ADDRESS is one of the enumerated hold reasons on a fulfillment order, alongside INVENTORY_OUT_OF_STOCK, AWAITING_PAYMENT, HIGH_RISK_OF_FRAUD, AWAITING_RETURN_ITEMS, UNKNOWN_DELIVERY_DATE, ONLINE_STORE_POST_PURCHASE_CROSS_SELL and OTHER.

That list is worth reading once, because it tells you what the platform expects a fulfillment flow to be able to stop for. A warehouse integration that can only report "shipped" and "not shipped" has nowhere to put six of those eight states, so it puts them in email.

Three: inventory that is promised, not merely counted

A single stock pool shared by several sales channels needs a reservation ledger rather than a computed "available" number, because the number you compute at read time is already stale by the time the picker sees it. For a Shopify-only setup the same logic applies at the location level: the quantity you decrement is a statement about what a specific location owes, and the timing of that decrement determines whether two channels can both sell the last unit.

Four: the packing decision, which is a customs decision

Two items that would each clear a destination's low-value threshold become one consignment that does not when a picker puts them in the same box. The check therefore belongs before the parcel is created, in the packing decision, and not in a report afterwards.

This is the decision most often made by whoever is standing at the bench. It is also the one that is cheapest to encode, because it needs exactly two inputs: the declared value already captured on the order, and the destination's current threshold from a dated rule table. We keep our prep and packing rules in that shape, one row per rule with the date the rule was read from its source, because a threshold that moved last month should not silently change what a shipment from March was judged against.

Five: the hold state, which Shopify already gives you

This is the decision point that separates a real integration from a queue with a label printer attached.

A fulfillment order in Shopify has statuses, and two of them exist precisely for "do not ship this yet": ON_HOLD, and SCHEDULED, which carries a fulfill_at timestamp for deferred release. The mutations are fulfillmentOrderHold (it takes the fulfillment order id and a hold object, and the reason comes from the enumeration above) and fulfillmentOrderReleaseHold, which returns the order to OPEN.

Now compare what happens in a typical China-side arrangement when an order cannot go out. The order sits in the warehouse system's own "pending" bucket. The merchant's Shopify admin shows nothing unusual. The seller finds out from a customer message.

The mapping that has to exist is short:

What stopped the order	Where it should be visible	Shopify state
Payment or fraud review	Store and warehouse	`ON_HOLD` with `AWAITING_PAYMENT` / `HIGH_RISK_OF_FRAUD`
Address failed validation	Store and warehouse	`ON_HOLD` with `INCORRECT_ADDRESS`
Stock committed elsewhere	Store and warehouse	`ON_HOLD` with `INVENTORY_OUT_OF_STOCK`
Awaiting a restock date	Store and warehouse	`SCHEDULED` with `fulfill_at`
Documents incomplete	Store and warehouse	`ON_HOLD` with `OTHER` plus a note
Ready	Warehouse queue	`OPEN`

The row that matters is the last-but-one. OTHER is not a junk slot; it is where a provider's own reasons go, and a note attached to a hold in the merchant's store is a different service level from the same fact arriving in a weekly email.

Six: handoff and the loop back

A tracking number written into Shopify is not the end of the flow. The exception loop is what makes the previous five decisions improve: a parcel returned undelivered, a shipment held at a border, a carton rejected at a receiving center. Each of those has to come back as a row with an owner, not as a message in a thread.

If a provider cannot show you one closed exception from last quarter, with the rule that triggered it, who owned it, and how long it stayed open, then the exception log is a feature of their dashboard rather than a record of your operation.

What should stay human

Three cases, and it is worth being explicit because over-automating them is how sellers get surprised:

A hold whose reason is OTHER and whose note is ambiguous. An address that fails validation a second time. An item whose remaining shelf life clears the requirement by a thin margin in a category that expects a buffer. In each case the correct system behavior is to stop and ask, and the correct provider behavior is to have a named person who gets asked.

Automation in a fulfillment arrangement is not there to remove judgment. It is there to make judgment necessary only at the three or four places where judgment actually changes the outcome, and to make every one of those places visible in the same screen the seller is already looking at.

FulfillNexa by SBT (fulfillnexa.com) runs inbound, storage and outbound for Shopify and marketplace orders from three warehouses in China, with 3,000 square meters in Shenzhen, 13,000 in Suzhou and 8,000 in Dongguan. We do not publish rates or transit commitments, and nothing above depends on either. The status names, hold reasons and mutation names quoted here are Shopify's own published Admin GraphQL vocabulary, and the version to work against is whatever their docs say on the day you build.

Prep requirements are a rules table, not an if-statement

life — Tue, 06 Oct 2026 16:42:40 +0000

Every fulfillment system ends up with a function that decides whether an item needs a poly bag, a label, a bubble wrap layer, or a "Sold as set" marking. It usually starts as ten lines of conditionals and it ends as the place where bugs go to hide, because the rules it encodes are not ours. They belong to the channel, and the channel changes them.

When the rules live in code, a change in the channel's requirement becomes a deploy. When they live in a table, it becomes a row with an effective date, and the difference shows up the first time you have to explain what a shipment that moved in March was prepped under.

What the rules actually look like

Strip away the per-channel naming and a prep rule has the same shape everywhere:

when  <item attributes>
require <one or more prep actions>
from    <date>
to      <date or open>
source  <where this came from>

The attributes are things like whether the item is liquid, whether it ships in its own packaging, whether the manufacturer barcode is scannable, whether it is a multi-pack that must not be separated, whether it is fragile, whether it has a shelf life. The actions are things like bag it, label it, overlabel the old barcode, bundle it with a set marker, add a suffocation warning, or reject it as unshippable until the seller changes the packaging.

Two of those are easy to miss and cause most of the rework. A bag can be required while the warning text is not, or the warning can be required only above a certain opening size. And an item that already carries a scannable barcode may still need an overlay label if that barcode encodes something the channel cannot resolve. Encoding either of those as a single boolean is how a shipment gets rejected at the door with a reason nobody in the building can reproduce.

The schema

CREATE TABLE prep_rule (
  id            bigserial PRIMARY KEY,
  channel       text        NOT NULL,   -- 'amazon-fba', 'walmart-wfs', 'dtc-marketplace'
  rule_code     text        NOT NULL,   -- stable name: 'poly_bag', 'suffocation_warning'
  predicate     jsonb       NOT NULL,   -- attribute constraints
  actions       jsonb       NOT NULL,   -- ordered list of prep actions to apply
  params        jsonb       NOT NULL DEFAULT '{}',  -- thresholds for this rule version
  effective_from date       NOT NULL,
  effective_to   date,                  -- NULL means still in force
  source_url    text        NOT NULL,   -- where the channel published it
  source_seen   date        NOT NULL,   -- when we read it
  UNIQUE (channel, rule_code, effective_from)
);

effective_to is never edited once a successor row exists. When a channel changes a rule, you close the old row by inserting a new one with a later effective_from, and you leave the old row alone. The history is the point. A seller disputing a March rejection needs the March rule, not today's rule with a comment attached.

params holds the thresholds. Keeping them out of the predicate is deliberate: the predicate decides whether the rule applies, the params decide how the action is carried out. "Bag it" is the action; the opening-size threshold that triggers the extra warning text is a parameter of a different rule that may also apply.

CREATE TABLE prep_action (
  code        text PRIMARY KEY,
  label       text NOT NULL,
  labor_secs  integer NOT NULL,   -- for costing, not for the customer-facing quote
  consumable  text                -- 'poly_bag_6x8', 'thermal_label', 'bubble_wrap'
);

Resolving a SKU at a point in time

type ItemAttrs = {
  isLiquid: boolean
  shipsInOwnPackaging: boolean
  barcodeScannable: boolean
  isSet: boolean
  fragile: boolean
  shelfLifeDays: number | null
  longestOpeningCm: number | null
}

export function requiredPrep(
  channel: string,
  item: ItemAttrs,
  asOf: Date
): { ruleCode: string; actions: string[]; params: Record<string, unknown> }[] {
  const day = asOf.toISOString().slice(0, 10)
  const rows = db.any(
    `SELECT rule_code, actions, params FROM prep_rule
      WHERE channel = $1
        AND effective_from <= $2
        AND (effective_to IS NULL OR effective_to > $2)
      ORDER BY rule_code`, [channel, day])

  return rows
    .filter(r => matches(r.predicate, item))
    .map(r => ({ ruleCode: r.rule_code, actions: r.actions, params: r.params }))
}

matches is a small predicate evaluator, not an expression engine. Something like { "isLiquid": true, "shipsInOwnPackaging": false } meaning both must hold, with arrays allowed on the right side for "any of". Resist the urge to let the predicate be arbitrary SQL or JavaScript. The moment it is, a bad row can do more than misclassify a SKU.

The asOf parameter is the whole design. Every caller from the receiving bench to the invoicing job passes the same date, and the answer is stable for that date forever.

The three bugs this shape prevents

Silent drift. A channel updates its requirement and nobody tells engineering. With rules in code, the system keeps applying the old rule and the failure surfaces as rejected shipments weeks later. With rules in a table plus a source_seen date, a periodic check that re-reads the published page and flags anything older than, say, ninety days is a fifteen-line job. It does not decide what the new rule is. It says "this one is old, go look".

Unreproducible decisions. "Why did we bag and label this twice?" is answerable when the resolution is a query: which rows were in force, what the item attributes were, what the actions produced. It is not answerable when the logic was an if-chain that has since been edited twice.

Costing that disagrees with the bench. labor_secs and consumable per action mean the prep charge derived from the same rules the operators follow. When the two come from different places, the invoice is always the one that turns out wrong.

Tests

test('rule that expired is not applied', () => {
  const r = requiredPrep('amazon-fba', liquidItem, new Date('2026-06-01'))
  expect(r.map(x => x.ruleCode)).not.toContain('poly_bag_old_threshold')
})

test('two rules can apply to one item', () => {
  const r = requiredPrep('amazon-fba', wideOpeningLiquidItem, new Date('2026-10-06'))
  expect(r.map(x => x.ruleCode).sort()).toEqual(['poly_bag', 'suffocation_warning'])
})

test('closing a rule does not change historical answers', () => {
  const before = requiredPrep('amazon-fba', liquidItem, new Date('2026-03-01'))
  insertNewRuleVersion('poly_bag', new Date('2026-10-01'))
  const after = requiredPrep('amazon-fba', liquidItem, new Date('2026-03-01'))
  expect(after).toEqual(before)
})

The last one is the test that protects the design. If closing a rule can change what March resolves to, the table is a cache of today's opinion rather than a record.

Where the numbers come from

One honest caveat about params. The thresholds belong to the channel and they move. Nothing in this schema makes your numbers correct; it only makes them dated, attributable and re-runnable. Every row carries a source_url and a source_seen date precisely so that a wrong number is a row you can find and replace, rather than a constant buried in a function nobody remembers touching.

FulfillNexa by SBT (fulfillnexa.com) runs inbound, storage and outbound across Shenzhen, Suzhou and Dongguan, 24,000 square meters combined and 13,000 of it in Suzhou, and the prep rules we apply are kept in exactly this shape because our operators need to see why an item was treated a certain way, not just that it was. We do not publish rates or transit commitments, and none of the above depends on either.

Shelf life is a column you query, not a number you decrement

life — Tue, 06 Oct 2026 16:41:16 +0000

A seller with 900 units of a six-month-shelf-life product discovers the problem at the worst possible moment: the fulfillment center refuses the inbound at check-in because the remaining life is under whatever the channel's floor happens to be that year. Nothing in the seller's system disagreed with the delivery. The stock was there, the count was right, the labels scanned. The only thing wrong was a date nobody had been querying.

Expiry handling looks like a small feature and turns into a data-model decision, because the number that matters is not "days left" but "was this acceptable at the moment it was received, and at the moment it shipped, and if we are asked next month, at those moments too".

The mistake is storing a countdown

sku_stock: id, sku, quantity, expiry_days_left

A countdown column has to be maintained by something. A nightly job decrements it, and the job runs late once, or the server was down for three days, or somebody backfills last November's receipts and the counter is now wrong in a direction that only shows up when a customer complains. Worse, the value is derived data that has been promoted to a source of truth, so you can no longer answer "what did we think on the 14th of March".

The fix is boring and correct: store the date, derive the remaining life at read time.

CREATE TABLE lot (
  id             bigserial PRIMARY KEY,
  sku            text   NOT NULL,
  lot_code       text,                    -- manufacturer batch code, if any
  manufacture_dt date,
  expiry_dt      date,                    -- NULL for non-perishable
  received_dt    date NOT NULL,
  quantity       integer NOT NULL CHECK (quantity >= 0),
  warehouse_code text NOT NULL,
  state          text NOT NULL DEFAULT 'available'
                 -- available | quarantined | expiring | expired | returned_hold
);

Remaining life at any instant is then arithmetic, and it is the same arithmetic everywhere:

export function daysRemaining(lot: Lot, on: Date): number | null {
  if (!lot.expiry_dt) return null
  return daysBetween(startOfDay(on), startOfDay(lot.expiry_dt))
}

Acceptance is a policy question with a date attached

The number that gets a shipment rejected is not a property of the goods. It is a policy of the channel, and it differs by category and by how much total life the item claims. A 30-day floor against a product with 180 days total life is a very different rule from a one-third-of-remaining-life rule against a product with two years.

So the item record needs the total life, and the check needs both:

type ShelfLifePolicy = {
  minDaysAtReceipt: number
  minFractionRemainingAtReceipt?: number
}

export function canReceive(lot: Lot, item: { totalLifeDays: number | null },
                           policy: ShelfLifePolicy, on: Date): Verdict {
  const left = daysRemaining(lot, on)
  if (left === null) return ok('no expiry')
  if (left < 0) return reject('EXPIRED', { left })
  if (left < policy.minDaysAtReceipt) return reject('BELOW_MIN_DAYS', { left, need: policy.minDaysAtReceipt })
  if (policy.minFractionRemainingAtReceipt && item.totalLifeDays) {
    const frac = left / item.totalLifeDays
    if (frac < policy.minFractionRemainingAtReceipt) return reject('BELOW_FRACTION', { frac })
  }
  return ok('acceptable')
}

totalLifeDays is the field that is almost always missing, and without it a fraction-based rule cannot be evaluated at all. Ask for it when a perishable SKU is first set up, and make the setup fail loudly if it is not there. Discovering during a rejection that you never recorded the total life is the expensive version of the same conversation.

The policy itself belongs somewhere with an effective date, because channels do revise these and a dispute about a shipment received in the spring is answered with the spring policy.

FEFO, and the trap inside it

First-expired-first-out is the obvious picking rule, and it is right most of the time. It goes wrong in two specific ways.

The first is partial lots. If a lot of 400 has 45 days left and the order needs 60 units, consuming from that lot is correct. If the next order needs 400, you now have to decide whether to split the lot or push the short-dated units to the back and ship fresher stock, which quietly turns FEFO into LIFO for the remainder. Make the split decision explicit in the reservation layer rather than leaving it to whichever picker reaches the bin first.

The second is the multi-channel case. A marketplace that requires 90 days of remaining life at receipt cannot be served from the same pool as a direct-to-consumer order with no such floor. FEFO across the whole pool will happily allocate the shortest-dated lot to the channel that cannot accept it.

export function allocate(sku: string, qty: number, channel: ChannelConstraint, on: Date) {
  const lots = db.many(
    `SELECT * FROM lot
      WHERE sku = $1 AND state = 'available' AND quantity > 0
        AND (expiry_dt IS NULL OR expiry_dt >= $2)
      ORDER BY expiry_dt NULLS LAST, received_dt`,
    [sku, channel.minExpiryDate(on)])          // the floor is in the WHERE, not in memory

  return take(lots, qty)                        // throws SHORT_STOCK with the gap
}

Putting the channel's floor into the WHERE clause is the difference between a rule that is enforced and a rule that is hoped for. An application-side filter gets forgotten by the next caller that writes a new query path.

Expiry is an event, not a state you poll

Stock does not become expired because a cron job noticed it. It becomes expired on a date. Model it that way and the reporting stops lying:

CREATE VIEW lot_status AS
SELECT l.*,
       daysRemaining_fn(l.expiry_dt, CURRENT_DATE) AS days_left,
       CASE
         WHEN l.expiry_dt IS NULL THEN 'none'
         WHEN l.expiry_dt <  CURRENT_DATE THEN 'expired'
         WHEN daysRemaining_fn(l.expiry_dt, CURRENT_DATE) <= 30 THEN 'expiring'
         ELSE 'ok'
       END AS life_state
FROM lot l;

Then the operational report is a query, and the "how much stock is about to become unsellable" number is the same number in the dashboard, the email alert and the reconciliation export, because all three read the same view instead of each recomputing it slightly differently.

The one thing to avoid is a nightly job that mutates state to 'expired'. That gives you two truths, and they will disagree for whatever window the job has not covered.

What this buys in practice

Three things show up within a month. Rejections at the channel's door drop, because the floor is enforced at allocation rather than discovered at check-in. Write-offs get attributed, because the lot row records when a quantity was destroyed and under which state, so "we lost 4% of this SKU" becomes "3.1% expired before sale, 0.9% damaged at inbound". And the argument with the supplier about which batch is short-dated ends, because the receipt recorded manufacture_dt and expiry_dt at the dock instead of reconstructing them from a carton later.

None of that is exotic. It is a date column instead of a countdown, a policy row with an effective date instead of a constant, and a WHERE clause instead of a hope.

FulfillNexa by SBT (fulfillnexa.com) receives, stores and ships across Shenzhen, Suzhou and Dongguan, 24,000 square meters in total with 13,000 of that in Suzhou, and lot dates are captured at the dock because a rejection discovered 12,000 kilometers from the buyer is a far more expensive conversation than one at the pallet. We do not publish rates or transit commitments, and none of the above depends on either.

Splitting one order into two parcels without lying to anyone

life — Mon, 05 Oct 2026 12:52:23 +0000

A support ticket arrives as a screenshot. The customer bought three items, received two, and the storefront still says the order is pending. The warehouse says both shipments went out on time. Nobody is wrong, and everything is broken, which is the signature of a split order handled at the wrong layer.

Splitting is not an exception path in fulfillment software. It is the normal path for any warehouse holding mixed stock depths, and the systems that treat it as a corner case fail in exactly the ways support teams describe as mysterious.

The model mistake

Most order systems store an order and a shipment as the same object, because at launch every order was one parcel. The moment a split happens, that object has to be two things at once: the commercial promise the customer bought, and the physical thing a carrier label was printed for.

Keep them separate from the start.

type Order = {
  id: string;
  currency: string;
  items: OrderLine[];               // quantity bought, allocated, shipped
  freightChargedOnce: Money;        // what the customer paid, billed a single time
  splits: Shipment[];
};

type Shipment = {
  id: string;
  orderId: string;
  lines: ShipmentLine[];       // subset of order lines with quantities
  trackingNumber: string | null;
  carrier: string;
  entry: CustomsEntry | null;  // one per shipment, never shared
  state: 'draft' | 'picked' | 'packed' | 'tendered' | 'inTransit'
       | 'delivered' | 'failed';
};

The invariant that prevents the screenshot is the first one: the customer is charged for shipping once, on the order, and a split never creates a second charge. Systems get this wrong when the shipping calculation is attached to the shipment record and the split inherits whatever the pricing engine last returned.

create table shipment (
  id            uuid primary key,
  order_id      uuid not null references "order"(id),
  seq           int  not null,
  unique (order_id, seq)
);

create or replace function assert_single_freight_charge() returns trigger language plpgsql as $$
begin
  if exists (select 1 from "order" where id = new.order_id and shipping_charged_count > 1) then
    raise exception 'order % charged freight more than once', new.order_id;
  end if;
  return new;
end $$;

Deciding the split

Allocation runs before split logic, not after. Attempt to satisfy each line from one location and one availability window, then partition.

function planShipments(order: Order, stock: StockIndex): ShipmentPlan[] {
  const groups = new Map<string, ShipmentLine[]>();

  for (const line of order.items) {
    const qty = line.quantity - line.quantityShipped;
    if (qty <= 0) continue;

    for (const chunk of allocate(line.sku, qty, stock)) {
      const key = `${chunk.locationId}|${chunk.availabilityBucket}|${chunk.dangerousGoods}`;
      push(groups, key, { ...line, quantity: chunk.quantity });
    }
  }

  return [...groups].map(([key, lines], i) => ({
    seq: i + 1,
    reason: splitReason(key),   // 'availability' | 'location' | 'hazmat' | 'size'
    lines,
  }));
}

availabilityBucket is the one people leave out. Two lines both in stock but one held for a pending quality check should not ship together, and a split driven only by location will happily put both in the same box and then hold the box. The split key has to contain every dimension on which "together or not" can change, and those dimensions are: physical location, availability status, handling class, size or weight band, and any destination rule that binds only some items.

Store the reason. A split without a recorded reason cannot be audited, and the first question your operations lead asks during a spike is why the split rate doubled this week. If the answer lives only in code, it will be guessed.

Every shipment gets its own downstream objects

Once the partition exists, three things have to be per shipment rather than per order, and each of them is a place where a naive implementation leaks.

Labels and tracking. One label per shipment, one tracking number per shipment, and the storefront shows one delivery per box instead of a single progress bar. A progress bar for a split order is a lie by construction.

Idempotent status ingestion. Carrier webhooks arrive twice and out of order, and with two shipments from one order you now also get interleaving. Key the state machine on shipmentId + status + occurredAt, reject stale transitions, and never derive order status by reading the last event. Order status should be a fold over its shipments.

const orderStatus = (s: Shipment[]) =>
  s.every(x => x.state === 'delivered') ? 'delivered'
  : s.some(x => x.state === 'delivered') ? 'partiallyDelivered'
  : s.some(x => ['tendered','inTransit'].includes(x.state)) ? 'inTransit'
  : 'preparing';

Customs entries. This is the one that stopped being theoretical in 2026. A shipment crossing into the United States is now declared, not waved through, and a declaration belongs to a shipment. Splitting an order therefore splits the declared value across two filings, and each filing needs its own line-level classification and its own value. Two consequences follow that a pure logistics model never sees.

The first is threshold sensitivity. Value bands decide which entry procedure applies, so a split can move a parcel from one procedural bucket to another, and the cost of the split is not just the second box and the second label. If your pricing code assumes freight cost scales with weight only, it will be wrong on the day someone splits an order to save a delivery.

The second is that arbitrary re-splitting to sit under a threshold is not an optimization. It is misrepresentation, because the underlying transaction is a single sale to a single person. Encode that as a hard rule, not a guideline, and let the split planner see the constraint instead of discovering it in a compliance review.

const canSplit = (plan: ShipmentPlan[], rule: EntryRule) => {
  if (plan.length > 1 && sameBuyerSameDay(plan) && rule.aggregatesByBuyerDay) {
    return { ok: false, reason: 'value_aggregation_required' };
  }
  return { ok: true };
};

Tests worth writing before the traffic does it for you

A two-line order with one line out of stock produces two shipments, one freight charge, and an order status of partiallyDelivered once the first is delivered. A webhook replayed after a later status does not regress the shipment. A split where the second shipment is cancelled returns the reserved stock without touching the first shipment's label. A carrier event that arrives with a shipment id belonging to a different order is rejected loudly instead of matched fuzzily. And the fold above returns delivered only when every shipment is delivered, which sounds obvious and is the assertion that catches an implementation where the last event wins.

What the customer should see

Nothing exotic. Two deliveries, each with its own contents, its own tracking link, and its own expected arrival if you can compute one honestly. The order page should make the split visible when it happens, not after the first parcel arrives, because a customer who knows a second box is coming never files the ticket that a confused customer files on day three.

The engineering version of the same advice is smaller. Model the order and the shipment as different objects, charge freight once and enforce it, give every shipment its own declaration, and record why the split happened. The ticket in the screenshot was not a picker error. It was a data model that had never been asked to describe two boxes.

Written from the operations side of FulfillNexa by SBT (fulfillnexa.com), a China-based cross-border fulfillment provider. Stock sits across three sites in mainland China, with the 13,000 square meter Suzhou warehouse handling supplier consolidation, 8,000 square meters at Dongguan for ecommerce and single-unit orders, and 3,000 square meters at Shenzhen for oversized cargo. Charges are quoted per shipment and per order value band rather than published as a rate card.

Landed cost is a join against a dated rule table, not a column

life — Mon, 05 Oct 2026 12:52:08 +0000

Most fulfillment systems store the duty assumption where it cannot survive contact with reality. A field on the product row. Sometimes a percentage in a shipping settings screen, entered the day the account was set up and never revisited. It looks like a reasonable denormalization until the first time a rule changes underneath it and forty thousand historical orders quietly become wrong.

The reason that shape fails is that duty is not an attribute of a product. It is the result of a lookup against several keys at a point in time, and every one of those keys can change independently of your catalog.

landed cost = f(jurisdiction, hs_code, country_of_origin, entry_path, declared_value, date)

The date is the part people leave out, and it is the part that bites.

A timeline that is really a table

The United States small-parcel exemption makes a good worked example because it moved four times in two years and every move was dated in the record that made it.

2016-02-24  value cap raised from 200 to 800 USD (TFTEA, Pub. L. 114-125, sec. 901(d))
2025-05-02  exemption suspended for China and Hong Kong origin (E.O. 14256)
2025-08-29  exemption suspended for all countries (E.O. 14324, signed 2025-07-30)
2026-06-24  suspension written into CBP regulation, non-postal modes, indefinite
2027-07-01  statutory termination of the exemption (Pub. L. 119-21, sec. 70531(b))

If your model stores a boolean called de_minimis_eligible on the destination country, that boolean has been wrong at least twice and you have no way to know what it said on the day an order shipped. If instead eligibility is a row with a validity interval and a citation, the same query that prices today's order can price March 2025 and tell you which authority it used.

The schema

Two tables carry almost all of this. The first is the rule side, which is reference data with history.

create table customs_rule (
  id              bigserial primary key,
  jurisdiction    text        not null,           -- 'US', 'EU', 'GB'
  rule_kind       text        not null,           -- 'de_minimis' | 'informal_entry_ceiling'
                                                   -- | 'ad_valorem' | 'fee'
  hs_prefix       text,                           -- null = applies to all codes
  origin_country  text,                           -- null = applies to all origins
  entry_path      text,                           -- 'postal' | 'non_postal' | null
  threshold_cents bigint,                         -- for eligibility rules
  rate            numeric,                        -- for ad valorem rules
  currency        text,
  effective_from  date        not null,
  effective_to    date,                           -- null = still in force
  authority       text        not null,           -- citation, never a paraphrase
  source_url      text        not null,
  captured_at     timestamptz not null default now(),
  rescinded_at    timestamptz,
  check (effective_to is null or effective_to >= effective_from)
);

The second is the snapshot side, written once per shipment and never updated.

create table landed_cost_snapshot (
  id             bigserial primary key,
  shipment_id    bigint       not null,
  as_of          date         not null,
  entry_path     text         not null,
  duty_cents     bigint       not null,
  fees_cents     bigint       not null default 0,
  fx_rate        numeric      not null,
  rule_ids       bigint[]     not null,           -- which rows produced this
  recompute_of   bigint,                          -- previous snapshot, if restated
  reason         text,                            -- 'initial' | 'rule_change' | 'claim'
  created_at     timestamptz  not null default now()
);

create index on landed_cost_snapshot (shipment_id, created_at desc);

rule_ids is the column that saves you later. A snapshot with the numbers but not the rules used is an opinion. With the rules listed, it is evidence, and you can rebuild it, dispute it, or restate it against a different rule set.

Resolving a price

The lookup is an as-of join, and the ordering rules need to be explicit instead of left to whichever index the planner picked.

create function resolve_entry_path(
  p_jurisdiction text, p_value_cents bigint, p_ship_date date, p_postal boolean
) returns text language sql stable as $$
  select case
    when r.threshold_cents is not null
         and p_value_cents <= r.threshold_cents
         and p_postal
    then 'postal_informal'
    when p_value_cents <= ceiling.threshold_cents
    then 'informal'
    else 'formal'
  end
  from lateral (
    select effective_from, threshold_cents
    from customs_rule
    where jurisdiction = p_jurisdiction
      and rule_kind = 'de_minimis'
      and (entry_path is null or (entry_path = 'postal') = p_postal)
      and effective_from <= p_ship_date
      and (effective_to is null or effective_to > p_ship_date)
    order by effective_from desc
    limit 1
  ) r
  cross join lateral (
    select threshold_cents
    from customs_rule
    where jurisdiction = p_jurisdiction
      and rule_kind = 'informal_entry_ceiling'
      and effective_from <= p_ship_date
      and (effective_to is null or effective_to > p_ship_date)
    order by effective_from desc
    limit 1
  ) ceiling;
$$;

In the United States the informal ceiling is 2,500 dollars, so nearly every single-parcel shipment lands in informal entry after June 2026 rather than in the exemption it used to claim. That distinction is invisible in a pricing model that only knows "duty or no duty", and it is exactly the thing that determines whether a broker, a bond, or a carrier filing arrangement has to exist before the parcel moves. Treating entry path as a derived output of the rule table instead of a hardcoded string is what lets the same code keep working when a jurisdiction reorganizes its low-value channel, which the postal side of that June rule did in a separate document the same day.

The invariant worth guarding

Rules for the same key must not overlap or leave gaps at a boundary, because both failures are silent. A gap returns no row, and the code that prices "no rule found" usually falls back to zero instead of raising. An overlap returns two rows and the order by effective_from desc limit 1 silently picks one.

create or replace view rule_overlap as
select a.jurisdiction, a.rule_kind, a.hs_prefix, a.origin_country,
       a.id as rule_a, b.id as rule_b
from customs_rule a
join customs_rule b
  on  a.id < b.id
  and a.jurisdiction = b.jurisdiction
  and a.rule_kind    = b.rule_kind
  and a.hs_prefix    is not distinct from b.hs_prefix
  and a.origin_country is not distinct from b.origin_country
  and a.effective_from < coalesce(b.effective_to, date 'infinity')
  and b.effective_from < coalesce(a.effective_to, date 'infinity');

Run that view in CI against your seeded reference data, not only in production. Overlaps get introduced by the most natural operation in this domain, which is importing a competitor's rate table or accepting a broker's spreadsheet as if it were a dimension.

Restating instead of editing

When a rule is rescinded with retroactive effect, which is what happened in February 2026 when the Supreme Court held that IEEPA does not authorize additional tariffs and the duties imposed under it were ended the same day, the tempting move is to update the rate. Do not. Historical shipments were priced under a rule that existed at the time, and the money actually moved accordingly.

The correct sequence is mechanical.

update customs_rule
   set rescinded_at = now(), effective_to = date '2026-02-19'
 where id = 1488;

insert into customs_rule (jurisdiction, rule_kind, rate, effective_from, authority, source_url)
values ('US', 'ad_valorem', 0.1000, date '2026-02-24',   -- placeholder rate and date,
        'Proclamation under section 122, Temp. Import Surcharge',   -- load your own lookup
        'https://www.federalregister.gov/...');

insert into landed_cost_snapshot (shipment_id, as_of, entry_path, duty_cents,
                                  fees_cents, fx_rate, rule_ids, recompute_of, reason)
select s.shipment_id, s.as_of, s.entry_path, /* recomputed */, s.fees_cents, s.fx_rate,
       array_agg(new_rule.id), s.id, 'rule_change'
  from landed_cost_snapshot s
  join unnest(s.rule_ids) old_rule(id) on true
  join customs_rule new_rule
    on new_rule.jurisdiction = old_rule.jurisdiction
   and new_rule.rule_kind    = old_rule.rule_kind
   and new_rule.effective_from = date '2026-02-24'
 where s.created_at < now() - interval '1 day'
 group by s.id;

Every restatement keeps its parent in recompute_of, which gives you a chain per shipment and lets finance see the original figure, the restated figure, and the authority that caused the difference. That chain is also the only sane way to build a refund claim list, because the claim has to be scoped by entry date and by which rule was in force on it.

The tests that catch the actual bugs

Three of them cover most of the pain. A boundary test that asserts a shipment dated at midnight on the day a rule changes resolves to the new rule and not the old one, since effective_to exclusivity is easy to write wrong. A no-rule-found test that asserts the resolver raises instead of returning zero, because zero duty is a legitimate answer in one case and a catastrophe in another, and only an explicit exception distinguishes them. And a determinism test that re-prices a frozen historical shipment after a rule insert and asserts the stored snapshot is byte-identical, since the whole point of the design is that history does not move when policy does.

Rates are not configuration. They are a foreign table with an effective date and a citation attached, and the moment the model accepts that, an announcement from a regulator becomes a data change instead of a code change.

FulfillNexa by SBT (fulfillnexa.com) is the cross-border fulfillment arm of SBT, operating three warehouses in mainland China with 24,000 square meters combined: Suzhou at 13,000 for consolidation out of the Yangtze delta, Dongguan at 8,000 for ecommerce stock and one-piece orders, and Shenzhen at 3,000 for oversized and sea-air cargo. This article models the shape of the data, not any current duty rate, and rates are settled per shipment rather than published.

Finding double-billed shipments before the carrier's invoice ages out

life — Fri, 02 Oct 2026 11:24:14 +0000

Carrier invoices contain duplicates. Not many, and not usually in bad faith, but enough that any operation shipping across several carriers and several services will find some on any given month. A parcel gets relabeled and both movements get billed. A return leg is invoiced as a forward leg. A charge appears in this cycle and again in the next one because the first was disputed and re-presented.

The reason nobody catches it is timing. You get about ninety days to raise a dispute on most carrier invoices, and by the time finance notices that total freight spend is drifting above expectation, the window has closed. So the detection has to be a job that runs against the invoice itself, on the day it lands, rather than a quarterly review.

What counts as the same shipment

The obvious key is the tracking number, and it is not enough. Carriers legitimately bill one tracking number more than once, for a forward leg and a return, or for a re-delivery attempt booked as a separate service. The key that works in practice is a tuple.

const billKey = (line) => [
  line.carrier,
  line.trackingNo,
  line.serviceCode,
  line.billableWeight != null ? round1(line.billableWeight) : 'na',
  line.billedAmountCents,
  normalizeDate(line.shipDate),      // carrier timezone, not yours
].join('|');

Weight belongs in the key precisely because it is the field carriers get wrong. If the same tracking number appears twice with different billable weights, that is not a duplicate, that is a re-weigh, and it is a different kind of exception worth its own bucket.

Date needs normalising before anything else. Invoice lines arrive in the carrier's timezone, and a shipment that left a China warehouse at 23:00 will land on two different dates depending on which system generated the line. Truncate to the shipping date rather than the invoice date, and never compare timestamps across the two.

Two passes, because the failure modes differ

Within one invoice. Straightforward grouping. Any key with a count above one is a candidate, and the candidates are usually re-presents of the same charge under a slightly different reason code.

select carrier, tracking_no, service_code, ship_date,
       count(*)                        as lines,
       sum(billed_amount_cents)        as total_cents,
       array_agg(distinct reason_code) as reasons
  from invoice_lines
 where invoice_id = $1
 group by 1,2,3,4
having count(*) > 1
 order by total_cents desc;

Across cycles. Harder, because the second copy arrives weeks later in a file you have no reason to compare against the first. This needs a persistent table of every line ever billed, keyed on the tuple, with the invoice it came from. A new line that matches an older one is a duplicate unless the older one was reversed, which is why reversals have to be recorded as rows rather than as edits.

The distinction matters. Within-invoice duplicates are usually a data problem in the file you are holding. Cross-cycle duplicates are a billing behavior, and the ones that repeat across months are the ones worth a conversation.

What you do with a hit

Do not auto-dispute. A duplicate detector that files its own disputes will eventually file a wrong one, and carriers remember. Route hits to a queue with the two lines side by side, plus the scan history for that tracking number, and let a person decide in seconds rather than minutes.

The fields worth showing together, because they are what settles it: both invoice dates, both amounts, the service code on each, the first scan event, the delivery event, and whether either line has already been credited. Half of these get dismissed on sight once the scan history is visible, which is the point of putting it in the view.

Track two numbers on the queue itself. How much was flagged, and how much was actually recovered. The gap between them is your false-positive rate wearing a different hat, and if recovery drops while flagged volume climbs, the key tuple has drifted and needs tightening rather than the carriers needing a lecture.

The boring win

The first month of this produces a small refund and a sense of accomplishment. The value is not in that month. It is that after six months you have a per-carrier history of how often their billing contradicts itself, which turns a vague feeling that one carrier is expensive into a specific number you can take into a rate conversation.

That history is a byproduct of a job that runs on the day the invoice lands, and it is the reason to build the detection as a permanent table rather than as a script you remember to run.

FulfillNexa by SBT (fulfillnexa.com) is a China-based cross-border 3PL running three China warehouses totalling 24,000 m², with Suzhou at 13,000 m² for supplier consolidation, Dongguan at 8,000 m² for ecommerce warehousing and one-piece fulfillment, and Shenzhen at 3,000 m² for oversized cargo and sea-air work. Rates, product acceptance and delivery arrangements are confirmed per shipment.

A hold-and-release queue for orders waiting on paperwork

life — Fri, 02 Oct 2026 11:24:06 +0000

Every fulfillment system eventually meets the order that cannot ship for a boring reason. The battery datasheet has not arrived. The commercial invoice lists a country of origin the buyer disputes. The destination needs a declaration the seller never filed. The order is real, the stock is real, and nothing will move until a document shows up.

Most implementations handle this badly. Somebody puts a note on the order, which means the order keeps flowing through the pick wave and gets pulled back at the pack bench. Somebody else adds a boolean called on_hold, which is worse, because a boolean cannot tell you why, who is chasing it, or when it stops being acceptable to wait.

A hold is a state with a reason, not a flag

The model that survives contact with a real warehouse is small. A hold has a reason code, an owner, a created time, an optional expiry, and a set of orders it applies to. Orders are not held, they carry holds, which sounds like a distinction without a difference until the first time one order needs two of them.

create table holds (
  id            uuid primary key,
  order_id      uuid not null references orders(id),
  reason        text not null,          -- doc_missing, declaration_required, address_unverifiable
  detail        jsonb not null default '{}',
  owner         text,                   -- who is chasing it
  opened_at     timestamptz not null default now(),
  expires_at    timestamptz,            -- what to do if nothing arrives
  released_at   timestamptz,
  released_by   text
);

create unique index one_open_hold_per_order_reason
  on holds (order_id, reason) where released_at is null;

The partial unique index does the quiet work. Without it, the same missing document discovered by three different checks creates three open holds, and releasing one leaves the order stuck with no visible reason. With it, the second insert fails, and the code path that found the problem learns that somebody already found it.

Where the check belongs

Release the hold at the last moment that still allows a release to be cheap. Too early and you block orders that would have cleared anyway, which is what starves your pick rates. Too late and a picker walks a carton to the bench, scans it, and has it pulled back from them.

In practice that means two gates rather than one.

Gate	Question	Cost of being wrong
Wave release	Can this order be picked at all?	A wasted walk, recoverable
Label purchase	Will this order be allowed to leave the building?	A label paid for, a parcel to retrieve

The label gate is the expensive one, and it is exactly where most systems stop checking. If the label prints, the parcel is committed, so a document hold discovered after that point is already a reverse-logistics problem. Check holds immediately before calling the rating and label API, not when the order was imported.

Expiry is the part people skip

A hold without an expiry is a decision you refused to make. Attach one and the queue becomes actionable, because now you can ask a question that has a real answer: what happens to this order if nothing arrives in five days?

The answers are ordinary. Cancel and restock. Ship anyway, if the missing document turns out not to be legally required for that lane, which is a rules question rather than a judgment call. Escalate to a human with the clock already running. Whatever you pick, encode it as an action on the hold rather than as a habit, because the alternative is a queue that nobody trusts to be looked at.

// runs every few minutes; the point is that a hold cannot silently age
async function sweepExpiredHolds(now) {
  const expired = await db.query(
    `select id, order_id, reason from holds
      where released_at is null and expires_at is not null and expires_at < $1
      for update skip locked`, [now]);

  for (const hold of expired) {
    const action = POLICIES[hold.reason]?.onExpiry ?? 'escalate';
    await apply(action, hold);        // cancel | ship_anyway | escalate
    await audit.record({ type: 'hold_expired', holdId: hold.id, action });
  }
}

for update skip locked matters if you run more than one worker. Without it, two sweeps pick up the same expired hold and the order gets cancelled twice, or cancelled and shipped, which is the worst of the combinations.

The reporting that makes it tolerable

Two numbers, on one screen, refreshed continuously. How many orders are currently held, broken by reason. And how long each reason typically takes to clear, as a median over the last thirty days rather than a snapshot.

The first number tells you whether you are meeting the day's volume. The second tells you which reason is actually a process problem. A reason code that clears in hours is a document you can chase. A reason code whose median is nine days is a supplier relationship or a data-collection step you have never fixed, and no amount of queue engineering will shorten it.

That is the whole design. Holds as rows rather than flags, two gates instead of one, an expiry that forces a decision, and a median that tells you what to fix upstream.

Retrying a label API that already charged you

life — Thu, 01 Oct 2026 07:43:21 +0000

Most retry advice assumes the operation you are retrying is free. For a shipping label API that assumption is wrong in an expensive way: the call creates a real object, it bills you when it succeeds, and the failure mode you actually hit is a timeout after the carrier already did the work.

Your client sees an error. The carrier has a label, a tracking number and a charge. Retry naively and now you have two of each, one of which will never be scanned, and at some point in the quarter that turns into a refund argument you cannot win because both labels are legitimately yours.

Here is the pattern that survives that.

The three outcomes, not two

Every money-spending call has three results, and code that models two is the source of the bug:

type LabelResult =
  | { kind: 'success'; tracking: string; labelUrl: string }
  | { kind: 'rejected'; reason: RejectReason }        // carrier said no. Safe to retry with changes.
  | { kind: 'unknown' }                              // timeout, 5xx after send, dropped connection.

rejected is a normal failure. Nothing was created, nothing was charged, you can retry.

unknown is the dangerous one. Nothing in the response tells you whether the carrier side succeeded, because you never got a response. Treating unknown as failure is what double-buys.

So the first rule is: never retry an unknown directly. Resolve it first.

Give every attempt a client reference you can look up

Before you can resolve an unknown, you need something to search on. Carrier APIs differ, but most accept a client reference or an external order id on the label request, and most expose a lookup by that reference.

async function createLabelWithResolve(order: Order, lane: Lane): Promise<LabelResult> {
  const clientRef = `${order.id}:${lane.code}:${attemptWindow(order.id)}`;

  const pre = await carrier.findByClientRef(clientRef);
  if (pre.found) return { kind: 'success', tracking: pre.tracking, labelUrl: pre.labelUrl };

  const res = await carrier.createLabel({ ...order, lane, clientRef });

  if (res.timedOut || res.is5xxAfterSend) {
    return resolveAfterTimeout(clientRef);
  }
  return res.ok
  ? { kind: 'success', tracking: res.tracking, labelUrl: res.labelUrl }
  : { kind: 'rejected', reason: res.reason };
}

async function resolveAfterTimeout(clientRef: string): Promise<LabelResult> {
  // The carrier may still be committing. Poll with backoff before deciding.
  for (const delay of [2000, 5000, 15000, 40000]) {
    await sleep(delay);
    const found = await carrier.findByClientRef(clientRef);
    if (found.found) return { kind: 'success', tracking: found.tracking, labelUrl: found.labelUrl };
  }
  return { kind: 'unknown' };   // still unresolved: escalate, do NOT create another label
}

The attemptWindow in the reference matters. If you bake the order id alone into the reference, a legitimate second shipment for the same order (a split, a reshipment after a loss) will collide with the first lookup and get swallowed. A per-attempt window or an explicit shipment sequence number keeps the lookup honest.

If the carrier supports a real idempotency key, use it instead of the reference and let the API do the deduplication. Reference lookup is the fallback for the many that do not.

Persist the pending state before you call

The resolve-after-timeout logic only helps if the process that runs it can be a different process, hours later. That means the intent has to be on disk before the network call, not after it.

create table label_attempt (
  id             bigint generated always as identity primary key,
  shipment_id    bigint not null references shipment(id),
  client_ref     text not null unique,
  lane_code      text not null,
  state          text not null,      -- pending | created | rejected | unresolved
  tracking       text,
  attempts       int  not null default 0,
  next_check_at  timestamptz,
  created_at     timestamptz not null default now()
);

Insert pending, then call. On success move to created with the tracking number. On rejection move to rejected. On unresolved, leave it unresolved with a next_check_at, and let a sweeper retry the lookup rather than the creation.

The sweeper is the piece that makes this operationally boring, which is the goal. An unresolved row is a question with a deadline: either the lookup eventually finds the label, or a window passes in which the carrier would certainly have committed if it had received the request, and then you can safely create a new one.

That window is a business parameter, not a technical one. Set it from the carrier's own documented commit behavior, and if they have not documented it, ask. A guess here is the difference between one orphaned label and a pile of them.

Handle the label you no longer need

Two more leaks are worth automating while you are in this code.

Voiding. A label that is never scanned can usually be voided for a refund inside a carrier-specific window. A label whose shipment was cancelled after purchase is a refund you are leaving on the table if nothing calls void. Track it as a state, not as an exception.

Orphan detection. A label created with no live shipment behind it happens when your own process dies between the carrier call returning and your database write landing. The join that finds them is short:

select la.client_ref, la.tracking, la.created_at
  from label_attempt la
  left join shipment s on s.id = la.shipment_id
 where la.state = 'created'
   and (s.status in ('cancelled') or s.id is null)
   and la.created_at > now() - interval '30 days';

Run that weekly and reconcile the results against the carrier's billing export. Anything in both lists is money you can ask for back, and the request is trivially evidenced because you have the tracking number and the cancellation timestamp.

The part that is not code

None of this removes the double-charge risk entirely, because a lookup can fail for the same reason the create did. What it does is make the residual case visible, bounded and refundable instead of silent, unbounded and absorbed.

That is the standard worth aiming at on any API that spends money: it is fine to be unsure, as long as unsure is a state you store, poll and report on rather than an error you catch and retry.

The lanes this was written against are the small-parcel and one-piece fulfillment ones FulfillNexa by SBT (fulfillnexa.com) runs from three sites in China, Dongguan at 8,000 m², Suzhou at 13,000 m² and Shenzhen at 3,000 m², where a single order can trigger a label purchase several times a day across different carriers and each of them handles client references slightly differently.