31 lines
1.2 KiB
SQL
31 lines
1.2 KiB
SQL
-- =====================================================================================
|
||
-- Schema: 用户分组表
|
||
-- 位置：docs/sql/10_schema/user/ak_user_groups_v1.sql
|
||
-- 对象类型：Schema (DDL)
|
||
-- 版本：v1
|
||
-- 说明：用户分组定义，支持逻辑删除和状态管理
|
||
-- =====================================================================================
|
||
|
||
CREATE TABLE IF NOT EXISTS public.ak_user_groups (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
||
name TEXT NOT NULL,
|
||
remark TEXT NULL,
|
||
status INT NOT NULL DEFAULT 1, -- 1:启用, 0:禁用
|
||
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
deleted_at TIMESTAMPTZ NULL,
|
||
|
||
CONSTRAINT ak_user_groups_name_length CHECK (char_length(name) >= 1)
|
||
);
|
||
|
||
-- 唯一性约束（仅对未删除记录生效）
|
||
CREATE UNIQUE INDEX IF NOT EXISTS ak_user_groups_name_uniq_active
|
||
ON public.ak_user_groups (name)
|
||
WHERE deleted_at IS NULL;
|
||
|
||
-- 常用查询索引
|
||
CREATE INDEX IF NOT EXISTS ak_user_groups_status_idx ON public.ak_user_groups (status) WHERE deleted_at IS NULL;
|
||
CREATE INDEX IF NOT EXISTS ak_user_groups_created_at_idx ON public.ak_user_groups (created_at DESC);
|