VPS Monitor 改造记：Token 用量 + 访问分析 + GA 风格 UI

第 2 页：Token 用量功能

数据来源

• one-api 的 SQLite 数据库：/home/user/one-api/one-api.db
• logs 表，时间字段 created_at（Unix 时间戳）
• 覆盖 6.5 个月历史

功能

• 时间范围：今週 / 今月 / 3ヶ月 / 6ヶ月 / 全部
• Summary 4 卡：总请求、输入 Token、输出 Token、合计
• 3 张图并排一行：应用别 / 模型别 / 日别趋势
• 应用明细表

API 源码（零依赖 SQLite）

import { NextResponse } from "next/server";
import fs from "fs";

export const dynamic = "force-dynamic";
export const runtime = "nodejs";

const DB_FILE = "/home/user/one-api/one-api.db";

type Range = "week" | "month" | "3m" | "6m" | "all";

function rangeToSeconds(range: Range): number {
  switch (range) {
    case "week": return 7 * 86400;
    case "month": return 30 * 86400;
    case "3m": return 90 * 86400;
    case "6m": return 180 * 86400;
    default: return 36500 * 86400;
  }
}

let _sqlite: any = null;
function loadSqlite() {
  if (_sqlite) return _sqlite;
  const req = eval("require");
  _sqlite = req("node:sqlite");
  return _sqlite;
}

export async function GET(req: Request) {
  const url = new URL(req.url);
  const range = (url.searchParams.get("range") || "month") as Range;

  if (!fs.existsSync(DB_FILE)) {
    return NextResponse.json({ error: "DB not found" }, { status: 404 });
  }

  const since = Math.floor(Date.now() / 1000) - rangeToSeconds(range);

  try {
    const { DatabaseSync } = loadSqlite();
    const db = new DatabaseSync(DB_FILE, { readOnly: true });

    try {
      const summary = db.prepare(
        "SELECT COUNT(*) AS total_requests, " +
        "COALESCE(SUM(prompt_tokens),0) AS total_input_tokens, " +
        "COALESCE(SUM(completion_tokens),0) AS total_output_tokens, " +
        "COALESCE(SUM(prompt_tokens + completion_tokens),0) AS total_tokens " +
        "FROM logs WHERE created_at >= ?"
      ).get(since);

      const byApp = db.prepare(
        "SELECT " +
        "CASE WHEN token_name IS NULL OR token_name = '' THEN 'unknown' ELSE token_name END AS app_name, " +
        "COUNT(*) AS requests, " +
        "COALESCE(SUM(prompt_tokens),0) AS input_tokens, " +
        "COALESCE(SUM(completion_tokens),0) AS output_tokens, " +
        "COALESCE(SUM(prompt_tokens + completion_tokens),0) AS total_tokens " +
        "FROM logs WHERE created_at >= ? " +
        "GROUP BY CASE WHEN token_name IS NULL OR token_name = '' THEN 'unknown' ELSE token_name END " +
        "ORDER BY total_tokens DESC LIMIT 20"
      ).all(since);

      const byModel = db.prepare(
        "SELECT " +
        "CASE WHEN model_name IS NULL OR model_name = '' THEN 'unknown' ELSE model_name END AS model, " +
        "COUNT(*) AS requests, " +
        "COALESCE(SUM(prompt_tokens),0) AS input_tokens, " +
        "COALESCE(SUM(completion_tokens),0) AS output_tokens, " +
        "COALESCE(SUM(prompt_tokens + completion_tokens),0) AS total_tokens " +
        "FROM logs WHERE created_at >= ? " +
        "GROUP BY CASE WHEN model_name IS NULL OR model_name = '' THEN 'unknown' ELSE model_name END " +
        "HAVING total_tokens > 0 " +
        "ORDER BY total_tokens DESC LIMIT 20"
      ).all(since);

      const byDate = db.prepare(
        "SELECT " +
        "DATE(created_at, 'unixepoch', 'localtime') AS date, " +
        "COUNT(*) AS requests, " +
        "COALESCE(SUM(prompt_tokens),0) AS input_tokens, " +
        "COALESCE(SUM(completion_tokens),0) AS output_tokens, " +
        "COALESCE(SUM(prompt_tokens + completion_tokens),0) AS total_tokens " +
        "FROM logs WHERE created_at >= ? " +
        "GROUP BY DATE(created_at, 'unixepoch', 'localtime') " +
        "ORDER BY date ASC"
      ).all(since);

      return NextResponse.json({
        range,
        timestamp: new Date().toISOString(),
        summary,
        by_app: byApp,
        by_model: byModel,
        by_date: byDate,
      });
    } finally {
      db.close();
    }
  } catch (e: any) {
    return NextResponse.json({ error: e.message, stack: String(e.stack || "") }, { status: 500 });
  }
}