V1__Complete_notification_schema.sql
7.61 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
-- V1__Complete_notification_schema.sql
-- =============================================
-- 通知偏好设置表
-- =============================================
CREATE TABLE IF NOT EXISTS notification_preferences (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
email_enabled BOOLEAN DEFAULT true,
sms_enabled BOOLEAN DEFAULT false,
push_enabled BOOLEAN DEFAULT true,
email_frequency VARCHAR(20) DEFAULT 'IMMEDIATE',
quiet_hours_start TIME,
quiet_hours_end TIME,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
version INTEGER DEFAULT 0,
UNIQUE(user_id)
);
-- =============================================
-- 通知记录表
-- =============================================
CREATE TABLE IF NOT EXISTS notifications (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
type VARCHAR(50) NOT NULL,
channel VARCHAR(20) NOT NULL,
title VARCHAR(255) NOT NULL,
message TEXT NOT NULL,
status VARCHAR(20) DEFAULT 'PENDING',
metadata JSONB,
scheduled_at TIMESTAMP,
sent_at TIMESTAMP,
read_at TIMESTAMP,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
version INTEGER DEFAULT 0
);
-- =============================================
-- 通知模板表
-- =============================================
CREATE TABLE IF NOT EXISTS notification_templates (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE,
type VARCHAR(50) NOT NULL,
channel VARCHAR(20) NOT NULL,
subject VARCHAR(255),
content TEXT NOT NULL,
variables JSONB,
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
version INTEGER DEFAULT 0
);
-- =============================================
-- 通知系统设置表
-- =============================================
CREATE TABLE IF NOT EXISTS notification_settings (
id BIGSERIAL PRIMARY KEY,
setting_key VARCHAR(100) NOT NULL UNIQUE,
setting_value TEXT,
description TEXT,
is_enabled BOOLEAN DEFAULT true,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
version INTEGER DEFAULT 0
);
-- =============================================
-- 创建索引
-- =============================================
-- notifications 表索引
CREATE INDEX IF NOT EXISTS idx_notifications_user_id ON notifications(user_id);
CREATE INDEX IF NOT EXISTS idx_notifications_status ON notifications(status);
CREATE INDEX IF NOT EXISTS idx_notifications_created_at ON notifications(created_at);
CREATE INDEX IF NOT EXISTS idx_notifications_type ON notifications(type);
CREATE INDEX IF NOT EXISTS idx_notifications_channel ON notifications(channel);
CREATE INDEX IF NOT EXISTS idx_notifications_scheduled_at ON notifications(scheduled_at);
-- notification_preferences 表索引
CREATE INDEX IF NOT EXISTS idx_notification_preferences_user_id ON notification_preferences(user_id);
-- notification_templates 表索引
CREATE INDEX IF NOT EXISTS idx_notification_templates_type ON notification_templates(type);
CREATE INDEX IF NOT EXISTS idx_notification_templates_channel ON notification_templates(channel);
CREATE INDEX IF NOT EXISTS idx_notification_templates_active ON notification_templates(is_active);
-- =============================================
-- 插入默认通知模板数据
-- =============================================
INSERT INTO notification_templates (name, type, channel, subject, content, variables, is_active) VALUES
-- 订单相关邮件模板
('ORDER_CONFIRMATION_EMAIL', 'ORDER_CONFIRMATION', 'EMAIL', '订单确认 - 订单号: {orderNumber}',
'<html><body><h1>订单确认</h1><p>尊敬的 {customerName}，</p><p>您的订单 #{orderNumber} 已确认。</p><p>订单总金额: {orderAmount}</p><p>预计发货时间: {estimatedDelivery}</p><p>感谢您的购买！</p></body></html>',
'["orderNumber", "customerName", "orderAmount", "estimatedDelivery"]', true),
('ORDER_SHIPPED_EMAIL', 'ORDER_SHIPPED', 'EMAIL', '订单已发货 - 订单号: {orderNumber}',
'<html><body><h1>订单已发货</h1><p>尊敬的 {customerName}，</p><p>您的订单 #{orderNumber} 已发货。</p><p>物流公司: {shippingCarrier}</p><p>运单号: {trackingNumber}</p><p>预计到达: {estimatedArrival}</p></body></html>',
'["orderNumber", "customerName", "shippingCarrier", "trackingNumber", "estimatedArrival"]', true),
('ORDER_DELIVERED_EMAIL', 'ORDER_DELIVERED', 'EMAIL', '订单已送达 - 订单号: {orderNumber}',
'<html><body><h1>订单已送达</h1><p>尊敬的 {customerName}，</p><p>您的订单 #{orderNumber} 已成功送达。</p><p>希望您对商品满意，期待再次为您服务！</p></body></html>',
'["orderNumber", "customerName"]', true),
-- 用户相关邮件模板
('WELCOME_EMAIL', 'WELCOME', 'EMAIL', '欢迎加入我们的平台！',
'<html><body><h1>欢迎加入！</h1><p>尊敬的 {customerName}，</p><p>感谢您注册我们的平台。</p><p>我们很高兴为您提供服务。</p></body></html>',
'["customerName"]', true),
('PASSWORD_RESET_EMAIL', 'PASSWORD_RESET', 'EMAIL', '密码重置请求',
'<html><body><h1>密码重置</h1><p>我们收到了您的密码重置请求。</p><p>重置链接: {resetLink}</p><p>如果您没有请求重置密码，请忽略此邮件。</p></body></html>',
'["resetLink"]', true),
-- SMS 模板
('ORDER_CONFIRMATION_SMS', 'ORDER_CONFIRMATION', 'SMS', NULL,
'尊敬的{customerName}，您的订单#{orderNumber}已确认，金额{orderAmount}。感谢您的购买！',
'["customerName", "orderNumber", "orderAmount"]', true),
('ORDER_SHIPPED_SMS', 'ORDER_SHIPPED', 'SMS', NULL,
'您的订单#{orderNumber}已发货，运单号{trackingNumber}，{shippingCarrier}承运。',
'["orderNumber", "trackingNumber", "shippingCarrier"]', true),
-- 推送通知模板
('PROMOTION_PUSH', 'PROMOTION', 'PUSH', '特别优惠',
'限时特惠！{discountPercentage}折扣等您来拿，仅限今天！',
'["discountPercentage"]', true),
('ABANDONED_CART_PUSH', 'ABANDONED_CART', 'PUSH', '购物车提醒',
'您的购物车还有商品未完成购买，立即完成订单享受优惠！',
'[]', true)
ON CONFLICT (name) DO NOTHING;
-- =============================================
-- 插入系统设置数据
-- =============================================
INSERT INTO notification_settings (setting_key, setting_value, description, is_enabled) VALUES
('EMAIL_PROVIDER', 'SENDGRID', '邮件服务提供商', true),
('SMS_PROVIDER', 'TWILIO', '短信服务提供商', true),
('PUSH_PROVIDER', 'FIREBASE', '推送通知服务提供商', true),
('MAX_RETRY_ATTEMPTS', '3', '最大重试次数', true),
('RETRY_DELAY_SECONDS', '60', '重试延迟时间（秒）', true),
('BATCH_PROCESSING_SIZE', '100', '批量处理大小', true),
('RATE_LIMIT_PER_MINUTE', '60', '每分钟速率限制', true),
('DEFAULT_TIMEZONE', 'UTC', '默认时区', true)
ON CONFLICT (setting_key) DO NOTHING;
-- =============================================
-- 添加表注释（可选）
-- =============================================
COMMENT ON TABLE notification_preferences IS '用户通知偏好设置';
COMMENT ON TABLE notifications IS '通知记录';
COMMENT ON TABLE notification_templates IS '通知模板';
COMMENT ON TABLE notification_settings IS '通知系统设置';
COMMENT ON COLUMN notification_preferences.email_frequency IS '邮件频率：IMMEDIATE, DAILY, WEEKLY';
COMMENT ON COLUMN notifications.channel IS '通知渠道：EMAIL, SMS, PUSH';
COMMENT ON COLUMN notifications.status IS '状态：PENDING, SENT, FAILED, READ';
COMMENT ON COLUMN notification_templates.variables IS '模板变量列表';