Deepgames_ERD

Tables (20)

agent_work_logs

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • game_project_id — BIGINT
  • agent_type — VARCHAR(20) — NOT NULL
  • prompt — TEXT — NOT NULL
  • response — TEXT
  • credits_used — INT — NOT NULL
  • processing_time_ms — INT
  • model_used — VARCHAR(50)
  • status — VARCHAR(20)
  • created_at — TIMESTAMP
  • INDEX — idx_user_agent_logs
  • INDEX — idx_project_logs
  • INDEX — idx_agent_type

asset_categories

  • id — INT
  • category_name — VARCHAR(100) — NOT NULL
  • parent_id — INT
  • description — TEXT
  • icon_url — VARCHAR(500)
  • is_active — TINYINT(1)
  • INDEX — idx_parent_category

asset_purchases

  • id — BIGINT
  • asset_id — BIGINT — NOT NULL
  • buyer_id — BIGINT — NOT NULL
  • seller_id — BIGINT — NOT NULL
  • purchase_price — DECIMAL(10,2) — NOT NULL
  • royalty_amount — DECIMAL(10,2)
  • license_granted — VARCHAR(20) — NOT NULL
  • created_at — TIMESTAMP
  • INDEX — idx_buyer_purchases
  • INDEX — idx_seller_sales

assets

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • category_id — INT — NOT NULL
  • asset_name — VARCHAR(255) — NOT NULL
  • description — TEXT
  • asset_type — VARCHAR(20) — NOT NULL
  • file_url — VARCHAR(500) — NOT NULL
  • preview_url — VARCHAR(500)
  • file_size — BIGINT
  • file_format — VARCHAR(20)
  • price — DECIMAL(10,2)
  • license_type — VARCHAR(20)
  • dimensions — VARCHAR(20)
  • duration_seconds — INT
  • status — VARCHAR(20)
  • is_public — TINYINT(1)
  • download_count — BIGINT
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_asset_category
  • INDEX — idx_asset_creator
  • INDEX — idx_asset_type

credit_transactions

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • transaction_type — VARCHAR(20) — NOT NULL
  • amount — INT — NOT NULL
  • balance_after — INT — NOT NULL
  • description — VARCHAR(255)
  • reference_type — VARCHAR(20)
  • reference_id — BIGINT
  • created_at — TIMESTAMP
  • INDEX — idx_user_transactions
  • INDEX — idx_transaction_type

game_comments

  • id — BIGINT
  • game_project_id — BIGINT — NOT NULL
  • user_id — BIGINT — NOT NULL
  • parent_comment_id — BIGINT
  • comment_text — TEXT — NOT NULL
  • is_deleted — TINYINT(1)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_game_comments
  • INDEX — idx_user_comments
  • INDEX — idx_parent_comments

game_genres

  • id — INT
  • genre_code — VARCHAR(50) — NOT NULL
  • genre_name — VARCHAR(100) — NOT NULL
  • description — TEXT
  • is_active — TINYINT(1)

game_play_stats

  • id — BIGINT
  • game_project_id — BIGINT — NOT NULL
  • user_id — BIGINT
  • ip_address — VARCHAR(45)
  • user_agent — TEXT
  • play_duration_seconds — INT
  • score — BIGINT
  • completed — TINYINT(1)
  • created_at — TIMESTAMP
  • INDEX — idx_game_stats
  • INDEX — idx_user_stats

game_project_media

  • id — BIGINT
  • game_project_id — BIGINT — NOT NULL
  • media_type — VARCHAR(20) — NOT NULL
  • file_url — VARCHAR(500) — NOT NULL
  • file_size — BIGINT
  • display_order — INT
  • created_at — TIMESTAMP
  • INDEX — idx_project_media

game_project_tags

  • game_project_id — BIGINT — Primary key — NOT NULL
  • tag_id — INT — Primary key — NOT NULL
  • created_at — TIMESTAMP

game_project_versions

  • id — BIGINT
  • game_project_id — BIGINT — NOT NULL
  • version_number — VARCHAR(20) — NOT NULL
  • game_code — LONGTEXT — NOT NULL
  • change_description — TEXT
  • is_current — TINYINT(1)
  • created_by — BIGINT — NOT NULL
  • created_at — TIMESTAMP
  • INDEX — idx_project_versions

game_projects

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • genre_id — INT — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • description — TEXT
  • thumbnail_url — VARCHAR(500)
  • game_code — LONGTEXT
  • status — VARCHAR(20)
  • is_public — TINYINT(1)
  • version — VARCHAR(20)
  • play_count — BIGINT
  • like_count — BIGINT
  • rating_avg — DECIMAL(3,2)
  • rating_count — INT
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • published_at — TIMESTAMP
  • INDEX — idx_user_projects
  • INDEX — idx_public_games
  • INDEX — idx_genre_games
  • INDEX — idx_rating

game_ratings

  • id — BIGINT
  • game_project_id — BIGINT — NOT NULL
  • user_id — BIGINT — NOT NULL
  • rating — TINYINT
  • review — TEXT
  • is_liked — TINYINT(1)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_game_ratings

game_revenue

  • id — BIGINT
  • game_project_id — BIGINT — NOT NULL
  • user_id — BIGINT — NOT NULL
  • revenue_type — VARCHAR(30) — NOT NULL
  • gross_amount — DECIMAL(10,2) — NOT NULL
  • platform_fee — DECIMAL(10,2) — NOT NULL
  • net_amount — DECIMAL(10,2) — NOT NULL
  • currency_code — VARCHAR(3)
  • revenue_date — DATE — NOT NULL
  • payout_status — VARCHAR(20)
  • created_at — TIMESTAMP
  • INDEX — idx_game_revenue
  • INDEX — idx_user_revenue

game_tags

  • id — INT
  • tag_name — VARCHAR(50) — NOT NULL
  • tag_color — VARCHAR(7)
  • usage_count — INT
  • is_active — TINYINT(1)

subscription_plans

  • id — INT
  • plan_code — VARCHAR(50) — NOT NULL
  • plan_name — VARCHAR(100) — NOT NULL
  • description — TEXT
  • monthly_price — DECIMAL(10,2) — NOT NULL
  • monthly_credits — INT — NOT NULL
  • max_projects — INT
  • can_commercial_use — TINYINT(1)
  • can_download_code — TINYINT(1)
  • royalty_rate — DECIMAL(5,2)
  • is_active — TINYINT(1)
  • created_at — TIMESTAMP

user_oauth_accounts

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • provider — VARCHAR(20) — NOT NULL
  • provider_user_id — VARCHAR(255) — NOT NULL
  • provider_email — VARCHAR(255)
  • access_token — TEXT
  • refresh_token — TEXT
  • token_expires_at — TIMESTAMP
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_user_oauth

user_sessions

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • refresh_token_hash — VARCHAR(255) — NOT NULL
  • device_fingerprint — VARCHAR(255)
  • ip_address — VARCHAR(45)
  • user_agent — TEXT
  • is_active — TINYINT(1)
  • expires_at — TIMESTAMP — NOT NULL
  • last_used_at — TIMESTAMP
  • created_at — TIMESTAMP
  • INDEX — idx_user_sessions
  • INDEX — idx_refresh_token
  • INDEX — idx_expires_at

user_subscriptions

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • plan_id — INT — NOT NULL
  • status — VARCHAR(20)
  • current_credits — INT
  • starts_at — TIMESTAMP — NOT NULL
  • ends_at — TIMESTAMP
  • auto_renew — TINYINT(1)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_user_subscription
  • INDEX — idx_subscription_expires

users

  • id — BIGINT
  • email — VARCHAR(255) — NOT NULL
  • username — VARCHAR(50)
  • password_hash — VARCHAR(255)
  • display_name — VARCHAR(100)
  • avatar_url — VARCHAR(500)
  • bio — TEXT
  • email_verified — TINYINT(1)
  • is_active — TINYINT(1)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • last_login_at — TIMESTAMP
  • INDEX — idx_email
  • INDEX — idx_username