E-Commerce — Product Catalog¶
OWNER: aimee (scoping) ALSO_USED_BY: anna (spec), floris, floor (frontend), urszula, maxim (backend) LAST_VERIFIED: 2026-03-26
Overview¶
The product catalog is the core data model of every e-commerce project. Getting the schema right at the start prevents expensive migrations later. This page covers products, categories, variants, inventory, pricing (including EU VAT), search, and image management.
Feature Decomposition¶
Products¶
SCOPE_ITEM: Product data model INCLUDES: name, slug, description (rich text), short description, SKU, status (draft/active/archived), created/updated timestamps OPTIONAL: meta title, meta description (SEO), weight, dimensions, brand, manufacturer COMPLIANCE: Product descriptions must not make misleading claims (EU Unfair Commercial Practices Directive) ESTIMATE_COMPLEXITY: simple
SCOPE_ITEM: Rich text product descriptions INCLUDES: WYSIWYG editor in admin (Tiptap or similar), support for headings, lists, bold/italic, links, embedded images OPTIONAL: Video embeds, size charts, ingredient lists, downloadable PDFs ESTIMATE_COMPLEXITY: simple
SCOPE_ITEM: Product SEO INCLUDES: Custom slug, meta title, meta description, Open Graph image, canonical URL OPTIONAL: Structured data (JSON-LD Product schema), breadcrumb schema COMPLIANCE: Google Merchant Center requirements if client runs Shopping ads ESTIMATE_COMPLEXITY: simple
Categories & Organization¶
SCOPE_ITEM: Category tree INCLUDES: Hierarchical categories (parent/child), name, slug, description, image, sort order OPTIONAL: Multi-level nesting (max 3 levels recommended), featured categories on homepage ESTIMATE_COMPLEXITY: simple
SCOPE_ITEM: Product-category assignment INCLUDES: Many-to-many relationship (product can be in multiple categories), primary category designation ESTIMATE_COMPLEXITY: simple
SCOPE_ITEM: Collections / curated groups INCLUDES: Manual product grouping (e.g., "Summer Sale", "Staff Picks"), independent of category tree OPTIONAL: Auto-collections based on rules (tag, price range, date added) ESTIMATE_COMPLEXITY: normal
SCOPE_ITEM: Tags and attributes INCLUDES: Freeform tags, filterable attributes (material, color, style), attribute types (text, number, boolean, select) OPTIONAL: Attribute templates per product type, custom attribute groups ESTIMATE_COMPLEXITY: normal
Variants¶
SCOPE_ITEM: Product variants INCLUDES: Variant options (size, color, material), each variant has own SKU, price, stock, images OPTIONAL: Variant-specific weight/dimensions, variant comparison table ESTIMATE_COMPLEXITY: normal
CHECK: Variant complexity IF: single axis (size only) → simple select dropdown IF: two axes (size + color) → matrix selector with availability indicators IF: three+ axes → complex, consider product configurator pattern instead
SCOPE_ITEM: Variant option management INCLUDES: Admin UI to define option types (size: S/M/L/XL, color: red/blue/green), auto-generate variant combinations, bulk edit prices/stock OPTIONAL: Option swatches (color circles, pattern thumbnails), option-dependent images ESTIMATE_COMPLEXITY: normal
Database Schema Pattern¶
-- Core catalog tables
products (id, name, slug, description, short_description, sku, status, brand, meta_title, meta_description, created_at, updated_at)
categories (id, parent_id, name, slug, description, image_url, sort_order, created_at)
product_categories (product_id, category_id, is_primary)
product_options (id, product_id, name, position) -- e.g., "Size", "Color"
product_option_values (id, option_id, value, position) -- e.g., "M", "Red"
product_variants (id, product_id, sku, price_cents, compare_at_price_cents, stock_quantity, weight_grams, is_active, created_at)
variant_option_values (variant_id, option_value_id) -- links variant to its option combination
product_images (id, product_id, variant_id, url, alt_text, position, created_at)
product_tags (product_id, tag)
product_attributes (id, product_id, attribute_name, attribute_value, attribute_type)
Inventory¶
SCOPE_ITEM: Basic inventory tracking INCLUDES: Stock quantity per variant, low-stock threshold alerts, out-of-stock handling (hide or show with "notify me") OPTIONAL: Reserved stock (held during checkout for 15 minutes) ESTIMATE_COMPLEXITY: simple
SCOPE_ITEM: Inventory management INCLUDES: Stock adjustments with reason codes (received, returned, damaged, correction), stock history log OPTIONAL: Multi-location inventory, warehouse assignment COMPLIANCE: Stock display must be accurate — showing "in stock" when out of stock violates EU consumer law ESTIMATE_COMPLEXITY: normal
SCOPE_ITEM: Back-in-stock notifications INCLUDES: "Notify me" button on out-of-stock products, email when restocked, auto-expire after 90 days COMPLIANCE: GDPR — email collected for this purpose only, easy unsubscribe ESTIMATE_COMPLEXITY: simple
Pricing¶
SCOPE_ITEM: Basic pricing INCLUDES: Price per variant (stored in cents to avoid floating point), compare-at price (strikethrough), currency code COMPLIANCE: EU price display — always show VAT-inclusive price for B2C. Strikethrough price must be the lowest price in the previous 30 days (EU Omnibus Directive) ESTIMATE_COMPLEXITY: simple
SCOPE_ITEM: EU VAT handling INCLUDES: Standard VAT rate per country (NL 21%, DE 19%, FR 20%, etc.), reduced rates for specific product categories (food, books), VAT calculation at checkout based on shipping destination OPTIONAL: OSS (One-Stop Shop) registration support for cross-border EU sales COMPLIANCE: EU VAT rules — price displayed MUST include VAT. If selling cross-border above EUR 10,000/year to other EU countries, must register for OSS or local VAT in each country ESTIMATE_COMPLEXITY: normal
CHECK: Client sells cross-border within EU? IF: yes, below EUR 10,000 → NL VAT rate applies to all IF: yes, above EUR 10,000 → destination country VAT rate, OSS registration needed IF: no, NL only → 21% standard, 9% reduced (food, books, some services)
SCOPE_ITEM: Sale pricing and scheduling INCLUDES: Sale price with start/end date, automatic activation/deactivation, sale badge on product cards COMPLIANCE: Omnibus Directive — must show lowest price in prior 30 days as reference ESTIMATE_COMPLEXITY: simple
SCOPE_ITEM: Tiered / volume pricing INCLUDES: Price breaks by quantity (buy 5+ get 10% off), displayed on product page OPTIONAL: Customer group pricing (wholesale vs retail) ESTIMATE_COMPLEXITY: normal
SCOPE_ITEM: Multi-currency INCLUDES: Currency selector, prices stored per currency OR auto-converted from base, currency in URL or cookie OPTIONAL: Rounding rules per currency, exchange rate management in admin COMPLIANCE: Displayed price must be the final price charged — no surprise conversion at checkout ESTIMATE_COMPLEXITY: complex
Search & Discovery¶
SCOPE_ITEM: Basic product search INCLUDES: Search bar, PostgreSQL full-text search (tsvector/tsquery), search results page with product cards OPTIONAL: Search suggestions/autocomplete, search analytics (popular terms, zero-result terms) ESTIMATE_COMPLEXITY: simple STACK_REF: wiki/docs/stack/postgresql/index.md
SCOPE_ITEM: Faceted filtering INCLUDES: Filter by category, price range, attributes (color, size, material), active filter display with remove option, filter count indicators OPTIONAL: Filter by rating, availability, brand, custom attributes ESTIMATE_COMPLEXITY: normal
SCOPE_ITEM: Sorting INCLUDES: Sort by relevance (default for search), price low-high, price high-low, newest, name A-Z OPTIONAL: Sort by popularity (requires view/sale tracking), sort by rating ESTIMATE_COMPLEXITY: simple
SCOPE_ITEM: Advanced search (Meilisearch) INCLUDES: Typo tolerance, synonym support, instant search, faceted filtering via search engine, search analytics OPTIONAL: AI-powered semantic search, personalized results COMPLIANCE: Search must be accessible — keyboard navigable, screen reader compatible ESTIMATE_COMPLEXITY: complex STACK_REF: wiki/docs/stack/postgresql/index.md
CHECK: Search tier IF: MVP, <500 products → PostgreSQL full-text search IF: 500-10,000 products → Meilisearch (self-hosted, EU) IF: 10,000+ products or complex search needs → Meilisearch with custom ranking
Image Management¶
SCOPE_ITEM: Product images INCLUDES: Multiple images per product, primary image designation, alt text (accessibility), drag-and-drop reordering OPTIONAL: Variant-specific images, zoom on hover, 360-degree view COMPLIANCE: EAA — all images must have descriptive alt text ESTIMATE_COMPLEXITY: simple
SCOPE_ITEM: Image processing pipeline INCLUDES: Upload to BunnyCDN (or S3-compatible), auto-resize to standard sizes (thumbnail 200px, card 400px, detail 800px, zoom 1600px), WebP conversion, lazy loading OPTIONAL: Image optimization API (Bunny Optimizer), background removal ESTIMATE_COMPLEXITY: normal
CHECK: Image storage IF: MVP → BunnyCDN Storage Zone + Pull Zone (simple, EU-hosted) IF: full → BunnyCDN with Optimizer (auto WebP/AVIF, resize on-the-fly)
Anti-Patterns¶
ANTI_PATTERN: Storing prices as floats FIX: Always store in cents (integer). EUR 19.99 → 1999. Calculate with integers, format for display only.
ANTI_PATTERN: Single product table with JSON variants FIX: Separate product_variants table. JSON columns are not indexable, not filterable, not joinable.
ANTI_PATTERN: Hardcoding VAT rate FIX: VAT rates in config/database table. Rates change (NL went from 19% to 21% in 2012, reduced from 6% to 9% in 2019).
ANTI_PATTERN: No slug uniqueness constraint FIX: Unique index on slug column. Duplicate slugs break routing silently.
ANTI_PATTERN: Unlimited image uploads FIX: Cap at 10-15 images per product. Enforce max file size (5MB) and dimensions (4000px max).
Cross-References¶
READ_ALSO: wiki/docs/archetypes/e-commerce/feature-tree.md READ_ALSO: wiki/docs/archetypes/e-commerce/cart-checkout.md READ_ALSO: wiki/docs/archetypes/e-commerce/compliance.md READ_ALSO: wiki/docs/archetypes/e-commerce/integrations.md READ_ALSO: wiki/docs/stack/drizzle/index.md READ_ALSO: wiki/docs/stack/postgresql/index.md