test2
Tables (18)
api_key_platforms
- id — bigint — Primary key — NOT NULL
- api_key_id — bigint — Foreign key → api_keys.id — NOT NULL
- platform — varchar(20) — NOT NULL — android | ios | web
- app_identifier — varchar(255) — NOT NULL
- cert_fingerprint — varchar(128)
- is_active — tinyint — NOT NULL
- created_at — timestamp
- updated_at — timestamp
api_keys
- id — bigint — Primary key — NOT NULL
- key_id — varchar(64) — NOT NULL
- key_type — varchar(20) — NOT NULL — sdk | server | admin
- environment — varchar(10) — NOT NULL — DEV | LIVE
- project_id — varchar(20)
- secret_encrypted — text
- secret_iv — varchar(32)
- secret_auth_tag — varchar(32)
- ip_whitelist — json
- rate_limit_per_min — int
- expires_at — timestamp
- created_at — timestamp
- revoked_at — timestamp
- last_used_at — timestamp
audit_logs
- id — bigint — Primary key — NOT NULL
- action — varchar(50) — NOT NULL
- actor_type — varchar(20) — NOT NULL — system | api_key | user
- actor_id — varchar(128)
- resource_type — varchar(50) — NOT NULL
- resource_id — varchar(128)
- changes — json
- ip_address — varchar(45)
- user_agent — text
- environment — varchar(10) — NOT NULL
- created_at — timestamp — NOT NULL
campaigns
- id — bigint — Primary key — NOT NULL
- campaign_id — varchar(64) — NOT NULL
- project_id — varchar(20)
- campaign_name — varchar(200) — NOT NULL
- description — text
- status — varchar(20) — NOT NULL — DRAFT | SCHEDULED | IN_PROGRESS | SETTLEMENT | COMPLETED | CANCELLED
- start_date — timestamp
- end_date — timestamp
- coupon_valid_from — timestamp
- coupon_valid_until — timestamp
- contribution_config — json
- environment — varchar(10) — NOT NULL
- created_at — timestamp
- updated_at — timestamp
coupon_packages
- id — bigint — Primary key — NOT NULL
- campaign_id — varchar(64) — NOT NULL — References campaigns (no FK)
- project_id — varchar(20) — NOT NULL
- package_no — int — NOT NULL
- package_name — varchar(200)
- reward_data — json — NOT NULL
- max_redemptions_per_user — int
- max_redemptions_global — int — NULL = unlimited
- current_redemptions — int
- valid_from — timestamp
- valid_until — timestamp
- environment — varchar(10) — NOT NULL
- is_active — tinyint — NOT NULL
- created_at — timestamp
- updated_at — timestamp
- coupon_code — varchar(100) — Legacy coupon code (NULL for SmartPlay)
- coupon_type — varchar(20) — NOT NULL — SMARTPLAY | LEGACY_SINGLE | LEGACY_MULTI
coupon_redemptions
- id — bigint — Primary key — NOT NULL
- coupon_id — bigint — Deprecated - use package_id
- package_id — bigint — Foreign key → coupon_packages.id — NOT NULL — References coupon_packages
- user_id — varchar(128) — NOT NULL — External game user ID
- device_id — bigint — Foreign key → devices.id — Optional - may not be available in Server APIs
- redeemed_at — timestamp
- ip_address — varchar(45)
- reward_issued — tinyint
- metadata — json
- campaign_id_ref — varchar(64)
- package_no — int
- creator_id — varchar(128) — Creator attribution for SmartPlay
- environment — varchar(10) — NOT NULL
creator_contributions
- id — bigint — Primary key — NOT NULL
- creator_id — varchar(128) — NOT NULL
- campaign_id — varchar(64) — NOT NULL — References campaigns (no FK)
- user_id — varchar(128) — NOT NULL — External game user ID
- contribution_type — varchar(50) — NOT NULL — SPONSOR_REGISTRATION | FIRST_PURCHASE | PURCHASE_AMOUNT | COUPON_REDEMPTION
- points — int — NOT NULL
- reference_id — varchar(128)
- metadata — json
- environment — varchar(10) — NOT NULL
- created_at — timestamp
creator_coupon_stats
- id — bigint — Primary key — NOT NULL
- package_id — bigint — Foreign key → coupon_packages.id — NOT NULL — References coupon_packages
- creator_id — varchar(128) — NOT NULL
- redemption_count — int — NOT NULL
- environment — varchar(10) — NOT NULL
- created_at — timestamp
- updated_at — timestamp
creator_keys
- id — bigint — Primary key — NOT NULL
- project_id — varchar(20) — NOT NULL
- campaign_id — varchar(64) — NOT NULL — References campaigns (no FK)
- creator_id — varchar(128) — NOT NULL — External creator ID from platform
- creator_key — varchar(5) — NOT NULL — 5-char alphanumeric key
- environment — varchar(10) — NOT NULL
- is_active — tinyint — NOT NULL
- created_at — timestamp
- updated_at — timestamp
device_attestation_keys
- id — bigint — Primary key — NOT NULL
- device_id — bigint — Foreign key → devices.id — NOT NULL
- platform — varchar(20) — NOT NULL — ios | android
- key_id — varchar(255) — iOS App Attest key ID
- public_key — text — iOS public key
- attestation — text — iOS attestation data
- counter — int — iOS assertion counter
- integrity_token — text — Android integrity token
- device_key — text — Android device key
- verdict_cache — json — Android cached verdict
- last_verified_at — timestamp
- created_at — timestamp
- updated_at — timestamp
devices
- id — bigint — Primary key — NOT NULL
- device_id — varchar(128) — NOT NULL
- project_id — varchar(20) — NOT NULL — Multi-project support
- user_id — varchar(128) — External game user ID
- platform — varchar(20) — NOT NULL — iOS | Android | Windows | Mac | WebGL
- device_model — varchar(100)
- os_version — varchar(50)
- locale — varchar(10)
- timezone_offset — int
- screen_width — int
- screen_height — int
- dpi — int
- ip_address — varchar(45)
- integrity_score — decimal(3, 2)
- advertising_id — varchar(128) — IDFA/GAID
- att_status — varchar(20) — iOS ATT status
- limited_ad_tracking — tinyint
- environment — varchar(10) — NOT NULL
- first_seen_at — timestamp
- last_seen_at — timestamp
- metadata — json
events
- id — bigint — Primary key — NOT NULL
- event_id — varchar(64) — NOT NULL
- project_id — varchar(20) — NOT NULL — Multi-project support
- user_id — varchar(128) — NOT NULL — External game user ID
- device_id — bigint — Foreign key → devices.id
- event_type — varchar(50) — NOT NULL
- event_timestamp — timestamp — NOT NULL
- stage_id — varchar(100)
- stage_name — varchar(200)
- difficulty — varchar(50)
- score — int
- duration — int
- custom_params — json
- sdk_version — varchar(20)
- environment — varchar(10) — NOT NULL
- created_at — timestamp
install_attributions
- id — bigint — Primary key — NOT NULL
- project_id — varchar(20) — NOT NULL — Multi-project support
- user_id — varchar(128) — NOT NULL — External game user ID
- device_id — bigint — Foreign key → devices.id — NOT NULL
- network — varchar(100)
- campaign — varchar(200)
- adgroup — varchar(200)
- creative — varchar(200)
- click_id — varchar(255)
- gclid — varchar(255)
- fbclid — varchar(255)
- af_status — varchar(50)
- media_source — varchar(100)
- attributed_at — timestamp
- environment — varchar(10) — NOT NULL
- created_at — timestamp
- raw_data — json
purchases
- id — bigint — Primary key — NOT NULL
- transaction_id — varchar(128) — NOT NULL
- project_id — varchar(20) — NOT NULL — Multi-project support
- user_id — varchar(128) — NOT NULL — External game user ID
- device_id — bigint — Foreign key → devices.id
- platform — varchar(20) — NOT NULL — iOS | Android
- product_id — varchar(100) — NOT NULL
- product_name — varchar(200)
- currency — varchar(3) — NOT NULL
- amount — decimal(10, 2) — NOT NULL
- receipt — text
- receipt_verified — tinyint
- verified_at — timestamp
- purchased_at — timestamp — NOT NULL
- environment — varchar(10) — NOT NULL
- created_at — timestamp
- metadata — json
sessions
- id — bigint — Primary key — NOT NULL
- session_id — varchar(64) — NOT NULL
- user_id — varchar(128) — NOT NULL — External game user ID
- device_id — bigint — Foreign key → devices.id — NOT NULL
- api_key_id — bigint — Foreign key → api_keys.id — NOT NULL
- environment — varchar(10) — NOT NULL
- issued_at — timestamp
- expires_at — timestamp — NOT NULL
- revoked_at — timestamp
- refresh_token_hash — varchar(128)
- refresh_expires_at — timestamp
- metadata — json
user_sponsor_history
- id — bigint — Primary key — NOT NULL
- user_id — varchar(128) — NOT NULL — External game user ID
- campaign_id — varchar(64) — NOT NULL — References campaigns (no FK)
- from_creator_id — varchar(128) — NULL for initial registration
- to_creator_id — varchar(128) — NOT NULL
- changed_at — timestamp — NOT NULL
- change_reason — varchar(50) — INITIAL | USER_CHANGE | ADMIN_CHANGE
- environment — varchar(10) — NOT NULL
- metadata — json
user_sponsors
- id — bigint — Primary key — NOT NULL
- user_id — varchar(128) — NOT NULL — External game user ID
- campaign_id — varchar(64) — NOT NULL — References campaigns (no FK)
- creator_id — varchar(128) — NOT NULL
- registered_at — timestamp
- changed_at — timestamp
- previous_creator_id — varchar(128)
- change_count — int
- environment — varchar(10) — NOT NULL
webhook_logs
- id — bigint — Primary key — NOT NULL
- webhook_type — varchar(50) — NOT NULL
- payload — json — NOT NULL
- target_url — varchar(500) — NOT NULL
- http_status — int
- response_body — text
- attempt_count — int
- last_attempt_at — timestamp
- next_retry_at — timestamp
- succeeded_at — timestamp
- environment — varchar(10) — NOT NULL
- created_at — timestamp