Data model

A map of the main database entities and how they relate. Full DDL lives in supabase/migrations/; conventions and RLS are in Database & RLS.

Scoping backbone

tenants ──1:N── brands ──1:N── players ──1:1── player_profiles
   │                │                        (derived financial + lifecycle snapshot)
   │                └── nearly every table carries brand_id (isolation boundary)
   └── nearly every table carries tenant_id

• tenants — operator organizations.
• brands — skins under a tenant; the primary isolation boundary.
• players — identity (external_id = the operator/PAM’s id, unique per brand), email/phone, status, metadata (incl. metadata.custom for custom fields).
• player_profiles — one per player: derived stats (deposits, GGR/NGR, wagered, counts, last-activity), lifecycle_stage (+ changed/checked timestamps), ownership (host_user_id), scores.

Events & derivation

• player_events — append-only stream of everything a player does. Optional event_id for idempotency; raw payload retained (_raw when a mapping derived fields). The source of truth.
• player_sessions — play sessions.
• balance_transactions — append-only ledger of point/currency movements.

Derived state (player_profiles, lifecycle, scores) is computed from these by the worker and can always be recomputed.

Identity, auth & RBAC

• user_profiles — operator users (role, tenant).
• roles, permissions, role_permissions, user_brand_roles, user_permission_overrides — the RBAC model (RBAC & permissions).
• api_keys — operator API keys (SHA-256 hash, optional brand_id pin, scopes).
• Player auth: players.auth_user_id links a portal login to a player.

Segmentation & lifecycle

• segments (+ rules) and segment_memberships.
• player_lifecycle_events — the lifecycle transition log.

Campaigns, messaging & journeys

• campaigns (+ variants/sends), notification_templates (channel config JSONB), notification_queue (worker drains per channel).
• journeys (graph) and journey_runs (per-player execution).
• Delivery targets: player_inbox (in-app, Realtime), push_subscriptions (web-push).

Gamification & bonuses

• point_currencies, balances, levels, missions, badges, tournaments, leaderboards.
• bonuses and player_bonuses (wagering, eligibility).

Integration

• integration_mappings — one active, versioned per-brand mapping config (Mapping engine).
• webhooks, webhook_queue, webhook_deliveries — outbound signed callbacks.

Ops & config

• crm_tasks (+ comments, reminders), custom_field_definitions, audit_log, alert rules, report subscriptions, suppression, and the analytics aggregate functions (analytics_*, cohort_*, revenue_*, …).

Glossary

Domain terms used throughout the platform and these docs.