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.

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.

Field	Prisma type	Null	Default	Constraints / notes
`id`	`String @db.Uuid`	—	`uuid(7)`	PK.
`code`	`String @db.VarChar(40)`	—	—	Unique. Internal code (not a coupon typed by resellers).
`name`	`String`	—	—	The label shown to resellers (API `label`, ≤ 60).
`internalName`	`String`	yes	—	Back-office name (API `name`, BO-50 Nom interne, SRV-42); `null` = `name`. Backfilled from `name`.
`nameAr`	`String`	yes	—	—
`description`	`String`	yes	—	—
`type`	`PromotionType`	—	—	—
`value`	`Int`	yes	—	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` · `fixedPriceDetail`	`Int`	yes	—	`FIXED_PRICE` with one unit price HT per customer type (SRV-41); all three or none (CHECK, each > 0); they override `value`.
`status`	`PromotionStatus`	—	`DRAFT`	—
`isDailyOffer`	`Boolean`	—	`false`	Offre du jour carousel with countdown to `endsAt` (PRM-05).
`isFlashSale`	`Boolean`	—	`false`	Flash 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`.
`startsAt`	`DateTime`	—	—	—
`endsAt`	`DateTime`	—	—	Exclusive.
`priority`	`Int`	—	`0`	Higher is applied first (PRM-03).
`isStackable`	`Boolean`	—	`false`	Non-stackable: the engine keeps the best non-stackable promotion per line; stackable ones are then applied in priority order on the remaining price.
`minOrderAmount`	`Int`	yes	—	Cart subtotal HT required (mandatory for `FREE_DELIVERY`).
`minLineQty`	`Int`	yes	—	Line quantity required for the discount to apply.
`stockCap`	`Int`	yes	—	Maximum units sold under the promotion, all resellers (stock de la promotion).
`usedQuantity`	`Int`	—	`0`	Units consumed by submitted orders; released when they are cancelled or refused.
`perResellerCap`	`Int`	yes	—	Maximum units one reseller buys under the promotion over its life (BO-50 Limite par revendeur, SRV-42); CHECK ≥ 1. Consumed in PromotionResellerUsage.
`notifyResellers`	`Boolean`	—	`false`	BO-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).
`imageFileId`	`String @db.Uuid`	yes	—	FK → File · `SetNull`. Card image (`BANNER_IMAGE`).
`createdById`	`String @db.Uuid`	yes	—	FK → User · `SetNull`.
`createdAt`	`DateTime`	—	`now()`	—
`updatedAt`	`DateTime`	—	`@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.

Field	Prisma type	Null	Default	Constraints / notes
`promotionId`	`String @db.Uuid`	—	—	PK (with `resellerId`). FK → Promotion · `Cascade`.
`resellerId`	`String @db.Uuid`	—	—	PK (with `promotionId`). FK → Reseller · `Cascade`.
`usedQuantity`	`Int`	—	`0`	CHECK ≥ 0. Units of the reseller's submitted orders under the promotion, released like `Promotion.usedQuantity`.
`updatedAt`	`DateTime`	—	`@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.

Field	Prisma type	Null	Default	Constraints / notes
`id`	`String @db.Uuid`	—	`uuid(7)`	PK.
`promotionId`	`String @db.Uuid`	—	—	FK → Promotion · `Cascade`.
`productId`	`String @db.Uuid`	yes	—	FK → Product · `Restrict`.
`variantId`	`String @db.Uuid`	yes	—	FK → ProductVariant · `Restrict`.
`categoryId`	`String @db.Uuid`	yes	—	FK → Category · `Restrict`.
`brandId`	`String @db.Uuid`	yes	—	FK → Brand · `Restrict`.
`createdAt`	`DateTime`	—	`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.

Field	Prisma type	Null	Default	Constraints / notes
`id`	`String @db.Uuid`	—	`uuid(7)`	PK.
`promotionId`	`String @db.Uuid`	—	—	FK → Promotion · `Cascade`.
`wilayaCode`	`String @db.VarChar(2)`	yes	—	FK → Wilaya.code · `Restrict`.
`segmentId`	`String @db.Uuid`	yes	—	FK → Segment · `Restrict`.
`customerType`	`CustomerType`	yes	—	Targets one pricing tier (e.g. a Détail-only offer). Addition to the cahier list, natural with the three prices.
`loyaltyLevel`	`LoyaltyLevel`	yes	—	Matches once loyalty exists (V2); ignored before.
`resellerId`	`String @db.Uuid`	yes	—	FK → Reseller · `Restrict`.
`minVolume`	`Int`	yes	—	Minimum HT amount of delivered orders over `volumePeriodDays`.
`volumePeriodDays`	`Int`	yes	—	—
`createdAt`	`DateTime`	—	`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, LoyaltyLevel.
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, loyalty_level, 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,
  "loyaltyLevel": 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).

Field	Prisma type	Null	Default	Constraints / notes
`id`	`String @db.Uuid`	—	`uuid(7)`	PK.
`title`	`String`	—	—	—
`subtitle`	`String`	yes	—	—
`imageFileId`	`String @db.Uuid`	—	—	FK → File · `Restrict`. Purpose `BANNER_IMAGE` (public CDN).
`imageMobileFileId`	`String @db.Uuid`	yes	—	FK → File · `SetNull`. `BANNER_IMAGE` sized for phones (BO-51 Image mobile); the reseller app gets it instead of `imageFileId` when set (SRV-42).
`placement`	`BannerPlacement`	—	—	—
`deepLink`	`String`	yes	—	App route opened on tap, e.g. `hoco://promotions/<id>` (same routes as push deep links).
`promotionId`	`String @db.Uuid`	yes	—	FK → Promotion · `SetNull`. When set, the banner also inherits the promotion's schedule and audience.
`targetWilayaCodes`	`String[]`	—	`[]`	Empty = all wilayas.
`targetSegmentIds`	`String[] @db.Uuid`	—	`[]`	Empty = all segments (no FK: array).
`targetCustomerTypes`	`CustomerType[]`	—	`[]`	Empty = every customer type (SRV-42).
`targetResellerIds`	`String[] @db.Uuid`	—	`[]`	Named resellers; empty = all (no FK: array).
`showToGuests`	`Boolean`	—	`true`	Shown in guest mode (API `audience.guests`); the API defaults it to « no targeting ». Backfilled to `false` for banners with wilaya or segment targets.
`countdown`	`Boolean`	—	`false`	Countdown to `endsAt` on the slide (`Banner.countdownEndsAt`). The API defaults it to the linked promotion's `isDailyOffer`; backfilled the same way.
`startsAt`	`DateTime`	yes	—	—
`endsAt`	`DateTime`	yes	—	—
`sortOrder`	`Int`	—	`0`	—
`isActive`	`Boolean`	—	`true`	`false` = paused (or a draft).
`isDraft`	`Boolean`	—	`false`	API status `DRAFT` (SRV-42): stored with `isActive = false`, never served to resellers or guests; cleared when the banner is activated or paused.
`archivedAt`	`DateTime`	yes	—	—
`createdById`	`String @db.Uuid`	yes	—	FK → User · `SetNull`.
`createdAt`	`DateTime`	—	`now()`	—
`updatedAt`	`DateTime`	—	`@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.

Field	Prisma type	Null	Default	Constraints / notes
`id`	`String @db.Uuid`	—	`uuid(7)`	PK.
`code`	`String @db.VarChar(40)`	—	—	Unique.
`name`	`String`	—	—	—
`description`	`String`	yes	—	—
`imageFileId`	`String @db.Uuid`	yes	—	FK → File · `SetNull`.
`packPrice`	`Int`	—	—	HT centimes for one pack.
`status`	`PromotionStatus`	—	`DRAFT`	Same lifecycle as promotions.
`startsAt`	`DateTime`	yes	—	—
`endsAt`	`DateTime`	yes	—	—
`stockCap`	`Int`	yes	—	Maximum packs sold.
`usedQuantity`	`Int`	—	`0`	—
`createdAt`	`DateTime`	—	`now()`	—
`updatedAt`	`DateTime`	—	`@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.

Field	Prisma type	Null	Default	Constraints / notes
`id`	`String @db.Uuid`	—	`uuid(7)`	PK.
`packId`	`String @db.Uuid`	—	—	FK → Pack · `Cascade`.
`variantId`	`String @db.Uuid`	—	—	FK → ProductVariant · `Restrict`.
`quantity`	`Int`	—	—	—
`createdAt`	`DateTime`	—	`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 balance and level of a reseller (LOY-01, LOY-03). Table loyalty_accounts · written by loyalty (SRV-30) · V2.

Field	Prisma type	Null	Default	Constraints / notes
`id`	`String @db.Uuid`	—	`uuid(7)`	PK.
`resellerId`	`String @db.Uuid`	—	—	Unique. FK → Reseller · `Restrict`.
`pointsBalance`	`Int`	—	`0`	Cache of Σ LoyaltyTransaction.points, updated in the same transaction; never negative.
`lifetimePoints`	`Int`	—	`0`	Σ positive points ever earned (statistics).
`level`	`LoyaltyLevel`	—	`BRONZE`	—
`periodPoints`	`Int`	—	`0`	Points earned in the current qualifying period (thresholds validated by HOCO).
`levelComputedAt`	`DateTime`	yes	—	—
`createdAt`	`DateTime`	—	`now()`	—
`updatedAt`	`DateTime`	—	`@updatedAt`	—

Relations: reseller → Reseller (resellerId, onDelete: Restrict).
Referenced by: LoyaltyTransaction.accountId, RewardRedemption.accountId.
Keys & indexes: resellerId unique.
Enums: LoyaltyLevel.
Derived, not stored: progress to next level — periodPoints against the level thresholds setting.

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

ALTER TABLE loyalty_accounts ADD CONSTRAINT loyalty_accounts_balance CHECK (points_balance >= 0);

Example row:

{
  "id": "01a3fe63-0800-747f-9bac-9f75c8ede05d",
  "resellerId": "01a19ba2-2fe0-7300-a148-73d1a4ef38c0",
  "pointsBalance": 1240,
  "lifetimePoints": 3100,
  "level": "SILVER",
  "periodPoints": 980,
  "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 (LOY-02, LOY-05). Table loyalty_transactions · written by loyalty (only writer) · V2.

Field	Prisma type	Null	Default	Constraints / notes
`id`	`String @db.Uuid`	—	`uuid(7)`	PK.
`accountId`	`String @db.Uuid`	—	—	FK → LoyaltyAccount · `Restrict`.
`type`	`LoyaltyTransactionType`	—	—	—
`points`	`Int`	—	—	Signed: positive for `EARN`/`BONUS`/positive `ADJUST`, negative for `REDEEM`/`EXPIRE`/`REVERSAL`.
`balanceAfter`	`Int`	—	—	—
`orderId`	`String @db.Uuid`	yes	—	FK → Order · `Restrict`.
`redemptionId`	`String @db.Uuid`	yes	—	FK → RewardRedemption · `Restrict`.
`expiresAt`	`DateTime`	yes	—	Expiry of the earned lot (`EARN`/`BONUS`).
`reason`	`String`	yes	—	Required for `ADJUST`.
`actorUserId`	`String @db.Uuid`	yes	—	FK → User · `Restrict`.
`createdAt`	`DateTime`	—	`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 (LOY-04). Table rewards · written by loyalty (admin.rewards.crud) · V2.

Field	Prisma type	Null	Default	Constraints / notes
`id`	`String @db.Uuid`	—	`uuid(7)`	PK.
`code`	`String @db.VarChar(40)`	—	—	Unique.
`name`	`String`	—	—	—
`description`	`String`	yes	—	—
`type`	`RewardType`	—	—	—
`pointsCost`	`Int`	—	—	—
`valueAmount`	`Int`	yes	—	Centimes HT for `VOUCHER` and amount `DISCOUNT`.
`valueBp`	`Int`	yes	—	Basis points for a percentage `DISCOUNT`.
`imageFileId`	`String @db.Uuid`	yes	—	FK → File · `SetNull`.
`stock`	`Int`	yes	—	`null` = unlimited.
`redeemedCount`	`Int`	—	`0`	—
`minLevel`	`LoyaltyLevel`	yes	—	—
`validFrom`	`DateTime`	yes	—	—
`validTo`	`DateTime`	yes	—	—
`isActive`	`Boolean`	—	`true`	—
`archivedAt`	`DateTime`	yes	—	—
`createdAt`	`DateTime`	—	`now()`	—
`updatedAt`	`DateTime`	—	`@updatedAt`	—

Relations: imageFile → File (imageFileId, onDelete: SetNull).
Referenced by: RewardRedemption.rewardId.
Keys & indexes: code unique.
Enums: RewardType, LoyaltyLevel.
Derived, not stored: available for a reseller — active, in validity, stock left, level ≥ minLevel, 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,
  "minLevel": 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.

Field	Prisma type	Null	Default	Constraints / notes
`id`	`String @db.Uuid`	—	`uuid(7)`	PK.
`accountId`	`String @db.Uuid`	—	—	FK → LoyaltyAccount · `Restrict`.
`rewardId`	`String @db.Uuid`	—	—	FK → Reward · `Restrict`.
`pointsSpent`	`Int`	—	—	—
`status`	`RedemptionStatus`	—	`PENDING`	—
`fulfilledById`	`String @db.Uuid`	yes	—	FK → User · `Restrict`. `null` when fulfilled automatically (voucher).
`fulfilledAt`	`DateTime`	yes	—	—
`cancelledAt`	`DateTime`	yes	—	—
`note`	`String`	yes	—	—
`createdAt`	`DateTime`	—	`now()`	—
`updatedAt`	`DateTime`	—	`@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 (proposed).

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.

Field	Prisma type	Null	Default	Constraints / notes
`id`	`String @db.Uuid`	—	`uuid(7)`	PK.
`code`	`String @db.VarChar(16)`	—	—	Unique. Same alphabet as invitation codes (no ambiguous letters).
`resellerId`	`String @db.Uuid`	—	—	FK → Reseller · `Restrict`.
`redemptionId`	`String @db.Uuid`	yes	—	Unique. FK → RewardRedemption · `Restrict`.
`type`	`RewardType`	—	—	`VOUCHER`, `FREE_DELIVERY` or `DISCOUNT`.
`amount`	`Int`	yes	—	—
`discountBp`	`Int`	yes	—	—
`minOrderAmount`	`Int`	yes	—	—
`expiresAt`	`DateTime`	—	—	—
`usedAt`	`DateTime`	yes	—	—
`usedOrderId`	`String @db.Uuid`	yes	—	Unique. FK → Order · `Restrict`. One voucher per order.
`createdAt`	`DateTime`	—	`now()`	—
`updatedAt`	`DateTime`	—	`@updatedAt`	—

Relations: reseller → Reseller (resellerId, onDelete: Restrict) · redemption → RewardRedemption (redemptionId, onDelete: Restrict) · usedOrder → Order (usedOrderId, onDelete: Restrict).
Keys & indexes: code unique · redemptionId unique · usedOrderId unique · @@index([resellerId, expiresAt]).
Enums: RewardType.
Derived, not stored: status (actif / utilisé / expiré) — from 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"
}

Promotions and loyalty

Promotions and bannersCopy Anchor Link

PromotionCopy Anchor Link

PromotionResellerUsageCopy Anchor Link

PromotionScopeCopy Anchor Link

PromotionAudienceCopy Anchor Link

BannerCopy Anchor Link

V2 — packsCopy Anchor Link

PackCopy Anchor Link

PackItemCopy Anchor Link

V2 — loyaltyCopy Anchor Link

LoyaltyAccountCopy Anchor Link

LoyaltyTransactionCopy Anchor Link

RewardCopy Anchor Link

RewardRedemptionCopy Anchor Link

VoucherCopy Anchor Link

On this page

Promotions and banners

Promotion

PromotionResellerUsage

PromotionScope

PromotionAudience

Banner

V2 — packs

Pack

PackItem

V2 — loyalty

LoyaltyAccount

LoyaltyTransaction

Reward

RewardRedemption

Voucher