BUY-3254-technical-design

BUY-3254: GET /v1/products/{sku} — Technical Design

Product detail endpoint with price variants and merchant links

Overview

This document provides the technical design for the GET /v1/products/{sku} endpoint. It includes Pydantic schema definitions, OpenAPI YAML additions, and implementation notes for the engineering team.

New Schemas (Pydantic)

Add to app/schemas/product.py:

class PriceVariant(BaseModel):
    """A price variant (color, size, storage option) for a product."""
    variant_id: str = Field(..., description="Unique variant identifier (SKU + variant suffix)")
    variant_name: str = Field(..., description="Human-readable variant name")
    price: Decimal = Field(..., description="Variant price")
    currency: str = Field(..., description="ISO currency code")
    stock_status: str = Field(..., description="Stock level: in_stock, low_stock, out_of_stock, pre_order")
    specs: Dict[str, Any] = Field(default_factory=dict, description="Variant-specific specs (color, storage, size)")

    model_config = {"from_attributes": True}


class MerchantLink(BaseModel):
    """An affiliate link for a product on a specific platform/merchant."""
    source: str = Field(..., description="Platform source (e.g., shopee_sg, lazada_sg)")
    merchant_id: str = Field(..., description="Merchant/store identifier")
    merchant_name: str = Field(..., description="Display name of merchant/store")
    price: Decimal = Field(..., description="Price on this platform")
    currency: str = Field(..., description="Currency code")
    buy_url: str = Field(..., description="Direct purchase URL on this platform")
    affiliate_url: Optional[str] = Field(None, description="BuyWhere tracked affiliate link")
    is_available: bool = Field(..., description="Currently in stock on this platform")
    rating: Optional[Decimal] = Field(None, description="Product rating on this platform (0-5)")
    review_count: Optional[int] = Field(None, description="Number of reviews on this platform")
    last_checked: Optional[datetime] = Field(None, description="ISO timestamp of last availability check")

    model_config = {"from_attributes": True}


class ProductDetailMeta(BaseModel):
    """Metadata about the product detail response."""
    total_merchants: int = Field(..., description="Number of platforms selling this SKU")
    total_variants: int = Field(..., description="Number of price variants")
    cheapest_price: Optional[Decimal] = Field(None, description="Lowest price across all merchants")
    cheapest_source: Optional[str] = Field(None, description="Platform with lowest price")
    lowest_rating: Optional[Decimal] = Field(None, description="Lowest rating across merchants")
    data_freshness: str = Field(..., description="fresh, recent, stale, or very_stale")


class ProductDetailResponse(BaseModel):
    """Response for GET /v1/products/{sku}."""
    product: ProductResponse = Field(..., description="Primary product details")
    price_variants: List[PriceVariant] = Field(default_factory=list, description="Available price variants")
    merchant_links: List[MerchantLink] = Field(default_factory=list, description="Affiliate links across all merchants")
    meta: ProductDetailMeta = Field(..., description="Response metadata")

    model_config = {"from_attributes": True}

OpenAPI Additions

Add to docs/api/openapi.yaml under paths:

  /v1/products/{sku}:
    get:
      tags:
        - products
      summary: Get product by SKU with price variants and merchant links
      operationId: get_product_by_sku_v1_products__sku__get
      security:
        - HTTPBearer: []
      parameters:
        - name: sku
          in: path
          required: true
          schema:
            type: string
            description: Product SKU from source platform
            example: IPHONE15PRO256BLK
          description: Product SKU from source platform
        - name: currency
          in: query
          required: false
          schema:
            anyOf:
              - type: string
              - type: 'null'
            description: 'Target currency for price conversion. Supported: SGD, USD, AUD, EUR, GBP, MYR, IDR, THB, PHP, VND'
            title: Currency
          description: 'Target currency for price conversion. Supported: SGD, USD, AUD, EUR, GBP, MYR, IDR, THB, PHP, VND'
        - name: include_variants
          in: query
          required: false
          schema:
            type: boolean
            default: true
            description: Include price variants in response
          description: Include price variants in response
        - name: include_merchant_links
          in: query
          required: false
          schema:
            type: boolean
            default: true
            description: Include merchant/affiliate links in response
          description: Include merchant/affiliate links in response
      responses:
        '200':
          description: Successful Response
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ProductDetailResponse'
        '404':
          description: Product not found
          content:
            application/json:
              schema:
                type: object
                properties:
                  error:
                    type: object
                    properties:
                      code:
                        type: string
                        example: PRODUCT_NOT_FOUND
                      message:
                        type: string
                        example: 'No product found with SKU: {sku}'
                      details:
                        type: object
                        properties:
                          sku:
                            type: string
                          suggestion:
                            type: string
        '422':
          description: Validation Error
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/HTTPValidationError'

Add to components/schemas in OpenAPI:

    PriceVariant:
      type: object
      properties:
        variant_id:
          type: string
          description: Unique variant identifier
        variant_name:
          type: string
          description: Human-readable variant name
        price:
          type: string
          description: Variant price
        currency:
          type: string
          description: ISO currency code
        stock_status:
          type: string
          enum: [in_stock, low_stock, out_of_stock, pre_order]
        specs:
          type: object
          additionalProperties: true
          description: Variant-specific specs
      required: [variant_id, variant_name, price, currency, stock_status]

    MerchantLink:
      type: object
      properties:
        source:
          type: string
          description: Platform source
        merchant_id:
          type: string
        merchant_name:
          type: string
        price:
          type: string
        currency:
          type: string
        buy_url:
          type: string
        affiliate_url:
          type: string
        is_available:
          type: boolean
        rating:
          type: string
        review_count:
          type: integer
        last_checked:
          type: string
          format: date-time
      required: [source, merchant_id, merchant_name, price, currency, buy_url, is_available]

    ProductDetailMeta:
      type: object
      properties:
        total_merchants:
          type: integer
        total_variants:
          type: integer
        cheapest_price:
          type: string
        cheapest_source:
          type: string
        lowest_rating:
          type: string
        data_freshness:
          type: string
          enum: [fresh, recent, stale, very_stale]
      required: [total_merchants, total_variants, data_freshness]

    ProductDetailResponse:
      type: object
      properties:
        product:
          $ref: '#/components/schemas/ProductResponse'
        price_variants:
          type: array
          items:
            $ref: '#/components/schemas/PriceVariant'
        merchant_links:
          type: array
          items:
            $ref: '#/components/schemas/MerchantLink'
        meta:
          $ref: '#/components/schemas/ProductDetailMeta'
      required: [product, meta]

Database Query

The endpoint should query products by SKU:

# Query primary product by SKU
result = await db.execute(
    select(Product).where(Product.sku == sku, Product.is_active == True)
)
product = result.scalar_one_or_none()

# Query all products with same SKU (different sources/merchants)
all_listings = await db.execute(
    select(Product).where(Product.sku == sku, Product.is_active == True)
)
all_products = all_listings.scalars().all()

Implementation Notes

Primary product — The product field uses the listing with the lowest price as the primary product, or the first active listing if prices are equal.
Price variants — Derived from metadata_ JSONB field. If metadata_.has_variants is true, parse variant information from metadata_.variants array.
Merchant links — Built from all_products list. Each product becomes a MerchantLink with its affiliate URL generated via get_affiliate_url().
Meta aggregation — Compute total_merchants, cheapest_price, cheapest_source, and lowest_rating from the full all_products list.
Caching — Cache key format: products:sku:{sku}:{currency}:{include_variants}:{include_merchant_links}. TTL: 300 seconds (5 minutes).
Query params — When include_variants=false, return empty price_variants array. When include_merchant_links=false, return empty merchant_links array.

Related Files

Router: app/routers/products.py
Schemas: app/schemas/product.py
OpenAPI: docs/api/openapi.yaml
Documentation: docs/api-reference/products-sku-detail.md