Open source file-system and knowledge based agent template. Build AI agents that stay up to date with your knowledge base
import { db, schema } from '@nuxthub/db'
import { desc, and, gte, lt, eq } from 'drizzle-orm'
/**
* GET /api/stats
* Get global usage statistics (admin only)
*
* Query params:
* - days: number of days to look back (default: 30)
* - sources: comma-separated source filter (e.g. web,github-bot)
* - models: comma-separated model filter
*/
export default defineEventHandler(async (event) => {
const requestLog = useLogger(event)
await requireAdmin(event)
const query = getQuery(event)
const days = Math.min(Math.max(Number(query.days) || 30, 1), 365)
const sourcesFilter = typeof query.sources === 'string' && query.sources ? query.sources.split(',') : null
const modelsFilter = typeof query.models === 'string' && query.models ? query.models.split(',') : null
requestLog.set({ days, sourcesFilter, modelsFilter })
const endDate = new Date()
const startDate = new Date()
startDate.setDate(startDate.getDate() - days)
// Previous period for trends comparison
const prevEndDate = new Date(startDate)
const prevStartDate = new Date(startDate)
prevStartDate.setDate(prevStartDate.getDate() - days)
const [messagesWithStats, prevMessagesWithStats, apiUsageDataRaw] = await Promise.all([
db
.select({
chatId: schema.messages.chatId,
model: schema.messages.model,
inputTokens: schema.messages.inputTokens,
outputTokens: schema.messages.outputTokens,
durationMs: schema.messages.durationMs,
feedback: schema.messages.feedback,
createdAt: schema.messages.createdAt,
})
.from(schema.messages)
.where(
and(
eq(schema.messages.role, 'assistant'),
gte(schema.messages.createdAt, startDate),
),
)
.orderBy(desc(schema.messages.createdAt)),
db
.select({
chatId: schema.messages.chatId,
inputTokens: schema.messages.inputTokens,
outputTokens: schema.messages.outputTokens,
})
.from(schema.messages)
.where(
and(
eq(schema.messages.role, 'assistant'),
gte(schema.messages.createdAt, prevStartDate),
lt(schema.messages.createdAt, prevEndDate),
),
),
db
.select({
source: schema.apiUsage.source,
model: schema.apiUsage.model,
inputTokens: schema.apiUsage.inputTokens,
outputTokens: schema.apiUsage.outputTokens,
durationMs: schema.apiUsage.durationMs,
createdAt: schema.apiUsage.createdAt,
})
.from(schema.apiUsage)
.where(gte(schema.apiUsage.createdAt, startDate))
.orderBy(desc(schema.apiUsage.createdAt)),
])
const chatIds = [...new Set(messagesWithStats.map(m => m.chatId))]
const prevChatIds = [...new Set(prevMessagesWithStats.map(m => m.chatId))]
const [chatsData, prevChatsData] = await Promise.all([
chatIds.length > 0
? db.select({ id: schema.chats.id, userId: schema.chats.userId }).from(schema.chats)
: Promise.resolve([]),
prevChatIds.length > 0
? db.select({ id: schema.chats.id, userId: schema.chats.userId }).from(schema.chats)
: Promise.resolve([]),
])
const chatUserMap = new Map<string, string>()
for (const chat of chatsData) {
if (chatIds.includes(chat.id)) {
chatUserMap.set(chat.id, chat.userId)
}
}
const prevUserIds = new Set<string>()
for (const chat of prevChatsData) {
if (prevChatIds.includes(chat.id)) {
prevUserIds.add(chat.userId)
}
}
const userIds = [...new Set(chatUserMap.values())]
const usersData = userIds.length > 0
? await db
.select({
id: schema.user.id,
name: schema.user.name,
email: schema.user.email,
image: schema.user.image,
})
.from(schema.user)
: []
const userInfoMap = new Map<string, { name: string, email: string, image: string | null }>()
for (const user of usersData) {
if (userIds.includes(user.id)) {
userInfoMap.set(user.id, { name: user.name, email: user.email, image: user.image })
}
}
// Collect from unfiltered data so dropdown options include all values
const allSources = new Set<string>(['web'])
const allModels = new Set<string>()
for (const msg of messagesWithStats) {
if (msg.model) allModels.add(msg.model)
}
for (const usage of apiUsageDataRaw) {
allSources.add(usage.source)
if (usage.model) allModels.add(usage.model)
}
const availableSources = Array.from(allSources).sort()
const availableModels = Array.from(allModels).sort()
const filteredMessages = messagesWithStats.filter((msg) => {
if (sourcesFilter && !sourcesFilter.includes('web')) return false
if (modelsFilter && !modelsFilter.includes(msg.model ?? 'unknown')) return false
return true
})
const apiUsageData = apiUsageDataRaw.filter((usage) => {
if (sourcesFilter && !sourcesFilter.includes(usage.source)) return false
if (modelsFilter && !modelsFilter.includes(usage.model ?? 'unknown')) return false
return true
})
let totalMessages = 0
let totalInputTokens = 0
let totalOutputTokens = 0
let totalDurationMs = 0
let messagesWithDuration = 0
let positiveFeedback = 0
let negativeFeedback = 0
const byModelMap = new Map<string, {
messageCount: number
inputTokens: number
outputTokens: number
totalDurationMs: number
positive: number
negative: number
}>()
const dailyMap = new Map<string, Map<string, {
messageCount: number
inputTokens: number
outputTokens: number
totalDurationMs: number
}>>()
const bySourceMap = new Map<string, {
requests: number
inputTokens: number
outputTokens: number
totalDurationMs: number
}>()
const byUserMap = new Map<string, {
messageCount: number
inputTokens: number
outputTokens: number
totalDurationMs: number
}>()
const dailyBySourceMap = new Map<string, Map<string, { messageCount: number, inputTokens: number, outputTokens: number }>>()
const hourlyBuckets: Array<{ messageCount: number, totalTokens: number }> = Array.from({ length: 24 }, () => ({ messageCount: 0, totalTokens: 0 }))
bySourceMap.set('web', { requests: 0, inputTokens: 0, outputTokens: 0, totalDurationMs: 0 })
for (const msg of filteredMessages) {
totalMessages++
totalInputTokens += msg.inputTokens ?? 0
totalOutputTokens += msg.outputTokens ?? 0
if (msg.durationMs && msg.durationMs > 0) {
totalDurationMs += msg.durationMs
messagesWithDuration++
}
if (msg.feedback === 'positive') positiveFeedback++
if (msg.feedback === 'negative') negativeFeedback++
const modelKey = msg.model ?? 'unknown'
const modelStats = byModelMap.get(modelKey) ?? { messageCount: 0, inputTokens: 0, outputTokens: 0, totalDurationMs: 0, positive: 0, negative: 0 }
modelStats.messageCount++
modelStats.inputTokens += msg.inputTokens ?? 0
modelStats.outputTokens += msg.outputTokens ?? 0
modelStats.totalDurationMs += msg.durationMs ?? 0
if (msg.feedback === 'positive') modelStats.positive++
if (msg.feedback === 'negative') modelStats.negative++
byModelMap.set(modelKey, modelStats)
const dateStr = msg.createdAt.toISOString().split('T')[0]!
if (!dailyMap.has(dateStr)) {
dailyMap.set(dateStr, new Map())
}
const dayModelMap = dailyMap.get(dateStr)!
const dayModelStats = dayModelMap.get(modelKey) ?? {
messageCount: 0,
inputTokens: 0,
outputTokens: 0,
totalDurationMs: 0,
}
dayModelStats.messageCount++
dayModelStats.inputTokens += msg.inputTokens ?? 0
dayModelStats.outputTokens += msg.outputTokens ?? 0
dayModelStats.totalDurationMs += msg.durationMs ?? 0
dayModelMap.set(modelKey, dayModelStats)
const webStats = bySourceMap.get('web')!
webStats.requests++
webStats.inputTokens += msg.inputTokens ?? 0
webStats.outputTokens += msg.outputTokens ?? 0
webStats.totalDurationMs += msg.durationMs ?? 0
if (!dailyBySourceMap.has(dateStr)) dailyBySourceMap.set(dateStr, new Map())
const daySourceMap = dailyBySourceMap.get(dateStr)!
const daySourceStats = daySourceMap.get('web') ?? { messageCount: 0, inputTokens: 0, outputTokens: 0 }
daySourceStats.messageCount++
daySourceStats.inputTokens += msg.inputTokens ?? 0
daySourceStats.outputTokens += msg.outputTokens ?? 0
daySourceMap.set('web', daySourceStats)
const hour = msg.createdAt.getUTCHours()
hourlyBuckets[hour]!.messageCount++
hourlyBuckets[hour]!.totalTokens += (msg.inputTokens ?? 0) + (msg.outputTokens ?? 0)
const userId = chatUserMap.get(msg.chatId)
if (userId) {
const userStats = byUserMap.get(userId) ?? { messageCount: 0, inputTokens: 0, outputTokens: 0, totalDurationMs: 0 }
userStats.messageCount++
userStats.inputTokens += msg.inputTokens ?? 0
userStats.outputTokens += msg.outputTokens ?? 0
userStats.totalDurationMs += msg.durationMs ?? 0
byUserMap.set(userId, userStats)
}
}
for (const usage of apiUsageData) {
const { source } = usage
const sourceStats = bySourceMap.get(source) ?? { requests: 0, inputTokens: 0, outputTokens: 0, totalDurationMs: 0 }
sourceStats.requests++
sourceStats.inputTokens += usage.inputTokens ?? 0
sourceStats.outputTokens += usage.outputTokens ?? 0
sourceStats.totalDurationMs += usage.durationMs ?? 0
bySourceMap.set(source, sourceStats)
totalMessages++
totalInputTokens += usage.inputTokens ?? 0
totalOutputTokens += usage.outputTokens ?? 0
if (usage.durationMs && usage.durationMs > 0) {
totalDurationMs += usage.durationMs
messagesWithDuration++
}
const modelKey = usage.model ?? 'unknown'
const modelStats = byModelMap.get(modelKey) ?? { messageCount: 0, inputTokens: 0, outputTokens: 0, totalDurationMs: 0, positive: 0, negative: 0 }
modelStats.messageCount++
modelStats.inputTokens += usage.inputTokens ?? 0
modelStats.outputTokens += usage.outputTokens ?? 0
modelStats.totalDurationMs += usage.durationMs ?? 0
byModelMap.set(modelKey, modelStats)
// Also add to dailyMap so the chart includes API usage
const dateStr = usage.createdAt.toISOString().split('T')[0]!
if (!dailyMap.has(dateStr)) {
dailyMap.set(dateStr, new Map())
}
const dayModelMap = dailyMap.get(dateStr)!
const dayModelStats = dayModelMap.get(modelKey) ?? {
messageCount: 0,
inputTokens: 0,
outputTokens: 0,
totalDurationMs: 0,
}
dayModelStats.messageCount++
dayModelStats.inputTokens += usage.inputTokens ?? 0
dayModelStats.outputTokens += usage.outputTokens ?? 0
dayModelStats.totalDurationMs += usage.durationMs ?? 0
dayModelMap.set(modelKey, dayModelStats)
if (!dailyBySourceMap.has(dateStr)) dailyBySourceMap.set(dateStr, new Map())
const daySourceMap2 = dailyBySourceMap.get(dateStr)!
const daySourceStats2 = daySourceMap2.get(source) ?? { messageCount: 0, inputTokens: 0, outputTokens: 0 }
daySourceStats2.messageCount++
daySourceStats2.inputTokens += usage.inputTokens ?? 0
daySourceStats2.outputTokens += usage.outputTokens ?? 0
daySourceMap2.set(source, daySourceStats2)
const hour = usage.createdAt.getUTCHours()
hourlyBuckets[hour]!.messageCount++
hourlyBuckets[hour]!.totalTokens += (usage.inputTokens ?? 0) + (usage.outputTokens ?? 0)
}
const prevTotalMessages = prevMessagesWithStats.length
let prevTotalTokens = 0
for (const msg of prevMessagesWithStats) {
prevTotalTokens += (msg.inputTokens ?? 0) + (msg.outputTokens ?? 0)
}
function calcTrend(current: number, previous: number): number | null {
if (previous === 0) return current > 0 ? 100 : null
return Math.round(((current - previous) / previous) * 100)
}
const byModel = Array.from(byModelMap.entries())
.filter(([model]) => model !== 'unknown')
.map(([model, stats]) => ({
model,
messageCount: stats.messageCount,
inputTokens: stats.inputTokens,
outputTokens: stats.outputTokens,
avgDurationMs: stats.messageCount > 0 ? Math.round(stats.totalDurationMs / stats.messageCount) : 0,
positive: stats.positive,
negative: stats.negative,
}))
.sort((a, b) => b.messageCount - a.messageCount)
const bySource = Array.from(bySourceMap.entries())
.filter(([, stats]) => stats.requests > 0)
.map(([source, stats]) => ({
source,
requests: stats.requests,
inputTokens: stats.inputTokens,
outputTokens: stats.outputTokens,
totalTokens: stats.inputTokens + stats.outputTokens,
avgDurationMs: stats.requests > 0 ? Math.round(stats.totalDurationMs / stats.requests) : 0,
}))
.sort((a, b) => b.requests - a.requests)
const topUsers = Array.from(byUserMap.entries())
.map(([userId, stats]) => {
const userInfo = userInfoMap.get(userId)
return {
userId,
name: userInfo?.name ?? 'Unknown',
email: userInfo?.email ?? '',
image: userInfo?.image ?? '',
...stats,
totalTokens: stats.inputTokens + stats.outputTokens,
}
})
.sort((a, b) => b.messageCount - a.messageCount)
.slice(0, 10)
const activeUsers = byUserMap.size
const prevActiveUsers = prevUserIds.size
const daily = Array.from(dailyMap.entries())
.flatMap(([date, modelMap]) =>
Array.from(modelMap.entries()).map(([model, stats]) => ({
date,
model,
messageCount: stats.messageCount,
inputTokens: stats.inputTokens,
outputTokens: stats.outputTokens,
avgDurationMs: stats.messageCount > 0 ? Math.round(stats.totalDurationMs / stats.messageCount) : 0,
})),
)
.sort((a, b) => a.date.localeCompare(b.date))
// Generate all dates in the period for zero-filling
const allDates: string[] = []
const cursor = new Date(startDate)
cursor.setHours(0, 0, 0, 0)
const endDay = new Date(endDate)
endDay.setHours(0, 0, 0, 0)
while (cursor <= endDay) {
allDates.push(cursor.toISOString().split('T')[0]!)
cursor.setDate(cursor.getDate() + 1)
}
// Zero-fill the per-model daily array
const dailyDates = new Set(daily.map(d => d.date))
for (const date of allDates) {
if (!dailyDates.has(date)) {
daily.push({ date, model: '', messageCount: 0, inputTokens: 0, outputTokens: 0, avgDurationMs: 0 })
}
}
daily.sort((a, b) => a.date.localeCompare(b.date))
// Daily totals for message trend chart
const dailyTotalsMap = new Map<string, { messages: number, tokens: number }>()
for (const [date, modelMap] of dailyMap.entries()) {
let messages = 0
let tokens = 0
for (const stats of modelMap.values()) {
messages += stats.messageCount
tokens += stats.inputTokens + stats.outputTokens
}
dailyTotalsMap.set(date, { messages, tokens })
}
// Zero-fill dailyTotals for all dates in the period
const dailyTotals = allDates.map(date => {
const existing = dailyTotalsMap.get(date)
return { date, messages: existing?.messages ?? 0, tokens: existing?.tokens ?? 0 }
})
// Flatten dailyBySource with zero-fill
const dailyBySource: Array<{ date: string, source: string, messageCount: number, inputTokens: number, outputTokens: number }> = []
const allSourcesInData = new Set<string>()
for (const sourceMap of dailyBySourceMap.values()) {
for (const src of sourceMap.keys()) allSourcesInData.add(src)
}
for (const date of allDates) {
const sourceMap = dailyBySourceMap.get(date)
for (const src of allSourcesInData) {
const s = sourceMap?.get(src)
dailyBySource.push({
date,
source: src,
messageCount: s?.messageCount ?? 0,
inputTokens: s?.inputTokens ?? 0,
outputTokens: s?.outputTokens ?? 0,
})
}
}
const hourlyDistribution = hourlyBuckets.map((bucket, hour) => ({
hour,
messageCount: bucket.messageCount,
totalTokens: bucket.totalTokens,
}))
const pricingMap = await getModelPricingMap()
const estimatedCost = computeEstimatedCost(byModel, pricingMap)
const totalFeedback = positiveFeedback + negativeFeedback
const feedbackScore = totalFeedback > 0 ? Math.round((positiveFeedback / totalFeedback) * 100) : null
const result = {
period: {
days,
from: startDate.toISOString().split('T')[0]!,
to: endDate.toISOString().split('T')[0]!,
},
totals: {
messages: totalMessages,
inputTokens: totalInputTokens,
outputTokens: totalOutputTokens,
totalTokens: totalInputTokens + totalOutputTokens,
avgDurationMs: messagesWithDuration > 0 ? Math.round(totalDurationMs / messagesWithDuration) : 0,
activeUsers,
},
trends: {
messages: calcTrend(totalMessages, prevTotalMessages),
tokens: calcTrend(totalInputTokens + totalOutputTokens, prevTotalTokens),
activeUsers: calcTrend(activeUsers, prevActiveUsers),
},
feedback: {
positive: positiveFeedback,
negative: negativeFeedback,
total: totalFeedback,
score: feedbackScore,
},
byModel,
bySource,
topUsers,
daily,
dailyTotals,
dailyBySource,
hourlyDistribution,
estimatedCost,
availableSources,
availableModels,
}
requestLog.set({ resultMessageCount: totalMessages, resultModelCount: byModel.length })
return result
})