-- 三端消息中心(MySQL 5.7+/8.0) -- RabbitMQ 只负责可靠事件和在线推送,以下表是消息及离线状态的最终数据源。 create table if not exists notification_message ( id varchar(32) not null comment '消息ID', event_id varchar(64) not null comment '全链路幂等事件ID', message_type varchar(32) not null comment 'SYSTEM/EXAM/SCORE/LEAVE/COURSE/TEACHING_PLAN', title varchar(200) not null, summary varchar(500) null, content text not null, sender_id varchar(64) null, sender_name varchar(100) null, business_type varchar(64) null, business_id varchar(64) null, action_url varchar(500) null, priority varchar(16) not null default 'NORMAL' comment 'NORMAL/IMPORTANT/URGENT', created_at datetime(3) not null, expires_at datetime(3) null, primary key (id), unique key uk_notification_message_event (event_id), key idx_notification_message_created (created_at), key idx_notification_message_business (business_type, business_id) ) engine=InnoDB comment='消息正文'; create table if not exists notification_message_recipient ( id varchar(32) not null, message_id varchar(32) not null, user_id varchar(64) not null, read_flag tinyint(1) not null default 0, read_at datetime(3) null, deleted_flag tinyint(1) not null default 0, deleted_at datetime(3) null, delivered_at datetime(3) null comment '已投递到至少一个在线连接的时间', created_at datetime(3) not null, primary key (id), unique key uk_notification_recipient (message_id, user_id), key idx_notification_recipient_inbox (user_id, deleted_flag, read_flag, created_at), constraint fk_notification_recipient_message foreign key (message_id) references notification_message (id) ) engine=InnoDB comment='用户消息状态'; create table if not exists notification_outbox ( id varchar(32) not null, event_id varchar(64) not null, routing_key varchar(100) not null, payload longtext not null, status varchar(16) not null comment 'NEW/PUBLISHING/PUBLISHED/RETRY/FAILED', retry_count int not null default 0, scheduled_at datetime(3) not null, next_retry_at datetime(3) null, published_at datetime(3) null, last_error varchar(1000) null, created_at datetime(3) not null, updated_at datetime(3) not null, primary key (id), unique key uk_notification_outbox_event (event_id), key idx_notification_outbox_due (status, scheduled_at, next_retry_at) ) engine=InnoDB comment='消息事务发件箱';