25 lines
1.1 KiB
SQL
25 lines
1.1 KiB
SQL
CREATE TABLE IF NOT EXISTS time_error_notification_state (
|
|
employee_id INT NOT NULL,
|
|
cycle_started_on DATE NOT NULL,
|
|
first_error_date DATE NOT NULL,
|
|
last_notification_stage VARCHAR(50) DEFAULT NULL,
|
|
last_notification_sent_at DATETIME DEFAULT NULL,
|
|
employee_day_1_sent_at DATETIME DEFAULT NULL,
|
|
employee_day_3_sent_at DATETIME DEFAULT NULL,
|
|
admin_day_7_sent_at DATETIME DEFAULT NULL,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (employee_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS time_error_notifications (
|
|
id INT NOT NULL AUTO_INCREMENT,
|
|
employee_id INT NOT NULL,
|
|
cycle_started_on DATE NOT NULL,
|
|
notification_stage VARCHAR(50) NOT NULL,
|
|
recipient_email VARCHAR(255) NOT NULL,
|
|
sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uniq_time_error_notification (employee_id, cycle_started_on, notification_stage, recipient_email)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|