Pricing History & Affiliates
Relevant source files
The following files were used as context for generating this wiki page:
The Pricing History & Affiliates system is a core component of the ProductCompare platform, responsible for tracking market values, merchant listings, and monetization through affiliate partnerships. It operates within the Pricing and Affiliate context boundaries, providing the data necessary for users to make informed purchasing decisions based on real-time and historical pricing data.
Sources: 2026-03-03-product-compare-backend-mvp-plan.md:12-14, README.md:25-26
Architecture and Data Modeling
The system is built on a PostgreSQL 18 backend using Ecto schemas. It follows a modular monolith architecture where pricing and affiliate logic are separated into dedicated contexts while sharing the same database. Data is exposed via a GraphQL API that utilizes Relay conventions, including global IDs and connections for list surfaces.
Sources: backend_starting_guide.md:6-14, ARCHITECTURE.md:11-13
Database Schema Relationships
The following diagram illustrates the relationships between merchants, products, pricing points, and affiliate structures:
The data model ensures that every price point is linked to a specific merchant's listing of a product, allowing for granular tracking of price fluctuations over time.
Sources: 2026-03-03-product-compare-backend-mvp-plan.md:86-103
Core Components
| Component | Description |
|---|---|
| Merchant | Represents a retail entity (e.g., "ExampleMart") identified by name and domain. |
| MerchantProduct | A specific listing of a product on a merchant's site, uniquely identified by the merchant ID and URL. |
| PricePoint | An append-only record of a product's price at a specific timestamp. |
| AffiliateNetwork | Platforms like Amazon Associates or Impact that manage multiple programs. |
| AffiliateLink | Normalized URLs that include tracking parameters for monetization. |
Sources: 2026-03-03-product-compare-backend-mvp-plan.md:86-103, 2026-03-03-product-compare-backend-mvp-plan.md:162-169
Pricing Workflows
The Pricing context manages the lifecycle of market data, from discovery to historical analysis. The primary workflows include merchant/product upserts and the ingestion of new price points.
Price Point Ingestion
Prices are recorded as append-only PricePoint entries. This preserves the full history of price changes rather than overwriting existing data.
API and Query Logic
The system provides specialized queries for retrieving current market data and historical trends:
latest_price/1: Retrieves the most recentPricePointfor a givenMerchantProductbased on theobserved_attimestamp.price_history/2: Returns a range-bounded list of price points for trend visualization.
Sources: 2026-03-03-product-compare-backend-mvp-plan.md:168-171
Frontend Integration (Product Offers)
The frontend consumes pricing data through GraphQL queries. Specifically, the ProductDetail page fetches active offers and their associated latest prices to display a list of purchasing options.
Offer Data Flow
Data Sanitization
To ensure security and consistency, offer URLs are normalized and validated on the client side before rendering. Only http: and https: protocols are permitted to prevent XSS via javascript: URIs.
Sources: assets/src/routes/products/api.ts:285-298, assets/src/routes/products/tests/detail.route.test.tsx:244-279
Affiliate System Metadata
The Affiliate context manages monetization metadata. While pricing tracks the raw market value, the affiliate system handles the routing and tracking of outbound clicks.
| Field | Type | Description |
|---|---|---|
affiliate_network_id | bigint | Reference to the managing network. |
program_code | string | Unique identifier within the network. |
commission_rate | decimal | The percentage or fixed amount earned per referral. |
coupon_code | string | Associated discount codes for the listing. |
Sources: 2026-03-03-product-compare-backend-mvp-plan.md:100-103, 2026-03-03-product-compare-backend-mvp-plan.md:113-114
Implementation Constraints
- Durable Storage: All pricing facts must persist in PostgreSQL. Ephemeral state (like active scraper sessions) may live in supervised processes, but the price history is permanent.
- Time Precision: All timestamps use
:utc_datetime_usecto ensure high-resolution ordering of price points. - Performance: Indexes are maintained on
price_points(merchant_product_id, observed_at DESC)to ensure fast retrieval of the latest prices and historical ranges.
Sources: backend_starting_guide.md:9, 2026-03-03-product-compare-backend-mvp-plan.md:113-114, backend_starting_guide.md:83-85
Conclusion
The Pricing History & Affiliates module provides a robust foundation for tracking product economics. By utilizing an append-only architecture for price points and a modular context for affiliate management, the system ensures data integrity while remaining extensible for future features like automated scraping and advanced revenue tracking.