git.iko.soy/crush
on 175ce34 help sign in
crush/internal/db/stats.sql.go
367 lines 9.7 kB history raw
1// Code generated by sqlc. DO NOT EDIT.
2// versions:
3// sqlc v1.30.0
4// source: stats.sql
6package db
8import (
9 "context"
10 "database/sql"
13const getAverageResponseTime = `-- name: GetAverageResponseTime :one
14SELECT
15 CAST(COALESCE(AVG(finished_at - created_at), 0) AS INTEGER) as avg_response_seconds
16FROM messages
17WHERE role = 'assistant'
18 AND finished_at IS NOT NULL
19 AND finished_at > created_at
22func (q *Queries) GetAverageResponseTime(ctx context.Context) (int64, error) {
23 row := q.queryRow(ctx, q.getAverageResponseTimeStmt, getAverageResponseTime)
24 var avg_response_seconds int64
25 err := row.Scan(&avg_response_seconds)
26 return avg_response_seconds, err
29const getHourDayHeatmap = `-- name: GetHourDayHeatmap :many
30SELECT
31 CAST(strftime('%w', created_at, 'unixepoch') AS INTEGER) as day_of_week,
32 CAST(strftime('%H', created_at, 'unixepoch') AS INTEGER) as hour,
33 COUNT(*) as session_count
34FROM sessions
35WHERE parent_session_id IS NULL
36GROUP BY day_of_week, hour
37ORDER BY day_of_week, hour
40type GetHourDayHeatmapRow struct {
41 DayOfWeek int64 `json:"day_of_week"`
42 Hour int64 `json:"hour"`
43 SessionCount int64 `json:"session_count"`
46func (q *Queries) GetHourDayHeatmap(ctx context.Context) ([]GetHourDayHeatmapRow, error) {
47 rows, err := q.query(ctx, q.getHourDayHeatmapStmt, getHourDayHeatmap)
48 if err != nil {
49 return nil, err
50 }
51 defer rows.Close()
52 items := []GetHourDayHeatmapRow{}
53 for rows.Next() {
54 var i GetHourDayHeatmapRow
55 if err := rows.Scan(&i.DayOfWeek, &i.Hour, &i.SessionCount); err != nil {
56 return nil, err
57 }
58 items = append(items, i)
59 }
60 if err := rows.Close(); err != nil {
61 return nil, err
62 }
63 if err := rows.Err(); err != nil {
64 return nil, err
65 }
66 return items, nil
69const getRecentActivity = `-- name: GetRecentActivity :many
70SELECT
71 date(created_at, 'unixepoch') as day,
72 COUNT(*) as session_count,
73 SUM(prompt_tokens + completion_tokens) as total_tokens,
74 SUM(cost) as cost
75FROM sessions
76WHERE parent_session_id IS NULL
77 AND created_at >= strftime('%s', 'now', '-30 days')
78GROUP BY date(created_at, 'unixepoch')
79ORDER BY day ASC
82type GetRecentActivityRow struct {
83 Day interface{} `json:"day"`
84 SessionCount int64 `json:"session_count"`
85 TotalTokens sql.NullFloat64 `json:"total_tokens"`
86 Cost sql.NullFloat64 `json:"cost"`
89func (q *Queries) GetRecentActivity(ctx context.Context) ([]GetRecentActivityRow, error) {
90 rows, err := q.query(ctx, q.getRecentActivityStmt, getRecentActivity)
91 if err != nil {
92 return nil, err
93 }
94 defer rows.Close()
95 items := []GetRecentActivityRow{}
96 for rows.Next() {
97 var i GetRecentActivityRow
98 if err := rows.Scan(
99 &i.Day,
100 &i.SessionCount,
101 &i.TotalTokens,
102 &i.Cost,
103 ); err != nil {
104 return nil, err
105 }
106 items = append(items, i)
107 }
108 if err := rows.Close(); err != nil {
109 return nil, err
110 }
111 if err := rows.Err(); err != nil {
112 return nil, err
113 }
114 return items, nil
117const getToolUsage = `-- name: GetToolUsage :many
118SELECT
119 json_extract(value, '$.data.name') as tool_name,
120 COUNT(*) as call_count
121FROM messages, json_each(parts)
122WHERE json_extract(value, '$.type') = 'tool_call'
123 AND json_extract(value, '$.data.name') IS NOT NULL
124GROUP BY tool_name
125ORDER BY call_count DESC
128type GetToolUsageRow struct {
129 ToolName interface{} `json:"tool_name"`
130 CallCount int64 `json:"call_count"`
133func (q *Queries) GetToolUsage(ctx context.Context) ([]GetToolUsageRow, error) {
134 rows, err := q.query(ctx, q.getToolUsageStmt, getToolUsage)
135 if err != nil {
136 return nil, err
137 }
138 defer rows.Close()
139 items := []GetToolUsageRow{}
140 for rows.Next() {
141 var i GetToolUsageRow
142 if err := rows.Scan(&i.ToolName, &i.CallCount); err != nil {
143 return nil, err
144 }
145 items = append(items, i)
146 }
147 if err := rows.Close(); err != nil {
148 return nil, err
149 }
150 if err := rows.Err(); err != nil {
151 return nil, err
152 }
153 return items, nil
156const getTotalStats = `-- name: GetTotalStats :one
157SELECT
158 COUNT(*) as total_sessions,
159 COALESCE(SUM(prompt_tokens), 0) as total_prompt_tokens,
160 COALESCE(SUM(completion_tokens), 0) as total_completion_tokens,
161 COALESCE(SUM(cost), 0) as total_cost,
162 COALESCE(SUM(message_count), 0) as total_messages,
163 COALESCE(AVG(prompt_tokens + completion_tokens), 0) as avg_tokens_per_session,
164 COALESCE(AVG(message_count), 0) as avg_messages_per_session
165FROM sessions
166WHERE parent_session_id IS NULL
169type GetTotalStatsRow struct {
170 TotalSessions int64 `json:"total_sessions"`
171 TotalPromptTokens interface{} `json:"total_prompt_tokens"`
172 TotalCompletionTokens interface{} `json:"total_completion_tokens"`
173 TotalCost interface{} `json:"total_cost"`
174 TotalMessages interface{} `json:"total_messages"`
175 AvgTokensPerSession interface{} `json:"avg_tokens_per_session"`
176 AvgMessagesPerSession interface{} `json:"avg_messages_per_session"`
179func (q *Queries) GetTotalStats(ctx context.Context) (GetTotalStatsRow, error) {
180 row := q.queryRow(ctx, q.getTotalStatsStmt, getTotalStats)
181 var i GetTotalStatsRow
182 err := row.Scan(
183 &i.TotalSessions,
184 &i.TotalPromptTokens,
185 &i.TotalCompletionTokens,
186 &i.TotalCost,
187 &i.TotalMessages,
188 &i.AvgTokensPerSession,
189 &i.AvgMessagesPerSession,
190 )
191 return i, err
194const getUsageByDay = `-- name: GetUsageByDay :many
195SELECT
196 date(created_at, 'unixepoch') as day,
197 SUM(prompt_tokens) as prompt_tokens,
198 SUM(completion_tokens) as completion_tokens,
199 SUM(cost) as cost,
200 COUNT(*) as session_count
201FROM sessions
202WHERE parent_session_id IS NULL
203GROUP BY date(created_at, 'unixepoch')
204ORDER BY day DESC
207type GetUsageByDayRow struct {
208 Day interface{} `json:"day"`
209 PromptTokens sql.NullFloat64 `json:"prompt_tokens"`
210 CompletionTokens sql.NullFloat64 `json:"completion_tokens"`
211 Cost sql.NullFloat64 `json:"cost"`
212 SessionCount int64 `json:"session_count"`
215func (q *Queries) GetUsageByDay(ctx context.Context) ([]GetUsageByDayRow, error) {
216 rows, err := q.query(ctx, q.getUsageByDayStmt, getUsageByDay)
217 if err != nil {
218 return nil, err
219 }
220 defer rows.Close()
221 items := []GetUsageByDayRow{}
222 for rows.Next() {
223 var i GetUsageByDayRow
224 if err := rows.Scan(
225 &i.Day,
226 &i.PromptTokens,
227 &i.CompletionTokens,
228 &i.Cost,
229 &i.SessionCount,
230 ); err != nil {
231 return nil, err
232 }
233 items = append(items, i)
234 }
235 if err := rows.Close(); err != nil {
236 return nil, err
237 }
238 if err := rows.Err(); err != nil {
239 return nil, err
240 }
241 return items, nil
244const getUsageByDayOfWeek = `-- name: GetUsageByDayOfWeek :many
245SELECT
246 CAST(strftime('%w', created_at, 'unixepoch') AS INTEGER) as day_of_week,
247 COUNT(*) as session_count,
248 SUM(prompt_tokens) as prompt_tokens,
249 SUM(completion_tokens) as completion_tokens
250FROM sessions
251WHERE parent_session_id IS NULL
252GROUP BY day_of_week
253ORDER BY day_of_week
256type GetUsageByDayOfWeekRow struct {
257 DayOfWeek int64 `json:"day_of_week"`
258 SessionCount int64 `json:"session_count"`
259 PromptTokens sql.NullFloat64 `json:"prompt_tokens"`
260 CompletionTokens sql.NullFloat64 `json:"completion_tokens"`
263func (q *Queries) GetUsageByDayOfWeek(ctx context.Context) ([]GetUsageByDayOfWeekRow, error) {
264 rows, err := q.query(ctx, q.getUsageByDayOfWeekStmt, getUsageByDayOfWeek)
265 if err != nil {
266 return nil, err
267 }
268 defer rows.Close()
269 items := []GetUsageByDayOfWeekRow{}
270 for rows.Next() {
271 var i GetUsageByDayOfWeekRow
272 if err := rows.Scan(
273 &i.DayOfWeek,
274 &i.SessionCount,
275 &i.PromptTokens,
276 &i.CompletionTokens,
277 ); err != nil {
278 return nil, err
279 }
280 items = append(items, i)
281 }
282 if err := rows.Close(); err != nil {
283 return nil, err
284 }
285 if err := rows.Err(); err != nil {
286 return nil, err
287 }
288 return items, nil
291const getUsageByHour = `-- name: GetUsageByHour :many
292SELECT
293 CAST(strftime('%H', created_at, 'unixepoch') AS INTEGER) as hour,
294 COUNT(*) as session_count
295FROM sessions
296WHERE parent_session_id IS NULL
297GROUP BY hour
298ORDER BY hour
301type GetUsageByHourRow struct {
302 Hour int64 `json:"hour"`
303 SessionCount int64 `json:"session_count"`
306func (q *Queries) GetUsageByHour(ctx context.Context) ([]GetUsageByHourRow, error) {
307 rows, err := q.query(ctx, q.getUsageByHourStmt, getUsageByHour)
308 if err != nil {
309 return nil, err
310 }
311 defer rows.Close()
312 items := []GetUsageByHourRow{}
313 for rows.Next() {
314 var i GetUsageByHourRow
315 if err := rows.Scan(&i.Hour, &i.SessionCount); err != nil {
316 return nil, err
317 }
318 items = append(items, i)
319 }
320 if err := rows.Close(); err != nil {
321 return nil, err
322 }
323 if err := rows.Err(); err != nil {
324 return nil, err
325 }
326 return items, nil
329const getUsageByModel = `-- name: GetUsageByModel :many
330SELECT
331 COALESCE(model, 'unknown') as model,
332 COALESCE(provider, 'unknown') as provider,
333 COUNT(*) as message_count
334FROM messages
335WHERE role = 'assistant'
336GROUP BY model, provider
337ORDER BY message_count DESC
340type GetUsageByModelRow struct {
341 Model string `json:"model"`
342 Provider string `json:"provider"`
343 MessageCount int64 `json:"message_count"`
346func (q *Queries) GetUsageByModel(ctx context.Context) ([]GetUsageByModelRow, error) {
347 rows, err := q.query(ctx, q.getUsageByModelStmt, getUsageByModel)
348 if err != nil {
349 return nil, err
350 }
351 defer rows.Close()
352 items := []GetUsageByModelRow{}
353 for rows.Next() {
354 var i GetUsageByModelRow
355 if err := rows.Scan(&i.Model, &i.Provider, &i.MessageCount); err != nil {
356 return nil, err
357 }
358 items = append(items, i)
359 }
360 if err := rows.Close(); err != nil {
361 return nil, err
362 }
363 if err := rows.Err(); err != nil {
364 return nil, err
365 }
366 return items, nil