-- PostgreSQL Database Schema for iMeeting (Multi-tenant) -- 0 为系统预留租户 ID -- ---------------------------- -- 0. 租户与组织 -- ---------------------------- -- 租户表 CREATE TABLE sys_tenant ( id BIGSERIAL PRIMARY KEY, tenant_code VARCHAR(64) NOT NULL UNIQUE, tenant_name VARCHAR(128) NOT NULL, status SMALLINT NOT NULL DEFAULT 1, expire_time TIMESTAMP(6), contact_name VARCHAR(64), contact_phone VARCHAR(32), remark VARCHAR(255), created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP, is_deleted SMALLINT DEFAULT 0 ); CREATE UNIQUE INDEX uk_tenant_code ON sys_tenant (tenant_code) WHERE is_deleted = 0; -- 组织架构表 DROP TABLE IF EXISTS sys_org CASCADE; CREATE TABLE sys_org ( id BIGSERIAL PRIMARY KEY, tenant_id BIGINT NOT NULL, parent_id BIGINT, org_name VARCHAR(128) NOT NULL, org_code VARCHAR(64), org_path VARCHAR(512), sort_order INTEGER DEFAULT 0, status SMALLINT DEFAULT 1, created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP, is_deleted SMALLINT DEFAULT 0, CONSTRAINT fk_org_parent FOREIGN KEY (parent_id) REFERENCES sys_org(id), CONSTRAINT fk_org_tenant FOREIGN KEY (tenant_id) REFERENCES sys_tenant(id) ); CREATE INDEX idx_org_tenant ON sys_org (tenant_id); -- ---------------------------- -- 1. 用户与角色 -- ---------------------------- -- 用户表 DROP TABLE IF EXISTS sys_user CASCADE; CREATE TABLE sys_user ( user_id BIGSERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, display_name VARCHAR(50) NOT NULL, email VARCHAR(100), phone VARCHAR(30) UNIQUE, password_hash VARCHAR(255) NOT NULL, status SMALLINT NOT NULL DEFAULT 1, is_deleted SMALLINT NOT NULL DEFAULT 0, pwd_reset_required SMALLINT DEFAULT 1, created_at TIMESTAMP(6) NOT NULL DEFAULT now(), updated_at TIMESTAMP(6) NOT NULL DEFAULT now(), is_platform_admin BOOLEAN DEFAULT false ); CREATE INDEX uk_user_username ON sys_user (username) WHERE is_deleted = 0; -- 角色表 DROP TABLE IF EXISTS sys_role CASCADE; CREATE TABLE sys_role ( role_id BIGSERIAL PRIMARY KEY, tenant_id BIGINT NOT NULL, role_code VARCHAR(50) NOT NULL, role_name VARCHAR(50) NOT NULL, status SMALLINT NOT NULL DEFAULT 1, remark TEXT, is_deleted SMALLINT NOT NULL DEFAULT 0, created_at TIMESTAMP(6) NOT NULL DEFAULT now(), updated_at TIMESTAMP(6) NOT NULL DEFAULT now() ); CREATE INDEX idx_sys_role_tenant ON sys_role (tenant_id); CREATE UNIQUE INDEX uk_role_code ON sys_role (tenant_id, role_code) WHERE is_deleted = 0; -- 用户-角色关联表 (按 tenant_id 强约束，避免跨租户角色污染) DROP TABLE IF EXISTS sys_user_role CASCADE; CREATE TABLE sys_user_role ( id BIGSERIAL PRIMARY KEY, tenant_id BIGINT NOT NULL, user_id BIGINT NOT NULL, role_id BIGINT NOT NULL, is_deleted SMALLINT NOT NULL DEFAULT 0, created_at TIMESTAMP(6) NOT NULL DEFAULT now(), updated_at TIMESTAMP(6) NOT NULL DEFAULT now(), UNIQUE (tenant_id, user_id, role_id) ); -- ---------------------------- -- 2. 权限/字典/参数 (全局共享, 无 tenant_id) -- ---------------------------- DROP TABLE IF EXISTS sys_permission CASCADE; CREATE TABLE sys_permission ( perm_id BIGSERIAL PRIMARY KEY, parent_id BIGINT, name VARCHAR(100) NOT NULL, code VARCHAR(100) NOT NULL UNIQUE, perm_type VARCHAR(20) NOT NULL, level INTEGER NOT NULL, path VARCHAR(255), component VARCHAR(255), icon VARCHAR(100), sort_order INTEGER NOT NULL DEFAULT 0, is_visible SMALLINT NOT NULL DEFAULT 1, status SMALLINT NOT NULL DEFAULT 1, description TEXT, meta JSONB, is_deleted SMALLINT NOT NULL DEFAULT 0, created_at TIMESTAMP(6) NOT NULL DEFAULT now(), updated_at TIMESTAMP(6) NOT NULL DEFAULT now() ); DROP TABLE IF EXISTS sys_tenant_user CASCADE; CREATE TABLE sys_tenant_user ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL, tenant_id BIGINT NOT NULL, org_id BIGINT, status SMALLINT DEFAULT 1, is_deleted SMALLINT DEFAULT 0, created_at TIMESTAMP(6) NOT NULL DEFAULT now(), updated_at TIMESTAMP(6) NOT NULL DEFAULT now() ); CREATE UNIQUE INDEX uk_tenant_user ON sys_tenant_user (user_id, tenant_id) WHERE is_deleted = 0; CREATE TABLE sys_dict_type ( dict_type_id BIGSERIAL PRIMARY KEY, type_code VARCHAR(50) UNIQUE NOT NULL, type_name VARCHAR(50) NOT NULL, status SMALLINT DEFAULT 1, remark TEXT, created_at TIMESTAMP NOT NULL DEFAULT NOW(), updated_at TIMESTAMP NOT NULL DEFAULT NOW(), is_deleted SMALLINT DEFAULT 0 ); CREATE TABLE sys_dict_item ( dict_item_id BIGSERIAL PRIMARY KEY, type_code VARCHAR(50) NOT NULL, item_label VARCHAR(100) NOT NULL, item_value VARCHAR(100) NOT NULL, sort_order INT DEFAULT 0, status SMALLINT DEFAULT 1, created_at TIMESTAMP NOT NULL DEFAULT NOW(), updated_at TIMESTAMP NOT NULL DEFAULT NOW(), is_deleted SMALLINT DEFAULT 0 ); CREATE INDEX idx_dict_item_type ON sys_dict_item (type_code); CREATE UNIQUE INDEX uk_dict_item_value ON sys_dict_item (type_code, item_value); CREATE TABLE sys_param ( id BIGSERIAL PRIMARY KEY, param_key VARCHAR(100) UNIQUE NOT NULL, param_value TEXT NOT NULL, param_type VARCHAR(20) NOT NULL, is_system SMALLINT DEFAULT 0, status SMALLINT DEFAULT 1, description TEXT, created_at TIMESTAMP NOT NULL DEFAULT NOW(), updated_at TIMESTAMP NOT NULL DEFAULT NOW(), is_deleted SMALLINT DEFAULT 0 ); -- ---------------------------- -- 3. 日志 (租户隔离) -- ---------------------------- CREATE TABLE sys_log ( id BIGSERIAL PRIMARY KEY, tenant_id BIGINT NOT NULL DEFAULT 0, user_id BIGINT, username VARCHAR(50), log_type VARCHAR(20), -- LOGIN, OPERATION operation VARCHAR(100) NOT NULL, method VARCHAR(200), params TEXT, status SMALLINT DEFAULT 1, ip VARCHAR(50), duration BIGINT, created_at TIMESTAMP NOT NULL DEFAULT NOW() ); CREATE INDEX idx_log_tenant_type ON sys_log (tenant_id, log_type, created_at); -- ---------------------------- -- 4. 平台配置 (系统品牌化) -- ---------------------------- DROP TABLE IF EXISTS sys_platform_config CASCADE; CREATE TABLE sys_platform_config ( id BIGINT PRIMARY KEY, -- 固定为 1 project_name VARCHAR(128) NOT NULL, logo_url VARCHAR(512), icon_url VARCHAR(512), login_bg_url VARCHAR(512), icp_info VARCHAR(128), copyright_info VARCHAR(255), system_description TEXT, created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP, is_deleted SMALLINT DEFAULT 0 ); INSERT INTO sys_platform_config (id, project_name, copyright_info) VALUES (1, 'iMeeting 智能会议系统', '© 2026 iMeeting Team. All rights reserved.'); -- ---------------------------- -- 5. 基础初始化数据 -- ---------------------------- -- 字典初始化数据 -- sys_common_status INSERT INTO sys_dict_type (type_code, type_name, remark) VALUES ('sys_common_status', '通用状态', '0=禁用, 1=启用'); INSERT INTO sys_dict_item (type_code, item_label, item_value, sort_order) VALUES ('sys_common_status', '启用', '1', 1); INSERT INTO sys_dict_item (type_code, item_label, item_value, sort_order) VALUES ('sys_common_status', '禁用', '0', 2); -- sys_permission_type INSERT INTO sys_dict_type (type_code, type_name, remark) VALUES ('sys_permission_type', '权限类型', 'directory=目录, menu=菜单, button=按钮'); INSERT INTO sys_dict_item (type_code, item_label, item_value, sort_order) VALUES ('sys_permission_type', '目录', 'directory', 1); INSERT INTO sys_dict_item (type_code, item_label, item_value, sort_order) VALUES ('sys_permission_type', '菜单', 'menu', 2); INSERT INTO sys_dict_item (type_code, item_label, item_value, sort_order) VALUES ('sys_permission_type', '按钮', 'button', 3); -- sys_common_visibility INSERT INTO sys_dict_type (type_code, type_name, remark) VALUES ('sys_common_visibility', '可见性', '0=隐藏, 1=显示'); INSERT INTO sys_dict_item (type_code, item_label, item_value, sort_order) VALUES ('sys_common_visibility', '显示', '1', 1); INSERT INTO sys_dict_item (type_code, item_label, item_value, sort_order) VALUES ('sys_common_visibility', '隐藏', '0', 2); -- sys_permission_level INSERT INTO sys_dict_type (type_code, type_name, remark) VALUES ('sys_permission_level', '权限层级', '1=一级入口, 2=二级子项, 3=三级按钮'); INSERT INTO sys_dict_item (type_code, item_label, item_value, sort_order) VALUES ('sys_permission_level', '一级入口', '1', 1); INSERT INTO sys_dict_item (type_code, item_label, item_value, sort_order) VALUES ('sys_permission_level', '二级子项', '2', 2); INSERT INTO sys_dict_item (type_code, item_label, item_value, sort_order) VALUES ('sys_permission_level', '三级按钮', '3', 3); -- sys_log_type INSERT INTO sys_dict_type (type_code, type_name, remark) VALUES ('sys_log_type', '日志类型', 'LOGIN=登录, OPERATION=操作'); INSERT INTO sys_dict_item (type_code, item_label, item_value, sort_order) VALUES ('sys_log_type', '登录', 'LOGIN', 1); INSERT INTO sys_dict_item (type_code, item_label, item_value, sort_order) VALUES ('sys_log_type', '操作', 'OPERATION', 2); -- sys_param_type INSERT INTO sys_dict_type (type_code, type_name, remark) VALUES ('sys_param_type', '参数类型', 'String, Number, Boolean, JSON'); INSERT INTO sys_dict_item (type_code, item_label, item_value, sort_order) VALUES ('sys_param_type', 'String', 'String', 1); INSERT INTO sys_dict_item (type_code, item_label, item_value, sort_order) VALUES ('sys_param_type', 'Number', 'Number', 2); INSERT INTO sys_dict_item (type_code, item_label, item_value, sort_order) VALUES ('sys_param_type', 'Boolean', 'Boolean', 3); INSERT INTO sys_dict_item (type_code, item_label, item_value, sort_order) VALUES ('sys_param_type', 'JSON', 'JSON', 4); -- sys_log_status INSERT INTO sys_dict_type (type_code, type_name, remark) VALUES ('sys_log_status', '操作状态', '1=成功, 0=失败'); INSERT INTO sys_dict_item (type_code, item_label, item_value, sort_order) VALUES ('sys_log_status', '成功', '1', 1); INSERT INTO sys_dict_item (type_code, item_label, item_value, sort_order) VALUES ('sys_log_status', '失败', '0', 2);