用户管理模块

-- 用户主表
CREATE TABLE users (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email         VARCHAR(255) NOT NULL,
  username      VARCHAR(100) NOT NULL,
  password_hash VARCHAR(255),            -- bcrypt/argon2 哈希，不存明文
  phone         VARCHAR(20),
  avatar_url    TEXT,
  status        VARCHAR(20) NOT NULL DEFAULT 'active',
                  -- active / disabled / pending_verification / deactivated
  email_verified BOOLEAN NOT NULL DEFAULT false,
  phone_verified BOOLEAN NOT NULL DEFAULT false,
  mfa_enabled   BOOLEAN NOT NULL DEFAULT false,
  registration_source VARCHAR(30) NOT NULL DEFAULT 'email',
                  -- email / google / github / apple
  last_login_at TIMESTAMPTZ,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at    TIMESTAMPTZ,             -- 软删除标记
  CONSTRAINT uq_users_email UNIQUE (email),
  CONSTRAINT uq_users_username UNIQUE (username)
);
 
-- 角色表
CREATE TABLE roles (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        VARCHAR(50) NOT NULL,       -- super_admin / admin / operator / developer
  display_name VARCHAR(100) NOT NULL,
  description TEXT,
  is_system   BOOLEAN NOT NULL DEFAULT false,  -- 系统内置角色不可删除
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT uq_roles_name UNIQUE (name)
);
 
-- 权限表
CREATE TABLE permissions (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  code        VARCHAR(100) NOT NULL,      -- user:read / user:update / user:delete
  name        VARCHAR(100) NOT NULL,
  module      VARCHAR(50) NOT NULL,       -- 所属模块：user / content / billing
  description TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT uq_permissions_code UNIQUE (code)
);
 
-- 角色-权限关联表
CREATE TABLE role_permissions (
  role_id       UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  permission_id UUID NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (role_id, permission_id)
);
 
-- 用户-角色关联表
CREATE TABLE user_roles (
  user_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role_id    UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  granted_by UUID NOT NULL REFERENCES users(id),  -- 谁分配的
  granted_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, role_id)
);

-- 高频查询字段索引
CREATE INDEX idx_users_email ON users (email) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_status ON users (status) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_created_at ON users (created_at DESC) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_last_login ON users (last_login_at DESC) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_registration_source ON users (registration_source) WHERE deleted_at IS NULL;
 
-- 模糊搜索加速（PostgreSQL pg_trgm 扩展）
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_username_trgm ON users USING gin (username gin_trgm_ops);
CREATE INDEX idx_users_email_trgm ON users USING gin (email gin_trgm_ops);
 
-- 复合索引：常见筛选组合
CREATE INDEX idx_users_status_created ON users (status, created_at DESC) WHERE deleted_at IS NULL;

// 权限校验中间件
function requirePermission(...requiredPermissions: string[]) {
  return async (c: Context, next: Next) => {
    const user = c.get('currentUser')
    const userPermissions = await getUserPermissions(user.id)
 
    const hasAll = requiredPermissions.every(p => userPermissions.includes(p))
    if (!hasAll) {
      return c.json({ error: '权限不足' }, 403)
    }
    await next()
  }
}
 
// 路由中使用
app.delete('/api/users/:id',
  requirePermission('user:delete'),
  async (c) => {
    const userId = c.req.param('id')
    await userService.softDelete(userId)
    return c.json({ success: true })
  }
)

// 数据权限中间件示例
function applyDataScope(query: SelectQuery, currentUser: User) {
  if (currentUser.isSuperAdmin) return query  // 超管无限制
 
  if (currentUser.scope === 'organization') {
    return query.where('organization_id', currentUser.organizationId)
  }
 
  // 默认只看自己
  return query.where('id', currentUser.id)
}

import { hash, verify } from '@node-rs/argon2'
 
// 密码哈希
const passwordHash = await hash(password, {
  algorithm: 'argon2id',
  memoryCost: 65536,    // 64MB
  timeCost: 3,
  parallelism: 4,
})
 
// 密码验证
const isValid = await verify(passwordHash, password)

-- 操作审计日志表
CREATE TABLE operation_logs (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID NOT NULL REFERENCES users(id),
  action      VARCHAR(50) NOT NULL,     -- user:disable / user:reset_password / user:assign_role
  target_type VARCHAR(50) NOT NULL,     -- user / role / permission
  target_id   UUID,                      -- 被操作对象的 ID
  detail      JSONB,                     -- 操作详情（变更前后对比）
  ip_address  INET,
  user_agent  TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
CREATE INDEX idx_operation_logs_user ON operation_logs (user_id, created_at DESC);
CREATE INDEX idx_operation_logs_action ON operation_logs (action, created_at DESC);

// apps/api/src/modules/user/service.ts
import { db } from '@/db'
import { users, userRoles, roles } from '@/db/schema'
import { and, eq, ilike, inArray, desc, isNull, sql } from 'drizzle-orm'
 
interface ListUsersParams {
  page: number
  pageSize: number
  keyword?: string
  status?: string[]
  source?: string[]
  roleIds?: string[]
  createdAfter?: string
  createdBefore?: string
  sortBy?: string
  sortOrder?: 'asc' | 'desc'
}
 
export async function listUsers(params: ListUsersParams) {
  const { page, pageSize, keyword, status, source, roleIds, createdAfter, createdBefore, sortBy = 'created_at', sortOrder = 'desc' } = params
 
  const conditions = [isNull(users.deletedAt)]
 
  // 关键词搜索
  if (keyword) {
    conditions.push(
      sql`(${ilike(users.username, `%${keyword}%`)} OR ${ilike(users.email, `%${keyword}%`)})`
    )
  }
 
  // 状态筛选
  if (status?.length) {
    conditions.push(inArray(users.status, status))
  }
 
  // 注册来源筛选
  if (source?.length) {
    conditions.push(inArray(users.registrationSource, source))
  }
 
  // 时间范围筛选
  if (createdAfter) {
    conditions.push(sql`${users.createdAt} >= ${createdAfter}::timestamptz`)
  }
  if (createdBefore) {
    conditions.push(sql`${users.createdAt} <= ${createdBefore}::timestamptz`)
  }
 
  const whereClause = and(...conditions)
 
  // 查询总数和数据
  const [totalResult, data] = await Promise.all([
    db.select({ count: sql<number>`count(*)` })
      .from(users)
      .where(whereClause),
    db.select({
      id: users.id,
      email: users.email,
      username: users.username,
      status: users.status,
      avatarUrl: users.avatarUrl,
      registrationSource: users.registrationSource,
      emailVerified: users.emailVerified,
      mfaEnabled: users.mfaEnabled,
      lastLoginAt: users.lastLoginAt,
      createdAt: users.createdAt,
    })
      .from(users)
      .where(whereClause)
      .orderBy(sortOrder === 'desc' ? desc(users[sortBy]) : users[sortBy])
      .limit(pageSize)
      .offset((page - 1) * pageSize),
  ])
 
  return {
    data,
    total: totalResult[0].count,
    page,
    pageSize,
  }
}

// apps/api/src/routes/admin/users/index.ts
import { Hono } from 'hono'
import { listUsers } from '@/modules/user/service'
import { requirePermission } from '@/middleware/permission'
 
const userRoutes = new Hono()
 
userRoutes.get('/',
  requirePermission('user:read'),
  async (c) => {
    const query = c.req.query()
    const result = await listUsers({
      page: Number(query.page) || 1,
      pageSize: Math.min(Number(query.pageSize) || 20, 100),
      keyword: query.keyword,
      status: query.status?.split(','),
      source: query.source?.split(','),
      roleIds: query.roleIds?.split(','),
      createdAfter: query.createdAfter,
      createdBefore: query.createdBefore,
      sortBy: query.sortBy,
      sortOrder: query.sortOrder as 'asc' | 'desc',
    })
    return c.json(result)
  }
)
 
export default userRoutes

// apps/web/src/app/[locale]/(admin)/admin/users/_components/user-table.tsx
'use client'
 
import { useQuery } from '@tanstack/react-query'
import { useSearchParams, useRouter } from 'next/navigation'
 
function useUserList() {
  const searchParams = useSearchParams()
  const router = useRouter()
 
  const params = {
    page: Number(searchParams.get('page')) || 1,
    pageSize: Number(searchParams.get('pageSize')) || 20,
    keyword: searchParams.get('keyword') || undefined,
    status: searchParams.get('status')?.split(',') || undefined,
    source: searchParams.get('source')?.split(',') || undefined,
    createdAfter: searchParams.get('createdAfter') || undefined,
    createdBefore: searchParams.get('createdBefore') || undefined,
  }
 
  return useQuery({
    queryKey: ['admin-users', params],
    queryFn: () => fetchUsers(params),
    placeholderData: (prev) => prev,  // 避免切换页码时闪烁
  })
}

// apps/api/src/modules/user/service.ts
export async function disableUser(operatorId: string, targetUserId: string) {
  // 不允许禁用自己
  if (operatorId === targetUserId) {
    throw new Error('不能禁用自己的账户')
  }
 
  // 不允许禁用超级管理员（如果操作者不是超管）
  const targetRoles = await getUserRoles(targetUserId)
  const operatorRoles = await getUserRoles(operatorId)
  if (targetRoles.some(r => r.name === 'super_admin') &&
      !operatorRoles.some(r => r.name === 'super_admin')) {
    throw new Error('权限不足，无法禁用超级管理员')
  }
 
  // 事务：更新状态 + 记录审计日志 + 销毁会话
  await db.transaction(async (tx) => {
    await tx.update(users)
      .set({ status: 'disabled', updatedAt: new Date() })
      .where(eq(users.id, targetUserId))
 
    await tx.insert(operationLogs).values({
      userId: operatorId,
      action: 'user:disable',
      targetType: 'user',
      targetId: targetUserId,
      detail: { before: { status: 'active' }, after: { status: 'disabled' } },
    })
 
    // 销毁该用户所有活跃会话
    await tx.delete(sessions)
      .where(eq(sessions.userId, targetUserId))
  })
}

CREATE TABLE organizations (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        VARCHAR(100) NOT NULL,
  plan        VARCHAR(20) NOT NULL DEFAULT 'free',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
CREATE TABLE organization_members (
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  org_role        VARCHAR(20) NOT NULL DEFAULT 'member',  -- owner / admin / member / viewer
  joined_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (organization_id, user_id)
);