-- mass e-mail to addresses that are not clients (e.g. the administrators of servers running your software),
-- imported with bin/console massmail:import; each has its own unsubscribe link and bounce mark
CREATE TABLE mass_contacts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL,
  name VARCHAR(190) NOT NULL DEFAULT '',
  list VARCHAR(64) NOT NULL DEFAULT '',
  unsubscribed_at DATETIME NULL,
  bounced_at DATETIME NULL,
  bounce_reason VARCHAR(255) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL,
  UNIQUE KEY email (email),
  KEY list (list)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE mail_queue MODIFY client_id INT UNSIGNED NULL, ADD contact_id INT UNSIGNED NULL AFTER client_id, ADD UNIQUE KEY one_contact (mass_id, contact_id);