Auth & User Identity ERD

This diagram covers user registration, authentication, sessions, KYC, permissions, responsible-gambling controls, and provably-fair seed management. All tables live in the public Postgres schema (libs/_prisma/src/schema/api.prisma).

The User model (api.prisma:198-293) is the central hub — it has 50+ relation fields. This ERD shows only the auth/identity-adjacent subset. For money-flow relations see erd-bet-accounting.md; for promo/affiliate relations see erd-promo-challenge.md.

erDiagram
    User {
        int id PK
        datetime createdAt
        string username UK
        string email UK
        boolean emailVerified
        string password
        string mfaSecret
        int vipLevel
        decimal exp
        string steamId UK
        string googleEmail UK
        string avatar
        boolean isBanned
        string banReason
        boolean isBot
        boolean isTest
        boolean isStaff
    }

    UserRole {
        int userId PK,FK
        enum role PK
    }

    UserPermission {
        int userId PK,FK
        string permissionKey PK,FK
    }

    Permission {
        string key PK
        string title
    }

    UserSession {
        string sessionKey PK
        string sessionId
        int userId FK
        string ip
        string countryCode
        string regionCode
        datetime lastActivity
        json userAgent
    }

    UserMfaRecovery {
        int userId PK,FK
        string recoveryCode PK
        datetime createdAt
    }

    UserKyc {
        int userId PK,FK
        boolean verificationPending
        enum level
        enum gender
        string firstName
        string lastName
        datetime dateOfBirth
        string countryCode FK
    }

    UserKycVerificationRequest {
        string id PK
        int userId FK
        enum kycLevel
        enum verdict
        enum rejectType
        string moderationComment
    }

    Country {
        string code PK
        string name
        string dialCode
        boolean isBlocked
    }

    CountryRegion {
        string countryCode PK,FK
        string code PK
        string name
        boolean isBlocked
    }

    UserFairnessSeeds {
        string id PK
        int userId UK,FK
        string serverSeed
        string hashedServerSeed
        string clientSeed
        int nonce
        string nextServerSeed
        string nextHashedServerSeed
    }

    UserInactiveFairnessSeeds {
        string id PK
        int userId FK
        string serverSeed
        string hashedServerSeed
        string clientSeed
        int nonce
        datetime createdAt
    }

    UserSelfExclusion {
        int id PK
        int userId FK
        datetime expiresAt
    }

    UserGamblingLimits {
        int id PK
        int userId UK,FK
        decimal betLimit
    }

    UserConsents {
        int userId FK
        enum type
        boolean optedIn
    }

    UserWithdrawalsBlock {
        int userId PK,FK
        string reason
        string reasonPublic
        datetime blockUntil
    }

    RegistrationInfo {
        int userId PK,FK
        string device
        string os
        string browser
        string countryCode FK
        string ipAddress
        enum type
        string referrer
    }

    User ||--o{ UserRole : "has"
    User ||--o{ UserPermission : "granted"
    Permission ||--o{ UserPermission : "assigned to"
    User ||--o{ UserSession : "logs in via"
    User ||--o{ UserMfaRecovery : "recovery codes"
    User ||--o| UserKyc : "verified by"
    UserKyc ||--o{ UserKycVerificationRequest : "requests"
    User ||--o{ UserKycVerificationRequest : "submitted"
    UserKyc |o--o| Country : "resides in"
    Country ||--o{ CountryRegion : "contains"
    User ||--o| UserFairnessSeeds : "active seeds"
    User ||--o{ UserInactiveFairnessSeeds : "rotated seeds"
    User ||--o{ UserSelfExclusion : "self-excluded"
    User ||--o| UserGamblingLimits : "limited by"
    User ||--o{ UserConsents : "consented"
    User ||--o| UserWithdrawalsBlock : "blocked"
    User ||--o| RegistrationInfo : "registered via"
    RegistrationInfo |o--o| Country : "from"

Table reference

Table	PK	Cardinality	Prisma line
User	id (autoincrement)	One per player/admin	api.prisma:198
UserRole	(userId, role)	1-3 per user (User/Admin/SuperAdmin)	api.prisma:511
UserPermission	(userId, permissionKey)	Sparse, admin-only	api.prisma:468
Permission	key (string)	Static seed data	api.prisma:459
UserSession	sessionKey (string)	Multiple per user, pruned by TTL	api.prisma:480
UserMfaRecovery	(userId, recoveryCode)	~10 per MFA-enabled user	api.prisma:500
UserKyc	userId	0-1 per user	api.prisma:688
UserKycVerificationRequest	id (string)	Multiple per user (retry flow)	api.prisma:413
Country	code (ISO)	~250 seeded rows	api.prisma:379
CountryRegion	(countryCode, code)	Subdivisions of countries	api.prisma:396
UserFairnessSeeds	id (uuid)	0-1 per user (active pair)	api.prisma:146
UserInactiveFairnessSeeds	id (uuid)	Historical rotated seeds	api.prisma:166
UserSelfExclusion	id (autoincrement)	Responsible-gambling lockouts	api.prisma:339
UserGamblingLimits	id (autoincrement)	0-1 per user	api.prisma:351
UserConsents	(userId, type) unique	GDPR marketing consents	api.prisma:677
UserWithdrawalsBlock	userId	Admin-imposed withdrawal freeze	api.prisma:1547
RegistrationInfo	userId	Captured at signup	api.prisma:1526

Auth flow notes

• Password: nullable — Google/Steam OAuth users have no password (api.prisma:204).
• MFA: mfaSecret stores the TOTP secret; UserMfaRecovery holds one-time backup codes.
• Sessions: sessionKey is the JWT jti claim; sessionId groups related tokens. The session queue (apps/api/src/auth/session/session.queue-producer.ts) updates lastActivity asynchronously via BullMQ.
• Roles: the Role enum has three values: User, Admin, SuperAdmin (api.prisma:16-19). Guards check roles via UserRole join, not a column on User.
• KYC levels: LEVEL_0 through LEVEL_4 (api.prisma:432-437), each unlocking higher withdrawal limits.

Cross-references

• Flow docs: docs/flows/sign-in.md, docs/flows/password-reset.md, docs/flows/admin-sign-in.md.
• Fairness seeds: docs/flows/house-game.md (nonce-burn cycle).
• Security findings: SF-001 (email enumeration), SF-002 (lockout reset), SF-029 (SuperAdmin MFA bypass) in docs/security-register.md.