Design Case Studies · Lesson 17

Design: Price-tracker API

A price tracker quietly polls product pages it has no official access to, stores years of price history, and fires a notification the instant a price drops below a user's target. The interesting design challenges are not in the API surface — they are behind it: how do you poll thousands of upstream sources politely, deduplicate redundant writes, recover gracefully from upstream failures, and deliver an alert within minutes of a price drop?

1 — Requirements

Before the API shape, nail down what the system actually needs to accomplish and what external constraints bind the design.

Functional requirements

• Watch a product: a user submits a product URL and an alert threshold price. The system begins tracking that product's price.
• Price history: for any tracked product, return the full price time-series (timestamp + price) queryable by date range.
• Threshold alerts: when an observed price drops at or below a user's threshold, deliver a notification to the user via their configured channel (email, webhook, push).
• Current price: return the most recently observed price for a tracked product on demand.
• Source ingestion: periodically crawl upstream product pages to read the current price. Handle multiple retailers as upstream sources.
• Watch management: users can list, pause, and delete their watches.

Non-functional requirements

• Upstream politeness: the crawler must not hammer upstream sources. Respect robots.txt, use per-domain rate limits and exponential backoff, and add jitter to scheduled jobs.
• Upstream failure resilience: if an upstream source goes down, the system must not cascade that failure into the rest of the pipeline. Use a circuit breaker per source domain.
• Alert timeliness: a price drop should trigger a user notification within 10 minutes of the drop being observed — sooner for actively watched popular products.
• History durability: price history must be retained and queryable for at least 3 years per product.
• Deduplication: if the crawl returns the same price as the previous data point for a product, do not write a duplicate row. Store only meaningful changes.
• Read scalability: price history queries (charting, export) may return thousands of data points; the query path must paginate and be independent of the ingestion write path.

Scale assumption: 2 million tracked product-URL pairs. Each product is crawled on average once every 30 minutes, yielding ~1,100 crawl completions per second. Most crawls find no price change (~80%), so effective write throughput is ~220 new price points per second. Price drops in practice are rare — assume 0.1% of crawls produce a price drop that triggers an alert, giving ~1 alert delivery per second at baseline with spikes during sale events.

2 — Design decisions

Decision 1: Scheduled polling with per-domain rate limiting and jitter

Think of the crawler as a polite librarian who makes copies of pages it needs, but calls ahead before arriving at each library and spaces its visits so it never overwhelms any single branch. The "calling ahead" is robots.txt checking; the spacing is rate limiting and jitter.

Concretely: a job scheduler queues crawl tasks per product. Each task is tagged with its domain (e.g. amazon.com). A per-domain rate-limiter allows at most N requests per second to that domain across all crawler workers. To prevent the thundering-herd problem where all products for a domain happen to be scheduled at the same minute, each task's scheduled time is offset by a random jitter of ±50% of the base interval (see retries and backoff).

Crawl interval adapts to demand: a product with 10,000 active watches is polled every 5 minutes; a product with 1 watch is polled every 60 minutes. This ensures high-value products are fresher without increasing total crawl load proportionally.

Decision 2: Time-series price history with change-only writes

Price history is an append-only log: each record is (product_id, observed_at, price, currency). The schema is intentionally narrow — no product metadata, just the price signal. This lets you partition the table by product_id and sub-partition by time bucket (month or year) to keep partition sizes bounded over 3-year retention.

Deduplication (change-only writes): before inserting a new price point, the ingestor reads the product's last known price. If the incoming price equals the last stored price, the row is discarded. Only a price change produces a write. This cuts write volume by ~80% at the stated scale assumptions and keeps the time-series compact — the chart shows when prices actually moved, not thousands of identical "no change" records.

-- Pseudo-SQL: change-only insert
INSERT INTO price_points (product_id, observed_at, price)
SELECT $product_id, $now, $price
WHERE NOT EXISTS (
  SELECT 1 FROM price_points
  WHERE  product_id = $product_id
  AND    price      = $price
  AND    observed_at = (
    SELECT MAX(observed_at) FROM price_points
    WHERE product_id = $product_id
  )
);

In practice, cache the last known price per product in Redis (key: last_price:{product_id}, value: price as a string). The ingestor reads Redis, compares, and only hits the database if the price changed. This eliminates the subquery entirely on the common (no-change) path.

Decision 3: Alert delivery via webhook / notification path

When the ingestor writes a new price point that is at or below any user's threshold for that product, it enqueues an alert task. The alert worker looks up all watchers for the product whose threshold_price >= new_price, then delivers a notification to each via the user's preferred channel (see Notification API pattern).

Delivery options per user:

• Email: lowest latency requirement; acceptable delay up to a few minutes.
• Webhook: user provides an endpoint URL; the alert worker POSTs the event payload. Retried with exponential backoff on failure (see retries & backoff).
• Push notification: mobile push via APNs / FCM; fastest perceived delivery for mobile users.

The alert pipeline is asynchronous — the ingestor does not wait for notification delivery before acknowledging the crawl task as complete. This decouples ingestion throughput from notification delivery latency.

Decision 4: Circuit breaker per upstream source domain

If a retailer's site goes down for an hour, the crawler should not continue hammering it with failing requests — wasting crawler worker threads, consuming rate-limit budget, and filling logs with errors. A circuit breaker per source domain handles this.

States per domain circuit breaker: CLOSED (normal crawling) → OPEN (upstream unavailable, skip all crawls for this domain, save capacity) → HALF-OPEN (probe with one request; if it succeeds, close; if it fails, reopen with longer timeout). Thresholds: open after 5 consecutive failures for a domain; probe after 10 minutes of OPEN state.

When a domain's circuit is OPEN, its crawl tasks are deferred (rescheduled to after the breaker probe window) rather than discarded. Price history shows a gap during the outage; that is correct and honest. Users are not alerted during a gap because no data was ingested — no false "price dropped to $0" events.

3 — The API model

Create a watch (start tracking a product)

POST /v1/watches
Authorization: Bearer <token>
Content-Type: application/json

// Request body
{
  "product_url":     "https://www.example-shop.com/products/headphones-pro",
  "threshold_price": 79.99,
  "currency":        "USD",
  "notify_via":      "webhook",
  "webhook_url":     "https://my-app.example.com/price-alerts"
}

// 201 Created
{
  "id":               "watch_c9d2",
  "product_id":       "prod_ff01",    // internal product identity
  "product_url":      "https://www.example-shop.com/products/headphones-pro",
  "threshold_price":  79.99,
  "currency":         "USD",
  "current_price":    109.00,          // last observed price if already tracked
  "status":           "active",
  "next_poll_at":     "2024-06-01T10:08:23Z",
  "created_at":       "2024-06-01T10:00:00Z"
}

// 422 Unprocessable Entity — URL from unsupported domain
{
  "error":  "unsupported_domain",
  "detail": "example-shop.com is not in the list of supported retailers"
}

Get price history for a product

GET /v1/products/prod_ff01/history?range=30d&limit=500&cursor=eyJ0IjoiMjAyNC0wNS0wMSJ9
Authorization: Bearer <token>

// 200 OK
{
  "product_id":  "prod_ff01",
  "currency":    "USD",
  "range":       "30d",
  "points": [
    { "observed_at": "2024-05-01T08:12:00Z", "price": 119.00 },
    { "observed_at": "2024-05-03T14:05:00Z", "price": 109.00 },
    { "observed_at": "2024-05-18T22:01:00Z", "price": 95.00  },
    // … up to limit
  ],
  "next_cursor": "eyJ0IjoiMjAyNC0wNS0xOSJ9",   // null if no more pages
  "total_points": 87
}

// range values: 7d | 30d | 90d | 1y | all

Alert delivery via webhook

// Server → user's webhook_url (POST from our alert worker)
POST https://my-app.example.com/price-alerts
Content-Type: application/json
X-Signature-SHA256: hmac_sha256(secret, body)   // for verification

{
  "event":            "price_drop",
  "watch_id":         "watch_c9d2",
  "product_id":       "prod_ff01",
  "product_url":      "https://www.example-shop.com/products/headphones-pro",
  "threshold_price":  79.99,
  "observed_price":   74.95,
  "observed_at":      "2024-06-01T10:08:10Z",
  "currency":         "USD"
}

The X-Signature-SHA256 header is an HMAC of the request body using a secret shared with the user on watch creation (see Stripe's webhook verification as a reference pattern). The recipient verifies the signature before acting on the alert. If the webhook endpoint returns a non-2xx status, the alert worker retries with exponential backoff up to 3 attempts over 30 minutes, then marks the delivery as failed.

4 — Evaluation & latency budget

Polling cadence vs. price freshness

The fresher you want price data, the more frequently you must crawl. But higher frequency means more upstream load. The relationship is linear: halving the poll interval doubles your crawl request rate. At 2 million tracked products:

The priority-tiered approach reconciles freshness with politeness: popular products (many watchers) get shorter intervals; niche products get longer. This keeps total crawl rate near 1,100 req/s without giving every product a 5-minute interval.

Upstream-failure handling

When a domain's circuit opens (5 consecutive failures), all crawl tasks for that domain are deferred. The price history shows a gap. Users are not falsely alerted. The circuit probes after 10 minutes; if the probe succeeds, the circuit closes and the scheduler resumes normal cadence for that domain's products — it does not immediately hammer the domain with the backlog of deferred tasks. Instead, the deferred tasks are re-queued with standard jitter to spread the recovery load.

What is lost during an outage? Freshness. A product that normally updates every 30 minutes may show prices from before the outage. If the actual price on the upstream site changed during the outage, we will not know until the circuit recovers and the next successful crawl. This is an inherent limitation of polling: you can only know what you can observe.

History storage and query

With change-only writes, a product that never changes price produces at most 1 row per period. A product with volatile pricing (flash sales, dynamic pricing) may produce dozens of rows per day. Over 3 years, a volatile product accumulates at most ~90,000 rows (1 per hour). At 200 bytes per row, that is 18 MB per volatile product — manageable.

Query: GET /v1/products/:id/history?range=30d translates to a range scan on (product_id, observed_at) with a composite index. The cursor-based pagination ensures that a large history (export, charting) does not block on a single large query. The query path is read-only and can be served from a read replica without touching the ingestor's write path.

Alert timeliness budget

The dominant term is the polling interval, not the processing pipeline. Improving alert timeliness means polling more frequently for high-value products — not optimising the ingestor.

Under the hood: the core mechanism

The visible API surface — watch creation, history queries, alert webhooks — is thin. All the interesting engineering is the ingestion pipeline that runs continuously behind it. This section traces exactly what happens inside one full poll cycle, from scheduler tick to alert delivery, and shows the data structures that make it fast.

Key data structures

Worked trace: one full poll cycle that crosses a threshold and fires an alert

Setup: product prod_ff01 (headphones), last stored price $109.00 (10900 cents in Redis). One user (user_42) has a watch with threshold $80.00 (8000 cents). The scheduler fires a crawl task for this product.

What happens when the upstream is flaky: circuit breaker trace

The deduplication pipeline in detail

The Redis fast-path and the SQL change-only write complement each other. Here is the decision tree the ingestor runs for every crawl result:

Operating & debugging it

Inspecting the ingestion pipeline

Symptom → cause → fix table

Production monitoring checklist

1. Circuit breaker state dashboard: alert if any domain circuit has been OPEN for more than 30 minutes — indicates a prolonged upstream outage or a mis-configured breaker.
2. Crawl queue depth: should stay below 5× worker throughput per minute. A growing queue means workers are falling behind; check for timeout spikes or a large domain outage deferring tasks.
3. Deduplication ratio: track the ratio of writes / crawl completions. Should be ~0.2 (20% are actual changes). A sudden spike toward 1.0 may mean your Redis TTL expired and every crawl is hitting the DB, or upstream is returning volatile/randomised prices.
4. Alert delivery success rate: track delivered / attempted per hour. Sustained failures above 5% warrant investigation — check webhook endpoint health, HMAC signing key rotation, or whether users are returning non-2xx intentionally.
5. Price anomaly log: count parse failures per domain per hour. A spike means the upstream changed its HTML structure and the price extractor needs updating.

Sources & further reading

• RFC 9309 — Robots Exclusion Protocol — the standard the crawler must implement before making any requests
• Stripe — Webhook best practices — canonical reference for idempotent, signed webhook delivery with retry
• Martin Fowler — Circuit Breaker — the original description of the circuit-breaker pattern used for upstream failure isolation
• PostgreSQL — Partial Indexes — useful for indexing only active watches when querying alert thresholds at ingest time
• TimescaleDB — Time-series data — purpose-built extension for time-series storage and range queries, directly applicable to price_points table design
• AWS Builders' Library — Avoiding insurmountable queue backlogs — covers the deferred-task recovery pattern used when a circuit breaker closes