Catalog and pricing

The B2B catalog (cahier §4.3, §5.1, §5.2, §15.4 — CAT, PRC). Prisma file: catalog.prisma.

Pricing in V1 (decision of 17 Oct, PRC-01, PRC-02): every ProductVariant holds three HT selling prices, one per CustomerType. The reseller's customerType picks the column; promotions apply on top; guests see no price at all (AUTH-10). There are no segment price lists and no quantity tiers in V1; per-reseller agreements come in V2 (PRC-03).

Advanced filters (CAT-12, CAT-13): categories form a tree (Category.parentId, categories and sub-categories); each category declares its filterable attributes (AttributeDefinition) and each variant stores typed values (VariantAttributeValue). catalog.facets counts products per value with the indexes listed on that table; price-range facets use the caller's price column and are only offered to signed-in resellers.

Stock shown to resellers and guests is only IN_STOCK / OUT_OF_STOCK; quantities stay in the back-office (stock).

CatalogCopy Anchor Link

BrandCopy Anchor Link

A brand distributed by HOCO — HOCO, ACEFAST, BOROFONE — managed as data (CAT-01). Table brands · written by catalog (seeded; admin.brands.crud, BO-29).

• Relations: logoFile → File (logoFileId, onDelete: SetNull).
• Referenced by: Product.brandId, InventorySession.brandId, PromotionScope.brandId.
• Keys & indexes: name unique · slug unique.

Example row:

{
  "id": "01a15db0-5680-7d82-83b0-3aeda0783e3d",
  "name": "HOCO",
  "slug": "hoco",
  "logoFileId": "01a19b48-2c33-72b2-8fe3-139325991f21",
  "description": "Accessoires mobiles : chargeurs, câbles, audio, powerbanks.",
  "sortOrder": 1,
  "isActive": true,
  "archivedAt": null,
  "createdAt": "2026-10-21T09:00:00.000Z",
  "updatedAt": "2026-10-21T09:00:00.000Z"
}

CategoryCopy Anchor Link

Node of the category tree (CAT-02): top-level categories (chargers, cables, earphones, power banks, car holders, audio, watches, accessories) and their sub-categories through parentId (CAT-12 filters by category and sub-category). Table categories · written by catalog (seeded; admin.categories.*, BO-30).

• Relations: parent → Category (parentId, onDelete: Restrict).
• Referenced by: Category.parentId, Product.categoryId, AttributeDefinition.categoryId, InventorySession.categoryId, PromotionScope.categoryId.
• Keys & indexes: slug unique · @@index([parentId, sortOrder]).
• Derived, not stored: product counts per category visible to the caller (catalog.categories) — counted per request through the visibility rules and cached 5 min per segment; descendants of a node — resolved from the cached tree (≈ 50 rows), no closure table.

Constraints and indexes Prisma cannot express (raw SQL in the migration):

ALTER TABLE categories ADD CONSTRAINT categories_not_own_parent CHECK (parent_id <> id);

Example row:

{
  "id": "01a15db0-5680-7eea-aabe-b4ba8c5fd08d",
  "parentId": null,
  "name": "Câbles",
  "nameAr": "كابلات",
  "nameEn": "Cables",
  "slug": "cables",
  "iconKey": "cable",
  "sortOrder": 2,
  "isFeatured": true,
  "isActive": true,
  "archivedAt": null,
  "createdAt": "2026-10-21T09:00:00.000Z",
  "updatedAt": "2026-10-21T09:00:00.000Z"
}

ProductCopy Anchor Link

A catalog reference with the minimum data of cahier §15.4 (CAT-03). Sellable units, prices and stock live on ProductVariant. Table products · written by catalog (admin.products.*, imports); inventory flips OUT_OF_STOCK automatically; analytics refreshes popularityScore.

• Relations: brand → Brand (brandId, onDelete: Restrict) · category → Category (categoryId, onDelete: Restrict) · createdBy → User (createdById, onDelete: SetNull).
• Referenced by: ProductVariant.productId, ProductImage.productId, ProductVisibilityRule.productId, Favorite.productId, OrderItem.productId, PromotionScope.productId.
• Keys & indexes: slug unique · @@unique([brandId, reference]) · @@index([status, categoryId]) · @@index([status, brandId]) · @@index([status, popularityScore(sort: Desc)]) · @@index([status, publishedAt(sort: Desc)]).
• Enums: ProductStatus.
• Lifecycle: DRAFT → ACTIVE ⇄ HIDDEN, ACTIVE ⇄ OUT_OF_STOCK (automatic when every variant's available stock is 0, back on restock), any → ARCHIVED. Only ACTIVE and OUT_OF_STOCK products are visible to resellers (subject to visibility rules); OUT_OF_STOCK ones cannot be added to the cart.
• History: Status changes and edits → AuditLog; price changes are on the variants (PriceChange).
• Derived, not stored: similar and complementary products (CAT-08) — computed from the category at read time; en promotion flag — active Promotion scopes; the caller's price — the variant price of the reseller's customerType, then promotions (null for guests, AUTH-10); availability — IN_STOCK/OUT_OF_STOCK from StockLevel (quantities never leave the back-office, STK-07).

Constraints and indexes Prisma cannot express (raw SQL in the migration):

-- catalog search by name / reference (CAT-07, under 3 s)
CREATE INDEX products_name_trgm ON products USING gin (f_unaccent(name) gin_trgm_ops);
CREATE INDEX products_reference_trgm ON products USING gin (f_unaccent(reference) gin_trgm_ops);

Example row:

{
  "id": "01a18298-9480-70cc-80a7-8ddb761bc4d8",
  "brandId": "01a15db0-5680-7d82-83b0-3aeda0783e3d",
  "categoryId": "01a15db0-5680-7eea-aabe-b4ba8c5fd08d",
  "reference": "X88",
  "name": "Câble X88 USB-A vers USB-C 3 A",
  "nameAr": null,
  "nameEn": "X88 USB-A to USB-C cable 3 A",
  "slug": "hoco-x88-usb-a-usb-c",
  "description": "Câble tressé, charge rapide 3 A, transfert de données.",
  "descriptionAr": null,
  "descriptionEn": null,
  "specs": [
    {
      "key": "length",
      "label": "Longueur",
      "value": "1",
      "unit": "m"
    },
    {
      "key": "current",
      "label": "Intensité max",
      "value": "3",
      "unit": "A"
    },
    {
      "key": "material",
      "label": "Gaine",
      "value": "Nylon tressé"
    }
  ],
  "warrantyMonths": 6,
  "status": "ACTIVE",
  "isFeatured": false,
  "popularityScore": 1840,
  "publishedAt": "2026-10-29T08:00:00.000Z",
  "createdById": "01a19f89-c802-701b-83ec-271e6091a1ed",
  "createdAt": "2026-10-28T13:00:00.000Z",
  "updatedAt": "2026-11-01T07:00:00.000Z"
}

ProductVariantCopy Anchor Link

The sellable unit (SKU): a colour/length/capacity variant of a product, carrying its three selling prices (one per CustomerType), VAT and quantity limits. Every product has at least one (isDefault). Table product_variants · written by catalog (variant data, limits, attribute values); pricing (the price columns, always with a PriceChange).

• Relations: product → Product (productId, onDelete: Cascade).
• Referenced by: ProductImage.variantId, VariantAttributeValue.variantId, PriceChange.variantId, PriceListItem.variantId, StockLevel.variantId, StockMovement.variantId, InventoryLine.variantId, CartItem.variantId, OrderItem.variantId, ReturnItem.variantId, PromotionScope.variantId, PackItem.variantId.
• Keys & indexes: sku unique · barcode unique · @@index([productId, sortOrder]) · @@index([priceSuperGros]) · @@index([priceDemiGros]) · @@index([priceDetail]).
• History: Every change of a selling price, recommendedPrice, purchasePrice or vatRate writes a PriceChange in the same transaction (PRC-01).
• Derived, not stored: the caller's price — the column of the reseller's customerType (SUPER_GROS → priceSuperGros, DEMI_GROS → priceDemiGros, DETAIL → priceDetail), then promotions (PRC-02); guests get null (AUTH-10). TTC = round(HT × (10000 + vatRate) / 10000) at read time.
• Derived, not stored: availability (IN_STOCK / OUT_OF_STOCK) — from StockLevel; the price-range facet uses the column of the caller's type (signed-in callers only, CAT-12).
• The editor warns (no CHECK) when the prices are not ordered priceSuperGros ≤ priceDemiGros ≤ priceDetail, the normal shape of the three tiers. Quantity tiers are not part of V1 (dropped by the 17 Oct decision).

Constraints and indexes Prisma cannot express (raw SQL in the migration):

ALTER TABLE product_variants ADD CONSTRAINT product_variants_qty_rules CHECK (
  min_qty >= 1 AND qty_step >= 1 AND (max_qty_per_order IS NULL OR max_qty_per_order >= min_qty));
ALTER TABLE product_variants ADD CONSTRAINT product_variants_prices CHECK (
  price_super_gros >= 0 AND price_demi_gros >= 0 AND price_detail >= 0
  AND (recommended_price IS NULL OR recommended_price >= 0)
  AND (purchase_price IS NULL OR purchase_price >= 0) AND vat_rate BETWEEN 0 AND 10000);
CREATE UNIQUE INDEX product_variants_one_default ON product_variants (product_id) WHERE is_default;
CREATE INDEX product_variants_sku_trgm ON product_variants USING gin (sku gin_trgm_ops);

Example row:

{
  "id": "01a18298-9480-7f48-8522-52331c7620e9",
  "productId": "01a18298-9480-70cc-80a7-8ddb761bc4d8",
  "sku": "HOCO-X88-C-BK-1M",
  "barcode": "6931474783042",
  "name": "Noir · 1 m",
  "priceSuperGros": 31000,
  "priceDemiGros": 35000,
  "priceDetail": 39000,
  "recommendedPrice": 50000,
  "purchasePrice": 21000,
  "vatRate": 1900,
  "minQty": 5,
  "maxQtyPerOrder": 500,
  "qtyStep": 5,
  "isDefault": true,
  "isActive": true,
  "sortOrder": 0,
  "createdAt": "2026-10-28T13:00:00.000Z",
  "updatedAt": "2026-11-01T07:00:00.000Z"
}

ProductImageCopy Anchor Link

An image of the product gallery, optionally specific to a variant. Table product_images · written by catalog.

• Relations: product → Product (productId, onDelete: Cascade) · variant → ProductVariant (variantId, onDelete: SetNull) · file → File (fileId, onDelete: Restrict).
• Keys & indexes: fileId unique · @@index([productId, sortOrder]).
• Removing an image from the gallery deletes the row (not a business record) and marks the File DELETED.

Example row:

{
  "id": "01a1829d-2860-727f-9b75-049df0576d13",
  "productId": "01a18298-9480-70cc-80a7-8ddb761bc4d8",
  "variantId": null,
  "fileId": "01a19b67-9409-7eb8-9344-665d55c91f89",
  "sortOrder": 0,
  "altText": "Câble HOCO X88 noir",
  "createdAt": "2026-10-28T13:05:00.000Z",
  "updatedAt": "2026-10-28T13:05:00.000Z"
}

ProductComplementCopy Anchor Link

A complementary product set on BO-28 (« Souvent achetés avec », SN-14, NOT-15): listed first in catalog.products.similar and in the product detail's complements, and suggested the day after an order (complement.suggested). Table product_complements · written by catalog (admin.products.update, complementProductIds).

• Relations: product and complementProduct → Product (onDelete: Cascade) · createdBy → User (onDelete: SetNull).
• Keys & indexes: @@unique([productId, complementProductId]) · @@index([productId, position]) · @@index([complementProductId]).

Constraints and indexes Prisma cannot express (raw SQL in the migration):

ALTER TABLE product_complements ADD CONSTRAINT product_complements_not_self
  CHECK (product_id <> complement_product_id);

Filterable attributesCopy Anchor Link

AttributeDefinitionCopy Anchor Link

A filterable product attribute defined for a category (CAT-13) — e.g. power (W), connector, cable length (m), colour, battery capacity (mAh), Bluetooth version. It applies to the products of the category and of its sub-categories. Table attribute_definitions · written by catalog (admin.attributes.crud, BO-71).

• Relations: category → Category (categoryId, onDelete: Restrict).
• Referenced by: VariantAttributeValue.attributeId.
• Keys & indexes: @@unique([categoryId, key]) · @@index([categoryId, sortOrder]).
• Enums: AttributeType (proposed), AttributeFilterWidget (proposed).
• A key defined on a category must not be redefined on one of its sub-categories (service check), so a product inherits at most one definition per key.

Constraints and indexes Prisma cannot express (raw SQL in the migration):

ALTER TABLE attribute_definitions ADD CONSTRAINT attribute_definitions_enum_values
  CHECK ((type = 'ENUM') = (allowed_values IS NOT NULL));

Example row:

{
  "id": "01a17de0-1580-7b30-9ac8-37a0752a1008",
  "categoryId": "01a15db0-5680-7eea-aabe-b4ba8c5fd08d",
  "key": "connector",
  "labelFr": "Connecteur",
  "labelAr": "الموصل",
  "labelEn": "Connector",
  "type": "ENUM",
  "unit": null,
  "allowedValues": [
    {
      "value": "USB_C",
      "labelFr": "USB-C"
    },
    {
      "value": "LIGHTNING",
      "labelFr": "Lightning"
    },
    {
      "value": "MICRO_USB",
      "labelFr": "Micro-USB"
    }
  ],
  "isFilterable": true,
  "filterWidget": "CHECKBOXES",
  "isRequired": true,
  "sortOrder": 1,
  "archivedAt": null,
  "createdAt": "2026-10-27T15:00:00.000Z",
  "updatedAt": "2026-10-27T15:00:00.000Z"
}

VariantAttributeValueCopy Anchor Link

The value of one attribute for one variant — the data behind the advanced filters and their counts (CAT-12, CAT-13). Table variant_attribute_values · written by catalog (variant editor, product import).

• Relations: variant → ProductVariant (variantId, onDelete: Cascade) · attribute → AttributeDefinition (attributeId, onDelete: Restrict).
• Keys & indexes: @@unique([variantId, attributeId]) · @@index([attributeId, valueText]) · @@index([attributeId, valueNumber]) · @@index([attributeId, valueBool]).
• Facet queries (catalog.facets): over the visible variants matching the current filters, GROUP BY attribute_id, value_text (or bucketed value_number, value_bool) with count(DISTINCT product_id); the three (attributeId, value…) indexes serve both the counts and the filters (one EXISTS per selected attribute). Results are cached 5 min per customer type and filter set.
• A product is listed once even when several variants match; it matches a filter when any active variant matches.

Constraints and indexes Prisma cannot express (raw SQL in the migration):

ALTER TABLE variant_attribute_values ADD CONSTRAINT variant_attribute_values_one_value
  CHECK (num_nonnulls(value_text, value_number, value_bool) = 1);

Example row:

{
  "id": "01a18298-9480-7ad0-a909-0f89909d4b39",
  "variantId": "01a18298-9480-7f48-8522-52331c7620e9",
  "attributeId": "01a17de0-1580-7b30-9ac8-37a0752a1008",
  "valueText": "USB_C",
  "valueNumber": null,
  "valueBool": null,
  "createdAt": "2026-10-28T13:00:00.000Z",
  "updatedAt": "2026-10-28T13:00:00.000Z"
}

Visibility and favoritesCopy Anchor Link

ProductVisibilityRuleCopy Anchor Link

Restricts who sees a product, per segment or per reseller (CAT-05). Table product_visibility_rules · written by catalog (admin.products.visibility).

• Relations: product → Product (productId, onDelete: Cascade) · segment → Segment (segmentId, onDelete: Restrict) · reseller → Reseller (resellerId, onDelete: Restrict) · createdBy → User (createdById, onDelete: SetNull).
• Keys & indexes: @@unique([productId, segmentId]) · @@unique([productId, resellerId]) · @@index([resellerId]) · @@index([segmentId]).
• Enums: VisibilityRuleEffect (proposed).
• Rule: a product is visible to reseller R when its status is ACTIVE or OUT_OF_STOCK, no DENY rule matches R (by resellerId or one of R's segments), and either it has no ALLOW rule or one ALLOW rule matches R. Guests see only products without any ALLOW rule. PUT …/visibility replaces the set (link rows; AuditLog keeps the diff).

Constraints and indexes Prisma cannot express (raw SQL in the migration):

ALTER TABLE product_visibility_rules ADD CONSTRAINT product_visibility_rules_one_target
  CHECK (num_nonnulls(segment_id, reseller_id) = 1);

Example row:

{
  "id": "01a1968d-3900-7e3a-a5dd-4d02e54d9a8b",
  "productId": "01a18298-9480-70cc-80a7-8ddb761bc4d8",
  "effect": "DENY",
  "segmentId": null,
  "resellerId": "01a19ad1-9bc2-7660-88e3-2ce0d7290ab6",
  "createdById": "01a1a00a-4535-7ff7-af24-a0a3bb095b70",
  "createdAt": "2026-11-01T10:00:00.000Z"
}

FavoriteCopy Anchor Link

A product bookmarked by a reseller user (CAT-09). Table favorites · written by catalog (favorites.*).

• Relations: user → User (userId, onDelete: Cascade) · product → Product (productId, onDelete: Cascade).
• Keys & indexes: @@id([userId, productId]) · @@index([productId]).
• PUT is an upsert, DELETE removes the row. Favorites of products no longer visible are filtered out at read time, not deleted. Guests have no favorites.

Example row:

{
  "userId": "01a1a077-1080-7aed-87e0-11ae957ae11e",
  "productId": "01a18298-9480-70cc-80a7-8ddb761bc4d8",
  "createdAt": "2026-11-04T19:22:00.000Z"
}

Price historyCopy Anchor Link

PriceChangeCopy Anchor Link

Append-only history of the three selling prices and the other price columns (PRC-01, cahier §8 historique des modifications). Table price_changes · written by pricing (only writer).

• Relations: variant → ProductVariant (variantId, onDelete: Restrict) · importJob → ImportJob (importJobId, onDelete: SetNull) · changedBy → User (changedById, onDelete: SetNull).
• Keys & indexes: @@index([variantId, createdAt(sort: Desc)]) · @@index([field, createdAt]).
• Enums: PriceField (proposed).
• One row per changed column: editing the three prices at once writes three rows sharing createdAt.
• Rows about PURCHASE_PRICE are returned only with the restricted permission pair (ACL-04). V2 adds a nullable priceListId for agreement prices.

Example row:

{
  "id": "01a195e8-6d80-7e56-9fb4-dd41badf333b",
  "variantId": "01a18298-9480-7f48-8522-52331c7620e9",
  "field": "PRICE_DEMI_GROS",
  "oldValue": 36000,
  "newValue": 35000,
  "reason": "Baisse tarifaire novembre",
  "importJobId": null,
  "changedById": "01a1a00a-4535-7ff7-af24-a0a3bb095b70",
  "createdAt": "2026-11-01T07:00:00.000Z"
}

V2 — per-reseller agreementsCopy Anchor Link

Removed from V1 by the 17 Oct decisions (AUTHORING §1b): V1 prices are the three variant columns. The two tables below are the V2 baseline (PRC-03); QuantityTier was dropped altogether.

PriceListCopy Anchor Link

A per-reseller price agreement (or a loyalty-level price list) applied on top of the customer-type prices. Not created in V1: the V1 price is always the variant column of the reseller's customerType. Table price_lists · written by pricing (V2, PRC-03) · V2.

• Relations: createdBy → User (createdById, onDelete: SetNull).
• Referenced by: PriceListItem.priceListId.
• Keys & indexes: code unique · @@index([type, isActive]).
• Enums: PriceListType (proposed), LoyaltyLevel.
• V2 resolution: valid agreement of Reseller.priceListId (V2 column) > level list of the reseller's loyalty level > customer-type price; then promotions.

Constraints and indexes Prisma cannot express (raw SQL in the migration):

ALTER TABLE price_lists ADD CONSTRAINT price_lists_type_level CHECK ((type = 'LEVEL') = (loyalty_level IS NOT NULL));
ALTER TABLE price_lists ADD CONSTRAINT price_lists_validity CHECK (valid_to IS NULL OR valid_from IS NULL OR valid_to > valid_from);

Example row:

{
  "id": "01a49ff6-8e80-799f-881b-1cb7d6cad621",
  "code": "ACC-REV-000123",
  "name": "Accord Boutique Amine Mobile 2027",
  "type": "AGREEMENT",
  "loyaltyLevel": null,
  "priority": 10,
  "validFrom": "2027-04-01T00:00:00.000Z",
  "validTo": null,
  "isActive": true,
  "archivedAt": null,
  "note": null,
  "createdById": "01a1a00a-4535-7ff7-af24-a0a3bb095b70",
  "createdAt": "2027-04-01T09:00:00.000Z",
  "updatedAt": "2027-04-01T09:00:00.000Z"
}

PriceListItemCopy Anchor Link

The price of one variant in a V2 agreement: a fixed price or a discount off the customer-type price. Table price_list_items · written by pricing (V2) · V2.

• Relations: priceList → PriceList (priceListId, onDelete: Cascade) · variant → ProductVariant (variantId, onDelete: Restrict).
• Keys & indexes: @@unique([priceListId, variantId]) · @@index([variantId]).

Constraints and indexes Prisma cannot express (raw SQL in the migration):

ALTER TABLE price_list_items ADD CONSTRAINT price_list_items_one_rule CHECK (
  num_nonnulls(price, discount_bp) = 1
  AND (price IS NULL OR price >= 0)
  AND (discount_bp IS NULL OR discount_bp BETWEEN 1 AND 10000));

Example row:

{
  "id": "01a49ffb-2260-7027-9751-59308e3ab3e9",
  "priceListId": "01a49ff6-8e80-799f-881b-1cb7d6cad621",
  "variantId": "01a18298-9480-7f48-8522-52331c7620e9",
  "price": 33000,
  "discountBp": null,
  "createdAt": "2027-04-01T09:05:00.000Z",
  "updatedAt": "2027-04-01T09:05:00.000Z"
}