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.
| 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 | — |
statusReason | String @db.VarChar(300) | yes | — | Why it was paused or ended early (DES-02); cleared by the other moves. |
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:
codeunique ·@@index([status, startsAt, endsAt])·@@index([isDailyOffer, status])·@@index([isFlashSale, status]). - Enums: PromotionType, PromotionStatus.
- Lifecycle:
DRAFT → SCHEDULED → ACTIVE ⇄ PAUSED → ENDED → ARCHIVED; the scheduler movesSCHEDULED → ACTIVEatstartsAtandACTIVE → ENDEDatendsAtor whenusedQuantityreachesstockCap. - 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 answers409 CART_CHANGEDwith 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.usedQuantityunits at the promotional price for the reseller (the rest at the type price, reasonRESELLER_CAP_REACHEDonce 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
DRAFTorSCHEDULED(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. |
levelId | String @db.Uuid | yes | — | FK → LoyaltyLevelDefinition · Restrict. Matches while loyalty.enabled is on; ignored while it is off, as in V1. |
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.
- API:
audience.loyaltyLevelsanswers 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"
}Banner
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 | — |
clicks | Int | — | 0 | Taps in the reseller apps (banners.click, DES-02); never moves updatedAt. |
lastClickedAt | DateTime | yes | — | Last tap. |
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/ACTIVEfrom 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:
codeunique. - 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 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.
| 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. May be negative under loyalty.regularisationMode = ALLOW_NEGATIVE; redemptions are then blocked. |
cashbackBalance | Int | — | 0 | Cache of Σ CashbackTransaction.amount, centimes. |
lifetimePoints | Int | — | 0 | Σ positive points ever earned (statistics). |
pointsDebt · cashbackDebt | Int | — | 0 | What later earnings still owe under loyalty.regularisationMode = DEDUCT_FROM_NEXT. |
levelId | String @db.Uuid | — | — | FK → LoyaltyLevelDefinition · Restrict. Standard at sign-up. |
qualifiedAmount | Int | — | 0 | Cache of the qualified purchases of the level period, centimes HT. |
qualifiedOrders | Int | — | 0 | Cache of the qualified orders of the level period. |
referralCode | String @db.VarChar(8) | — | — | Unique. Same alphabet as invitation codes. |
levelComputedAt | DateTime | yes | — | — |
dropNoticeAt · dropEffectiveAt | DateTime | yes | — | Upkeep notice under way and the announced date of the drop (LOY-20). |
earlyAccessUntil | DateTime | yes | — | Set by a reward EARLY_ACCESS. While it runs, the catalog shows products held back by Product.earlyAccessUntil. |
nextOrderBonus | Int | — | 0 | Points a prize NEXT_ORDER_BONUS adds to the reseller's next earning order. |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @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:
resellerIdunique. - Derived, not stored: progress to next level —
qualifiedAmountandqualifiedOrdersagainst the nextLoyaltyLevelDefinition.
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.
| 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. |
entitlementKey | String | yes | — | Unique. Blocks a second payment of the same entitlement (BR-93). |
ruleVersionId | String @db.Uuid | yes | — | FK → LoyaltyRuleVersion · Restrict. |
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, commercial advantage (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. Back-office only: resellers see availability (BR-99). |
redeemedCount | Int | — | 0 | — |
minLevelId | String @db.Uuid | yes | — | FK → LoyaltyLevelDefinition · Restrict. |
conditions | String | yes | — | Shown to resellers. |
deliveryMethod | String | yes | — | Shown to resellers. |
validFrom | DateTime | yes | — | — |
validTo | DateTime | yes | — | — |
validityDays | Int | yes | — | Voucher validity; null = loyalty.voucher.validityDays. |
perResellerLimit · limitPeriodDays | Int | yes | — | Units per reseller, over that many days (null = over the reward's life). |
minOrderAmount | Int | yes | — | Copied on the voucher (D-46). |
stacksWithPromotions · stacksWithCashback | Boolean | — | true | Copied on the voucher (D-46). |
isActive | Boolean | — | true | — |
archivedAt | DateTime | yes | — | — |
version | Int | — | 1 | Optimistic lock. |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @updatedAt | — |
- Relations:
imageFile→ File (imageFileId,onDelete: SetNull). - Referenced by: RewardRedemption.
rewardId. - Keys & indexes:
codeunique. - 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.
| 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. |
qty | Int | — | 1 | One voucher per unit for a voucher reward. |
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.
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 | — | FK → RewardRedemption · Restrict. |
type | RewardType | — | — | VOUCHER, FREE_DELIVERY or DISCOUNT. |
amount | Int | yes | — | Centimes HT (D-60). |
discountBp | Int | yes | — | — |
minOrderAmount | Int | yes | — | — |
stacksWithPromotions · stacksWithCashback | Boolean | — | true | From the reward. |
expiresAt | DateTime | — | — | — |
usedAt | DateTime | yes | — | Cleared with usedOrderId when a refused or cancelled order gives the voucher back. |
cancelledAt | DateTime | yes | — | A cancelled redemption. |
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:
codeunique ·usedOrderIdunique ·@@index([resellerId, expiresAt])·@@index([redemptionId]).redemptionIdis not unique: a redemption ofqty2 issues two single-use vouchers. - Enums: RewardType.
- Derived, not stored: status (actif / utilisé / expiré / annulé) — from
cancelledAt,usedAtandexpiresAt.
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.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
code | String @db.VarChar(30) | — | — | Unique, stable. STANDARD always exists. |
name | String | — | — | Shown to resellers. |
rank | Int | — | — | Unique among active levels; 0 = Standard. |
minAmount | Int | — | 0 | Qualified amount, centimes HT. |
minOrders | Int | yes | — | Optional AND condition. |
pointsBonusBp | Int | — | 0 | Bonus on base points, basis points. |
benefits | Json | — | [] | Labels shown to resellers. |
retention | LevelRetentionMode | — | PERMANENT | — |
upkeep | Json | yes | — | Evaluation period, conditions, notice days. |
badgeId | String @db.Uuid | yes | — | FK → Badge · SetNull. Added with badges (SRV-47). |
isActive | Boolean | — | true | — |
version | Int | — | 1 | Optimistic lock. |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @updatedAt | — |
- Keys & indexes:
codeunique ·rankunique where active. - Enums: LevelRetentionMode.
LoyaltyLevelChange
Append-only level history (LOY-03, LOY-20). Table loyalty_level_changes · written by loyalty · V2.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
accountId | String @db.Uuid | — | — | FK → LoyaltyAccount · Restrict. |
fromLevelId | String @db.Uuid | yes | — | FK → LoyaltyLevelDefinition. |
toLevelId | String @db.Uuid | — | — | FK → LoyaltyLevelDefinition. |
type | LevelChangeType | — | — | — |
reason | String | yes | — | Required for MANUAL. |
ruleVersionId | String @db.Uuid | yes | — | FK → LoyaltyRuleVersion · Restrict. |
actorUserId | String @db.Uuid | yes | — | FK → User · Restrict. Staff who made it. |
effectiveAt | DateTime | — | — | — |
createdAt | DateTime | — | 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.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
accountId | String @db.Uuid | — | — | FK → LoyaltyAccount · Restrict. |
type | QualifiedPurchaseEntryType | — | — | — |
amount | Int | — | — | Signed, centimes HT. |
orderCount | Int | — | 0 | Signed: +1, −1 or 0. |
orderId | String @db.Uuid | yes | — | FK → Order · Restrict. |
returnId | String @db.Uuid | yes | — | FK → ReturnRequest. |
reason | String | yes | — | Required for ADJUSTMENT. |
entitlementKey | String | yes | — | Unique; a replayed event writes nothing. |
ruleVersionId | String @db.Uuid | yes | — | FK → LoyaltyRuleVersion · Restrict. |
actorUserId | String @db.Uuid | yes | — | FK → User · Restrict. Staff who made it. |
occurredAt | DateTime | — | — | Counts for the level period. A reversal keeps the date of the purchase it undoes. |
createdAt | DateTime | — | 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.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
accountId | String @db.Uuid | — | — | FK → LoyaltyAccount · Restrict. |
type | CashbackTransactionType | — | — | — |
amount | Int | — | — | Signed, centimes. |
balanceAfter | Int | — | — | — |
orderId | String @db.Uuid | yes | — | FK → Order · Restrict. |
expiresAt | DateTime | yes | — | Earned lots. |
entitlementKey | String | yes | — | Unique; blocks double payment (BR-93). |
reason | String | yes | — | Required for ADJUST. |
ruleVersionId | String @db.Uuid | yes | — | FK → LoyaltyRuleVersion · Restrict. |
actorUserId | String @db.Uuid | yes | — | FK → User · Restrict. Staff who made it. |
createdAt | DateTime | — | now() | — |
- Keys & indexes:
entitlementKeyunique ·@@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.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
kind | LoyaltyRuleKind | — | — | — |
name | String | — | — | — |
scope | EarningRuleScope | yes | — | Barème rules. |
basis | EarningBasis | yes | — | Barème rules. |
targetIds | String[] | — | [] | Categories, brands or products of the scope. |
config | Json | — | {} | Fields of the kind (rate, tiers, caps, levels, customer types), Zod-validated. |
status | LoyaltyRuleStatus | — | DRAFT | — |
startsAt | DateTime | yes | — | — |
endsAt | DateTime | yes | — | — |
currentVersionId | String @db.Uuid | yes | — | FK → LoyaltyRuleVersion. |
version | Int | — | 1 | Optimistic lock. |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @updatedAt | — |
- Keys & indexes:
@@index([kind, status]). - Enums: LoyaltyRuleKind, LoyaltyRuleStatus, EarningRuleScope, EarningBasis.
LoyaltyRuleVersion
Immutable snapshot of a rule or campaign at each save (LOY-25). Table loyalty_rule_versions · written by loyalty · V2.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
ruleId | String @db.Uuid | yes | — | FK → LoyaltyRule. |
campaignId | String @db.Uuid | yes | — | FK → LoyaltyCampaign. |
number | Int | — | — | — |
snapshot | Json | — | — | Whole rule. |
changeScope | RuleChangeScope | — | NEW_OPERATIONS_ONLY | — |
actorUserId | String @db.Uuid | yes | — | FK → User · Restrict. Staff who made it. |
createdAt | DateTime | — | 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.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
orderId | String @db.Uuid | — | — | Unique. FK → Order · Restrict. |
eligibleAmount | Int | — | — | Centimes HT. |
lines | Json | — | — | Per line: order line id, base points, rule version, multiplier, level bonus, cashback. |
orderBonuses | Json | — | [] | Fixed bonuses. |
totalPoints | Int | — | — | — |
qualifiedAmount · totalCashback | Int | — | — | Centimes HT. |
appliedRuleIds · appliedCampaignIds | String[] @db.Uuid | — | [] | What the caps of rules and campaigns count. |
adjustments | Json | — | [] | After-sales credit notes on lines, by credit note. |
reversedPoints · reversedCashback · reversedAmount · reversedOrders | Int | — | 0 | What 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. |
revision | Int | — | 1 | Replaced when a modified order is computed again; the ledger keeps every step. |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @updatedAt | — |
- Keys & indexes:
orderIdunique.
LoyaltyRecalculation
A previewed and run recalculation (LOY-25). Table loyalty_recalculations · written by loyalty · V2.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
ruleVersionId | String @db.Uuid | — | — | FK → LoyaltyRuleVersion. |
from | DateTime | — | — | — |
to | DateTime | — | — | — |
status | RecalculationStatus | — | PREVIEWED | — |
impact | Json | yes | — | Resellers, points, cashback, level changes. |
actorUserId | String @db.Uuid | yes | — | FK → User · Restrict. Staff who made it. |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @updatedAt | — |
- Keys & indexes:
@@index([status]). - Enums: RecalculationStatus.
LoyaltyCampaign
A time-boxed points campaign (LOY-09, LOY-10, LOY-13). Table loyalty_campaigns · written by loyalty · V2.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
kind | LoyaltyCampaignKind | — | — | — |
name | String | — | — | — |
startsAt | DateTime | — | — | — |
endsAt | DateTime | — | — | — |
recurrence | Json | yes | — | Days and hours, Africa/Algiers. |
targets | Json | — | {} | Products, categories, levels, customer types. |
multiplierBp | Int | yes | — | 20000 = ×2. |
bonusPoints | Int | yes | — | — |
stacking | CampaignStackingMode | — | BEST_ONLY | — |
priority | Int | — | 100 | — |
maxBeneficiaries | Int | yes | — | Never exposed to resellers as a remainder. |
capPerReseller | Int | yes | — | — |
status | LoyaltyRuleStatus | — | DRAFT | — |
version | Int | — | 1 | — |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @updatedAt | — |
- Keys & indexes:
@@index([status, startsAt, endsAt]). - Enums: LoyaltyCampaignKind, CampaignStackingMode, LoyaltyRuleStatus.
LoyaltyCampaignBeneficiary
Who took a place in a first-N campaign, and in which order (LOY-13). Table loyalty_campaign_beneficiaries · written by loyalty · V2.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
campaignId | String @db.Uuid | — | — | FK → LoyaltyCampaign. |
resellerId | String @db.Uuid | — | — | FK → Reseller · Restrict. |
rank | Int | — | — | — |
orderId | String @db.Uuid | — | — | FK → Order. |
releasedAt | DateTime | yes | — | Order cancelled or refused. |
createdAt | DateTime | — | now() | — |
- Keys & indexes: unique (
campaignId,resellerId).
Mission
A mission definition (LOY-06). Table missions · written by loyalty · V2.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
name | String | — | — | — |
description | String | yes | — | — |
cadence | MissionCadence | — | — | — |
condition | MissionCondition | — | — | — |
params | Json | — | {} | Target, category, product. |
reward | Json | — | — | Points or prize. |
repeatable | Boolean | — | false | — |
audience | Json | — | {} | Levels, customer types, segment. |
startsAt | DateTime | yes | — | — |
endsAt | DateTime | yes | — | — |
status | LoyaltyRuleStatus | — | DRAFT | — |
version | Int | — | 1 | — |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @updatedAt | — |
- Keys & indexes:
@@index([status]). - Enums: MissionCadence, MissionCondition, LoyaltyRuleStatus.
MissionProgress
A mission for one reseller and period (LOY-06). Table mission_progress · written by loyalty · V2.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
missionId | String @db.Uuid | — | — | FK → Mission. |
accountId | String @db.Uuid | — | — | FK → LoyaltyAccount · Restrict. |
periodKey | String @db.VarChar(20) | — | — | 2027-05, 2027-W20… |
current | Int | — | 0 | — |
target | Int | — | — | — |
status | MissionStatus | — | ASSIGNED | — |
completedAt | DateTime | yes | — | — |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @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.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
name | String | — | — | — |
criterion | ChallengeCriterion | — | — | — |
params | Json | — | {} | Weights, products. |
topN | Int | — | 10 | Document example; document 03 gave 3. |
prizes | Json | — | [] | Prize per rank. |
startsAt | DateTime | — | — | — |
endsAt | DateTime | — | — | — |
status | ChallengeStatus | — | DRAFT | — |
publishedAt | DateTime | yes | — | — |
publishedById | String @db.Uuid | yes | — | FK → User. |
version | Int | — | 1 | — |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @updatedAt | — |
- Keys & indexes:
@@index([status, endsAt]). - Enums: ChallengeCriterion, ChallengeStatus.
ChallengeResult
Frozen rank of a reseller in a challenge (LOY-07). Table challenge_results · written by loyalty · V2.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
challengeId | String @db.Uuid | — | — | FK → Challenge. |
resellerId | String @db.Uuid | — | — | FK → Reseller · Restrict. |
rank | Int | — | — | — |
score | Int | — | — | Back-office and own view only. |
excludedReason | String | yes | — | — |
prize | Json | yes | — | — |
awardedAt | DateTime | yes | — | — |
createdAt | DateTime | — | now() | — |
- Keys & indexes: unique (
challengeId,resellerId).
Badge
A badge definition (LOY-17). Table badges · written by loyalty · V2.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
name | String | — | — | — |
imageFileId | String @db.Uuid | yes | — | FK → File · SetNull. |
condition | Json | — | {} | — |
validity | BadgeValidity | — | PERMANENT | — |
validityDays | Int | yes | — | — |
displayed | Boolean | — | true | — |
benefit | Json | yes | — | Optional. |
isActive | Boolean | — | true | — |
version | Int | — | 1 | — |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @updatedAt | — |
- Keys & indexes: —
- Enums: BadgeValidity.
ResellerBadge
A badge earned (LOY-17). Table reseller_badges · written by loyalty · V2.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
badgeId | String @db.Uuid | — | — | FK → Badge. |
resellerId | String @db.Uuid | — | — | FK → Reseller · Restrict. |
earnedAt | DateTime | — | — | — |
endsAt | DateTime | yes | — | — |
sourceKey | String | — | — | — |
createdAt | DateTime | — | now() | — |
- Keys & indexes: unique (
badgeId,resellerId,sourceKey).
Referral
A sponsor and a new reseller (LOY-15). Table referrals · written by loyalty · V2.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
sponsorId | String @db.Uuid | — | — | FK → Reseller. |
referredId | String @db.Uuid | — | — | Unique. FK → Reseller. |
signedUpAt | DateTime | — | — | — |
firstQualifiedOrderId | String @db.Uuid | yes | — | FK → Order. |
firstQualifiedOrderAt | DateTime | yes | — | — |
status | ReferralStatus | — | PENDING | — |
flags | String[] | — | [] | Same phone, address, store… |
decisionReason | String | yes | — | — |
decidedById | String @db.Uuid | yes | — | FK → User. |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @updatedAt | — |
- Keys & indexes:
referredIdunique ·@@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.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
resellerId | String @db.Uuid | — | — | FK → Reseller · Restrict. |
type | OccasionType | — | — | OWNER_BIRTHDAY or SHOP_FOUNDING; the account anniversary uses the validation date. |
date | DateTime @db.Date | — | — | — |
proofFileId | String @db.Uuid | yes | — | FK → File. |
status | OccasionDateStatus | — | DECLARED | — |
verifiedById | String @db.Uuid | yes | — | FK → User. |
verifiedAt | DateTime | yes | — | — |
rejectReason | String | yes | — | — |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @updatedAt | — |
- Keys & indexes: unique (
resellerId,type). - Enums: OccasionType, OccasionDateStatus.
PrizeTable
A chest or wheel configuration (LOY-18, LOY-19). Table prize_tables · written by loyalty · V2.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
kind | PrizeTableKind | — | — | — |
name | String | — | — | — |
condition | Json | — | {} | Minimum order, source events. |
maxPerReseller | Int | yes | — | — |
validityDays | Int | — | 30 | — |
budget | Int | yes | — | Centimes HT. |
spent | Int | — | 0 | Cache; back-office only. |
status | LoyaltyRuleStatus | — | DRAFT | — |
version | Int | — | 1 | — |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @updatedAt | — |
- Keys & indexes:
@@index([kind, status]). - Enums: PrizeTableKind, LoyaltyRuleStatus.
PrizeTableEntry
One prize of a table. Table prize_table_entries · written by loyalty · V2.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
tableId | String @db.Uuid | — | — | FK → PrizeTable · Cascade. |
type | PrizeType | — | — | — |
label | String | — | — | — |
value | Json | — | {} | Points, voucher, variant. |
oddsBp | Int | — | — | The entries of a table add up to 10 000. |
stock | Int | yes | — | null = no limit; back-office only. |
unitCost | Int | yes | — | Centimes HT, for the budget. |
conditions | String | yes | — | — |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @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.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
accountId | String @db.Uuid | — | — | FK → LoyaltyAccount · Restrict. |
tableId | String @db.Uuid | — | — | FK → PrizeTable. |
sourceKey | String | — | — | Order or mission that gave it. |
status | PrizeEntitlementStatus | — | AVAILABLE | — |
expiresAt | DateTime | — | — | — |
usedAt | DateTime | yes | — | — |
entryId | String @db.Uuid | yes | — | FK → PrizeTableEntry: the prize drawn. |
voucherId | String @db.Uuid | yes | — | FK → Voucher. |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @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.
| Field | Prisma type | Null | Default | Constraints / notes |
|---|---|---|---|---|
id | String @db.Uuid | — | uuid(7) | PK. |
resellerId | String @db.Uuid | — | — | FK → Reseller · Restrict. |
orderId | String @db.Uuid | — | — | FK → Order. |
productId | String @db.Uuid | yes | — | FK → Product. |
rating | Int | — | — | 1–5. |
comment | String | yes | — | ≤ 1000 characters. |
status | ReviewStatus | — | PENDING | — |
decidedById | String @db.Uuid | yes | — | FK → User. |
rejectReason | String | yes | — | — |
createdAt | DateTime | — | now() | — |
updatedAt | DateTime | — | @updatedAt | — |
- Keys & indexes: unique (
orderId,productId) ·@@index([status]). - Enums: ReviewStatus.