User Management Documentation

📋 Overview

The User Management System is a normalized, relational database architecture designed to handle user authentication, authorization, profile data, device management, sessions, and membership-based quota tracking.

The design follows these principles:

Separation of Concerns - Each table has a single, clear responsibility
Normalization - Eliminates data duplication through proper relationships
Scalability - Easy to extend with new features without modifying core tables
Performance - Indexed properly for common queries
Audit Trail - Session tracking and usage logging for analytics

🏗️ Architecture

┌─────────────────────────────────────────────────────────────────────────────┐
│                              USER MANAGEMENT SYSTEM                          │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│  ┌──────────────┐     ┌──────────────┐     ┌──────────────┐                │
│  │  app_users   │────▶│user_profiles │     │user_addresses│                │
│  │  (Core Auth) │     │  (1:1)       │     │  (1:N)       │                │
│  └──────┬───────┘     └──────────────┘     └──────────────┘                │
│         │                                                                   │
│         ├─────────────────────────────────────────────────────────────────┤
│         │                                                                   │
│         ▼                                                                    │
│  ┌─────────────────────────────────────────────────────────────────────┐ │
│  │                        AUTHORIZATION (RBAC)                            │ │
│  │  ┌──────────┐    ┌──────────────┐    ┌───────────────────────────┐    │ │
│  │  │  roles   │◀──▶│user_roles    │    │user_permissions (overrides)│    │ │
│  │  └────┬─────┘    └──────────────┘    └───────────────────────────┘    │ │
│  │       │                                                               │ │
│  │       ▼                                                               │ │
│  │  ┌──────────────┐    ┌──────────────┐                               │ │
│  │  │ permissions  │◀──▶│role_permissions│                              │ │
│  │  └──────────────┘    └──────────────┘                               │ │
│  └─────────────────────────────────────────────────────────────────────┘ │
│                                                                              │
│         ├─────────────────────────────────────────────────────────────────┤
│         │                                                                   │
│         ▼                                                                    │
│  ┌─────────────────────────────────────────────────────────────────────┐ │
│  │                         MEMBERSHIP SYSTEM                             │ │
│  │  ┌──────────────┐    ┌──────────────────────┐                       │ │
│  │  │ memberships  │◀──▶│membership_features  │                       │ │
│  │  └──────┬───────┘    └──────────────────────┘                       │ │
│  │         │                                                               │ │
│  │         ▼                                                               │ │
│  │  ┌──────────────────┐    ┌──────────────────────┐                    │ │
│  │  │user_memberships  │◀──▶│user_membership_quotas│                    │ │
│  │  └──────────────────┘    └──────────────────────┘                    │ │
│  │                                │                                       │ │
│  │                                ▼                                       │ │
│  │                     ┌──────────────────┐                              │ │
│  │                     │user_usage_logs   │ (Transaction History)        │ │
│  │                     └──────────────────┘                              │ │
│  └─────────────────────────────────────────────────────────────────────┘ │
│                                                                              │
│         ├─────────────────────────────────────────────────────────────────┤
│         │                                                                   │
│         ▼                                                                    │
│  ┌─────────────────────────────────────────────────────────────────────┐ │
│  │                      DEVICE & SESSION TRACKING                         │ │
│  │  ┌──────────────┐    ┌──────────────┐    ┌──────────────────────┐    │ │
│  │  │user_devices  │    │user_sessions │    │user_login_stats      │    │ │
│  │  │(FCM tokens)  │    │(Active/Logs) │    │(Login counts)        │    │ │
│  │  └──────────────┘    └──────────────┘    └──────────────────────┘    │ │
│  └─────────────────────────────────────────────────────────────────────┘ │
│                                                                              │
│         ├─────────────────────────────────────────────────────────────────┤
│         │                                                                   │
│         ▼                                                                    │
│  ┌─────────────────────────────────────────────────────────────────────┐ │
│  │                        LOCATION INTEGRATION                             │ │
│  │  ┌──────────────┐    ┌──────────────┐    ┌──────────────────┐        │ │
│  │  │  countries   │◀──▶│   states     │◀──▶│     cities       │        │ │
│  │  │ (multi-lang) │    │ (multi-lang) │    │  (multi-lang)    │        │ │
│  │  └──────────────┘    └──────────────┘    └──────────────────┘        │ │
│  │                        ▲                                            │        │ │
│  │                        │                                            │        │ │
│  │                        └── user_addresses (stores codes only)       │        │ │
│  └─────────────────────────────────────────────────────────────────────┘ │
│                                                                              │
└─────────────────────────────────────────────────────────────────────────────┘

📊 Table Reference

Complete list of all tables in the user management system:

🔐 Core Authentication

app_users - Core user data
user_profiles - Profile details
user_addresses - Address data (with location integration)

🌍 Location Tables (Multi-Language)

countries - Country names by language
states - State names by language
cities - City names by language

🛡️ RBAC System

roles - Role definitions
permissions - Permission definitions
role_permissions - Role-permission mapping
user_roles - User-role assignments
user_permissions - User permission overrides

💎 Membership System

memberships - Membership plans
membership_features - Plan features
user_memberships - User subscriptions
user_membership_quotas - Current period usage
user_usage_logs - Usage history

📱 Device & Sessions

user_devices - Device & FCM tokens
user_sessions - Active sessions
user_login_stats - Login statistics

🗄️ Detailed Schema

app_users

Core authentication table. user_id serves as both the primary identifier and the user's referral code.

Field	Type	Attributes	Description
`id`	BIGINT	PK, Auto Increment	Internal primary key
`user_id`	VARCHAR(32)	UNIQUE	Public identifier & referral code
`username`	VARCHAR(255)	UNIQUE, NULLABLE	Username (optional)
`email`	VARCHAR(255)	UNIQUE, NULLABLE	Email address
`phone`	VARCHAR(20)	UNIQUE	Phone number (primary identifier)
`password`	VARCHAR(255)	NULLABLE	Bcrypt hashed password
`status`	TINYINT	Default: 0	0=inactive, 1=active, 2=suspended
`email_verified`	BOOLEAN	Default: false	Email verification status
`phone_verified`	BOOLEAN	Default: false	Phone verification status
`last_login_at`	TIMESTAMP	NULLABLE	Last successful login
`created_at`	TIMESTAMP	Auto	Account creation time
`updated_at`	TIMESTAMP	Auto	Last update time
`deleted_at`	TIMESTAMP	NULLABLE	Soft delete timestamp

user_profiles

One-to-one relationship with app_users. Stores profile information.

Field	Type	Attributes	Description
`id`	BIGINT	PK, Auto Increment	Primary key
`user_id`	VARCHAR(32)	FK, UNIQUE	References app_users.user_id
`full_name`	VARCHAR(200)	NULLABLE	User's full name
`gender`	TINYINT	NULLABLE	0=male, 1=female, 2=other, 3=prefer_not_to_say
`image_url`	VARCHAR(500)	NULLABLE	Profile picture URL
`language`	VARCHAR(10)	Default: 'en'	Preferred language
`country`	CHAR(2)	NULLABLE	ISO 3166-1 alpha-2 country code
`timezone`	VARCHAR(50)	Default: 'Asia/Kolkata'	User's timezone

user_addresses

One-to-many relationship. Users can have multiple addresses. Integrated with location tables (countries, states, cities) for language-specific address retrieval.

🌍 Location Integration

Addresses store location codes (not IDs) which allows for flexible language lookups:

country_code → References countries.cnty_code
state_code → References states.st_code
city_code → References cities.cty_code

Each location can have multiple language records. The address is retrieved in the user's preferred language automatically.

Field	Type	Attributes	Description
`id`	BIGINT	PK, Auto Increment	Primary key
`user_id`	VARCHAR(32)	FK	References app_users.user_id
`address_type`	ENUM	default: both	billing, shipping, both
`address`	TEXT	NOT NULL	Street address (multiline supported)
`country_code`	VARCHAR(10)	FK	References countries.cnty_code
`state_code`	VARCHAR(10)	FK	References states.st_code
`city_code`	VARCHAR(10)	FK	References cities.cty_code
`postal_code`	VARCHAR(20)	NOT NULL	Zip/Postal code
`is_primary`	BOOLEAN	default: false	Primary address flag

🌍 Location Integration

The user_addresses table integrates with the location system for language-specific address retrieval. This ensures users always see location names in their preferred language.

How It Works

┌────────────────────────────────────────────────────────────────────────────┐
│                    LANGUAGE-SPECIFIC ADDRESS RETRIEVAL                        │
└────────────────────────────────────────────────────────────────────────────┘

user_addresses Table:
├── Stores: country_code, state_code, city_code (codes only)
└── Does NOT store: country_name, state_name, city_name

Location Tables (Multi-Language):
├── countries: cnty_code, cnty_name, cnty_lng ('IN', 'India', 'en')
│                         │           ('IN', 'भारत', 'hi')
├── states:   st_code, st_name, st_lng, cnty_code
└── cities:   cty_code, cty_name, cty_lng, st_code

Retrieval Flow:
1. Get user's preferred language from user_profiles.language
2. Query location tables matching the language code
3. Fallback to is_default=true if language not available
4. Return formatted address in user's language

Example Query:
SELECT ua.address, ua.postal_code,
       c.cty_name AS city,      -- Filtered by user's language
       s.st_name AS state,       -- Filtered by user's language
       cnt.cty_name AS country   -- Filtered by user's language
FROM user_addresses ua
JOIN cities c   ON c.cty_code = ua.city_code AND c.cty_lng = 'hi'
JOIN states s  ON s.st_code = ua.state_code AND s.st_lng = 'hi'
JOIN countries cnt ON cnt.cnty_code = ua.country_code AND cnt.cnty_lng = 'hi'
WHERE ua.user_id = 'ABC123'

Result for Hindi speaker:
"123 Main Street, मुंबई, महाराष्ट्र, 400001"

Result for English speaker:
"123 Main Street, Mumbai, Maharashtra, 400001"

UserAddress Model Helper Methods

// Get location name in specific language
$address->getCountryName('hi');  // Returns 'भारत' for India
$address->getStateName('en');   // Returns 'Maharashtra'
$address->getCityName('hi');    // Returns 'मुंबई'

// Get formatted address in user's preferred language
$language = $user->profile->language ?? 'en';
$formatted = $address->getFormattedAddress($language, includeCountry: true);
// Output: "123 Main Street, Mumbai, Maharashtra, 400001, India"

// Get short address (city, state only)
$short = $address->getShortAddress($language);
// Output: "Mumbai, Maharashtra"

// Get complete location data as array
$locationData = $address->getLocationData($language);
/*
[
    'address' => '123 Main Street',
    'city' => 'Mumbai',
    'state' => 'Maharashtra',
    'country' => 'India',
    'postal_code' => '400001',
    'formatted' => '123 Main Street, Mumbai, Maharashtra, 400001, India',
    'codes' => [
        'country_code' => 'IN',
        'state_code' => 'MH',
        'city_code' => 'MUM',
    ],
]
*/

// Laravel accessor (uses user's language automatically)
$address->formatted;  // Auto-detects language from user profile
$address->short;       // Same for short version

Available Scopes

// Get primary address only
UserAddress::primary()->get();

// Get addresses by type
UserAddress::byType('billing')->get();
UserAddress::byType('shipping')->get();

// Get billing addresses (billing OR both)
UserAddress::billing()->get();

// Get shipping addresses (shipping OR both)
UserAddress::shipping()->get();

Creating an Address

use App\Models\UserAddress;

// Create a new address using location codes
$address = UserAddress::create([
    'user_id' => 'ABC123',
    'address_type' => 'both',
    'address' => '123 Main Street, Apt 4B',
    'country_code' => 'IN',      // References countries.cnty_code
    'state_code' => 'MH',         // References states.st_code
    'city_code' => 'MUM',         // References cities.cty_code
    'postal_code' => '400001',
    'is_primary' => true,
]);

// The location names are NOT stored - they're looked up
// based on the user's language when retrieved

RBAC Tables (roles, permissions, role_permissions, user_roles, user_permissions)

Role-Based Access Control system with hierarchical roles and flexible permissions.

roles

id - BIGINT PK
name - VARCHAR(50) UNIQUE
slug - VARCHAR(50) UNIQUE
description - TEXT NULLABLE
level - TINYINT (hierarchy)

permissions

id - BIGINT PK
name - VARCHAR(100) UNIQUE
slug - VARCHAR(100) UNIQUE
description - TEXT NULLABLE
module - VARCHAR(50) (grouping)

Membership Tables

Membership system with quota tracking for features like API calls, posts, and storage.

memberships

Defines available membership plans.

id - BIGINT PK
name - VARCHAR(100) (Free, Basic, Pro, etc.)
slug - VARCHAR(50) UNIQUE
duration_days - INT NULL (NULL = lifetime)
price - DECIMAL(10,2)
currency - CHAR(3) Default: 'INR'
level - TINYINT (hierarchy)
is_active - BOOLEAN

membership_features

Defines what each membership plan includes.

id - BIGINT PK
membership_id - FK to memberships
feature_key - VARCHAR(50) (api_calls, posts, storage)
feature_name - VARCHAR(100) (Display name)
limit_value - INT NULL (NULL = unlimited)
period - ENUM (lifetime, monthly, yearly, daily)

user_memberships

User's active subscriptions.

id - BIGINT PK
user_id - FK to app_users
membership_id - FK to memberships
status - ENUM (active, expired, cancelled, pending)
start_date - DATE
end_date - DATE NULLABLE
auto_renew - BOOLEAN

user_membership_quotas

Current period quota tracking. Updated frequently.

id - BIGINT PK
user_id - FK to app_users
user_membership_id - FK to user_memberships
feature_key - VARCHAR(50)
limit_value - INT NULL (from membership_features)
used_value - INT Default: 0
remaining_value - INT NULL (NULL = unlimited)
period_start - DATE
period_end - DATE

user_usage_logs

Transaction history of all usage. Append-only table for audit trail.

id - BIGINT PK
user_id - FK to app_users
feature_key - VARCHAR(50)
action_type - VARCHAR(50) (api_call, post_create, etc.)
quantity - INT Default: 1
resource_id - VARCHAR(100) NULLABLE (reference)
metadata - JSON NULLABLE (context)
created_at - TIMESTAMP

Device & Session Tables

user_devices

Stores device info and FCM tokens for push notifications.

id - BIGINT PK
user_id - FK to app_users
device_id - VARCHAR(255) UNIQUE per user
device_name - VARCHAR(100) NULLABLE
platform - ENUM (ios, android, web, other)
fcm_token - VARCHAR(500) NULLABLE
is_active - BOOLEAN
last_login_at - TIMESTAMP NULLABLE

user_sessions

Active and historical user sessions.

id - BIGINT PK
user_id - FK to app_users
device_id - VARCHAR(255) NULLABLE
ip_address - VARCHAR(45) NULLABLE (IPv6 compatible)
user_agent - TEXT NULLABLE
login_at - TIMESTAMP
logout_at - TIMESTAMP NULLABLE
last_activity_at - TIMESTAMP (auto-updated)
is_active - BOOLEAN

user_login_stats

Aggregated login statistics (1:1 with app_users).

user_id - VARCHAR(32) PRIMARY KEY
app_login_count - INT Default: 0
web_login_count - INT Default: 0
total_login_count - INT Default: 0
last_login_at - TIMESTAMP NULLABLE
last_login_ip - VARCHAR(45) NULLABLE

📈 Usage Tracking Flow

How the membership quota system works:

┌────────────────────────────────────────────────────────────────────────────┐
│                        QUOTA CHECK & USAGE FLOW                             │
└────────────────────────────────────────────────────────────────────────────┘

1. USER INITIATES ACTION (e.g., Create Post)
   │
   ▼
2. GET USER'S ACTIVE MEMBERSHIP
   └──▶ SELECT * FROM user_memberships
       WHERE user_id = ? AND status = 'active'
       AND (end_date IS NULL OR end_date > NOW())
   │
   ▼
3. GET MEMBERSHIP FEATURES
   └──▶ SELECT * FROM membership_features
       WHERE membership_id = ?
   │
   ▼
4. GET CURRENT QUOTA STATE
   └──▶ SELECT * FROM user_membership_quotas
       WHERE user_membership_id = ?
       AND feature_key = 'posts'
       AND period_start <= NOW()
       AND period_end > NOW()
   │
   ▼
5. CHECK IF QUOTA AVAILABLE
   │
   ├──▶ IF remaining_value > 0 OR remaining_value IS NULL:
   │     │
   │     ├── LOG USAGE (user_usage_logs)
   │     │   └──▶ INSERT INTO user_usage_logs
   │     │       (user_id, feature_key, action_type, quantity, created_at)
   │     │
   │     ├── UPDATE QUOTA (user_membership_quotas)
   │     │   └──▶ UPDATE user_membership_quotas
   │     │       SET used_value = used_value + 1,
   │     │           remaining_value = GREATEST(0, remaining_value - 1)
   │     │       WHERE id = ?
   │     │
   │     └──▶ PROCESS REQUEST (Return success)
   │
   └──▶ ELSE (Quota exceeded):
        └──▶ RETURN ERROR "Quota exceeded for this month"
            Suggest upgrade to higher membership

6. PERIODIC QUOTA RESET (Scheduled Job - Daily)
   └──▶ For each active user_membership:
        ├── Check if period has ended
        ├── If yes, create new quota records for new period
        └── Archive old usage_logs to separate table (optional)

🔢 Constants Reference

Status Values (app_users.status)

Value	Constant	Description
`0`	`STATUS_INACTIVE`	Inactive/Pending account
`1`	`STATUS_ACTIVE`	Active account
`2`	`STATUS_SUSPENDED`	Suspended account

Gender Values (user_profiles.gender)

Value	Constant	Description
`0`	`GENDER_MALE`	Male
`1`	`GENDER_FEMALE`	Female
`2`	`GENDER_OTHER`	Other
`3`	`GENDER_PREFER_NOT_TO_SAY`	Prefer not to say

📦 Laravel Models

Eloquent models with relationships:

// AppUser Model (app_users table)
class AppUser extends Authenticatable implements JWTSubject
{
    protected $table = 'app_users';
    protected $fillable = ['user_id', 'username', 'email', 'phone', 'password', ...];

    // Relationships
    public function profile() { return $this->hasOne(UserProfile::class, 'user_id'); }
    public function addresses() { return $this->hasMany(UserAddress::class, 'user_id'); }
    public function devices() { return $this->hasMany(UserDevice::class, 'user_id'); }
    public function sessions() { return $this->hasMany(UserSession::class, 'user_id'); }
    public function loginStats() { return $this->hasOne(UserLoginStats::class, 'user_id'); }
    public function roles() { return $this->belongsToMany(Role::class, 'user_roles', 'user_id', 'role_id'); }
    public function permissions() { return $this->belongsToMany(Permission::class, 'user_permissions', 'user_id', 'permission_id'); }
    public function memberships() { return $this->hasMany(UserMembership::class, 'user_id'); }
}

// Usage Example
$user = AppUser::with('profile', 'roles.permissions')->where('user_id', $userId)->first();

// Check if user has specific permission
$hasPermission = $user->roles->contains('slug', 'admin') ||
                 $user->permissions()->where('slug', 'users.create')->exists();

// Get user's active membership with features
$activeMembership = $user->memberships()
    ->where('status', 'active')
    ->where('end_date', '>', now())
    ->with('membership.features')
    ->first();

🌱 Seeders

Run seeders to populate reference data:

        
# Run all user management seeders
php artisan db:seed --class=UserManagementSeeder

# Individual seeders
php artisan db:seed --class=RoleSeeder
php artisan db:seed --class=PermissionSeeder
php artisan db:seed --class=RolePermissionSeeder
php artisan db:seed --class=MembershipSeeder
php artisan db:seed --class=MembershipFeatureSeeder
        
    

Default Roles Created:

Super Admin (level: 100) - Full access
Admin (level: 80) - Administrative access
Moderator (level: 60) - Content moderation
User (level: 20) - Standard user
Guest (level: 10) - Limited access

Default Memberships Created:

Free - ₹0 (100 API calls, 10 posts/month, 100MB storage)
Basic - ₹199/month (1000 API calls, 50 posts/month, 500MB storage)
Pro - ₹499/month (10000 API calls, 500 posts/month, 2GB storage)
Premium - ₹999/month (50000 API calls, 2000 posts/month, 10GB storage)
Enterprise - ₹9999/year (Unlimited everything)

🔌 Related GraphQL Endpoints

        
# Authentication
sendMobileOtp(phone, meta)
verifyMobileOtp(phone, otp, meta)
registerUser(input)
logout(access_token, refresh_token, device_id, meta)
refreshToken(refresh_token, device_id, meta)

# User Management (to be implemented)
getUser(user_id, meta)
updateProfile(input, meta)
changePassword(input, meta)
        
    