Documentation Database

Database

Detailed documentation and implementation guides.

🗄️ Database Architecture & Design

This document provides a comprehensive overview of Mercury CRM's database architecture, including schema design patterns, relationship structures, performance optimizations, and development best practices.

📊 Database Overview

Mercury CRM uses a sophisticated database architecture designed for:

  • 🏗️ Scalability - Horizontal and vertical scaling capabilities
  • 🔒 Security - Data encryption and access control
  • ⚡ Performance - Optimized queries and intelligent indexing
  • 🔄 Flexibility - Modular schema design

Technology Stack

  • Primary Database: MySQL 8.0+
  • Cache Layer: Redis 6.0+
  • Search Engine: Elasticsearch (optional)
  • Migrations: Laravel Migration System

🏛️ Core Schema Design

User Management System

-- Users table (Core authentication)
users
├── id (Primary Key)
├── name (VARCHAR 255)
├── email (UNIQUE VARCHAR 255)
├── email_verified_at (TIMESTAMP)
├── password (VARCHAR 255)
├── remember_token (VARCHAR 100)
├── created_at, updated_at (TIMESTAMPS)

-- User roles and permissions
roles
├── id (Primary Key)
├── name (VARCHAR 255)
├── guard_name (VARCHAR 255)
├── created_at, updated_at

permissions
├── id (Primary Key)
├── name (VARCHAR 255)
├── guard_name (VARCHAR 255)
├── created_at, updated_at

-- Pivot tables
model_has_permissions
├── permission_id (Foreign Key)
├── model_type (VARCHAR 255)
├── model_id (BIGINT)

model_has_roles
├── role_id (Foreign Key)
├── model_type (VARCHAR 255)
├── model_id (BIGINT)

Waha WhatsApp Integration

-- Session management
waha_sessions
├── id (Primary Key)
├── name (UNIQUE VARCHAR 255) -- Session identifier
├── user_id (Foreign Key → users.id)
├── status (ENUM: connecting, working, failed)
├── config (JSON) -- Session configuration
├── created_at, updated_at

-- Chat conversations
waha_chats
├── id (Primary Key)
├── session_id (Foreign Key → waha_sessions.id)
├── chat_id (VARCHAR 255) -- WhatsApp chat ID
├── name (VARCHAR 255)
├── last_message_at (TIMESTAMP)
├── created_at, updated_at

-- Message storage
waha_messages
├── id (Primary Key)
├── session_id (Foreign Key → waha_sessions.id)
├── chat_id (Foreign Key → waha_chats.id)
├── message_id (VARCHAR 255) -- WhatsApp message ID
├── type (ENUM: text, image, document, audio, video)
├── content (TEXT)
├── media_url (VARCHAR 500)
├── status (ENUM: sent, delivered, read, failed)
├── created_at, updated_at

-- Call logging
waha_calls
├── id (Primary Key)
├── session_id (Foreign Key → waha_sessions.id)
├── call_id (VARCHAR 255) -- WhatsApp call ID
├── from (VARCHAR 255) -- Caller number
├── to (VARCHAR 255) -- Recipient number
├── type (ENUM: received, accepted, rejected)
├── created_at, updated_at

-- User-session relationships
user_waha
├── id (Primary Key)
├── user_id (Foreign Key → users.id)
├── session_id (Foreign Key → waha_sessions.id)
├── created_at, updated_at

-- Read status tracking
chat_reads
├── id (Primary Key)
├── chat_id (Foreign Key → waha_chats.id)
├── user_id (Foreign Key → users.id)
├── last_read_message_id (VARCHAR 255)
├── created_at, updated_at

File Management System

-- Attachment storage
attachments
├── id (Primary Key)
├── name (VARCHAR 255) -- Generated filename
├── original_name (VARCHAR 255) -- Original filename
├── display_name (VARCHAR 255) -- User-friendly name
├── extension (VARCHAR 10)
├── type_id (Foreign Key → attachment_types.id)
├── type_name (VARCHAR 100)
├── model_type (VARCHAR 255) -- Polymorphic relationship
├── model_id (BIGINT) -- Polymorphic relationship
├── path (VARCHAR 500) -- Storage path
├── created_at, updated_at

-- Attachment types
attachment_types
├── id (Primary Key)
├── name (VARCHAR 255)
├── description (TEXT)
├── created_at, updated_at

Audit & Logging System

-- System audit logs
audit_logs
├── id (Primary Key)
├── user_type (VARCHAR 255)
├── user_id (BIGINT)
├── event (VARCHAR 255)
├── auditable_type (VARCHAR 255)
├── auditable_id (BIGINT)
├── old_values (JSON)
├── new_values (JSON)
├── url (TEXT)
├── ip_address (VARCHAR 45)
├── user_agent (TEXT)
├── tags (VARCHAR 255)
├── created_at, updated_at

-- Error logging
error_logs
├── id (Primary Key)
├── level (VARCHAR 50)
├── message (TEXT)
├── context (JSON)
├── extra (JSON)
├── created_at, updated_at

🔗 Relationship Patterns

Polymorphic Relationships

Many models use polymorphic relationships for flexibility:

// Attachments can belong to any model
class Attachment extends Model
{
    public function attachable(): MorphTo
    {
        return $this->morphTo('model');
    }
}

// Usage examples
$user->attachments() // User documents
$instructor->attachments() // Instructor certificates
$company->attachments() // Company logos

Many-to-Many Relationships

// Users can access multiple Waha sessions
class User extends Model
{
    public function wahaSessions(): BelongsToMany
    {
        return $this->belongsToMany(WahaSession::class, 'user_waha');
    }
}

// Sessions can be shared with multiple users
class WahaSession extends Model
{
    public function users(): BelongsToMany
    {
        return $this->belongsToMany(User::class, 'user_waha');
    }
}

📈 Performance Optimizations

Strategic Indexing

-- Performance indexes for frequent queries
CREATE INDEX idx_waha_messages_session_chat ON waha_messages(session_id, chat_id);
CREATE INDEX idx_waha_messages_status ON waha_messages(status);
CREATE INDEX idx_waha_calls_session_type ON waha_calls(session_id, type);
CREATE INDEX idx_audit_logs_user ON audit_logs(user_type, user_id);
CREATE INDEX idx_attachments_model ON attachments(model_type, model_id);

-- Composite indexes for complex queries
CREATE INDEX idx_waha_messages_session_created ON waha_messages(session_id, created_at DESC);
CREATE INDEX idx_waha_chats_session_updated ON waha_chats(session_id, updated_at DESC);

Query Optimization Patterns

// Eager loading to prevent N+1 queries
$messages = WahaMessage::with(['session', 'chat'])
    ->where('session_id', $sessionId)
    ->latest()
    ->paginate(50);

// Selective column loading
$sessions = WahaSession::select(['id', 'name', 'status'])
    ->where('status', 'working')
    ->get();

// Chunking for large datasets
WahaMessage::where('created_at', '<', now()->subDays(90))
    ->chunk(1000, function ($messages) {
        foreach ($messages as $message) {
            // Process old messages
        }
    });

🗂️ Data Partitioning Strategy

Table Partitioning

For high-volume tables, implement partitioning:

-- Partition waha_messages by month
CREATE TABLE waha_messages_2024_01 PARTITION OF waha_messages
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE waha_messages_2024_02 PARTITION OF waha_messages
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Automated partition management
DELIMITER //
CREATE EVENT create_monthly_partitions
ON SCHEDULE EVERY 1 MONTH
DO
BEGIN
    SET @sql = CONCAT('CREATE TABLE waha_messages_', 
                     DATE_FORMAT(NOW() + INTERVAL 1 MONTH, '%Y_%m'),
                     ' PARTITION OF waha_messages FOR VALUES FROM (\'',
                     DATE_FORMAT(NOW() + INTERVAL 1 MONTH, '%Y-%m-01'),
                     '\') TO (\'',
                     DATE_FORMAT(NOW() + INTERVAL 2 MONTH, '%Y-%m-01'),
                     '\')');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END//
DELIMITER ;

Archival Strategy

-- Archive old data to separate tables
CREATE TABLE waha_messages_archive AS 
SELECT * FROM waha_messages 
WHERE created_at < NOW() - INTERVAL 6 MONTHS;

-- Create indexes on archive table
CREATE INDEX idx_archive_messages_session ON waha_messages_archive(session_id);
CREATE INDEX idx_archive_messages_created ON waha_messages_archive(created_at);

🔒 Security & Compliance

Data Encryption

-- Encrypt sensitive fields at application level
ALTER TABLE users 
ADD COLUMN encrypted_phone VARCHAR(255) AFTER phone;

-- Use database-level encryption for highly sensitive data
CREATE TABLE sensitive_data (
    id BIGINT PRIMARY KEY,
    encrypted_content BLOB,
    encryption_key_id VARCHAR(255),
    created_at TIMESTAMP
) ENCRYPTION='Y';

Access Control

// Row-level security through Eloquent scopes
class WahaMessage extends Model
{
    protected static function booted()
    {
        static::addGlobalScope('user_access', function (Builder $builder) {
            if (auth()->check()) {
                $builder->whereHas('session.users', function ($query) {
                    $query->where('user_id', auth()->id());
                });
            }
        });
    }
}

🧪 Migration Best Practices

Safe Migration Patterns

// Safe column addition
public function up()
{
    Schema::table('waha_messages', function (Blueprint $table) {
        $table->string('new_column')->nullable()->after('existing_column');
    });
}

// Rollback-safe migrations
public function down()
{
    Schema::table('waha_messages', function (Blueprint $table) {
        $table->dropColumn('new_column');
    });
}

// Large table migrations with batching
public function up()
{
    DB::transaction(function () {
        // Disable foreign key checks temporarily
        DB::statement('SET FOREIGN_KEY_CHECKS=0;');

        // Perform migration in chunks
        WahaMessage::chunk(1000, function ($messages) {
            foreach ($messages as $message) {
                // Update records in batches
            }
        });

        // Re-enable foreign key checks
        DB::statement('SET FOREIGN_KEY_CHECKS=1;');
    });
}

Data Seeding Strategy

// Factory-based seeding
class DatabaseSeeder extends Seeder
{
    public function run()
    {
        // Create test users
        User::factory(10)->create();

        // Create Waha sessions with relationships
        WahaSession::factory(3)
            ->has(WahaChat::factory(5)
                ->has(WahaMessage::factory(20)))
            ->create();

        // Seed permissions and roles
        $this->call([
            RoleSeeder::class,
            PermissionSeeder::class,
            UserRoleSeeder::class,
        ]);
    }
}

📊 Database Monitoring

Performance Metrics

-- Monitor slow queries
SELECT 
    query_time,
    lock_time,
    rows_sent,
    rows_examined,
    sql_text
FROM mysql.slow_log
WHERE start_time > NOW() - INTERVAL 1 HOUR
ORDER BY query_time DESC;

-- Check index usage
SELECT 
    table_name,
    index_name,
    cardinality,
    non_unique
FROM information_schema.statistics
WHERE table_schema = 'mercury_crm'
ORDER BY table_name, cardinality DESC;

Health Checks

// Database health monitoring
class DatabaseHealthCheck
{
    public function check(): array
    {
        return [
            'connection' => $this->checkConnection(),
            'query_performance' => $this->checkQueryPerformance(),
            'storage_usage' => $this->checkStorageUsage(),
            'replication_lag' => $this->checkReplicationLag(),
        ];
    }

    private function checkConnection(): bool
    {
        try {
            DB::connection()->getPdo();
            return true;
        } catch (\Exception $e) {
            return false;
        }
    }
}

🚀 Scaling Strategies

Read Replicas

// Configure read/write splitting
'mysql' => [
    'read' => [
        'host' => [
            'slave1.mysql.example.com',
            'slave2.mysql.example.com',
        ],
    ],
    'write' => [
        'host' => [
            'master.mysql.example.com',
        ],
    ],
    // ... other configuration
],

// Force specific connection types
$users = DB::connection('mysql::read')->table('users')->get();
$result = DB::connection('mysql::write')->table('users')->insert($data);

Sharding Strategy

// Horizontal sharding by tenant/company
class ShardManager
{
    public function getShardForCompany(int $companyId): string
    {
        $shardNumber = $companyId % config('database.shard_count');
        return "shard_{$shardNumber}";
    }

    public function query(int $companyId, callable $callback)
    {
        $shard = $this->getShardForCompany($companyId);
        return DB::connection($shard)->transaction($callback);
    }
}

📚 Development Guidelines

Model Best Practices

class WahaMessage extends Model
{
    // Always define fillable or guarded
    protected $fillable = [
        'session_id',
        'chat_id', 
        'message_id',
        'type',
        'content',
        'media_url',
        'status'
    ];

    // Define attribute casting
    protected $casts = [
        'created_at' => 'datetime',
        'updated_at' => 'datetime',
    ];

    // Use enums for status fields
    protected $enums = [
        'type' => MessageType::class,
        'status' => MessageStatus::class,
    ];

    // Define relationships with return types
    public function session(): BelongsTo
    {
        return $this->belongsTo(WahaSession::class);
    }

    // Use query scopes for common filters
    public function scopeRecent(Builder $query): Builder
    {
        return $query->where('created_at', '>=', now()->subDays(7));
    }
}

Query Optimization Tips

// ✅ Good: Use specific columns
$messages = WahaMessage::select(['id', 'content', 'created_at'])
    ->where('session_id', $sessionId)
    ->get();

// ❌ Bad: Select all columns
$messages = WahaMessage::where('session_id', $sessionId)->get();

// ✅ Good: Eager load relationships
$sessions = WahaSession::with(['messages' => function ($query) {
    $query->select(['id', 'session_id', 'content'])->latest();
}])->get();

// ❌ Bad: N+1 query problem
$sessions = WahaSession::all();
foreach ($sessions as $session) {
    $session->messages; // This creates N additional queries
}

🎯 Quick Reference

Common Queries

-- Find active sessions with message count
SELECT 
    s.id,
    s.name,
    s.status,
    COUNT(m.id) as message_count
FROM waha_sessions s
LEFT JOIN waha_messages m ON s.id = m.session_id
WHERE s.status = 'working'
GROUP BY s.id, s.name, s.status;

-- Get recent messages with sender info
SELECT 
    m.content,
    m.created_at,
    c.name as chat_name,
    s.name as session_name
FROM waha_messages m
JOIN waha_chats c ON m.chat_id = c.id
JOIN waha_sessions s ON m.session_id = s.id
WHERE m.created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
ORDER BY m.created_at DESC;

Maintenance Commands

# Run migrations
php artisan migrate

# Rollback migrations
php artisan migrate:rollback

# Seed database
php artisan db:seed

# Generate factory
php artisan make:factory WahaMessageFactory

# Create migration
php artisan make:migration create_waha_calls_table

This database architecture provides a solid foundation for Mercury CRM's scalable, secure, and high-performance data layer.

For more information on specific modules, see our Architecture Documentation and HMVC Guide.