模型用户使用面板 - Admin API 设计

Context

管理员需要一个面板查看不同 AI 模型的用户使用情况，支持日期范围筛选、金额汇总和成功笔数统计。

涉及表：consumptions、ai_models、user_meta，无需其他表。

接口设计

GET /api/admin/model-usage

参数

所有筛选条件可自由组合。

使用场景

响应

{
  "data": {
    "date_range": { "start_date": "2026-03-18", "end_date": "2026-03-31" },
    "totals": {
      "total_calls": 120,
      "success_calls": 118,
      "total_amount": 15.5,
      "total_credits": 1550
    },
    "items": [
      {
        "user_id": "user_abc",
        "email": "test@example.com",
        "stat_date": "2026-03-24",
        "total_calls": 4,
        "success_calls": 4,
        "total_amount": 1.2,
        "total_credits": 120
      }
    ],
    "daily_stats": [
      { "date": "2026-03-23", "total_calls": 20, "success_calls": 19, "amount": 2.5, "credits": 250 },
      { "date": "2026-03-24", "total_calls": 35, "success_calls": 35, "amount": 4.8, "credits": 480 }
    ],
    "pageInfo": { "page": 1, "pageSize": 20, "total": 32 }
  }
}

字段说明：

• totals — 当前筛选条件下的汇总
• items — 按用户+日期聚合的明细列表（分页），每行 = 某用户某天的使用统计
• daily_stats — 每日趋势（用于图表，自动填充空日期为零值）
• pageInfo — items 的分页信息

文件结构

packages/server/src/
  pages/api/admin/
    model-usage/
      index.ts              # 路由入口
  services/admin/
    model-usage.ts          # 服务层

SQL 思路

模型标识提取

模型信息统一存储在 consumptions.input_meta JSON 的 $.model 字段中：

JSON_UNQUOTE(JSON_EXTRACT(c.input_meta, '$.model'))

共享 WHERE 构建

WHERE c.deleted_at IS NULL
  AND c.created_at >= :start_date
  AND c.created_at < DATE_ADD(:end_date, INTERVAL 1 DAY)
  -- 按需追加：
  AND JSON_UNQUOTE(JSON_EXTRACT(c.input_meta, '$.model')) IN (:model_keys)  -- 多选
  AND c.api_key_id IS [NOT] NULL                                            -- source
  AND (c.user_id LIKE :user_keyword OR um.email LIKE :user_keyword)         -- 用户搜索

当传了 category 时，额外 JOIN ai_models 并加 AND am.category = :category。

totals 查询

SELECT
  COUNT(*) AS total_calls,
  SUM(CASE WHEN c.complete_at IS NOT NULL THEN 1 ELSE 0 END) AS success_calls,
  COALESCE(SUM(c.task_used), 0) AS total_credits_db
FROM consumptions c
{JOINS}
{WHERE}

items 查询（按用户+日期聚合 + 分页）

SELECT
  c.user_id, um.email,
  DATE(c.created_at) AS stat_date,
  COUNT(*) AS total_calls,
  SUM(CASE WHEN c.complete_at IS NOT NULL THEN 1 ELSE 0 END) AS success_calls,
  COALESCE(SUM(c.task_used), 0) AS total_credits_db
FROM consumptions c
LEFT JOIN user_meta um ON um.user_id = c.user_id
{WHERE}
GROUP BY c.user_id, um.email, stat_date
ORDER BY :sortExpr :sortOrder
LIMIT :limit OFFSET :offset

daily_stats 查询

SELECT DATE(c.created_at) AS date,
  COUNT(*) AS total_calls,
  SUM(CASE WHEN c.complete_at IS NOT NULL THEN 1 ELSE 0 END) AS success_calls,
  COALESCE(SUM(c.task_used), 0) AS total_credits_db
FROM consumptions c
{JOINS}
{WHERE}
GROUP BY date
ORDER BY date ASC

应用层用 dayjs 循环填充空日期为零值。

三个查询并行执行（Promise.all）。

复用的现有工具

关键设计决策