Migrations & Seeding Data
Relevant source files
The following files were used as context for generating this wiki page:
- scripts/migrate.ts
- scripts/seed.ts
- src/database/seeds/countries.ts
- src/database/seeds/supermarkets.ts
- 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/006_create_scrape_logs.sql
- src/database/migrations/007_create_canonical_products.sql
- src/database/migrations/008_create_users_and_sessions.sql
Migrations and seeding are fundamental to the "WhereIsLifeCheaper" project, providing the structure and initial data required for the multi-country grocery price comparison system. Migrations manage the evolution of the PostgreSQL database schema, while seeding populates the database with essential records such as supported countries, supermarket configurations, and product categories.
The system is designed to facilitate a daily automated scraping workflow. Before scrapers can run, the database must be initialized to define where data is stored and which supermarkets are active. This process ensures consistency across different development and production environments.
Sources: CLAUDE.md:14-23, plan.md:11-20
Database Migration Architecture
The migration system uses sequential SQL files to define and update the PostgreSQL schema. These migrations are executed via a dedicated script that ensures tables, indexes, and constraints are created in the correct order.
Migration Management
Schema changes are managed using the npm run migrate command, which executes the scripts/migrate.ts script. This process creates the foundational core tables: countries, supermarkets, categories, products, product_mappings, prices, and scrape_logs.
Sources: CLAUDE.md:95-98, package.json:14, plan.md:130-132
Core Table Definitions
| Table | Purpose | Key Fields |
|---|---|---|
supermarkets | Stores supermarket chains and scraper configs | country_id, name, scraper_class, scraper_config |
products | Master product catalog with normalized names | name, normalized_name, brand, unit, barcode |
product_mappings | Links products to specific supermarkets | product_id, supermarket_id, external_id, url |
prices | Historical price data tracking | product_mapping_id, price, currency, scraped_at |
canonical_products | Cross-country product identifiers | name, description, category_id |
scrape_logs | Audit trail for scraping runs | supermarket_id, status, products_scraped, duration |
| Sources: src/database/migrations/002_create_supermarkets.sql:4-16, src/database/migrations/004_create_products.sql:4-18, src/database/migrations/005_create_product_mappings_and_prices.sql:6-17, src/database/migrations/006_create_scrape_logs.sql:4-15 |
Entity Relationship Diagram
The following diagram illustrates the relationships between the core database entities established by the migrations.
The database schema uses foreign key constraints (e.g., ON DELETE CASCADE) to maintain referential integrity across related data points.
Sources: src/database/migrations/002_create_supermarkets.sql:6, src/database/migrations/005_create_product_mappings_and_prices.sql:8-9, src/database/migrations/007_create_canonical_products.sql:24
Data Seeding Process
Seeding is the process of populating the database with initial, static data required for the application to function. This is performed using the npm run seed command, which calls scripts/seed.ts.
Seeding Scope
The seeding process typically includes:
- Countries: Definitions for the 10 supported regions (TR, ME, ES, UZ, UA, KZ, DE, MY, AL, AT).
- Categories: Standardized product categories such as Fruits & Vegetables, Dairy, and Meat.
- Supermarkets: Initial configurations for chains like Migros (Turkey), Voli (Montenegro), and Spar (Albania), including their respective scraper class names and base URLs.
Sources: CLAUDE.md:27-35, plan.md:80-92, README.md:144-149
Automated Updates
Both migration and seeding scripts utilize PostgreSQL triggers to maintain metadata, specifically the updated_at column.
CREATE TRIGGER update_products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
Sources: src/database/migrations/004_create_products.sql:22-26, src/database/migrations/007_create_canonical_products.sql:16-20
Performance Optimization
The migrations include specific indexes and constraints to ensure efficient querying during scraping and API access.
Indexing Strategy
- Normalized Names:
idx_products_normalized_nameallows for fast product matching during scraping. - Price History:
idx_prices_mapping_scraped(composite index) optimizes latest price lookups for specific products. - External IDs: A
UNIQUEconstraint on(supermarket_id, external_id)in theproduct_mappingstable prevents duplicate entries for the same product at the same retailer. - Full-Text Search: A GIN index
idx_products_name_searchis applied to the product name field to facilitate rapid searching via the web interface.
Sources: src/database/migrations/004_create_products.sql:31-38, src/database/migrations/005_create_product_mappings_and_prices.sql:15, 52-53
Summary of Operations
To initialize a new environment, developers follow a specific command sequence to prepare the database layer.
The successful execution of migrations and seeds is a prerequisite for running any scraper or starting the API server.
Sources: CLAUDE.md:94-98, package.json:14-15