Building a Web-Based Inventory Information System

The owner asks how many bags of coffee are left. The system says 40, the shelf shows 25, and nobody can explain where the other 15 went. That is the classic symptom of an inventory app that stores stock as a single number on the products table and edits it directly.

It looks simple, but once returns, corrections, or audits enter the picture, the history is gone. This tutorial builds an auditable inventory system in Laravel 11 (PHP 8.2+) using the stock movement pattern, a ledger of every change. It suits retail shops, warehouses, pharmacies, and repair workshops alike. Every code sample below is plain Eloquent, so you can drop it into an existing project without new packages.

Three Ways to Store Stock

1. Data Model

Three core tables: suppliers, products, and stock_movements. Current stock is total in minus total out, never a hand-edited number.

Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->string('sku')->unique();
    $table->string('name');
    $table->unsignedBigInteger('price_cents')->default(0);
    $table->unsignedInteger('min_stock')->default(5);
    $table->timestamps();
});

Schema::create('stock_movements', function (Blueprint $table) {
    $table->id();
    $table->foreignId('product_id')->constrained()->restrictOnDelete();
    $table->foreignId('supplier_id')->nullable()->constrained();
    $table->foreignId('user_id')->nullable()->constrained();
    $table->enum('type', ['in', 'out']);
    $table->unsignedInteger('qty');
    $table->string('reference')->nullable(); // invoice, receipt, or count code
    $table->text('note')->nullable();
    $table->timestamps();

    $table->index(['product_id', 'type']);
});

These choices are deliberate. type separates in from out while qty stays positive, so there is no sign confusion. reference and user_id answer the two audit questions: which document caused this, and who recorded it. restrictOnDelete stops anyone deleting a product that has history; deactivate it instead. When you later change this schema, follow safe database migrations in Laravel.

2. Models

class Product extends Model
{
    protected $fillable = ['sku', 'name', 'price_cents', 'min_stock'];

    public function movements(): HasMany
    {
        return $this->hasMany(StockMovement::class);
    }

    public function currentStock(): int
    {
        $in  = (int) $this->movements()->where('type', 'in')->sum('qty');
        $out = (int) $this->movements()->where('type', 'out')->sum('qty');

        return $in - $out;
    }
}

currentStock() is a plain method rather than an accessor on purpose. Accessors get called repeatedly inside Blade loops without anyone noticing, and each call is a new query.

3. Recording Stock In and Out

public function store(Request $request)
{
    $data = $request->validate([
        'product_id' => 'required|exists:products,id',
        'type'       => 'required|in:in,out',
        'qty'        => 'required|integer|min:1',
        'reference'  => 'nullable|string|max:100',
    ]);

    DB::transaction(function () use ($data, $request) {
        $product = Product::lockForUpdate()->findOrFail($data['product_id']);

        if ($data['type'] === 'out' && $product->currentStock() < $data['qty']) {
            abort(422, 'Insufficient stock. Remaining: ' . $product->currentStock());
        }

        $product->movements()->create($data + ['user_id' => $request->user()?->id]);
    });

    return back()->with('success', 'Stock movement recorded.');
}

The transaction plus row lock stops two cashiers selling the last unit at the same moment and pushing stock below zero. Mistakes are fixed by posting an opposite movement with a note, never by deleting rows.

4. Low-Stock Alerts Without N+1

$products = Product::query()
    ->withSum(['movements as qty_in' => fn ($q) => $q->where('type', 'in')], 'qty')
    ->withSum(['movements as qty_out' => fn ($q) => $q->where('type', 'out')], 'qty')
    ->get()
    ->each(fn ($p) => $p->stock = (int) $p->qty_in - (int) $p->qty_out);

$lowStock = $products->filter(fn ($p) => $p->stock <= $p->min_stock);

For thousands of products, filter in the database instead:

$stockExpr = "COALESCE(SUM(CASE WHEN m.type = 'in' THEN m.qty ELSE 0 END), 0)
            - COALESCE(SUM(CASE WHEN m.type = 'out' THEN m.qty ELSE 0 END), 0)";

$lowStock = Product::query()
    ->leftJoin('stock_movements as m', 'm.product_id', '=', 'products.id')
    ->select('products.id', 'products.sku', 'products.name', 'products.min_stock')
    ->selectRaw("$stockExpr AS stock")
    ->groupBy('products.id', 'products.sku', 'products.name', 'products.min_stock')
    ->havingRaw("$stockExpr <= products.min_stock")
    ->get();

Repeating the expression in havingRaw keeps the query portable across MySQL and PostgreSQL. More tuning tips are in optimizing Laravel Eloquent query performance.

5. Physical Stock Counts

1. Pause transactions for the location being counted, or record the count start time.
2. Staff enter the physical quantity per product.
3. The system computes the difference: physical minus system stock.
4. Positive differences post as in, negative as out, with a reference such as COUNT-2026-10.
5. The owner reviews the largest differences before closing the count.

$diff = $physicalQty - $product->currentStock();

if ($diff !== 0) {
    $product->movements()->create([
        'type'      => $diff > 0 ? 'in' : 'out',
        'qty'       => abs($diff),
        'reference' => 'COUNT-' . now()->format('Y-m'),
        'note'      => 'Stock count adjustment',
        'user_id'   => auth()->id(),
    ]);
}

6. When to Add a Cached Stock Column

If a checkout screen must show stock for thousands of products instantly, add a stock column to products as a cache. Update it only inside the same transaction that creates the movement, using increment or decrement, and run a nightly scheduled command that compares the cache with the ledger sum and reports any mismatch. The ledger stays the source of truth.

7. Post Movements From Sales Automatically

Staff should rarely type outgoing movements by hand. When a sale is saved in the POS, create one out movement per line item inside the same database transaction as the sale, with the invoice number as the reference. If the sale is voided, post matching in movements referencing the void. This keeps the ledger complete without extra clicks and makes every unit traceable to a document.

8. Test the Stock Rules

Business rules break more often than screens. A handful of feature tests with Pest or PHPUnit form a safety net: an outgoing movement larger than stock must return 422, ten in and four out must leave six, and a count adjustment with zero difference must not create a movement. Run them whenever you add returns or warehouse transfers.

Common Mistakes

• Editing a stock column directly erases history.
• Skipping the transaction and lock allows negative stock.
• Deleting wrong movements instead of posting corrections breaks the audit trail.
• Computing stock inside Blade loops causes N+1 queries.

Go-Live Checklist

• Opening balances entered as in movements with an "OPENING" reference.
• Over-selling tested and rejected.
• Every movement stores a user and a document number.
• A written monthly stock count procedure.
• Delete rights for products and movements limited to admins or disabled.

From here, purchase returns, multi-warehouse transfers (add warehouse_id), and POS integration fit without restructuring. To see how inventory ties into sales, read what a POS system is and how it works.