Database Schema Overview

Relevant source files

The following files were used as context for generating this wiki page:

• src/database/migrations/002_create_supermarkets.sql
• src/database/migrations/003_create_categories.sql
• src/database/migrations/004_create_products.sql
• src/database/migrations/005_create_product_mappings_and_prices.sql
• src/database/migrations/007_create_canonical_products.sql
• src/database/migrations/008_create_users_and_sessions.sql
• CLAUDE.md

The WhereIsLifeCheaper database is a PostgreSQL-based system designed to facilitate multi-country grocery price tracking and comparison. The schema is architected to handle high-frequency time-series price data while maintaining a normalized catalog of products across different supermarket chains and geographical regions.

The core objective of the schema is to bridge the gap between specific supermarket offerings and a "canonical" view of products, enabling users to compare the cost of living by matching similar items (e.g., "1L Milk") across diverse international retailers.

Sources: CLAUDE.md, plan.md

Core Entity Relationship Diagram

The following diagram illustrates the primary relationships between countries, supermarkets, products, and their historical price points.

Diagram shows the flow from regional entities to specific price data points. Sources: src/database/migrations/002_create_supermarkets.sql, src/database/migrations/004_create_products.sql, src/database/migrations/005_create_product_mappings_and_prices.sql, src/database/migrations/007_create_canonical_products.sql

Product Management and Normalization

The system distinguishes between internal supermarket listings and a standardized product catalog. This is achieved through three specific tables: categories, products, and canonical_products.

Categories

The categories table supports a hierarchical structure through a self-referencing parent_id.

Field	Type	Description
id	SERIAL (PK)	Unique identifier for the category.
name	VARCHAR(100)	Localized name of the category.
name_en	VARCHAR(100)	English name for cross-country matching.
parent_id	INTEGER (FK)	Reference to a parent category for hierarchy.

Sources: src/database/migrations/003_create_categories.sql:4-12

Products and Canonical Products

The products table serves as the master catalog. It uses a normalized_name (lowercased, special characters removed) to facilitate automated matching. The canonical_products table allows for human-defined identifiers that link equivalent products across countries.

-- Product structure snippet
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    normalized_name VARCHAR(255),
    canonical_product_id INTEGER REFERENCES canonical_products(id),
    brand VARCHAR(100),
    unit VARCHAR(50),
    unit_quantity DECIMAL(10, 2),
    barcode VARCHAR(50)
);

Sources: src/database/migrations/004_create_products.sql:4-17, src/database/migrations/007_create_canonical_products.sql:19-21

Price Tracking and Scraper Integration

The system tracks specific supermarket listings through product_mappings and their historical prices through the prices table.

Product Mappings

This table links a generic product to a specific URL and external identifier on a supermarket's website.

• external_id: A supermarket-specific unique identifier (e.g., SKU).
• is_available: Tracks whether the item is currently in stock.
• last_scraped_at: Monitoring field for scraper health.

Sources: src/database/migrations/005_create_product_mappings_and_prices.sql:6-16

Price History

The prices table is optimized for time-series analysis. It includes composite indexes on product_mapping_id and scraped_at (DESC) to efficiently retrieve the latest price for any given item.

Field	Type	Description
price	DECIMAL(10, 2)	Current selling price.
original_price	DECIMAL(10, 2)	Price before discounts.
is_on_sale	BOOLEAN	Flag for active promotions.
price_per_unit	DECIMAL(10, 2)	Calculated normalized price (e.g., price per kg).

Sources: src/database/migrations/005_create_product_mappings_and_prices.sql:41-51, src/database/migrations/005_create_product_mappings_and_prices.sql:61-61

Authentication and Infrastructure

User management and session persistence are handled via standard SQL tables to support Google OAuth integration and Express session management.

Users and Sessions

The users table stores profile data and roles, while the sessions table uses the standard schema required by connect-pg-simple.

Key Database Indexes

To maintain performance during scraping and API requests, several critical indexes are implemented:

• idx_products_name_search: GIN index on product names for full-text search.
• idx_prices_mapping_scraped: Composite index for high-speed latest price retrieval.
• idx_supermarkets_scraper_class: Used by the Scraper Factory to identify active scraper targets.
• idx_canonical_products_name: Unique index for standardized product lookups.

Sources: src/database/migrations/002_create_supermarkets.sql:22, src/database/migrations/004_create_products.sql:31, src/database/migrations/005_create_product_mappings_and_prices.sql:61, src/database/migrations/007_create_canonical_products.sql:16

The schema provides a robust foundation for multi-currency, multi-region grocery data aggregation, separating the volatility of scraper-derived data from the consistency of a standardized product catalog.