A Stock-Up Alert Service in Laravel: Comparing Current Deals Against Months of Price History

A grocery deal is only a deal if the price is genuinely low. The sticker says "Sale: $3.99" but was it $3.49 last month? Is this the same cycle that repeats every six weeks?

The stock-up alert service answers that question automatically. It compares every current supermarket circular deal against 6 months of historical price data for the same product and store. If the price falls within 5% of the all-time low, it creates an alert. No manual window shopping. No gut checks.

The Data: Two Tables, One Question

The pipeline collects two streams of data:

grocery_deals -- fresh circulars scraped daily from Flipp. Each row has a product name, store ID, current sale price, and validity window. A scopeCurrent() scope filters to deals active right now.

price_histories -- the same products, tracked over time. Every scrape run records the sale_price as a new row, timestamped with scraped_at. Over months this builds a dense price profile: which prices are normal, which are outliers, and what the floor looks like.

The alert service bridges the two. For each active deal, it queries the historical low over the trailing 6 months and computes the gap.

The Threshold: Why 5%

The 5% threshold is a heuristic tuned to grocery pricing behavior. A price at the historical low is a clear stock-up signal. Within 5% of the low, the downside is negligible and the upside (price returning to the mean) is high. Above 5%, the deal is ordinary -- wait for a better cycle.

The threshold is configurable but hardcoded as a constant in production. After running this against 8,000+ deals across four major grocers, 5% catches genuine clearance events without flooding the alert inbox. Tighter than 3% and you miss most rotation cycles. Wider than 10% and every Tuesday sale triggers a false positive.

// Simplified core check
$historicalLow = PriceHistory::where('grocery_store_id', $deal->grocery_store_id)
    ->where('product', $deal->product)
    ->where('scraped_at', '>=', now()->subMonths(6))
    ->min('sale_price');

$savingsPct = (($historicalLow - $deal->price) / $historicalLow) * 100;

if (abs($savingsPct) <= 5.0) {
    StockUpAlert::create([...]);
}

Deduplication

The same deal often appears in multiple scrape cycles. Without deduplication, every cron run would re-fire alerts for the same product. A simple firstOrCreate on grocery_deal_id prevents that:

$existing = StockUpAlert::where('grocery_deal_id', $deal->id)->first();
if (!$existing) {
    // Create alert
}

Each deal gets one alert, period. If the price drops further (a deeper clearance), a new deal record appears with its own ID, so the alert fires again correctly.

Performance at Scale

The naive approach -- loop every active deal, query historical lows one-by-one -- is an N+1 nightmare. With 200+ active deals per region and a 6-month lookback window, that is 200+ aggregate queries per run.

The production version batches the historical lookups:

// Collect all deal store+product pairs
$lookups = $currentDeals->map(fn ($d) => [
    'store_id' => $d->grocery_store_id,
    'product' => $d->product,
]);

// Single query with JOIN to price_histories
// Group by store+product, MIN(sale_price) per group

This collapses 200+ queries into one, dropping run time from ~4 seconds to under 200ms.

What Happens After an Alert Fires

An alert creates a stock_up_alerts row with the product, store, price, historical low, and savings percentage. A separate notification channel (email and in-app) surfaces the alert. The user sees:

"Ground Beef 93/7 at Metro: $4.99 (historical low: $4.79, 4.2% above floor)"

That is the full signal. No chart. No trend line. Just the one number that matters: how close you are to the best price in six months.

Production Observations

After two months of production runs across 4 grocery chains:

• Hit rate: ~12% of active deals trigger an alert in any given week
• False positives: ~2% (deals where the historical low was a clearance anomaly, e.g. expiring soon inventory)
• Average savings vs. mean price: 18% below the 6-month average
• Most common alert products: Meat, dairy, and pantry staples -- products with predictable restock cycles

The system paid for its development time in the first month. Not in direct savings, but in time not spent comparing flyers manually. The service runs at 6 AM daily, and a push notification replaces 20 minutes of circular-page flipping.

The Pattern Generalizes

This stock-up alert pattern -- compare a current value against a trailing historical distribution and fire on threshold -- applies well beyond groceries. Credit card deals, flight prices, SaaS tool subscription tiers, contractor quotes. Any domain with cyclic pricing and regular snapshots benefits from the same two-table structure and threshold logic.

The code is a single Laravel service, 120 lines, zero third-party APIs beyond the existing scrape pipeline. That is the bar for a useful automation: small scope, clear threshold, immediate value.