Data Repository Layer
Relevant source files
The following files were used as context for generating this wiki page:
- src/api/routes/canonical.ts
- src/api/server.ts (referenced via scripts)
- src/database/index.ts
- CLAUDE.md
- plan.md
- README.md
The Data Repository Layer serves as the primary abstraction between the business logic and the persistence storage in the WhereIsLifeCheaper system. It manages complex queries, data normalization, and state persistence for countries, supermarkets, products, and price history. By centralizing data access, it enables both the Scraper Service to store daily price updates and the REST API to retrieve comparative analytics across multiple countries.
This layer is built on top of a PostgreSQL 15+ database and utilizes a connection pool for efficient query execution. It handles core entities including countries, supermarkets, products, prices, and canonical_products, the latter of which is critical for cross-country price normalization.
Sources: CLAUDE.md, plan.md:14-25, README.md
Core Data Schema
The repository layer operates on a relational schema designed to track time-series price data and product mappings.
Schema Relationships
The following diagram illustrates the relationship between the core tables managed by the repository layer.
The system uses a master catalog approach where products contain normalized metadata, while product_mappings link these to specific supermarket listings. Prices are stored as time-series data linked to these mappings.
Sources: CLAUDE.md, plan.md:65-88, README.md
Key Tables Summary
| Table | Description | Primary Fields |
|---|---|---|
| countries | Supported regions | code, name, currency_code |
| supermarkets | Chains and scraper configs | id, country_code, base_url |
| products | Master product catalog | name, brand, barcode, normalized_name |
| product_mappings | Supermarket-specific links | external_id, product_url, last_scraped_at |
| prices | Historical price points | price, currency, is_on_sale, scraped_at |
| canonical_products | Cross-country identifiers | id, name, show_per_unit_price |
| scrape_logs | Audit trail for scrapers | status, duration, products_scraped |
Sources: CLAUDE.md, plan.md:65-88, README.md
Canonical Product Management
A specialized repository, the canonicalProductRepository, manages the high-level grouping of similar products across different countries. This allows the system to compare the price of "Milk 1L" in Turkey versus Germany, even if the local product names and brands differ.
Data Aggregation and Comparison
The repository layer performs complex joins to calculate averages and per-unit pricing. It derives price_per_unit for products where units (kg, L) are available but explicit per-unit pricing is missing in the raw scrape data.
Key Repository Methods
- findAll(search): Retrieves all canonical products with optional text filtering.
- getComparison(filters): Aggregates products by country, calculating average prices and price-per-unit metrics.
- linkProduct(productId, canonicalId): Maps a specific supermarket product to a canonical group.
- getMappedProducts(params): Retrieves products that have been linked, with support for identifying "stale" mappings that haven't been updated recently.
Sources: src/api/routes/canonical.ts:54-131
Product Matching Logic
The repository layer supports the Product Service in identifying equivalent items. Matching is performed using a tiered confidence strategy:
- Barcode Matching: The highest confidence match.
- Normalized Name + Brand + Unit: Matching based on standardized strings.
- Fuzzy Matching: Within the same category for broad comparisons.
- Manual Mapping: Using the
canonical_productsandproduct_mappingstables to force relationships.
Sources: CLAUDE.md, plan.md:117-123
Scraper Integration
The repository layer is the target for all data collected by the scraper services. It implements incremental saving via callbacks, allowing the system to persist data as it is parsed from supermarket websites rather than waiting for an entire category to finish.
Scrape Persistence Flow
Conclusion
The Data Repository Layer is the structural backbone of the project, transforming raw, heterogeneous data from 10+ countries into a standardized comparison engine. By managing the lifecycle of product mappings and historical prices, it enables the system to provide cost-of-living insights while maintaining the integrity of time-series price data.