104 lines
5.6 KiB
SQL
104 lines
5.6 KiB
SQL
-- 进销存数据开放平台 - 数据库表结构
|
||
-- 数据库: jc_open_platform
|
||
|
||
-- ========== 客户表 ==========
|
||
CREATE TABLE IF NOT EXISTS `clients` (
|
||
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`client_no` varchar(50) DEFAULT NULL COMMENT '客户序号',
|
||
`name` varchar(200) NOT NULL COMMENT '客户名称',
|
||
`client_type` varchar(20) NOT NULL COMMENT '客户类型: supplier/distributor/hospital/other',
|
||
`contact_person` varchar(100) DEFAULT NULL COMMENT '联系人',
|
||
`phone` varchar(20) DEFAULT NULL COMMENT '手机号',
|
||
`status` tinyint(1) DEFAULT '1' COMMENT '1=启用 0=禁用',
|
||
`remark` text COMMENT '备注',
|
||
`created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_client_no` (`client_no`),
|
||
KEY `idx_client_type` (`client_type`),
|
||
KEY `idx_status` (`status`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='上下游客户表';
|
||
|
||
-- ========== API密钥表 ==========
|
||
CREATE TABLE IF NOT EXISTS `api_keys` (
|
||
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`client_id` bigint(20) unsigned NOT NULL COMMENT '客户ID',
|
||
`api_key` varchar(64) NOT NULL COMMENT 'API Key 公开标识',
|
||
`api_secret_hash` varchar(256) NOT NULL COMMENT 'API Secret 原始值（用于HMAC验证）',
|
||
`status` tinyint(1) DEFAULT '1' COMMENT '1=启用 0=禁用',
|
||
`rate_limit_per_minute` int(11) DEFAULT '60' COMMENT '每分钟限流次数',
|
||
`rate_limit_per_day` int(11) DEFAULT '5000' COMMENT '每天限流次数',
|
||
`created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_api_key` (`api_key`),
|
||
KEY `idx_client_id` (`client_id`),
|
||
KEY `idx_status` (`status`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='API密钥表';
|
||
|
||
-- ========== 权限配置表 ==========
|
||
CREATE TABLE IF NOT EXISTS `permissions` (
|
||
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`api_key_id` bigint(20) unsigned NOT NULL COMMENT 'API Key ID',
|
||
`allow_chuku` tinyint(1) DEFAULT '0' COMMENT '是否允许销售接口 1=允许 0=禁止',
|
||
`allow_ruku` tinyint(1) DEFAULT '0' COMMENT '是否允许入库接口 1=允许 0=禁止',
|
||
`allow_full_data` tinyint(1) DEFAULT '0' COMMENT '是否允许全量数据 1=允许 0=过滤',
|
||
`data_mode` varchar(20) DEFAULT 'filter' COMMENT '数据过滤模式: filter=按接口权限过滤, custom=自定义过滤规则, full=全量数据',
|
||
`filter_field` varchar(50) DEFAULT NULL COMMENT '过滤字段: 销方全称/供方全称/销方序号/供方序号',
|
||
`filter_values` json DEFAULT NULL COMMENT '过滤值 JSON 数组',
|
||
`created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_api_key_id` (`api_key_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='权限配置表';
|
||
|
||
-- ========== 管理员表 ==========
|
||
CREATE TABLE IF NOT EXISTS `admin_users` (
|
||
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`username` varchar(50) NOT NULL COMMENT '用户名',
|
||
`password` varchar(255) NOT NULL COMMENT '密码（bcrypt加密）',
|
||
`status` tinyint(1) DEFAULT '1' COMMENT '1=启用 0=禁用',
|
||
`created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_username` (`username`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管理员表';
|
||
|
||
-- ========== API调用日志表 ==========
|
||
CREATE TABLE IF NOT EXISTS `api_logs` (
|
||
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`api_key_id` bigint(20) unsigned NOT NULL COMMENT 'API Key ID',
|
||
`client_id` bigint(20) unsigned NOT NULL COMMENT '客户ID',
|
||
`endpoint` varchar(100) NOT NULL COMMENT '接口路径',
|
||
`request_params` json DEFAULT NULL COMMENT '请求参数',
|
||
`response_code` int(11) DEFAULT NULL COMMENT '响应状态码',
|
||
`response_body` json DEFAULT NULL COMMENT '响应体摘要',
|
||
`record_count` int(11) DEFAULT '0' COMMENT '返回记录数',
|
||
`sql_query` text COMMENT '执行的SQL语句',
|
||
`duration_ms` int(11) DEFAULT NULL COMMENT '执行耗时（毫秒）',
|
||
`ip_address` varchar(50) DEFAULT NULL COMMENT '客户端IP',
|
||
`error_message` varchar(500) DEFAULT NULL COMMENT '错误信息',
|
||
`created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_api_key_id` (`api_key_id`),
|
||
KEY `idx_client_id` (`client_id`),
|
||
KEY `idx_endpoint` (`endpoint`),
|
||
KEY `idx_created_at` (`created_at`),
|
||
KEY `idx_response_code` (`response_code`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='API调用日志表';
|
||
|
||
-- ========== 初始化数据 ==========
|
||
-- 插入默认管理员（用户名: admin，密码: admin123）
|
||
INSERT INTO `admin_users` (`username`, `password`, `status`) VALUES
|
||
('admin', '$2y$10$Kds/RYu23SNPlroW3NnyveSP5Yd2JiYWklgx5zLE5Y/ORYlzi8F52', 1)
|
||
ON DUPLICATE KEY UPDATE `id` = `id`;
|
||
|
||
-- ========== 外键约束 ==========
|
||
ALTER TABLE `api_keys`
|
||
ADD CONSTRAINT `fk_api_keys_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE;
|
||
|
||
ALTER TABLE `permissions`
|
||
ADD CONSTRAINT `fk_permissions_api_key` FOREIGN KEY (`api_key_id`) REFERENCES `api_keys` (`id`) ON DELETE CASCADE;
|
||
|
||
ALTER TABLE `api_logs`
|
||
ADD CONSTRAINT `fk_api_logs_api_key` FOREIGN KEY (`api_key_id`) REFERENCES `api_keys` (`id`) ON DELETE CASCADE,
|
||
ADD CONSTRAINT `fk_api_logs_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE; |