'use server' import type { AuditLogActivity, AuditLogActivityQuery } from '@admin/actions/audit/types' import { buildAuditLogWhere } from '@admin/actions/audit/build-audit-log-where' import { requireRole, UserRole } from '@admin/auth/middleware' import db from '@admin/db' import { auditLog } from '@admin/db/schema' import { getAuditRangeBucketCount } from '@admin/utils/audit/get-audit-range-bucket-count' import { getAuditRangeStart } from '@admin/utils/audit/get-audit-range-start' import { and, sql } from 'drizzle-orm' const IANA_TIME_ZONE = /^[A-Za-z_]+(?:\/[A-Za-z0-9_+-]+)*$/ export async function getAuditLogActivity(query: AuditLogActivityQuery): Promise { await requireRole([UserRole.ADMIN]) const now = new Date() const from = getAuditRangeStart(query.range, now) const bucketCount = getAuditRangeBucketCount(query.range) const bucketSeconds = (now.getTime() - from.getTime()) / 1000 / bucketCount const fromSeconds = from.getTime() / 1000 const timeZone = IANA_TIME_ZONE.test(query.timeZone) ? query.timeZone : 'UTC' const where = and( buildAuditLogWhere({ ...query, createdAtFrom: from.toISOString(), createdAtTo: undefined }), sql`${auditLog.createdAt} <= ${now.toISOString()}` ) // Inlined rather than bound: the same expression must appear verbatim in SELECT and GROUP BY, // and bound parameters would number differently in each. Both numbers are computed here, and // the time zone passed the IANA pattern above, so none of them can carry user input. const bucketExpression = sql`floor((extract(epoch from ${auditLog.createdAt}) - ${sql.raw(String(fromSeconds))}) / ${sql.raw(String(bucketSeconds))})` const dayExpression = sql`to_char(${auditLog.createdAt} at time zone ${sql.raw(`'${timeZone}'`)}, 'YYYY-MM-DD')` const [bucketRows, dayRows] = await Promise.all([ db .select({ bucket: bucketExpression, count: sql`count(*)` }) .from(auditLog) .where(where) .groupBy(bucketExpression), db .select({ day: dayExpression, count: sql`count(*)` }) .from(auditLog) .where(where) .groupBy(dayExpression) ]) const buckets = Array.from({ length: bucketCount }, () => 0) let total = 0 for (const row of bucketRows) { const count = Number(row.count ?? 0) const index = Math.min(bucketCount - 1, Math.max(0, Math.floor(Number(row.bucket)))) buckets[index] = (buckets[index] ?? 0) + count total += count } const days: Record = {} for (const row of dayRows) { days[row.day] = Number(row.count ?? 0) } return { total, from: from.toISOString(), to: now.toISOString(), buckets, days } }