29 lines
1.3 KiB
SQL
29 lines
1.3 KiB
SQL
ALTER TABLE vehicle_alert_notification
|
|
ADD COLUMN recipient VARCHAR(160) NOT NULL DEFAULT '',
|
|
ADD COLUMN attempt_count INT NOT NULL DEFAULT 1,
|
|
ADD COLUMN provider_message_id VARCHAR(160) NOT NULL DEFAULT '',
|
|
ADD COLUMN delivered_at DATETIME(3) NULL,
|
|
ADD COLUMN last_error VARCHAR(500) NOT NULL DEFAULT '',
|
|
ADD COLUMN last_attempt_at DATETIME(3) NULL,
|
|
ADD COLUMN retry_requested_by VARCHAR(128) NOT NULL DEFAULT '',
|
|
ADD COLUMN retry_requested_at DATETIME(3) NULL,
|
|
ADD KEY idx_alert_notification_delivery (delivery_status, created_at),
|
|
ADD KEY idx_alert_notification_retry (retry_requested_at, id);
|
|
|
|
CREATE TABLE IF NOT EXISTS vehicle_alert_notification_retry_audit (
|
|
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
notification_id BIGINT NOT NULL,
|
|
idempotency_key VARCHAR(96) NOT NULL,
|
|
actor VARCHAR(128) NOT NULL,
|
|
reason VARCHAR(200) NOT NULL,
|
|
previous_status VARCHAR(32) NOT NULL,
|
|
next_status VARCHAR(32) NOT NULL,
|
|
attempt_count INT NOT NULL,
|
|
requested_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
UNIQUE KEY uk_alert_notification_retry_key (idempotency_key),
|
|
KEY idx_alert_notification_retry_notification (notification_id, requested_at),
|
|
CONSTRAINT fk_alert_notification_retry_notification
|
|
FOREIGN KEY (notification_id) REFERENCES vehicle_alert_notification(id)
|
|
ON DELETE CASCADE
|
|
);
|