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

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.