zjrs-jeecgBoot/.docs/sql/建表语句/消息通知记录表.sql

-- ============================================================
-- 消息通知记录表 + 接收人关联表（DM8）
-- 设计：消息主题/内容只存一份，接收人独立关联，批量发送无冗余
-- 接收人名称不存储，查询时关联 PERSONAL_INFO/ENTERPRISE_INFO 实时获取
-- ============================================================

CREATE TABLE notification_record (
    ID            VARCHAR(36)  NOT NULL,
    MODULE_TYPE   VARCHAR(50)           COMMENT '来源模块',
    SUBJECT       VARCHAR(200) NOT NULL COMMENT '消息主题',
    CONTENT       VARCHAR(2000)         COMMENT '消息内容',
    SENDER_ID     VARCHAR(36)           COMMENT '推送人用户ID(关联sys_user)',
    SENDER        VARCHAR(50)  NOT NULL COMMENT '推送人姓名',
    SEND_TIME     TIMESTAMP    NOT NULL COMMENT '推送时间',
    CREATE_BY     VARCHAR(50),
    CREATE_TIME   TIMESTAMP    DEFAULT CURRENT_TIMESTAMP NOT NULL,
    SYS_ORG_CODE  VARCHAR(50),
    PRIMARY KEY (ID)
);
COMMENT ON TABLE notification_record IS '消息通知记录表';
ALTER TABLE notification_record ADD EXPIRE_TIME TIMESTAMP;
COMMENT ON COLUMN notification_record.EXPIRE_TIME IS '过期时间';
ALTER TABLE notification_record ADD STATUS VARCHAR(20) DEFAULT '0';
COMMENT ON COLUMN notification_record.STATUS IS '状态: 0未读 1已读';

CREATE TABLE notification_target (
    NOTIFICATION_ID VARCHAR(36) NOT NULL COMMENT '消息ID',
    TARGET_TYPE     VARCHAR(20) NOT NULL COMMENT '类型: personal/enterprise',
    TARGET_ID       VARCHAR(36) NOT NULL COMMENT '目标ID'
);
COMMENT ON TABLE notification_target IS '消息接收人关联表';
CREATE INDEX idx_nt_target ON notification_target(TARGET_TYPE, TARGET_ID);
CREATE INDEX idx_nt_notify ON notification_target(NOTIFICATION_ID);
ALTER TABLE notification_target ADD STATUS VARCHAR(20) DEFAULT '0';
COMMENT ON COLUMN notification_target.STATUS IS '状态: 0未读 1已读';