Coin Package Player Assignments

Junction table for assigning specific coin packages to individual players. Primarily used by the Smartico CRM integration to push personalized offers.

Table Comment

Junction table for assigning specific packages to specific players (managed by Smartico)

Relationships

• Belongs to players via player_id
• Belongs to coin_packages via package_id

Columns

Column	Type	Nullable	Default	Description
id	uuid	NO	gen_random_uuid()	Primary key
package_id	uuid	NO		FK to coin_packages. The package being assigned (typically package_source = smartico).
player_id	uuid	NO		FK to players. The player who can see this package.
assigned_at	timestamptz	NO	CURRENT_TIMESTAMP	When the assignment was created
expires_at	timestamptz	YES		When this assignment expires. NULL = never expires.
assigned_by	varchar(50)	NO	'smartico'	System that created the assignment (e.g., smartico, admin)
metadata	jsonb	YES		Additional data from the assigning system (e.g., campaign ID, segment info)
claimed	boolean	NO	false	Whether the player has purchased/claimed this assigned package
claimed_at	timestamptz	YES		When the player claimed the package
created_at	timestamptz	NO	CURRENT_TIMESTAMP	Record creation timestamp

Key Indexes

Index	Columns	Notes
coin_package_player_assignments_pkey	id	Primary key
idx_package_assignments_player	player_id	Find all assignments for a player
idx_package_assignments_package	package_id	Find all players assigned a package
idx_package_assignments_player_expires	player_id, expires_at	Active/valid assignments
idx_package_assignments_expires	expires_at	Partial: WHERE expires_at IS NOT NULL — for cleanup jobs
idx_package_assignments_unclaimed	player_id, claimed	Partial: WHERE claimed = false — fast lookup for pending offers

Business Rules

• Used primarily with the Smartico CRM to deliver personalized offers to targeted player segments
• Only packages with package_source = 'smartico' or package_source = 'affiliate_hidden' typically use this table
• When the player purchases the assigned package, claimed is set to true and claimed_at is recorded
• Expired assignments (expires_at < NOW()) are filtered out when displaying available packages
• The metadata field stores campaign and segmentation data from Smartico for analytics