Building a Web-Based Inventory Information System

An inventory information system is one of the most requested apps from clients: shops, warehouses, pharmacies, and workshops all need to know how much stock is left right now. The most common beginner mistake is storing stock as a single number on the product row and directly adding or subtracting from it. That looks simple, but the moment you have returns, corrections, or an audit, you lose the history and cannot explain why the number suddenly went wrong. This tutorial builds inventory the right way in Laravel 11 using the stock movement (stock ledger) pattern.

1. Designing the Data Model

We need three core entities: suppliers, products, and stock_movements. The key design decision: current stock is not a manually edited column, but the sum of all movements. Every item received is recorded as an in row and every item issued as an out row.

php artisan make:model Supplier -m
php artisan make:model Product -m
php artisan make:model StockMovement -m

The suppliers and products migrations

Schema::create('suppliers', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('phone')->nullable();
    $table->timestamps();
});

Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->string('sku')->unique();
    $table->string('name');
    $table->integer('price')->default(0);
    $table->integer('min_stock')->default(5); // low-stock threshold
    $table->timestamps();
});

The stock_movements migration

This is the heart of the system. The type column distinguishes in/out, qty is always positive, and reference holds the invoice or receipt number so every movement can be traced back to its source.

Schema::create('stock_movements', function (Blueprint $table) {
    $table->id();
    $table->foreignId('product_id')->constrained()->cascadeOnDelete();
    $table->foreignId('supplier_id')->nullable()->constrained();
    $table->enum('type', ['in', 'out']);
    $table->unsignedInteger('qty');
    $table->string('reference')->nullable();
    $table->text('note')->nullable();
    $table->timestamps();
});

php artisan migrate

2. Setting Up Models and Relations

On the Product model, we define the relation to movements and an accessor that computes running stock. The accessor makes $product->current_stock always accurate without storing a number that can go stale.

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

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

    public function getCurrentStockAttribute(): int
    {
        return (int) $this->movements()
            ->selectRaw("SUM(CASE WHEN type = 'in' THEN qty ELSE -qty END) as total")
            ->value('total');
    }

    public function getIsLowAttribute(): bool
    {
        return $this->current_stock <= $this->min_stock;
    }
}

The StockMovement model stays compact:

class StockMovement extends Model
{
    protected $fillable = ['product_id', 'supplier_id', 'type', 'qty', 'reference', 'note'];

    public function product()
    {
        return $this->belongsTo(Product::class);
    }
}

3. Recording Stock In and Out

Every stock change must go through a recorded movement. For items going out, we must first check whether stock is sufficient so it never goes negative. Wrap the operation in a database transaction to stay safe from race conditions.

use Illuminate\Support\Facades\DB;

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) {
        $product = Product::lockForUpdate()->findOrFail($data['product_id']);

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

        $product->movements()->create($data);
    });

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

Because stock is derived from movements, corrections are easy: a wrong entry is fixed with an opposite movement, and the history stays intact for auditing.

4. Low-Stock Alerts

The shop owner wants to know which items to reorder soon. Since stock is a SUM of movements, we filter at the query level so it stays fast even with thousands of products:

$lowStock = Product::query()
    ->withSum(['movements as stock' => function ($q) {
        $q->selectRaw("SUM(CASE WHEN type = 'in' THEN qty ELSE -qty END)");
    }], 'qty')
    ->get()
    ->filter(fn ($p) => $p->current_stock <= $p->min_stock);

For large datasets it is more efficient to compute the aggregate directly in the database with selectRaw and havingRaw:

$lowStock = Product::query()
    ->leftJoin('stock_movements', 'stock_movements.product_id', '=', 'products.id')
    ->selectRaw('products.*, COALESCE(SUM(CASE WHEN type = "in" THEN qty ELSE -qty END), 0) as stock')
    ->groupBy('products.id')
    ->havingRaw('stock <= products.min_stock')
    ->get();

5. A Simple Report

A per-period movement report just filters the stock_movements table by date, then summarizes total in and out:

$report = StockMovement::query()
    ->whereBetween('created_at', [$start, $end])
    ->selectRaw("type, SUM(qty) as total")
    ->groupBy('type')
    ->pluck('total', 'type');

// $report['in'] = total received, $report['out'] = total issued

Common Mistakes to Avoid

• Storing stock as a single directly-edited column: you lose history and risk silent errors. Use a movement ledger.
• Skipping transactions + lockForUpdate: two concurrent requests can drive stock negative.
• Storing negative qty for outgoing items: it is cleaner to use a type column and always keep qty positive.

With this stock movement pattern, your inventory app is accurate, easy to audit, and ready to grow into returns, warehouse transfers, or stock-taking features without reworking the data structure.