Batch and expiry are not attributes of a product

You add expiry_date to the products table because the client sells food. It works while every delivery of a SKU shares a date. It stops working on the second delivery, which is roughly week two.

Batches are the unit of stock

Expiry, and often cost, belong to the receipt, not the product. Two crates of the same yoghurt received a fortnight apart are different stock with different deadlines and possibly different prices.

batches
  id
  sku
  received_at
  expiry_date
  unit_cost_minor
  supplier_ref

Every movement then references a batch, not just a SKU. Stock on hand for a product is the sum across its batches; stock expiring this week is a filter on those batches. Neither is expressible if expiry is a product column.

Picking order is a policy, not an accident

With batches you must decide which one a sale consumes. FEFO — first expired, first out — is right for perishables. FIFO is right for most other things. LIFO exists mostly for accounting reasons and rarely matches physical reality.

Make it explicit and per-category, in one function every sale routes through. Leave it implicit and the answer becomes “whichever batch the query happened to return first”, which is stable until you add an index and silently changes.

Partial consumption is the common case

A sale of 30 units against batches of 12 and 25 consumes all of the first and 18 of the second. Two movement rows, not one. Any model that assumes a movement maps to a single batch will produce a negative batch quantity the first time an order spans two deliveries.

The invariant worth enforcing in the database: no batch’s summed movements may go below zero. Products can be over-sold as a business decision; a physical batch cannot contain minus four units.

What this unlocks

Expiry reporting becomes a query rather than a manual sweep. Recalls become answerable — “which orders included batch 4471” is one join, and without batches it is unanswerable at any price. And margin is real, because cost is attached to the units actually shipped rather than a rolling average that drifts from the invoices.