users
Core identity, authentication and account-state table
SQL
Identity
Critical
users
Purpose
Stores the primary account record for every user in BattleScore. This table underpins authentication, ownership, subscription state, policy acceptance, account status, and cross-area access to app, TO, and admin functionality.
Role in the system
users
├── user_sessions
├── password_resets
├── email_verifications
├── army_lists
├── games
├── tournaments / TO ownership
└── referrals / claims / profile flowsColumns
| Column | Type | Null | Notes |
|---|---|---|---|
id | bigint unsigned | No | Primary key |
email | varchar(255) | No | Login identity |
password_hash | varchar(255) | No | Hashed password |
first_name | varchar(80) | Yes | Profile field |
last_name | varchar(80) | Yes | Profile field |
display_name | varchar(120) | No | Public/user-facing identifier |
role | enum('user','admin') | No | Authorization role |
is_active | tinyint(1) | No | Account active flag |
suspended_at | datetime(3) | Yes | Suspension timestamp |
suspension_reason | varchar(255) | Yes | Suspension reason text |
email_verified_at | datetime(3) | Yes | Email verification completion timestamp |
last_login_at | datetime(3) | Yes | Most recent login |
failed_login_count | int | No | Lockout support |
locked_until | datetime(3) | Yes | Temporary lockout expiry |
first_game_used | tinyint(1) | No | Free-period logic flag |
subscription_status | enum('none','trial','active','past_due','canceled','expired') | No | Subscription state |
subscription_started_at | datetime(3) | Yes | Subscription start |
subscription_expires_at | datetime(3) | Yes | Subscription expiry |
cancel_at_period_end | tinyint(1) | No | Billing lifecycle flag |
billing_provider | varchar(30) | Yes | e.g. PayPal |
billing_customer_id | varchar(80) | Yes | External customer identifier |
billing_subscription_id | varchar(80) | Yes | External subscription identifier |
terms_accepted_at | datetime(3) | Yes | Terms acceptance timestamp |
privacy_accepted_at | datetime(3) | Yes | Privacy acceptance timestamp |
terms_version | varchar(20) | Yes | Accepted terms version |
privacy_version | varchar(20) | Yes | Accepted privacy version |
marketing_opt_in | tinyint(1) | No | Consent flag |
marketing_opt_in_at | datetime(3) | Yes | Consent timestamp |
analytics_opt_in | tinyint(1) | No | Consent flag |
analytics_opt_in_at | datetime(3) | Yes | Consent timestamp |
created_at | datetime(3) | No | Creation timestamp |
updated_at | datetime(3) | No | Last update timestamp |
deleted_at | datetime(3) | Yes | Soft delete marker |
Keys and indexes
PRIMARY KEY (id)UNIQUE KEY ux_users_email (email)UNIQUE KEY ux_users_display_name (display_name)idx_users_role (role)idx_users_subscription_status (subscription_status)idx_users_deleted_at (deleted_at)idx_users_email_verified_at (email_verified_at)
Relationships
| Relationship | Type | Notes |
|---|---|---|
user_sessions.user_id → users.id | One-to-many | Active and historical sessions |
password_resets.user_id → users.id | One-to-many | Password reset requests |
email_verifications.user_id → users.id | One-to-many | Email verification tokens |
army_lists.user_id → users.id | One-to-many | Army ownership |
games.user_id / opponent_user_id → users.id | Referenced externally | Game ownership and opponents |
Used by
- Signup, login, logout, me, verify, forgot, reset
- Profile and subscription flows
- Admin identity management
- Ownership checks across app, TO and admin
Design notes
- This table combines identity, auth state, profile data, subscription state, and consent tracking
- Role support already exists in schema via
role, even if not fully enforced everywhere - Soft delete exists alongside active/suspended/locked states, so account lifecycle is multi-layered
- Display name is unique and functions as a public-facing identifier
Risks
- This table is overloaded and central to nearly every subsystem
- State combinations can become complex: active vs suspended vs deleted vs locked
- Subscription and consent logic live alongside auth logic, increasing coupling
Recommended future improvements
- Document account lifecycle rules explicitly
- Ensure role-based authorization is consistently enforced everywhere
- Consider whether subscription data should remain embedded here long-term
- Add audit/change tracking for high-risk identity updates