You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
247 lines
11 KiB
247 lines
11 KiB
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
|
|
CREATE TABLE departments (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
name VARCHAR(100) NOT NULL,
|
|
parent_id UUID REFERENCES departments(id),
|
|
path VARCHAR(500) NOT NULL DEFAULT '/',
|
|
level INT NOT NULL DEFAULT 0,
|
|
sort_order INT DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE users (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
username VARCHAR(50) UNIQUE NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
display_name VARCHAR(100) NOT NULL,
|
|
email VARCHAR(100),
|
|
phone VARCHAR(20),
|
|
wecom_user_id VARCHAR(100) UNIQUE,
|
|
department_id UUID REFERENCES departments(id),
|
|
position VARCHAR(100),
|
|
manager_id UUID REFERENCES users(id),
|
|
status VARCHAR(20) DEFAULT 'active',
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE roles (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
name VARCHAR(50) UNIQUE NOT NULL,
|
|
code VARCHAR(50) UNIQUE NOT NULL,
|
|
description VARCHAR(200),
|
|
is_system BOOLEAN DEFAULT FALSE,
|
|
data_scope VARCHAR(50) DEFAULT 'self_only',
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE permissions (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
code VARCHAR(100) UNIQUE NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
resource VARCHAR(100) NOT NULL,
|
|
action VARCHAR(50) NOT NULL,
|
|
description VARCHAR(200)
|
|
);
|
|
|
|
CREATE TABLE role_permissions (
|
|
role_id UUID REFERENCES roles(id) ON DELETE CASCADE,
|
|
permission_id UUID REFERENCES permissions(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (role_id, permission_id)
|
|
);
|
|
|
|
CREATE TABLE user_roles (
|
|
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
role_id UUID REFERENCES roles(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (user_id, role_id)
|
|
);
|
|
|
|
CREATE TABLE chat_sessions (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
agent_type VARCHAR(50) NOT NULL,
|
|
session_id VARCHAR(100) UNIQUE NOT NULL,
|
|
status VARCHAR(20) DEFAULT 'active',
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE chat_messages (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
session_id UUID REFERENCES chat_sessions(id) ON DELETE CASCADE,
|
|
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
role VARCHAR(20) NOT NULL,
|
|
content TEXT NOT NULL,
|
|
metadata JSONB DEFAULT '{}',
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE tasks (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
title VARCHAR(200) NOT NULL,
|
|
content TEXT,
|
|
assigner_id UUID REFERENCES users(id),
|
|
assignee_id UUID REFERENCES users(id) NOT NULL,
|
|
status VARCHAR(20) DEFAULT 'pending',
|
|
priority VARCHAR(20) DEFAULT 'normal',
|
|
deadline TIMESTAMP,
|
|
wecom_message_id VARCHAR(100),
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE flow_definitions (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
name VARCHAR(200) NOT NULL,
|
|
description TEXT,
|
|
version INT DEFAULT 1,
|
|
status VARCHAR(20) DEFAULT 'draft',
|
|
definition_json JSONB NOT NULL,
|
|
creator_id UUID REFERENCES users(id),
|
|
published_to_wecom BOOLEAN DEFAULT FALSE,
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE flow_executions (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
flow_id UUID REFERENCES flow_definitions(id) ON DELETE CASCADE,
|
|
trigger_type VARCHAR(50),
|
|
trigger_user_id UUID REFERENCES users(id),
|
|
input_data JSONB,
|
|
output_data JSONB,
|
|
status VARCHAR(20) DEFAULT 'running',
|
|
started_at TIMESTAMP DEFAULT NOW(),
|
|
finished_at TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE audit_logs (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
operator_id UUID REFERENCES users(id),
|
|
action VARCHAR(100) NOT NULL,
|
|
resource VARCHAR(100),
|
|
resource_id VARCHAR(100),
|
|
detail JSONB DEFAULT '{}',
|
|
ip_address VARCHAR(50),
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
-- ============================================================
|
|
-- 种子数据
|
|
-- ============================================================
|
|
|
|
INSERT INTO departments (id, name, path, level, sort_order) VALUES
|
|
('00000000-0000-0000-0000-000000000001', '公司', '/公司', 0, 0),
|
|
('00000000-0000-0000-0000-000000000002', '技术部', '/公司/技术部', 1, 1),
|
|
('00000000-0000-0000-0000-000000000003', '市场部', '/公司/市场部', 1, 2),
|
|
('00000000-0000-0000-0000-000000000004', '人事部', '/公司/人事部', 1, 3);
|
|
|
|
INSERT INTO roles (id, name, code, description, is_system, data_scope) VALUES
|
|
('10000000-0000-0000-0000-000000000000', '系统根账号', 'root', '无条件超级权限', TRUE, 'all'),
|
|
('10000000-0000-0000-0000-000000000001', '超级管理员', 'super_admin', '全部功能权限', TRUE, 'all'),
|
|
('10000000-0000-0000-0000-000000000002', '部门经理', 'dept_manager', '部门管理权限', TRUE, 'subordinate_only'),
|
|
('10000000-0000-0000-0000-000000000003', '组长', 'team_lead', '组管理权限', TRUE, 'subordinate_only'),
|
|
('10000000-0000-0000-0000-000000000004', '普通员工', 'employee', '基础权限', TRUE, 'self_only'),
|
|
('10000000-0000-0000-0000-000000000005', '工作流编辑', 'workflow_editor', '流编排权限', TRUE, 'self_only');
|
|
|
|
INSERT INTO permissions (id, code, name, resource, action) VALUES
|
|
('20000000-0000-0000-0000-000000000000', '*:*', '全部权限', '*', '*'),
|
|
('20000000-0000-0000-0000-000000000001', 'user:create', '创建用户', 'user', 'create'),
|
|
('20000000-0000-0000-0000-000000000002', 'user:read', '查看用户', 'user', 'read'),
|
|
('20000000-0000-0000-0000-000000000003', 'user:update', '更新用户', 'user', 'update'),
|
|
('20000000-0000-0000-0000-000000000004', 'user:delete', '删除用户', 'user', 'delete'),
|
|
('20000000-0000-0000-0000-000000000005', 'dept:read', '查看部门', 'department', 'read'),
|
|
('20000000-0000-0000-0000-000000000006', 'dept:create', '创建部门', 'department', 'create'),
|
|
('20000000-0000-0000-0000-000000000007', 'role:read', '查看角色', 'role', 'read'),
|
|
('20000000-0000-0000-0000-000000000008', 'role:update', '更新角色权限', 'role', 'update'),
|
|
('20000000-0000-0000-0000-000000000009', 'monitor:read', '查看工作监控', 'monitor', 'read'),
|
|
('20000000-0000-0000-0000-000000000010', 'analysis:read', '查看AI分析报告', 'analysis', 'read'),
|
|
('20000000-0000-0000-0000-000000000011', 'task:create', '创建任务', 'task', 'create'),
|
|
('20000000-0000-0000-0000-000000000012', 'task:read', '查看任务', 'task', 'read'),
|
|
('20000000-0000-0000-0000-000000000013', 'flow:create', '创建流', 'flow', 'create'),
|
|
('20000000-0000-0000-0000-000000000014', 'flow:update', '更新流', 'flow', 'update'),
|
|
('20000000-0000-0000-0000-000000000015', 'flow:read', '查看流', 'flow', 'read'),
|
|
('20000000-0000-0000-0000-000000000016', 'flow:publish', '上架流', 'flow', 'publish'),
|
|
('20000000-0000-0000-0000-000000000017', 'audit:read', '查看审计日志', 'audit', 'read'),
|
|
('20000000-0000-0000-0000-000000000018', 'self:read', '查看个人信息', 'self', 'read');
|
|
|
|
-- root: *:* wildcard permission
|
|
INSERT INTO role_permissions (role_id, permission_id) VALUES
|
|
('10000000-0000-0000-0000-000000000000', '20000000-0000-0000-0000-000000000000');
|
|
|
|
-- super_admin: all permissions
|
|
INSERT INTO role_permissions (role_id, permission_id)
|
|
SELECT '10000000-0000-0000-0000-000000000001', id FROM permissions;
|
|
|
|
-- dept_manager
|
|
INSERT INTO role_permissions (role_id, permission_id) VALUES
|
|
('10000000-0000-0000-0000-000000000002', '20000000-0000-0000-0000-000000000009'),
|
|
('10000000-0000-0000-0000-000000000002', '20000000-0000-0000-0000-000000000010'),
|
|
('10000000-0000-0000-0000-000000000002', '20000000-0000-0000-0000-000000000011'),
|
|
('10000000-0000-0000-0000-000000000002', '20000000-0000-0000-0000-000000000012');
|
|
|
|
-- team_lead
|
|
INSERT INTO role_permissions (role_id, permission_id) VALUES
|
|
('10000000-0000-0000-0000-000000000003', '20000000-0000-0000-0000-000000000009'),
|
|
('10000000-0000-0000-0000-000000000003', '20000000-0000-0000-0000-000000000011'),
|
|
('10000000-0000-0000-0000-000000000003', '20000000-0000-0000-0000-000000000012');
|
|
|
|
-- employee
|
|
INSERT INTO role_permissions (role_id, permission_id) VALUES
|
|
('10000000-0000-0000-0000-000000000004', '20000000-0000-0000-0000-000000000018'),
|
|
('10000000-0000-0000-0000-000000000004', '20000000-0000-0000-0000-000000000012');
|
|
|
|
-- workflow_editor
|
|
INSERT INTO role_permissions (role_id, permission_id) VALUES
|
|
('10000000-0000-0000-0000-000000000005', '20000000-0000-0000-0000-000000000013'),
|
|
('10000000-0000-0000-0000-000000000005', '20000000-0000-0000-0000-000000000014'),
|
|
('10000000-0000-0000-0000-000000000005', '20000000-0000-0000-0000-000000000015'),
|
|
('10000000-0000-0000-0000-000000000005', '20000000-0000-0000-0000-000000000016');
|
|
|
|
-- 默认用户 (密码: admin123)
|
|
INSERT INTO users (id, username, password_hash, display_name, department_id, position, status) VALUES
|
|
('30000000-0000-0000-0000-000000000000', 'sroot', '$2b$12$0G/3v9vN3aJP3eGoqEse/uqyNxj6iigyGkUnZyndRN4ZURo9lDm/2', '系统根账号', '00000000-0000-0000-0000-000000000001', '超级管理员', 'active'),
|
|
('30000000-0000-0000-0000-000000000001', 'admin', '$2b$12$0G/3v9vN3aJP3eGoqEse/uqyNxj6iigyGkUnZyndRN4ZURo9lDm/2', '系统管理员', '00000000-0000-0000-0000-000000000001', '管理员', 'active');
|
|
|
|
INSERT INTO user_roles (user_id, role_id) VALUES
|
|
('30000000-0000-0000-0000-000000000000', '10000000-0000-0000-0000-000000000000'),
|
|
('30000000-0000-0000-0000-000000000001', '10000000-0000-0000-0000-000000000001');
|
|
|
|
-- MCP 服务注册表
|
|
CREATE TABLE IF NOT EXISTS mcp_services (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name VARCHAR(100) UNIQUE NOT NULL,
|
|
transport VARCHAR(20) DEFAULT 'http',
|
|
url VARCHAR(500),
|
|
command VARCHAR(500),
|
|
args JSONB DEFAULT '[]',
|
|
env JSONB DEFAULT '{}',
|
|
status VARCHAR(20) DEFAULT 'disconnected',
|
|
tools JSONB DEFAULT '[]',
|
|
creator_id UUID REFERENCES users(id),
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
-- 通知模板表
|
|
CREATE TABLE IF NOT EXISTS notification_templates (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name VARCHAR(100) NOT NULL,
|
|
code VARCHAR(100) UNIQUE NOT NULL,
|
|
channel VARCHAR(20) DEFAULT 'wecom',
|
|
title_template VARCHAR(500),
|
|
body_template TEXT NOT NULL,
|
|
variables JSONB DEFAULT '[]',
|
|
is_system BOOLEAN DEFAULT FALSE,
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
-- 系统指标表
|
|
CREATE TABLE IF NOT EXISTS system_metrics (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
metric_type VARCHAR(50) NOT NULL,
|
|
value JSONB NOT NULL,
|
|
collected_at TIMESTAMP DEFAULT NOW()
|
|
);
|