HOCO B2B
SRSDatabase design

Promotions and loyalty

Promotions with scope, audience, schedule, cap and stacking, banners; V2 packs and the loyalty programme.

Commercial offers (cahier §4.6, §5.5 — PRM) and the V2 loyalty programme (LOY). Prisma file: promotions.prisma (its V2 models arrive with the P4 migrations).

A Promotion is one discount mechanic (PromotionType) with a schedule, a scope (what it applies to: products, variants, categories with their sub-categories, brands) and an audience (who sees it: wilaya, segment, customer type, loyalty level, reseller, purchase volume). It applies on top of the reseller's customer-type price; guests see the offers without any price (AUTH-10). The order keeps what was applied (Order.appliedPromotions, OrderItem.promotionIds), so a promotion edited later never changes a placed order.

Packs and loyalty are V2 (cahier §14.2): their tables are the baseline for the V2 contract and are not created by the V1 migration. Change request 04 (not yet accepted or priced by HOCO) adds the models below and turns levels into configured data. The enum LoyaltyLevel of V1 is gone: migration 20261107090000_loyalty_core turned PromotionAudience.loyaltyLevel into levelId, keeping each row's target through its code, and the contract's segment criteria take level ids. PriceList.loyaltyLevel does not exist yet and will refer to LoyaltyLevelDefinition when per-reseller price lists are built (SRV-45).

Promotions and banners

Promotion

A discount mechanic with a schedule, a target, a stock cap and stacking rules (PRM-01…04): daily offers, scheduled promotions, temporary discounts. Table promotions · written by promotions (admin.promotions.*, BO-49/50; status job); the cart engine applies it.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
codeString @db.VarChar(40)——Unique. Internal code (not a coupon typed by resellers).
nameString——The label shown to resellers (API label, ≤ 60).
internalNameStringyes—Back-office name (API name, BO-50 Nom interne, SRV-42); null = name. Backfilled from name.
nameArStringyes——
descriptionStringyes——
typePromotionType———
valueIntyes—Applied on top of the reseller's customer-type price (PRC-02). PERCENT_OFF: basis points; AMOUNT_OFF: centimes HT off the unit price; FIXED_PRICE: promotional unit price, centimes HT, for every customer type unless the three fixedPrice* columns are set; FREE_DELIVERY: null.
fixedPriceSuperGros · fixedPriceDemiGros · fixedPriceDetailIntyes—FIXED_PRICE with one unit price HT per customer type (SRV-41); all three or none (CHECK, each > 0); they override value.
statusPromotionStatus—DRAFT—
statusReasonString @db.VarChar(300)yes—Why it was paused or ended early (DES-02); cleared by the other moves.
isDailyOfferBoolean—falseOffre du jour carousel with countdown to endsAt (PRM-05).
isFlashSaleBoolean—falseFlash sale (PRM-07, SN-04): 2 h to 48 h (checked by the API), never a daily offer (CHECK); « Ventes flash » tab and flash_sale.started.
startsAtDateTime———
endsAtDateTime——Exclusive.
priorityInt—0Higher is applied first (PRM-03).
isStackableBoolean—falseNon-stackable: the engine keeps the best non-stackable promotion per line; stackable ones are then applied in priority order on the remaining price.
minOrderAmountIntyes—Cart subtotal HT required (mandatory for FREE_DELIVERY).
minLineQtyIntyes—Line quantity required for the discount to apply.
stockCapIntyes—Maximum units sold under the promotion, all resellers (stock de la promotion).
usedQuantityInt—0Units consumed by submitted orders; released when they are cancelled or refused.
perResellerCapIntyes—Maximum units one reseller buys under the promotion over its life (BO-50 Limite par revendeur, SRV-42); CHECK ≥ 1. Consumed in PromotionResellerUsage.
notifyResellersBoolean—falseBO-50 Notifier les revendeurs: a plain promotion emits promotion.started at activation only when set (daily offers and flash sales always announce themselves). Rows created before SRV-42 are backfilled to true (they always notified).
imageFileIdString @db.Uuidyes—FK → File · SetNull. Card image (BANNER_IMAGE).
createdByIdString @db.Uuidyes—FK → User · SetNull.
createdAtDateTime—now()—
updatedAtDateTime—@updatedAt—
  • Relations: imageFile → File (imageFileId, onDelete: SetNull) · createdBy → User (createdById, onDelete: SetNull).
  • Referenced by: PromotionScope.promotionId, PromotionAudience.promotionId, Banner.promotionId.
  • Keys & indexes: code unique · @@index([status, startsAt, endsAt]) · @@index([isDailyOffer, status]) · @@index([isFlashSale, status]).
  • Enums: PromotionType, PromotionStatus.
  • Lifecycle: DRAFT → SCHEDULED → ACTIVE ⇄ PAUSED → ENDED → ARCHIVED; the scheduler moves SCHEDULED → ACTIVE at startsAt and ACTIVE → ENDED at endsAt or when usedQuantity reaches stockCap.
  • Derived, not stored: remaining cap = stockCap − usedQuantity (promotions.get); countdown = endsAt − now().
  • Cap concurrency: submission runs UPDATE promotions SET used_quantity = used_quantity + $q WHERE id = $id AND (stock_cap IS NULL OR used_quantity + $q <= stock_cap); 0 rows → checkout answers 409 CART_CHANGED with the cart re-priced without the promotion. Caps are released when the order is refused or cancelled.
  • Per-reseller limit: the cart prices at most perResellerCap − PromotionResellerUsage.usedQuantity units at the promotional price for the reseller (the rest at the type price, reason RESELLER_CAP_REACHED once nothing is left); submission upserts the usage row under the same guard (… ON CONFLICT DO UPDATE … WHERE used_quantity + $q <= per_reseller_cap), 0 rows → 409 CART_CHANGED; refusal and cancellation give the units back.
  • Scope and audience: no PromotionScope row = whole catalog (only allowed for FREE_DELIVERY); no PromotionAudience row = every reseller. Guests see promotions without prices or discounted amounts (AUTH-10).

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

ALTER TABLE promotions ADD CONSTRAINT promotions_schedule CHECK (ends_at > starts_at);
ALTER TABLE promotions ADD CONSTRAINT promotions_value CHECK (
  (type = 'PERCENT_OFF' AND value BETWEEN 1 AND 10000)
  OR (type IN ('AMOUNT_OFF', 'FIXED_PRICE') AND value >= 0)
  OR (type = 'FREE_DELIVERY' AND value IS NULL AND min_order_amount IS NOT NULL));
ALTER TABLE promotions ADD CONSTRAINT promotions_cap CHECK (
  used_quantity >= 0 AND (stock_cap IS NULL OR used_quantity <= stock_cap));

Example row:

{
  "id": "01a19656-4a80-7734-8064-1c5352836363",
  "code": "NOV-CABLES-10",
  "name": "-10 % sur les câbles",
  "nameAr": null,
  "description": "Sur tous les câbles HOCO, BOROFONE et ACEFAST jusqu'au 30 novembre.",
  "type": "PERCENT_OFF",
  "value": 1000,
  "status": "ACTIVE",
  "isDailyOffer": false,
  "startsAt": "2026-11-01T00:00:00.000Z",
  "endsAt": "2026-12-01T00:00:00.000Z",
  "priority": 10,
  "isStackable": false,
  "minOrderAmount": null,
  "minLineQty": null,
  "stockCap": 5000,
  "usedQuantity": 1260,
  "imageFileId": null,
  "createdById": "01a1a00a-4535-7ff7-af24-a0a3bb095b70",
  "createdAt": "2026-11-01T09:00:00.000Z",
  "updatedAt": "2026-11-08T14:00:00.000Z"
}

PromotionResellerUsage

Units of a promotion already bought by one reseller, for Promotion.perResellerCap (SRV-42). Table promotion_reseller_usages · written by orders (submission, refusal, cancellation, expiry) · read by the pricing engine for a reseller caller.

FieldPrisma typeNullDefaultConstraints / notes
promotionIdString @db.Uuid——PK (with resellerId). FK → Promotion · Cascade.
resellerIdString @db.Uuid——PK (with promotionId). FK → Reseller · Cascade.
usedQuantityInt—0CHECK ≥ 0. Units of the reseller's submitted orders under the promotion, released like Promotion.usedQuantity.
updatedAtDateTime—@updatedAt—
  • Keys & indexes: @@id([promotionId, resellerId]) · @@index([resellerId]).
  • Rows exist only for promotions with a perResellerCap (created at the first submission).

PromotionScope

What a promotion applies to: a product, a variant, a category (with its descendants) or a brand. Table promotion_scopes · written by promotions.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
promotionIdString @db.Uuid——FK → Promotion · Cascade.
productIdString @db.Uuidyes—FK → Product · Restrict.
variantIdString @db.Uuidyes—FK → ProductVariant · Restrict.
categoryIdString @db.Uuidyes—FK → Category · Restrict.
brandIdString @db.Uuidyes—FK → Brand · Restrict.
createdAtDateTime—now()—
  • Relations: promotion → Promotion (promotionId, onDelete: Cascade) · product → Product (productId, onDelete: Restrict) · variant → ProductVariant (variantId, onDelete: Restrict) · category → Category (categoryId, onDelete: Restrict) · brand → Brand (brandId, onDelete: Restrict).
  • Keys & indexes: @@index([promotionId]) · @@index([productId]) · @@index([variantId]) · @@index([categoryId]) · @@index([brandId]).
  • Rows are ORed: a line is in scope when any row matches it. Editable only while the promotion is DRAFT or SCHEDULED (link rows, replaced as a set).

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

ALTER TABLE promotion_scopes ADD CONSTRAINT promotion_scopes_one_target
  CHECK (num_nonnulls(product_id, variant_id, category_id, brand_id) = 1);

Example row:

{
  "id": "01a19656-4a80-792b-a1f9-2482abd23eb7",
  "promotionId": "01a19656-4a80-7734-8064-1c5352836363",
  "productId": null,
  "variantId": null,
  "categoryId": "01a15db0-5680-7eea-aabe-b4ba8c5fd08d",
  "brandId": null,
  "createdAt": "2026-11-01T09:00:00.000Z"
}

PromotionAudience

Who a promotion targets: wilaya, segment, customer type, loyalty level, a single reseller or a minimum purchase volume (PRM-02, cahier §4.6). Table promotion_audiences · written by promotions.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
promotionIdString @db.Uuid——FK → Promotion · Cascade.
wilayaCodeString @db.VarChar(2)yes—FK → Wilaya.code · Restrict.
segmentIdString @db.Uuidyes—FK → Segment · Restrict.
customerTypeCustomerTypeyes—Targets one pricing tier (e.g. a Détail-only offer). Addition to the cahier list, natural with the three prices.
levelIdString @db.Uuidyes—FK → LoyaltyLevelDefinition · Restrict. Matches while loyalty.enabled is on; ignored while it is off, as in V1.
resellerIdString @db.Uuidyes—FK → Reseller · Restrict.
minVolumeIntyes—Minimum HT amount of delivered orders over volumePeriodDays.
volumePeriodDaysIntyes——
createdAtDateTime—now()—
  • Relations: promotion → Promotion (promotionId, onDelete: Cascade) · wilaya → Wilaya (wilayaCode, onDelete: Restrict) · segment → Segment (segmentId, onDelete: Restrict) · reseller → Reseller (resellerId, onDelete: Restrict).
  • Keys & indexes: @@index([promotionId]).
  • Enums: CustomerType.
  • API: audience.loyaltyLevels answers level ids. A V1 client may still send a level code (BRONZE): it is resolved to its id on write.
  • Matching: rows of the same kind are ORed, kinds are ANDed — e.g. (wilaya 16 or 09) and segment Grossistes.

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

ALTER TABLE promotion_audiences ADD CONSTRAINT promotion_audiences_one_criterion CHECK (
  num_nonnulls(wilaya_code, segment_id, customer_type, level_id, reseller_id, min_volume) = 1
  AND ((min_volume IS NULL) = (volume_period_days IS NULL)));

Example row:

{
  "id": "01a19656-4a80-75d6-98bb-3c5fb14c0851",
  "promotionId": "01a19656-4a80-7734-8064-1c5352836363",
  "wilayaCode": "16",
  "segmentId": null,
  "customerType": null,
  "levelId": null,
  "resellerId": null,
  "minVolume": null,
  "volumePeriodDays": null,
  "createdAt": "2026-11-01T09:00:00.000Z"
}

A home or promotions banner (PRM-05), optionally linked to a promotion, targeted by wilaya and segment. Table banners · written by promotions (admin.banners.crud, BO-51).

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
titleString———
subtitleStringyes——
imageFileIdString @db.Uuid——FK → File · Restrict. Purpose BANNER_IMAGE (public CDN).
imageMobileFileIdString @db.Uuidyes—FK → File · SetNull. BANNER_IMAGE sized for phones (BO-51 Image mobile); the reseller app gets it instead of imageFileId when set (SRV-42).
placementBannerPlacement———
deepLinkStringyes—App route opened on tap, e.g. hoco://promotions/<id> (same routes as push deep links).
promotionIdString @db.Uuidyes—FK → Promotion · SetNull. When set, the banner also inherits the promotion's schedule and audience.
targetWilayaCodesString[]—[]Empty = all wilayas.
targetSegmentIdsString[] @db.Uuid—[]Empty = all segments (no FK: array).
targetCustomerTypesCustomerType[]—[]Empty = every customer type (SRV-42).
targetResellerIdsString[] @db.Uuid—[]Named resellers; empty = all (no FK: array).
showToGuestsBoolean—trueShown in guest mode (API audience.guests); the API defaults it to « no targeting ». Backfilled to false for banners with wilaya or segment targets.
countdownBoolean—falseCountdown to endsAt on the slide (Banner.countdownEndsAt). The API defaults it to the linked promotion's isDailyOffer; backfilled the same way.
startsAtDateTimeyes——
endsAtDateTimeyes——
sortOrderInt—0—
clicksInt—0Taps in the reseller apps (banners.click, DES-02); never moves updatedAt.
lastClickedAtDateTimeyes—Last tap.
isActiveBoolean—truefalse = paused (or a draft).
isDraftBoolean—falseAPI status DRAFT (SRV-42): stored with isActive = false, never served to resellers or guests; cleared when the banner is activated or paused.
archivedAtDateTimeyes——
createdByIdString @db.Uuidyes—FK → User · SetNull.
createdAtDateTime—now()—
updatedAtDateTime—@updatedAt—
  • Relations: imageFile → File (imageFileId, onDelete: Restrict) · imageMobileFile → File (imageMobileFileId, onDelete: SetNull) · promotion → Promotion (promotionId, onDelete: SetNull) · createdBy → User (createdById, onDelete: SetNull).
  • Status (API): ARCHIVED (archivedAt) → DRAFT (isDraft) → PAUSED (isActive = false) → SCHEDULED / ENDED / ACTIVE from the dates. Audience matching is the promotions' one (kinds ANDed, values ORed); loyalty levels are V2 and not stored.
  • Keys & indexes: @@index([placement, isActive, sortOrder]).
  • Enums: BannerPlacement (proposed).

Example row:

{
  "id": "01a19279-8580-7922-8a9b-9c4339546000",
  "title": "Novembre câbles : -10 %",
  "subtitle": "Jusqu'au 30 novembre",
  "imageFileId": "01a19b68-3ad6-70a1-906f-94a8424c177d",
  "placement": "HOME_CAROUSEL",
  "deepLink": null,
  "promotionId": "01a19656-4a80-7734-8064-1c5352836363",
  "targetWilayaCodes": [],
  "targetSegmentIds": [],
  "startsAt": "2026-11-01T00:00:00.000Z",
  "endsAt": "2026-12-01T00:00:00.000Z",
  "sortOrder": 1,
  "isActive": true,
  "archivedAt": null,
  "createdById": "01a1a00a-4535-7ff7-af24-a0a3bb095b70",
  "createdAt": "2026-10-31T15:00:00.000Z",
  "updatedAt": "2026-10-31T15:00:00.000Z"
}

V2 — packs

Pack

A profitable pack (pack rentable): several references sold together at a pack price, added to the cart as one unit (PRM-06). Table packs · written by promotions (SRV-31) · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
codeString @db.VarChar(40)——Unique.
nameString———
descriptionStringyes——
imageFileIdString @db.Uuidyes—FK → File · SetNull.
packPriceInt——HT centimes for one pack.
statusPromotionStatus—DRAFTSame lifecycle as promotions.
startsAtDateTimeyes——
endsAtDateTimeyes——
stockCapIntyes—Maximum packs sold.
usedQuantityInt—0—
createdAtDateTime—now()—
updatedAtDateTime—@updatedAt—
  • Relations: imageFile → File (imageFileId, onDelete: SetNull).
  • Referenced by: PackItem.packId.
  • Keys & indexes: code unique.
  • Enums: PromotionStatus.
  • Derived, not stored: pack availability = min over items of ⌊available stock ÷ item quantity⌋; savings shown = Σ item prices − packPrice.

Example row:

{
  "id": "01a4a51c-ea80-7cb8-99b4-75097b208e3d",
  "code": "PACK-STARTER-CABLES",
  "name": "Pack démarrage câbles",
  "description": "30 câbles USB-C, 20 Lightning, 10 micro-USB.",
  "imageFileId": null,
  "packPrice": 1800000,
  "status": "ACTIVE",
  "startsAt": "2027-04-05T00:00:00.000Z",
  "endsAt": null,
  "stockCap": 300,
  "usedQuantity": 41,
  "createdAt": "2027-04-02T09:00:00.000Z",
  "updatedAt": "2027-04-02T09:00:00.000Z"
}

PackItem

A variant and quantity inside a pack. Table pack_items · written by promotions · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
packIdString @db.Uuid——FK → Pack · Cascade.
variantIdString @db.Uuid——FK → ProductVariant · Restrict.
quantityInt———
createdAtDateTime—now()—
  • Relations: pack → Pack (packId, onDelete: Cascade) · variant → ProductVariant (variantId, onDelete: Restrict).
  • Keys & indexes: @@unique([packId, variantId]).

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

ALTER TABLE pack_items ADD CONSTRAINT pack_items_quantity CHECK (quantity > 0);

Example row:

{
  "id": "01a4a51c-ea80-7e20-a17b-92b0e31fb6c7",
  "packId": "01a4a51c-ea80-7cb8-99b4-75097b208e3d",
  "variantId": "01a18298-9480-7f48-8522-52331c7620e9",
  "quantity": 30,
  "createdAt": "2027-04-02T09:00:00.000Z"
}

V2 — loyalty

LoyaltyAccount

Points and cashback balances, qualified purchases and level of a reseller (LOY-01, LOY-03, LOY-14). Three balances, kept separate (BR-87). Table loyalty_accounts · written by loyalty (SRV-30) · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
resellerIdString @db.Uuid——Unique. FK → Reseller · Restrict.
pointsBalanceInt—0Cache of Σ LoyaltyTransaction.points, updated in the same transaction. May be negative under loyalty.regularisationMode = ALLOW_NEGATIVE; redemptions are then blocked.
cashbackBalanceInt—0Cache of Σ CashbackTransaction.amount, centimes.
lifetimePointsInt—0Σ positive points ever earned (statistics).
pointsDebt · cashbackDebtInt—0What later earnings still owe under loyalty.regularisationMode = DEDUCT_FROM_NEXT.
levelIdString @db.Uuid——FK → LoyaltyLevelDefinition · Restrict. Standard at sign-up.
qualifiedAmountInt—0Cache of the qualified purchases of the level period, centimes HT.
qualifiedOrdersInt—0Cache of the qualified orders of the level period.
referralCodeString @db.VarChar(8)——Unique. Same alphabet as invitation codes.
levelComputedAtDateTimeyes——
dropNoticeAt · dropEffectiveAtDateTimeyes—Upkeep notice under way and the announced date of the drop (LOY-20).
earlyAccessUntilDateTimeyes—Set by a reward EARLY_ACCESS. While it runs, the catalog shows products held back by Product.earlyAccessUntil.
nextOrderBonusInt—0Points a prize NEXT_ORDER_BONUS adds to the reseller's next earning order.
createdAtDateTime—now()—
updatedAtDateTime—@updatedAt—
  • Created on first use, not at sign-up: V1 sign-up and deletion are untouched while the programme is off, and a reseller without an account reads as Standard with nothing.
  • Relations: reseller → Reseller (resellerId, onDelete: Restrict).
  • Referenced by: LoyaltyTransaction.accountId, RewardRedemption.accountId.
  • Keys & indexes: resellerId unique.
  • Derived, not stored: progress to next level — qualifiedAmount and qualifiedOrders against the next LoyaltyLevelDefinition.

Example row:

{
  "id": "01a3fe63-0800-747f-9bac-9f75c8ede05d",
  "resellerId": "01a19ba2-2fe0-7300-a148-73d1a4ef38c0",
  "pointsBalance": 1240,
  "lifetimePoints": 3100,
  "cashbackBalance": 0,
  "levelId": "01a60000-0000-7000-8000-000000000004",
  "qualifiedAmount": 52000000,
  "qualifiedOrders": 11,
  "referralCode": "AB12CD34",
  "levelComputedAt": "2027-06-01T00:10:00.000Z",
  "createdAt": "2027-03-01T00:00:00.000Z",
  "updatedAt": "2027-06-01T00:10:00.000Z"
}

LoyaltyTransaction

Append-only points ledger: earn, bonus, redeem, expire, adjust, reversal, recalculation (LOY-02, LOY-05, LOY-22). Table loyalty_transactions · written by loyalty (only writer) · V2. A database trigger refuses updates and deletes on the ledgers (loyalty_transactions, cashback_transactions, qualified_purchase_entries, loyalty_level_changes); only a purge that sets hoco.ledger_purge = 'on' in its transaction may delete.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
accountIdString @db.Uuid——FK → LoyaltyAccount · Restrict.
typeLoyaltyTransactionType———
pointsInt——Signed: positive for EARN/BONUS/positive ADJUST, negative for REDEEM/EXPIRE/REVERSAL.
balanceAfterInt———
orderIdString @db.Uuidyes—FK → Order · Restrict.
redemptionIdString @db.Uuidyes—FK → RewardRedemption · Restrict.
expiresAtDateTimeyes—Expiry of the earned lot (EARN/BONUS).
reasonStringyes—Required for ADJUST.
entitlementKeyStringyes—Unique. Blocks a second payment of the same entitlement (BR-93).
ruleVersionIdString @db.Uuidyes—FK → LoyaltyRuleVersion · Restrict.
actorUserIdString @db.Uuidyes—FK → User · Restrict.
createdAtDateTime—now()—
  • Relations: account → LoyaltyAccount (accountId, onDelete: Restrict) · order → Order (orderId, onDelete: Restrict) · redemption → RewardRedemption (redemptionId, onDelete: Restrict) · actorUser → User (actorUserId, onDelete: Restrict).
  • Keys & indexes: @@index([accountId, createdAt]) · @@index([expiresAt]).
  • Enums: LoyaltyTransactionType.

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

ALTER TABLE loyalty_transactions ADD CONSTRAINT loyalty_transactions_non_zero CHECK (points <> 0);
-- one earning per order; its reversal is a separate REVERSAL row
CREATE UNIQUE INDEX loyalty_transactions_one_earn ON loyalty_transactions (order_id) WHERE type = 'EARN';

Example row:

{
  "id": "01a59c85-1900-7663-9600-3af22def238c",
  "accountId": "01a3fe63-0800-747f-9bac-9f75c8ede05d",
  "type": "EARN",
  "points": 75,
  "balanceAfter": 1240,
  "orderId": "01a1abaa-9c82-7a56-9c55-f658ce3038e5",
  "redemptionId": null,
  "expiresAt": "2028-05-20T00:00:00.000Z",
  "reason": null,
  "actorUserId": null,
  "createdAt": "2027-05-20T10:00:00.000Z"
}

Reward

An entry of the rewards catalogue: voucher, free delivery, discount, gift, early access, commercial advantage (LOY-04). Table rewards · written by loyalty (admin.rewards.crud) · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
codeString @db.VarChar(40)——Unique.
nameString———
descriptionStringyes——
typeRewardType———
pointsCostInt———
valueAmountIntyes—Centimes HT for VOUCHER and amount DISCOUNT.
valueBpIntyes—Basis points for a percentage DISCOUNT.
imageFileIdString @db.Uuidyes—FK → File · SetNull.
stockIntyes—null = unlimited. Back-office only: resellers see availability (BR-99).
redeemedCountInt—0—
minLevelIdString @db.Uuidyes—FK → LoyaltyLevelDefinition · Restrict.
conditionsStringyes—Shown to resellers.
deliveryMethodStringyes—Shown to resellers.
validFromDateTimeyes——
validToDateTimeyes——
validityDaysIntyes—Voucher validity; null = loyalty.voucher.validityDays.
perResellerLimit · limitPeriodDaysIntyes—Units per reseller, over that many days (null = over the reward's life).
minOrderAmountIntyes—Copied on the voucher (D-46).
stacksWithPromotions · stacksWithCashbackBoolean—trueCopied on the voucher (D-46).
isActiveBoolean—true—
archivedAtDateTimeyes——
versionInt—1Optimistic lock.
createdAtDateTime—now()—
updatedAtDateTime—@updatedAt—
  • Relations: imageFile → File (imageFileId, onDelete: SetNull).
  • Referenced by: RewardRedemption.rewardId.
  • Keys & indexes: code unique.
  • Enums: RewardType.
  • Derived, not stored: available for a reseller — active, in validity, stock left, level rank ≥ the rank of minLevelId, balance ≥ pointsCost (cahier §5.5: unavailable rewards cannot be claimed).

Example row:

{
  "id": "01a40051-6a80-726b-8a9a-b50391c978c6",
  "code": "BON-2000",
  "name": "Bon d'achat 2 000 DA",
  "description": null,
  "type": "VOUCHER",
  "pointsCost": 1000,
  "valueAmount": 200000,
  "valueBp": null,
  "imageFileId": null,
  "stock": null,
  "redeemedCount": 37,
  "minLevelId": null,
  "validFrom": null,
  "validTo": null,
  "isActive": true,
  "archivedAt": null,
  "createdAt": "2027-03-01T09:00:00.000Z",
  "updatedAt": "2027-03-01T09:00:00.000Z"
}

RewardRedemption

A reseller exchanging points for a reward. Table reward_redemptions · written by loyalty (loyalty.redeem, admin.redemptions.*) · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
accountIdString @db.Uuid——FK → LoyaltyAccount · Restrict.
rewardIdString @db.Uuid——FK → Reward · Restrict.
qtyInt—1One voucher per unit for a voucher reward.
pointsSpentInt———
statusRedemptionStatus—PENDING—
fulfilledByIdString @db.Uuidyes—FK → User · Restrict. null when fulfilled automatically (voucher).
fulfilledAtDateTimeyes——
cancelledAtDateTimeyes——
noteStringyes——
createdAtDateTime—now()—
updatedAtDateTime—@updatedAt—
  • Relations: account → LoyaltyAccount (accountId, onDelete: Restrict) · reward → Reward (rewardId, onDelete: Restrict) · fulfilledBy → User (fulfilledById, onDelete: Restrict).
  • Referenced by: LoyaltyTransaction.redemptionId, Voucher.redemptionId.
  • Keys & indexes: @@index([accountId, createdAt]) · @@index([status]).
  • Enums: RedemptionStatus.

Example row:

{
  "id": "01a5a81b-6800-70ad-a622-8dfaa03220cf",
  "accountId": "01a3fe63-0800-747f-9bac-9f75c8ede05d",
  "rewardId": "01a40051-6a80-726b-8a9a-b50391c978c6",
  "pointsSpent": 1000,
  "status": "FULFILLED",
  "fulfilledById": null,
  "fulfilledAt": "2027-05-22T16:00:01.000Z",
  "cancelledAt": null,
  "note": null,
  "createdAt": "2027-05-22T16:00:00.000Z",
  "updatedAt": "2027-05-22T16:00:00.000Z"
}

Voucher

A voucher usable once at checkout. Table vouchers · written by loyalty (issued by a redemption; consumed by cart.checkout) · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
codeString @db.VarChar(16)——Unique. Same alphabet as invitation codes (no ambiguous letters).
resellerIdString @db.Uuid——FK → Reseller · Restrict.
redemptionIdString @db.Uuidyes—FK → RewardRedemption · Restrict.
typeRewardType——VOUCHER, FREE_DELIVERY or DISCOUNT.
amountIntyes—Centimes HT (D-60).
discountBpIntyes——
minOrderAmountIntyes——
stacksWithPromotions · stacksWithCashbackBoolean—trueFrom the reward.
expiresAtDateTime———
usedAtDateTimeyes—Cleared with usedOrderId when a refused or cancelled order gives the voucher back.
cancelledAtDateTimeyes—A cancelled redemption.
usedOrderIdString @db.Uuidyes—Unique. FK → Order · Restrict. One voucher per order.
createdAtDateTime—now()—
updatedAtDateTime—@updatedAt—
  • Relations: reseller → Reseller (resellerId, onDelete: Restrict) · redemption → RewardRedemption (redemptionId, onDelete: Restrict) · usedOrder → Order (usedOrderId, onDelete: Restrict).
  • Keys & indexes: code unique · usedOrderId unique · @@index([resellerId, expiresAt]) · @@index([redemptionId]). redemptionId is not unique: a redemption of qty 2 issues two single-use vouchers.
  • Enums: RewardType.
  • Derived, not stored: status (actif / utilisé / expiré / annulé) — from cancelledAt, usedAt and expiresAt.

Example row:

{
  "id": "01a5a81b-6be8-76d1-b0ac-2947ce42b4d3",
  "code": "V7KQ-M2XP",
  "resellerId": "01a19ba2-2fe0-7300-a148-73d1a4ef38c0",
  "redemptionId": "01a5a81b-6800-70ad-a622-8dfaa03220cf",
  "type": "VOUCHER",
  "amount": 200000,
  "discountBp": null,
  "minOrderAmount": 1000000,
  "expiresAt": "2027-08-22T00:00:00.000Z",
  "usedAt": null,
  "usedOrderId": null,
  "createdAt": "2027-05-22T16:00:01.000Z",
  "updatedAt": "2027-05-22T16:00:01.000Z"
}

V2 — loyalty, change request 04

Status: change request 04, not yet accepted or priced by HOCO. All tables are V2 and arrive with the P4 migrations. Ledgers are append-only. Written by loyalty (SRV-44 to SRV-50).

LoyaltyLevelDefinition

A level as configured data (LOY-03, LOY-20). Replaces the enum LoyaltyLevel. Seed rows: Standard, then Bronze, Silver, Gold, VIP as examples; Diamond or others are added by HOCO. Table loyalty_level_definitions · written by loyalty · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
codeString @db.VarChar(30)——Unique, stable. STANDARD always exists.
nameString——Shown to resellers.
rankInt——Unique among active levels; 0 = Standard.
minAmountInt—0Qualified amount, centimes HT.
minOrdersIntyes—Optional AND condition.
pointsBonusBpInt—0Bonus on base points, basis points.
benefitsJson—[]Labels shown to resellers.
retentionLevelRetentionMode—PERMANENT—
upkeepJsonyes—Evaluation period, conditions, notice days.
badgeIdString @db.Uuidyes—FK → Badge · SetNull. Added with badges (SRV-47).
isActiveBoolean—true—
versionInt—1Optimistic lock.
createdAtDateTime—now()—
updatedAtDateTime—@updatedAt—

LoyaltyLevelChange

Append-only level history (LOY-03, LOY-20). Table loyalty_level_changes · written by loyalty · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
accountIdString @db.Uuid——FK → LoyaltyAccount · Restrict.
fromLevelIdString @db.Uuidyes—FK → LoyaltyLevelDefinition.
toLevelIdString @db.Uuid——FK → LoyaltyLevelDefinition.
typeLevelChangeType———
reasonStringyes—Required for MANUAL.
ruleVersionIdString @db.Uuidyes—FK → LoyaltyRuleVersion · Restrict.
actorUserIdString @db.Uuidyes—FK → User · Restrict. Staff who made it.
effectiveAtDateTime———
createdAtDateTime—now()—
  • Keys & indexes: @@index([accountId, effectiveAt]).
  • Enums: LevelChangeType.

QualifiedPurchaseEntry

Append-only ledger of the amounts and orders that drive levels (BR-87, BR-88). Redeeming points never writes here. Table qualified_purchase_entries · written by loyalty · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
accountIdString @db.Uuid——FK → LoyaltyAccount · Restrict.
typeQualifiedPurchaseEntryType———
amountInt——Signed, centimes HT.
orderCountInt—0Signed: +1, −1 or 0.
orderIdString @db.Uuidyes—FK → Order · Restrict.
returnIdString @db.Uuidyes—FK → ReturnRequest.
reasonStringyes—Required for ADJUSTMENT.
entitlementKeyStringyes—Unique; a replayed event writes nothing.
ruleVersionIdString @db.Uuidyes—FK → LoyaltyRuleVersion · Restrict.
actorUserIdString @db.Uuidyes—FK → User · Restrict. Staff who made it.
occurredAtDateTime——Counts for the level period. A reversal keeps the date of the purchase it undoes.
createdAtDateTime—now()—
  • Keys & indexes: @@index([accountId, occurredAt]) · partial unique (order) where type = ORDER.
  • Enums: QualifiedPurchaseEntryType.

CashbackTransaction

Append-only cashback ledger (LOY-14). Balance cached on LoyaltyAccount. Table cashback_transactions · written by loyalty · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
accountIdString @db.Uuid——FK → LoyaltyAccount · Restrict.
typeCashbackTransactionType———
amountInt——Signed, centimes.
balanceAfterInt———
orderIdString @db.Uuidyes—FK → Order · Restrict.
expiresAtDateTimeyes—Earned lots.
entitlementKeyStringyes—Unique; blocks double payment (BR-93).
reasonStringyes—Required for ADJUST.
ruleVersionIdString @db.Uuidyes—FK → LoyaltyRuleVersion · Restrict.
actorUserIdString @db.Uuidyes—FK → User · Restrict. Staff who made it.
createdAtDateTime—now()—
  • Keys & indexes: entitlementKey unique · @@index([accountId, createdAt]) · @@index([expiresAt]).
  • Enums: CashbackTransactionType.

LoyaltyRule

A configurable rule: barème, bonus, referral, birthday or cashback (LOY-08 to LOY-16, LOY-23). Table loyalty_rules · written by loyalty · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
kindLoyaltyRuleKind———
nameString———
scopeEarningRuleScopeyes—Barème rules.
basisEarningBasisyes—Barème rules.
targetIdsString[]—[]Categories, brands or products of the scope.
configJson—{}Fields of the kind (rate, tiers, caps, levels, customer types), Zod-validated.
statusLoyaltyRuleStatus—DRAFT—
startsAtDateTimeyes——
endsAtDateTimeyes——
currentVersionIdString @db.Uuidyes—FK → LoyaltyRuleVersion.
versionInt—1Optimistic lock.
createdAtDateTime—now()—
updatedAtDateTime—@updatedAt—

LoyaltyRuleVersion

Immutable snapshot of a rule or campaign at each save (LOY-25). Table loyalty_rule_versions · written by loyalty · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
ruleIdString @db.Uuidyes—FK → LoyaltyRule.
campaignIdString @db.Uuidyes—FK → LoyaltyCampaign.
numberInt———
snapshotJson——Whole rule.
changeScopeRuleChangeScope—NEW_OPERATIONS_ONLY—
actorUserIdString @db.Uuidyes—FK → User · Restrict. Staff who made it.
createdAtDateTime—now()—
  • Keys & indexes: unique (ruleId, number) · unique (campaignId, number).
  • Enums: RuleChangeScope.

LoyaltyCalculation

The steps that produced an order's points and cashback, per line (LOY-11, BR-91). Supports reversal per line. Table loyalty_calculations · written by loyalty · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
orderIdString @db.Uuid——Unique. FK → Order · Restrict.
eligibleAmountInt——Centimes HT.
linesJson——Per line: order line id, base points, rule version, multiplier, level bonus, cashback.
orderBonusesJson—[]Fixed bonuses.
totalPointsInt———
qualifiedAmount · totalCashbackInt——Centimes HT.
appliedRuleIds · appliedCampaignIdsString[] @db.Uuid—[]What the caps of rules and campaigns count.
adjustmentsJson—[]After-sales credit notes on lines, by credit note.
reversedPoints · reversedCashback · reversedAmount · reversedOrdersInt—0What reversals already removed. The ledger cannot tell: under DEDUCT_FROM_NEXT a reversal removes less there and owes the rest.
levelId · referenceAt———The level and reference time used, kept for a recalculation.
revisionInt—1Replaced when a modified order is computed again; the ledger keeps every step.
createdAtDateTime—now()—
updatedAtDateTime—@updatedAt—
  • Keys & indexes: orderId unique.

LoyaltyRecalculation

A previewed and run recalculation (LOY-25). Table loyalty_recalculations · written by loyalty · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
ruleVersionIdString @db.Uuid——FK → LoyaltyRuleVersion.
fromDateTime———
toDateTime———
statusRecalculationStatus—PREVIEWED—
impactJsonyes—Resellers, points, cashback, level changes.
actorUserIdString @db.Uuidyes—FK → User · Restrict. Staff who made it.
createdAtDateTime—now()—
updatedAtDateTime—@updatedAt—

LoyaltyCampaign

A time-boxed points campaign (LOY-09, LOY-10, LOY-13). Table loyalty_campaigns · written by loyalty · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
kindLoyaltyCampaignKind———
nameString———
startsAtDateTime———
endsAtDateTime———
recurrenceJsonyes—Days and hours, Africa/Algiers.
targetsJson—{}Products, categories, levels, customer types.
multiplierBpIntyes—20000 = ×2.
bonusPointsIntyes——
stackingCampaignStackingMode—BEST_ONLY—
priorityInt—100—
maxBeneficiariesIntyes—Never exposed to resellers as a remainder.
capPerResellerIntyes——
statusLoyaltyRuleStatus—DRAFT—
versionInt—1—
createdAtDateTime—now()—
updatedAtDateTime—@updatedAt—

LoyaltyCampaignBeneficiary

Who took a place in a first-N campaign, and in which order (LOY-13). Table loyalty_campaign_beneficiaries · written by loyalty · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
campaignIdString @db.Uuid——FK → LoyaltyCampaign.
resellerIdString @db.Uuid——FK → Reseller · Restrict.
rankInt———
orderIdString @db.Uuid——FK → Order.
releasedAtDateTimeyes—Order cancelled or refused.
createdAtDateTime—now()—
  • Keys & indexes: unique (campaignId, resellerId).

Mission

A mission definition (LOY-06). Table missions · written by loyalty · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
nameString———
descriptionStringyes——
cadenceMissionCadence———
conditionMissionCondition———
paramsJson—{}Target, category, product.
rewardJson——Points or prize.
repeatableBoolean—false—
audienceJson—{}Levels, customer types, segment.
startsAtDateTimeyes——
endsAtDateTimeyes——
statusLoyaltyRuleStatus—DRAFT—
versionInt—1—
createdAtDateTime—now()—
updatedAtDateTime—@updatedAt—

MissionProgress

A mission for one reseller and period (LOY-06). Table mission_progress · written by loyalty · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
missionIdString @db.Uuid——FK → Mission.
accountIdString @db.Uuid——FK → LoyaltyAccount · Restrict.
periodKeyString @db.VarChar(20)——2027-05, 2027-W20…
currentInt—0—
targetInt———
statusMissionStatus—ASSIGNED—
completedAtDateTimeyes——
createdAtDateTime—now()—
updatedAtDateTime—@updatedAt—
  • Keys & indexes: unique (missionId, accountId, periodKey), the entitlement key of BR-93.
  • Enums: MissionStatus.

Challenge

A monthly ranking (LOY-07). Table challenges · written by loyalty · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
nameString———
criterionChallengeCriterion———
paramsJson—{}Weights, products.
topNInt—10Document example; document 03 gave 3.
prizesJson—[]Prize per rank.
startsAtDateTime———
endsAtDateTime———
statusChallengeStatus—DRAFT—
publishedAtDateTimeyes——
publishedByIdString @db.Uuidyes—FK → User.
versionInt—1—
createdAtDateTime—now()—
updatedAtDateTime—@updatedAt—

ChallengeResult

Frozen rank of a reseller in a challenge (LOY-07). Table challenge_results · written by loyalty · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
challengeIdString @db.Uuid——FK → Challenge.
resellerIdString @db.Uuid——FK → Reseller · Restrict.
rankInt———
scoreInt——Back-office and own view only.
excludedReasonStringyes——
prizeJsonyes——
awardedAtDateTimeyes——
createdAtDateTime—now()—
  • Keys & indexes: unique (challengeId, resellerId).

Badge

A badge definition (LOY-17). Table badges · written by loyalty · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
nameString———
imageFileIdString @db.Uuidyes—FK → File · SetNull.
conditionJson—{}—
validityBadgeValidity—PERMANENT—
validityDaysIntyes——
displayedBoolean—true—
benefitJsonyes—Optional.
isActiveBoolean—true—
versionInt—1—
createdAtDateTime—now()—
updatedAtDateTime—@updatedAt—

ResellerBadge

A badge earned (LOY-17). Table reseller_badges · written by loyalty · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
badgeIdString @db.Uuid——FK → Badge.
resellerIdString @db.Uuid——FK → Reseller · Restrict.
earnedAtDateTime———
endsAtDateTimeyes——
sourceKeyString———
createdAtDateTime—now()—
  • Keys & indexes: unique (badgeId, resellerId, sourceKey).

Referral

A sponsor and a new reseller (LOY-15). Table referrals · written by loyalty · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
sponsorIdString @db.Uuid——FK → Reseller.
referredIdString @db.Uuid——Unique. FK → Reseller.
signedUpAtDateTime———
firstQualifiedOrderIdString @db.Uuidyes—FK → Order.
firstQualifiedOrderAtDateTimeyes——
statusReferralStatus—PENDING—
flagsString[]—[]Same phone, address, store…
decisionReasonStringyes——
decidedByIdString @db.Uuidyes—FK → User.
createdAtDateTime—now()—
updatedAtDateTime—@updatedAt—
  • Keys & indexes: referredId unique · @@index([sponsorId]).
  • Enums: ReferralStatus.

OccasionDate

A declared date for the birthday bonus (LOY-16). Personal data: SEC-08 and BR-84 apply. Table occasion_dates · written by loyalty · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
resellerIdString @db.Uuid——FK → Reseller · Restrict.
typeOccasionType——OWNER_BIRTHDAY or SHOP_FOUNDING; the account anniversary uses the validation date.
dateDateTime @db.Date———
proofFileIdString @db.Uuidyes—FK → File.
statusOccasionDateStatus—DECLARED—
verifiedByIdString @db.Uuidyes—FK → User.
verifiedAtDateTimeyes——
rejectReasonStringyes——
createdAtDateTime—now()—
updatedAtDateTime—@updatedAt—

PrizeTable

A chest or wheel configuration (LOY-18, LOY-19). Table prize_tables · written by loyalty · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
kindPrizeTableKind———
nameString———
conditionJson—{}Minimum order, source events.
maxPerResellerIntyes——
validityDaysInt—30—
budgetIntyes—Centimes HT.
spentInt—0Cache; back-office only.
statusLoyaltyRuleStatus—DRAFT—
versionInt—1—
createdAtDateTime—now()—
updatedAtDateTime—@updatedAt—

PrizeTableEntry

One prize of a table. Table prize_table_entries · written by loyalty · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
tableIdString @db.Uuid——FK → PrizeTable · Cascade.
typePrizeType———
labelString———
valueJson—{}Points, voucher, variant.
oddsBpInt——The entries of a table add up to 10 000.
stockIntyes—null = no limit; back-office only.
unitCostIntyes—Centimes HT, for the budget.
conditionsStringyes——
createdAtDateTime—now()—
updatedAtDateTime—@updatedAt—
  • Keys & indexes: @@index([tableId]).
  • Enums: PrizeType.

PrizeEntitlement

One chest or spin earned, and its result (LOY-18, LOY-19). Table prize_entitlements · written by loyalty · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
accountIdString @db.Uuid——FK → LoyaltyAccount · Restrict.
tableIdString @db.Uuid——FK → PrizeTable.
sourceKeyString——Order or mission that gave it.
statusPrizeEntitlementStatus—AVAILABLE—
expiresAtDateTime———
usedAtDateTimeyes——
entryIdString @db.Uuidyes—FK → PrizeTableEntry: the prize drawn.
voucherIdString @db.Uuidyes—FK → Voucher.
createdAtDateTime—now()—
updatedAtDateTime—@updatedAt—
  • Keys & indexes: unique (tableId, accountId, sourceKey) · @@index([accountId, status]).
  • Enums: PrizeEntitlementStatus.

ProductReview

A private review of a delivered order or product (LOY-12). Table product_reviews · written by loyalty · V2.

FieldPrisma typeNullDefaultConstraints / notes
idString @db.Uuid—uuid(7)PK.
resellerIdString @db.Uuid——FK → Reseller · Restrict.
orderIdString @db.Uuid——FK → Order.
productIdString @db.Uuidyes—FK → Product.
ratingInt——1–5.
commentStringyes—≤ 1000 characters.
statusReviewStatus—PENDING—
decidedByIdString @db.Uuidyes—FK → User.
rejectReasonStringyes——
createdAtDateTime—now()—
updatedAtDateTime—@updatedAt—
  • Keys & indexes: unique (orderId, productId) · @@index([status]).
  • Enums: ReviewStatus.

On this page