Product Matching & Canonical Products

Relevant source files

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

• src/services/ProductService.ts
• src/database/migrations/007_create_canonical_products.sql
• src/api/routes/canonical.ts
• frontend/src/pages/admin/Mapping.tsx
• plan.md
• CLAUDE.md

The Product Matching & Canonical Products system is a core component of the "Where Is Life Cheaper" platform designed to unify disparate product data from multiple international supermarkets. Its primary purpose is to allow for direct price comparisons of equivalent items across different countries and currencies.

This module achieves data consistency by mapping specific supermarket items (which may have varying names, languages, and units) to a "Canonical Product"—a shared, user-defined identifier. This architecture enables the system to aggregate historical price data and generate cross-country price comparisons for standardized goods like "Milk 1L" or "White Bread 500g".
Sources: plan.md:14-25, CLAUDE.md:144-150, src/database/migrations/007_create_canonical_products.sql:4-15

Data Architecture

The system uses a hierarchical data model to manage the relationship between raw scraped data and standardized comparison units.

Entity Relationships

The core of the matching system relies on three primary entities:

1. Products: The master catalog entries containing normalized names, brands, and units.
2. Product Mappings: Links between the master product and a specific supermarket's external ID or URL.
3. Canonical Products: Shared identifiers that group one or more products for cross-country comparison.

Matching Logic & Workflow

The system employs a multi-tiered matching strategy to resolve products during the scraping and ingestion process.

Automated Matching Strategy

When a new item is scraped, the ProductService attempts to identify an existing product record using the following priority:

1. External ID Match: Checking if the supermarket's external_id (e.g., SKU or barcode) already exists in product_mappings.
2. URL Match: Matching the normalized product URL if the external ID is unavailable.
3. Name & Brand Match: A fallback using a composite key of the normalized_name and brand.

Canonical Linking

While product matching identifies the same item within or across supermarkets, Canonical Linking is the manual or semi-automated process of grouping items for the comparison engine.

Feature	Description
Manual Mapping UI	A React-based interface for administrators to search and link raw products to canonical entries.
Bilingual Search	Uses an translation API to match English search terms with local language product names (e.g., Turkish or Spanish).
Per-Unit Pricing	A toggle on canonical products to calculate and display prices in €/kg or €/L instead of per-package.
Disabling	The ability to hide specific canonical products from the public comparison dashboard.

Sources: frontend/src/pages/admin/Mapping.tsx:15-135, src/api/routes/canonical.ts:240-270, plan.md:154-165

Implementation Details

Product Normalization

Before matching, product names are processed to ensure high confidence in text-based comparison. The normalization includes:

• Converting to lowercase.
• Removing special characters.
• Normalizing whitespace.
• Decoding URI components and normalizing to NFC form.

// Example from ProductService normalization logic
private normalizeExternalId(externalId?: string): string | undefined {
    if (!externalId) return undefined;
    const trimmed = externalId.trim();
    try {
        return decodeURIComponent(trimmed).normalize('NFC').toLowerCase();
    } catch {
        return trimmed.normalize('NFC').toLowerCase();
    }
}

Sources: src/services/ProductService.ts:32-43, plan.md:320-327

API Endpoints for Canonical Data

The backend provides several REST endpoints to manage the matching lifecycle.

Endpoint	Method	Description
/api/canonical	GET	List all available canonical products.
/api/canonical/link	PUT	Link a specific product ID to a canonical_product_id.
/api/canonical/comparison	GET	Aggregated price comparison grouped by canonical product.
/api/canonical/:id	PATCH	Update canonical product settings (e.g., disabled, show_per_unit_price).
/api/canonical/products-by-country/:id	GET	List raw products for a country with their current mapping status.

Sources: src/api/routes/canonical.ts:50-55, src/api/routes/canonical.ts:211-235, src/api/routes/canonical.ts:240-258, plan.md:175-185

Price Aggregation

The comparison engine (accessible via /api/canonical/comparison) performs heavy aggregation. It groups multiple supermarket mappings under a single canonical ID and calculates the average price per country. If show_per_unit_price is enabled, the system derives the price per kilogram or liter based on the unit_quantity and unit fields stored in the product record.
Sources: src/api/routes/canonical.ts:98-175, src/services/ProductService.ts:139-245

The system is designed for high scalability, supporting bulk operations in ProductService where hundreds of items are deduplicated and mapped in a single transaction during the daily scraping cycle.
Sources: src/services/ProductService.ts:153-220