What is a Price Alert System?
A price alert system notifies users when a tracked item’s price drops below their target threshold. Used by Amazon, Google Shopping, Honey, and Camelcamelcamel. The core challenge: millions of users have set alerts on millions of products. When any product’s price changes, efficiently find and notify only the users whose threshold has been crossed — without scanning all alerts.
Requirements
- Users set price alerts: “notify me when product X drops below $Y”
- Process 100K product price changes per hour
- Evaluate millions of alerts per price change in <5 seconds
- Send notifications via email, push, and in-app
- Deduplicate: don’t notify the same user twice for the same alert until the price recovers above their threshold
- Alert management: list, pause, delete alerts
Data Model
PriceAlert(
alert_id UUID PRIMARY KEY,
user_id UUID NOT NULL,
product_id UUID NOT NULL,
target_price DECIMAL(10,2) NOT NULL, -- notify when price <= this
status ENUM(ACTIVE, TRIGGERED, PAUSED, DELETED),
created_at TIMESTAMPTZ,
last_triggered_at TIMESTAMPTZ,
INDEX (product_id, target_price) -- critical for efficient evaluation
)
ProductPrice(
product_id UUID PRIMARY KEY,
current_price DECIMAL(10,2),
previous_price DECIMAL(10,2),
updated_at TIMESTAMPTZ
)
AlertNotification(
notification_id UUID PRIMARY KEY,
alert_id UUID,
user_id UUID,
product_id UUID,
triggered_price DECIMAL(10,2),
sent_via VARCHAR[], -- ['email', 'push']
sent_at TIMESTAMPTZ
)
Efficient Alert Evaluation
When product X’s price changes to $45: find all ACTIVE alerts for product X with target_price >= $45 (i.e., threshold crossed).
def evaluate_alerts(product_id, new_price, old_price):
if new_price >= old_price:
return # price went up — no alerts to trigger
# Find all alerts whose threshold is now crossed
alerts = db.query('''
SELECT * FROM PriceAlert
WHERE product_id = :pid
AND status = 'ACTIVE'
AND target_price >= :new_price
ORDER BY target_price DESC
''', pid=product_id, new_price=new_price)
for alert in alerts:
trigger_alert(alert, new_price)
def trigger_alert(alert, triggered_price):
# Mark as triggered (deduplication — won't fire again until price recovers)
db.update(PriceAlert, alert.id, status='TRIGGERED',
last_triggered_at=now())
# Queue notification
notification_queue.enqueue({
'user_id': alert.user_id,
'alert_id': alert.alert_id,
'product_id': alert.product_id,
'triggered_price': triggered_price,
'target_price': alert.target_price
})
Price Recovery Reset
Once triggered, the alert should re-arm when the price recovers above the threshold:
def on_price_change(product_id, new_price, old_price):
evaluate_alerts(product_id, new_price, old_price)
# Re-arm triggered alerts if price recovered above threshold
if new_price > old_price:
db.execute('''
UPDATE PriceAlert SET status='ACTIVE'
WHERE product_id=:pid
AND status='TRIGGERED'
AND target_price < :new_price
''', pid=product_id, new_price=new_price)
Handling Large Products (Hot Items)
For a popular product with 500K active alerts (e.g., iPhone), evaluating all on every price change is expensive. Optimize:
# Redis sorted set: product → alerts indexed by target_price
# Key: alerts:{product_id}
# Score: target_price
# Member: alert_id
# On price drop to $45: find all alerts with target_price >= 45
triggered_alerts = redis.zrangebyscore(f'alerts:{product_id}', 45, '+inf')
# Process in batches of 1000; publish to Kafka for async notification
for batch in chunks(triggered_alerts, 1000):
kafka.produce('alert-triggers', {'product_id': product_id,
'price': 45.0,
'alert_ids': batch})
Key Design Decisions
- Index on (product_id, target_price) — enables fast range query for alerts whose threshold was crossed
- TRIGGERED status for deduplication — prevents repeat notifications until price recovers above threshold
- Redis sorted set for hot products — O(log n + k) query for alerts with target_price >= new_price; faster than DB for products with many alerts
- Async notification via queue — decouples alert evaluation (fast, synchronous) from notification delivery (slower, may fail)
- Price recovery re-arms alerts — users don’t need to manually reset; alert re-triggers on the next price drop
{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”How do you efficiently find all alerts triggered by a price change?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Index the PriceAlert table on (product_id, target_price). When price drops from $60 to $45 for product X: query WHERE product_id=X AND status=ACTIVE AND target_price >= 45. This is an index range scan — extremely fast even with millions of alerts. The index is ordered by target_price, so the DB scans only the relevant range. Without this composite index, a full table scan of millions of alerts would be required for each price change.”}},{“@type”:”Question”,”name”:”How do you prevent duplicate notifications for the same price alert?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”When an alert triggers, set its status to TRIGGERED. In the evaluation query, only select alerts WHERE status=ACTIVE — triggered alerts are excluded. This prevents re-notification while the price remains below the threshold. When the price recovers above the user’s target, reset status to ACTIVE: UPDATE PriceAlert SET status=ACTIVE WHERE product_id=X AND status=TRIGGERED AND target_price < new_price. Now the alert will trigger again on the next price drop.”}},{“@type”:”Question”,”name”:”How does Amazon implement price tracking for millions of products?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”The price change detection pipeline: merchant/listing updates trigger price change events to a Kafka topic. Alert evaluation workers subscribe to the topic, partitioned by product_id so each product’s updates go to the same worker. Each worker keeps a Redis sorted set of alerts per product (score = target_price). On a price drop: ZRANGEBYSCORE alerts:{product_id} new_price +inf returns all triggered alerts in O(log n + k). Workers publish triggered alert_ids to a notification queue for async delivery.”}},{“@type”:”Question”,”name”:”How do you handle price alert volume spikes during sales events?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”During Black Friday or flash sales, thousands of products drop in price simultaneously, triggering millions of alerts. Decouple evaluation from notification: alert evaluation writes triggered alert_ids to a queue (Kafka, SQS). Notification workers pull from the queue and send emails/push notifications at a controlled rate (rate limited to avoid overwhelming the notification service). Queue provides backpressure — evaluation can run ahead of notification delivery without data loss.”}},{“@type”:”Question”,”name”:”How do you let users track alerts across price fluctuations?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Store an AlertNotification record each time an alert triggers: triggered_price, triggered_at, sent_via. Users can view their alert history: all times product X dropped below their threshold, what price it reached, when they were notified. For UI: show current price vs target price as a sparkline chart. Use the ProductPrice table to store both current_price and previous_price — this enables the history view and simplifies the "price went up/down" evaluation logic.”}}]}
Price alert and product tracking system design is discussed in Amazon system design interview questions.
Price alert and e-commerce notification systems are discussed in Shopify system design interview preparation.
Price tracking and alert notification system design is covered in Google system design interview guide.