etrade_sentinel.sql
7.13 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
USE dili_etrade;
DROP TABLE IF EXISTS `sentinel_rule`;
CREATE TABLE `sentinel_rule` (
`id` bigint NOT NULL AUTO_INCREMENT,
`market_id` bigint unsigned NOT NULL COMMENT '市场id',
`name` varchar(25) NOT NULL DEFAULT '' COMMENT '规则名称',
`type` tinyint unsigned NOT NULL COMMENT '1:单价预警、2:库存预警、3:售卖周期预警',
`expiry_begin` datetime NOT NULL COMMENT '有效期开始时间',
`expiry_end` datetime NOT NULL COMMENT '有效期结束时间',
`lower_limit` bigint unsigned NOT NULL COMMENT '库存:比例(数字) 单价:下限(金额分) 有效期:超出天数(数字)',
`upper_limit` bigint unsigned DEFAULT NULL COMMENT '单价:上限(金额分)',
`state` tinyint unsigned NOT NULL COMMENT '规则状态：0未启用 1启用中',
`version` int unsigned NOT NULL DEFAULT '1' COMMENT '版本号',
`creator_id` bigint NOT NULL COMMENT '创建人id',
`creator_name` varchar(50) NOT NULL DEFAULT '' COMMENT '创建人名称',
`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`modifier_id` bigint DEFAULT NULL COMMENT '修改人id',
`modifier_name` varchar(50) DEFAULT NULL COMMENT '修改人名称',
`modified_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`id`),
UNIQUE KEY `unq_type_name` (`market_id`,`name`,`type`) USING BTREE COMMENT '同一类型的规则名称唯一索引',
KEY `idx_expiry` (`market_id`,`expiry_begin`) USING BTREE COMMENT '有效时间索引',
KEY `idx_create` (`market_id`,`created_time`) USING BTREE COMMENT '创建时间'
) ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='风控-规则配置';
DROP TABLE IF EXISTS `sentinel_rule_category`;
CREATE TABLE `sentinel_rule_category` (
`id` bigint NOT NULL AUTO_INCREMENT,
`rule_id` bigint NOT NULL COMMENT '关联规则id',
`category_id` bigint NOT NULL COMMENT '品类id',
`category_name` varchar(255) DEFAULT '' COMMENT '品类名称(基础数据)',
PRIMARY KEY (`id`),
KEY `idx_rule_id` (`rule_id`) USING BTREE COMMENT '规则索引',
KEY `idx_cateory_id` (`category_id`) USING BTREE COMMENT '品类索引'
) ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='风控-规则关联品类';
DROP TABLE IF EXISTS `sentinel_product_expiry`;
CREATE TABLE `sentinel_product_expiry` (
`id` bigint NOT NULL AUTO_INCREMENT,
`market_id` bigint NOT NULL COMMENT '市场id',
`rule_id` bigint DEFAULT NULL COMMENT '规则id',
`rule_category_id` bigint DEFAULT NULL COMMENT '品类规则id',
`category_id` bigint NOT NULL COMMENT '品类id',
`batch_code` varchar(255) NOT NULL DEFAULT '' COMMENT '批次号(进门)',
`shop_id` bigint NOT NULL COMMENT '店铺id',
`product_id` bigint NOT NULL COMMENT '商品id',
`product_name` varchar(80) NOT NULL COMMENT '商品名称',
`effective_time` datetime NOT NULL COMMENT '有效终止时间(品类有效期+超出天数)',
`created_time` datetime NOT NULL COMMENT '消息接收时间',
PRIMARY KEY (`id`),
KEY `idx_category` (`market_id`,`category_id`) USING BTREE COMMENT '品类索引',
KEY `idx_rule_category` (`rule_category_id`) USING BTREE COMMENT '批次索引'
) ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='风控-商品有效期';
DROP TABLE IF EXISTS `sentinel_category_config`;
CREATE TABLE `sentinel_category_config` (
`id` bigint NOT NULL AUTO_INCREMENT,
`market_id` bigint unsigned NOT NULL COMMENT '市场id',
`category_id` bigint unsigned NOT NULL COMMENT '品类id',
`category_name` varchar(35) DEFAULT NULL COMMENT '品类名称',
`category_parent_id` bigint DEFAULT NULL COMMENT '品类父id',
`category_path` varchar(100) DEFAULT NULL COMMENT '品类完整路径',
`sales_cycle` int unsigned NOT NULL DEFAULT '0' COMMENT '销售周期(天)',
`version` int unsigned NOT NULL DEFAULT '1' COMMENT '版本号',
`creator_id` bigint NOT NULL COMMENT '创建人id',
`creator_name` varchar(50) NOT NULL DEFAULT '' COMMENT '创建人名称',
`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`modifier_id` bigint DEFAULT NULL COMMENT '修改人id',
`modifier_name` varchar(50) DEFAULT NULL COMMENT '修改人名称',
`modified_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`id`),
UNIQUE KEY `unq_market_category` (`market_id`,`category_id`) USING BTREE COMMENT '品类索引',
KEY `idx_path` (`category_path`) USING BTREE COMMENT '左匹配'
) ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='风控-品类预警基础设置';
DROP TABLE IF EXISTS `sentinel_inform`;
CREATE TABLE `sentinel_inform` (
`id` bigint NOT NULL AUTO_INCREMENT,
`market_id` bigint NOT NULL COMMENT '市场id',
`rule_id` bigint NOT NULL COMMENT '关联预警规则id',
`rule_type` tinyint unsigned NOT NULL COMMENT '预警类型：1:单价预警、2:库存预警、3:售卖周期预警',
`code` varchar(50) NOT NULL DEFAULT '' COMMENT '通知编号',
`order_code` varchar(50) DEFAULT '' COMMENT '订单编号',
`batch_code` varchar(255) DEFAULT '' COMMENT '批次号(存在多批次)',
`product_id` bigint DEFAULT NULL COMMENT '商品id',
`product_name` varchar(80) DEFAULT NULL COMMENT '商品名称',
`category_id` bigint DEFAULT NULL COMMENT '品类id',
`seller_id` bigint DEFAULT NULL COMMENT '卖家id',
`seller_name` varchar(50) DEFAULT NULL COMMENT '卖家名称',
`buyer_id` bigint DEFAULT NULL COMMENT '买家id',
`buyer_name` varchar(50) DEFAULT NULL COMMENT '买家名称',
`inform_time` datetime NOT NULL COMMENT '预警时间',
`state` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '状态 0未处理 1已处理',
`audit_state` tinyint unsigned DEFAULT NULL COMMENT '已处理状态下-处理结果1正常 2异常 3忽略',
`auditor_id` bigint DEFAULT NULL COMMENT '审核人id',
`auditor_name` varchar(50) DEFAULT NULL COMMENT '审核人名称',
`auditor_time` datetime DEFAULT NULL COMMENT '审核时间',
`auditor_source` tinyint unsigned DEFAULT '1' COMMENT '审核来源-1PC 2APP',
`version` int unsigned NOT NULL DEFAULT '1' COMMENT '版本号',
PRIMARY KEY (`id`),
KEY `idx_inform_time` (`inform_time`) USING BTREE COMMENT '预警时间',
KEY `idx_order` (`market_id`,`order_code`) USING BTREE COMMENT '订单编码',
KEY `idx_code` (`market_id`,`code`) USING BTREE COMMENT '通知编码',
KEY `idx_seller` (`market_id`,`seller_name`) USING BTREE COMMENT '卖家'
) ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='风控-告警通知';
DROP TABLE IF EXISTS `sentinel_inform_detail`;
CREATE TABLE `sentinel_inform_detail` (
`id` bigint NOT NULL AUTO_INCREMENT,
`inform_id` bigint NOT NULL COMMENT '关联通知id',
`effective_time` datetime DEFAULT NULL COMMENT '有效期-有效时间',
`inform_context` varchar(500) NOT NULL DEFAULT '' COMMENT '预警信息(文本)',
`auditor_result` varchar(250) DEFAULT '' COMMENT '审核结果(备注)',
`details` json DEFAULT NULL COMMENT '冗余业务数据内容',
PRIMARY KEY (`id`),
KEY `idx_inform` (`inform_id`) USING BTREE COMMENT '关联通知索引'
) ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='风控-通知详情';