Historical Price Tracking
Relevant source files
The following files were used as context for generating this wiki page:
Historical Price Tracking is a core feature of the WhereIsLifeCheaper system that enables the monitoring and visualization of grocery price fluctuations over time across multiple countries and supermarkets. By capturing time-series data during daily automated scraping runs, the system allows users to analyze trends, detect sales, and compare cost-of-living metrics based on historical evidence rather than just real-time snapshots.
The system tracks specific price points, including original prices and sale indicators, linked to unique product mappings in different geographical regions. This data is then aggregated and exposed via a REST API to a frontend dashboard that utilizes interactive charting to present the historical trends to the end-user.
Sources: CLAUDE.md:12-14, plan.md:11-14, src/database/migrations/005_create_product_mappings_and_prices.sql:1-4
Data Architecture and Storage
The foundation of historical tracking lies in the relational database schema, specifically the prices and product_mappings tables. The system uses a time-series approach where every scrape event generates a new record in the prices table rather than overwriting existing data.
Database Schema for Prices
The prices table stores individual price observations and maintains a link to the product_mappings table, which identifies the specific supermarket and product being tracked.
| Field | Type | Description |
|---|---|---|
id | SERIAL | Primary key for the price record |
product_mapping_id | INTEGER | Reference to the supermarket-specific product mapping |
price | DECIMAL(10, 2) | The current observed price |
currency | VARCHAR(3) | ISO currency code (e.g., EUR, TRY, ALL) |
original_price | DECIMAL(10, 2) | Price before discount, used to track historical sales |
is_on_sale | BOOLEAN | Flag indicating if the price was a promotional offer |
price_per_unit | DECIMAL(10, 2) | Normalized price for comparison (e.g., per kg/L) |
scraped_at | TIMESTAMP | The exact time the price was recorded |
Sources: src/database/migrations/005_create_product_mappings_and_prices.sql:40-51, plan.md:111-118
The relationship between products and their historical price points is illustrated below:
API Implementation
The backend provides specific endpoints to retrieve historical data for both individual products and canonical product comparisons.
Price History Endpoints
The productsApi and canonicalApi services facilitate the retrieval of time-series data. The primary endpoint for historical trends is /api/products/:productId/price-history.
| Parameter | Type | Description |
|---|---|---|
productId | number | The unique ID of the product mapping |
days | number | Date range for history (default: 30, supports 7, 30, 90) |
Sources: frontend/src/services/api.ts:153-163, frontend/src/components/comparison/PriceHistoryChart.tsx:23
Backend Routing Logic
The system uses zod for query validation to ensure that historical requests adhere to expected limits and formats. For example, canonical comparisons allow for searching and limiting the result set of historical comparisons.
Sources: src/api/routes/canonical.ts:14-23
Frontend Visualization
The system utilizes recharts to render historical price data in an interactive line chart. The PriceHistoryChart component is responsible for fetching parallel history for multiple selected countries and normalizing them for display.
Data Normalization for Charting
Since prices are recorded in local currencies (e.g., TRY for Turkey, ALL for Albania), the frontend converts all historical points into a common currency (EUR) for accurate cross-country comparison.
Component Features
- Parallel Data Fetching: Uses
@tanstack/react-query'suseQueriesto fetch history for each selected country simultaneously. - Date Range Selection: Supports 7-day, 30-day, and 90-day views.
- Currency Conversion: Integrates with
convertToEURutility to standardize the Y-axis. - Sales Visualization: While the chart tracks the primary price, the database captures
original_priceandis_on_saleto provide context for historical dips in the graph.
Sources: frontend/src/components/comparison/PriceHistoryChart.tsx:75-88, frontend/src/components/comparison/PriceHistoryChart.tsx:138-160, src/database/migrations/005_create_product_mappings_and_prices.sql:44-46
Freshness and Stale Data Tracking
Historical tracking is also used for administrative monitoring. The "Canonical Freshness" system tracks the last_price_updated_at field to identify when the tracking for a particular product link has become "stale."
| Status | Threshold | Description |
|---|---|---|
| Never Scanned | Null date | Product has no historical price records yet |
| Stale | > 7 days | Product hasn't received a historical update recently |
| Fresh | < 7 days | Product is being actively tracked by daily scrapers |
Sources: frontend/src/pages/admin/CanonicalFreshness.tsx:10-15, frontend/src/pages/admin/CanonicalFreshness.tsx:94-105
Summary
Historical Price Tracking in WhereIsLifeCheaper transforms simple web scraping into a powerful analytical tool. By maintaining a immutable log of prices in the prices table and providing specialized API endpoints and charting components, the system enables users to visualize economic trends across different countries. This historical depth is critical for distinguishing between temporary sales and long-term price differences in the international grocery market.
Sources: plan.md:11-14, src/database/migrations/005_create_product_mappings_and_prices.sql:58-62