56 lines
1.9 KiB
SQL
56 lines
1.9 KiB
SQL
-- =====================================================================================
|
||
-- Schema: 用户等级表
|
||
-- 位置：docs/sql/10_schema/user/
|
||
-- 对象类型：Schema (DDL)
|
||
-- 版本：v1
|
||
-- 说明：用户等级（经验值/折扣/展示/状态），支持逻辑删除
|
||
-- =====================================================================================
|
||
|
||
CREATE TABLE IF NOT EXISTS public.ak_user_levels (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
||
name TEXT NOT NULL,
|
||
level_weight INT NOT NULL,
|
||
min_experience INT NOT NULL DEFAULT 0,
|
||
discount_percent INT NOT NULL DEFAULT 100,
|
||
|
||
is_visible BOOLEAN NOT NULL DEFAULT TRUE,
|
||
status INT NOT NULL DEFAULT 1,
|
||
|
||
icon_url TEXT NULL,
|
||
bg_image_url TEXT NULL,
|
||
bg_style_json JSONB NULL,
|
||
|
||
remark TEXT NULL,
|
||
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
deleted_at TIMESTAMPTZ NULL,
|
||
|
||
CONSTRAINT ak_user_levels_level_weight_nonnegative CHECK (level_weight >= 0),
|
||
CONSTRAINT ak_user_levels_min_experience_nonnegative CHECK (min_experience >= 0),
|
||
CONSTRAINT ak_user_levels_discount_percent_range CHECK (discount_percent BETWEEN 1 AND 100)
|
||
);
|
||
|
||
-- 唯一性（仅对未删除记录生效）
|
||
CREATE UNIQUE INDEX IF NOT EXISTS ak_user_levels_name_uniq_active
|
||
ON public.ak_user_levels (name)
|
||
WHERE deleted_at IS NULL;
|
||
|
||
CREATE UNIQUE INDEX IF NOT EXISTS ak_user_levels_level_weight_uniq_active
|
||
ON public.ak_user_levels (level_weight)
|
||
WHERE deleted_at IS NULL;
|
||
|
||
-- 常用查询索引
|
||
CREATE INDEX IF NOT EXISTS ak_user_levels_active_filter_idx
|
||
ON public.ak_user_levels (status, is_visible)
|
||
WHERE deleted_at IS NULL;
|
||
|
||
CREATE INDEX IF NOT EXISTS ak_user_levels_min_experience_idx
|
||
ON public.ak_user_levels (min_experience)
|
||
WHERE deleted_at IS NULL;
|
||
|
||
CREATE INDEX IF NOT EXISTS ak_user_levels_level_weight_desc_idx
|
||
ON public.ak_user_levels (level_weight DESC)
|
||
WHERE deleted_at IS NULL;
|