01
export const roles = sqliteTable(
02
  'roles',
03
  {
04
    id: text('id').primaryKey(),
05
    applicationId: text('application_id')
06
      .notNull()
07
      .references(() => applications.id, { onDelete: 'cascade' }),
08
    code: text('code').notNull(), // 机器可读的角色标识，例如 admin_owner
09
    name: text('name').notNull(), // 给人看的名称，例如 Admin Owner
10
    status: text('status').notNull(), // active / disabled / deleted
11
    createdAtMs: integer('created_at_ms').notNull(),
12
    updatedAtMs: integer('updated_at_ms').notNull(),
13
    disabledAtMs: integer('disabled_at_ms'), // 角色被禁用的时间
14
    deletedAtMs: integer('deleted_at_ms'), // 角色被逻辑删除的时间
15
  },
16
  (table) => [uniqueIndex('idx_roles_application_code_unique').on(table.applicationId, table.code)],
17
)

01
export const userRoleBindings = sqliteTable(
02
  'user_role_bindings',
03
  {
04
    id: text('id').primaryKey(),
05
    userId: text('user_id')
06
      .notNull()
07
      .references(() => users.id, { onDelete: 'cascade' }),
08
    roleId: text('role_id')
09
      .notNull()
10
      .references(() => roles.id, { onDelete: 'cascade' }),
11
    status: text('status').notNull(), // active / revoked
12
    grantedAtMs: integer('granted_at_ms').notNull(),
13
    revokedAtMs: integer('revoked_at_ms'),
14
  },
15
  (table) => [uniqueIndex('idx_user_role_bindings_unique').on(table.userId, table.roleId)],
16
)

1
export const protectedRoleCodes = new Set(['admin_owner', 'web_user'])
2

3
export function isProtectedRole(code: string) {
4
  return protectedRoleCodes.has(code)
5
}

01
roleRoute.get('/list', async (c) => {
02
  // 1. 先过后台最高权限守卫
03
  await requireAdminOwner(c)
04

05
  // 2. 从数据库查角色列表
06
  const roles = await findRoleList(getDb(c.env.DB))
07

08
  // 3. 派生前端直接可用的字段
09
  const res = RoleListResponseSchema.parse({
10
    items: roles.map((role) => ({
11
      ...role,
12
      isProtected: isProtectedRole(role.code),
13
    })),
14
  })
15

16
  // 4. 返回统一响应结构
17
  return c.json(buildSuccess(res, createApiMeta()))
18
})

1
async function requireAdminOwner(c: Context<{ Bindings: ApiBindings }>) {
2
  const claims = await requireAccessTokenClaims(c)
3

4
  if (!claims.roles.includes('admin_owner')) {
5
    throw forbiddenError()
6
  }
7

8
  return claims
9
}

01
export async function findRoleList(db: ApiDb) {
02
  return db
03
    .select({
04
      id: roles.id,
05
      applicationCode: applications.code,
06
      code: roles.code,
07
      name: roles.name,
08
      status: roles.status,
09
      createdAtMs: roles.createdAtMs,
10
      updatedAtMs: roles.updatedAtMs,
11
      disabledAtMs: roles.disabledAtMs,
12
      deletedAtMs: roles.deletedAtMs,
13
    })
14
    .from(roles) // 从角色表开始查
15
    .innerJoin(applications, eq(applications.id, roles.applicationId)) // 补出角色属于哪个 application
16
    .where(sql`${roles.status} != 'deleted'`) // 已删除角色不进入列表
17
    .orderBy(sql`${applications.code} asc, ${roles.createdAtMs} desc, ${roles.id} desc`)
18
}

01
export async function disableRole(params: { db: ApiDb; roleId: string; nowMs: number }) {
02
  await params.db
03
    .update(roles)
04
    .set({
05
      status: 'disabled',
06
      updatedAtMs: params.nowMs,
07
      disabledAtMs: params.nowMs,
08
    })
09
    .where(eq(roles.id, params.roleId))
10
}
11

12
export async function deleteRole(params: { db: ApiDb; roleId: string; nowMs: number }) {
13
  await params.db
14
    .update(roles)
15
    .set({
16
      status: 'deleted',
17
      updatedAtMs: params.nowMs,
18
      deletedAtMs: params.nowMs,
19
    })
20
    .where(eq(roles.id, params.roleId))
21
}

01
export async function getAdminRolesForUser(db: ApiDb, userId: string): Promise<string[]> {
02
  const rows = await db
03
    .select({ code: roles.code })
04
    .from(userRoleBindings) // 这次从绑定表出发，因为问题变成了“这个用户有哪些角色”
05
    .innerJoin(roles, eq(roles.id, userRoleBindings.roleId))
06
    .innerJoin(applications, eq(applications.id, roles.applicationId))
07
    .where(
08
      and(
09
        eq(userRoleBindings.userId, userId),
10
        eq(userRoleBindings.status, 'active'), // 绑定关系本身还有效
11
        eq(roles.status, 'active'), // 角色本身也还有效
12
        eq(applications.code, 'admin'), // 只取 admin 侧角色
13
      ),
14
    )
15

16
  return rows.map((row) => row.code)
17
}