Prompt-to-PR：添加管理数据表

用于在Next.js中使用TanStack Table和PostgreSQL查询添加服务器端分页、可排序、可搜索的管理数据表的SOP——无ORM魔术。

管理表是常见的AI编码任务，但经常产生不安全的、完全客户端实现。本攻略将分页和排序保持在服务器端，并显式保护路由。

1. 需求

添加一个仅对拥有role = "admin"的用户可访问的/admin/users页面。该表为服务器端分页（每页25行）、可按列排序、可按电子邮件搜索。数据通过参数化查询从PostgreSQL的users表中获取——不使用未参数化的SQL。

2. 首次提示

Add an admin users table to this Next.js 15 App Router project.

Requirements:
1. Create `src/app/admin/users/page.tsx` — an async Server Component.
   - Protect it: check the session (using the existing auth helper at
     src/lib/auth.ts). If no session or session.user.role !== "admin",
     call notFound() or redirect to /.
   - Read `page` (default 1), `sort` (default "createdAt"), `order`
     ("asc"|"desc", default "desc"), and `q` (search string) from
     `searchParams`.
   - Fetch data by calling a function `getUsers({ page, sort, order, q })`
     from `src/lib/queries/users.ts`.
   - Pass results to `<UsersTable>` client component.
2. Create `src/lib/queries/users.ts` exporting `getUsers`.
   - Use parameterized SQL via the existing db client (Drizzle or postgres.js,
     whichever is in this project).
   - Allowed sort columns whitelist: ["email","name","createdAt","role"].
     Reject any other sort value by defaulting to "createdAt".
   - Return `{ rows: User[], total: number }`.
   - Page size: 25.
3. Create `src/components/admin/UsersTable.tsx` — a Client Component using
   TanStack Table v8.
   - Columns: email, name, role, createdAt (formatted), actions (Edit link).
   - Column headers are clickable to sort — update URL search params via
     useRouter/useSearchParams (do not manage sort state locally).
   - Render a search input that debounces 300 ms before updating the URL.
   - Render a pagination row: Previous / Next and "Page N of M".
4. Install `@tanstack/react-table` if not already present.
5. Do not implement the Edit action — leave it as a placeholder link.

3. 预期文件变更

package.json                                   (@tanstack/react-table if missing)
src/app/admin/users/page.tsx                   (new — protected Server Component)
src/lib/queries/users.ts                       (new — parameterized query)
src/components/admin/UsersTable.tsx            (new — TanStack Table client component)

4. 审查清单

页面在服务器端检查session.user.role === "admin"——无仅客户端保护。
getUsers在插入SQL之前白名单允许的排序列——列名中无原始用户输入。
所有WHERE/LIMIT/OFFSET值使用参数化占位符，而非字符串拼接。
搜索(q)使用带参数的ILIKE '%' || $1 || '%'，而非字符串插值。
仅在"use client"组件中使用TanStack Table——服务器组件中无@tanstack/react-table导入。
URL搜索参数驱动排序/页面/搜索状态——浏览器返回按钮正常工作。
total计数来自同一查询函数中的SELECT COUNT(*)——并非与分页查询分离的全表扫描。

5. 测试命令

bun dev

# Hit the page as an unauthenticated user
curl -I http://localhost:3000/admin/users
# Expect: 404 or redirect, not 200

# Log in as an admin user in the browser, visit /admin/users
# Confirm: 25 rows, sortable columns, search, pagination

# SQL audit — confirm no dynamic column names slip through
grep -n "sort\|order\|column" src/lib/queries/users.ts

bun tsc --noEmit
bun test

6. 常见失败

通过排序列的SQL注入——代理直接插值sort：`ORDER BY ${sort}`。必须使用白名单查找至安全的列名字符串。
searchParams在Next.js 15+中为异步——必须使用await：const { page } = await searchParams。
TanStack Table在服务器组件中渲染——因使用useState导致构建错误。将其移至"use client"组件。
total计数差一页——若total为0，Math.ceil(total / 25)给出错误页数。保护：Math.max(1, Math.ceil(total / 25))。
缺少角色检查——代理仅检查会话，而非管理员角色。任何登录用户均可访问该表。

7. 修复提示

The sort column in getUsers is interpolated directly into SQL:
  `ORDER BY ${sort} ${order}`
This is a SQL injection risk.

Fix: create an allowlist object:
  const ALLOWED_SORT: Record<string, string> = {
    email: "users.email",
    name: "users.name",
    createdAt: "users.created_at",
    role: "users.role",
  };
  const safeSort = ALLOWED_SORT[sort] ?? "users.created_at";

Then use `safeSort` in the query. The order direction must also be
validated: only accept "asc" or "desc", default to "desc".

8. PR描述

## Feature: Admin users table at /admin/users

- Server-side pagination (25 rows/page), sorting, and email search
- Role guard: redirects non-admins server-side (no client-only check)
- Parameterized SQL with sort-column whitelist — no injection surface
- TanStack Table v8 client component; sort/page/search state lives in URL
- `getUsers` returns `{ rows, total }` from a single query function