FIFO Inventory in Laravel: Why Reducing Product Stock Is Not Enough (2026)

Most inventory systems begin with a simple idea:

A product has a quantity. When a sale is completed, reduce that quantity.

That approach works for a small catalogue where every unit has the same cost and expiry date. It becomes unreliable when a business purchases the same product several times at different prices.

Imagine this purchase history:

The product now has 30 units in stock. But the number 30 does not tell you which units should be sold first, which cost belongs to the next sale, or how much profit the business actually made.

That is the reason FIFO inventory matters.

FIFO means First In, First Out. The oldest available stock is consumed before newer stock. To implement FIFO correctly in Laravel, you must preserve the individual stock layers instead of storing only one final quantity.

This guide explains the design in simple terms and shows a practical foundation for implementing it with Laravel and MySQL.

The short answer

Reducing products.stock is not enough for FIFO because a single stock number loses the history of purchases.

A reliable FIFO system should track:

• each purchase batch or stock layer;

• the quantity received in that layer;

• the quantity still available;

• the unit cost of that layer;

• optional landed costs such as shipping, VAT, and discounts;

• expiry information where applicable;

• every stock-in and stock-out movement;

• the exact layers consumed by each sale.

The product's current quantity should be treated as a calculated balance or a carefully maintained summary—not as the complete inventory record.

What is FIFO inventory?

FIFO inventory assumes that the first stock received is the first stock sold.

Suppose a store receives the following batches:

If the store sells 6 units, the system should consume:

• 4 units from Layer B;

• 2 units from Layer C.

The FIFO cost of the sale is:

(4 × Rs. 120) + (2 × Rs. 140) = Rs. 760

The remaining inventory is:

• Layer B: 0 units;

• Layer C: 8 units.

If the selling price is Rs. 200 per unit, the merchandise revenue is Rs. 1,200 and the gross profit before other adjustments is Rs. 440.

Without the layers, the application cannot calculate this cost with confidence.

Why a single stock column fails

A common table looks like this:

products
--------
id
name
stock
unit_price

When a purchase is received, the application adds to stock. When a sale is created, it subtracts from stock.

This answers one question:

How many units are currently available?

It does not answer these questions:

• Which purchase batch supplied this sale?

• What was the actual cost of the sold units?

• Which batch should be used next?

• Is the oldest batch expired?

• How much landed cost belongs to each unit?

• Which stock movement changed the balance?

• Can a sale be reversed without destroying the original allocation?

• What was the profit for a particular sale or date range?

Once purchase history is overwritten or ignored, the missing information cannot be reconstructed reliably from the final quantity alone.

A better mental model: stock is a collection of layers

Instead of thinking like this:

Product A = 30 units

Think like this:

Product A
├── Batch A: 10 units at Rs. 100
├── Batch B: 10 units at Rs. 120
└── Batch C: 10 units at Rs. 140

After selling 6 units, the system should preserve the remaining layers:

Product A
├── Batch B: 4 units at Rs. 120
└── Batch C: 10 units at Rs. 140

The inventory quantity is now 14, but the application still knows the origin and cost of every remaining unit.

Recommended database design

The exact table names can differ between projects. The important part is the responsibility of each table.

1. Products table

This contains product identity and stable product information.

products
--------
id
name
sku
unit_price
created_at
updated_at

Do not use this table alone as the FIFO source of truth.

2. Inventory layers table

Each received batch creates one inventory layer.

inventory_layers
----------------
id
owner_id
product_id
variation_key
source_type
source_id
received_at
expiry_date
quantity_received
quantity_remaining
unit_cost
landed_unit_cost
status
created_at
updated_at

Useful meanings:

• owner_id: supports admin, store, seller, or franchise-level stock isolation;

• product_id: identifies the product;

• variation_key: distinguishes variants such as size or color;

• source_type and source_id: link the layer to a purchase, transfer, or adjustment;

• received_at: determines FIFO order;

• quantity_received: preserves the original quantity;

• quantity_remaining: records what is still available;

• unit_cost: records the base purchase cost;

• landed_unit_cost: records the cost after applicable shipping, VAT, discount, or other allocation;

• status: can be available, exhausted, expired, or cancelled.

3. Inventory movements table

The movement table is the audit trail.

inventory_movements
-------------------
id
owner_id
product_id
variation_key
layer_id
movement_type
reference_type
reference_id
quantity
quantity_before
quantity_after
unit_cost
expiry_date
created_by
created_at

Typical movement types include:

• purchase_received;

• sale;

• sale_reversal;

• purchase_return;

• customer_return;

• transfer_out;

• transfer_in;

• adjustment_in;

• adjustment_out;

• opening_balance.

The movement table explains what happened. The layer table explains what remains available.

4. Sale-layer allocations table

When one sale consumes multiple layers, store those allocations explicitly.

sale_layer_allocations
----------------------
id
order_id
order_detail_id
layer_id
quantity
unit_cost
total_cost
created_at

For a 6-unit sale that consumes 4 units from Layer B and 2 units from Layer C, this table stores two rows:

This makes profit reports, returns, audits, and corrections much easier.

FIFO ordering rules

The oldest layer should normally be selected first using deterministic ordering:

$layers = InventoryLayer::query()
    ->where('owner_id', $ownerId)
    ->where('product_id', $productId)
    ->where('variation_key', $variationKey)
    ->where('quantity_remaining', '>', 0)
    ->where(function ($query) use ($saleDate) {
        $query->whereNull('expiry_date')
            ->orWhereDate('expiry_date', '>=', $saleDate);
    })
    ->orderBy('received_at')
    ->orderBy('id')
    ->lockForUpdate()
    ->get();

The second ordering rule, id, is important. If two layers have the same received_at value, the database still needs a stable tie-breaker.

The exact expiry rule depends on the business:

• strict FIFO may select the oldest layer regardless of expiry and block expired stock separately;

• FEFO, or First Expired, First Out, may select the earliest expiry date first;

• a mixed policy may prefer unexpired stock while preserving the original cost order.

Choose the policy intentionally. Do not silently change FIFO into FEFO by adding an expiry sort without documenting it.

A practical FIFO allocation service in Laravel

The allocation operation should be kept in a service class rather than scattered across controllers, POS code, APIs, and background jobs.

<?php

namespace App\Services;

use App\Models\InventoryLayer;
use App\Models\SaleLayerAllocation;
use Illuminate\Support\Facades\DB;
use RuntimeException;

class FifoInventoryAllocator
{
    public function allocate(
        int $ownerId,
        int $productId,
        ?string $variationKey,
        int $quantity,
        int $orderId,
        int $orderDetailId,
        ?string $saleDate = null,
    ): int {
        if ($quantity < 1) {
            throw new RuntimeException('Allocation quantity must be greater than zero.');
        }

        return DB::transaction(function () use (
            $ownerId,
            $productId,
            $variationKey,
            $quantity,
            $orderId,
            $orderDetailId,
            $saleDate,
        ) {
            $remainingToAllocate = $quantity;
            $allocatedQuantity = 0;

            $layers = InventoryLayer::query()
                ->where('owner_id', $ownerId)
                ->where('product_id', $productId)
                ->where('variation_key', $variationKey)
                ->where('quantity_remaining', '>', 0)
                ->when($saleDate, function ($query) use ($saleDate) {
                    $query->where(function ($query) use ($saleDate) {
                        $query->whereNull('expiry_date')
                            ->orWhereDate('expiry_date', '>=', $saleDate);
                    });
                })
                ->orderBy('received_at')
                ->orderBy('id')
                ->lockForUpdate()
                ->get();

            foreach ($layers as $layer) {
                if ($remainingToAllocate === 0) {
                    break;
                }

                $take = min(
                    $remainingToAllocate,
                    (int) $layer->quantity_remaining
                );

                $before = (int) $layer->quantity_remaining;
                $after = $before - $take;

                $layer->update([
                    'quantity_remaining' => $after,
                    'status' => $after === 0 ? 'exhausted' : 'available',
                ]);

                SaleLayerAllocation::create([
                    'order_id' => $orderId,
                    'order_detail_id' => $orderDetailId,
                    'layer_id' => $layer->id,
                    'quantity' => $take,
                    'unit_cost' => $layer->landed_unit_cost ?? $layer->unit_cost,
                    'total_cost' => $take * ($layer->landed_unit_cost ?? $layer->unit_cost),
                ]);

                $remainingToAllocate -= $take;
                $allocatedQuantity += $take;
            }

            if ($remainingToAllocate > 0) {
                throw new RuntimeException('Insufficient available stock for FIFO allocation.');
            }

            return $allocatedQuantity;
        });
    }
}

This example assumes integer quantities. If a product can be sold in decimal quantities, use a suitable decimal column and a consistent precision strategy instead of casting quantities to integers.

Why the transaction matters

The allocation process is not one database action. It is a sequence:

1. find available layers;

2. reserve or lock them;

3. calculate how much to take from each layer;

4. reduce each layer's remaining quantity;

5. create sale-layer allocation records;

6. create movement records;

7. finish the order.

If step 5 fails after step 4 has succeeded, the inventory must not remain partially reduced. A database transaction allows the complete operation to commit together or roll back together.

Laravel supports DB::transaction() for this purpose. Its query builder also provides lockForUpdate() for pessimistic locking. Laravel's documentation recommends using pessimistic locks inside a transaction so the selected data remains protected until the operation completes. Laravel Query Builder documentation

Why lockForUpdate() matters

Without a row lock, two checkout requests can read the same layer at nearly the same time.

Example:

Layer A has 5 units.

Checkout 1 reads 5.
Checkout 2 reads 5.

Checkout 1 sells 4.
Checkout 2 sells 4.

The system has now promised 8 units even though only 5 existed.

With lockForUpdate() inside a transaction:

1. Checkout 1 locks the relevant layer rows.

2. Checkout 2 waits until Checkout 1 commits or rolls back.

3. Checkout 2 reads the updated remaining quantity.

4. The application rejects the second sale if the remaining stock is insufficient.

This is a database consistency problem, not only a Laravel validation problem. Application-level checks such as if ($stock >= $requestedQuantity) are not enough when multiple requests run concurrently.

MySQL's InnoDB documentation describes SELECT ... FOR UPDATE as a locking read that prevents other transactions from updating or deleting the locked rows until the transaction releases them. MySQL locking reads documentation

The difference between available quantity and allocated quantity

A useful system distinguishes at least these concepts:

quantity_received
quantity_reserved
quantity_sold
quantity_remaining

For a simple checkout flow, quantity_remaining may be enough. For an online store with carts, payment delays, and order cancellation, reservations become important.

For example:

Received: 10
Reserved: 2
Sold: 3
Available for new orders: 5

Do not reduce the permanent FIFO layer at the moment an unconfirmed cart is created unless the business has a reservation policy. Otherwise abandoned carts may make stock appear unavailable forever.

A common approach is:

• reserve stock when an order is confirmed or payment begins;

• convert the reservation into a sale allocation after successful payment;

• release the reservation when the order expires or is cancelled;

• keep the final sale allocation immutable after completion.

Purchase receiving flow

When a purchase is received, create a new layer instead of merely incrementing a product quantity.

$layer = InventoryLayer::create([
    'owner_id' => $ownerId,
    'product_id' => $item->product_id,
    'variation_key' => $item->variation_key,
    'source_type' => 'purchase',
    'source_id' => $purchase->id,
    'received_at' => $purchase->received_at ?? now(),
    'expiry_date' => $item->expiry_date,
    'quantity_received' => $item->quantity,
    'quantity_remaining' => $item->quantity,
    'unit_cost' => $item->unit_price,
    'landed_unit_cost' => $item->landed_unit_cost,
    'status' => 'available',
]);

The layer's cost should represent the cost policy used by the business. If shipping, VAT, or purchase-level discounts are part of inventory cost, allocate those values before the layer is created.

For example:

Product subtotal: Rs. 500
VAT:              Rs. 50
Shipping:         Rs. 100
Discount:         Rs. 10
Grand total:      Rs. 640
Quantity:         10
Landed unit cost: Rs. 64

If the system stores only the original unit price of Rs. 50, profit reports will overstate profit by ignoring the cost of bringing the product into inventory.

Sale flow

A robust sale flow should generally follow this order:

1. validate the order and line quantities;

2. start a database transaction;

3. lock the relevant inventory layers;

4. allocate each line using FIFO;

5. write sale-layer allocation rows;

6. write inventory movement rows;

7. update order and payment state;

8. commit the transaction.

The exact order can vary, but the important inventory changes should happen inside the same transaction as the sale decision.

Avoid putting a second, different stock-decrement algorithm in the mobile API, POS controller, admin controller, and queued order processor. All entry points should call the same inventory service.

Inventory movements should be append-oriented

An audit trail becomes difficult to trust when old movement rows are overwritten. A safer approach is to append a new movement for every correction or reversal.

For example, if a sale of 3 units is cancelled:

Original movement: sale, quantity -3
Reversal movement: sale_reversal, quantity +3

You may restore the original layer quantities, but preserve the reversal record and the relationship to the original sale. This allows an administrator to answer:

Why did this layer increase again on March 12?

The answer should be visible from the records, not hidden in an overwritten quantity.

Handling returns correctly

Customer returns are not always identical to new purchases.

Before adding a returned item back to sellable inventory, decide:

• Is the item still in good condition?

• Is the original batch known?

• Has the product expired?

• Does the item require inspection or quarantine?

• Should it return to the original layer or create a new layer?

If the original sale allocation is known and the item is resalable, a return can restore quantity to the original layer. If the item is not traceable or the business treats returns as a new receipt, create a separate return layer with its own source reference.

Do not silently add every returned unit to the product's general stock balance. That loses the cost and quality context again.

FIFO versus FEFO

FIFO and FEFO are related but different:

If expiry is more important than receipt date, FEFO may be the better business rule. The database design can support both, but the allocation query must make the policy explicit.

For FEFO, the ordering might be:

->orderByRaw('expiry_date IS NULL')
->orderBy('expiry_date')
->orderBy('received_at')
->orderBy('id')

Test the ordering with real examples before applying it to production inventory. A null expiry date should not accidentally become the first selected layer unless that is intentional.

Product variants need their own inventory identity

A product with variants should not share one FIFO pool unless every variant is physically interchangeable.

For example:

T-shirt / Red / Small
T-shirt / Red / Medium
T-shirt / Blue / Small

Each variation should have a normalized identity such as:

red-small
red-medium
blue-small

Use the same normalized variation key when receiving purchases, searching stock, allocating sales, transferring stock, and calculating current balances. If one part of the system stores Small-Red and another stores red-small, the application may create duplicate inventory pools and show incorrect stock.

Owner and store isolation

Multi-store systems need more than product_id in their inventory queries.

If the same product exists for an admin warehouse and a franchise store, every read and write should include the correct ownership or location boundary:

$query->where('owner_id', $authenticatedOwnerId);

Depending on the application, you may need:

• owner_id;

• warehouse_id;

• store_id;

• seller_id;

• stock_owner_user_id.

The same boundary must be applied to:

• available-layer queries;

• current balance reports;

• stock searches;

• transfers;

• sales allocations;

• low-stock reports;

• expiry reports.

An otherwise correct FIFO algorithm can still produce wrong results if it consumes another store's layers.

Useful database indexes

FIFO queries repeatedly filter by ownership, product, variation, remaining quantity, and date. Add indexes that match the access pattern.

For example:

Schema::table('inventory_layers', function (Blueprint $table) {
    $table->index([
        'owner_id',
        'product_id',
        'variation_key',
        'quantity_remaining',
        'received_at',
    ], 'inventory_layers_fifo_lookup');
});

The ideal index depends on the database engine, query shape, and data volume. Inspect the generated query and use EXPLAIN before adding large indexes to a production table.

MySQL notes that locking reads can lock the index records scanned by the query. A suitable index helps the database scan a narrower set of records instead of unnecessarily locking a large portion of the table. MySQL InnoDB locking documentation

Common mistakes in Laravel FIFO implementations

Mistake 1: storing only the latest purchase price

The latest purchase price is useful for display, but it is not the cost of every unit currently in stock. Older units may have a different cost.

Mistake 2: using the selling price as the stock cost

Selling price and inventory cost serve different purposes. A discount, flash deal, tax, or customer-specific price should not replace the layer's acquisition cost.

Mistake 3: calculating profit from today's product price

Historical profit should use the cost attached to the sold layers, not the product's current price or current purchase price.

Mistake 4: checking stock before opening a transaction

The check and allocation can become stale between two concurrent requests. Perform the protected read and update inside the same transaction.

Mistake 5: decrementing stock in multiple places

If the controller, model event, observer, and queue job all reduce stock, one sale may be deducted more than once. Establish one ownership point for allocation.

Mistake 6: deleting exhausted layers

An exhausted layer still explains the history of previous sales. Mark it exhausted instead of deleting it.

Mistake 7: ignoring failed or partially completed transactions

If an exception occurs after reducing a layer but before creating the sale allocation, the transaction must roll back. Test this failure path deliberately.

Mistake 8: treating a cart as a completed sale

A cart may be abandoned. Decide whether carts reserve stock, and define when those reservations expire.

Mistake 9: mixing base cost and landed cost

If reports sometimes use base purchase cost and sometimes use landed cost, users will see inconsistent profit. Store the cost basis clearly and label estimated or incomplete values.

Testing strategy

FIFO should be tested as an inventory accounting rule, not only as a controller feature.

At minimum, test these cases:

1. one layer, one sale;

2. one layer, partial sale;

3. one sale consuming multiple layers;

4. exact exhaustion of a layer;

5. multiple layers with equal receipt dates;

6. insufficient stock;

7. two concurrent sales for the same product;

8. sale reversal;

9. customer return;

10. expired layer handling;

11. product variants;

12. separate store or owner isolation;

13. landed cost allocation;

14. failed transaction rollback;

15. duplicate sale request or retry.

A simple feature test can verify the allocation order:

public function test_sale_consumes_oldest_layers_first(): void
{
    $oldLayer = InventoryLayer::factory()->create([
        'product_id' => $this->product->id,
        'quantity_remaining' => 5,
        'received_at' => '2026-01-01 10:00:00',
        'unit_cost' => 100,
    ]);

    $newLayer = InventoryLayer::factory()->create([
        'product_id' => $this->product->id,
        'quantity_remaining' => 5,
        'received_at' => '2026-01-02 10:00:00',
        'unit_cost' => 120,
    ]);

    $this->fifoAllocator->allocate(
        ownerId: $this->owner->id,
        productId: $this->product->id,
        variationKey: null,
        quantity: 7,
        orderId: $this->order->id,
        orderDetailId: $this->orderDetail->id,
    );

    $this->assertDatabaseHas('sale_layer_allocations', [
        'layer_id' => $oldLayer->id,
        'quantity' => 5,
    ]);

    $this->assertDatabaseHas('sale_layer_allocations', [
        'layer_id' => $newLayer->id,
        'quantity' => 2,
    ]);
}

For concurrency tests, use separate database connections or parallel test processes. A sequential test cannot prove that two simultaneous requests are correctly serialized.

A safe migration path from a simple stock column

Many existing Laravel applications already have products and stock tables. A full rewrite is not always necessary.

Use an incremental migration:

Step 1: create the new layer and movement tables

Do not remove the old stock field immediately. First create the new structure and indexes.

Step 2: convert current stock into opening layers

For each product and owner, create an opening_balance layer for the current quantity. Mark its cost as known, estimated, or unresolved according to the available historical data.

Step 3: write new purchases as layers

From this point, every received purchase should create a layer.

Step 4: route new sales through the allocator

Use the same service for web checkout, POS, APIs, and administrative sales.

Step 5: record adjustments as movements

Do not directly edit the layer quantity from unrelated screens. Create an adjustment movement and update the layer inside a transaction.

Step 6: reconcile the summary balance

Compare the calculated sum of layer balances with the existing product or inventory balance:

SELECT
    product_id,
    SUM(quantity_remaining) AS layer_quantity
FROM inventory_layers
WHERE status IN ('available', 'expired')
GROUP BY product_id;

Investigate differences before changing the summary field.

Step 7: make the new source authoritative

After the new flow is stable, treat layers and movements as the inventory source of truth. Keep a summary balance only for performance if necessary, and update it from the same transaction.

Reporting with FIFO

FIFO makes several reports more trustworthy:

• current stock by product and variation;

• stock valuation;

• cost of goods sold;

• gross profit;

• remaining quantity by purchase batch;

• expiring stock;

• purchase cost history;

• store transfer history;

• unresolved or estimated cost records.

For a sale line, calculate cost from its allocations:

$cost = $orderDetail->layerAllocations()->sum('total_cost');
$revenue = $orderDetail->quantity * $orderDetail->selling_price;
$grossProfit = $revenue - $cost;

If some sold quantity has no matching allocation, do not silently show a precise profit number. Mark the result as estimated, partially costed, or unavailable.

That distinction is important for business users. A report that admits missing cost data is more useful than a report that confidently displays an incorrect profit.

When a summary stock table is still useful

A summary table is not inherently wrong. It is useful for fast reads:

inventory_balances
------------------
owner_id
product_id
variation_key
quantity_after
updated_at

The problem is treating the summary as the only record.

A good architecture can use:

• inventory layers for FIFO order and cost;

• movement records for audit history;

• a balance table for fast current-stock reads;

• database transactions to update all related records together.

The summary should be derived from, or kept consistent with, the detailed records.

A practical implementation checklist

Before calling a Laravel inventory system FIFO-ready, verify the following:

• Purchases create distinct inventory layers.

• Each layer stores received quantity and remaining quantity.

• Each layer stores a clear cost basis.

• FIFO ordering has a deterministic tie-breaker.

• Sale allocation happens inside a database transaction.

• Relevant rows are locked before allocation.

• Sale-layer allocations are persisted.

• Inventory movements are auditable.

• Returns and cancellations have explicit reversal rules.

• Product variants use normalized inventory identities.

• Owner, store, or warehouse isolation is applied consistently.

• Expiry policy is documented as FIFO or FEFO.

• Failed transactions roll back all inventory changes.

• Concurrency and duplicate requests are tested.

• Reports distinguish fully costed, partially costed, and unresolved sales.

• Exhausted layers remain available for historical reporting.

Final thoughts

Inventory is not just a number on a product row. It is a history of receipts, costs, locations, expiry dates, adjustments, transfers, and sales.

Reducing a quantity may make a screen look correct for a moment, but it does not preserve the information needed for reliable FIFO costing and profit reporting.

A durable Laravel inventory design separates three responsibilities:

1. Layers preserve what stock remains and where it came from.

2. Movements explain how inventory changed.

3. Allocations connect each sale to the exact cost layers it consumed.

Once those relationships are explicit, the rest of the system becomes easier to reason about. Current stock, FIFO cost, gross profit, expiry reporting, returns, and audits can all use the same reliable foundation.

The main lesson is simple:

If your application needs accurate inventory cost, do not store only how much stock remains. Store the layers that created that stock.

Frequently asked questions

What does FIFO mean in inventory management?

FIFO means First In, First Out. The oldest available inventory layer is consumed before newer layers.

Is reducing product stock enough for FIFO in Laravel?

No. A single stock quantity does not preserve purchase batches, costs, expiry dates, or the exact source of each sale. FIFO requires batch or layer-level records.

Should FIFO be implemented in a Laravel controller?

The controller may start the business operation, but the allocation logic should live in a reusable service. This prevents web, POS, API, and queue flows from using different stock rules.

Why should lockForUpdate() be used for FIFO stock allocation?

It protects selected inventory rows during a transaction so concurrent sales cannot allocate the same remaining units at the same time.

What is the difference between FIFO and FEFO?

FIFO selects the oldest received stock first. FEFO selects stock with the earliest expiry date first. FEFO is often better for expiry-sensitive products.

How should landed cost be used in FIFO?

If shipping, VAT, discounts, or other purchase costs belong to inventory, allocate them to the received layer and use the resulting landed unit cost for cost and profit reporting.

Should exhausted FIFO layers be deleted?

No. Mark them as exhausted and preserve them for audit history, sale allocation, and reporting.

Can a Laravel inventory system migrate gradually to FIFO?

Yes. Create opening-balance layers for current stock, route new purchases and sales through the layer system, reconcile differences, and make the detailed records authoritative after testing.