Building Inventory Software With Lot Tracking and Expiry Control: Cost, FEFO and Recall Readiness

What lot tracking actually is once real inventory moves

Most people describe this feature as "add a lot number and an expiry date column to the products table." That model survives about three weeks in production.

The failure looks like this. A distributor receives 40 cases of a product on lot A2291, expiry 2027-03-14. Two weeks later they receive another 60 cases, same lot number A2291, because the supplier reused a lot code across two production runs and gave the second one an expiry of 2027-09-02. Your unique constraint on (product_id, lot_number) rejects the receipt. The warehouse worker, who has a truck at the dock and a driver waiting, types A2291-B. Now you have two lots in your system and one lot in the physical world, and no report will ever reconcile them again.

The correction is that a lot is not an attribute of a product. It is its own entity with its own identity, and the natural key is not the lot number. It is the supplier plus the lot code plus the received date plus the expiry, and even that is not reliably unique. Competent builds generate an internal surrogate lot ID at receipt, treat the supplier's printed lot code as a searchable label rather than a key, and allow two internal lots to share a printed code with a merge tool for when someone confirms they are genuinely the same production run.

The second thing that surprises buyers: expiry is not one date. FEFO picking needs the expiry. Quality holds need a retest date. Customer contracts need a minimum remaining shelf life at time of delivery, often expressed as a percentage. A customer with a 50 percent remaining shelf life clause will reject a 24 month product with 11 months left, and if your system shipped it because it only checked "not expired," you eat the return freight and the restock.

Quantity is not a number, it is a ledger

The naive schema puts a quantity column on the lot row and updates it. This is the single most expensive mistake in this category, because it destroys the thing you actually bought lot tracking for: the ability to answer "where did this lot go" during a recall.

What a real build uses is an append-only movement ledger. Every event is a row: receipt, putaway, pick, transfer, adjustment, cycle count variance, scrap, return. On-hand quantity per lot per location is a projection over that ledger, not a stored truth. You can cache it in a materialized view or a summary table, but the ledger is the source of record and the cache is rebuildable from zero at any time. The moment you can rebuild on-hand from the ledger, most of the terrifying data-corruption class of bugs turn into a reprojection job instead of an incident.

This also solves concurrency. Two pickers hitting the same lot in the same second with a mutable quantity column produces a lost update or a deadlock, depending on your isolation level. With a ledger and a check that the projected balance stays non-negative, you get contention on a narrow row and a clean rejection rather than a phantom negative. In PostgreSQL the pattern that works is SELECT FOR UPDATE on a per (lot, location) balance row inside the transaction that appends the movement, which serializes only the specific bin that two people are actually fighting over.

Budget for backdated corrections. Warehouses discover mistakes days later. Someone will need to reverse a movement from last Tuesday after the month closed. Build reversal entries, never deletes, and decide up front whether a reversal reopens a closed accounting period or posts to the current one. Finance has an opinion about this and it is expensive to change later.

FEFO allocation, and why it is never actually FEFO

First Expired First Out sounds like an ORDER BY expiry_date ASC. In practice the allocation engine has to satisfy competing constraints simultaneously:

Single-lot preference. Many customers, especially in food and pharma, will not accept a pallet split across three lots because their own receiving system creates one record per lot and it triples their work. So the engine prefers the oldest lot that can fill the whole line, not simply the oldest lot.

Quality status. Lots sit in states: quarantine, available, on hold, released, rejected. A lot that failed QC must be invisible to allocation but still visible in on-hand for the finance team, because you own it and it is on your balance sheet.

Minimum remaining shelf life per customer. The same lot is allocatable to customer X and forbidden for customer Y on the same day.

Pick path. Strict FEFO can send a picker to the far end of the warehouse for a lot that expires two days earlier than the one in the next aisle. Real engines apply a tolerance window, typically 7 to 30 days configurable, inside which they optimize for pick distance rather than expiry.

Allocation also has to be reservable and expiring. When an order is picked but not shipped, that lot quantity is committed, not on-hand, not free. If your reservation has no TTL, a cancelled order leaves phantom commitments and your available-to-promise silently drops until someone notices you are refusing orders on stock you have. Reservations need an owner, an expiry and a sweeper job.

Barcodes, GS1 and the scanner reality

Lot and expiry data on a physical label is almost always GS1-128 or a GS1 DataMatrix, and the encoding matters. The relevant Application Identifiers are (01) GTIN, (10) batch or lot number, (17) expiration date, (21) serial number, (37) count. AI (10) is variable length, which means it must be terminated by a Group Separator character, ASCII 29, when it is not the last element in the barcode. Scanners in keyboard-wedge mode frequently swallow or mangle that separator, and the result is a lot number that silently absorbs the next field. You get a lot code of "A229117270314" and nobody notices until the recall.

Configure scanners to emit a visible substitute for the separator, parse with a real GS1 parser rather than a regex, and reject barcodes that do not parse cleanly rather than best-effort them. Note also that AI (17) uses YYMMDD and permits DD = 00, meaning end of that month. A naive date parse turns that into an invalid date or, worse, silently into the first of the month, which shortens your shelf life by up to 30 days on every affected lot.

If you are in US pharma, DSCSA changes the shape of this entirely: you are dealing with serialized units, not just lots, and EPCIS 1.2 or 2.0 event exchange with trading partners. That is a different and much larger project, and any vendor who quotes it at the same price as lot tracking has not done it.

Offline scanning, and what happens when the wifi drops

Warehouse wifi has dead zones, freezers kill batteries and handhelds, and a receiving dock that stops working when the AP reboots is a business outage. So the handheld app buffers locally. Now you have a distributed system, and the interesting question is what happens when two devices both allocate the last 12 units of lot A2291.

Last-write-wins is wrong here and it is what most quick builds do. Because the ledger is append-only, you do not need LWW: you need each device to generate a client-side movement ID (a UUIDv7 or a ULID works, and the time ordering helps), post movements idempotently keyed on that ID, and let the server be the only thing that decides whether the resulting balance is legal. If the server rejects a queued movement because the stock is gone, the device gets a conflict item in a queue that a human resolves, not a silent overwrite. Retries are then free, because posting the same movement ID twice is a no-op.

The parts that get skipped and then cost you: the local queue must survive an app kill and a device reboot, so it is SQLite or IndexedDB, not memory. The device clock is not trustworthy, so store both the device timestamp and the server receipt time and reconcile on server time. And the conflict queue needs a UI, an owner and an escalation, or it silently accumulates until someone finds 400 unresolved items in March.

The recall clock is the real requirement

Nobody buys lot tracking for the lot numbers. They buy it because a regulator, an auditor or a customer will one day ask: which of my customers received lot A2291, and which raw material lots went into it. In the US, FSMA Section 204 and the Food Traceability Rule expect a sortable electronic record produced within 24 hours of request. Many customer quality agreements and internal mock-recall drills demand two to four hours. FDA and comparable bodies routinely run mock recalls as part of audits.

Two directions matter and they are different queries. Trace forward: given an inbound lot, every downstream shipment and customer. Trace backward: given a customer complaint or a finished lot, every input lot and supplier. If you do manufacturing or repacking, the link between them is a genealogy record produced at the work order, and if you did not capture the consumed lots at the moment of production you cannot reconstruct it later at any price.

Test this the way it will actually be used. Pick a random lot from 8 months ago and run both traces on production-scale data. If the query takes 40 seconds against a full ledger, it will time out under the pressure of a real recall. Recursive CTEs over a genealogy edge table with an index on both directions is usually enough up to tens of millions of movements. Past that you precompute a closure table.

Related and routinely missed: audit trail. In FDA-regulated contexts, 21 CFR Part 11 means the record of who changed what, when and why is itself part of the deliverable, with reason-for-change capture on manual adjustments and no ability for an admin to edit history. Bolting that on afterward is a rewrite of your write path.

What this costs, from Digital Heroes delivery experience

Across more than 2,000 projects, the bands we see for this capability:

Focused build inside an existing product: $15,000 to $60,000, 4 to 9 weeks. You already have products, locations and orders. We add the lot entity, the movement ledger, the FEFO allocator, expiry alerting and the two trace reports. The low end assumes one warehouse, no offline, no barcode scanning, one unit of measure.

Part of a first release: $50,000 to $130,000, 10 to 16 weeks. This is lot tracking plus the receiving, picking and shipping flows around it, plus a scanner-driven handheld screen, plus roles and permissions.

Full traceability platform: $150,000 to $350,000, 6 to 12 months. Multi-site, manufacturing genealogy, offline handhelds, EDI or EPCIS exchange with partners, validated audit trail, recall workbench.

What specifically pushes this capability's number up, in rough order of impact:

Manufacturing or repacking. The moment inputs become a different output, you need genealogy, yield reconciliation and partial-consumption handling. Add 40 to 70 percent.

Offline handhelds. The conflict queue, the local store, the idempotency layer and the testing matrix are typically $18,000 to $45,000 on their own.

Regulated audit trail (Part 11, GxP, validated system with IQ/OQ/PQ documentation). Frequently doubles the QA and documentation line, and it is documentation cost more than code cost.

Catch weight and dual units of measure. If you sell by case but stock by kilogram and each case weighs differently, every quantity in the ledger becomes two numbers and every rounding decision becomes a business rule. Add 20 to 35 percent.

Migrating existing inventory. Bringing 5 years of history into a ledger model from a system that stored mutable quantities is its own project, usually 2 to 4 weeks, and the data will be worse than anyone told you.

The thing that does not drive cost much: the number of products. The thing that does: the number of distinct places in your workflow where a lot number can be created or changed. Count them before you brief anyone.

When you should not build this

If lot tracking and expiry control is the main thing you need, and your workflow is broadly standard receiving, putaway, picking and shipping, do not build custom software. Buy it.

For a distributor or a food or supplement business running under about $30M revenue with one to three sites and no unusual process, an off-the-shelf system is the right answer and it is not close. Fishbowl, Cin7, Katana and Odoo all have working lot and expiry tracking. NetSuite and Dynamics 365 Business Central do too at the ERP tier. You will spend $10,000 to $60,000 on implementation and licensing in year one and you will be live in weeks, not months, with the recall report already built and the scanner integration already debugged by someone else.

Build custom when one of these is true. Your process is genuinely non-standard and the off-the-shelf system would require so much customization that you are writing code anyway, just inside someone else's constraints and their upgrade cycle. You are building a product that other companies will use, in which case lot tracking is your product, not your back office. Your lot logic is a competitive advantage, for example allocation that accounts for potency or assay values, blending rules or a cold-chain excursion model that no packaged system expresses. Or you already have a working system that does everything else and the lot layer is the only gap, which is the $15,000 to $60,000 focused build above and is very often the best value on this page.

How to brief and vet a developer for this

Before you talk to anyone, write down: how many physical sites, whether you manufacture or only distribute, whether any customer has a minimum remaining shelf life clause, whether you sell in a different unit than you stock, whether the warehouse has reliable wifi everywhere, and who has ever asked you for a recall report and how fast they wanted it. Those six answers move the price more than everything else combined.

Then ask these questions. They are designed to sort people who have shipped this from people who have read about it.

"Do you store quantity on the lot row or derive it from a movement ledger?" If the answer is a quantity column, they have not run a recall.

"What happens when a supplier reuses a lot code with a different expiry date?" Someone who has shipped this will immediately talk about surrogate keys and merge tooling. Someone who has not will say the unique constraint handles it.

"How do you handle the GS1 AI (10) variable length field and the group separator?" If they have not hit the mangled-separator problem, they have never parsed a real supplier label.

"What does AI (17) with a DD of 00 mean and what do you do with it?" This one is close to a shibboleth. The answer is end of month.

"Two handhelds offline both allocate the last 12 units. Walk me through the resolution." Listen for idempotency keys and a human conflict queue. If you hear "last write wins" or "we sync on reconnect," end the conversation.

"Show me the trace-backward query and tell me its runtime on 10 million movement rows." Anyone who has done a mock recall has this number.

"How do I reverse a movement from a closed accounting period?" Correct answer involves reversal entries and a conversation with your finance team. Wrong answer is an admin edit screen.

And one for the commercial side: ask them what they would put in a fixed-price statement of work and what they would keep hourly. Someone who has shipped this will fix-price the ledger, the schema and the allocator, and will refuse to fix-price the data migration and the supplier label parsing, because both depend on data quality they have not seen yet. That refusal is a good sign.