Design a price tracker and alerts

You cannot check 50M products every 15 minutes; that is 4.8B fetches a day. So treat fetches as a budget and spend it where value is highest. Value of checking a product depends on how many users watch it (alerts, extension views), how volatile its price is, and whether a sale is running. Compute a tier daily: hot (has many alerts or changed recently) every 15-30 minutes, warm every 4-6 hours, cold once a day. Adapt between checks: after a change, shorten the interval; after many unchanged checks, lengthen it (like an exponential backoff on freshness). Before a known sale (Big Billion Days, Great Indian Festival), raise frequency for watched products and pre-scale fetchers. Politeness is part of the design: per-store token buckets in Redis cap request rate, fetchers honour robots rules and back off on 429 or 503, and you prefer official APIs (Amazon's Product Advertising API, affiliate feeds) because they are stable, legal and cheaper than parsing HTML. The scheduler itself is simple: an indexed next_check_at column, batch claims with FOR UPDATE SKIP LOCKED, and per-store queues so one slow store cannot block the others.

The naive design scans all 20M alerts after each price change, or checks each alert on a timer. Both waste work. The key insight is that alerts are keyed by product: a price change for product X can only affect alerts on X. Store alerts sharded by product_id with an index on (product_id, state, target_paise). When the price becomes P, a single range query returns armed alerts with target >= P; usually a handful, rarely thousands for a popular phone. Percentage-drop alerts are stored as an absolute target computed when the alert is created (target = price_at_creation × (1 - pct)), so they use the same index. Back-in-stock alerts are a separate small list per product triggered on the in_stock false→true edge. Firing must be exactly once per arming: use a conditional update from armed to fired and a dedupe key on the notification. If the user wants repeated alerts, re-arm with a new lower target (e.g. notify again only if it drops another 5%) to avoid spamming during a sale where the price flips between two values every few minutes.

Price pages are messy. The same product has many sellers and the buy-box seller changes; a variant (colour, storage) may have a different price; a 'deal price' may need a bank card; out-of-stock pages may show a stale or zero price; parsers break when the store changes its HTML. Bad data creates fake alerts and kills trust. Defences: canonicalise products by store SKU plus variant, not by URL; record seller and in_stock with each observation; ignore prices when the item is out of stock; reject observations that differ by more than, say, 70% from the median of the last 30 days unless confirmed by a second fetch a few minutes later; monitor parser success rate per store and page template, and alert when it falls. For the deals page, compare against the 90-day median rather than the inflated MRP, because many 'big discounts' are against an MRP nobody pays.

Writing only changes cuts storage and write load by more than 10x and makes history queries fast. You lose the record of when the price was last confirmed, so store last_checked_at on the product row to show 'checked 10 min ago'.

Matching on change is event-driven and costs work only when prices move. Periodic scans are simpler but slow and expensive at 20M alerts. Event-driven wins, with a nightly sweep as a safety net for missed events.

At 10M rows/day, a partitioned Postgres table works for a while. TimescaleDB or ClickHouse give compression (often 10x) and fast range scans by product and time, which matters once history is billions of rows.

Official APIs are stable and compliant but have quotas and may not expose every field. Page fetching covers more stores but breaks often and must be rate-limited carefully. Use APIs first and fetch pages only within the store's rules.

Prices change every few minutes and alerts fire 10x more. Notifications become the bottleneck: queue them, batch per user into digests, and scale notification workers; respect FCM and WhatsApp throughput limits.

A popular phone may have 200k alerts. One range query returning 200k rows is slow; process in pages, fan out to the notification queue in chunks, and keep the index tight.

When the per-store budget is the limit, the only lever is smarter prioritisation: spend fetches on watched and volatile products, and use extension page views as free freshness signals (the extension can report the price it sees, verified before use).

Parser success rate drops; the monitor pauses that store's parser version so bad prices are not written, alerts on-call, and old prices stay in place. Products show 'last checked' honestly.

Alerts stay in the queue with retries and backoff; dedupe keys prevent duplicates when the provider recovers. Fall back to email if push fails for long.

Claimed rows have a lease (claimed_until); if a scheduler dies, another picks them up after the lease. Jobs are idempotent because a repeated fetch only writes a point if the price changed.