Expiry Tracking Patterns for WooCommerce — Summary
- FIFO (First In, First Out): simplest, picks the oldest stock first by date received.
- FEFO (First Expired, First Out): picks the soonest-to-expire lot regardless of receipt date — mandatory for food, pharma, cosmetics.
- Lot Recall: requires bidirectional traceability — know which lots went to which orders AND which orders contain a recalled lot.
- All three patterns need a dedicated lot/batch table; WooCommerce core does not provide one.
WooCommerce doesn’t know what a lot is. It tracks product quantities as integers, not traceable batches. For stores selling perishables, supplements, cosmetics, or any regulated product, that’s a compliance gap — and a recall liability. Here are three concrete patterns to fill it.
The Data Model All Three Patterns Share
Before choosing FIFO, FEFO, or recall-ready architecture, you need a lot table. This is a custom WooCommerce / WordPress table (or a NetSuite custom record if NetSuite is your inventory source of truth):
wp_sx_lots ────────────────────────────────────────────── lot_id INT Primary key product_id INT WooCommerce product/variation ID lot_number VARCHAR(64) Supplier batch number expiry_date DATE Null allowed for non-expiring goods received_date DATE When stock arrived in warehouse qty_received INT Units received qty_allocated INT Units reserved for open orders qty_shipped INT Units already dispatched qty_available INT (computed) qty_received - qty_allocated - qty_shipped supplier_id INT FK to supplier record notes TEXT Recall memo, cert of analysis link created_at DATETIME ──────────────────────────────────────────────
A second table links lots to order line items for recall traceability:
wp_sx_order_lots ────────────────────────────────────── id INT order_id INT WC order ID order_item_id INT WC order item ID lot_id INT FK to wp_sx_lots qty_from_lot INT shipped_date DATE ──────────────────────────────────────
Pattern 1: FIFO Allocation
FIFO picks the lot with the earliest received_date that has available quantity. It’s the simplest pattern and is appropriate when you don’t track expiry dates — general merchandise, electronics accessories, non-perishable goods.
Query wp_sx_lots for all lots of the ordered product with qty_available > 0, ordered by received_date ASC.
Walk the result set and allocate from each lot until the ordered quantity is met. A single order may span multiple lots.
Insert one row per lot into wp_sx_order_lots and increment qty_allocated on each used lot. Do this in a DB transaction — partial allocations cause phantom stock.
Move qty_allocated to qty_shipped, write shipped_date to the order-lot row. This seals the trace record.
Pattern 2: FEFO Allocation
FEFO picks the lot with the earliest expiry_date — not the earliest receipt date. This is the correct pattern for food, supplements, cosmetics, and pharma. The goal is to ship the soonest-to-expire stock before it becomes unsellable or a disposal cost.
A lot received three months ago with a 2-year expiry should be shipped AFTER a lot received last week with a 6-month expiry. FIFO would pick the wrong lot. For any product with a defined shelf life, FEFO is the only legally defensible picking rule.
The query change from FIFO to FEFO is minimal — just change the ORDER BY:
-- FIFO:
SELECT * FROM wp_sx_lots
WHERE product_id = %d AND qty_available > 0
ORDER BY received_date ASC;
-- FEFO:
SELECT * FROM wp_sx_lots
WHERE product_id = %d AND qty_available > 0
AND (expiry_date IS NULL OR expiry_date > CURDATE())
ORDER BY expiry_date ASC, received_date ASC;
-- ^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- Secondary sort by received_date breaks ties where
-- multiple lots share the same expiry date.
Run a daily cron that finds lots where expiry_date <= CURDATE() + INTERVAL 30 DAY and qty_available > 0. Auto-apply a discount code to those products, or flag them for manual review. Selling at 20% discount beats writing off expired stock.
Pattern 3: Lot Recall Workflow
A recall means you need to answer two questions instantly: (a) which customers received a specific lot number, and (b) which lots are currently in open orders. The wp_sx_order_lots mapping table makes both queries trivial.
| Recall Question | Query | Output |
|---|---|---|
| Who received lot X? | JOIN order_lots on lot_id WHERE lot_number = ‘LOT-2026-A47’ | Order IDs → customer emails for notification |
| Is lot X in open orders? | Same join, filter order_status IN (‘processing’,’on-hold’) | Orders to cancel or hold for inspection |
| How much of lot X is still in warehouse? | SELECT qty_available FROM wp_sx_lots WHERE lot_number = ‘LOT-2026-A47’ | Units to quarantine physically |
| Which lots of product Y are at risk? | Supplier join on supplier_id, date_received range | Adjacent lots from same supplier batch |
A recall workflow that takes hours to run manually (searching orders, emailing customers one by one) can be reduced to minutes with these tables in place. The regulatory clock starts the moment you identify the issue — every hour you spend querying data is an hour you’re not notifying affected customers.
References
- FDA: Recalls, Market Withdrawals, & Safety AlertsU.S. Food & Drug Administration — regulatory framework for product recalls including notification timelines.
- 21 CFR Part 7 — Enforcement PolicyU.S. Electronic Code of Federal Regulations — FDA recall classification and procedures.
- WooCommerce Database DescriptionWooCommerce.com — schema reference for extending WC with custom tables.
- NetSuite Inventory Lot and Serial TrackingOracle NetSuite Help — native lot/serial number tracking features in NetSuite Inventory.
- ISO 22000:2018 — Food Safety ManagementISO — traceability requirements for food supply chain management.
Frequently asked questions
What is the difference between FIFO and FEFO?
FIFO allocates the oldest stock first; FEFO allocates the earliest expiry first. For perishable goods, FEFO is usually the correct default.
What data model do these patterns need?
All three share one model: stock tracked at batch level with received and expiry dates, not a single quantity per product.
How does lot recall work in WooCommerce?
A recall workflow needs batch-level traceability from goods-in through to the orders that shipped, so affected customers can be identified precisely.
Leave a Reply