FIFO Is More Than Sorting Database Records

Material can be sorted perfectly in a database. The oldest container comes first, followed by the second, third, and fourth. Then a production worker picks up the third one. If the system merely says that another container should have been selected, FIFO is not working. Only the list is.

Real FIFO exists only when the order stored in an information system is reliably connected to specific material, physical movement, and operational rules. The database query is the easy part of the problem.

ORDER BY Solves the Sequence, Not the Process #

At database level, FIFO looks simple. Select the oldest available item by its receipt time:

SELECT *
FROM material
WHERE state = 'IN_STOCK'
ORDER BY received_at ASC
LIMIT 1;

The result can be technically correct and operationally useless. A database does not know which container is closest to the worker, whether someone moved the material, whether a label has been damaged, or whether an item marked as available is still physically in the warehouse.

Software operates on a model of reality. FIFO fails as soon as that model diverges from what is actually happening.

Imagine four containers in the sequence A → B → C → D. The system correctly determines that A should leave first, but the worker reaches for C. This is the moment that reveals what was really built:

• a list showing the recommended order,
• a control system that warns about a deviation,
• or a managed process that refuses an incorrect step unless an exception is approved.

All three solutions may use the same database sorting. Their operational value is fundamentally different.

A Scan Connects a Physical Object to the System #

A barcode or QR code is more than a convenient way to enter data. It is the link between a physical container and its digital record.

A scan should tell the system at least three things:

1. what has just been identified,
2. where in the process it happened,
3. which operation is being attempted.

Reading a code alone does not prove that the material was actually moved. A worker can scan the correct label and pick up a different container. A code can be attached to the wrong package, or the same scan can accidentally be submitted twice.

The scanning method therefore has to be designed together with the physical workstation. Label placement, scanner range, the order of actions, and the ease with which two containers can be confused all matter. A good user interface supports this flow: it expects an unambiguous action, confirms the result immediately, and tells the worker what to do next when something goes wrong.

FIFO Needs a State Model #

The sequence cannot be calculated from every item ever received. Each item must have a state that matches physical reality. A simple process might use states such as:

• in stock — the material is available for issue,
• on the line — it has been released to production,
• blocked — it exists but must not be used,
• consumed — it is no longer available.

If the oldest container is damaged, it should not stop the entire queue indefinitely. It must be excluded from the available FIFO sequence, but it must not silently disappear. The system needs to know who blocked it, when, for what reason, and what happened to it later.

It is equally important to define the exact moment from which the order is calculated. Is it the manufacturing time, arrival at the site, warehouse receipt, or release by quality control? Choosing the wrong timestamp produces a precisely sorted but factually incorrect list.

When shelf life is the deciding factor, FIFO itself may not be the right rule. FEFO may be more appropriate: the material with the earliest expiration date is used first. The label given to the method matters less than ensuring that the sequence reflects the real operational risk.

A Rule Needs a Defined Response #

When a worker scans C while A is next in line, the system must do more than display a red warning. The design has to specify what happens next:

• the operation is blocked and the correct container is shown,
• the worker can repeat the scan,
• an authorized person can approve an exception,
• or the mismatch reveals that the database no longer reflects reality and a check is required.

Not every mismatch requires a hard stop. At some workstations, stopping production completely would cause more harm than a controlled deviation. But the decision should not remain hidden in a worker’s head. An exception needs a reason, appropriate authorization, and a record.

This is where automation differs from basic digitalization. Digitalization can move a paper list onto a screen. Automation uses the data to decide whether the process may continue and consistently applies the agreed response.

Event History Matters More Than the Current State #

The current record may say that a container is on the production line. During a complaint investigation or stocktake, more questions arise: When did it arrive there? Who scanned it? From which location? Had it been blocked before? Did it bypass the sequence through an approved exception?

This is why it is useful to store not only the resulting state but also the events that produced it. Every receipt, transfer, issue, return, block, and manual intervention creates a trace containing the time, the user’s identity, and the necessary context.

Such a history makes it possible to:

• explain a particular material movement after the fact,
• distinguish human error from incorrect data,
• identify recurring exceptions,
• verify whether FIFO is followed in practice,
• and reconstruct state after an outage or an incorrect intervention.

An audit trail is not a substitute for a sound process. If a system merely records chaos in detail, the company has excellent documentation of the problem—not a solution to it.

The Most Expensive Questions Are Not Technical #

Before developing a FIFO solution, examining the physical flow of material matters more than discussing screens. The following questions are particularly useful:

• What exactly counts as one identifiable unit: an individual item, package, container, or batch?
• At which points in the process is scanning mandatory?
• Can material be returned, and where does it re-enter the sequence?
• Who may block material or approve an exception?
• What happens when a label is damaged, the network is unavailable, or a scanner fails?
• Which timestamp genuinely determines the order?
• How is a discrepancy between recorded and physical stock discovered?

The answers affect the data model, permissions, user interface, and workstation equipment. If these situations are discovered only during deployment, they are not minor additions. They often change the core system logic.

Reliable FIFO Keeps the Database and Reality Aligned #

FIFO is not a feature that can be completed with a single database query. It is an agreement between the physical arrangement of material, unambiguous identification, state transitions, permissions, and responses to deviations.

The most important question is therefore not, “Can the system sort records from the oldest?” It is, “How does the system know which material a person is actually using, and what will it do when it is the wrong one?”

Without a reliable answer, the company does not have FIFO. It has a database that believes it does.