freedakgmail
80 lines
2.9 KiB
PL/PgSQL
80 lines
2.9 KiB
PL/PgSQL
-- 000001: 用户表与部门表
|
|
|
|
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
|
|
|
|
-- 用户表
|
|
CREATE TABLE users (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
employee_id VARCHAR(50) UNIQUE,
|
|
name VARCHAR(100) NOT NULL,
|
|
email VARCHAR(255) UNIQUE NOT NULL,
|
|
password_hash VARCHAR(255),
|
|
phone VARCHAR(20),
|
|
avatar_url TEXT,
|
|
role VARCHAR(20) NOT NULL DEFAULT 'user'
|
|
CHECK (role IN ('super_admin', 'admin', 'creator', 'user')),
|
|
status VARCHAR(20) NOT NULL DEFAULT 'active'
|
|
CHECK (status IN ('active', 'disabled', 'pending')),
|
|
sso_provider VARCHAR(50),
|
|
sso_external_id VARCHAR(255),
|
|
last_login_at TIMESTAMPTZ,
|
|
login_count INTEGER NOT NULL DEFAULT 0,
|
|
preferences JSONB NOT NULL DEFAULT '{}',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_users_email ON users(email);
|
|
CREATE INDEX idx_users_employee_id ON users(employee_id);
|
|
CREATE INDEX idx_users_role ON users(role);
|
|
CREATE INDEX idx_users_status ON users(status);
|
|
|
|
-- 部门表
|
|
CREATE TABLE departments (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name VARCHAR(100) NOT NULL,
|
|
code VARCHAR(50) UNIQUE NOT NULL,
|
|
parent_id UUID REFERENCES departments(id),
|
|
path TEXT NOT NULL DEFAULT '',
|
|
level INTEGER NOT NULL DEFAULT 0,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'active'
|
|
CHECK (status IN ('active', 'inactive')),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_departments_parent ON departments(parent_id);
|
|
CREATE INDEX idx_departments_path ON departments(path);
|
|
|
|
-- 用户-部门关联表
|
|
CREATE TABLE user_departments (
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
dept_id UUID NOT NULL REFERENCES departments(id) ON DELETE CASCADE,
|
|
is_primary BOOLEAN NOT NULL DEFAULT false,
|
|
role VARCHAR(20) NOT NULL DEFAULT 'member'
|
|
CHECK (role IN ('head', 'manager', 'member')),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
PRIMARY KEY (user_id, dept_id)
|
|
);
|
|
|
|
CREATE INDEX idx_user_dept_user ON user_departments(user_id);
|
|
CREATE INDEX idx_user_dept_dept ON user_departments(dept_id);
|
|
|
|
-- updated_at 自动更新触发器
|
|
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ language 'plpgsql';
|
|
|
|
CREATE TRIGGER update_users_updated_at
|
|
BEFORE UPDATE ON users
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_departments_updated_at
|
|
BEFORE UPDATE ON departments
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|