Function identifyBotSuspects

web/src/server/free-session/abuse-detection.ts:68–405

(params: {
  logger: Logger
})

const CREATION_CLUSTER_MIN_SIZE = 4

export async function identifyBotSuspects(params: {
  logger: Logger
}): Promise<SweepReport> {
  const { logger } = params
  const now = new Date()
  const cutoff = new Date(now.getTime() - WINDOW_HOURS * 3600_000)
  // postgres-js can't encode a JS Date as an ad-hoc template parameter
  // (it only knows how when the driver recognises the target column's
  // type). Embed the ISO string with an explicit cast so the FILTER
  // clauses below go through cleanly.
  const cutoffIso = cutoff.toISOString()

  const sessions = await db
    .select({
      user_id: schema.freeSession.user_id,
      status: schema.freeSession.status,
      model: schema.freeSession.model,
      email: schema.user.email,
      name: schema.user.name,
      handle: schema.user.handle,
      banned: schema.user.banned,
      user_created_at: schema.user.created_at,
    })
    .from(schema.freeSession)
    .leftJoin(schema.user, eq(schema.freeSession.user_id, schema.user.id))

  if (sessions.length === 0) {
    return {
      generatedAt: now,
      totalSessions: 0,
      activeCount: 0,
      queuedCount: 0,
      suspects: [],
      creationClusters: [],
    }
  }

  const userIds = sessions.map((s) => s.user_id)

  const msgStats = await db
    .select({
      user_id: schema.message.user_id,
      msgs24h: sql<number>`COUNT(*) FILTER (WHERE ${schema.message.finished_at} >= ${cutoffIso}::timestamptz)`,
      distinctHours24h: sql<number>`COUNT(DISTINCT EXTRACT(HOUR FROM ${schema.message.finished_at})) FILTER (WHERE ${schema.message.finished_at} >= ${cutoffIso}::timestamptz)`,
      lifetime: sql<number>`COUNT(*)`,
    })
    .from(schema.message)
    .where(
      and(
        inArray(schema.message.user_id, userIds),
        inArray(schema.message.agent_id, FREEBUFF_ROOT_AGENT_IDS),
      ),
    )
    .groupBy(schema.message.user_id)
  const statsByUser = new Map(msgStats.map((m) => [m.user_id!, m]))

  // Agent diversity is a counter-signal: real users fan out across basher,
  // file-picker, code-reviewer, etc.; bot farms stay narrow on the root agent.