Player Affiliates

Tracks affiliate/referral links with metadata. Records are initially created when an affiliate link is clicked (storing the affiliate code), then linked to a player after registration.

Table Comment

Tracks affiliate links with metadata. Initially stores just affiliate_code, then links to player_id after registration.

Relationships

• Belongs to players via player_id (nullable — linked after registration)

Columns

Column	Type	Nullable	Default	Description
id	uuid	NO	gen_random_uuid()	Primary key
player_id	uuid	YES		FK to players. NULL until the visitor registers. Set after signup.
affiliate_code	varchar(255)	NO		The hash parameter from the affiliate URL (e.g., d5daff1d82620648029999e65ae60f2b). Globally unique.
metadata	jsonb	NO	'{}'	JSONB field storing all URL parameters: promotion_id, device info, IP, country, UTM tags, referrer, etc.
created_at	timestamp	NO	CURRENT_TIMESTAMP	When the affiliate link was first clicked/recorded
updated_at	timestamp	NO	CURRENT_TIMESTAMP	Last update (managed by trigger)

Key Indexes

Index	Columns	Notes
player_affiliates_pkey	id	Primary key
player_affiliates_affiliate_code_key	affiliate_code	Unique constraint
idx_player_affiliates_player_id	player_id	Player lookup
idx_player_affiliates_player_id_unique	player_id	Unique (WHERE player_id IS NOT NULL) — ensures one player per affiliate link
idx_player_affiliates_affiliate_code	affiliate_code	Fast code lookup
idx_player_affiliates_created_at	created_at	Time-based reporting

Triggers

Trigger	Function	Description
trigger_update_player_affiliates_updated_at	update_player_affiliates_updated_at	Auto-updates updated_at on row change

Business Rules

• The two-phase lifecycle:
  1. Link click: A record is created with affiliate_code and metadata but player_id = NULL
  2. Registration: The player_id is set when the visitor completes signup
• The unique constraint on player_id (where not null) ensures each player can only be attributed to one affiliate source
• The metadata JSONB captures the full marketing attribution data including UTM parameters, device info, and geo-data
• Affiliate codes are used to attribute players to marketing campaigns and calculate affiliate commissions
• Hidden coin packages with package_source = 'affiliate_hidden' can be unlocked via affiliate links