Database Tables Reference¶

Complete reference of Mailborder's MariaDB database schema.

Overview¶

Mailborder uses MariaDB to store:

User accounts and authentication
System configuration
Email policies and rules
Quarantined messages metadata
Audit logs and statistics
Internationalization strings

Database: mailborder Engine: InnoDB Charset: utf8mb4 Collation: utf8mb4_unicode_ci

Schema Organization¶

Tables are organized by function:

Prefix	Purpose	Example
`mb_users_*`	User management	`mb_users`, `mb_users_meta`
`mb_auth_*`	Authentication	`mb_auth_totp`, `mb_auth_passkeys`
`mb_policy_*`	Email policies	`mb_policy_rules`, `mb_policy_whitelist`
`mb_quarantine_*`	Quarantine	`mb_quarantine`, `mb_quarantine_attachments`
`mb_log_*`	Logging	`mb_log_audit`, `mb_log_email`
`mb_stats_*`	Statistics	`mb_stats_spam`, `mb_stats_virus`
`mb_settings_*`	Configuration	`mb_settings`, `mb_settings_defaults`
`mb_i18n_*`	Languages	`mb_i18n_strings`, `mb_i18n_languages`

User Management Tables¶

mb_users¶

Primary user accounts table.

Purpose: Store administrator user accounts.

CREATE TABLE `mb_users` (
  `user_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(255) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `display_name` VARCHAR(255) DEFAULT NULL,
  `privilege_level` TINYINT(2) UNSIGNED NOT NULL DEFAULT 5,
  `enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `locked` TINYINT(1) NOT NULL DEFAULT 0,
  `failed_login_attempts` INT(11) UNSIGNED NOT NULL DEFAULT 0,
  `last_login` DATETIME DEFAULT NULL,
  `last_login_ip` VARCHAR(45) DEFAULT NULL,
  `password_changed_at` DATETIME DEFAULT NULL,
  `must_change_password` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `email` (`email`),
  KEY `enabled` (`enabled`),
  KEY `locked` (`locked`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Columns:

user_id - Unique user identifier
email - Login email (unique)
password_hash - Argon2id password hash
display_name - Full name for display
privilege_level - Access level (1-10, 10=highest)
enabled - Account active status
locked - Account locked after failed logins
failed_login_attempts - Counter for lockout
last_login - Most recent successful login
last_login_ip - IP address of last login
password_changed_at - Password age tracking
must_change_password - Force password change on next login

mb_users_meta¶

WordPress-style metadata for users.

Purpose: Store flexible user preferences and settings.

CREATE TABLE `mb_users_meta` (
  `meta_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) UNSIGNED NOT NULL,
  `meta_key` VARCHAR(255) NOT NULL,
  `meta_value` LONGTEXT DEFAULT NULL,
  PRIMARY KEY (`meta_id`),
  KEY `user_id` (`user_id`),
  KEY `meta_key` (`meta_key`),
  CONSTRAINT `mb_users_meta_user_id` FOREIGN KEY (`user_id`)
    REFERENCES `mb_users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Common meta keys:

language - User's preferred language code
timezone - User's timezone
theme - UI theme preference
notifications_enabled - Email notifications on/off
two_factor_backup_codes - Encrypted 2FA backup codes
session_fingerprint - Browser fingerprint for security

Authentication Tables¶

mb_auth_sessions¶

Active user sessions.

CREATE TABLE `mb_auth_sessions` (
  `session_id` VARCHAR(64) NOT NULL,
  `user_id` INT(11) UNSIGNED NOT NULL,
  `ip_address` VARCHAR(45) NOT NULL,
  `user_agent` VARCHAR(500) DEFAULT NULL,
  `fingerprint` VARCHAR(64) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_activity` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `expires_at` DATETIME NOT NULL,
  PRIMARY KEY (`session_id`),
  KEY `user_id` (`user_id`),
  KEY `expires_at` (`expires_at`),
  CONSTRAINT `mb_auth_sessions_user_id` FOREIGN KEY (`user_id`)
    REFERENCES `mb_users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Session lifecycle: 1. Created on login 2. Validated on each request 3. Expired after inactivity or logout 4. Cleaned up by cron

mb_auth_totp¶

TOTP (Time-based One-Time Password) 2FA configuration.

CREATE TABLE `mb_auth_totp` (
  `totp_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) UNSIGNED NOT NULL,
  `secret` VARCHAR(32) NOT NULL,
  `enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `verified` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_used` DATETIME DEFAULT NULL,
  PRIMARY KEY (`totp_id`),
  UNIQUE KEY `user_id` (`user_id`),
  CONSTRAINT `mb_auth_totp_user_id` FOREIGN KEY (`user_id`)
    REFERENCES `mb_users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

mb_auth_passkeys¶

WebAuthn/FIDO2 passkey credentials.

CREATE TABLE `mb_auth_passkeys` (
  `credential_id` VARCHAR(255) NOT NULL,
  `user_id` INT(11) UNSIGNED NOT NULL,
  `public_key` TEXT NOT NULL,
  `counter` INT(11) UNSIGNED NOT NULL DEFAULT 0,
  `device_name` VARCHAR(255) DEFAULT NULL,
  `device_type` VARCHAR(50) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_used` DATETIME DEFAULT NULL,
  PRIMARY KEY (`credential_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `mb_auth_passkeys_user_id` FOREIGN KEY (`user_id`)
    REFERENCES `mb_users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Passkey data: - credential_id - Unique WebAuthn credential ID - public_key - CBOR-encoded public key - counter - Signature counter (anti-replay) - device_name - User-friendly name ("YubiKey 5", "iPhone 13") - device_type - platform or cross-platform

Email Policy Tables¶

mb_policy_rules¶

Custom email policy rules.

CREATE TABLE `mb_policy_rules` (
  `rule_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `priority` INT(11) NOT NULL DEFAULT 100,
  `conditions` TEXT NOT NULL,
  `actions` TEXT NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `match_count` INT(11) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`rule_id`),
  UNIQUE KEY `name` (`name`),
  KEY `enabled` (`enabled`),
  KEY `priority` (`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

JSON format for conditions/actions:

{
  "conditions": [
    {"type": "from", "value": "@external.com"},
    {"type": "score", "operator": ">", "value": 5.0}
  ],
  "actions": [
    {"type": "quarantine"},
    {"type": "notify", "email": "admin@example.com"}
  ]
}

mb_policy_whitelist¶

Sender whitelist entries.

CREATE TABLE `mb_policy_whitelist` (
  `whitelist_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `entry_type` ENUM('email','domain','ip') NOT NULL,
  `entry_value` VARCHAR(255) NOT NULL,
  `reason` TEXT DEFAULT NULL,
  `created_by` INT(11) UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` DATETIME DEFAULT NULL,
  `match_count` INT(11) UNSIGNED NOT NULL DEFAULT 0,
  `last_match` DATETIME DEFAULT NULL,
  PRIMARY KEY (`whitelist_id`),
  UNIQUE KEY `entry` (`entry_type`, `entry_value`),
  KEY `expires_at` (`expires_at`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `mb_policy_whitelist_created_by` FOREIGN KEY (`created_by`)
    REFERENCES `mb_users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

mb_policy_blacklist¶

Sender blacklist entries.

CREATE TABLE `mb_policy_blacklist` (
  `blacklist_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `entry_type` ENUM('email','domain','ip') NOT NULL,
  `entry_value` VARCHAR(255) NOT NULL,
  `action` ENUM('reject','discard','quarantine') NOT NULL DEFAULT 'reject',
  `reason` TEXT DEFAULT NULL,
  `created_by` INT(11) UNSIGNED DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` DATETIME DEFAULT NULL,
  `match_count` INT(11) UNSIGNED NOT NULL DEFAULT 0,
  `last_match` DATETIME DEFAULT NULL,
  PRIMARY KEY (`blacklist_id`),
  UNIQUE KEY `entry` (`entry_type`, `entry_value`),
  KEY `expires_at` (`expires_at`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `mb_policy_blacklist_created_by` FOREIGN KEY (`created_by`)
    REFERENCES `mb_users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Quarantine Tables¶

mb_quarantine¶

Quarantined email messages.

CREATE TABLE `mb_quarantine` (
  `quarantine_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `message_id` VARCHAR(255) NOT NULL,
  `envelope_from` VARCHAR(255) DEFAULT NULL,
  `envelope_to` VARCHAR(255) DEFAULT NULL,
  `header_from` VARCHAR(255) DEFAULT NULL,
  `header_to` VARCHAR(255) DEFAULT NULL,
  `subject` VARCHAR(500) DEFAULT NULL,
  `reason` ENUM('spam','virus','policy','geoip','auth','other') NOT NULL,
  `spam_score` DECIMAL(5,2) DEFAULT NULL,
  `virus_name` VARCHAR(255) DEFAULT NULL,
  `file_path` VARCHAR(500) NOT NULL,
  `file_size` INT(11) UNSIGNED NOT NULL,
  `quarantined_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `sender_ip` VARCHAR(45) DEFAULT NULL,
  `sender_country` VARCHAR(2) DEFAULT NULL,
  `spf_result` VARCHAR(20) DEFAULT NULL,
  `dkim_result` VARCHAR(20) DEFAULT NULL,
  `dmarc_result` VARCHAR(20) DEFAULT NULL,
  `status` ENUM('quarantined','released','deleted') NOT NULL DEFAULT 'quarantined',
  `released_at` DATETIME DEFAULT NULL,
  `released_by` INT(11) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`quarantine_id`),
  UNIQUE KEY `message_id` (`message_id`),
  KEY `envelope_to` (`envelope_to`),
  KEY `quarantined_at` (`quarantined_at`),
  KEY `reason` (`reason`),
  KEY `status` (`status`),
  CONSTRAINT `mb_quarantine_released_by` FOREIGN KEY (`released_by`)
    REFERENCES `mb_users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

mb_quarantine_attachments¶

Attachment metadata for quarantined emails.

CREATE TABLE `mb_quarantine_attachments` (
  `attachment_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `quarantine_id` BIGINT(20) UNSIGNED NOT NULL,
  `filename` VARCHAR(255) NOT NULL,
  `mime_type` VARCHAR(100) DEFAULT NULL,
  `file_size` INT(11) UNSIGNED NOT NULL,
  `file_hash` VARCHAR(64) DEFAULT NULL,
  PRIMARY KEY (`attachment_id`),
  KEY `quarantine_id` (`quarantine_id`),
  KEY `file_hash` (`file_hash`),
  CONSTRAINT `mb_quarantine_attachments_quarantine_id` FOREIGN KEY (`quarantine_id`)
    REFERENCES `mb_quarantine` (`quarantine_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Logging Tables¶

mb_log_audit¶

Audit trail for administrative actions.

CREATE TABLE `mb_log_audit` (
  `log_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) UNSIGNED DEFAULT NULL,
  `action` VARCHAR(100) NOT NULL,
  `resource_type` VARCHAR(50) DEFAULT NULL,
  `resource_id` VARCHAR(255) DEFAULT NULL,
  `details` TEXT DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` VARCHAR(500) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`log_id`),
  KEY `user_id` (`user_id`),
  KEY `action` (`action`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `mb_log_audit_user_id` FOREIGN KEY (`user_id`)
    REFERENCES `mb_users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Common actions: - LOGIN, LOGOUT, LOGIN_FAILED - USER_CREATE, USER_UPDATE, USER_DELETE - POLICY_CREATE, POLICY_UPDATE, POLICY_DELETE - QUARANTINE_RELEASE, QUARANTINE_DELETE - SETTINGS_UPDATE, BACKUP_CREATE

mb_log_email¶

Email processing log.

CREATE TABLE `mb_log_email` (
  `log_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `message_id` VARCHAR(255) NOT NULL,
  `envelope_from` VARCHAR(255) DEFAULT NULL,
  `envelope_to` VARCHAR(255) DEFAULT NULL,
  `subject` VARCHAR(500) DEFAULT NULL,
  `verdict` ENUM('pass','quarantine','reject','discard') NOT NULL,
  `spam_score` DECIMAL(5,2) DEFAULT NULL,
  `virus_detected` TINYINT(1) NOT NULL DEFAULT 0,
  `processing_time` INT(11) UNSIGNED DEFAULT NULL,
  `sender_ip` VARCHAR(45) DEFAULT NULL,
  `received_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`log_id`),
  KEY `message_id` (`message_id`),
  KEY `envelope_to` (`envelope_to`),
  KEY `verdict` (`verdict`),
  KEY `received_at` (`received_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Statistics Tables¶

mb_stats_daily¶

Daily aggregated statistics.

CREATE TABLE `mb_stats_daily` (
  `stat_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `stat_date` DATE NOT NULL,
  `emails_received` INT(11) UNSIGNED NOT NULL DEFAULT 0,
  `emails_passed` INT(11) UNSIGNED NOT NULL DEFAULT 0,
  `emails_quarantined` INT(11) UNSIGNED NOT NULL DEFAULT 0,
  `emails_rejected` INT(11) UNSIGNED NOT NULL DEFAULT 0,
  `spam_detected` INT(11) UNSIGNED NOT NULL DEFAULT 0,
  `virus_detected` INT(11) UNSIGNED NOT NULL DEFAULT 0,
  `avg_spam_score` DECIMAL(5,2) DEFAULT NULL,
  `avg_processing_time` INT(11) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`stat_id`),
  UNIQUE KEY `stat_date` (`stat_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Settings Tables¶

mb_settings¶

System configuration key-value store.

CREATE TABLE `mb_settings` (
  `setting_key` VARCHAR(255) NOT NULL,
  `setting_value` TEXT DEFAULT NULL,
  `setting_type` ENUM('string','integer','boolean','json') NOT NULL DEFAULT 'string',
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` INT(11) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`setting_key`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `mb_settings_updated_by` FOREIGN KEY (`updated_by`)
    REFERENCES `mb_users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Common settings: - system.name - System name - system.hostname - FQDN - spam.threshold.pass - Spam pass threshold - antivirus.enabled - AV scanning enabled - geoip.enabled - GeoIP filtering enabled

Internationalization Tables¶

mb_i18n_languages¶

Supported languages.

CREATE TABLE `mb_i18n_languages` (
  `language_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(10) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `native_name` VARCHAR(100) NOT NULL,
  `enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `progress` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`language_id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

mb_i18n_strings¶

Translatable strings.

CREATE TABLE `mb_i18n_strings` (
  `string_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `string_key` VARCHAR(255) NOT NULL,
  `language_code` VARCHAR(10) NOT NULL,
  `string_value` TEXT NOT NULL,
  `context` VARCHAR(255) DEFAULT NULL,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`string_id`),
  UNIQUE KEY `translation` (`string_key`, `language_code`),
  KEY `language_code` (`language_code`),
  CONSTRAINT `mb_i18n_strings_language` FOREIGN KEY (`language_code`)
    REFERENCES `mb_i18n_languages` (`code`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Database Maintenance¶

Optimize Tables¶

OPTIMIZE TABLE mb_log_audit;
OPTIMIZE TABLE mb_log_email;
OPTIMIZE TABLE mb_quarantine;

Analyze Tables¶

ANALYZE TABLE mb_users;
ANALYZE TABLE mb_policy_rules;
ANALYZE TABLE mb_quarantine;

Check Table Sizes¶

SELECT
  table_name,
  ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb,
  table_rows
FROM information_schema.TABLES
WHERE table_schema = 'mailborder'
ORDER BY (data_length + index_length) DESC;

Cleanup Old Data¶

-- Delete old audit logs (>90 days)
DELETE FROM mb_log_audit WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- Delete old email logs (>30 days)
DELETE FROM mb_log_email WHERE received_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- Delete old quarantine (>30 days)
DELETE FROM mb_quarantine
WHERE status = 'deleted'
  AND quarantined_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

Backup and Restore¶

Backup Database¶

sudo mysqldump mailborder > /tmp/mailborder-$(date +%Y%m%d).sql

# With compression
sudo mysqldump mailborder | gzip > /tmp/mailborder-$(date +%Y%m%d).sql.gz

Restore Database¶

sudo mysql mailborder < /tmp/mailborder-20250113.sql

# From compressed
gunzip < /tmp/mailborder-20250113.sql.gz | sudo mysql mailborder

Backup Specific Tables¶

# Only user data
sudo mysqldump mailborder mb_users mb_users_meta mb_auth_totp mb_auth_passkeys \
  > /tmp/mailborder-users-$(date +%Y%m%d).sql

# Only configuration
sudo mysqldump mailborder mb_settings mb_policy_rules \
  > /tmp/mailborder-config-$(date +%Y%m%d).sql

Indexes and Performance¶

Key Indexes¶

All tables have optimized indexes for: - Primary keys (always indexed) - Foreign keys (indexed for join performance) - Frequently queried columns - Date/time columns for range queries

Query Performance¶

Check slow queries:

SHOW FULL PROCESSLIST;

Explain query:

EXPLAIN SELECT * FROM mb_quarantine WHERE envelope_to = 'user@example.com';

Add missing index:

CREATE INDEX idx_envelope_to ON mb_quarantine(envelope_to);