133 lines
5.1 KiB
PL/PgSQL
133 lines
5.1 KiB
PL/PgSQL
-- IAM 表结构（与 internal/iam/entity 中 GORM 模型一致；PostgreSQL）
|
||
-- 执行：psql $DATABASE_URL -f migrations/postgres/001_iam.sql
|
||
|
||
BEGIN;
|
||
|
||
-- 租户
|
||
CREATE TABLE IF NOT EXISTS iam_tenant (
|
||
id varchar(36) PRIMARY KEY,
|
||
tenant_code varchar(64) NOT NULL,
|
||
tenant_name varchar(128) NOT NULL,
|
||
admin_user_id varchar(36) NULL,
|
||
status smallint NOT NULL DEFAULT 1,
|
||
expire_time timestamptz NULL,
|
||
created_at timestamptz NOT NULL DEFAULT now(),
|
||
updated_at timestamptz NOT NULL DEFAULT now(),
|
||
deleted_at timestamptz NULL
|
||
);
|
||
CREATE UNIQUE INDEX IF NOT EXISTS uq_iam_tenant_code ON iam_tenant (tenant_code);
|
||
CREATE INDEX IF NOT EXISTS idx_iam_tenant_deleted_at ON iam_tenant (deleted_at);
|
||
|
||
-- 部门（根部门 parent_id 为空串）
|
||
CREATE TABLE IF NOT EXISTS iam_dept (
|
||
id varchar(36) PRIMARY KEY,
|
||
tenant_id varchar(36) NOT NULL,
|
||
parent_id varchar(36) NOT NULL DEFAULT '',
|
||
dept_name varchar(128) NOT NULL,
|
||
dept_path text NULL,
|
||
leader_id varchar(36) NULL,
|
||
sort_order int NOT NULL DEFAULT 0,
|
||
status smallint NOT NULL DEFAULT 1,
|
||
created_at timestamptz NOT NULL DEFAULT now(),
|
||
updated_at timestamptz NOT NULL DEFAULT now(),
|
||
deleted_at timestamptz NULL
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_dept_tenant ON iam_dept (tenant_id);
|
||
CREATE INDEX IF NOT EXISTS idx_dept_parent ON iam_dept (parent_id);
|
||
CREATE INDEX IF NOT EXISTS idx_iam_dept_deleted_at ON iam_dept (deleted_at);
|
||
|
||
-- 用户
|
||
CREATE TABLE IF NOT EXISTS iam_user (
|
||
id varchar(36) PRIMARY KEY,
|
||
tenant_id varchar(36) NOT NULL,
|
||
dept_id varchar(36) NULL,
|
||
user_name varchar(64) NOT NULL,
|
||
real_name varchar(64) NULL,
|
||
password_hash varchar(255) NOT NULL,
|
||
phone varchar(20) NULL,
|
||
email varchar(128) NULL,
|
||
avatar varchar(512) NULL,
|
||
gender smallint NOT NULL DEFAULT 0,
|
||
status smallint NOT NULL DEFAULT 1,
|
||
login_attempts int NOT NULL DEFAULT 0,
|
||
locked_until timestamptz NULL,
|
||
last_login_at timestamptz NULL,
|
||
last_login_ip varchar(45) NULL,
|
||
created_at timestamptz NOT NULL DEFAULT now(),
|
||
updated_at timestamptz NOT NULL DEFAULT now(),
|
||
deleted_at timestamptz NULL
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_user_tenant ON iam_user (tenant_id);
|
||
CREATE INDEX IF NOT EXISTS idx_user_dept ON iam_user (dept_id);
|
||
CREATE INDEX IF NOT EXISTS idx_iam_user_deleted_at ON iam_user (deleted_at);
|
||
|
||
-- 用户-部门关联
|
||
CREATE TABLE IF NOT EXISTS iam_user_dept (
|
||
id varchar(36) PRIMARY KEY,
|
||
user_id varchar(36) NOT NULL,
|
||
dept_id varchar(36) NOT NULL,
|
||
is_primary boolean NOT NULL DEFAULT false,
|
||
created_at timestamptz NOT NULL DEFAULT now(),
|
||
CONSTRAINT uk_user_dept UNIQUE (user_id, dept_id)
|
||
);
|
||
|
||
-- 角色
|
||
CREATE TABLE IF NOT EXISTS iam_role (
|
||
id varchar(36) PRIMARY KEY,
|
||
tenant_id varchar(36) NOT NULL,
|
||
role_code varchar(64) NOT NULL,
|
||
role_name varchar(128) NOT NULL,
|
||
data_scope smallint NOT NULL DEFAULT 4,
|
||
description varchar(512) NULL,
|
||
is_builtin boolean NOT NULL DEFAULT false,
|
||
status smallint NOT NULL DEFAULT 1,
|
||
created_at timestamptz NOT NULL DEFAULT now(),
|
||
updated_at timestamptz NOT NULL DEFAULT now(),
|
||
deleted_at timestamptz NULL
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_role_tenant ON iam_role (tenant_id);
|
||
CREATE INDEX IF NOT EXISTS idx_iam_role_deleted_at ON iam_role (deleted_at);
|
||
|
||
-- 菜单（全局）
|
||
CREATE TABLE IF NOT EXISTS iam_menu (
|
||
id varchar(36) PRIMARY KEY,
|
||
parent_id varchar(36) NOT NULL DEFAULT '',
|
||
menu_name varchar(128) NOT NULL,
|
||
menu_type smallint NOT NULL,
|
||
perms varchar(128) NULL,
|
||
path varchar(255) NULL,
|
||
component varchar(255) NULL,
|
||
icon varchar(64) NULL,
|
||
sort_order int NOT NULL DEFAULT 0,
|
||
is_visible boolean NOT NULL DEFAULT true,
|
||
is_builtin boolean NOT NULL DEFAULT false,
|
||
external_link varchar(512) NULL,
|
||
status smallint NOT NULL DEFAULT 1,
|
||
created_at timestamptz NOT NULL DEFAULT now(),
|
||
updated_at timestamptz NOT NULL DEFAULT now(),
|
||
deleted_at timestamptz NULL
|
||
);
|
||
CREATE UNIQUE INDEX IF NOT EXISTS uq_iam_menu_perms ON iam_menu (perms);
|
||
CREATE INDEX IF NOT EXISTS idx_menu_parent ON iam_menu (parent_id);
|
||
CREATE INDEX IF NOT EXISTS idx_iam_menu_deleted_at ON iam_menu (deleted_at);
|
||
|
||
-- 用户-角色
|
||
CREATE TABLE IF NOT EXISTS iam_user_role (
|
||
id varchar(36) PRIMARY KEY,
|
||
user_id varchar(36) NOT NULL,
|
||
role_id varchar(36) NOT NULL,
|
||
created_at timestamptz NOT NULL DEFAULT now(),
|
||
CONSTRAINT uk_user_role UNIQUE (user_id, role_id)
|
||
);
|
||
|
||
-- 角色-菜单
|
||
CREATE TABLE IF NOT EXISTS iam_role_menu (
|
||
id varchar(36) PRIMARY KEY,
|
||
role_id varchar(36) NOT NULL,
|
||
menu_id varchar(36) NOT NULL,
|
||
created_at timestamptz NOT NULL DEFAULT now(),
|
||
CONSTRAINT uk_role_menu UNIQUE (role_id, menu_id)
|
||
);
|
||
|
||
COMMIT;
|