Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
97.51%
196 / 201
91.67%
11 / 12
 CRAP
0.00%
0 / 1
DashboardQueryService
97.51%
196 / 201
91.67%
11 / 12
 35
0.00%
0 / 1
 eventQuery
100.00%
4 / 4
100.00%
1 / 1
 1
 getSummary
100.00%
22 / 22
100.00%
1 / 1
 1
 getSessions
100.00%
28 / 28
100.00%
1 / 1
 7
 getSessionDetail
100.00%
12 / 12
100.00%
1 / 1
 1
 getSessionActivity
100.00%
62 / 62
100.00%
1 / 1
 17
 getRecentEvents
100.00%
3 / 3
100.00%
1 / 1
 1
 getCostByModel
100.00%
16 / 16
100.00%
1 / 1
 1
 getToolUsage
100.00%
13 / 13
100.00%
1 / 1
 1
 getApiErrors
0.00%
0 / 5
0.00%
0 / 1
 2
 getApiPerformance
100.00%
10 / 10
100.00%
1 / 1
 2
 getTokenBreakdown
100.00%
14 / 14
100.00%
1 / 1
 1
 getLinesOfCodeBreakdown
100.00%
12 / 12
100.00%
1 / 1
 1
1<?php
2
3namespace App\Services;
4
5use App\Models\TelemetryEvent;
6use App\Models\TelemetryMetric;
7use App\Models\TelemetrySession;
8use Illuminate\Support\Collection;
9use Illuminate\Support\Facades\DB;
10
11class DashboardQueryService
12{
13    /**
14     * Match event names with or without 'claude_code.' prefix.
15     */
16    private function eventQuery(string $name): \Illuminate\Database\Eloquent\Builder
17    {
18        return TelemetryEvent::where(function ($q) use ($name) {
19            $q->where('event_name', $name)
20              ->orWhere('event_name', "claude_code.{$name}");
21        });
22    }
23
24    public function getSummary(): array
25    {
26        $totalSessions = TelemetrySession::count();
27        $activeSessions = TelemetrySession::where('last_seen_at', '>', now()->subMinutes(30))->count();
28
29        $totalCost = TelemetryMetric::where('metric_name', 'claude_code.cost.usage')->sum('value');
30        $totalTokens = TelemetryMetric::where('metric_name', 'claude_code.token.usage')->sum('value');
31        $totalLoc = TelemetryMetric::where('metric_name', 'claude_code.lines_of_code.count')->sum('value');
32        $totalCommits = TelemetryMetric::where('metric_name', 'claude_code.commit.count')->sum('value');
33        $totalPrs = TelemetryMetric::where('metric_name', 'claude_code.pull_request.count')->sum('value');
34        $totalActiveTime = TelemetryMetric::where('metric_name', 'claude_code.active_time.total')->sum('value');
35
36        // API stats from events
37        $apiRequests = $this->eventQuery('api_request')->count();
38        $apiErrors = $this->eventQuery('api_error')->count();
39
40        return [
41            'total_sessions' => $totalSessions,
42            'active_sessions' => $activeSessions,
43            'total_cost' => round($totalCost, 4),
44            'total_tokens' => (int) $totalTokens,
45            'total_loc' => (int) $totalLoc,
46            'total_commits' => (int) $totalCommits,
47            'total_prs' => (int) $totalPrs,
48            'total_active_time' => (int) $totalActiveTime,
49            'api_requests' => $apiRequests,
50            'api_errors' => $apiErrors,
51        ];
52    }
53
54    public function getSessions(): Collection
55    {
56        $sessions = TelemetrySession::orderByDesc('last_seen_at')->get();
57
58        if ($sessions->isEmpty()) {
59            return $sessions;
60        }
61
62        $groupCounts = $sessions->whereNotNull('session_group_id')
63            ->groupBy('session_group_id')
64            ->map->count();
65
66        $grouped = collect();
67        $seen = [];
68
69        foreach ($sessions as $session) {
70            $gid = $session->session_group_id;
71
72            if (! $gid || ($groupCounts[$gid] ?? 0) < 2) {
73                $session->group_index = null;
74                $session->group_size = null;
75                $session->group_collapsed = false;
76                $grouped->push($session);
77
78                continue;
79            }
80
81            if (isset($seen[$gid])) {
82                continue;
83            }
84
85            $seen[$gid] = true;
86            $groupMembers = $sessions->where('session_group_id', $gid)
87                ->sortByDesc('last_seen_at')
88                ->values();
89
90            foreach ($groupMembers as $i => $member) {
91                $member->group_index = $groupMembers->count() - $i;
92                $member->group_size = $groupMembers->count();
93                $member->group_collapsed = $i > 0;
94                $grouped->push($member);
95            }
96        }
97
98        return $grouped;
99    }
100
101    public function getSessionDetail(string $sessionId): array
102    {
103        $session = TelemetrySession::where('session_id', $sessionId)->firstOrFail();
104        $metrics = TelemetryMetric::where('session_id', $sessionId)->orderByDesc('recorded_at')->get();
105        $events = TelemetryEvent::where('session_id', $sessionId)->orderByDesc('recorded_at')->get();
106
107        $sessionCost = $metrics->where('metric_name', 'claude_code.cost.usage')->sum('value');
108        $sessionTokens = $metrics->where('metric_name', 'claude_code.token.usage')->sum('value');
109
110        return [
111            'session' => $session,
112            'metrics' => $metrics,
113            'events' => $events,
114            'cost' => round($sessionCost, 4),
115            'tokens' => (int) $sessionTokens,
116        ];
117    }
118
119    public function getSessionActivity(string $sessionId): array
120    {
121        $session = TelemetrySession::where('session_id', $sessionId)->firstOrFail();
122
123        $lastSeenAt = $session->last_seen_at;
124        $inactivitySeconds = (int) abs(now()->diffInSeconds($lastSeenAt));
125
126        $status = match (true) {
127            $inactivitySeconds < 60 => 'working',
128            $inactivitySeconds < 1800 => 'idle',
129            default => 'inactive',
130        };
131
132        $recentEvents = TelemetryEvent::where('session_id', $sessionId)
133            ->orderByDesc('recorded_at')
134            ->limit(15)
135            ->get();
136
137        $fiveMinAgo = now()->subMinutes(5);
138        $eventsLast5Min = TelemetryEvent::where('session_id', $sessionId)
139            ->where('recorded_at', '>=', $fiveMinAgo)
140            ->count();
141
142        $activityRate = round($eventsLast5Min / 5, 1);
143
144        $recent = $recentEvents->map(function ($event) {
145            $attrs = $event->attributes ?? [];
146            $baseName = str_replace('claude_code.', '', $event->event_name);
147            $detail = $attrs['tool_name'] ?? $attrs['model'] ?? null;
148
149            return [
150                'type' => 'event',
151                'name' => $baseName,
152                'detail' => $detail,
153                'time' => $event->recorded_at?->format('H:i:s'),
154            ];
155        });
156
157        // Current activity description from latest event
158        $currentActivity = null;
159        if ($recentEvents->isNotEmpty()) {
160            $latest = $recentEvents->first();
161            $latestAttrs = $latest->attributes ?? [];
162            $latestBase = str_replace('claude_code.', '', $latest->event_name);
163            $currentActivity = match ($latestBase) {
164                'tool_result' => 'Used ' . ($latestAttrs['tool_name'] ?? 'tool') .
165                    (isset($latestAttrs['success']) && ($latestAttrs['success'] === 'true' || $latestAttrs['success'] === true) ? '' : ' (failed)'),
166                'tool_decision' => 'Deciding to use ' . ($latestAttrs['tool_name'] ?? 'tool'),
167                'api_request' => 'API call to ' . ($latestAttrs['model'] ?? 'model') .
168                    (isset($latestAttrs['duration_ms']) ? ' (' . $latestAttrs['duration_ms'] . 'ms)' : ''),
169                'api_error' => 'API error' . (isset($latestAttrs['error']) ? ': ' . \Illuminate\Support\Str::limit($latestAttrs['error'], 80) : ''),
170                'user_prompt' => 'User prompt received',
171                default => $latestBase,
172            };
173        }
174
175        // Progress: events per minute over 1-min windows for the last 5 minutes
176        $progressBuckets = [];
177        for ($i = 4; $i >= 0; $i--) {
178            $from = now()->subMinutes($i + 1);
179            $to = now()->subMinutes($i);
180            $count = TelemetryEvent::where('session_id', $sessionId)
181                ->where('recorded_at', '>=', $from)
182                ->where('recorded_at', '<', $to)
183                ->count();
184            $progressBuckets[] = $count;
185        }
186
187        return [
188            'status' => $status,
189            'last_activity_at' => $lastSeenAt?->toIso8601String(),
190            'inactivity_seconds' => $inactivitySeconds,
191            'events_last_5min' => $eventsLast5Min,
192            'activity_rate' => $activityRate,
193            'current_activity' => $currentActivity,
194            'progress_buckets' => $progressBuckets,
195            'recent' => $recent->toArray(),
196        ];
197    }
198
199    public function getRecentEvents(int $limit = 50): Collection
200    {
201        return TelemetryEvent::orderByDesc('recorded_at')
202            ->limit($limit)
203            ->get();
204    }
205
206    public function getCostByModel(): Collection
207    {
208        return TelemetryEvent::where(function ($q) {
209                $q->where('event_name', 'api_request')
210                  ->orWhere('event_name', 'claude_code.api_request');
211            })
212            ->select(
213                DB::raw("json_extract(attributes, '$.model') as model"),
214                DB::raw("SUM(json_extract(attributes, '$.cost_usd')) as total_cost"),
215                DB::raw("COUNT(*) as request_count"),
216                DB::raw("SUM(json_extract(attributes, '$.input_tokens')) as input_tokens"),
217                DB::raw("SUM(json_extract(attributes, '$.output_tokens')) as output_tokens"),
218                DB::raw("SUM(json_extract(attributes, '$.cache_read_tokens')) as cache_read_tokens"),
219                DB::raw("SUM(json_extract(attributes, '$.cache_creation_tokens')) as cache_creation_tokens")
220            )
221            ->groupBy('model')
222            ->orderByDesc('total_cost')
223            ->get();
224    }
225
226    public function getToolUsage(): Collection
227    {
228        return TelemetryEvent::where(function ($q) {
229                $q->where('event_name', 'tool_result')
230                  ->orWhere('event_name', 'claude_code.tool_result');
231            })
232            ->select(
233                DB::raw("json_extract(attributes, '$.tool_name') as tool_name"),
234                DB::raw("COUNT(*) as invocations"),
235                DB::raw("SUM(CASE WHEN json_extract(attributes, '$.success') IN ('true', 1) THEN 1 ELSE 0 END) as successes"),
236                DB::raw("AVG(json_extract(attributes, '$.duration_ms')) as avg_duration_ms")
237            )
238            ->groupBy('tool_name')
239            ->orderByDesc('invocations')
240            ->get();
241    }
242
243    public function getApiErrors(int $limit = 50): Collection
244    {
245        return $this->eventQuery('api_error')
246            ->with('session:id,session_id,project_name')
247            ->orderByDesc('recorded_at')
248            ->limit($limit)
249            ->get();
250    }
251
252    public function getApiPerformance(): array
253    {
254        $totalRequests = $this->eventQuery('api_request')->count();
255        $totalErrors = $this->eventQuery('api_error')->count();
256        $avgDuration = $this->eventQuery('api_request')
257            ->avg(DB::raw("json_extract(attributes, '$.duration_ms')"));
258
259        return [
260            'total_requests' => $totalRequests,
261            'total_errors' => $totalErrors,
262            'error_rate' => $totalRequests > 0 ? round(($totalErrors / $totalRequests) * 100, 1) : 0,
263            'avg_duration_ms' => round((float) $avgDuration, 0),
264        ];
265    }
266
267    public function getTokenBreakdown(): array
268    {
269        $breakdown = TelemetryMetric::where('metric_name', 'claude_code.token.usage')
270            ->select(
271                DB::raw("json_extract(attributes, '$.type') as token_type"),
272                DB::raw("SUM(value) as total")
273            )
274            ->groupBy('token_type')
275            ->pluck('total', 'token_type')
276            ->toArray();
277
278        return [
279            'input' => (int) ($breakdown['input'] ?? $breakdown['"input"'] ?? 0),
280            'output' => (int) ($breakdown['output'] ?? $breakdown['"output"'] ?? 0),
281            'cache_read' => (int) ($breakdown['cacheRead'] ?? $breakdown['"cacheRead"'] ?? 0),
282            'cache_creation' => (int) ($breakdown['cacheCreation'] ?? $breakdown['"cacheCreation"'] ?? 0),
283        ];
284    }
285
286    public function getLinesOfCodeBreakdown(): array
287    {
288        $breakdown = TelemetryMetric::where('metric_name', 'claude_code.lines_of_code.count')
289            ->select(
290                DB::raw("json_extract(attributes, '$.type') as loc_type"),
291                DB::raw("SUM(value) as total")
292            )
293            ->groupBy('loc_type')
294            ->pluck('total', 'loc_type')
295            ->toArray();
296
297        return [
298            'added' => (int) ($breakdown['added'] ?? $breakdown['"added"'] ?? 0),
299            'removed' => (int) ($breakdown['removed'] ?? $breakdown['"removed"'] ?? 0),
300        ];
301    }
302}