Sign In

Database Structure #

Complete reference for Server Monitor plugin database schema, models, and relationships.

Database Tables #

Core Tables #

servers #

Primary table for monitored endpoints.

CREATE TABLE servers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    number VARCHAR(255),                    -- Unique identifier (timestamp + id)
    title VARCHAR(255),                     -- Display name
    endpoint TEXT,                          -- URL to monitor
    status VARCHAR(255),                    -- Current HTTP status
    admin_id INT,                          -- User who created (deprecated)
    organization_id INT,                   -- Organization owner
    percent_uptime DECIMAL(5,2),           -- Calculated uptime percentage
    require_calculation BOOLEAN DEFAULT 1,  -- Flag for uptime recalculation
    last_checked_at TIMESTAMP,             -- Last status check time
    last_job_queued_at TIMESTAMP,          -- Last job dispatch time
    last_job_executed_at TIMESTAMP,        -- Last job execution time
    last_job_response_time DECIMAL(8,2),   -- Last job duration in seconds
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP                   -- Soft delete support
);

Indexes:

  • organization_id - Query filtering
  • status - Status filtering
  • deleted_at - Soft delete queries

server_logs #

Status change history for threshold-based notifications.

CREATE TABLE server_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    server_id INT,                         -- Foreign key to servers
    note VARCHAR(255),                     -- Human-readable change description
    original_status VARCHAR(255),          -- Previous status
    updated_status VARCHAR(255),           -- New status
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP
);

Indexes:

  • server_id - Server relationship
  • created_at - Chronological queries

Performance Monitoring #

server_check_logs #

Individual job execution performance tracking.

CREATE TABLE server_check_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    server_id INT,                         -- Foreign key to servers
    plan_type VARCHAR(20),                 -- 'premium' or 'free'
    job_queued_at TIMESTAMP,               -- When job was dispatched
    job_executed_at TIMESTAMP,             -- When job started execution
    response_time_seconds DECIMAL(8,2),    -- Total execution time
    is_delayed BOOLEAN DEFAULT 0,          -- SLA breach flag
    is_new_server BOOLEAN DEFAULT 0,       -- New server grace period
    grace_period_applied BOOLEAN DEFAULT 0, -- Grace period was used
    notes TEXT,                            -- Additional information
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

Indexes:

  • plan_type, created_at - Performance queries
  • is_delayed, created_at - SLA breach analysis
  • server_id, created_at - Server-specific performance

dispatch_logs #

System-wide job dispatch performance.

CREATE TABLE dispatch_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    dispatch_type VARCHAR(20),             -- 'premium', 'free', 'all'
    server_count INT,                      -- Number of servers processed
    new_servers_count INT DEFAULT 0,       -- New servers in this dispatch
    execution_time DECIMAL(8,2),           -- Dispatch duration
    is_critical BOOLEAN DEFAULT 0,         -- Critical performance flag
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

Indexes:

  • dispatch_type, created_at - Type-specific analysis
  • is_critical, created_at - Critical event tracking

Communication System #

alert_logs #

Notification delivery tracking and cost management.

CREATE TABLE alert_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    server_id INT,                         -- Foreign key to servers
    organization_id INT,                   -- Foreign key to organizations
    log_id INT,                            -- Foreign key to server_logs
    type VARCHAR(50),                      -- 'email' or 'sms'
    recipient VARCHAR(255),                -- Email address or phone number
    content TEXT,                          -- Message content sent
    status VARCHAR(50),                    -- Delivery status
    rate VARCHAR(20),                      -- Cost in USD (4 decimal places)
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP
);

Indexes:

  • organization_id - Organization-specific logs
  • type, created_at - Communication type analysis
  • server_id - Server-specific communications

servermonitor_notification_settings #

Per-organization notification preferences.

CREATE TABLE servermonitor_notification_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT UNIQUE,            -- One setting per organization
    administrators TEXT,                   -- JSON array of user preferences
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

JSON Structure for administrators:

[
    {
        "user_id": 123,
        "email_enabled": true,
        "sms_enabled": false
    },
    {
        "user_id": 456,
        "email_enabled": true,
        "sms_enabled": true
    }
]

Model Relationships #

Server Model #

Relationships:

// One server belongs to one organization
$server->organization; // BelongsTo

// One server has many status change logs
$server->logs; // HasMany (with soft delete)

// One server has many performance logs
$server->server_check_logs; // HasMany

// One server has many notification logs
$server->alert_logs; // HasMany (through organization)

Key Methods:

// Calculate and update uptime percentage
$server->updatePercentUptime();

// Get current uptime (cached)
$server->getUptimePercent($server->id);

// Check if organization can view logs
Server::canViewLogs($organization);

// Get server limits for plan
Server::getServerLimitForPlan('basic'); // Returns 200

// Check remaining server capacity
Server::getRemainingServerCount($organizationId);

Log Model #

Relationships:

// One log belongs to one server
$log->server; // BelongsTo

// One log can trigger many alerts
$log->alert_logs; // HasMany

Key Methods:

// Automatic notification sending (afterCreate event)
// Threshold crossing detection
$log->crossedStatusThreshold(); // Returns boolean

// Plan-based log creation control (beforeCreate event)
// Scopes for organization filtering
Log::forOrganization($orgId)->get();
Log::olderThan(30)->get(); // Logs older than 30 days

AlertLog Model #

Relationships:

// Alert belongs to server, organization, and triggering log
$alertLog->server;       // BelongsTo
$alertLog->organization; // BelongsTo
$alertLog->log;          // BelongsTo (triggering status change)

Key Methods:

// Get communication cost
AlertLog::getRate('sms');   // Returns '0.0075'
AlertLog::getRate('email'); // Returns '0.0000'

NotificationSetting Model #

Relationships:

// Settings belong to one organization
$settings->organization; // BelongsTo

Key Methods:

// Get or create settings for organization
NotificationSetting::getForOrganization($orgId);

// Get settings for current user's organization
NotificationSetting::getCurrentOrganizationSettings();

// Get user dropdown options
$settings->getUserOptions(); // Returns formatted user list

Data Flow #

Server Monitoring Flow #

  1. Cron Job Triggers → API endpoint called
  2. JobDispatcher → Determines servers to check based on plan
  3. CheckServerJob → Queued for execution
  4. Status Check → HTTP request to server endpoint
  5. Server Updated → Status and timestamps saved
  6. Log Created → Status change recorded (if changed)
  7. Notification → Sent if threshold crossed
  8. AlertLog → Communication recorded

Uptime Calculation Flow #

  1. Status Change → require_calculation flag set
  2. Hourly Cron → Triggers recalculation job
  3. RecalculateUptimeJob → Processes flagged servers
  4. Time Analysis → Calculates downtime periods
  5. Percentage → (total_time - downtime) / total_time * 100
  6. Database Update → Saves percentage and clears flag

Notification Flow #

  1. Status Change → Server status updated
  2. Log Creation → Status change recorded
  3. Threshold Check → Determines if notification needed
  4. Settings Lookup → Gets organization notification preferences
  5. Recipient Processing → Iterates through configured admins
  6. Email Dispatch → Sends HTML email if enabled
  7. SMS Dispatch → Sends Twilio SMS if enabled (paid plans)
  8. Communication Logging → Records all attempts with costs

Performance Considerations #

Query Optimization #

Efficient Queries:

// Use indexes for filtering
Server::where('organization_id', $orgId)
      ->where('status', 200)
      ->get();

// Use relationships to avoid N+1
Server::with(['organization', 'logs'])->get();

// Use scopes for complex filtering
Log::forOrganization($orgId)
   ->olderThan(30)
   ->chunk(1000, function($logs) {
       // Process in batches
   });

Index Usage #

Key Indexes:

  • servers(organization_id, status) - Dashboard queries
  • server_logs(server_id, created_at) - Status history
  • server_check_logs(plan_type, created_at) - Performance analysis
  • alert_logs(organization_id, type) - Communication reporting

Chunked Processing #

Large dataset operations use chunking:

// Log purging
Log::forOrganization($orgId)
   ->olderThan($retentionDays)
   ->chunk(1000, function($logs) {
       foreach ($logs as $log) {
           $log->delete();
       }
   });

// Uptime recalculation
Server::where('require_calculation', true)
      ->chunk(100, function($servers) {
          foreach ($servers as $server) {
              $server->updatePercentUptime();
          }
      });

Maintenance Operations #

Log Cleanup #

Automated Cleanup (via cron):

# Purge logs based on plan retention
curl "https://yoursite.com/api/servermonitor/purge-logs/YOUR_API_KEY"

Manual Cleanup:

// Remove logs older than specified days
$organization = Organization::find(123);
$plan = $organization->selected_plan;
$retentionDays = Config::get("plans.{$plan}.log_retention_days");

Log::forOrganization(123)
   ->olderThan($retentionDays)
   ->chunk(1000, function($logs) {
       foreach ($logs as $log) {
           $log->delete();
       }
   });

Performance Monitoring #

Check Table Sizes:

SELECT
    table_name,
    table_rows,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.tables
WHERE table_schema = DATABASE()
    AND table_name LIKE '%server%'
ORDER BY (data_length + index_length) DESC;

Analyze Query Performance:

-- Check slow queries related to server monitoring
SELECT * FROM mysql.slow_log
WHERE sql_text LIKE '%servers%'
ORDER BY start_time DESC
LIMIT 10;

Backup and Recovery #

Critical Data #

Must Backup:

  • servers - Server definitions
  • server_logs - Status change history
  • alert_logs - Communication history
  • servermonitor_notification_settings - Alert preferences

Can Regenerate:

  • server_check_logs - Performance data
  • dispatch_logs - System performance data

Backup Script #

#!/bin/bash
# Backup critical ServerMonitor data

DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backups/servermonitor"
DB_NAME="your_database"

mkdir -p $BACKUP_DIR

# Backup critical tables
mysqldump $DB_NAME \
    --tables servers server_logs alert_logs servermonitor_notification_settings \
    --single-transaction \
    --routines \
    --triggers \
    > "$BACKUP_DIR/servermonitor_$DATE.sql"

# Compress backup
gzip "$BACKUP_DIR/servermonitor_$DATE.sql"

# Keep only last 30 days
find $BACKUP_DIR -name "*.sql.gz" -mtime +30 -delete

Recovery Procedures #

Restore from Backup:

# Restore critical data (CAUTION: This overwrites existing data)
gunzip -c servermonitor_20250101_120000.sql.gz | mysql your_database

# Regenerate calculated fields
curl "https://yoursite.com/api/servermonitor/calculate/all/YOUR_API_KEY"

Security Considerations #

Data Protection #

Sensitive Data:

  • Server endpoints (may reveal infrastructure)
  • Phone numbers (PII)
  • Email addresses (PII)
  • Communication content (may contain sensitive info)

Access Control:

  • Organization-based data isolation
  • Role-based access (organization admins)
  • API key protection

Data Retention #

Configurable Retention:

  • Free plan: 7 days log retention
  • Basic plan: 30 days retention
  • Pro plan: 90 days retention
  • Enterprise plan: 90 days retention

GDPR Considerations:

  • User data deleted when organization cancelled
  • Log retention configurable per plan
  • Communication logs include PII (email/phone)

Previous: ← Support | Next: Branding →