Skip to content

Evenfire is an intelligence layer platform for running LLM agents that take real actions, on your infrastructure

TypeScript
7.6 KB
Raw Blame
/**
 * Read service for the admin LLM-usage dashboard. Tier-routes by `interval`
 * and reads from rollup tables only — never from `usage_events`.
 *
 * Group-by and filter columns are gated by a fixed allowlist; the mapping
 * is the only place a caller-controlled string ever touches SQL
 * identifiers. Time bounds and filter values are bound parameters.
 */
import { type DbClient, pool } from '../db.js'

export type UsageInterval = '5min' | 'hour' | 'day'

export type UsageGroupBy =
  | 'model'
  | 'provider'
  | 'host_ref'
  | 'team_id'
  | 'recipe_name'
  | 'llm_secret_name'
  | 'user_id'
  | 'sender'
  | 'channel_type'
  | 'source_kind'

export type UsageFilters = Partial<Record<UsageGroupBy, string[]>>

export type UsageSeriesRow = {
  bucket: string
  group: string | null
  input_tokens: number
  output_tokens: number
  cache_read_tokens: number
  cache_write_tokens: number
  total_tokens: number
  request_count: number
}

export type UsageTotalsRow = {
  group: string | null
  input_tokens: number
  output_tokens: number
  cache_read_tokens: number
  cache_write_tokens: number
  total_tokens: number
  request_count: number
}

const TIER_BY_INTERVAL: Record<UsageInterval, { table: string; retentionDays: number }> = {
  '5min': { table: 'usage_5min', retentionDays: 7 },
  hour: { table: 'usage_hourly', retentionDays: 30 },
  day: { table: 'usage_daily', retentionDays: Number.POSITIVE_INFINITY },
}

const ALLOWED_GROUP_BY: ReadonlySet<UsageGroupBy> = new Set<UsageGroupBy>([
  'model',
  'provider',
  'host_ref',
  'team_id',
  'recipe_name',
  'llm_secret_name',
  'user_id',
  'sender',
  'channel_type',
  'source_kind',
])

export function isAllowedGroupBy(value: unknown): value is UsageGroupBy {
  return typeof value === 'string' && ALLOWED_GROUP_BY.has(value as UsageGroupBy)
}

export function isUsageInterval(value: unknown): value is UsageInterval {
  return value === '5min' || value === 'hour' || value === 'day'
}

export type RangeError = {
  error: 'range_too_old_for_interval'
  interval: UsageInterval
  retentionDays: number
}

/**
 * The chosen rollup tier must cover the requested `from`. We can't read 5-min
 * resolution for a window older than 7 days because the rows have been pruned.
 * Day tier has no upper bound.
 */
export function checkIntervalCoversRange(
  interval: UsageInterval,
  from: Date,
  now: Date = new Date()
): RangeError | null {
  const tier = TIER_BY_INTERVAL[interval]
  if (!Number.isFinite(tier.retentionDays)) return null
  const cutoffMs = now.getTime() - tier.retentionDays * 24 * 60 * 60 * 1000
  if (from.getTime() < cutoffMs) {
    return {
      error: 'range_too_old_for_interval',
      interval,
      retentionDays: tier.retentionDays,
    }
  }
  return null
}

function buildFilterSql(
  filters: UsageFilters,
  startIndex: number
): { sql: string; params: string[] } {
  const clauses: string[] = []
  const params: string[] = []
  let idx = startIndex
  for (const key of Object.keys(filters) as UsageGroupBy[]) {
    if (!ALLOWED_GROUP_BY.has(key)) continue
    const values = filters[key]
    if (!Array.isArray(values) || values.length === 0) continue
    const placeholders: string[] = []
    for (const v of values) {
      params.push(v)
      placeholders.push(`$${idx++}`)
    }
    clauses.push(`${key} IN (${placeholders.join(',')})`)
  }
  return { sql: clauses.length > 0 ? ` AND ${clauses.join(' AND ')}` : '', params }
}

export type QueryUsageSeriesOptions = {
  from: Date
  to: Date
  interval: UsageInterval
  groupBy: UsageGroupBy
  filters?: UsageFilters
  db?: DbClient
}

export async function queryUsageSeries(opts: QueryUsageSeriesOptions): Promise<UsageSeriesRow[]> {
  const { from, to, interval, groupBy } = opts
  const filters = opts.filters ?? {}
  const db = opts.db ?? pool
  const tier = TIER_BY_INTERVAL[interval]

  const filterSql = buildFilterSql(filters, 3)
  const sql = `
    SELECT
      bucket,
      ${groupBy} AS group_col,
      SUM(input_tokens)::bigint        AS input_tokens,
      SUM(output_tokens)::bigint       AS output_tokens,
      SUM(cache_read_tokens)::bigint   AS cache_read_tokens,
      SUM(cache_write_tokens)::bigint  AS cache_write_tokens,
      SUM(total_tokens)::bigint        AS total_tokens,
      SUM(request_count)::bigint       AS request_count
    FROM ${tier.table}
    WHERE bucket >= $1 AND bucket < $2${filterSql.sql}
    GROUP BY bucket, ${groupBy}
    ORDER BY bucket ASC, group_col ASC NULLS LAST
  `
  const params: unknown[] = [from.toISOString(), to.toISOString(), ...filterSql.params]
  const result = await db.query(sql, params)
  return (result.rows as Record<string, unknown>[]).map(rowToSeries)
}

export type QueryUsageTotalsOptions = {
  from: Date
  to: Date
  interval: UsageInterval
  groupBy: UsageGroupBy
  filters?: UsageFilters
  limit?: number
  db?: DbClient
}

export async function queryUsageTotals(opts: QueryUsageTotalsOptions): Promise<UsageTotalsRow[]> {
  const { from, to, interval, groupBy } = opts
  const filters = opts.filters ?? {}
  const limit = clampLimit(opts.limit)
  const db = opts.db ?? pool
  const tier = TIER_BY_INTERVAL[interval]

  const filterSql = buildFilterSql(filters, 3)
  const sql = `
    SELECT
      ${groupBy} AS group_col,
      SUM(input_tokens)::bigint        AS input_tokens,
      SUM(output_tokens)::bigint       AS output_tokens,
      SUM(cache_read_tokens)::bigint   AS cache_read_tokens,
      SUM(cache_write_tokens)::bigint  AS cache_write_tokens,
      SUM(total_tokens)::bigint        AS total_tokens,
      SUM(request_count)::bigint       AS request_count
    FROM ${tier.table}
    WHERE bucket >= $1 AND bucket < $2${filterSql.sql}
    GROUP BY ${groupBy}
    ORDER BY total_tokens DESC NULLS LAST, group_col ASC NULLS LAST
    LIMIT ${limit}
  `
  const params: unknown[] = [from.toISOString(), to.toISOString(), ...filterSql.params]
  const result = await db.query(sql, params)
  return (result.rows as Record<string, unknown>[]).map(rowToTotals)
}

function clampLimit(limit: number | undefined): number {
  if (typeof limit !== 'number' || !Number.isFinite(limit)) return 50
  if (limit < 1) return 1
  if (limit > 500) return 500
  return Math.floor(limit)
}

function toNumber(raw: unknown): number {
  if (typeof raw === 'number') return raw
  if (typeof raw === 'string') {
    const n = Number(raw)
    return Number.isFinite(n) ? n : 0
  }
  if (typeof raw === 'bigint') return Number(raw)
  return 0
}

function toStringOrNull(raw: unknown): string | null {
  if (raw === null || raw === undefined) return null
  if (typeof raw === 'string') return raw
  return String(raw)
}

function bucketToIso(raw: unknown): string {
  if (raw instanceof Date) return raw.toISOString()
  if (typeof raw === 'string') {
    const d = new Date(raw)
    if (!Number.isNaN(d.getTime())) return d.toISOString()
    return raw
  }
  return String(raw)
}

function rowToSeries(row: Record<string, unknown>): UsageSeriesRow {
  return {
    bucket: bucketToIso(row.bucket),
    group: toStringOrNull(row.group_col),
    input_tokens: toNumber(row.input_tokens),
    output_tokens: toNumber(row.output_tokens),
    cache_read_tokens: toNumber(row.cache_read_tokens),
    cache_write_tokens: toNumber(row.cache_write_tokens),
    total_tokens: toNumber(row.total_tokens),
    request_count: toNumber(row.request_count),
  }
}

function rowToTotals(row: Record<string, unknown>): UsageTotalsRow {
  return {
    group: toStringOrNull(row.group_col),
    input_tokens: toNumber(row.input_tokens),
    output_tokens: toNumber(row.output_tokens),
    cache_read_tokens: toNumber(row.cache_read_tokens),
    cache_write_tokens: toNumber(row.cache_write_tokens),
    total_tokens: toNumber(row.total_tokens),
    request_count: toNumber(row.request_count),
  }
}