Design a Price Tracker: Price History and Drop Alerts at Scale

The product boils down to three user stories. A shopper drops in a product URL and the system begins monitoring it; that one action is also how new products enter the catalogue, so tracking and onboarding are the same flow. Anyone visiting a product can then open a chart covering the past month, the past year or the full recorded lifetime of that item.

The third story is the reason people install a tracker at all: set a target such as ₹1,499 and receive an email or push message when the price reaches it. Checkout, coupon codes and cross-store comparison are deliberately excluded. Saying this out loud keeps the interview focused on the hard parts and shows you can scope a problem before drawing boxes.

Freshness is not uniform. A phone with fifty thousand watchers deserves a check roughly every hour, while an obscure cable that nobody has opened in months can be refreshed far less often. Writing this as a requirement up front justifies the priority scheduler that appears later in the design.

Alert latency matters because deals expire. If a lightning offer lasts twenty minutes, an alert delivered an hour late is worse than useless, so the goal is to notify within about five minutes of detecting the drop. Politeness is a hard constraint rather than a nice-to-have: hammer a retailer and you get rate limited or banned, which destroys freshness for every product on that site. Finally, history must be stored cheaply, because the archive only ever grows and nobody deletes old prices.

Assume roughly 100 million tracked products. Because popular items are refreshed many times a day and the long tail only occasionally, a reasonable blended figure is about 500 million checks per day, which works out to around 6,000 checks per second sustained. That number sizes the scraper fleet and the per-retailer queues.

The more important figure is how few of those checks change anything. If we assume only 50 million real price movements per day, and each stored point is about 20 bytes, history grows by roughly 1 GB a day, which is trivial. Add around 200 million active alerts and the shape of the system becomes clear: the read side of crawling is huge, the write side of history is small, and every one of those 50 million changes needs a quick answer to "which alerts did this just satisfy?"

Four entities cover the domain: a Product, a Price point (one price at one timestamp), an Alert, and the User who owns alerts. Keeping price points as a separate entity rather than a column on the product is what lets history scale independently.

The chart is served by GET /products/{id}/prices?range=1y, returning an ordered list of timestamp and price pairs; the range parameter lets the server pick raw data or rollups. Creating an alert uses POST /alerts with the product URL, the target price and a delivery channel. Accepting a URL instead of a product ID is intentional: if the item is unknown, the same call registers it for crawling. DELETE /alerts/{id} removes an alert and returns 204.

The design splits into a write pipeline and a user-facing path. In the pipeline, a crawl scheduler holds a priority queue of products keyed by when each one is next due. It hands work to scrapers, which fetch pages while obeying limits configured per retailer. Their output flows into a price service that parses the page, compares the result with the last known value, and discards anything unchanged.

Genuine changes go two ways. They are appended to the price history store, and if the movement is downward they are sent to an alert matcher. The matcher queries an alerts database organised by product and target, then passes winners to a notifier that sends the message and flags the alert as delivered. On the user side, a thin API reads history for charts and writes new alerts. Because the two paths share only storage, a slow crawler never slows chart loading.

Hitting 6,000 checks per second without getting blocked comes down to spending the crawl budget wisely. Products that carry alerts or get frequent views are scheduled hourly; the long tail drifts to daily or weekly. On top of popularity, the scheduler watches volatility. An item whose price jumps around every few hours gets pulled forward, while one that has been flat for a month backs off. Storing a next-check timestamp on each product makes this a simple ordered queue.

Each retailer gets its own queue and token bucket, so a store that throttles aggressively only slows its own products. Wherever possible, prefer structured sources such as affiliate or catalogue APIs, which are cheaper and more reliable than parsing HTML. A browser extension that reports prices shoppers are already seeing adds free freshness for busy items, but those reports are untrusted input: a value far outside the recent range should trigger a verification crawl before it is recorded.

A single relational table of product, timestamp and price is the obvious start, and it works for a while. Several years in, the row count reaches the billions and the indexes keep swelling, so queries and maintenance both get slower. A wide-column store partitioned by product and clustered by time fixes write scaling and makes each chart a single partition read, but downsampling and expiry become code you have to write and operate.

A time-series database is the better fit. Data of the form (product_id, ts, price) compresses extremely well, and continuous aggregates plus retention policies come built in. Rows are written only on change, while the last-checked time sits on the product record so we still know the data is current. For anything older than a year, keep a daily minimum, maximum and closing price. A multi-year chart looks identical to users, yet the stored footprint shrinks dramatically.

With 200 million alerts, scanning them on every change is impossible, so the layout does the work. Alerts are partitioned by product ID and ordered by target price, which places every watcher of one item side by side in sorted order. Suppose a phone falls from ₹1,999 to ₹1,499. The only alerts newly satisfied are those with targets between those two numbers, so a single bounded range read returns exactly the set to fire. An alert whose target is already met at creation time fires immediately instead of waiting.

Each alert is marked fired once sent, so a price that bounces between ₹1,499 and ₹1,599 does not spam the same person repeatedly. Big sale events are the stress case: a popular product might match a million alerts in one drop. Rather than notifying inline, the matcher pages through the range and enqueues batches, letting the notifier drain them at a pace that email and push providers accept.

Every choice here gives something up. Checking more often catches short-lived deals but burns crawl budget and raises the chance of being blocked, so freshness is rationed by popularity. Writing only changes keeps history tiny, at the cost of tracking the last-checked time somewhere else so a flat line is not mistaken for stale data.

Crowdsourced prices are fresh and free but need validation before anyone acts on them, since a buggy extension or a bad actor could otherwise trigger false alerts. Rolling up old data saves storage while sacrificing hour-level detail for older periods, which few users ever look at. The pattern to remember is: prioritise the crawl, persist movements rather than observations, and let a sorted index turn alert matching into one range read.