24 lines
1007 B
SQL
24 lines
1007 B
SQL
-- RBAC权限扩展 - user_project表
|
|
-- Date: 2026-03-21
|
|
-- Purpose: 支持用户多项目参与
|
|
|
|
-- 创建用户-项目关联表
|
|
CREATE TABLE IF NOT EXISTS user_project (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL,
|
|
project_id UUID NOT NULL,
|
|
role_in_project VARCHAR(50) NOT NULL DEFAULT 'member',
|
|
joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT uk_user_project UNIQUE(user_id, project_id),
|
|
CONSTRAINT fk_up_user FOREIGN KEY (user_id) REFERENCES auth_user(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_up_project FOREIGN KEY (project_id) REFERENCES mdm_project(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- 创建索引
|
|
CREATE INDEX IF NOT EXISTS idx_up_user ON user_project(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_up_project ON user_project(project_id);
|
|
|
|
-- 注释
|
|
COMMENT ON TABLE user_project IS '用户-项目关联表，支持用户参与多个项目';
|
|
COMMENT ON COLUMN user_project.role_in_project IS '在项目中的角色: leader/member/viewer';
|