Skip to content

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

TypeScript
9.7 KB
Raw Blame
/**
 * Rollup crons for LLM-usage events. Three independent setInterval timers
 * aggregate raw events upward:
 *
 *   usage_events  ──(every 60s, sweep last 48h)──▶  usage_5min
 *   usage_5min    ──(every 5m,  sweep last 48h)──▶  usage_hourly
 *   usage_hourly  ──(every 1h,  sweep last 2d)─────▶  usage_daily
 *
 * Each rollup is `INSERT ... ON CONFLICT DO UPDATE` so re-running the same
 * window is idempotent: GROUP BY produces the same dimension row, and
 * EXCLUDED.* overwrites the prior aggregate.
 *
 * The 5-min rollup is the only tier that resolves `team_id`. New events carry
 * the effective team snapshot in `usage_events.team_id`; context-bound channel
 * usage can still resolve through `context_ref -> team_contexts`. Workflow
 * usage must carry the canonical run_id prefix in `task_id`, but rollup does
 * not infer ownership from mutable actor/team membership.
 * Once resolved at that tier, hourly/daily rollups carry the `team_id` forward
 * as a plain dimension column.
 * "Team at the time of the call" is the event snapshot. Historical rows with
 * missing snapshots are migration/cleanup concerns, not compatibility fallbacks.
 */
import { type DbClient, pool } from '../db.js'
import { rootLogger } from '../observability/logger.js'

const log = rootLogger.child({ service: 'usage_rollup' })

// team_contexts is an M:N table, so context resolution collapses to one
// canonical team before joining usage rows.
//
// team_contexts: a context
// shared across teams would naively fan out the LEFT JOIN and double-count
// every event. The DISTINCT ON subquery collapses to one canonical team
// per context — earliest-bound wins, with team_id breaking ties — so each
// raw event contributes exactly one row to usage_5min. Multi-team contexts
// surface only under their canonical team in the breakdowns; consequence
// documented in the read endpoint's group-by behaviour.
const ROLLUP_5MIN_SQL = `
  WITH purged_recent AS (
    DELETE FROM usage_5min
    WHERE bucket >= NOW() - INTERVAL '48 hours'
    RETURNING 1
  )
  INSERT INTO usage_5min (
    bucket, host_ref, context_ref, team_id, llm_secret_name, user_id, sender,
    channel_type, recipe_name, cron_job_id, provider, model, source_kind,
    input_tokens, output_tokens, cache_read_tokens, cache_write_tokens,
    total_tokens, request_count
  )
  SELECT
    date_trunc('minute', e.ts) - (extract(minute from e.ts)::int % 5) * interval '1 minute' AS bucket,
    e.host_ref,
    e.context_ref,
    COALESCE(e.team_id, tc.team_id::text) AS team_id,
    e.llm_secret_name,
    e.user_id,
    e.sender,
    e.channel_type,
    e.recipe_name,
    e.cron_job_id,
    e.provider,
    e.model,
    e.source_kind,
    SUM(e.input_tokens)::bigint        AS input_tokens,
    SUM(e.output_tokens)::bigint       AS output_tokens,
    SUM(e.cache_read_tokens)::bigint   AS cache_read_tokens,
    SUM(e.cache_write_tokens)::bigint  AS cache_write_tokens,
    (SUM(e.input_tokens) + SUM(e.output_tokens))::bigint AS total_tokens,
    COUNT(*)::bigint AS request_count
  FROM usage_events e
  LEFT JOIN (
    SELECT DISTINCT ON (context_id) context_id, team_id
    FROM team_contexts
    ORDER BY context_id, created_at ASC, team_id ASC
  ) tc ON tc.context_id = e.context_ref
  WHERE e.ts >= NOW() - INTERVAL '48 hours'
  GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13
  ON CONFLICT (bucket, host_ref, context_ref_key, team_id_key,
               provider, model, llm_secret_key, source_kind,
               user_id_key, sender_key, channel_type_key,
               recipe_name_key, cron_job_id_key)
  DO UPDATE SET
    input_tokens       = EXCLUDED.input_tokens,
    output_tokens      = EXCLUDED.output_tokens,
    cache_read_tokens  = EXCLUDED.cache_read_tokens,
    cache_write_tokens = EXCLUDED.cache_write_tokens,
    total_tokens       = EXCLUDED.total_tokens,
    request_count      = EXCLUDED.request_count
`

const ROLLUP_HOURLY_SQL = `
  WITH purged_recent AS (
    DELETE FROM usage_hourly
    WHERE bucket >= NOW() - INTERVAL '48 hours'
    RETURNING 1
  )
  INSERT INTO usage_hourly (
    bucket, host_ref, context_ref, team_id, llm_secret_name, user_id, sender,
    channel_type, recipe_name, cron_job_id, provider, model, source_kind,
    input_tokens, output_tokens, cache_read_tokens, cache_write_tokens,
    total_tokens, request_count
  )
  SELECT
    date_trunc('hour', f.bucket) AS bucket,
    f.host_ref, f.context_ref, f.team_id, f.llm_secret_name, f.user_id, f.sender,
    f.channel_type, f.recipe_name, f.cron_job_id, f.provider, f.model, f.source_kind,
    SUM(f.input_tokens)::bigint        AS input_tokens,
    SUM(f.output_tokens)::bigint       AS output_tokens,
    SUM(f.cache_read_tokens)::bigint   AS cache_read_tokens,
    SUM(f.cache_write_tokens)::bigint  AS cache_write_tokens,
    SUM(f.total_tokens)::bigint        AS total_tokens,
    SUM(f.request_count)::bigint       AS request_count
  FROM usage_5min f
  WHERE f.bucket >= NOW() - INTERVAL '48 hours'
  GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13
  ON CONFLICT (bucket, host_ref, context_ref_key, team_id_key,
               provider, model, llm_secret_key, source_kind,
               user_id_key, sender_key, channel_type_key,
               recipe_name_key, cron_job_id_key)
  DO UPDATE SET
    input_tokens       = EXCLUDED.input_tokens,
    output_tokens      = EXCLUDED.output_tokens,
    cache_read_tokens  = EXCLUDED.cache_read_tokens,
    cache_write_tokens = EXCLUDED.cache_write_tokens,
    total_tokens       = EXCLUDED.total_tokens,
    request_count      = EXCLUDED.request_count
`

const ROLLUP_DAILY_SQL = `
  WITH purged_recent AS (
    DELETE FROM usage_daily
    WHERE bucket >= NOW() - INTERVAL '2 days'
    RETURNING 1
  )
  INSERT INTO usage_daily (
    bucket, host_ref, context_ref, team_id, llm_secret_name, user_id, sender,
    channel_type, recipe_name, cron_job_id, provider, model, source_kind,
    input_tokens, output_tokens, cache_read_tokens, cache_write_tokens,
    total_tokens, request_count
  )
  SELECT
    date_trunc('day', f.bucket) AS bucket,
    f.host_ref, f.context_ref, f.team_id, f.llm_secret_name, f.user_id, f.sender,
    f.channel_type, f.recipe_name, f.cron_job_id, f.provider, f.model, f.source_kind,
    SUM(f.input_tokens)::bigint        AS input_tokens,
    SUM(f.output_tokens)::bigint       AS output_tokens,
    SUM(f.cache_read_tokens)::bigint   AS cache_read_tokens,
    SUM(f.cache_write_tokens)::bigint  AS cache_write_tokens,
    SUM(f.total_tokens)::bigint        AS total_tokens,
    SUM(f.request_count)::bigint       AS request_count
  FROM usage_hourly f
  WHERE f.bucket >= NOW() - INTERVAL '2 days'
  GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13
  ON CONFLICT (bucket, host_ref, context_ref_key, team_id_key,
               provider, model, llm_secret_key, source_kind,
               user_id_key, sender_key, channel_type_key,
               recipe_name_key, cron_job_id_key)
  DO UPDATE SET
    input_tokens       = EXCLUDED.input_tokens,
    output_tokens      = EXCLUDED.output_tokens,
    cache_read_tokens  = EXCLUDED.cache_read_tokens,
    cache_write_tokens = EXCLUDED.cache_write_tokens,
    total_tokens       = EXCLUDED.total_tokens,
    request_count      = EXCLUDED.request_count
`

export type RollupResult = { rowCount: number }

export async function rollupUsageEventsTo5Min(db: DbClient = pool): Promise<RollupResult> {
  const res = await db.query(ROLLUP_5MIN_SQL)
  return { rowCount: res.rowCount ?? 0 }
}

export async function rollupUsage5MinToHourly(db: DbClient = pool): Promise<RollupResult> {
  const res = await db.query(ROLLUP_HOURLY_SQL)
  return { rowCount: res.rowCount ?? 0 }
}

export async function rollupUsageHourlyToDaily(db: DbClient = pool): Promise<RollupResult> {
  const res = await db.query(ROLLUP_DAILY_SQL)
  return { rowCount: res.rowCount ?? 0 }
}

export type StartUsageRollupOptions = {
  fiveMinIntervalMs: number
  hourlyIntervalMs: number
  dailyIntervalMs: number
}

let fiveMinHandle: ReturnType<typeof setInterval> | null = null
let hourlyHandle: ReturnType<typeof setInterval> | null = null
let dailyHandle: ReturnType<typeof setInterval> | null = null

async function safeRollup(
  name: string,
  fn: () => Promise<RollupResult>,
  successEvent: string
): Promise<void> {
  try {
    const result = await fn()
    if (result.rowCount > 0) {
      log.debug(
        { event: successEvent, rowCount: result.rowCount },
        `${name} rolled ${result.rowCount} rows`
      )
    }
  } catch (err) {
    log.error(
      {
        event: `${successEvent}_error`,
        err: err instanceof Error ? err.message : String(err),
      },
      `${name} rollup failed`
    )
  }
}

export function startUsageRollupCron(opts: StartUsageRollupOptions): void {
  if (fiveMinHandle || hourlyHandle || dailyHandle) return

  fiveMinHandle = setInterval(() => {
    void safeRollup('usage_events→5min', rollupUsageEventsTo5Min, 'usage_rollup_5min')
  }, opts.fiveMinIntervalMs)
  fiveMinHandle.unref()

  hourlyHandle = setInterval(() => {
    void safeRollup('5min→hourly', rollupUsage5MinToHourly, 'usage_rollup_hourly')
  }, opts.hourlyIntervalMs)
  hourlyHandle.unref()

  dailyHandle = setInterval(() => {
    void safeRollup('hourly→daily', rollupUsageHourlyToDaily, 'usage_rollup_daily')
  }, opts.dailyIntervalMs)
  dailyHandle.unref()

  log.info(
    {
      event: 'usage_rollup_started',
      fiveMinIntervalMs: opts.fiveMinIntervalMs,
      hourlyIntervalMs: opts.hourlyIntervalMs,
      dailyIntervalMs: opts.dailyIntervalMs,
    },
    'usage rollup crons started'
  )
}

export function stopUsageRollupCron(): void {
  if (fiveMinHandle) {
    clearInterval(fiveMinHandle)
    fiveMinHandle = null
  }
  if (hourlyHandle) {
    clearInterval(hourlyHandle)
    hourlyHandle = null
  }
  if (dailyHandle) {
    clearInterval(dailyHandle)
    dailyHandle = null
  }
}