Coin Packages

Defines purchasable coin packages for sweepstakes brands. Each package specifies a price and the amount of Gold Coins (GC) and Sweepstakes Coins (SC) the player receives.

Relationships

• Belongs to brands via brand_id
• Has many coin_package_purchases via package_id
• Has many coin_package_player_assignments via package_id

Columns

Column	Type	Nullable	Default	Description
id	uuid	NO	gen_random_uuid()	Primary key
brand_id	uuid	NO		FK to brands. Which brand this package belongs to.
name	varchar(100)	NO		Display name shown to players in the store
system_name	varchar(100)	NO		Internal identifier used in code and APIs
description	text	YES		Marketing description for the package
price_amount	numeric(18)	NO		Purchase price
price_currency	varchar(10)	NO	'USD'	Currency for the price (e.g., USD, EUR)
gc_amount	numeric(18)	NO		Gold Coins granted on purchase
sc_amount	numeric(18)	NO	0	Sweepstakes Coins granted on purchase (the "free" promotional component)
badge	varchar(50)	YES		Badge text displayed on the package (e.g., "Best Value", "Popular")
badge_style	varchar(50)	YES		CSS class/style for badge display (e.g., badge-yellow, badge-blue, badge-red). Handled by frontend.
image_url	varchar(500)	YES		Package thumbnail image URL
banner_url	varchar(500)	YES		Package banner image URL (for featured/promoted packages)
sort_order	integer	NO	0	Legacy ordering field
display_priority	integer	NO	0	Primary sort field. Higher numbers appear first. Smartico packages should have highest priority.
is_active	boolean	NO	true	Whether the package is currently available for purchase
starts_at	timestamptz	YES		When the package becomes available. NULL = available immediately.
expires_at	timestamptz	YES		When the package expires. NULL = never expires.
available_countries	text[]	YES		Array of country codes where the package IS available. NULL = all countries.
restricted_countries	text[]	YES		Array of country codes where the package is NOT available
restricted_states	text[]	YES		Array of US state codes where the package is NOT available (e.g., WA, ID, NV, MT for sweepstakes compliance)
package_source	varchar(20)	NO	'standard'	Source/type of package. See values below.
availability_unit	varchar(10)	YES		Time unit for limited availability (e.g., hour, day)
availability_value	integer	YES		Number of time units for limited availability
created_at	timestamptz	NO	CURRENT_TIMESTAMP	Record creation timestamp
updated_at	timestamptz	NO	CURRENT_TIMESTAMP	Last update timestamp

Package Source Values

Value	Description
standard	Regular packages created by the brand admin
smartico	Personalized offers pushed via the Smartico CRM integration
affiliate_hidden	Hidden packages accessible only via affiliate/promotional links
unrestricted	Packages available to all players regardless of assignment rules

Key Indexes

Index	Columns	Notes
coin_packages_pkey	id	Primary key
idx_coin_packages_brand_active	brand_id, is_active	Active packages per brand
idx_coin_packages_brand_active_source	brand_id, is_active, package_source	Partial: WHERE is_active = true
idx_coin_packages_display_priority	display_priority DESC	Store ordering
idx_coin_packages_source	package_source	Filter by source type
idx_coin_packages_system_name	system_name	Lookup by internal name
idx_coin_packages_starts_at	starts_at	Time-based availability
idx_coin_packages_expires_at	expires_at	Expiration filtering

Business Rules

• Packages use a "purchase GC, get free SC" sweepstakes model for legal compliance
• restricted_states is critical for US sweepstakes law compliance (certain states prohibit sweepstakes)
• When a player purchases a package, a snapshot of the package details is stored in coin_package_purchases for audit purposes
• Smartico packages are assigned to specific players via coin_package_player_assignments
• The availability_unit + availability_value fields allow time-limited offers (e.g., "available for 24 hours after assignment")
• VIP players may receive additional coins via the store_multiplier in vip_levels