Ask any ERP team what their most dangerous line of code is, and many of them will point at something like this:

$product->stock -= $line->quantity; $product->save();

It looks harmless. It is also the reason so many small and mid-sized companies have a warehouse that says 42 while the shelf says 37, and nobody can explain where the other five went.

In this post I want to show a pattern I now use in every inventory module: stock is not a number you update, it is a ledger you append to. The current quantity is just a cached sum of that ledger. The code is Laravel, but the idea works in any stack.

The problem with a quantity column

A single stock column on the products table fails in three predictable ways.

1. It has no memory. When the number is wrong, you have nothing to audit. Was it the delivery note from Tuesday? The return that was posted twice? A manual correction someone did "just to fix it"? The column only knows the latest value.

2. It races. Two requests read stock = 10, both subtract 8, both save 2. You just sold 16 units of something you had 10 of. Read-modify-write in PHP is a classic lost update.

3. It cannot answer time questions. "What was our stock at the end of September?" is a normal question from finance. With a single column the honest answer is "we don't know", or a nightly snapshot job that someone eventually forgets to fix.

Accounting solved this a few centuries ago: you never overwrite a balance, you post entries, and the balance is the sum of entries. Inventory deserves the same treatment.

Step 1: The movements table (the source of truth)

Every change in stock becomes one row: a receipt, a delivery, a return, a transfer, an inventory count adjustment.

use Illuminate\Database\Migrations\Migration; use Illuminate\Database\Schema\Blueprint; use Illuminate\Support\Facades\Schema; return new class extends Migration { public function up(): void { Schema::create('stock_movements', function (Blueprint $table) { $table->id(); $table->foreignId('product_id')->constrained(); $table->foreignId('warehouse_id')->constrained(); $table->decimal('quantity', 18, 3); // signed: + in, - out $table->string('type', 32); // receipt, issue, transfer_in, ... $table->string('source_type', 64); // goods_receipt, delivery, count, ... $table->unsignedBigInteger('source_id'); $table->unsignedInteger('line_no'); $table->dateTime('occurred_at'); // business time, not insert time $table->foreignId('created_by')->nullable()->constrained('users'); $table->timestamps(); // One document line can only hit one warehouse once. $table->unique(['source_type', 'source_id', 'line_no', 'warehouse_id'], 'stock_movements_source_unique'); $table->index(['product_id', 'warehouse_id', 'occurred_at']); }); Schema::create('stock_balances', function (Blueprint $table) { $table->foreignId('product_id')->constrained(); $table->foreignId('warehouse_id')->constrained(); $table->decimal('quantity', 18, 3)->default(0); $table->timestamps(); $table->primary(['product_id', 'warehouse_id']); }); } };

Two details matter more than they look:

  • The unique index is your idempotency key. If a queue job or a nervous user posts the same delivery note twice, the second insert fails instead of silently doubling the movement.
  • occurred_at is business time. A goods receipt typed in on Monday for a truck that arrived on Friday belongs to Friday. created_at stays as the audit trail of when it was entered.

stock_balances is not the truth. It is a cache that makes "how many do we have right now?" a primary-key lookup instead of a SUM() over millions of rows.

Step 2: Make movements append-only

If people can edit or delete movements, you are back to a column with extra steps. Enforce it at the model level:

namespace App\Models; use Illuminate\Database\Eloquent\Model; use LogicException; class StockMovement extends Model { protected $guarded = []; protected function casts(): array { return [ 'quantity' => 'decimal:3', 'occurred_at' => 'immutable_datetime', ]; } protected static function booted(): void { static::updating(fn () => throw new LogicException('Stock movements are append-only. Post a correction instead.')); static::deleting(fn () => throw new LogicException('Stock movements are append-only. Post a reversal instead.')); } }

This does not stop someone with a SQL client, but it stops the most common source of damage: well-meaning application code. If you want a database-level guarantee, revoke UPDATE and DELETE on the table for the application's DB user.

Mistakes are fixed the way an accountant fixes them: a reversal (the same lines with the opposite sign, pointing at the original document) and, if needed, a new correct posting. The history stays complete.

Step 3: One service that posts documents

All stock changes go through one class. Controllers, jobs and imports never touch stock_balances directly.

namespace App\Inventory; use Illuminate\Support\Facades\DB; use RuntimeException; final class InsufficientStock extends RuntimeException {} final class StockLedger { /** * @param array<int, array{product_id:int, warehouse_id:int, quantity:string, type:string, line_no:int}> $lines */ public function post(string $sourceType, int $sourceId, array $lines, \DateTimeInterface $occurredAt, bool $allowNegative = false): void { // Always touch rows in the same order to avoid deadlocks between documents. usort($lines, fn ($a, $b) => [$a['product_id'], $a['warehouse_id']] <=> [$b['product_id'], $b['warehouse_id']]); DB::transaction(function () use ($sourceType, $sourceId, $lines, $occurredAt, $allowNegative) { foreach ($lines as $line) { if (! is_numeric($line['quantity']) || (float) $line['quantity'] == 0.0) { throw new \InvalidArgumentException("Line {$line['line_no']} needs a non-zero numeric quantity."); } DB::table('stock_movements')->insert([ 'product_id' => $line['product_id'], 'warehouse_id' => $line['warehouse_id'], 'quantity' => $line['quantity'], 'type' => $line['type'], 'source_type' => $sourceType, 'source_id' => $sourceId, 'line_no' => $line['line_no'], 'occurred_at' => $occurredAt, 'created_by' => auth()->id(), 'created_at' => now(), 'updated_at' => now(), ]); // Make sure the balance row exists before we update it. DB::table('stock_balances')->insertOrIgnore([ 'product_id' => $line['product_id'], 'warehouse_id' => $line['warehouse_id'], 'quantity' => 0, 'created_at' => now(), 'updated_at' => now(), ]); // Let the database do the arithmetic and the check in one atomic statement. $query = DB::table('stock_balances') ->where('product_id', $line['product_id']) ->where('warehouse_id', $line['warehouse_id']); if (! $allowNegative) { $query->whereRaw('quantity + ? >= 0', [$line['quantity']]); } // increment() accepts negative amounts and returns the number of affected rows. $affected = $query->increment('quantity', $line['quantity'], ['updated_at' => now()]); if ($affected === 0) { throw new InsufficientStock("Not enough stock for product {$line['product_id']} in warehouse {$line['warehouse_id']}."); } } }, attempts: 3); } }

Why it is written this way:

  • increment() compiles to UPDATE ... SET quantity = quantity + 12.500, so the arithmetic runs in the database. No PHP read-modify-write, so no lost updates. The row lock taken by the UPDATE serializes concurrent postings for the same product and warehouse until the transaction commits.
  • The negative-stock check is part of the same statement. If the condition fails, zero rows are affected, we throw, and DB::transaction() rolls back the movements already inserted for this document. A document posts completely or not at all.
  • attempts: 3 tells Laravel to retry the closure if the database reports a deadlock. Sorting the lines makes deadlocks rare; retrying makes the rare ones harmless.
  • Quantities are strings. Pass '12.500', not 12.5. Floats and stock do not mix (more on that below).

If you need to read a balance and make a decision in PHP before writing (for example, reserving stock across several warehouses), use lockForUpdate() inside the transaction, again in a consistent order.

A transfer is simply one document with two lines:

app(StockLedger::class)->post('transfer', $transfer->id, [ ['product_id' => 10, 'warehouse_id' => 1, 'quantity' => '-30', 'type' => 'transfer_out', 'line_no' => 1], ['product_id' => 10, 'warehouse_id' => 2, 'quantity' => '30', 'type' => 'transfer_in', 'line_no' => 1], ], now());

Both warehouses change in the same transaction, so stock is never "in the air".

Step 4: Answer time questions for free

Because every movement has occurred_at, historical stock is a query, not a project:

$stockAtMonthEnd = DB::table('stock_movements') ->where('product_id', $productId) ->where('warehouse_id', $warehouseId) ->where('occurred_at', '<', '2026-10-01 00:00:00') ->sum('quantity');

The same table gives you a stock card per product (running list of movements with their source documents), turnover reports and "who changed this and why" in one place. When the table grows into the millions, add monthly closing snapshots and sum only the movements after the last snapshot.

Step 5: Reconcile the cache

A cache can drift: a manual SQL fix, a bug in an old import, a restore from backup. A scheduled check catches it early:

$drift = DB::table('stock_balances as b') ->leftJoin('stock_movements as m', function ($join) { $join->on('m.product_id', '=', 'b.product_id') ->on('m.warehouse_id', '=', 'b.warehouse_id'); }) ->select('b.product_id', 'b.warehouse_id', 'b.quantity', DB::raw('COALESCE(SUM(m.quantity), 0) as ledger_quantity')) ->groupBy('b.product_id', 'b.warehouse_id', 'b.quantity') ->havingRaw('b.quantity <> COALESCE(SUM(m.quantity), 0)') ->get();

If $drift is not empty, alert someone. Because the ledger is the truth, the fix is always the same: recalculate the balance from the movements.

Common pitfalls

Floats. 0.1 + 0.2 is not 0.3 in PHP. Use decimal columns, pass quantities as strings and let the database do the math. If you must compute in PHP, use integer base units (grams instead of kilograms) or a decimal library.

Units of measure. If you buy in boxes of 12 and sell in pieces, convert to one base unit before posting. A ledger that mixes boxes and pieces cannot be summed.

Editing posted documents. Users will ask for an "edit" button on a delivery note that already moved stock. The ledger-friendly version of that button is: reverse the old document, post the new one. Both stay visible.

Negative stock policy. Some businesses must allow it temporarily (goods sold before the receipt is typed in). Make it an explicit, per-warehouse setting, not an accident. That is what $allowNegative is for.

Backdated postings. A movement with an old occurred_at changes historical reports. Decide on a period lock (for example, no postings into a month that finance has closed) and enforce it in StockLedger.

Forgetting the cache in tests. Write at least one test that posts random documents and then asserts that every balance equals the sum of its movements. It catches more bugs than ten hand-written cases.

Summary

  • Stop updating a stock column. Append signed movements to a ledger and treat the balance as a cache.
  • Use a unique key on the source document line to make postings idempotent.
  • Post through one service, inside one transaction, with the arithmetic and the negative check in a single UPDATE.
  • Fix mistakes with reversals, never with edits or deletes.
  • Reconcile the cache on a schedule, and enjoy historical stock reports without snapshot jobs.

I first moved an inventory module to this pattern after a stock count that did not match anything. The next count matched, and when it didn't, the stock card showed exactly which document caused it.

How does your system handle stock today: a quantity column, a ledger, or something in between?