Product Data Model

aplt_products Table

The master product table stores multi-language, multi-supplier product data:

Identification

Column	Type	Example
`id`	serial	Auto-increment primary key
`sku`	varchar	`376-00` (base SKU)
`variant_code`	varchar	`376-00.001` (full variant)
`supplier_code`	varchar	`01`, `04`, `05`
`articlenumber`	varchar	Supplier article number

Naming (Multi-language)

Column	Language
`name` / `name_nl` / `name_de` / `name_en`	Product name
`description` / `description_nl` / `description_de` / `description_en`	Description
`variant_name_nl` / `variant_name_de` / `variant_name_en`	Variant name
`model_name_nl` / `model_name_de` / `model_name_en`	Model name
`material` / `material_nl` / `material_de` / `material_en`	Material
`color` / `color_nl` / `color_de` / `color_en`	Color
`packaging_nl` / `packaging_de` / `packaging_en`	Packaging
`size_nl` / `size_de` / `size_en`	Size

Physical Properties

Column	Type	Description
`dimensions`	varchar	Formatted dimensions string
`length`	decimal	Length (mm/cm)
`width`	decimal	Width
`height`	decimal	Height
`diameter`	decimal	Diameter
`weight`	decimal	Weight (grams)
`colli_weight`	decimal	Colli/package weight

Trade & Import

Column	Description
`ean`	EAN barcode
`customs_tariff`	HS code
`country_of_origin`	ISO country code
`tax`	Tax rate
`packaging_unit`	Packaging unit quantity
`min_order_quantity`	Minimum order quantity
`delivery_time`	Delivery time info

Categorization

Column	Description
`brand`	Product brand
`model`	Model identifier
`category`	Primary category
`category_id`	Category ID reference
`subcategory`	Sub-category
`master_category_code`	Master category code
`original_category`	Original supplier category

Tiered Pricing

Retail Price	Buying Price	Quantity
`retail_price_100`	`buying_price_100`	100 units
`retail_price_200`	`buying_price_200`	200 units
`retail_price_300`	`buying_price_300`	300 units
`retail_price_500`	`buying_price_500`	500 units
`retail_price_1000`	`buying_price_1000`	1000 units

Column	Description
`price_from`	Starting price

Images & Media

Column	Description
`image_1` through `image_5`	Image URLs/paths
`image_url`	Primary image URL
`model_image`	Model/lifestyle image
`media_gallery`	Additional media gallery data
`svg_path`	SVG file path prefix
`show_all_positions`	Show all print positions in images

Print & Decoration

Column	Description
`techniques`	Available print techniques
`print_option_group`	Print option group
`must_have_imprint`	Requires imprint/decoration
`maxcolors`	Maximum print colors

Search & Filtering

Column	Description
`color_id`	Color identifier for filtering
`search_color`	Normalized search color
`filter_codes`	Filter code values
`popularity`	Popularity score
`is_eco_friendly`	Eco-friendly flag
`meta_keyword`	Meta keywords for search

Timestamps

Column	Description
`created_at`	Record creation timestamp
`updated_at`	Last update timestamp

aplt_techniques Table

Product decoration/print techniques:

Core Fields

Column	Description
`id`	Primary key
`product_sku`	FK to aplt_products
`product_size`	Product size variant
`technique_name`	e.g., “Borduren”, “Zeefdruk”
`technique_code`	Technique identifier code
`position` / `position_nl` / `position_de` / `position_en`	Position description (multi-language)
`max_colors`	Maximum print colors
`print_area`	Print area description
`print_area_shape`	Shape of print area
`print_area_group`	Print area group identifier
`print_base_color`	Base color of the print area
`is_active`	Whether technique is active

Coordinates & Dimensions

Column	Description
`x_position_1` through `x_position_5`	X coordinates per area
`y_position_1` through `y_position_5`	Y coordinates per area
`position_count`	Number of print positions
`position_1_width` / `position_1_height`	Area 1 dimensions
`centroid_x_1` / `centroid_y_1`	Area 1 centroid
`position_2_width` / `position_2_height`	Area 2 dimensions
`centroid_x_2` / `centroid_y_2`	Area 2 centroid
`position_3_width` / `position_3_height`	Area 3 dimensions
`centroid_x_3` / `centroid_y_3`	Area 3 centroid
`position_4_width` / `position_4_height`	Area 4 dimensions
`position_5_width` / `position_5_height`	Area 5 dimensions
`coordinate_unit`	Unit of measurement for coordinates
`rotation`	Rotation angle

Pricing

Column	Description
`setup_cost`	One-time setup cost
`cost_per_color`	Cost per additional color
`cost_per_thousand`	Cost per thousand units
`notes`	Additional notes

Timestamps

Column	Description
`created_at`	Record creation timestamp

SKU & Variant Convention (All Suppliers)

Critical: The sku, variant_code, and model fields have specific meanings:

Field	Meaning	Examples
`sku`	Base product / model code	`376-00` (Spranz), `P436` (XD), `029030` (NewWave)
`variant_code`	Unique variant identifier (color/size combo)	`376-00.001`, `P436.055`, `029030-00-3`
`model`	Product grouping key for Medusa sync	Same as sku for most, or supplier-specific model code

variant_code is the primary identifier per variant row in aplt_products. The upsertProduct() function uses variant_code + supplier_code as unique key.

Spranz-specific Convention

Common Bug: If sku contains a variant suffix (e.g., 1701-00.001), the designer creates double suffixes (1701-00.001.001.svg). Fix: correct the database to sku = '1701-00', variant_code = '1701-00.001'.

SVG File Naming

Variant SVG: {variant_code}.svg (e.g., 376-00.001.svg)
Base SVG: {sku}.svg (e.g., 376-00.svg)
Path: /svg/spranz/{variant_code}.svg
Designer lookup order: variant-specific first, then base SKU fallback