Database Schema¶

Mailyte uses MySQL 8.0 with InnoDB and utf8mb4 / utf8mb4_unicode_ci. The schema is owned entirely by Alembic (alembic/versions/, currently 0001_baseline → 0018_mail_logs_sasl_username) and applied by the migrate compose service on every docker compose up — the frozen SQL under database/migrations/sql/ is only the historical source the baseline was generated from (and the Community Edition's first-boot path).

Primary keys are CHAR(26) ULIDs for most application tables; a minority use INT/BIGINT AUTO_INCREMENT (noted below). No migration has ever dropped or renamed a table — all 18 revisions are additive. The current head holds 83 tables plus one view (audit_log, a compatibility view over audit_logs).

Entity Relationship Diagram¶

Real foreign-key constraints only (most cross-table references are by convention + index, without FK constraints):

erDiagram
    organizations ||--o{ domains : "fk_domains_org"
    organizations ||--o{ email_accounts : "fk_accounts_org"
    organizations ||--o{ smtp_credentials : "fk"
    organizations ||--o{ idempotency_keys : "fk_idem_org"
    organizations ||--o{ users : "fk_users_org"
    organizations ||--o{ alert_rules : "fk_alert_rules_org"
    domains ||--o{ email_accounts : "fk_accounts_domain"
    domains ||--o{ aliases : "fk_aliases_domain"
    domains ||--o{ dkim_keys : "fk_dkim_domain"
    domains ||--o{ smtp_credentials : "fk"
    domains ||--|| domain_policy : "fk_domain_policy_domain"
    email_accounts ||--o{ mailbox_sessions : "fk"
    email_accounts ||--|| mailbox_preferences : "fk"
    users ||--o{ web_sessions : "fk_web_sessions_user"
    platform_operators ||--o{ operator_sessions : "fk"
    alert_rules ||--o{ alert_events : "fk_alert_events_rule"

Core Tenancy Tables¶

organizations¶

Top-level tenant container. PK id CHAR(26).

domains¶

email_accounts¶

Individual mailboxes — the table Dovecot authenticates against (status = 'active', bcrypt in password).

aliases¶

SMTP Credentials (migrations 0008 / 0017 / 0018 — live since 2026-08-27)¶

smtp_credentials¶

Domain-scoped SMTP API keys, enforced by Dovecot's SMTP-only passdb.

smtp_credential_events¶

Append-only audit trail — deliberately FK-free so rows survive credential and organization deletion.

Mail Flow Tables¶

mail_logs (written by the log ingestor since 2026-08-22)¶

email_bodies (migration 0012)¶

Short-lived capture of outbound bodies, written by the tracking injector, joined into delivery webhooks, pruned by expires_at.

id VARCHAR(26) PK · message_id (indexed, non-unique) · organization_id · sender · subject · html MEDIUMTEXT · text MEDIUMTEXT · has_attachments · captured_at · expires_at

mail_queue¶

Application-level outbound queue (the queue_manager worker; distinct from Postfix's own on-disk queue).

id CHAR(26) PK · sender · recipient · organization_id · subject · body · headers JSON · priority INT default 5 · status (same enum as mail_logs, default queued) · attempts / max_attempts (default 3) · scheduled_at · processed_at · error_message · worker_id · processing_time

bounce_events / suppression_list / email_suppressions¶

• bounce_events — one row per bounce: message_id, sender, recipient, bounce_type ENUM(hard,soft,complaint,unsubscribe), bounce_code, bounce_reason, domain, suppressed.
• suppression_list (migration 0002, owned by delivery_optimizer) — UNIQUE(organization_id,email), reason ENUM(hard_bounce,complaint,unsubscribe,manual), bounce_count.
• email_suppressions — the tracking-side suppression store: UNIQUE(email,organization_id,suppression_type), suppression_type ENUM(BOUNCE,COMPLAINT,UNSUBSCRIBE,MANUAL), bounce_type ENUM(HARD,SOFT,BLOCK), expires_at, active.

Auth & Security Tables¶

api_keys¶

Operator & session tables (migrations 0006 / 0015 / 0016)¶

• platform_operators — staff identities: email UNIQUE, password_hash, role (support/operator/admin/owner, enforced in app code), mfa_required, is_active, self-FK created_by.
• operator_sessions — token_hash CHAR(64) UNIQUE, idle expires_at + absolute_expiry, revoked_at, mfa_satisfied; FK → platform_operators CASCADE.
• operator_audit — append-only, FK-free: operator_id, operator_email, caller_scope, action, target_type/target_id, organization_id, request_body JSON, result, ip_address, correlation_id.
• mailbox_sessions (0015) — webmail sessions: email_account_id FK CASCADE, organization_id, token_hash UNIQUE, expires_at/absolute_expiry/revoked_at, mfa_satisfied.
• mailbox_preferences (0016) — one row per mailbox (UNIQUE email_account_id, FK CASCADE): signature_html, signature_on_reply, display_density, undo_send_enabled, undo_send_seconds.
• users / web_sessions — tenant-dashboard admins and their sessions (users UNIQUE(organization_id,email), FK → organizations; web_sessions.token_hash UNIQUE, FK → users).
• failed_auth_attempts — brute-force ledger; service ENUM grew to (smtp,imap,pop3,api,sieve,api_key,operator,webmail) across migrations 0004/0007/0015.
• totp_secrets — INT PK, user_email UNIQUE, secret, backup_codes JSON, enabled, verified.
• idempotency_keys — UNIQUE(organization_id,idempotency_key), request hash + cached response, state, expires_at; FK → organizations.
• ip_access_rules, ip_reputation, user_logins, audit_logs (+ the audit_log view) — access rules, per-IP reputation, login history, audit trail.

Certificates & Keys¶

• dkim_keys — domain_id FK CASCADE, selector (default default), public_key, and since migration 0003 the private key is envelope-encrypted: private_key_ciphertext BLOB + private_key_nonce + key_version (plaintext private_key column retained but nullable/legacy).
• ssl_certificates — cert_manager's ledger: domain_id, certificate_path, private_key_path, status ENUM(active,expired,revoked,pending), valid_from/valid_until, auto_renew, last_renewed.
• acme_account_stats (0002) — per-ACME-account issuance counters.
• pgp_keys / smime_certs — user encryption keys; private material envelope-encrypted since 0003 (private_key_ciphertext/nonce/key_version; smime_certs.fingerprint UNIQUE).

Webhook Tables¶

• webhook_urls — per-endpoint config: organization_id, url, event_types JSON, service_types JSON, webhook_secret, encryption_key, priority, timeout_seconds (default 30), retry_attempts (default 3), retry_delay_seconds, rate_limit_per_minute, custom_headers, success_count/failure_count, last_success/last_failure.
• webhook_delivery_logs — one row per delivery outcome: event_type, event_data JSON (truncated at 100 KB), webhook_url, delivery_status ENUM(pending,delivered,failed,retrying,abandoned), attempts, http_status_code, request_duration_ms, error_message, timing columns.
• webhook_dead_letters — events that exhausted all retries: event_type, endpoint_url, payload JSON (truncated at 1 MB), last_error, attempt_count, status ENUM(pending,retrying,resolved,abandoned).

Alerting (migration 0010)¶

• alert_channels — type (email/webhook), target, envelope-encrypted signing secret, enabled, last_used_at/last_error.
• alert_rules — metric, comparator, threshold, for_seconds, severity (critical/warning/info), channel_ids JSON, organization_id (null = platform-wide; FK CASCADE).
• alert_events — firing history; FK → alert_rules SET NULL; state (firing/resolved).
• alerts (baseline) — the older quota/rate-limit alert table (alert_type ENUM(rate_limit,storage_quota), webhook delivery bookkeeping).

Analytics & Tracking Tables¶

• email_tracking — individual events: email_id, recipient, organization_id, event_type ENUM(delivered,opened,clicked,bounced,complained,unsubscribed), UA/IP/geo columns (device_type, browser, operating_system, country, region, city), additional_data JSON.
• tracking_statistics — per-email aggregates: total_count, unique_count, unique_ips, first_event/last_event.
• analytics_data — generic metric rows: metric_name, metric_value DECIMAL(15,4), dimensions JSON, period.
• domain_reputation — periodized scores: overall_score/deliverability_score/engagement_score (default 50), sent/delivered/bounced/complained/opened/clicked counters, period_type ENUM(HOURLY,DAILY,WEEKLY,MONTHLY), isp_data JSON.
• usage_history, feedback_loops, ai_transactions (AI usage/cost ledger, UNIQUE transaction_id).

Remaining Tables (by owner)¶

Useful Queries¶

-- Entity counts
SELECT
  (SELECT COUNT(*) FROM organizations) AS orgs,
  (SELECT COUNT(*) FROM domains) AS domains,
  (SELECT COUNT(*) FROM email_accounts) AS mailboxes,
  (SELECT COUNT(*) FROM aliases) AS aliases,
  (SELECT COUNT(*) FROM smtp_credentials) AS smtp_keys;

-- Recent bounces
SELECT sender, recipient, bounce_reason, timestamp
FROM mail_logs WHERE status = 'bounced'
ORDER BY timestamp DESC LIMIT 10;

-- Delivery by SMTP credential (last 24 h)
SELECT sasl_username, status, COUNT(*)
FROM mail_logs
WHERE timestamp > NOW() - INTERVAL 1 DAY AND sasl_username IS NOT NULL
GROUP BY sasl_username, status;

-- Current Alembic revision
SELECT * FROM alembic_version;