211 lines
6.6 KiB
SQL
211 lines
6.6 KiB
SQL
|
-- =============================================
|
||
|
|
-- 智慧水务管理系统 - PostgreSQL DDL
|
||
|
|
-- 版本: V1
|
||
|
|
-- 描述: 核心业务表建表脚本
|
||
|
|
-- =============================================
|
||
|
|
|
||
|
|
-- ==================== 系统管理 ====================
|
||
|
|
|
||
|
|
-- 部门表
|
||
|
|
CREATE TABLE IF NOT EXISTS sys_dept (
|
||
|
|
id BIGSERIAL PRIMARY KEY,
|
||
|
|
parent_id BIGINT,
|
||
|
|
dept_name VARCHAR(100) NOT NULL,
|
||
|
|
dept_type VARCHAR(20) NOT NULL DEFAULT 'water_company', -- water_bureau/water_company/ops
|
||
|
|
sort_order INT DEFAULT 0,
|
||
|
|
leader VARCHAR(50),
|
||
|
|
phone VARCHAR(20),
|
||
|
|
status SMALLINT DEFAULT 1,
|
||
|
|
deleted SMALLINT DEFAULT 0,
|
||
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
||
|
|
);
|
||
|
|
COMMENT ON TABLE sys_dept IS '部门表';
|
||
|
|
COMMENT ON COLUMN sys_dept.dept_type IS '部门类型: water_bureau(水利局)/water_company(水务公司)/ops(运维单位)';
|
||
|
|
|
||
|
|
-- 用户表
|
||
|
|
CREATE TABLE IF NOT EXISTS sys_user (
|
||
|
|
id BIGSERIAL PRIMARY KEY,
|
||
|
|
dept_id BIGINT REFERENCES sys_dept(id),
|
||
|
|
username VARCHAR(50) UNIQUE NOT NULL,
|
||
|
|
password VARCHAR(255) NOT NULL,
|
||
|
|
real_name VARCHAR(50),
|
||
|
|
nickname VARCHAR(50),
|
||
|
|
phone VARCHAR(20),
|
||
|
|
email VARCHAR(100),
|
||
|
|
avatar VARCHAR(500),
|
||
|
|
gender SMALLINT DEFAULT 0, -- 0:未知 1:男 2:女
|
||
|
|
role_type VARCHAR(30) DEFAULT 'operator', -- admin/leader/manager/operator/tech
|
||
|
|
status SMALLINT DEFAULT 1, -- 0:停用 1:启用
|
||
|
|
login_ip VARCHAR(50),
|
||
|
|
login_at TIMESTAMP,
|
||
|
|
deleted SMALLINT DEFAULT 0,
|
||
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
||
|
|
);
|
||
|
|
COMMENT ON TABLE sys_user IS '用户表';
|
||
|
|
COMMENT ON COLUMN sys_user.role_type IS '角色类型: admin/leader/manager/operator/tech';
|
||
|
|
|
||
|
|
-- 角色表
|
||
|
|
CREATE TABLE IF NOT EXISTS sys_role (
|
||
|
|
id BIGSERIAL PRIMARY KEY,
|
||
|
|
role_name VARCHAR(50) UNIQUE NOT NULL,
|
||
|
|
role_key VARCHAR(50) UNIQUE NOT NULL,
|
||
|
|
role_sort INT DEFAULT 0,
|
||
|
|
data_scope VARCHAR(20) DEFAULT 'SELF', -- ALL/DEPT/CUSTOM/SELF
|
||
|
|
status SMALLINT DEFAULT 1,
|
||
|
|
remark VARCHAR(500),
|
||
|
|
deleted SMALLINT DEFAULT 0,
|
||
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
||
|
|
);
|
||
|
|
COMMENT ON TABLE sys_role IS '角色表';
|
||
|
|
COMMENT ON COLUMN sys_role.role_key IS '角色标识: admin/supervisor/biz_manager/field_ops/tech_maintain';
|
||
|
|
COMMENT ON COLUMN sys_role.data_scope IS '数据权限范围: ALL/DEPT/CUSTOM/SELF';
|
||
|
|
|
||
|
|
-- 菜单表
|
||
|
|
CREATE TABLE IF NOT EXISTS sys_menu (
|
||
|
|
id BIGSERIAL PRIMARY KEY,
|
||
|
|
parent_id BIGINT DEFAULT 0,
|
||
|
|
menu_name VARCHAR(50) NOT NULL,
|
||
|
|
menu_type CHAR(1) DEFAULT 'M', -- M:目录 C:菜单 F:按钮
|
||
|
|
path VARCHAR(200),
|
||
|
|
component VARCHAR(255),
|
||
|
|
perms VARCHAR(100),
|
||
|
|
icon VARCHAR(100),
|
||
|
|
sort_order INT DEFAULT 0,
|
||
|
|
visible SMALLINT DEFAULT 1,
|
||
|
|
status SMALLINT DEFAULT 1,
|
||
|
|
deleted SMALLINT DEFAULT 0,
|
||
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
||
|
|
);
|
||
|
|
COMMENT ON TABLE sys_menu IS '菜单表';
|
||
|
|
|
||
|
|
-- 角色-菜单关联
|
||
|
|
CREATE TABLE IF NOT EXISTS sys_role_menu (
|
||
|
|
role_id BIGINT NOT NULL REFERENCES sys_role(id),
|
||
|
|
menu_id BIGINT NOT NULL REFERENCES sys_menu(id),
|
||
|
|
PRIMARY KEY (role_id, menu_id)
|
||
|
|
);
|
||
|
|
COMMENT ON TABLE sys_role_menu IS '角色菜单关联表';
|
||
|
|
|
||
|
|
-- 用户-角色关联
|
||
|
|
CREATE TABLE IF NOT EXISTS sys_user_role (
|
||
|
|
user_id BIGINT NOT NULL REFERENCES sys_user(id),
|
||
|
|
role_id BIGINT NOT NULL REFERENCES sys_role(id),
|
||
|
|
PRIMARY KEY (user_id, role_id)
|
||
|
|
);
|
||
|
|
COMMENT ON TABLE sys_user_role IS '用户角色关联表';
|
||
|
|
|
||
|
|
-- 操作日志表
|
||
|
|
CREATE TABLE IF NOT EXISTS sys_oper_log (
|
||
|
|
id BIGSERIAL PRIMARY KEY,
|
||
|
|
user_id BIGINT,
|
||
|
|
username VARCHAR(50),
|
||
|
|
module VARCHAR(50),
|
||
|
|
operation VARCHAR(50),
|
||
|
|
method VARCHAR(200),
|
||
|
|
request_method VARCHAR(10),
|
||
|
|
request_url VARCHAR(500),
|
||
|
|
request_params TEXT,
|
||
|
|
response_result TEXT,
|
||
|
|
ip VARCHAR(50),
|
||
|
|
location VARCHAR(100),
|
||
|
|
duration BIGINT,
|
||
|
|
status SMALLINT DEFAULT 1,
|
||
|
|
error_msg TEXT,
|
||
|
|
created_at TIMESTAMP DEFAULT NOW()
|
||
|
|
);
|
||
|
|
COMMENT ON TABLE sys_oper_log IS '操作日志表';
|
||
|
|
|
||
|
|
-- 登录日志表
|
||
|
|
CREATE TABLE IF NOT EXISTS sys_login_log (
|
||
|
|
id BIGSERIAL PRIMARY KEY,
|
||
|
|
username VARCHAR(50),
|
||
|
|
ip VARCHAR(50),
|
||
|
|
location VARCHAR(100),
|
||
|
|
browser VARCHAR(100),
|
||
|
|
os VARCHAR(100),
|
||
|
|
status SMALLINT DEFAULT 1, -- 0:失败 1:成功
|
||
|
|
msg VARCHAR(500),
|
||
|
|
login_at TIMESTAMP DEFAULT NOW()
|
||
|
|
);
|
||
|
|
COMMENT ON TABLE sys_login_log IS '登录日志表';
|
||
|
|
|
||
|
|
-- ==================== 数据字典 ====================
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS sys_dict_type (
|
||
|
|
id BIGSERIAL PRIMARY KEY,
|
||
|
|
dict_name VARCHAR(100) NOT NULL,
|
||
|
|
dict_key VARCHAR(100) UNIQUE NOT NULL,
|
||
|
|
status SMALLINT DEFAULT 1,
|
||
|
|
remark VARCHAR(500),
|
||
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
||
|
|
);
|
||
|
|
COMMENT ON TABLE sys_dict_type IS '字典类型表';
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS sys_dict_data (
|
||
|
|
id BIGSERIAL PRIMARY KEY,
|
||
|
|
dict_type_id BIGINT REFERENCES sys_dict_type(id),
|
||
|
|
dict_label VARCHAR(100) NOT NULL,
|
||
|
|
dict_value VARCHAR(100) NOT NULL,
|
||
|
|
css_class VARCHAR(100),
|
||
|
|
list_class VARCHAR(100),
|
||
|
|
sort_order INT DEFAULT 0,
|
||
|
|
status SMALLINT DEFAULT 1,
|
||
|
|
remark VARCHAR(500),
|
||
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
||
|
|
);
|
||
|
|
COMMENT ON TABLE sys_dict_data IS '字典数据表';
|
||
|
|
|
||
|
|
-- ==================== 通知方案 ====================
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS sys_notify_scheme (
|
||
|
|
id BIGSERIAL PRIMARY KEY,
|
||
|
|
scheme_name VARCHAR(100) NOT NULL,
|
||
|
|
channels VARCHAR(200), -- sms,app_push,websocket,wechat (逗号分隔)
|
||
|
|
template_id VARCHAR(50),
|
||
|
|
status SMALLINT DEFAULT 1,
|
||
|
|
created_at TIMESTAMP DEFAULT NOW()
|
||
|
|
);
|
||
|
|
COMMENT ON TABLE sys_notify_scheme IS '通知方案表';
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS sys_notify_record (
|
||
|
|
id BIGSERIAL PRIMARY KEY,
|
||
|
|
scheme_id BIGINT REFERENCES sys_notify_scheme(id),
|
||
|
|
target_user_id BIGINT,
|
||
|
|
target_phone VARCHAR(20),
|
||
|
|
channel VARCHAR(20),
|
||
|
|
title VARCHAR(200),
|
||
|
|
content TEXT,
|
||
|
|
status VARCHAR(20) DEFAULT 'pending', -- pending/sent/failed/read
|
||
|
|
sent_at TIMESTAMP,
|
||
|
|
read_at TIMESTAMP,
|
||
|
|
error_msg VARCHAR(500),
|
||
|
|
created_at TIMESTAMP DEFAULT NOW()
|
||
|
|
);
|
||
|
|
COMMENT ON TABLE sys_notify_record IS '通知记录表';
|
||
|
|
|
||
|
|
-- ==================== 文件管理 ====================
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS sys_file (
|
||
|
|
id BIGSERIAL PRIMARY KEY,
|
||
|
|
file_name VARCHAR(200) NOT NULL,
|
||
|
|
original_name VARCHAR(200),
|
||
|
|
file_path VARCHAR(500) NOT NULL,
|
||
|
|
file_size BIGINT,
|
||
|
|
mime_type VARCHAR(100),
|
||
|
|
file_ext VARCHAR(20),
|
||
|
|
storage_type VARCHAR(20) DEFAULT 'minio',
|
||
|
|
bucket VARCHAR(100) DEFAULT 'water-management',
|
||
|
|
module VARCHAR(50),
|
||
|
|
biz_id BIGINT,
|
||
|
|
upload_by BIGINT,
|
||
|
|
deleted SMALLINT DEFAULT 0,
|
||
|
|
created_at TIMESTAMP DEFAULT NOW()
|
||
|
|
);
|
||
|
|
COMMENT ON TABLE sys_file IS '文件管理表';
|