Price Drop Tracker (CamelCamelCamel)

0) Problem Restatement

Design a service like CamelCamelCamel (asked at Meta twice). Users paste a product link from a large online store, see the product's price history chart, and set an alert: "tell me when this drops below $250". The system periodically fetches current prices for millions of products (through official APIs or scraping, within rate limits), stores the history, and notifies users when a price crosses their threshold.

1) Requirements

• Add a product by URL (normalize it to a product ID, e.g., an ASIN).
• Price history chart (daily or hourly points).
• Alerts: below a price, or X% drop. One-time or recurring.
• Notifications by email or push, without spam.

1.1 Scale Estimates

• 100M tracked products, 20M active alerts.
• If we fetched every product hourly: 100M/hour ≈ 28K fetches/sec, likely beyond the source's limits. So we prioritize.
• History: 100M products × 1 point/day × 16 bytes ≈ 1.6 GB/day, which is fine.

2) Architecture

flowchart LR
    U["Users"] --> API["API - products, alerts, history"]
    API --> PDB[("Products + alerts DB")]
    SCH["Fetch Scheduler - priority"] --> FQ[("Fetch queue")]
    FQ --> FE["Fetchers - API/scraper, rate limited"]
    FE --> STORE["Marketplace"]
    FE --> K[("Price updates")]
    K --> HIST[("Price history - time series")]
    K --> AM["Alert Matcher"]
    AM --> PDB
    AM --> N["Notifier - dedupe"]
    N --> U

3) Data Model

products:     product_id, marketplace, url, title, last_price, last_fetched_at, next_fetch_at, priority
price_points: product_id, ts, price_cents, availability          (time-series, partitioned by month)
alerts:       alert_id, user_id, product_id, type (below|pct_drop), threshold_cents, active, last_notified_price

Index alerts by (product_id, threshold_cents), so when a product's price changes, we can quickly find alerts where threshold >= new_price.

4) Key Flows

4.1 Fetch scheduling

• Each product has a next_fetch_at and a priority:
• Popular products (many alerts or views) → every hour.
• Volatile products (prices change often) → more often.
• Products with no alerts and few views → daily or weekly.
• The scheduler pulls due products (indexed by next_fetch_at) into the queue.
• Rate limits: fetchers use a token bucket per marketplace (and per API key), prefer official APIs with batch lookups (e.g., 10 products per call), and fall back to scraping carefully (respect robots.txt, back off on errors).

4.2 Price update

1. The fetcher gets a price and publishes { product_id, price, ts } if it changed (or every day as a heartbeat point).
2. History stores the point. products.last_price is updated.
3. The Alert Matcher runs only when the price dropped: SELECT alerts WHERE product_id = ? AND threshold_cents >= new_price AND active.
4. For each match, notify, unless we already notified this user at this or a lower price (last_notified_price). One-time alerts are deactivated.

5) Details Worth Mentioning

• Validation: ignore obviously wrong prices (parser errors, a $0 price, a third-party seller at a weird price). Confirm a big drop with a quick re-fetch before alerting.
• Deal spikes: a big sale may trigger 1M alerts at once. The notifier queues and rate-limits sending (and batches emails).
• URL normalization: many URLs map to the same product (tracking params, mobile vs desktop), so extract the canonical product ID.
• History charts: downsample old data (daily min, max and close), and cache chart images or JSON for popular products.

6) Trade-offs & Alternatives

7) Wrap-Up

Normalize product URLs to canonical IDs and schedule price fetches by priority (popularity and volatility) with per-marketplace rate limits, preferring batched official APIs. Stream price changes into a time-series history and an alert matcher that, on price drops, finds alerts via a (product, threshold) index and sends deduplicated, rate-limited notifications. Validate suspicious prices before alerting, and downsample history for cheap long-term charts.