17. 综合实践：管理员登录审计

本章把前面的建表、Migration、TypeScript Model、事务、关联、分页、错误和测试串在一起。请先完成“题目与 TODO”，再看后半部分参考设计。

第一部分：题目

1. 业务背景

安全人员希望查看管理员登录尝试，包括：

尝试登录的用户名；
是否成功；
若用户名对应管理员，则关联该管理员；
客户端 IP；
发生时间；
管理员后来删除后，历史审计仍保留；
用户名不存在的失败尝试也必须记录。

本练习假设：

MySQL 8、InnoDB、utf8mb4_0900_ai_ci；
数据库和 Sequelize 字段统一 snake_case；
应用采用统一时区策略，业务时间使用 DATETIME(3)；
审计记录只追加，不通过普通业务接口修改；
暂不记录明文密码、Token 或 password_hash；
IP 可能是 IPv4 或 IPv6；
本章不要求真实修改当前登录代码，先完成设计和独立练习。

2. 数据模型要求

创建 admin_login_audit，最少包含：

字段	业务要求
`id`	长期增长的主键，与项目 BIGINT 字符串策略一致
`admin_user_id`	用户存在时关联管理员；未知用户和管理员删除后允许 NULL
`attempted_username`	必填，保存尝试时用户名快照
`login_result`	必填，只允许已定义的小范围状态值
`ip_address`	可空，兼容 IPv4/IPv6 文本形式
`created_at`	必填，毫秒精度，数据库提供默认时间

查询需求：

按管理员查询最近审计，按 created_at DESC, id DESC；
按尝试用户名查询最近审计，包括不存在的用户名；
分页查询全部审计并 include 管理员安全字段；
管理员删除后，审计仍可通过用户名快照排查。

3. 你需要完成的文件/函数

以下是建议骨架，不代表必须照搬目录。先保持简单：

migrations/
  00xx_create_admin_login_audit.sql
models/
  admin_login_audit.ts
  associations.ts
services/
  admin-login-audit.service.ts
types/
  admin-login-audit.ts

建议函数：

export async function createAdminLoginAudit(
  input: CreateAdminLoginAuditInput,
  options?: { transaction?: Transaction },
): Promise<AdminLoginAudit>;

export async function listAdminLoginAudits(
  query: ListAdminLoginAuditsQuery,
): Promise<AdminLoginAuditPageDto>;

export function toAdminLoginAuditDto(
  audit: AdminLoginAudit,
): AdminLoginAuditDto;

4. TODO 清单

TODO A：设计 Migration

[ ] 明确每个字段的 MySQL 类型、NULL 和默认值。
[ ] 给 login_result 建稳定数据底线。
[ ] 选择并解释 ON DELETE 策略。
[ ] 根据两类列表查询建立联合索引。
[ ] 给表、字段、约束和索引使用明确名称。
[ ] 在空测试库执行 Migration。

TODO B：定义 TypeScript Model

[ ] 使用 InferAttributes、InferCreationAttributes。
[ ] 自增 ID 使用 CreationOptional<string>。
[ ] 外键使用 ForeignKey<AdminUser["id"]> | null。
[ ] include 属性使用 NonAttribute<AdminUser | null>，且考虑未加载时的 undefined。
[ ] created_at 使用 CreationOptional<Date>。
[ ] Model.init() 与 DDL 逐字段一致。

TODO C：注册关联

[ ] AdminUser.hasMany(..., as: "login_audits")。
[ ] AdminLoginAudit.belongsTo(..., as: "admin")。
[ ] 在应用启动时统一注册一次，避免 CommonJS 循环依赖。

TODO D：接入登录流程

[ ] 不存在用户名时，写入 admin_user_id: null。
[ ] 已知用户密码错误时，写入管理员 ID 和失败结果。
[ ] 登录成功时写入成功结果。
[ ] 决定登录状态更新和成功审计是否必须同一事务。
[ ] 审计写入失败时，明确登录应该失败还是继续。
[ ] 从请求边界安全取得 IP；不要信任任意客户端伪造的转发头。

TODO E：查询和 DTO

[ ] 列表必须限制最大 page size。
[ ] 使用确定性排序 created_at + id。
[ ] include 只查询管理员 id/username/display_name。
[ ] 验证当前“审计 belongsTo 管理员”查询的 count；再比较管理员 hasMany 审计时为什么可能需要 distinct。
[ ] DTO 不输出 Sequelize 内部属性或敏感管理员字段。

TODO F：测试

[ ] 未知用户名失败记录。
[ ] 已知用户名成功/失败记录。
[ ] 管理员删除后审计保留、外键置 NULL、快照不变。
[ ] 外键、CHECK、默认时间生效。
[ ] JOIN 的 required true/false 行为。
[ ] 分页 count 和跨页排序稳定。
[ ] 事务失败时最终数据库状态符合决策。

5. 验收 SQL

先用 SQL 独立验证数据，再验证 Sequelize：

-- 最近一次指定管理员的审计
SELECT id, admin_user_id, attempted_username, login_result,
       ip_address, created_at
FROM admin_login_audit
WHERE admin_user_id = 1
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- 不存在用户名也能留下审计
SELECT id, admin_user_id, attempted_username, login_result, created_at
FROM admin_login_audit
WHERE attempted_username = 'not_exists'
ORDER BY created_at DESC, id DESC;

-- 连表列表，管理员删除后 audit 仍保留
SELECT
  audit.id,
  audit.attempted_username,
  audit.login_result,
  audit.ip_address,
  audit.created_at,
  admin.id AS admin_id,
  admin.username AS current_username,
  admin.display_name
FROM admin_login_audit AS audit
LEFT JOIN admin_user AS admin
  ON admin.id = audit.admin_user_id
ORDER BY audit.created_at DESC, audit.id DESC
LIMIT 50;

使用 EXPLAIN ANALYZE 检查前两个查询是否匹配预期联合索引。

6. 设计问题（先写答案）

为什么既保存 admin_user_id，又保存 attempted_username？
为什么 admin_user_id 允许 NULL，而 attempted_username 不允许？
为什么不使用 ON DELETE CASCADE？
登录成功状态更新与审计写入是否必须同一事务？若审计失败怎么办？
用户名不存在时做 Argon2 假验证与写审计的先后顺序如何影响时序侧信道？
为什么审计列表不能返回 admin.password_hash？

第二部分：参考设计

7. 字段决策表

字段	类型	NULL	默认值	选择理由
`id`	`BIGINT UNSIGNED`	否	AUTO_INCREMENT	长期增长主键；与项目管理员 ID 策略一致，Node.js 中按 string
`admin_user_id`	`BIGINT UNSIGNED`	是	NULL	未知用户名无管理员可关联；管理员删除后通过 SET NULL 保留历史；类型与主键完全一致
`attempted_username`	`VARCHAR(50)`	否	无	保存尝试时快照；长度与当前 username 产品边界一致，不使用会随主表变化的当前值代替
`login_result`	`TINYINT UNSIGNED`	否	无	小范围状态；TypeScript enum 提升可读性，CHECK 守住数据库底线
`ip_address`	`VARCHAR(45)`	是	NULL	IPv4/IPv6 文本最大常用表示；无法可靠获取时 NULL 比空串明确
`created_at`	`DATETIME(3)`	否	`CURRENT_TIMESTAMP(3)`	普通业务时间，毫秒精度；由数据库为所有写入入口提供默认值

这里没有机械使用 VARCHAR(255)：用户名长度来自现有产品/表约定；IP 的 45 来自 IPv6 文本表示上限。created_at 采用 DATETIME(3)，项目需要统一约定存 UTC 或统一业务时区。

8. 状态值设计

参考值：

export enum AdminLoginResult {
  Success = 1,
  InvalidCredentials = 2,
  AccountDisabled = 3,
  AccountLocked = 4,
}

数据库不用 MySQL ENUM，而使用 TINYINT UNSIGNED + CHECK：

添加状态时 Migration 意图明确；
TypeScript 中仍有可读名称；
数据库拒绝未知数字；
日志与 API 不散落魔法数字。

状态集合属于本练习假设。真实项目还要决定数据库/JWT故障是否属于“登录结果”；通常内部故障写错误日志，不伪装成用户认证失败。

9. 完整 MySQL 8 DDL

CREATE TABLE admin_login_audit (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '登录审计主键',
  admin_user_id BIGINT UNSIGNED NULL DEFAULT NULL COMMENT '匹配到的管理员ID；未知或管理员删除后为空',
  attempted_username VARCHAR(50) NOT NULL COMMENT '登录尝试时的用户名快照',
  login_result TINYINT UNSIGNED NOT NULL COMMENT '结果：1成功，2凭据错误，3账号禁用，4账号锁定',
  ip_address VARCHAR(45) NULL DEFAULT NULL COMMENT '客户端IPv4或IPv6地址',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '登录尝试时间',

  PRIMARY KEY (id),
  KEY idx_admin_login_audit_admin_created
    (admin_user_id, created_at, id),
  KEY idx_admin_login_audit_username_created
    (attempted_username, created_at, id),

  CONSTRAINT chk_admin_login_audit_result
    CHECK (login_result IN (1, 2, 3, 4)),
  CONSTRAINT fk_admin_login_audit_admin
    FOREIGN KEY (admin_user_id) REFERENCES admin_user (id)
    ON UPDATE RESTRICT
    ON DELETE SET NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci
  COMMENT='管理员登录审计记录';

索引对应关系：

(admin_user_id, created_at, id) 支持“指定管理员最近记录”；
(attempted_username, created_at, id) 支持“不论用户是否存在，按尝试用户名排查”；
id 作为同时间的稳定排序补充；
外键列的索引也满足外键检查需要。

每增加一个索引都会提高写入成本。若业务从不按用户名查，第二个索引不应因为“可能有用”盲目建立；本练习明确给出了对应查询，所以合理。

ON UPDATE RESTRICT 表示不鼓励修改管理员主键；ON DELETE SET NULL 要求外键列允许 NULL。删除管理员不会删除审计，但仍需授权和审计管理员删除操作本身。

10. TypeScript Model 参考

import {
  CreationOptional,
  DataTypes,
  ForeignKey,
  InferAttributes,
  InferCreationAttributes,
  Model,
  NonAttribute,
  type Transaction,
} from "sequelize";

import db from "../db";
import AdminUser from "./admin_user";
import { AdminLoginResult } from "../types/admin-login-audit";

export class AdminLoginAudit extends Model<
  InferAttributes<AdminLoginAudit>,
  InferCreationAttributes<AdminLoginAudit>
> {
  declare id: CreationOptional<string>;
  declare admin_user_id: ForeignKey<AdminUser["id"]> | null;
  declare attempted_username: string;
  declare login_result: AdminLoginResult;
  declare ip_address: string | null;
  declare created_at: CreationOptional<Date>;

  // undefined = 未 include；null = include 了但管理员不存在/已删除。
  declare admin?: NonAttribute<AdminUser | null>;
}

AdminLoginAudit.init(
  {
    id: {
      type: DataTypes.BIGINT.UNSIGNED,
      primaryKey: true,
      autoIncrement: true,
    },
    admin_user_id: {
      type: DataTypes.BIGINT.UNSIGNED,
      allowNull: true,
      defaultValue: null,
    },
    attempted_username: {
      type: DataTypes.STRING(50),
      allowNull: false,
    },
    login_result: {
      type: DataTypes.TINYINT.UNSIGNED,
      allowNull: false,
      validate: {
        isIn: [[1, 2, 3, 4]],
      },
    },
    ip_address: {
      type: DataTypes.STRING(45),
      allowNull: true,
      defaultValue: null,
    },
    created_at: {
      type: DataTypes.DATE(3),
      allowNull: false,
    },
  },
  {
    sequelize: db,
    tableName: "admin_login_audit",
    freezeTableName: true,
    timestamps: false,
    indexes: [
      { name: "idx_admin_login_audit_admin_created", fields: ["admin_user_id", "created_at", "id"] },
      { name: "idx_admin_login_audit_username_created", fields: ["attempted_username", "created_at", "id"] },
    ],
  },
);

Model 中 indexes 是映射文档和 sync() 信息，真实环境结构仍由 Migration 管理。Model 的 validate.isIn 改善应用写入错误，但数据库 CHECK 才能保护 DBeaver、CLI 等其他入口。

这里不配置 defaultValue: DataTypes.NOW，让 Migration 中的 CURRENT_TIMESTAMP(3) 成为默认时间的唯一来源。MySQL 创建记录后如果代码立刻需要数据库生成的 created_at，应对实例执行 reload()；测试数据库默认值时则应使用省略该列的 Raw SQL，避免 Model 层默认值或校验遮蔽数据库行为。

11. 关联参考

AdminUser.hasMany(AdminLoginAudit, {
  foreignKey: "admin_user_id",
  as: "login_audits",
  onUpdate: "RESTRICT",
  onDelete: "SET NULL",
});

AdminLoginAudit.belongsTo(AdminUser, {
  foreignKey: "admin_user_id",
  as: "admin",
  onUpdate: "RESTRICT",
  onDelete: "SET NULL",
});

AdminUser 可声明：

declare login_audits?: NonAttribute<AdminLoginAudit[]>;

关联注册最好集中在 models/associations.ts，所有模型完成 init() 后调用一次。

12. 创建输入与事务传递

export interface CreateAdminLoginAuditInput {
  admin_user_id: string | null;
  attempted_username: string;
  login_result: AdminLoginResult;
  ip_address: string | null;
}

export async function createAdminLoginAudit(
  input: CreateAdminLoginAuditInput,
  options: { transaction?: Transaction } = {},
): Promise<AdminLoginAudit> {
  // exactOptionalPropertyTypes 下不能把 transaction: undefined 显式传给 Sequelize。
  const create_options = options.transaction === undefined
    ? {}
    : { transaction: options.transaction };

  return AdminLoginAudit.create(
    {
      admin_user_id: input.admin_user_id,
      attempted_username: input.attempted_username,
      login_result: input.login_result,
      ip_address: input.ip_address,
    },
    create_options,
  );
}

使用显式白名单，避免调用者把任意请求对象直接传给 Model。transaction 可选，使登录 Service 能决定原子边界。

13. 登录流程怎样接入

未知用户名或错误密码

记录审计前必须先决定安全策略。不应因为“写审计”破坏不存在用户与密码错误的统一响应，也不要记录密码。

await createAdminLoginAudit({
  admin_user_id: admin?.id ?? null,
  attempted_username: normalized_username,
  login_result: AdminLoginResult.InvalidCredentials,
  ip_address,
});

若未来使用固定假 Hash 缓解用户名时序枚举，未知用户也应走相近的验证路径。数据库审计写入耗时也可能形成差异，因此完整安全设计还需限流和聚合监控，不能只靠统一文案。

成功登录

业务不变量可定义为：

只有 last_login_at 更新与成功审计都写入后，登录才返回 Token。

则可在短事务中完成两个数据库写入：

await db.transaction(async (transaction) => {
  await AdminUser.update(
    {
      failed_login_count: 0,
      locked_until: null,
      last_login_at: logged_in_at,
    },
    { where: { id: admin.id }, transaction },
  );

  await createAdminLoginAudit(
    {
      admin_user_id: admin.id,
      attempted_username: admin.username,
      login_result: AdminLoginResult.Success,
      ip_address,
    },
    { transaction },
  );
});

Argon2 和 JWT 签发不应放入长事务。还要明确：JWT 是内存中的结果，数据库回滚不能“撤回”已经交给外部的 Token，所以必须在所有必要写入成功后才发送 HTTP 响应。

另一种业务选择是审计尽力而为，失败不阻止登录。这提高可用性但会丢安全记录。对于管理员安全审计，本教程倾向将成功状态与成功审计作为共同成功条件；最终决定需要产品、安全和运维共同确认。

14. 分页查询参考

const result = await AdminLoginAudit.findAndCountAll({
  attributes: [
    "id",
    "admin_user_id",
    "attempted_username",
    "login_result",
    "ip_address",
    "created_at",
  ],
  include: [{
    association: "admin",
    attributes: ["id", "username", "display_name"],
    required: false,
  }],
  order: [["created_at", "DESC"], ["id", "DESC"]],
  limit: page_size,
  offset: (page - 1) * page_size,
});

这里主表是审计，每条审计至多属于一个管理员，不会像“管理员 hasMany 审计”那样把审计主记录重复。仍要测试 count，并限制 page_size。大数据量时升级为 (created_at, id) 游标分页。

15. DTO 参考

export interface AdminLoginAuditDto {
  id: string;
  attempted_username: string;
  login_result: AdminLoginResult;
  ip_address: string | null;
  created_at: string;
  admin: {
    id: string;
    username: string;
    display_name: string;
  } | null;
}

Mapper 必须区分未 include 和关联为空。列表 Service 可以保证 include 后再调用专用 Mapper；若 audit.admin === undefined，应抛内部编程错误，而不是悄悄当成管理员已删除。

16. IP 获取注意事项

Express 的 req.ip 是否相信 X-Forwarded-For 由 trust proxy 配置决定。直接读取任意请求头会允许客户端伪造 IP。

实践流程：

明确部署是否经过可信反向代理；
按代理层数/网段配置 trust proxy；
在 Controller 请求边界取得规范化 IP；
Service 接收 string | null，不依赖 Express；
限制日志和查询权限，并制定审计保留周期。

17. 常见错误设计

admin_user_id NOT NULL：不存在用户名的攻击无法记录。
ON DELETE CASCADE：删管理员时把安全历史一起删除。
只保存外键：管理员删除/改名后丢失尝试时信息。
用空字符串代替未知管理员 ID 或 IP：破坏类型和语义。
把 login_result 做成无约束整数：数据库可写入任意魔法值。
给每个字段单独建索引：无法针对查询排序设计，且增加写成本。
审计写入包含密码、Hash 或完整 JWT：制造新的高危数据源。
include 完整 AdminUser 直接响应：可能泄露敏感字段。
在事务中完成密码 Hash、JWT、网络调用：事务过长。
Migration 已执行后直接修改原文件：不同环境结构分叉。

18. 推荐实施顺序

写出业务不变量和保留策略
  → 设计字段、NULL、约束与查询
  → 编写版本化 Migration
  → 空 MySQL 8 测试库执行
  → 编写 TypeScript Model 和关联
  → 写独立 create/list 集成测试
  → 接入登录 Service 的事务边界
  → HTTP 测试响应与错误脱敏
  → EXPLAIN 验证索引
  → 提交 Migration、Model、Service、测试

19. 最终验收清单

[ ] 所有表/列/索引/约束使用小写 snake_case。
[ ] 主键与外键均为 BIGINT UNSIGNED，TS 中保持 string。
[ ] 未知用户名可创建 admin_user_id = NULL 的记录。
[ ] 删除管理员后记录仍存在且快照不变。
[ ] 非法 login_result 被数据库拒绝。
[ ] 两个联合索引分别有真实查询对应。
[ ] include 使用 LEFT JOIN，并且只选择管理员安全字段。
[ ] 列表有上限、稳定排序和正确 count。
[ ] 事务内每条相关 SQL 都显式传递同一 transaction。
[ ] 任何日志与 DTO 都没有密码、Hash、Token 或 Secret。
[ ] 从空测试库能执行全部 Migration。
[ ] 打开 SQL 日志并用 EXPLAIN ANALYZE 验证关键查询。

20. 复盘问题

attempted_username 是冗余，还是必要历史快照？为什么？
NULL 在 admin_user_id 和 ip_address 中分别表达什么？
Model 的 validate 与数据库 CHECK 为什么都值得保留？
成功审计和登录状态是否应同一事务，代价是什么？
删除管理员后，include 的 admin 可能是 undefined 还是 null？两者如何区分？
如果数据达到千万级，分页和索引要怎样演进？