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

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.