数据表描述及功能

users 表

描述： 存储用户的基本信息和状态。

字段：

id              - 用户唯一标识
email           - 邮件地址
email_hash   - 邮件hash
phone           - 手机号码
phone_hash   - 电话hash
password        - 用户密码
email_verified  - 邮件验证状态（布尔值）
phone_verified  - 手机验证状态（布尔值）
status          - 用户状态（激活、禁用等）
created         - 创建日期
updated         - 更新日期
nickname        - 昵称
language        - 语言偏好
country         - 国家

功能描述：
- 注册：保存用户的注册信息。
- 登录：验证用户的登录信息。
- 个人资料管理：更新用户的昵称、语言、国家等资料。
- 验证：记录用户的邮箱和手机验证状态。
- 账号状态管理：管理用户账号的激活和禁用。

users_oidc 表

描述： 存储用户与 OIDC 提供者的绑定信息。

字段：

id             - 绑定记录唯一标识
user_id        - 用户唯一标识
provider       - OIDC 提供者名称（例如：Google、Facebook）
provider_uid   - OIDC 提供者标识符（用户在提供者系统中的唯一标识）
created        - 绑定创建日期
updated        - 绑定更新日期

功能描述：
- 第三方登录：支持用户通过 OIDC 提供者进行第三方登录。
- 账户绑定：允许用户将多个第三方账户绑定到同一个系统账户。
- 解绑管理：允许用户和管理员管理和解绑 OIDC 提供者账户。

mfa 表

描述： 存储用户多因素认证（MFA）信息。

字段：

id        - MFA 记录唯一标识
user_id   - 用户唯一标识
type      - 认证类型（email、phone、totp）
name      - 设备名称或描述
status     - 状态 (启用、禁用)
last_verified - 最近一次验证时间
last_type - 最近一次验证类型
created   - 创建日期
updated   - 更新日期

功能描述：
- 启用多因素认证：存储用户启用的 MFA 信息（类型和设备名称）。
- 管理 MFA 设置：修改和删除 MFA 设置。
- 认证：使用 MFA 进行用户身份验证。

session 表

描述： 存储用户的会话信息。

字段：

id        - 会话唯一标识
user_id   - 用户唯一标识
session   - 会话令牌或标识符
created   - 创建日期
updated   - 更新日期
expires_at  - 会话过期时间

功能描述：
- 登录会话管理：保存用户的登录会话。
- 会话验证：验证用户的会话令牌。
- 多设备支持：记录不同设备的会话信息。

session_history 表

描述： 存储用户会话的历史记录。

字段：

id         - 会话历史记录唯一标识
user_id - 用户ID
session_id - 会话唯一标识
date       - 记录日期
ip         - 登录 IP 地址
country    - 登录国家
device_info - 设备信息

功能描述：
- 安全审计：记录用户每次会话的详细信息（IP、国家等）。
- 异常检测：检测异常的登录活动，例如地理位置变化异常。
- 历史查询：用户可以查看自己的登录历史记录。

activity_log 表

字段：

id           - 行为记录唯一标识
user_id      - 用户唯一标识
action       - 操作类型（例如：登录、修改信息、注销等）
timestamp    - 操作时间
ip_address   - 操作时的 IP 地址
action - 操作动作
details      - 操作详情（例如：修改了什么信息、登录失败原因等）

功能描述：
- 操作审计：记录用户的各种操作行为，便于系统管理员审计。
- 安全分析：通过记录操作行为，可以分析和检测异常活动。
- 行为跟踪：帮助用户和管理员跟踪操作历史。

是否需要

users

last_login 最后登录时间
avatar 头像
role 权限

session

expires_at 过期时间

tokens 表

描述： 存储用户生成的token，用于API操作

字段：

id              - 用户唯一标识
user_id   - 用户ID
token           - 手机号码
created         - 创建日期
updated         - 更新日期
expriy_at        - 过期时间
ip_address       - api授权的IP地址
resources        - token 可以访问的资源
last_used_at    - 最后一次访问时间

tokens 表

描述： 存储用户生成的token，用于API操作

字段：

id              - 用户唯一标识
user_id   - 用户ID
token           - 手机号码
created         - 创建日期
updated         - 更新日期
expriy_at        - 过期时间
ip_address       - api授权的IP地址
resources        - token 可以访问的资源
last_used_at    - 最后一次访问时间

存储这个信息用于记录的目的吗？但按常规操作，通过程序我们拿到 token 只会验证而已，而不是存储它。存储 token 会不会有点敏感，安全风险。

如果不存储，我们只需要写入 redis，只是负责读写操作而已，会不会更简单呢

改动点

1. users 表

表名更改：users 改为 user。
email_verified 改为 isEmailVerified，类型更改为 integer，默认值为 0。
phone_verified 改为 isPhoneVerified，类型更改为 integer，默认值为 0。
status 字段的文档描述增加了状态对应的值说明（如：0 为禁用，1 为激活）。
created, updated 字段改为 created_at, updated_at。
增加了对 email 和 phone 字段的唯一索引（emailIndex, phoneIndex）。

2. 新的表

增加了 email_verification 表，用于存储邮件验证相关信息，包括：
- code 字段用于存储验证代码。
- expiresAt 用于存储过期时间。
- ip 存储验证时的IP地址。
增加了 password_reset 表，用于存储密码重置相关信息。
增加了 users_oauth 表，用于存储用户与 OIDC 提供者的绑定信息，新增了 accessToken 和 refreshToken 字段，以及 expiresAt。
增加了 session 表，包含字段如 status, date, ip, country, 和 device_info。
增加了 activity_log 表，新增了 sessionId 字段。

3. mfa 表

last_type 改为 lastMfaId，并修改了字段的描述。

4. 字段类型

多处timestampt类型从created和updated更改为integer` 类型，具体实现可能会使用时间戳。
字段名称和类型等细节有修改，例如sessionToken 替代 session。

schema.ts

import { integer, sqliteTable, text, uniqueIndex } from 'drizzle-orm/sqlite-core'

export const userTable = sqliteTable('user', {
  id: text('id').primaryKey(),
  email: text('email').unique(),
  emailHash: text('email_hash').notNull(),
  phone: text('phone').unique(),
  phoneHash: text('phone_hash').notNull(),
  password: text('password').notNull(),
  isEmailVerified: integer('is_email_verified').default(0),
  isPhoneVerified: integer('is_phone_verified').default(0),
  /**
   * 状态
   * - 0 禁用
   * - 1 激活
   */
  status: integer('status').default(0),
  created: integer('created_at').default(Date.now()),
  updated: integer('updated_at').default(Date.now()),
  nickname: text('nickname'),
  language: text('language'),
  country: text('country'),
}, (table) => {
  return {
    emailIndex: uniqueIndex('email_index').on(table.email),
    phoneIndex: uniqueIndex('phone_index').on(table.phone),
  }
})

export const emailVerificationTable = sqliteTable(
  'email_verification',
  {
    id: text('id').primaryKey(),
    userId: text('user_id')
      .notNull()
      .references(() => userTable.id),
    code: text('code').notNull(),
    expiresAt: integer('expires_at').notNull(),
    ip: text('ip_address').notNull(),
  },
  table => ({
    index: uniqueIndex('idx_email_verification_userId_code').on(table.userId, table.code),
  }),
)

export const passwordResetTable = sqliteTable(
  'password_reset',
  {
    id: text('id').primaryKey(),
    userId: text('user_id')
      .notNull()
      .references(() => userTable.id),
    code: text('code').notNull(),
    expiresAt: integer('expires_at').notNull(),
  },
  table => ({
    index: uniqueIndex('idx_password_reset_userId_code').on(table.userId, table.code),
  }),
)

export const usersOauthTable = sqliteTable('users_oauth', {
  id: text('id').primaryKey(),
  userId: text('user_id')
    .notNull()
    .references(() => userTable.id),
  provider: text('provider').notNull(),
  providerUserId: text('provider_user_id').notNull(),
  accessToken: text('access_token').notNull(),
  refreshToken: text('refresh_token'),
  expiresAt: integer('expires_at'),
  created: integer('created_at').default(Date.now()),
  updated: integer('updated_at').default(Date.now()),
}, (table) => {
  return {
    uniqueProviderUserIndex: uniqueIndex('unique_provider_user_index').on(table.provider, table.providerUserId),
  }
})

export const mfaTable = sqliteTable('mfa', {
  id: text('id').primaryKey(),
  userId: text('user_id')
    .notNull()
    .references(() => userTable.id),
  type: text('type').notNull(),
  name: text('name').notNull(),
  status: text('status').notNull(),
  lastVerified: integer('last_verified_at'),
  lastMfaId: text('last_mfa_id'),
  created: integer('created_at').default(Date.now()),
  updated: integer('updated_at').default(Date.now()),
})

export const sessionTable = sqliteTable('session', {
  id: text('id').primaryKey(),
  userId: text('user_id')
    .notNull()
    .references(() => userTable.id),
  sessionToken: text('session_token').notNull(),
  created: integer('created_at').default(Date.now()),
  updated: integer('updated_at').default(Date.now()),
  expiresAt: integer('expires_at').notNull(),
  status: text('status').notNull(),
  date: integer('date').default(Date.now()),
  ip: text('ip_address').notNull(),
  country: text('country'),
  deviceInfo: text('device_info'),
})

export const activityLogTable = sqliteTable('activity_log', {
  id: text('id').primaryKey(),
  userId: text('user_id')
    .notNull()
    .references(() => userTable.id),
  action: text('action').notNull(),
  timestamp: integer('timestamp').default(Date.now()),
  ipAddress: text('ip_address').notNull(),
  details: text('details'),
  sessionId: text('session_id'),
})

最新 schema.ts 同步

/* eslint-disable ts/no-use-before-define */

import { blob, integer, sqliteTable, text, uniqueIndex } from 'drizzle-orm/sqlite-core'

export const userTable = sqliteTable(
  'user',
  {
    id: text('id').primaryKey(),
    email: text('email'),
    emailHash: text('email_hash'),
    phone: text('phone'),
    phoneHash: text('phone_hash'),
    oauth_register_id: text('oauth_register_id').references(() => usersOauthTable.id),
    password: text('password'),
    isEmailVerified: integer('is_email_verified').default(0),
    isPhoneVerified: integer('is_phone_verified').default(0),
    /**
     * - 0 disabled
     * - 1 activate
     */
    status: integer('status').default(0),
    nickname: text('nickname'),
    language: text('language'),
    country: text('country'),
    createdAt: integer('created_at').default(Date.now()),
    updatedAt: integer('updated_at').default(Date.now()),
    isDeleted: integer('is_deleted'),
  },
  (table) => {
    return {
      userUniqueIndex: uniqueIndex('user_unique_index').on(
        table.email,
        table.phone,
        table.oauth_register_id,
      ),
    }
  },
)

export const usersOauthTable = sqliteTable(
  'users_oauth',
  {
    id: text('id').primaryKey(),
    userId: text('user_id').references(() => userTable.id),
    provider: text('provider').notNull(),
    providerUserId: text('provider_user_id').notNull(),
    accessToken: text('access_token'),
    refreshToken: text('refresh_token'),
    expiresAt: integer('expires_at'),
    createdAt: integer('created_at').default(Date.now()),
    updatedAt: integer('updated_at').default(Date.now()),
    isDeleted: integer('is_deleted'),
  },
  (table) => {
    return {
      uniqueProviderUserIndex: uniqueIndex('unique_provider_user_index').on(
        table.provider,
        table.providerUserId,
      ),
    }
  },
)

export const verificationTable = sqliteTable(
  'verification',
  {
    id: text('id').primaryKey(),
    userId: text('user_id')
      .references(() => userTable.id),
    status: integer('status').default(0),
    verifyId: text('verify_id').notNull(),
    expiresAt: integer('expires_at').notNull(),
    createIp: text('create_ip'),
    verifyIp: text('verify_ip'),
    verifyData: text('verify_data'),
    /**
     * serial number
     */
    serial: text('serial'),
    /**
     * - email
     * - phone
     */
    type: text('type'),
    /**
     * Used to validate type records, Write routing address
     *
     * example:
     * - 1 retrieve password
     * - 2 modify email
     * - 3 modify phone
     * - 4 other
     */
    action: text('action'),
    createdAt: integer('created_at').default(Date.now()),
    updatedAt: integer('updated_at').default(Date.now()),
    isDeleted: integer('is_deleted'),
  },
)

export const mfaTable = sqliteTable(
  'mfa',
  {
    id: text('id').primaryKey(),
    userId: text('user_id')
      .notNull()
      .references(() => userTable.id),
    type: text('type').notNull(),
    name: text('name').notNull(),
    status: text('status').notNull(),
    value: text('value'),
    lastVerifiedAt: integer('last_verified_at'),
    createdAt: integer('created_at').default(Date.now()),
    updatedAt: integer('updated_at').default(Date.now()),
    isDeleted: integer('is_deleted'),
  },
)

export const sessionTable = sqliteTable(
  'session',
  {
    id: text('id').primaryKey(),
    userId: text('user_id')
      .notNull()
      .references(() => userTable.id),
    sessionToken: text('session_token').notNull(),
    expiresAt: integer('expires_at').notNull(),
    status: text('status').notNull(),
    mfaId: text('mfa_id')
      .references(() => mfaTable.id),
    // [
    //   {
    //     ip: '',
    //     country: '',
    //     deviceInfo: '',
    //     createdAt: Date.now(),
    //   },
    // ]
    metadata: blob('metadata').notNull(),
    createdAt: integer('created_at').default(Date.now()),
    updatedAt: integer('updated_at').default(Date.now()),
    isDeleted: integer('is_deleted'),
  },
)

export const activityLogTable = sqliteTable(
  'activity_log',
  {
    id: text('id').primaryKey(),
    userId: text('user_id')
      .notNull()
      .references(() => userTable.id),
    action: text('action').notNull(),
    details: text('details'),
    sessionId: text('session_id')
      .notNull()
      .references(() => sessionTable.id),
    createdAt: integer('created_at').default(Date.now()),
    isDeleted: integer('is_deleted'),
  },
)

export const tokens = sqliteTable(
  'tokens',
  {
    id: text('id').primaryKey(),
    userId: text('user_id')
      .notNull()
      .references(() => userTable.id),
    token: text('token').notNull(),
    resources: blob('resources'),
    accesses: blob('accesses'),
    expiresAt: integer('expires_at'),
    createdAt: integer('created_at').default(Date.now()),
    updatedAt: integer('updated_at').default(Date.now()),
    lastUsedAt: integer('last_used_at').default(Date.now()),
    isDeleted: integer('is_deleted'),
  },
)

ttpos / url

用户部分: 用户数据库设计 #18

数据表描述及功能

users 表

users_oidc 表

mfa 表

session 表

session_history 表

activity_log 表

users

session

tokens 表

tokens 表

改动点

1. users 表

2. 新的表

3. mfa 表

4. 字段类型

schema.ts