21 KiB
21 KiB
泰豪电源 AI 数智化平台 - 数据库完整指南
更新日期: 2025-12-02
数据库: PostgreSQL 16
总表数: 50张核心业务表
📖 目录
1. 系统概述
1.1 设计理念
基于功能结构.xml的系统架构,遵循 "一个底座、多种智能体" 核心理念,支持四大业务模块:
- 资料管理智能化
- 招投标自动化
- 售后客服智能化
- 企业内部知识协同
1.2 技术选型
- 数据库: PostgreSQL 16
- 连接池: HikariCP
- ORM: MyBatis-Plus 3.5
- 迁移工具: Flyway (可选)
1.3 设计原则
- ✅ 模块化: 按业务模块划分 Schema
- ✅ 规范化: 统一命名规范和字段设计
- ✅ 可扩展: 预留扩展字段和软删除
- ✅ 高性能: 合理索引和分区设计
- ✅ 安全性: 行级安全和审计日志
2. Schema架构
2.1 Schema划分
采用多Schema架构,按业务模块逻辑隔离:
| Schema | 说明 | 核心表数量 | 用途 |
|---|---|---|---|
sys |
系统基础模块 | 11 | 用户、角色、权限、部门 |
file |
文件管理模块 | 1 | 文件存储与管理 |
message |
消息通知模块 | 4 | 站内信、系统通知 |
log |
日志审计模块 | 1 | 操作日志、审计 |
config |
系统配置模块 | 1 | 系统参数配置 |
knowledge |
知识库管理模块 | 4 | 文档、分段、问答对 |
bidding |
招投标智能体 | 8 | 招投标业务流程 |
customer_service |
智能客服系统 | 9 | 客服、工单、对话 |
agent |
智能体管理 | 11 | 智能体、API、监控 |
总计: 50张核心业务表
2.2 命名规范
-- Schema 命名: 小写字母
CREATE SCHEMA IF NOT EXISTS sys;
-- 表命名: tb_{schema}_{表名}
CREATE TABLE sys.tb_sys_user (...);
CREATE TABLE knowledge.tb_knowledge_document (...);
-- 字段命名: 小写字母 + 下划线
user_id, create_time, full_name
-- 索引命名: idx_{表名}_{字段名}
CREATE INDEX idx_user_email ON sys.tb_sys_user(email);
-- 外键命名: fk_{表名}_{关联表名}
CONSTRAINT fk_user_dept FOREIGN KEY (dept_id) ...
3. 核心模块设计
3.1 系统基础模块 (sys)
权限体系设计
采用 RBAC (基于角色的访问控制) + ACL (访问控制列表) 混合模型
核心表:
tb_sys_user- 用户表tb_sys_role- 角色表tb_sys_permission- 权限表tb_sys_dept- 部门表tb_sys_acl- 对象级权限表tb_sys_user_role- 用户角色关联表
权限模型:
用户 (User)
├─ 部门 (Dept) - 数据隔离
├─ 角色 (Role)
│ ├─ 权限 (Permission) - 功能权限
│ └─ 视图 (View) - 菜单权限
└─ ACL - 对象级权限(细粒度)
设计亮点:
- 多租户支持:
dept_path字段实现部门级数据隔离 - 角色作用域:
scope字段区分全局/部门角色 - 对象权限: ACL表支持任意对象的细粒度权限
核心表结构
-- 用户表
CREATE TABLE sys.tb_sys_user (
user_id VARCHAR(50) PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
email VARCHAR(100) UNIQUE,
phone VARCHAR(20),
wechat_id VARCHAR(100),
status VARCHAR(20) DEFAULT 'active',
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
deleted BOOLEAN DEFAULT false
);
-- 部门表(树形结构)
CREATE TABLE sys.tb_sys_dept (
dept_id VARCHAR(50) PRIMARY KEY,
dept_name VARCHAR(100) NOT NULL,
parent_id VARCHAR(50),
dept_path TEXT, -- 路径:/1/2/3/
sort_order INTEGER DEFAULT 0,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
deleted BOOLEAN DEFAULT false,
CONSTRAINT fk_dept_parent FOREIGN KEY (parent_id)
REFERENCES sys.tb_sys_dept(dept_id)
);
-- 角色表
CREATE TABLE sys.tb_sys_role (
role_id VARCHAR(50) PRIMARY KEY,
role_name VARCHAR(100) NOT NULL,
role_code VARCHAR(50) UNIQUE NOT NULL,
scope VARCHAR(20) DEFAULT 'global', -- global/dept
owner_dept_id VARCHAR(50),
description TEXT,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
deleted BOOLEAN DEFAULT false
);
-- ACL权限表
CREATE TABLE sys.tb_sys_acl (
acl_id VARCHAR(50) PRIMARY KEY,
object_type VARCHAR(50) NOT NULL, -- user/dept/document等
object_id VARCHAR(50) NOT NULL,
principal_type VARCHAR(20) NOT NULL, -- user/role/dept
principal_id VARCHAR(50) NOT NULL,
permission VARCHAR(50) NOT NULL, -- view/edit/delete/admin
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 索引
CREATE INDEX idx_user_status ON sys.tb_sys_user(status) WHERE deleted = false;
CREATE INDEX idx_dept_path ON sys.tb_sys_dept USING GIN(dept_path gin_trgm_ops);
CREATE INDEX idx_acl_object ON sys.tb_sys_acl(object_type, object_id);
3.2 知识库管理模块 (knowledge)
模块概述
支持文档管理、向量检索、RAG问答,版本控制。
核心表:
tb_knowledge_document- 文档主表tb_knowledge_chunk- 文档分段表(向量检索)tb_knowledge_qa_pair- 问答对表tb_knowledge_file_relation- 文档文件关联表
版本管理设计
采用 简化版本管理 方案:
-- 文档表(新增版本字段)
CREATE TABLE knowledge.tb_knowledge_document (
doc_id VARCHAR(50) PRIMARY KEY,
root_doc_id VARCHAR(50), -- ✨ 根文档ID(版本组标识)
version INTEGER DEFAULT 1, -- ✨ 版本号
is_current BOOLEAN DEFAULT true, -- ✨ 是否当前版本
title VARCHAR(500) NOT NULL,
content TEXT,
knowledge_base_id VARCHAR(50),
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
deleted BOOLEAN DEFAULT false
);
-- 分段表(向量检索)
CREATE TABLE knowledge.tb_knowledge_chunk (
chunk_id VARCHAR(50) PRIMARY KEY,
doc_id VARCHAR(50) NOT NULL,
chunk_index INTEGER NOT NULL,
content TEXT NOT NULL,
embedding VECTOR(1536), -- ✨ 向量字段(pgvector扩展)
version INTEGER DEFAULT 1, -- ✨ 乐观锁版本
tokens INTEGER,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_chunk_doc FOREIGN KEY (doc_id)
REFERENCES knowledge.tb_knowledge_document(doc_id)
);
-- 索引
CREATE INDEX idx_doc_root_current
ON knowledge.tb_knowledge_document(root_doc_id, is_current)
WHERE deleted = false;
CREATE INDEX idx_chunk_embedding
ON knowledge.tb_knowledge_chunk
USING ivfflat (embedding vector_cosine_ops) -- ✨ 向量索引
WITH (lists = 100);
版本管理示例:
-- 创建新版本
BEGIN;
-- 1. 标记旧版本为非当前
UPDATE knowledge.tb_knowledge_document
SET is_current = false
WHERE root_doc_id = 'root_xxx';
-- 2. 插入新版本
INSERT INTO knowledge.tb_knowledge_document (
doc_id, root_doc_id, version, is_current, title, content
) VALUES (
'new_doc_id', 'root_xxx', 2, true, '新版本标题', '内容'
);
COMMIT;
-- 查询当前版本
SELECT * FROM knowledge.tb_knowledge_document
WHERE root_doc_id = 'root_xxx' AND is_current = true;
-- 查询所有版本
SELECT version, title, create_time
FROM knowledge.tb_knowledge_document
WHERE root_doc_id = 'root_xxx'
ORDER BY version DESC;
3.3 招投标智能体模块 (bidding)
核心表
-- 招标项目表
CREATE TABLE bidding.tb_bidding_project (
project_id VARCHAR(50) PRIMARY KEY,
project_name VARCHAR(500) NOT NULL,
project_code VARCHAR(100),
bidding_org VARCHAR(200), -- 招标单位
project_type VARCHAR(50), -- 项目类型
status VARCHAR(50) DEFAULT 'draft',
budget DECIMAL(18,2),
bid_deadline TIMESTAMP,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
deleted BOOLEAN DEFAULT false
);
-- 招标文件表
CREATE TABLE bidding.tb_bidding_document (
doc_id VARCHAR(50) PRIMARY KEY,
project_id VARCHAR(50) NOT NULL,
doc_type VARCHAR(50), -- tender/technical/commercial
file_id VARCHAR(50),
parsed_content JSONB, -- ✨ 智能解析结果
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_bdoc_project FOREIGN KEY (project_id)
REFERENCES bidding.tb_bidding_project(project_id)
);
-- 标书模板表
CREATE TABLE bidding.tb_bidding_template (
template_id VARCHAR(50) PRIMARY KEY,
template_name VARCHAR(200) NOT NULL,
template_type VARCHAR(50),
content JSONB, -- 模板结构化内容
variables JSONB, -- 可替换变量
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
3.4 智能客服系统模块 (customer_service)
核心表
-- 对话会话表
CREATE TABLE customer_service.tb_cs_conversation (
conv_id VARCHAR(50) PRIMARY KEY,
customer_user_id VARCHAR(50),
customer_name VARCHAR(100),
channel VARCHAR(50), -- wechat/web/phone
source VARCHAR(50), -- miniprogram/h5/app
status VARCHAR(50) DEFAULT 'active',
start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
end_time TIMESTAMP,
satisfaction_score INTEGER -- 满意度评分
);
-- 对话消息表
CREATE TABLE customer_service.tb_cs_message (
message_id VARCHAR(50) PRIMARY KEY,
conv_id VARCHAR(50) NOT NULL,
sender_type VARCHAR(20), -- customer/agent/bot
sender_id VARCHAR(50),
content TEXT,
message_type VARCHAR(50), -- text/image/file
ai_response JSONB, -- AI响应详情
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_msg_conv FOREIGN KEY (conv_id)
REFERENCES customer_service.tb_cs_conversation(conv_id)
);
-- 工单表
CREATE TABLE customer_service.tb_cs_work_order (
order_id VARCHAR(50) PRIMARY KEY,
order_code VARCHAR(100) UNIQUE NOT NULL,
conv_id VARCHAR(50),
customer_user_id VARCHAR(50),
title VARCHAR(500) NOT NULL,
description TEXT,
category VARCHAR(100),
priority VARCHAR(20) DEFAULT 'medium',
status VARCHAR(50) DEFAULT 'pending',
assigned_to VARCHAR(50),
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
resolve_time TIMESTAMP
);
3.5 智能体管理模块 (agent)
核心表
-- 智能体定义表
CREATE TABLE agent.tb_agent_definition (
agent_id VARCHAR(50) PRIMARY KEY,
agent_name VARCHAR(100) NOT NULL,
agent_type VARCHAR(50), -- bidding/cs/knowledge等
description TEXT,
config JSONB, -- 配置(模型、参数等)
status VARCHAR(20) DEFAULT 'active',
version VARCHAR(20),
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- API调用日志表
CREATE TABLE agent.tb_agent_api_log (
log_id VARCHAR(50) PRIMARY KEY,
agent_id VARCHAR(50),
api_endpoint VARCHAR(200),
request_params JSONB,
response_data JSONB,
status_code INTEGER,
duration_ms INTEGER,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 智能体监控表(按月分区)
CREATE TABLE agent.tb_agent_monitor (
monitor_id VARCHAR(50) NOT NULL,
agent_id VARCHAR(50) NOT NULL,
metric_name VARCHAR(100),
metric_value NUMERIC,
timestamp TIMESTAMP NOT NULL,
PRIMARY KEY (monitor_id, timestamp)
) PARTITION BY RANGE (timestamp);
-- 创建分区
CREATE TABLE agent.tb_agent_monitor_2025_12
PARTITION OF agent.tb_agent_monitor
FOR VALUES FROM ('2025-12-01') TO ('2026-01-01');
4. 表结构速查
4.1 系统基础模块 (sys)
| 表名 | 主键 | 核心字段 | 说明 |
|---|---|---|---|
tb_sys_user |
user_id | username, email, phone | 用户表 |
tb_sys_user_info |
user_id | avatar, full_name, level | 用户信息表 |
tb_sys_dept |
dept_id | name, parent_id, dept_path | 部门表 |
tb_sys_role |
role_id | name, scope, owner_dept_id | 角色表 |
tb_sys_permission |
permission_id | name, code, module_id | 权限表 |
tb_sys_acl |
acl_id | object_type, principal_type | 对象权限表 |
4.2 知识库管理 (knowledge)
| 表名 | 主键 | 核心字段 | 说明 |
|---|---|---|---|
tb_knowledge_document |
doc_id | title, root_doc_id, version | 文档表 |
tb_knowledge_chunk |
chunk_id | doc_id, content, embedding | 分段表 |
tb_knowledge_qa_pair |
qa_pair_id | question, answer | 问答对表 |
4.3 招投标 (bidding)
| 表名 | 主键 | 核心字段 | 说明 |
|---|---|---|---|
tb_bidding_project |
project_id | project_name, status | 招标项目表 |
tb_bidding_document |
doc_id | project_id, parsed_content | 招标文件表 |
tb_bidding_template |
template_id | template_name, content | 标书模板表 |
4.4 智能客服 (customer_service)
| 表名 | 主键 | 核心字段 | 说明 |
|---|---|---|---|
tb_cs_conversation |
conv_id | customer_user_id, status | 对话会话表 |
tb_cs_message |
message_id | conv_id, content | 对话消息表 |
tb_cs_work_order |
order_id | title, status, assigned_to | 工单表 |
5. 优化方案
5.1 索引优化
-- 1. 复合索引(状态 + 时间查询)
CREATE INDEX idx_project_status_time
ON bidding.tb_bidding_project(status, create_time DESC)
WHERE deleted = false;
-- 2. 部分索引(只索引活跃数据)
CREATE INDEX idx_user_active
ON sys.tb_sys_user(user_id)
WHERE status = 'active' AND deleted = false;
-- 3. GIN索引(全文检索)
CREATE INDEX idx_doc_content_gin
ON knowledge.tb_knowledge_document
USING GIN(to_tsvector('chinese', title || ' ' || content));
-- 4. BRIN索引(时间序列)
CREATE INDEX idx_log_time_brin
ON log.tb_operation_log
USING BRIN(create_time);
5.2 分区策略
-- 按月分区(监控数据)
CREATE TABLE agent.tb_agent_monitor (
monitor_id VARCHAR(50) NOT NULL,
timestamp TIMESTAMP NOT NULL,
...
PRIMARY KEY (monitor_id, timestamp)
) PARTITION BY RANGE (timestamp);
-- 自动创建分区脚本
CREATE OR REPLACE FUNCTION create_monthly_partition()
RETURNS void AS $$
DECLARE
start_date DATE;
end_date DATE;
partition_name TEXT;
BEGIN
start_date := DATE_TRUNC('month', CURRENT_DATE + INTERVAL '1 month');
end_date := start_date + INTERVAL '1 month';
partition_name := 'tb_agent_monitor_' || TO_CHAR(start_date, 'YYYY_MM');
EXECUTE format(
'CREATE TABLE IF NOT EXISTS agent.%I PARTITION OF agent.tb_agent_monitor
FOR VALUES FROM (%L) TO (%L)',
partition_name, start_date, end_date
);
END;
$$ LANGUAGE plpgsql;
5.3 查询优化
-- 使用CTE优化复杂查询
WITH user_roles AS (
SELECT ur.user_id, r.role_id, r.role_code
FROM sys.tb_sys_user_role ur
JOIN sys.tb_sys_role r ON ur.role_id = r.role_id
WHERE r.deleted = false
),
user_permissions AS (
SELECT ur.user_id, p.permission_code
FROM user_roles ur
JOIN sys.tb_sys_role_permission rp ON ur.role_id = rp.role_id
JOIN sys.tb_sys_permission p ON rp.permission_id = p.permission_id
)
SELECT u.username, array_agg(DISTINCT up.permission_code) as permissions
FROM sys.tb_sys_user u
JOIN user_permissions up ON u.user_id = up.user_id
WHERE u.deleted = false
GROUP BY u.user_id, u.username;
5.4 并发控制
-- 乐观锁(版本号)
UPDATE knowledge.tb_knowledge_chunk
SET content = '新内容',
version = version + 1
WHERE chunk_id = 'xxx'
AND version = 1; -- 版本匹配才更新
-- 悲观锁
BEGIN;
SELECT * FROM bidding.tb_bidding_project
WHERE project_id = 'xxx'
FOR UPDATE; -- 锁定记录
-- 更新...
COMMIT;
6. 部署与维护
6.1 初始化脚本
# 位置: urbanLifelineServ/.bin/database/postgres/sql/
# 完整初始化
psql -U postgres -d urban_lifeline -f createTableAll.sql
# 分模块初始化
psql -U postgres -d urban_lifeline -f createTablePermission.sql
psql -U postgres -d urban_lifeline -f createTableKnowledge.sql
psql -U postgres -d urban_lifeline -f createTableBidding.sql
# 优化补丁
psql -U postgres -d urban_lifeline -f optimizations.sql
6.2 备份策略
# 全量备份
pg_dump -U postgres -d urban_lifeline -F c -f backup_$(date +%Y%m%d).dump
# 仅备份 Schema
pg_dump -U postgres -d urban_lifeline -s -f schema_backup.sql
# 仅备份数据
pg_dump -U postgres -d urban_lifeline -a -f data_backup.sql
# 还原
pg_restore -U postgres -d urban_lifeline backup_20251202.dump
6.3 监控指标
-- 表大小
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
-- 索引使用情况
SELECT
schemaname, tablename, indexname,
idx_scan as index_scans,
idx_tup_read as tuples_read
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY schemaname, tablename;
-- 慢查询
SELECT
query,
calls,
total_time,
mean_time,
max_time
FROM pg_stat_statements
WHERE mean_time > 1000 -- 超过1秒
ORDER BY mean_time DESC
LIMIT 10;
7. 常用查询示例
7.1 用户权限查询
-- 查询用户的所有权限
SELECT DISTINCT p.permission_code, p.permission_name
FROM sys.tb_sys_user u
JOIN sys.tb_sys_user_role ur ON u.user_id = ur.user_id
JOIN sys.tb_sys_role_permission rp ON ur.role_id = rp.role_id
JOIN sys.tb_sys_permission p ON rp.permission_id = p.permission_id
WHERE u.username = 'admin'
AND u.deleted = false;
-- 查询部门下所有用户
WITH RECURSIVE dept_tree AS (
SELECT dept_id FROM sys.tb_sys_dept WHERE dept_id = 'target_dept_id'
UNION ALL
SELECT d.dept_id
FROM sys.tb_sys_dept d
JOIN dept_tree dt ON d.parent_id = dt.dept_id
)
SELECT u.*
FROM sys.tb_sys_user u
JOIN sys.tb_sys_user_dept ud ON u.user_id = ud.user_id
WHERE ud.dept_id IN (SELECT dept_id FROM dept_tree);
7.2 知识库查询
-- 向量相似度检索(Top 5)
SELECT
chunk_id,
content,
1 - (embedding <=> '[0.1, 0.2, ...]'::vector) as similarity
FROM knowledge.tb_knowledge_chunk
WHERE deleted = false
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 5;
-- 查询文档的所有版本
SELECT
version,
title,
create_time,
is_current
FROM knowledge.tb_knowledge_document
WHERE root_doc_id = 'root_xxx'
ORDER BY version DESC;
7.3 招投标查询
-- 统计项目状态分布
SELECT
status,
COUNT(*) as count,
SUM(budget) as total_budget
FROM bidding.tb_bidding_project
WHERE deleted = false
GROUP BY status;
-- 查询即将截止的项目
SELECT
project_name,
bid_deadline,
EXTRACT(DAY FROM (bid_deadline - NOW())) as days_left
FROM bidding.tb_bidding_project
WHERE status = 'active'
AND bid_deadline > NOW()
AND deleted = false
ORDER BY bid_deadline ASC;
7.4 智能客服查询
-- 查询工单处理效率
SELECT
assigned_to,
COUNT(*) as total_orders,
AVG(EXTRACT(EPOCH FROM (resolve_time - create_time))/3600) as avg_hours,
COUNT(CASE WHEN status = 'resolved' THEN 1 END) as resolved_count
FROM customer_service.tb_cs_work_order
WHERE create_time > NOW() - INTERVAL '30 days'
GROUP BY assigned_to;
-- 查询满意度统计
SELECT
DATE(start_time) as date,
AVG(satisfaction_score) as avg_satisfaction,
COUNT(*) as total_conversations
FROM customer_service.tb_cs_conversation
WHERE satisfaction_score IS NOT NULL
AND start_time > NOW() - INTERVAL '7 days'
GROUP BY DATE(start_time)
ORDER BY date DESC;
附录
A. SQL文件清单
urbanLifelineServ/.bin/database/postgres/sql/
├── createTableAll.sql # 完整初始化
├── createTablePermission.sql # 权限模块
├── createTableUser.sql # 用户模块
├── createTableFile.sql # 文件模块
├── createTableMessage.sql # 消息模块
├── createTableLog.sql # 日志模块
├── createTableConfig.sql # 配置模块
├── createTableKnowledge.sql # 知识库模块 ✨
├── createTableBidding.sql # 招投标模块 ✨
├── createTableCustomerService.sql # 智能客服模块 ✨
├── createTableAgent.sql # 智能体管理模块 ✨
├── optimizations.sql # 优化补丁 ✨
├── initDataPermission.sql # 权限初始数据
├── initDataUser.sql # 用户初始数据
└── initAll.sql # 完整初始化(表+数据)
B. 扩展插件
-- 向量检索
CREATE EXTENSION IF NOT EXISTS vector;
-- 全文检索(中文)
CREATE EXTENSION IF NOT EXISTS zhparser;
CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);
-- 模糊匹配
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- UUID生成
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
C. 性能基准
| 指标 | 目标值 | 说明 |
|---|---|---|
| 单表查询 | < 100ms | 主键/索引查询 |
| 复杂查询 | < 500ms | 多表JOIN |
| 向量检索 | < 200ms | Top-K检索 |
| 写入TPS | > 1000 | 批量插入 |
| 并发连接 | 200+ | HikariCP连接池 |
最后更新: 2025-12-02
维护者: Urban Lifeline Team