AgentStatsService.php 18 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331
  1. <?php
  2. namespace App\Services\V3;
  3. use App\Models\AgentRun;
  4. use App\Services\UserService;
  5. use Carbon\CarbonImmutable;
  6. use Illuminate\Contracts\Pagination\LengthAwarePaginator;
  7. use Illuminate\Database\Eloquent\ModelNotFoundException;
  8. use Illuminate\Database\Query\Builder;
  9. use Illuminate\Support\Facades\Cache;
  10. use Illuminate\Support\Facades\DB;
  11. /**
  12. * 管理后台「AI 对话统计」与研究过程查阅。时间范围按 UTC 日,闭区间 [from, to],
  13. * 以提问开始时间 started_at 为准(入库时总会填,缺省取入库时刻)。
  14. *
  15. * 数据来源:agent_runs(一次提问)、agent_threads(account_type + account_id = 谁问的)、agent_run_steps(工具调用)、
  16. * credit_ledger(积分流水)。设计见 agent-poc 仓库 docs/trace.md、credits.md。
  17. *
  18. * 统计结果缓存 10 分钟;提问列表与详情不缓存。
  19. */
  20. class AgentStatsService
  21. {
  22. private const CACHE_SECONDS = 600;
  23. /**
  24. * @param 'all'|'user'|'guest' $accountType
  25. * @return array<string, mixed>
  26. */
  27. public function stats(CarbonImmutable $from, CarbonImmutable $to, string $accountType): array
  28. {
  29. $key = "admin:agent-stats:{$from->toDateString()}:{$to->toDateString()}:{$accountType}";
  30. return Cache::remember($key, self::CACHE_SECONDS, function () use ($from, $to, $accountType) {
  31. $days = $from->diffInDays($to) + 1;
  32. $prevTo = $from->subDay();
  33. $prevFrom = $prevTo->subDays($days - 1);
  34. return [
  35. 'range' => ['from' => $from->toDateString(), 'to' => $to->toDateString(), 'account_type' => $accountType],
  36. 'summary' => $this->summary($from, $to, $accountType),
  37. 'previous' => $this->summary($prevFrom, $prevTo, $accountType),
  38. 'daily' => $this->daily($from, $to, $accountType),
  39. 'clients' => $this->runs($from, $to, $accountType)
  40. ->selectRaw("coalesce(r.client, 'unknown') as client, count(*) as runs")->groupByRaw('1')
  41. ->orderByDesc('runs')->get()->map(fn ($r) => ['client' => $r->client, 'runs' => (int) $r->runs])->all(),
  42. 'tools' => $this->tools($from, $to, $accountType),
  43. 'feedback_reasons' => $this->feedbackReasons($from, $to, $accountType),
  44. 'credits' => $this->creditFlows($from, $to, $accountType),
  45. ];
  46. });
  47. }
  48. /**
  49. * @return array<string, int|float|null>
  50. */
  51. private function summary(CarbonImmutable $from, CarbonImmutable $to, string $accountType): array
  52. {
  53. $row = $this->runs($from, $to, $accountType)
  54. ->selectRaw('count(*) as runs')
  55. ->selectRaw('count(*) filter (where r.deep) as deep')
  56. ->selectRaw("count(*) filter (where r.route = 'passage') as passage")
  57. ->selectRaw("count(*) filter (where r.status = 'denied') as denied")
  58. ->selectRaw("count(*) filter (where r.status = 'error') as errors")
  59. ->selectRaw("count(*) filter (where r.status = 'cancelled') as cancelled")
  60. ->selectRaw('count(*) filter (where r.improve) as improve')
  61. // 账号=两列一起:用户 uid 与设备 id 可能相同,只数 account_id 会把两人算成一个
  62. ->selectRaw('count(distinct (t.account_type, t.account_id)) as askers')
  63. ->selectRaw("count(distinct t.account_id) filter (where t.account_type = 'user') as askers_user")
  64. ->selectRaw("count(distinct t.account_id) filter (where t.account_type = 'guest') as askers_guest")
  65. ->selectRaw("percentile_cont(0.5) within group (order by r.latency_ms) filter (where r.status = 'ok') as p50")
  66. ->selectRaw("percentile_cont(0.95) within group (order by r.latency_ms) filter (where r.status = 'ok') as p95")
  67. ->selectRaw('coalesce(sum(r.tokens_in), 0) as tokens_in, coalesce(sum(r.tokens_out), 0) as tokens_out')
  68. ->first();
  69. $feedback = $this->runs($from, $to, $accountType)
  70. ->join('agent_feedback as f', 'f.message_id', '=', 'r.assistant_message_id')
  71. ->selectRaw('count(*) filter (where f.rating > 0) as up, count(*) filter (where f.rating < 0) as down')
  72. ->selectRaw('count(*) filter (where f.asked) as asked')
  73. ->first();
  74. $unchecked = $this->runs($from, $to, $accountType)
  75. ->join('agent_run_steps as s', fn ($j) => $j->on('s.agent_run_id', '=', 'r.id')->whereNull('s.deleted_at'))
  76. ->where('s.kind', 'event')->where('s.name', 'quota')
  77. ->whereRaw("s.args->>'result' = 'unchecked'")
  78. ->count();
  79. return [
  80. 'runs' => (int) $row->runs,
  81. 'deep' => (int) $row->deep,
  82. 'passage' => (int) $row->passage,
  83. 'denied' => (int) $row->denied,
  84. 'errors' => (int) $row->errors,
  85. 'cancelled' => (int) $row->cancelled,
  86. 'unchecked' => $unchecked,
  87. 'improve' => (int) $row->improve,
  88. 'askers' => (int) $row->askers,
  89. 'askers_user' => (int) $row->askers_user,
  90. 'askers_guest' => (int) $row->askers_guest,
  91. 'latency_p50_ms' => $row->p50 !== null ? (int) round((float) $row->p50) : null,
  92. 'latency_p95_ms' => $row->p95 !== null ? (int) round((float) $row->p95) : null,
  93. 'tokens_in' => (int) $row->tokens_in,
  94. 'tokens_out' => (int) $row->tokens_out,
  95. 'feedback_up' => (int) $feedback->up,
  96. 'feedback_down' => (int) $feedback->down,
  97. 'feedback_asked' => (int) $feedback->asked,
  98. ];
  99. }
  100. /**
  101. * 点踩原因分布(区间内提问收到的点踩)。
  102. *
  103. * @return list<array{reason: string, count: int}>
  104. */
  105. private function feedbackReasons(CarbonImmutable $from, CarbonImmutable $to, string $accountType): array
  106. {
  107. return $this->runs($from, $to, $accountType)
  108. ->join('agent_feedback as f', 'f.message_id', '=', 'r.assistant_message_id')
  109. ->where('f.rating', '<', 0)
  110. ->crossJoin(DB::raw("jsonb_array_elements_text(coalesce(f.tags, '[]'::jsonb)) as tag(reason)"))
  111. ->selectRaw('tag.reason as reason, count(*) as n')
  112. ->groupBy('tag.reason')->orderByDesc('n')->get()
  113. ->map(fn ($r) => ['reason' => $r->reason, 'count' => (int) $r->n])->all();
  114. }
  115. /**
  116. * 每天一行:按研究路径的提问数、拦截 / 出错数、耗时分位数。没有数据的日子补 0。
  117. *
  118. * @return list<array<string, int|string|null>>
  119. */
  120. private function daily(CarbonImmutable $from, CarbonImmutable $to, string $accountType): array
  121. {
  122. $byRoute = $this->runs($from, $to, $accountType)
  123. ->selectRaw("date({$this->time()}) as d, coalesce(r.route, 'none') as route, count(*) as n")
  124. ->groupByRaw('1, 2')->get();
  125. $other = $this->runs($from, $to, $accountType)
  126. ->selectRaw("date({$this->time()}) as d")
  127. ->selectRaw("count(*) filter (where r.status = 'denied') as denied")
  128. ->selectRaw("count(*) filter (where r.status = 'error') as errors")
  129. ->selectRaw("percentile_cont(0.5) within group (order by r.latency_ms) filter (where r.status = 'ok') as p50")
  130. ->selectRaw("percentile_cont(0.95) within group (order by r.latency_ms) filter (where r.status = 'ok') as p95")
  131. ->groupByRaw('1')->get()->keyBy(fn ($r) => substr((string) $r->d, 0, 10));
  132. $votes = $this->runs($from, $to, $accountType)
  133. ->join('agent_feedback as f', 'f.message_id', '=', 'r.assistant_message_id')
  134. ->selectRaw("date({$this->time()}) as d, count(*) filter (where f.rating > 0) as up, count(*) filter (where f.rating < 0) as down")
  135. ->groupByRaw('1')->get()->keyBy(fn ($r) => substr((string) $r->d, 0, 10));
  136. $rows = [];
  137. for ($day = $from; $day->lte($to); $day = $day->addDay()) {
  138. $d = $day->toDateString();
  139. $rows[$d] = ['date' => $d] + array_fill_keys([...AgentRun::ROUTES, 'none'], 0) + [
  140. 'denied' => (int) ($other[$d]->denied ?? 0),
  141. 'errors' => (int) ($other[$d]->errors ?? 0),
  142. 'latency_p50_ms' => isset($other[$d]) && $other[$d]->p50 !== null ? (int) round((float) $other[$d]->p50) : null,
  143. 'latency_p95_ms' => isset($other[$d]) && $other[$d]->p95 !== null ? (int) round((float) $other[$d]->p95) : null,
  144. 'feedback_up' => (int) ($votes[$d]->up ?? 0),
  145. 'feedback_down' => (int) ($votes[$d]->down ?? 0),
  146. ];
  147. }
  148. foreach ($byRoute as $row) {
  149. $d = substr((string) $row->d, 0, 10);
  150. $route = in_array($row->route, AgentRun::ROUTES, true) ? $row->route : 'none';
  151. if (isset($rows[$d])) {
  152. $rows[$d][$route] += (int) $row->n;
  153. }
  154. }
  155. return array_values($rows);
  156. }
  157. /**
  158. * @return list<array{name: string, calls: int, errors: int, avg_ms: int}>
  159. */
  160. private function tools(CarbonImmutable $from, CarbonImmutable $to, string $accountType): array
  161. {
  162. return $this->runs($from, $to, $accountType)
  163. ->join('agent_run_steps as s', fn ($j) => $j->on('s.agent_run_id', '=', 'r.id')->whereNull('s.deleted_at'))
  164. ->where('s.kind', 'tool')
  165. ->selectRaw("coalesce(s.name, '?') as name, count(*) as calls, count(*) filter (where s.error is not null) as errors, coalesce(avg(s.ms), 0) as avg_ms")
  166. ->groupByRaw('1')->orderByDesc('calls')->limit(10)->get()
  167. ->map(fn ($r) => ['name' => $r->name, 'calls' => (int) $r->calls, 'errors' => (int) $r->errors, 'avg_ms' => (int) round((float) $r->avg_ms)])
  168. ->all();
  169. }
  170. /**
  171. * 区间内的积分流水:赠送 / 积分消耗、退回、读经 / 编辑发放、手工调整(正负分开记)。
  172. *
  173. * @return array<string, int>
  174. */
  175. private function creditFlows(CarbonImmutable $from, CarbonImmutable $to, string $accountType): array
  176. {
  177. $row = DB::table('credit_ledger')
  178. ->whereBetween('day', [$from->toDateString(), $to->toDateString()])
  179. ->when($accountType !== 'all', fn ($q) => $q->where('account_type', $accountType))
  180. ->selectRaw("coalesce(-sum(delta) filter (where kind = 'spend' and bucket = 'free'), 0) as spent_free")
  181. ->selectRaw("coalesce(-sum(delta) filter (where kind = 'spend' and bucket = 'earned'), 0) as spent_earned")
  182. ->selectRaw("coalesce(sum(delta) filter (where kind = 'refund'), 0) as refunded")
  183. ->selectRaw("coalesce(sum(delta) filter (where kind = 'earn' and source = 'reading'), 0) as earned_reading")
  184. ->selectRaw("coalesce(sum(delta) filter (where kind = 'earn' and source = 'editing'), 0) as earned_editing")
  185. ->selectRaw("coalesce(sum(delta) filter (where kind = 'adjust'), 0) as adjusted")
  186. ->first();
  187. return array_map('intval', (array) $row);
  188. }
  189. /**
  190. * 提问列表(管理员查阅),最新的在前。
  191. *
  192. * @param array{filter?: array{status?: string, route?: string, account_type?: string, client?: string, improve?: bool|string, q?: string}} $filters
  193. * @return LengthAwarePaginator<int, AgentRun>
  194. */
  195. public function listRuns(array $filters, int $perPage): LengthAwarePaginator
  196. {
  197. $f = $filters['filter'] ?? [];
  198. return AgentRun::query()
  199. ->with(['thread:id,thread_id,account_type,account_id,title', UserService::eagerLoadActor()[0]])
  200. ->when($f['status'] ?? null, fn ($q, $v) => $q->where('status', $v))
  201. ->when($f['route'] ?? null, fn ($q, $v) => $q->where('route', $v))
  202. ->when($f['client'] ?? null, fn ($q, $v) => $q->where('client', $v))
  203. ->when($f['account_type'] ?? null, fn ($q, $v) => $q->where('account_type', $v))
  204. ->when(isset($f['improve']), fn ($q) => $q->where('improve', filter_var($f['improve'], FILTER_VALIDATE_BOOLEAN)))
  205. ->when($f['q'] ?? null, fn ($q, $v) => $q->where('question', 'ilike', '%'.addcslashes($v, '%_\\').'%'))
  206. ->orderByDesc('id')
  207. ->paginate($perPage);
  208. }
  209. /**
  210. * 分析用的精简导出:区间内**允许用于改进回答**(improve=true)的提问,最早的在前,最多 $limit 条。
  211. *
  212. * 不带任何身份(账号、设备、IP)。每条只给分析要用的:问题、路由与把握度、计划里的检索词、
  213. * 状态与耗时、工具调用序列(名称 / 耗时 / 结果字数 / 是否出错)、用户评价、隐式信号计数,
  214. * 以及同一会话里下一次提问隔了几秒(短间隔常是「没答到点子上、换个说法再问」)。
  215. * `$full` 时再带回答、证据全文与引用——回答质量由 agent 拉去评估,服务端不做。
  216. *
  217. * @return array{items: list<array<string, mixed>>, has_more: bool}
  218. */
  219. public function digest(CarbonImmutable $from, CarbonImmutable $to, int $limit, bool $full = false): array
  220. {
  221. $rows = $this->runs($from, $to, 'all')
  222. ->where('r.improve', true)
  223. ->select(['r.id', 'r.run_id', 'r.started_at', 'r.question', 'r.route', 'r.route_raw', 'r.confidence', 'r.deep', 'r.status',
  224. 'r.latency_ms', 'r.plan', 'r.passage', 'r.retries', 'r.llm_calls', 'r.tool_calls', 'r.tokens_in', 'r.tokens_out',
  225. 'r.client', 'r.assistant_message_id'])
  226. ->when($full, fn ($q) => $q->addSelect(['r.answer', 'r.evidence', 'r.citations']))
  227. ->selectRaw('extract(epoch from (lead(r.started_at) over (partition by r.agent_thread_id order by r.started_at) - r.started_at)) as next_after')
  228. ->orderBy('r.started_at')->orderBy('r.id')
  229. ->limit($limit + 1)
  230. ->get();
  231. $hasMore = $rows->count() > $limit;
  232. $rows = $rows->take($limit);
  233. $ids = $rows->pluck('id');
  234. $messages = $rows->pluck('assistant_message_id')->filter()->values();
  235. $steps = DB::table('agent_run_steps')->whereIn('agent_run_id', $ids)->whereNull('deleted_at')->where('kind', 'tool')
  236. ->orderBy('seq')->get(['agent_run_id', 'name', 'ms', 'result_chars', 'error'])->groupBy('agent_run_id');
  237. $feedback = DB::table('agent_feedback')->whereIn('message_id', $messages)
  238. ->get(['message_id', 'rating', 'tags', 'comment', 'asked'])->groupBy('message_id');
  239. $events = DB::table('agent_events')->whereIn('message_id', $messages)
  240. ->selectRaw('message_id, kind, count(*) as n')->groupBy('message_id', 'kind')->get()->groupBy('message_id');
  241. $items = $rows->map(function ($r) use ($steps, $feedback, $events, $full) {
  242. $plan = $r->plan ? json_decode($r->plan, true) : [];
  243. $passage = $r->passage ? json_decode($r->passage, true) : null;
  244. return [
  245. 'run_id' => $r->run_id,
  246. 'started_at' => $r->started_at,
  247. 'question' => $r->question,
  248. 'route' => $r->route,
  249. 'route_raw' => $r->route_raw,
  250. 'confidence' => $r->confidence === null ? null : round((float) $r->confidence, 2),
  251. 'deep' => (bool) $r->deep,
  252. 'from_reader' => $passage !== null,
  253. 'client' => $r->client,
  254. 'status' => $r->status,
  255. 'latency_ms' => $r->latency_ms === null ? null : (int) $r->latency_ms,
  256. 'lemmas' => $plan['lemmas'] ?? [],
  257. 'keywords' => array_map(fn ($k) => is_array($k) ? trim(($k['lemma'] ?? '').'|'.($k['tags'] ?? ''), '|') : $k, $plan['keywords'] ?? []),
  258. 'retries' => (int) $r->retries,
  259. 'llm_calls' => (int) $r->llm_calls,
  260. 'tokens' => (int) $r->tokens_in + (int) $r->tokens_out,
  261. 'tools' => ($steps[$r->id] ?? collect())->map(fn ($s) => [
  262. 'name' => $s->name, 'ms' => $s->ms === null ? null : (int) $s->ms,
  263. 'chars' => $s->result_chars === null ? null : (int) $s->result_chars, 'error' => $s->error !== null,
  264. ])->values()->all(),
  265. 'feedback' => ($feedback[$r->assistant_message_id] ?? collect())->map(fn ($f) => [
  266. 'rating' => (int) $f->rating, 'tags' => $f->tags ? json_decode($f->tags, true) : [],
  267. 'comment' => $f->comment, 'asked' => (bool) $f->asked,
  268. ])->values()->all(),
  269. 'events' => ($events[$r->assistant_message_id] ?? collect())->mapWithKeys(fn ($e) => [$e->kind => (int) $e->n])->all(),
  270. 'next_question_after_s' => $r->next_after === null ? null : (int) round((float) $r->next_after),
  271. // 给 agent 评估回答质量用:回答、给作答模型的证据全文、引用
  272. ...($full ? [
  273. 'answer' => $r->answer,
  274. 'evidence' => $r->evidence,
  275. 'citations' => $r->citations ? json_decode($r->citations, true) : [],
  276. ] : []),
  277. ];
  278. })->values()->all();
  279. return ['items' => $items, 'has_more' => $hasMore];
  280. }
  281. /**
  282. * 一次提问的完整研究过程(含全部步骤)。
  283. *
  284. * @throws ModelNotFoundException
  285. */
  286. public function showRun(string $runId): AgentRun
  287. {
  288. return AgentRun::with(['thread', 'steps', UserService::eagerLoadActor()[0]])->where('run_id', $runId)->firstOrFail();
  289. }
  290. private function runs(CarbonImmutable $from, CarbonImmutable $to, string $accountType): Builder
  291. {
  292. // 软删的提问(用户删了会话)不计入任何统计
  293. return DB::table('agent_runs as r')
  294. ->whereNull('r.deleted_at')
  295. ->join('agent_threads as t', 't.id', '=', 'r.agent_thread_id')
  296. ->whereRaw("{$this->time()} >= ?", [$from->startOfDay()])
  297. ->whereRaw("{$this->time()} < ?", [$to->addDay()->startOfDay()])
  298. ->when($accountType !== 'all', fn ($q) => $q->where('r.account_type', $accountType));
  299. }
  300. private function time(): string
  301. {
  302. return 'r.started_at';
  303. }
  304. }