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