Promo, Challenge & Affiliate ERD

This diagram covers the promotional system (promo codes, deposit bonuses), competitive features (challenges, leaderboards), and the affiliate/referral program. All tables live in the public Postgres schema (libs/_prisma/src/schema/api.prisma).

The promo system has two bonus types: INSTANT (immediate balance credit) and DEPOSIT (match bonus with wager rollover requirements). The PromoCode model (api.prisma:1767-1868) is the most complex single table — it encodes claim conditions, activation rules, and completion criteria in ~30 columns.

erDiagram
    User {
        int id PK
        string username
        string affiliateCodeId FK
    }

    PromoCode {
        string id PK
        int createdById FK
        string title
        enum type
        string code
        decimal amount
        enum currencyId
        boolean isPublic
        boolean isHidden
        boolean isActive
        datetime expiresAt
        int claimsLeft
        enum minKYCLevelToClaim
        decimal minTotalWagerUsdToClaim
        float bonusMultiplier
        decimal maxBonusUsdAmount
        int timeSecondsToComplete
        decimal wagerMultiplierToComplete
    }

    UserPromoCode {
        string id PK
        int userId FK
        string promoCodeId FK
        boolean isActive
        enum status
    }

    UserPromoCodeProgress {
        string id PK
        int userId FK
        string promoCodeId FK
        datetime expiresAt
        decimal bonusUsdAmount
        decimal baseUsdAmount
        enum currencyId
        decimal wagerMultiplierToComplete
        decimal wagerUsdAmountToComplete
        decimal wageredUsdAmount
    }

    PromoGameWhitelist {
        string id PK
        string promoCodeId FK
        int gameId FK
        decimal maxBetAmount
        float wagerMultiplier
        boolean enabled
    }

    Challenge {
        string id PK
        int createdById FK
        string defaultTitle
        int targetGameId FK
        decimal minBetAmountUsd
        float minBetMultiplier
        enum rewardCurrency
        decimal rewardAmount
        decimal rewardUsdAmount
        int winnerId FK
        datetime winnerSelectedAt
        int winnerConfirmedById FK
        string betId
        boolean isHidden
    }

    Leaderboard {
        string id PK
        string name
        enum type
        json config
        datetime startDate
        datetime endDate
        enum status
        string scheduleId FK
    }

    LeaderboardUser {
        string id PK
        int userId FK
        string leaderboardId FK
        decimal usdAmount
        decimal remainingUsdPrize
        boolean isWinner
        int finalPosition
    }

    LeaderboardSchedule {
        string id PK
        boolean enabled
        json config
    }

    LeaderboardUserPositionView {
        string id PK
        int position
    }

    AffiliateCode {
        string id PK
        int ownerId FK
        decimal wageredUsdAmount
        boolean allowCommissionBonus
    }

    AffiliateUserStats {
        int userId PK,FK
        enum currencyId PK
        decimal claimableAmount
        decimal claimedAmount
    }

    AffiliateUserStatsUsd {
        int userId PK,FK
        decimal claimableAmount
        decimal claimedAmount
        datetime lastBetCreatedAt
    }

    AffiliateAggregatedInfo {
        int userId PK,FK
        decimal earnedCommissions
        decimal unclaimedCommissions
        int referralsCount
        int activeReferralsCount
        decimal referralsGgr
    }

    AffiliateStreamer {
        int id PK,FK
        int referrals
        int activeReferrals
        decimal referralsDepositUsdAmount
        decimal profitLoss
    }

    User ||--o{ PromoCode : "created"
    User ||--o{ UserPromoCode : "claimed"
    PromoCode ||--o{ UserPromoCode : "claimed by"
    UserPromoCode ||--o| UserPromoCodeProgress : "tracks"
    PromoCode ||--o{ UserPromoCodeProgress : "progress"
    PromoCode ||--o{ PromoGameWhitelist : "allowed games"
    User ||--o{ Challenge : "created"
    User ||--o{ Challenge : "won"
    User ||--o{ Challenge : "confirmed"
    User ||--o{ LeaderboardUser : "participates"
    Leaderboard ||--o{ LeaderboardUser : "ranked in"
    LeaderboardSchedule ||--o{ Leaderboard : "generates"
    LeaderboardUser ||--o| LeaderboardUserPositionView : "ranked"
    User ||--o{ AffiliateCode : "owns"
    User |o--o| AffiliateCode : "referred by"
    AffiliateCode ||--o{ PromoCode : "exclusive"
    User ||--o{ AffiliateUserStats : "affiliate stats"
    User ||--o| AffiliateUserStatsUsd : "affiliate usd"
    User ||--o| AffiliateAggregatedInfo : "affiliate summary"
    User ||--o| AffiliateStreamer : "streamer view"

Table reference

Table	PK	Purpose	Prisma line
PromoCode	id (uuid)	Promo/bonus definition	api.prisma:1767
UserPromoCode	id (uuid)	Per-user claim record	api.prisma:1893
UserPromoCodeProgress	id (uuid)	Deposit-bonus rollover tracking	api.prisma:1918
PromoGameWhitelist	id (uuid)	Per-game wager multiplier overrides	api.prisma:1960
Challenge	id (uuid)	Admin-created hit-multiplier challenges	api.prisma:1561
Leaderboard	id (uuid)	Time-boxed wagering competitions	api.prisma:1319
LeaderboardUser	id (uuid)	User entry in a leaderboard	api.prisma:1346
LeaderboardSchedule	id (uuid)	Auto-recurring leaderboard config	api.prisma:1294
LeaderboardUserPositionView	id	Postgres view for rank calculation	api.prisma:1371
AffiliateCode	id (string)	Referral codes owned by users	api.prisma:1107
AffiliateUserStats	(userId, currencyId)	Per-currency affiliate earnings	api.prisma:1048
AffiliateUserStatsUsd	userId	USD-denominated affiliate summary	api.prisma:1062
AffiliateAggregatedInfo	userId	Postgres view — aggregate commissions	api.prisma:1074
AffiliateStreamer	id (userId)	Postgres view — streamer-specific metrics	api.prisma:1089

Promo lifecycle (DEPOSIT type)

PromoCode created (isActive=true)
  └─> User claims → UserPromoCode (status=CLAIMED)
      └─> User deposits → UserPromoCode (status=ACTIVE)
          └─> UserPromoCodeProgress created (tracks wager rollover)
              └─> wageredUsdAmount reaches target → status=COMPLETED
              └─> expiresAt passes → status=EXPIRED
              └─> admin cancels → status=CANCELLED

Key constraints: - @@unique([code, isActive]) (api.prisma:1865) — only one active promo per code string; deactivated codes set isActive=null. - @@unique([promoCodeId, userId]) on both UserPromoCode (api.prisma:1909) and UserPromoCodeProgress (api.prisma:1957) — one claim per user per promo.

Leaderboard lifecycle

States flow: NOT_STARTED -> ACTIVE -> ENDED -> SETTLEMENT -> FINISHED (api.prisma:1309-1315). The LeaderboardSchedule.config JSON defines recurrence rules; a BullMQ job creates the next Leaderboard when the current one ends.

Affiliate system

• AffiliateCode.id is a human-readable string (e.g. "STREAMER123").
• User.affiliateCodeId links to the code that referred them — set once at registration.
• AffiliateAggregatedInfo and AffiliateStreamer are Postgres views (not tables) — they aggregate data from AffiliateUserStatsUsd, UserStatsUsd, and Deposit.

Cross-references

• Flow docs: docs/flows/promo-claim.md, docs/flows/challenges.md.
• Security findings: FM-C-1 (missing @Post on promo claim), FM-C-4 (.getMilliseconds() bug) in docs/security-register.md.
• Leaderboard queue: apps/api/src/leaderboard/ — uses BullMQ for settlement processing.