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.
Top comments (0)