Database Schema Reference
Complete reference of all database tables with descriptions and relationships
🔗 Quick Navigation
📊 Complete Table Reference
User Management (6 tables)
app_users
Core authentication with user_id as referral code
user_profiles
One-to-one profile data with language/timezone
user_addresses
Multiple addresses with location integration
user_devices
Device info & FCM tokens
user_sessions
Active/historical sessions
user_login_stats
Login statistics (1:1 with app_users)
RBAC System (5 tables)
roles
Role definitions with hierarchy levels
permissions
Permission definitions by module
role_permissions
Role-permission junction table
user_roles
User-role assignments (can expire)
user_permissions
Individual user permission overrides
Membership System (5 tables)
memberships
Membership plan definitions
membership_features
Plan features and limits
user_memberships
User subscriptions
user_membership_quotas
Current period usage tracking
user_usage_logs
Usage transaction history
Notification System (5 tables)
notif_items
Notification definitions with content
bdg_items
Badge configurations (indicators)
action_items
Action button definitions
notification_templates
Multi-channel templates
notif_reads
Read tracking per user
Location System (3 tables)
countries
Multi-language country names
states
Multi-language state names
cities
Multi-language city names
Metadata & API (4 tables)
api_callrequests
API call logging with metadata
doc_access_controls
Documentation access management
doc_access_logs
Documentation access logs
personal_access_tokens
API token management
Content & Instrumentation (3 tables)
instrumentations
Device instrumentation data
time_lines
Location timeline tracking
failed_jobs
Failed queue job tracking
🏗️ Key Design Principles
Normalization
- Each table has a single, clear responsibility
- No redundant data storage
- Proper foreign key relationships with cascade rules
Performance
- Indexed on all foreign keys and frequently queried columns
- Composite indexes for common query patterns
- TINYINT for status/gender (memory efficient)
Scalability
- Easy to extend with new features without modifying core tables
- Soft deletes for data recovery
- Append-only logging for audit trails
Data Integrity
- Foreign key constraints with proper cascade rules
- UNIQUE constraints on natural keys (email, phone)
- ENUM for limited value sets (status, type)