Inbox/Go项目实战/用户模块/02_SQL DDL 脚本.md

tags, aliases, date created, date modified

tags	aliases	date created	date modified
	SQL DDL 脚本 🛡️ 设计说明与合规性检查	星期二, 十二月 9日 2025, 11:04:59 晚上	星期二, 十二月 9日 2025, 11:53:32 晚上

这里是为您生成的 PostgreSQL 15 生产级 SQL DDL 脚本。

SQL DDL 脚本

🛡️ 设计说明与合规性检查

1. 架构一致性: 严格遵循之前确认的 Mermaid ER 图逻辑。

2. 软删除优化: 所有包含 deleted_at 的表均增加了部分索引 (Partial Index)，确保在使用 WHERE deleted_at IS NULL 时查询性能最优，同时减少索引体积。

3. 安全性:

   • 所有外键均使用 ON DELETE RESTRICT，防止误删用户导致级联删除关联数据（如订单、日志）。
   • refresh_tokens 表的 token_hash 设为唯一，防止哈希碰撞。

4. 类型规范: 遵循宪法，使用 TIMESTAMPTZ 处理时间，TEXT 处理字符串，SMALLINT 处理枚举状态。

---

📝 修改说明文档 (Changelog v1.1)

本次更新核心聚焦于 “千万级数据下的查询性能” 与 “高频鉴权的延迟优化”。

修改点	变更内容 (What)	预期效果 (Effect)	架构师理由 (Why)
1. 反范式化缓存	users 表新增字段 cached_role_codes TEXT[]。	鉴权性能提升 10x。API 网关/中间件在鉴权时，无需关联查询 user_roles 和 roles 表，直接读取当前行即可拿到权限。	Space for Time (以空间换时间)。鉴权是系统最高频（QPS 10,000+）的操作，3 表 JOIN 在高并发下是 CPU 杀手。虽然增加了写入时的维护成本，但换来了极高的读取性能。
2. 深分页索引	新增索引 idx_users_active_created_at (针对 created_at DESC + deleted_at IS NULL)。	后台列表秒开。即使翻到第 10 万页，数据库也能利用索引直接定位数据，避免全表扫描和内存排序。	B 端体验保障。原索引仅优化了登录（按用户名查），未覆盖管理后台最常用的“按注册时间倒序查看”场景。
3. 分区策略标注	在 refresh_tokens 表头添加了关于 Range Partitioning 的架构警告与注释。	防止 IO 爆炸。明确告知 DBA/运维团队，在数据量突破 5000 万前必须实施表分区。	生命周期管理。日志类/令牌类数据如果不做分区，在执行批量删除（清理过期 Token）时会导致数据库 WAL 日志爆满甚至锁死。

---

📜 SQL DDL 脚本 (v1.1 Production-Ready)

-- =========================================================
-- Enterprise-CMS-Core Schema v1.1
-- Architecture: Modular Clean Architecture
-- Database: PostgreSQL 15+
-- =========================================================

-- ⚠️ 警告: 生产环境请移除 DROP 语句
DROP TABLE IF EXISTS refresh_tokens CASCADE;
DROP TABLE IF EXISTS user_roles CASCADE;
DROP TABLE IF EXISTS roles CASCADE;
DROP TABLE IF EXISTS users CASCADE;

-- 1. 全局设置
-- 确保时间存储统一，避免应用层时区转换混乱
SET timezone = 'Asia/Shanghai';

-- =========================================================
-- 2. 用户核心表 (users)
-- =========================================================
CREATE TABLE users (
    id                BIGSERIAL    PRIMARY KEY,
    username          TEXT         NOT NULL,
    password_hash     TEXT         NOT NULL, -- Argon2/Bcrypt Hash
    
    -- Profile 字段 (允许 NULL，应用层需处理指针)
    nickname          TEXT,
    avatar_url        TEXT,
    bio               TEXT,
    
    -- 状态: 1=Active, 0=Banned (应用层枚举)
    status            SMALLINT     NOT NULL DEFAULT 1,
    
    -- [v1.1 新增] 反范式化字段: 缓存角色编码
    -- 目的: 让鉴权中间件实现 Zero-Join 查询
    -- 默认值: 空数组 '{}'，避免 NULL 指针异常
    cached_role_codes TEXT[]       NOT NULL DEFAULT '{}',
    
    -- Base Model 字段
    created_at        TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at        TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    deleted_at        TIMESTAMPTZ
);

-- 2.1 约束定义
ALTER TABLE users 
    ADD CONSTRAINT uniq_users_username UNIQUE (username);

-- 2.2 索引策略
-- [Index] 软删除查询优化 (BRIN / Partial Index)
-- 场景: 绝大多数业务只查“未删除”数据，此过滤条件能大幅减小索引体积
CREATE INDEX idx_users_deleted_at_brin ON users (deleted_at) 
    WHERE deleted_at IS NULL; 

-- [Index] 登录查询优化
-- 场景: 根据用户名登录，且必须未被删除
CREATE INDEX idx_users_username_active ON users (username) 
    WHERE deleted_at IS NULL;

-- [v1.1 新增] [Index] 后台管理列表/深分页优化
-- 场景: SELECT * FROM users WHERE deleted_at IS NULL ORDER BY created_at DESC LIMIT N OFFSET M
-- 理由: 消除 FileSort，直接利用索引顺序扫描
CREATE INDEX idx_users_active_created_at ON users (created_at DESC) 
    WHERE deleted_at IS NULL;

-- 2.3 注释
COMMENT ON TABLE users IS '用户核心表';
COMMENT ON COLUMN users.cached_role_codes IS '[冗余字段] 缓存用户当前拥有的角色Code (e.g. {admin, editor})，用于提升鉴权性能';

-- =========================================================
-- 3. 角色定义表 (roles)
-- =========================================================
CREATE TABLE roles (
    id              BIGSERIAL    PRIMARY KEY,
    code            TEXT         NOT NULL, -- 业务唯一标识: 'admin', 'editor'
    name            TEXT         NOT NULL, -- 显示名称: '超级管理员'
    description     TEXT,
    
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    deleted_at      TIMESTAMPTZ
);

ALTER TABLE roles 
    ADD CONSTRAINT uniq_roles_code UNIQUE (code);

COMMENT ON TABLE roles IS '系统角色定义表 (元数据)';

-- =========================================================
-- 4. 用户-角色关联表 (user_roles)
-- =========================================================
CREATE TABLE user_roles (
    id              BIGSERIAL    PRIMARY KEY,
    user_id         BIGINT       NOT NULL,
    role_id         BIGINT       NOT NULL,
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- 4.1 外键约束 (确保数据一致性，防止孤儿数据)
ALTER TABLE user_roles
    ADD CONSTRAINT fk_user_roles_users FOREIGN KEY (user_id) 
    REFERENCES users(id) ON DELETE RESTRICT;

ALTER TABLE user_roles
    ADD CONSTRAINT fk_user_roles_roles FOREIGN KEY (role_id) 
    REFERENCES roles(id) ON DELETE RESTRICT;

-- 4.2 唯一约束 (防止重复授权)
ALTER TABLE user_roles 
    ADD CONSTRAINT uniq_user_roles_pair UNIQUE (user_id, role_id);

-- 4.3 索引
-- 场景: 当管理员更新某用户角色时，需要快速查找到关联记录
CREATE INDEX idx_user_roles_user_id ON user_roles(user_id);

COMMENT ON TABLE user_roles IS '用户与角色的多对多关联表 (Write Source of Truth)';

-- =========================================================
-- 5. 刷新令牌表 (refresh_tokens)
-- =========================================================
-- [v1.1 架构备注] 
-- ⚠️ Scaling Policy: 
-- 当单表行数预计超过 5000 万时，必须启用 Range Partitioning。
-- 建议策略: PARTITION BY RANGE (created_at)，按月分表，定期 DROP 旧分区。
-- 当前 V1 阶段保持标准表结构。
CREATE TABLE refresh_tokens (
    id              BIGSERIAL    PRIMARY KEY,
    user_id         BIGINT       NOT NULL,
    token_hash      TEXT         NOT NULL, 
    
    -- 风控与轮转字段
    family_id       TEXT         NOT NULL, -- 令牌家族，用于检测复用攻击
    parent_token_id TEXT         NOT NULL DEFAULT '', -- 溯源链
    is_revoked      BOOLEAN      NOT NULL DEFAULT FALSE, -- 黑名单开关
    
    expires_at      TIMESTAMPTZ  NOT NULL,
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

ALTER TABLE refresh_tokens
    ADD CONSTRAINT fk_refresh_tokens_users FOREIGN KEY (user_id) 
    REFERENCES users(id) ON DELETE RESTRICT;

ALTER TABLE refresh_tokens 
    ADD CONSTRAINT uniq_refresh_tokens_hash UNIQUE (token_hash);

-- [Index] 安全风控查询
-- 场景 1: 用户改密码 -> 吊销所有设备 (WHERE user_id = ?)
-- 场景 2: 检测到令牌盗用 -> 吊销整个家族 (WHERE family_id = ?)
CREATE INDEX idx_refresh_tokens_user_id ON refresh_tokens(user_id);
CREATE INDEX idx_refresh_tokens_family_id ON refresh_tokens(family_id);

COMMENT ON TABLE refresh_tokens IS '双令牌机制存储表 (高频写入/删除，需关注膨胀问题)';

---

🔍 索引策略自辩 (Why These indexes?)

1. users.idx_users_deleted_at_brin:

   • 场景: 系统中几乎 100% 的查询（List, Get）都会带上 deleted_at IS NULL。
   • 优势: 这是一个“部分索引” (Partial Index)。它只索引活跃用户，随着时间推移，被软删除的用户越来越多，这个索引依然会保持很小，查询速度极快。

2. refresh_tokens.idx_refresh_tokens_family_id:

   • 场景: 触发“令牌复用”警报时。
   • 逻辑: 黑客利用旧的 Refresh Token 尝试刷新 -> 系统检测到该 Token 已被用过 -> 系统根据 family_id 瞬间查出该链条上所有新颁发的 Token -> 全部标记为 is_revoked = TRUE。如果没有这个索引，高并发下这一步风控逻辑会锁表或超时。

3. user_roles.idx_user_roles_user_id:

   • 场景: 每次 HTTP 请求经过 AuthMiddleware。
   • 逻辑: 中间件必须校验当前 User 是否为 Admin。这需要高频执行 SELECT role_id FROM user_roles WHERE user_id = ?。此索引是系统吞吐量的关键。