A buyer approves a supplier quote. Two weeks later, finance asks a simple question: "We paid 14.20 per unit last spring. Why are we paying 16.80 now, and who noticed?"

Nobody noticed, because the system could not tell them. The product had a last_price column on the supplier pivot table, and every new purchase order overwrote it. The old price was gone. So was the date it changed, the document that changed it, and any chance of comparing a new quote against what the company actually paid before.

Price history is one of those features that looks optional until the first audit or the first awkward meeting with a supplier. This post shows a small, reliable way to keep it in a Laravel procurement module: an append-only price table with effective dates, a safe way to record new prices, an "as of" lookup, and a variance check that flags suspicious quotes during RFQ comparison.

The problem with a single last_price column

A column like supplier_product.last_price answers exactly one question: what is the latest number? It cannot answer the questions procurement actually asks:

  • What did we pay this supplier for this item on a given date?
  • Is this new quote higher than our recent purchases, and by how much?
  • Did the price change because of a price list, a quote, or a negotiated purchase order?
  • Which document introduced the change?

There is also a subtle data problem. Quoted prices and paid prices are not the same thing. A supplier can quote 15.00 and the purchase order can close at 14.40 after negotiation. If both write to the same column, your "history" mixes promises with facts.

Step 1: An append-only, effective-dated price table

Each row says: from this date, this supplier charged this price for this product, in this currency and unit, according to this source document.

<?php 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('supplier_prices', function (Blueprint $table) { $table->id(); $table->foreignId('supplier_id')->constrained(); $table->foreignId('product_id')->constrained(); $table->char('currency', 3); $table->string('unit', 16); // the unit the price refers to: pcs, kg, box12 $table->decimal('unit_price', 15, 4); $table->string('source', 20); // price_list | quote | purchase_order $table->unsignedBigInteger('source_id')->nullable(); $table->date('valid_from'); $table->date('valid_to')->nullable(); // null = still current $table->timestamps(); $table->unique( ['supplier_id', 'product_id', 'currency', 'unit', 'source', 'valid_from'], 'supplier_prices_unique_start' ); $table->index(['product_id', 'source', 'valid_from']); }); } public function down(): void { Schema::dropIfExists('supplier_prices'); } };

A few deliberate choices:

  • decimal(15, 4), never float. Unit prices often need more than two decimals, and floating point rounding is not something you want to explain to an accountant.
  • source is part of the key. Quotes and purchase orders live side by side without overwriting each other. Your comparison logic decides which source counts as "what we paid".
  • valid_from and valid_to are dates, not timestamps. Prices change per business day. Dates avoid the surprises you get when midnight in one timezone is still yesterday in another.
  • Rows are never edited, only closed. The one allowed change to an existing row is setting valid_to when a newer price starts.

The model casts keep the values honest. Laravel's decimal:<precision> cast gives you the value as a fixed-precision string rather than a float, which is what you want for money:

<?php namespace App\Models; use Illuminate\Database\Eloquent\Builder; use Illuminate\Database\Eloquent\Model; class SupplierPrice extends Model { protected $guarded = []; protected function casts(): array { return [ 'unit_price' => 'decimal:4', 'valid_from' => 'immutable_date', 'valid_to' => 'immutable_date', ]; } public function scopeEffectiveOn(Builder $query, string $date): void { $query->where('valid_from', '<=', $date) ->where(function (Builder $q) use ($date) { $q->whereNull('valid_to')->orWhere('valid_to', '>', $date); }); } }

Note the grouped orWhere. Without the closure, the OR escapes the rest of the query and starts matching rows for other suppliers and products. The Laravel docs recommend always grouping orWhere calls for exactly this reason.

Step 2: Record a new price without breaking the timeline

Recording a price is two writes: close the current open row, then insert the new one. If two requests do this at the same time, you can end up with two open rows for the same supplier and product. Do both writes in one transaction and lock the open row before touching it:

<?php namespace App\Procurement; use App\Models\SupplierPrice; use Illuminate\Support\Facades\DB; use InvalidArgumentException; final class RecordSupplierPrice { public function handle( int $supplierId, int $productId, string $currency, string $unit, string $unitPrice, string $source, ?int $sourceId, string $validFrom, // Y-m-d ): SupplierPrice { if (! preg_match('/^\d+(\.\d{1,4})?$/', $unitPrice)) { throw new InvalidArgumentException("Invalid unit price [{$unitPrice}]."); } $key = [ 'supplier_id' => $supplierId, 'product_id' => $productId, 'currency' => $currency, 'unit' => $unit, 'source' => $source, ]; return DB::transaction(function () use ($key, $unitPrice, $sourceId, $validFrom) { $open = SupplierPrice::query() ->where($key) ->whereNull('valid_to') ->lockForUpdate() ->first(); if ($open && $open->valid_from->toDateString() >= $validFrom) { throw new InvalidArgumentException( 'The current price starts on '.$open->valid_from->toDateString() .'. Backdated changes need an explicit correction flow.' ); } $open?->update(['valid_to' => $validFrom]); return SupplierPrice::create($key + [ 'unit_price' => $unitPrice, 'source_id' => $sourceId, 'valid_from' => $validFrom, ]); }, 3); } }

The second argument to DB::transaction is the number of attempts when a deadlock occurs. The Laravel docs recommend wrapping lockForUpdate in a transaction so the lock is held until the whole operation commits, and released automatically on rollback.

The price is validated as a string with at most four decimals. It never passes through a float on its way into the database.

Rejecting backdated prices may feel strict. It is intentional. Inserting a price in the middle of an existing timeline means splitting a closed range, and that is a correction, not a normal price update. Make it a separate, audited action.

Step 3: Ask "what did we pay on this date?"

With effective dates, the "as of" lookup is a single query:

<?php use App\Models\SupplierPrice; $paid = SupplierPrice::query() ->where('supplier_id', $supplierId) ->where('product_id', $productId) ->where('source', 'purchase_order') ->effectiveOn('2026-03-15') ->first();

And the recent purchase history for a variance check is just as simple:

<?php use App\Models\SupplierPrice; $recent = SupplierPrice::query() ->where('product_id', $productId) ->where('currency', $quote->currency) ->where('unit', $quote->unit) ->where('source', 'purchase_order') ->where('valid_from', '>=', now()->subMonths(12)->toDateString()) ->orderByDesc('valid_from') ->limit(10) ->pluck('unit_price') ->all();

Note that the history query is across all suppliers. When a new supplier quotes, you still want to compare against what the company paid anyone for the same item, in the same currency and unit.

Step 4: Flag quotes that drift too far

The variance check should not block anything. Its job is to put a visible warning next to a quote in the RFQ comparison screen, so a human looks twice. Keep it as a plain PHP class with no framework dependencies, so it is trivial to test.

To avoid floats in the comparison, convert four-decimal strings to integer units first, and express the variance in basis points (1 bp = 0.01%):

<?php namespace App\Procurement; final class PriceVarianceChecker { public function __construct( private int $warnAboveBp = 800, // +8% private int $blockReviewBp = 2000, // +20% private int $minHistory = 2, ) {} /** * @param list<string> $history recent paid prices, newest first, 4-decimal strings * @return array{level: string, last: ?string, median: ?string, vs_last_bp: ?int, vs_median_bp: ?int} */ public function check(string $quoted, array $history): array { if (count($history) < $this->minHistory) { return ['level' => 'no_history', 'last' => $history[0] ?? null, 'median' => null, 'vs_last_bp' => null, 'vs_median_bp' => null]; } $q = self::units($quoted); $last = self::units($history[0]); $median = self::median(array_map(self::units(...), $history)); $vsLast = intdiv(($q - $last) * 10000, $last); $vsMedian = intdiv(($q - $median) * 10000, $median); $worst = max($vsLast, $vsMedian); $level = match (true) { $worst >= $this->blockReviewBp => 'needs_review', $worst >= $this->warnAboveBp => 'warning', default => 'ok', }; return [ 'level' => $level, 'last' => $history[0], 'median' => self::format($median), 'vs_last_bp' => $vsLast, 'vs_median_bp' => $vsMedian, ]; } /** "14.2" -> 142000 (units of 0.0001) */ public static function units(string $price): int { [$int, $frac] = array_pad(explode('.', $price, 2), 2, ''); return (int) $int * 10000 + (int) str_pad(substr($frac, 0, 4), 4, '0'); } public static function format(int $units): string { return intdiv($units, 10000).'.'.str_pad((string) ($units % 10000), 4, '0', STR_PAD_LEFT); } /** @param list<int> $values */ private static function median(array $values): int { sort($values); $n = count($values); $mid = intdiv($n, 2); return $n % 2 ? $values[$mid] : intdiv($values[$mid - 1] + $values[$mid], 2); } }

Why compare against both the last price and the median? The last price alone is easy to game: one unusually expensive emergency purchase becomes the new baseline, and every following quote looks "normal". The median of recent purchases is much harder to move with a single outlier.

Here is what it returns for the opening story, with paid prices of 14.20, 14.40 and 13.90:

<?php use App\Procurement\PriceVarianceChecker; $checker = new PriceVarianceChecker(); $checker->check('16.80', ['14.20', '14.40', '13.90']); // ['level' => 'warning', 'last' => '14.20', 'median' => '14.2000', // 'vs_last_bp' => 1830, 'vs_median_bp' => 1830] $checker->check('14.50', ['14.20', '14.40', '13.90']); // ['level' => 'ok', ..., 'vs_last_bp' => 211, ...] $checker->check('18.00', ['14.20', '14.40', '13.90']); // ['level' => 'needs_review', ..., 'vs_last_bp' => 2676, ...]

The 16.80 quote is 18.3% above both baselines. It does not cross the 20% review line, but it gets a warning in the comparison table, which is exactly the "who noticed?" moment the buyer was missing.

Common pitfalls

Comparing different units. A price per box of 12 and a price per piece are not comparable. That is why unit is part of the key and part of the history query. Normalize to a base unit when quotes arrive, or compare only like with like.

Comparing different currencies. The checker above only compares prices in the same currency. If you convert, use the exchange rate of the quote date and store the rate you used, otherwise the history changes every time the rate table does.

Treating quotes as paid prices. Keep source separate and decide explicitly which sources feed the variance baseline. Usually it is purchase orders only.

Relying on the row lock for the first price. lockForUpdate locks rows that exist. When there is no open row yet, two concurrent requests can both try to insert the first price. The unique index on valid_from is what turns that race into a clean UniqueConstraintViolationException instead of duplicate data.

Thresholds in code. 8% and 20% are examples. Different categories behave differently: raw materials move with markets, office supplies barely move. Store thresholds per category so procurement can tune them without a deploy.

Hiding the warning. A variance flag that only lives in a log helps nobody. Show it in the comparison screen, next to the quote, with the baseline values, so the buyer can explain it or challenge the supplier.

Summary

  • Replace a single last_price column with an append-only, effective-dated supplier_prices table.
  • Keep source (price list, quote, purchase order) and unit in the key, and store prices as decimal, never float.
  • Record new prices in a transaction with lockForUpdate, and let a unique index handle the first-price race.
  • Answer "what did we pay on this date?" with a grouped effective-date scope.
  • Flag quotes against both the last paid price and the median of recent purchases, using integer math and basis points.

The goal is not to stop buyers from accepting higher prices. Prices go up for good reasons. The goal is that nobody accepts one without seeing it.

How does your procurement system decide which price counts as "what we paid": the purchase order, the invoice, or something else?