189 lines
9.8 KiB
SQL
189 lines
9.8 KiB
SQL
-- ============================================================
|
||
-- Msg 服务数据库初始化脚本（v1.0 完整实现）
|
||
-- 对齐 services/msg/src/notifications/notifications.schema.ts
|
||
-- services/msg/src/shared/outbox/outbox.schema.ts
|
||
-- 仲裁依据：02-architecture-design.md §3.1 + G11（cuid2 varchar(32)）
|
||
-- ============================================================
|
||
|
||
-- 数据库已由 docker-compose 创建（msg_db），此处仅建表
|
||
-- 若需切换数据库：USE msg_db;
|
||
|
||
SET NAMES utf8mb4;
|
||
SET FOREIGN_KEY_CHECKS = 0;
|
||
|
||
-- ============================================================
|
||
-- 1. msg_notifications（通知主表 · 扩展）
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS `msg_notifications` (
|
||
`id` VARCHAR(32) NOT NULL COMMENT 'cuid2 主键',
|
||
`user_id` VARCHAR(32) NOT NULL COMMENT '接收用户 ID',
|
||
`type` VARCHAR(32) NOT NULL COMMENT '通知类型: system/exam/homework/grade/attendance/mastery',
|
||
`title` VARCHAR(255) NOT NULL COMMENT '通知标题',
|
||
`content` TEXT NOT NULL COMMENT '通知正文',
|
||
`channel` VARCHAR(32) NOT NULL COMMENT '渠道: in_app/email/sms/push/wechat',
|
||
`is_read` BOOLEAN NOT NULL DEFAULT FALSE COMMENT '是否已读',
|
||
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`status` VARCHAR(32) NOT NULL DEFAULT 'pending' COMMENT '状态: pending/sent/delivered/read/recalled/failed',
|
||
`metadata` JSON NULL COMMENT '扩展元数据',
|
||
`related_entity_type` VARCHAR(64) NULL COMMENT '关联实体类型(exam/homework/grade/attendance/mastery)',
|
||
`related_entity_id` VARCHAR(32) NULL COMMENT '关联实体 ID',
|
||
`group_id` VARCHAR(32) NULL COMMENT '群组 ID（批量发送/撤回用）',
|
||
`sender_id` VARCHAR(32) NULL COMMENT '发送者 ID',
|
||
`template_id` VARCHAR(32) NULL COMMENT '使用的模板 ID',
|
||
`event_id` VARCHAR(64) NULL COMMENT '幂等键（Kafka event_id 或自生成）',
|
||
`read_at` TIMESTAMP NULL COMMENT '已读时间',
|
||
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_notifications_event_id` (`event_id`),
|
||
KEY `idx_notifications_user_id` (`user_id`),
|
||
KEY `idx_notifications_is_read` (`is_read`),
|
||
KEY `idx_notifications_created_at` (`created_at`),
|
||
KEY `idx_notifications_user_read` (`user_id`, `is_read`),
|
||
KEY `idx_notifications_group_id` (`group_id`),
|
||
KEY `idx_notifications_related` (`related_entity_type`, `related_entity_id`),
|
||
KEY `idx_notifications_status` (`status`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='msg 通知主表';
|
||
|
||
-- ============================================================
|
||
-- 2. msg_notification_preferences（用户偏好 · 扩展）
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS `msg_notification_preferences` (
|
||
`id` VARCHAR(32) NOT NULL COMMENT 'cuid2 主键',
|
||
`user_id` VARCHAR(32) NOT NULL COMMENT '用户 ID',
|
||
`type` VARCHAR(32) NOT NULL COMMENT '通知类型',
|
||
`channels` JSON NOT NULL COMMENT '启用的渠道数组,如 ["in_app","email"]',
|
||
`frequency_limit` INT NULL COMMENT '频率限制(每小时最大通知数)',
|
||
`quiet_hours_start` VARCHAR(8) NULL COMMENT '免打扰开始(HH:MM)',
|
||
`quiet_hours_end` VARCHAR(8) NULL COMMENT '免打扰结束(HH:MM)',
|
||
`quiet_hours_timezone` VARCHAR(64) NULL DEFAULT 'Asia/Shanghai',
|
||
`enabled` BOOLEAN NOT NULL DEFAULT TRUE COMMENT '是否启用该类型偏好',
|
||
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_preferences_user_type` (`user_id`, `type`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='msg 用户通知偏好';
|
||
|
||
-- ============================================================
|
||
-- 3. msg_notification_templates（通知模板）
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS `msg_notification_templates` (
|
||
`id` VARCHAR(32) NOT NULL COMMENT 'cuid2 主键',
|
||
`code` VARCHAR(64) NOT NULL COMMENT '模板编码(业务可读)',
|
||
`type` VARCHAR(32) NOT NULL COMMENT '通知类型',
|
||
`title_template` VARCHAR(255) NOT NULL COMMENT '标题模板(含 {{var}})',
|
||
`content_template` TEXT NOT NULL COMMENT '正文模板(含 {{var}})',
|
||
`default_channels` JSON NOT NULL COMMENT '默认渠道数组',
|
||
`variables` JSON NOT NULL COMMENT '变量声明数组,如 ["userName","examTitle"]',
|
||
`locale` VARCHAR(16) NOT NULL DEFAULT 'zh-CN',
|
||
`status` VARCHAR(32) NOT NULL DEFAULT 'draft' COMMENT '状态: draft/active/archived',
|
||
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_templates_code` (`code`),
|
||
KEY `idx_templates_type` (`type`),
|
||
KEY `idx_templates_status` (`status`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='msg 通知模板';
|
||
|
||
-- ============================================================
|
||
-- 4. msg_notification_deliveries（投递记录）
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS `msg_notification_deliveries` (
|
||
`id` VARCHAR(32) NOT NULL COMMENT 'cuid2 主键',
|
||
`notification_id` VARCHAR(32) NOT NULL COMMENT '关联通知 ID',
|
||
`channel` VARCHAR(32) NOT NULL COMMENT '投递渠道',
|
||
`status` VARCHAR(32) NOT NULL DEFAULT 'pending' COMMENT '状态: pending/sent/delivered/failed/retrying',
|
||
`external_id` VARCHAR(128) NULL COMMENT '外部服务返回的消息 ID',
|
||
`attempt_count` INT NOT NULL DEFAULT 0,
|
||
`max_retry` INT NOT NULL DEFAULT 3,
|
||
`last_error` TEXT NULL,
|
||
`next_retry_at` TIMESTAMP NULL,
|
||
`delivered_at` TIMESTAMP NULL,
|
||
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_deliveries_notification_id` (`notification_id`),
|
||
KEY `idx_deliveries_status` (`status`),
|
||
KEY `idx_deliveries_next_retry` (`next_retry_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='msg 通知投递记录';
|
||
|
||
-- ============================================================
|
||
-- 5. msg_outbox_events（事务性 Outbox）
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS `msg_outbox_events` (
|
||
`event_id` VARCHAR(64) NOT NULL COMMENT '事件 ID(Kafka 幂等键)',
|
||
`aggregate_type` VARCHAR(64) NOT NULL COMMENT '聚合类型: notification',
|
||
`aggregate_id` VARCHAR(32) NOT NULL COMMENT '聚合 ID(通知 ID)',
|
||
`event_type` VARCHAR(64) NOT NULL COMMENT '事件类型: notification.sent/read/recalled/failed',
|
||
`topic` VARCHAR(128) NOT NULL COMMENT '目标 Kafka topic',
|
||
`payload` JSON NOT NULL COMMENT '事件 payload',
|
||
`status` VARCHAR(16) NOT NULL DEFAULT 'pending' COMMENT '状态: pending/published/failed',
|
||
`retry_count` INT NOT NULL DEFAULT 0,
|
||
`max_retry_count` INT NOT NULL DEFAULT 5,
|
||
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`published_at` TIMESTAMP NULL,
|
||
`next_retry_at` TIMESTAMP NULL,
|
||
`last_error` TEXT NULL,
|
||
`metadata` JSON NULL,
|
||
PRIMARY KEY (`event_id`),
|
||
KEY `idx_outbox_status_next_retry` (`status`, `next_retry_at`),
|
||
KEY `idx_outbox_topic` (`topic`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='msg Outbox 事务性事件';
|
||
|
||
-- ============================================================
|
||
-- 6. processed_events（消费幂等 · Redis 降级表）
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS `processed_events` (
|
||
`event_id` VARCHAR(64) NOT NULL COMMENT '事件 ID(幂等键)',
|
||
`topic` VARCHAR(128) NOT NULL COMMENT '来源 topic',
|
||
`processed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (`event_id`),
|
||
KEY `idx_processed_topic` (`topic`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='msg 消费幂等记录';
|
||
|
||
SET FOREIGN_KEY_CHECKS = 1;
|
||
|
||
-- ============================================================
|
||
-- 初始化数据：默认通知模板（可选）
|
||
-- ============================================================
|
||
INSERT INTO `msg_notification_templates` (`id`, `code`, `type`, `title_template`, `content_template`, `default_channels`, `variables`, `locale`, `status`)
|
||
SELECT * FROM (SELECT
|
||
'tmpl_welcome_001' AS id,
|
||
'welcome' AS code,
|
||
'system' AS type,
|
||
'欢迎加入 Edu 云课堂' AS title_template,
|
||
'你好 {{userName}}，欢迎加入 Edu 云课堂！开始你的学习之旅吧。' AS content_template,
|
||
JSON_ARRAY('in_app') AS default_channels,
|
||
JSON_ARRAY('userName') AS variables,
|
||
'zh-CN' AS locale,
|
||
'active' AS status
|
||
) AS tmp
|
||
WHERE NOT EXISTS (SELECT 1 FROM `msg_notification_templates` WHERE `code` = 'welcome');
|
||
|
||
INSERT INTO `msg_notification_templates` (`id`, `code`, `type`, `title_template`, `content_template`, `default_channels`, `variables`, `locale`, `status`)
|
||
SELECT * FROM (SELECT
|
||
'tmpl_exam_published_001' AS id,
|
||
'exam_published' AS code,
|
||
'exam' AS type,
|
||
'新考试通知' AS title_template,
|
||
'「{{className}}」班级发布了新考试：{{examTitle}}' AS content_template,
|
||
JSON_ARRAY('in_app') AS default_channels,
|
||
JSON_ARRAY('className', 'examTitle') AS variables,
|
||
'zh-CN' AS locale,
|
||
'active' AS status
|
||
) AS tmp
|
||
WHERE NOT EXISTS (SELECT 1 FROM `msg_notification_templates` WHERE `code` = 'exam_published');
|
||
|
||
INSERT INTO `msg_notification_templates` (`id`, `code`, `type`, `title_template`, `content_template`, `default_channels`, `variables`, `locale`, `status`)
|
||
SELECT * FROM (SELECT
|
||
'tmpl_grade_recorded_001' AS id,
|
||
'grade_recorded' AS code,
|
||
'grade' AS type,
|
||
'成绩录入通知' AS title_template,
|
||
'你的{{subject}}成绩已录入{{score}}' AS content_template,
|
||
JSON_ARRAY('in_app') AS default_channels,
|
||
JSON_ARRAY('subject', 'score') AS variables,
|
||
'zh-CN' AS locale,
|
||
'active' AS status
|
||
) AS tmp
|
||
WHERE NOT EXISTS (SELECT 1 FROM `msg_notification_templates` WHERE `code` = 'grade_recorded');
|