| 35956 |
amit |
1 |
-- Mail Outbox tables for async mail sending
|
|
|
2 |
-- Run against the dtr schema
|
|
|
3 |
|
|
|
4 |
CREATE TABLE IF NOT EXISTS mail_outbox (
|
|
|
5 |
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
|
6 |
email_to VARCHAR(2000) NOT NULL,
|
|
|
7 |
email_cc VARCHAR(2000),
|
|
|
8 |
email_bcc VARCHAR(2000),
|
|
|
9 |
subject VARCHAR(500) NOT NULL,
|
|
|
10 |
body LONGTEXT NOT NULL,
|
|
|
11 |
is_html TINYINT(1) DEFAULT 0,
|
|
|
12 |
status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
|
|
|
13 |
retry_count INT DEFAULT 0,
|
|
|
14 |
error_message VARCHAR(1000),
|
|
|
15 |
created_at DATETIME NOT NULL,
|
|
|
16 |
sent_at DATETIME,
|
|
|
17 |
source VARCHAR(200),
|
|
|
18 |
sender_type VARCHAR(20) DEFAULT 'SENDGRID',
|
|
|
19 |
INDEX idx_mail_outbox_status (status),
|
|
|
20 |
INDEX idx_mail_outbox_created (created_at),
|
|
|
21 |
INDEX idx_mail_outbox_status_retry (status, retry_count)
|
|
|
22 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
23 |
|
|
|
24 |
CREATE TABLE IF NOT EXISTS mail_outbox_attachment (
|
|
|
25 |
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
|
26 |
mail_outbox_id BIGINT NOT NULL,
|
|
|
27 |
file_name VARCHAR(500) NOT NULL,
|
|
|
28 |
file_data LONGBLOB NOT NULL,
|
|
|
29 |
content_type VARCHAR(100),
|
|
|
30 |
INDEX idx_mail_attachment_mail_id (mail_outbox_id),
|
|
|
31 |
CONSTRAINT fk_mail_attachment_outbox FOREIGN KEY (mail_outbox_id) REFERENCES mail_outbox(id) ON DELETE CASCADE
|
|
|
32 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|