Design a Price Comparison Engine

Product Entity Resolution

Product matching represents the core algorithmic challenge in multi-retailer aggregation because identical SKUs feature divergent titles, formats, and identifiers across platforms. Matching pipelines pair UPC barcodes with fuzzy title matching and human verification queues.

Event Bus Design (Kafka)

Topic: raw-price-events
  Partitions: 256
  Partition key: retailer_id (preserves per-retailer crawl ordering)
  Retention: 7 days (replay after Flink outage)
  Replication factor: 3, min.insync.replicas: 2

Producer: Crawler connectors + affiliate feed processors (idempotent producer)
  Event: { retailer_id, product_url, price, currency, scraped_at, raw_html_s3_key }

Consumer groups:
  1. price-pipeline: Flink handles deduplication under 5 minutes, validates changes under 50%, and performs entity resolution to map product_id
  2. alert-checker: batches affected product IDs over 60 seconds and queries alerts where target_price >= new_price
  3. anomaly-detector: flags spikes or drops exceeding 50% from the rolling median to a manual review queue
  4. history-writer: inserts time-series records into ClickHouse price_history for trend charts

Sync path: GET /compare reads Redis price cache + PostgreSQL current prices < 50ms
Async path: 500M daily price updates never block synchronous API
DLQ: raw-price-events-dlq; alert when Flink lag > 5 min

Multi-Source Ingestion Architecture

Ingestion prioritizes high-confidence affiliate APIs first, structured bulk feeds second, and headless browser scraping as a targeted gap filler with rate limiting and proxy rotation.

Three data sources:

1. Affiliate APIs (highest data quality):
   Amazon Product Advertising API, eBay Finding API, and retailer partner APIs
   Structured data: price, availability, shipping, and product imagery
   Rate limited: approximately 1 request per second per API key
   Coverage: major national retailers

2. Data Feeds (bulk ingestion):
   Retailers provide daily CSV or XML dumps containing all active catalog prices
   Process: download feed -> parse rows -> match canonical products -> update prices
   Coverage: merchants enrolled in affiliate networks

3. Web Scraping (filling catalog gaps):
   Crawl merchant product pages when no programmatic API or feed exists
   Challenges: anti-bot fingerprinting, dynamic JavaScript rendering, and rate limits
   Compliance: strictly honor robots.txt and published terms of service
   Execution: headless Chromium (Playwright) backed by rotating residential proxy pools
    
Pipeline execution:
  Source -> Kafka (raw price events) -> Flink (deduplication, validation, matching)
    -> PostgreSQL (current prices) + ClickHouse (historical time-series)
  
Validation rules:
  - Price is positive and realistic (flagging obvious anomalies such as a $0.01 laptop)
  - Currency codes match country specifications
  - Product target URL returns HTTP 200 and remains active
  - Price changes exceeding 50% relative to prior observations trigger review queues

Asynchronous Price Alert Pipeline

Price alerts invert the standard read path by evaluating price drops against stored user thresholds and batching notifications to avoid sudden alert storms.

User sets alert: "Notify me when AirPods Pro drops below $180"

Implementation:
  1. Alerts stored in PostgreSQL:
     alerts: { alert_id, user_id, product_id, target_price, active }
     Index on (product_id, active, target_price)

  2. Price update arrives for product X at $175:
     SELECT user_id FROM alerts 
     WHERE product_id = 'X' AND active = true AND target_price >= 175

  3. For each matching user: dispatch push or email notification
     Mark alert as triggered: UPDATE alerts SET active = false

  Optimization for batch processing:
    Flink consumer accumulates price updates over 1-minute tumbling windows
    Batch query: SELECT * FROM alerts WHERE product_id IN (updated_products)
                 AND target_price >= current_price AND active = true
    Dispatch notifications in batched worker pools, minimizing database round trips