24.03-用户体系

-- 用户表
CREATE TABLE users (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  email TEXT NOT NULL,
  password_hash TEXT NOT NULL,
  name TEXT,
  role TEXT NOT NULL DEFAULT 'member',  -- admin/member/viewer
  status TEXT NOT NULL DEFAULT 'active',  -- active/invited/suspended
  last_login_at INTEGER,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  UNIQUE(tenant_id, email)  -- 同一租户内邮箱唯一
)
 
-- 用户会话表（可选，用于主动失效 token）
CREATE TABLE sessions (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  token_hash TEXT NOT NULL,
  expires_at INTEGER NOT NULL,
  created_at INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
)

// src/types/user.ts
export interface User {
  id: string
  tenantId: string
  email: string
  passwordHash: string
  name?: string
  role: UserRole
  status: UserStatus
  lastLoginAt?: number
  createdAt: number
  updatedAt: number
}
 
export type UserRole = 'admin' | 'member' | 'viewer'
 
export type UserStatus = 'active' | 'invited' | 'suspended'
 
// 返回给前端的用户信息（不包含敏感字段）
export interface UserProfile {
  id: string
  email: string
  name?: string
  role: UserRole
  status: UserStatus
  lastLoginAt?: number
}

// src/lib/auth.ts
import { hash, compare } from 'bcryptjs'
 
export async function hashPassword(password: string): Promise<string> {
  return hash(password, 10)  // salt rounds
}
 
export async function verifyPassword(
  password: string,
  hash: string
): Promise<boolean> {
  return compare(password, hash)
}

// src/routes/auth.ts
import { Hono } from 'hono'
import { zValidator } from '@hono/zod-validator'
import { z } from 'zod'
import { sign } from 'hono/jwt'
import { verifyPassword } from '../lib/auth'
 
const app = new Hono()
 
app.post('/login', zValidator('json', z.object({
  email: z.string().email(),
  password: z.string(),
})), async (c) => {
  const { email, password } = c.req.valid('json')
 
  // 1. 查找用户
  const user = await c.env.DB.prepare(`
    SELECT * FROM users WHERE email = ?
  `).bind(email).first()
 
  if (!user) {
    return c.json({ error: '邮箱或密码错误' }, 401)
  }
 
  // 2. 验证密码
  const valid = await verifyPassword(password, user.password_hash)
  if (!valid) {
    return c.json({ error: '邮箱或密码错误' }, 401)
  }
 
  // 3. 检查用户状态
  if (user.status !== 'active') {
    return c.json({ error: '账号已被禁用' }, 403)
  }
 
  // 4. 生成 JWT
  const token = await sign(
    {
      userId: user.id,
      tenantId: user.tenant_id,
      exp: Math.floor(Date.now() / 1000) + 60 * 60 * 24 * 7,  // 7 天
    },
    c.env.JWT_SECRET
  )
 
  // 5. 更新最后登录时间
  await c.env.DB.prepare(`
    UPDATE users SET last_login_at = ? WHERE id = ?
  `).bind(Date.now(), user.id).run()
 
  return c.json({
    token,
    user: {
      id: user.id,
      email: user.email,
      name: user.name,
      role: user.role,
      tenantId: user.tenant_id,
    },
  })
})

// src/middleware/auth.ts
import { Context, Next } from 'hono'
import { verify } from 'hono/jwt'
 
export async function authMiddleware(c: Context, next: Next) {
  const authHeader = c.req.header('Authorization')
 
  if (!authHeader?.startsWith('Bearer ')) {
    return c.json({ error: 'Unauthorized' }, 401)
  }
 
  const token = authHeader.slice(7)
 
  try {
    const payload = await verify(token, c.env.JWT_SECRET)
    const userId = payload.userId as string
    const tenantId = payload.tenantId as string
 
    // 验证用户是否存在且状态正常
    const user = await c.env.DB.prepare(`
      SELECT id, status FROM users WHERE id = ? AND tenant_id = ?
    `).bind(userId, tenantId).first()
 
    if (!user) {
      return c.json({ error: 'User not found' }, 404)
    }
 
    if (user.status !== 'active') {
      return c.json({ error: 'Account is suspended' }, 403)
    }
 
    // 存储到上下文
    c.set('userId', userId)
    c.set('tenantId', tenantId)
 
    await next()
  } catch (error) {
    return c.json({ error: 'Invalid token' }, 401)
  }
}

// src/routes/users.ts
import { Hono } from 'hono'
import { zValidator } from '@hono/zod-validator'
import { z } from 'zod'
import { generateId } from '../lib/utils'
import { sign } from 'hono/jwt'
 
const app = new Hono()
 
// 邀请用户
app.post('/invite', zValidator('json', z.object({
  email: z.string().email(),
  role: z.enum(['admin', 'member', 'viewer']),
})), async (c) => {
  const tenantId = c.get('tenantId')
  const inviterId = c.get('userId')
  const { email, role } = c.req.valid('json')
 
  // 1. 检查用户是否已存在
  const existing = await c.env.DB.prepare(`
    SELECT id FROM users WHERE tenant_id = ? AND email = ?
  `).bind(tenantId, email).first()
 
  if (existing) {
    return c.json({ error: '用户已存在' }, 409)
  }
 
  // 2. 创建邀请记录（状态为 invited）
  const userId = generateId()
  const now = Date.now()
 
  await c.env.DB.prepare(`
    INSERT INTO users (id, tenant_id, email, password_hash, name, role, status, created_at, updated_at)
    VALUES (?, ?, ?, '', NULL, ?, 'invited', ?, ?)
  `).bind(userId, tenantId, email, role, now, now).run()
 
  // 3. 生成邀请 token（24 小时有效）
  const inviteToken = await sign(
    {
      userId,
      tenantId,
      email,
      exp: Math.floor(Date.now() / 1000) + 60 * 60 * 24,
    },
    c.env.JWT_SECRET
  )
 
  // 4. 发送邀请邮件
  const inviteUrl = `${process.env.FRONTEND_URL}/invite?token=${inviteToken}`
  c.executionCtx.waitUntil(
    sendInviteEmail(email, inviteUrl, tenantId)
  )
 
  return c.json({ success: true, inviteUrl })
})
 
// 接受邀请（设置密码）
app.post('/accept-invite', zValidator('json', z.object({
  token: z.string(),
  password: z.string().min(8),
  name: z.string().optional(),
})), async (c) => {
  const { token, password, name } = c.req.valid('json')
 
  // 1. 验证 token
  const payload = await verify(token, c.env.JWT_SECRET)
  const { userId, tenantId, email } = payload
 
  // 2. 查找用户
  const user = await c.env.DB.prepare(`
    SELECT * FROM users WHERE id = ? AND status = 'invited'
  `).bind(userId).first()
 
  if (!user) {
    return c.json({ error: '邀请无效或已过期' }, 400)
  }
 
  // 3. 设置密码，激活用户
  const passwordHash = await hashPassword(password)
  const now = Date.now()
 
  await c.env.DB.prepare(`
    UPDATE users
    SET password_hash = ?, name = ?, status = 'active', updated_at = ?
    WHERE id = ?
  `).bind(passwordHash, name || null, now, userId).run()
 
  return c.json({ success: true })
})

// src/middleware/permission.ts
import { Context, Next } from 'hono'
 
export function requireRole(...roles: string[]) {
  return async (c: Context, next: Next) => {
    const userId = c.get('userId')
    const tenantId = c.get('tenantId')
 
    const user = await c.env.DB.prepare(`
      SELECT role FROM users WHERE id = ? AND tenant_id = ?
    `).bind(userId, tenantId).first()
 
    if (!user || !roles.includes(user.role)) {
      return c.json({ error: 'Forbidden' }, 403)
    }
 
    await next()
  }
}
 
// 使用
app.delete('/users/:id', requireRole('admin'), async (c) => {
  const userId = c.req.param('id')
  // 只有 admin 可以删除用户
})
 
app.get('/settings', requireRole('admin', 'member'), async (c) => {
  // admin 和 member 可以访问
})

// src/types/permission.ts
export type Permission =
  | 'user:read'
  | 'user:write'
  | 'user:delete'
  | 'config:read'
  | 'config:write'
  | 'chat:use'
  | 'rag:use'
  | 'rag:manage'
 
export const ROLE_PERMISSIONS: Record<string, Permission[]> = {
  admin: [
    'user:read', 'user:write', 'user:delete',
    'config:read', 'config:write',
    'chat:use', 'rag:use', 'rag:manage',
  ],
  member: [
    'user:read',
    'config:read',
    'chat:use', 'rag:use',
  ],
  viewer: [
    'user:read',
    'config:read',
    'rag:use',  // 只能使用，不能管理
  ],
}
 
export function hasPermission(role: string, permission: Permission): boolean {
  const permissions = ROLE_PERMISSIONS[role] || []
  return permissions.includes(permission)
}

// 使用
app.post('/rag/knowledge-base', async (c) => {
  const userId = c.get('userId')
  const tenantId = c.get('tenantId')
 
  const user = await c.env.DB.prepare(`
    SELECT role FROM users WHERE id = ? AND tenant_id = ?
  `).bind(userId, tenantId).first()
 
  if (!hasPermission(user.role, 'rag:manage')) {
    return c.json({ error: 'Forbidden' }, 403)
  }
 
  // 创建知识库
})

// src/routes/users.ts
app.get('/', async (c) => {
  const tenantId = c.get('tenantId')
  const page = parseInt(c.req.query('page') || '1')
  const pageSize = parseInt(c.req.query('pageSize') || '20')
  const offset = (page - 1) * pageSize
 
  const users = await c.env.DB.prepare(`
    SELECT id, email, name, role, status, last_login_at, created_at
    FROM users
    WHERE tenant_id = ?
    ORDER BY created_at DESC
    LIMIT ? OFFSET ?
  `).bind(tenantId, pageSize, offset).all()
 
  const total = await c.env.DB.prepare(`
    SELECT COUNT(*) as count FROM users WHERE tenant_id = ?
  `).bind(tenantId).first()
 
  return c.json({
    users: users.results,
    pagination: {
      page,
      pageSize,
      total: total.count,
    },
  })
})

app.put('/me', zValidator('json', z.object({
  name: z.string().optional(),
})), async (c) => {
  const userId = c.get('userId')
  const { name } = c.req.valid('json')
 
  await c.env.DB.prepare(`
    UPDATE users SET name = ?, updated_at = ? WHERE id = ?
  `).bind(name, Date.now(), userId).run()
 
  return c.json({ success: true })
})
 
// 管理员更新其他用户
app.put('/:id', requireRole('admin'), zValidator('json', z.object({
  role: z.enum(['admin', 'member', 'viewer']).optional(),
  status: z.enum(['active', 'suspended']).optional(),
})), async (c) => {
  const targetUserId = c.req.param('id')
  const tenantId = c.get('tenantId')
  const updates = c.req.valid('json')
 
  // 不能修改自己的角色
  if (targetUserId === c.get('userId') && updates.role) {
    return c.json({ error: 'Cannot change your own role' }, 400)
  }
 
  const setClauses = []
  const values = []
 
  if (updates.role) {
    setClauses.push('role = ?')
    values.push(updates.role)
  }
 
  if (updates.status) {
    setClauses.push('status = ?')
    values.push(updates.status)
  }
 
  if (setClauses.length === 0) {
    return c.json({ error: 'No updates provided' }, 400)
  }
 
  setClauses.push('updated_at = ?')
  values.push(Date.now())
  values.push(targetUserId)
  values.push(tenantId)
 
  await c.env.DB.prepare(`
    UPDATE users SET ${setClauses.join(', ')} WHERE id = ? AND tenant_id = ?
  `).bind(...values).run()
 
  return c.json({ success: true })
})

app.delete('/:id', requireRole('admin'), async (c) => {
  const targetUserId = c.req.param('id')
  const tenantId = c.get('tenantId')
  const currentUserId = c.get('userId')
 
  // 不能删除自己
  if (targetUserId === currentUserId) {
    return c.json({ error: 'Cannot delete yourself' }, 400)
  }
 
  // 检查是否是最后一个 admin
  const adminCount = await c.env.DB.prepare(`
    SELECT COUNT(*) as count FROM users
    WHERE tenant_id = ? AND role = 'admin' AND status = 'active'
  `).bind(tenantId).first()
 
  const targetUser = await c.env.DB.prepare(`
    SELECT role FROM users WHERE id = ? AND tenant_id = ?
  `).bind(targetUserId, tenantId).first()
 
  if (targetUser.role === 'admin' && adminCount.count === 1) {
    return c.json({ error: 'Cannot delete the last admin' }, 400)
  }
 
  await c.env.DB.prepare(`
    DELETE FROM users WHERE id = ? AND tenant_id = ?
  `).bind(targetUserId, tenantId).run()
 
  return c.json({ success: true })
})

app.post('/change-password', zValidator('json', z.object({
  oldPassword: z.string(),
  newPassword: z.string().min(8),
})), async (c) => {
  const userId = c.get('userId')
  const { oldPassword, newPassword } = c.req.valid('json')
 
  // 1. 获取用户
  const user = await c.env.DB.prepare(`
    SELECT password_hash FROM users WHERE id = ?
  `).bind(userId).first()
 
  // 2. 验证旧密码
  const valid = await verifyPassword(oldPassword, user.password_hash)
  if (!valid) {
    return c.json({ error: '旧密码错误' }, 400)
  }
 
  // 3. 更新密码
  const newPasswordHash = await hashPassword(newPassword)
  await c.env.DB.prepare(`
    UPDATE users SET password_hash = ?, updated_at = ? WHERE id = ?
  `).bind(newPasswordHash, Date.now(), userId).run()
 
  return c.json({ success: true })
})

// src/routes/users.ts
import { Hono } from 'hono'
import { zValidator } from '@hono/zod-validator'
import { z } from 'zod'
import { generateId } from '../lib/utils'
import { hashPassword, verifyPassword } from '../lib/auth'
import { requireRole } from '../middleware/permission'
import { sign, verify } from 'hono/jwt'
 
const app = new Hono()
 
// 获取用户列表
app.get('/', async (c) => {
  const tenantId = c.get('tenantId')
  const users = await c.env.DB.prepare(`
    SELECT id, email, name, role, status, last_login_at, created_at
    FROM users WHERE tenant_id = ?
    ORDER BY created_at DESC
  `).bind(tenantId).all()
 
  return c.json(users.results)
})
 
// 获取当前用户信息
app.get('/me', async (c) => {
  const userId = c.get('userId')
  const user = await c.env.DB.prepare(`
    SELECT id, email, name, role, status, last_login_at
    FROM users WHERE id = ?
  `).bind(userId).first()
 
  return c.json(user)
})
 
// 更新当前用户信息
app.put('/me', zValidator('json', z.object({
  name: z.string().optional(),
})), async (c) => {
  const userId = c.get('userId')
  const { name } = c.req.valid('json')
 
  await c.env.DB.prepare(`
    UPDATE users SET name = ?, updated_at = ? WHERE id = ?
  `).bind(name, Date.now(), userId).run()
 
  return c.json({ success: true })
})
 
// 邀请用户
app.post('/invite', requireRole('admin'), zValidator('json', z.object({
  email: z.string().email(),
  role: z.enum(['admin', 'member', 'viewer']),
})), async (c) => {
  const tenantId = c.get('tenantId')
  const { email, role } = c.req.valid('json')
 
  const existing = await c.env.DB.prepare(`
    SELECT id FROM users WHERE tenant_id = ? AND email = ?
  `).bind(tenantId, email).first()
 
  if (existing) {
    return c.json({ error: '用户已存在' }, 409)
  }
 
  const userId = generateId()
  const now = Date.now()
 
  await c.env.DB.prepare(`
    INSERT INTO users (id, tenant_id, email, password_hash, role, status, created_at, updated_at)
    VALUES (?, ?, ?, '', ?, 'invited', ?, ?)
  `).bind(userId, tenantId, email, role, now, now).run()
 
  const inviteToken = await sign(
    { userId, tenantId, email, exp: Math.floor(Date.now() / 1000) + 86400 },
    c.env.JWT_SECRET
  )
 
  c.executionCtx.waitUntil(sendInviteEmail(email, inviteToken))
 
  return c.json({ success: true })
})
 
// 接受邀请
app.post('/accept-invite', zValidator('json', z.object({
  token: z.string(),
  password: z.string().min(8),
  name: z.string().optional(),
})), async (c) => {
  const { token, password, name } = c.req.valid('json')
  const payload = await verify(token, c.env.JWT_SECRET)
 
  const passwordHash = await hashPassword(password)
  await c.env.DB.prepare(`
    UPDATE users
    SET password_hash = ?, name = ?, status = 'active', updated_at = ?
    WHERE id = ? AND status = 'invited'
  `).bind(passwordHash, name || null, Date.now(), payload.userId).run()
 
  return c.json({ success: true })
})
 
// 修改密码
app.post('/change-password', zValidator('json', z.object({
  oldPassword: z.string(),
  newPassword: z.string().min(8),
})), async (c) => {
  const userId = c.get('userId')
  const { oldPassword, newPassword } = c.req.valid('json')
 
  const user = await c.env.DB.prepare(`
    SELECT password_hash FROM users WHERE id = ?
  `).bind(userId).first()
 
  const valid = await verifyPassword(oldPassword, user.password_hash)
  if (!valid) {
    return c.json({ error: '旧密码错误' }, 400)
  }
 
  const newPasswordHash = await hashPassword(newPassword)
  await c.env.DB.prepare(`
    UPDATE users SET password_hash = ?, updated_at = ? WHERE id = ?
  `).bind(newPasswordHash, Date.now(), userId).run()
 
  return c.json({ success: true })
})
 
// 管理员更新用户
app.put('/:id', requireRole('admin'), zValidator('json', z.object({
  role: z.enum(['admin', 'member', 'viewer']).optional(),
  status: z.enum(['active', 'suspended']).optional(),
})), async (c) => {
  const targetUserId = c.req.param('id')
  const tenantId = c.get('tenantId')
  const updates = c.req.valid('json')
 
  const setClauses = []
  const values = []
 
  if (updates.role) {
    setClauses.push('role = ?')
    values.push(updates.role)
  }
 
  if (updates.status) {
    setClauses.push('status = ?')
    values.push(updates.status)
  }
 
  if (setClauses.length === 0) {
    return c.json({ error: 'No updates' }, 400)
  }
 
  setClauses.push('updated_at = ?')
  values.push(Date.now())
  values.push(targetUserId)
  values.push(tenantId)
 
  await c.env.DB.prepare(`
    UPDATE users SET ${setClauses.join(', ')} WHERE id = ? AND tenant_id = ?
  `).bind(...values).run()
 
  return c.json({ success: true })
})
 
// 删除用户
app.delete('/:id', requireRole('admin'), async (c) => {
  const targetUserId = c.req.param('id')
  const tenantId = c.get('tenantId')
  const currentUserId = c.get('userId')
 
  if (targetUserId === currentUserId) {
    return c.json({ error: 'Cannot delete yourself' }, 400)
  }
 
  await c.env.DB.prepare(`
    DELETE FROM users WHERE id = ? AND tenant_id = ?
  `).bind(targetUserId, tenantId).run()
 
  return c.json({ success: true })
})
 
export default app