Admin Analytics Optimized Repository

The AdminAnalyticsOptimizedRepository class provides high-performance analytics queries for the admin dashboard. It uses raw SQL via Drizzle ORM with an in-memory TTL cache to reduce database load for frequently accessed metrics.

Source file: template/lib/repositories/admin-analytics-optimized.repository.ts

Architecture Overview

Admin Dashboard UI
        |
  API Route Handler
        |
  AdminAnalyticsOptimizedRepository
        |
    SimpleCache (in-memory TTL)
        |
    Drizzle ORM (raw SQL)
        |
    PostgreSQL

All queries use db.execute(sql\...`)` with parameterized inputs for SQL injection safety. Results are cached with configurable TTLs per query type.

Exported Interfaces

`UserGrowthTrend`

interface UserGrowthTrend {
  month: string;    // e.g. "Jan 2025"
  users: number;    // new users that month
  active: number;   // cumulative total
}

`ActivityTrend`

interface ActivityTrend {
  day: string;      // e.g. "Mar 5"
  views: number;    // always 0 (views not tracked in current schema)
  votes: number;
  comments: number;
}

`TopItem`

interface TopItem {
  name: string;
  views: number;     // always 0 (views not tracked)
  votes: number;
  category?: string;
}

`RecentActivity`

interface RecentActivity {
  type: 'user_signup' | 'submission' | 'comment' | 'vote';
  description: string;
  timestamp: string;   // ISO 8601
  user?: string;
}

Cache System

The repository uses a SimpleCache class -- an in-memory Map-based cache with TTL expiration.

Cache Keys and TTLs

Cache Key	TTL	Description
`user_growth`	10 minutes	User growth trend data
`activity_trends`	5 minutes	Daily activity data
`top_items`	15 minutes	Top items by vote count
`recent_activity`	2 minutes	Recent activity feed
`user_stats`	10 minutes	Aggregate user statistics
`activity_stats`	5 minutes	Aggregate activity statistics

Cache Methods

cache.set(key: string, data: any, ttl: number): void
cache.get(key: string): any | null
cache.clear(): void
cache.invalidate(pattern: string): void   // deletes keys containing pattern

Query Methods

`getUserGrowthTrends(months?): Promise<UserGrowthTrend[]>`

Returns monthly user registration trends with cumulative totals.

async getUserGrowthTrends(months: number = 12): Promise<UserGrowthTrend[]>

Parameter	Type	Default	Range
`months`	`number`	`12`	1 -- 120

SQL pattern: Uses a CTE (monthly_counts) to group users.created_at by DATE_TRUNC('month', ...), then a cumulative window function (SUM(...) OVER (ORDER BY month_start)) to compute running totals. Results are limited to the requested number of months and ordered ascending.

WITH monthly_counts AS (
  SELECT DATE_TRUNC('month', created_at) AS month_start,
         COUNT(*) AS new_users
  FROM users WHERE deleted_at IS NULL
  GROUP BY DATE_TRUNC('month', created_at)
),
cum_counts AS (
  SELECT month_start, new_users,
         SUM(new_users) OVER (ORDER BY month_start) AS cumulative_users
  FROM monthly_counts
)
SELECT * FROM (
  SELECT * FROM cum_counts ORDER BY month_start DESC LIMIT $1
) t ORDER BY month_start ASC

`getActivityTrends(days?): Promise<ActivityTrend[]>`

Returns daily vote and comment activity for the specified number of days.

async getActivityTrends(days: number = 7): Promise<ActivityTrend[]>

Parameter	Type	Default	Range
`days`	`number`	`7`	1 -- 365

SQL pattern: Uses UNION ALL to combine daily counts from votes and comments tables, then pivots with conditional SUM(CASE WHEN ...) aggregation. Missing days are filled with zeros by generating a complete date range in JavaScript and using an O(1) Map lookup.

`getTopItems(limit?): Promise<TopItem[]>`

Returns items ranked by total vote count.

async getTopItems(limit: number = 10): Promise<TopItem[]>

Parameter	Type	Default	Range
`limit`	`number`	`10`	1 -- 1000

SQL pattern: Uses a CTE with ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) window function to rank items by vote count, then filters to the top N.

WITH item_rankings AS (
  SELECT item_id, COUNT(*) as vote_count,
         ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) as rank
  FROM votes GROUP BY item_id
)
SELECT item_id, vote_count, rank
FROM item_rankings WHERE rank <= $1
ORDER BY rank ASC

`getRecentActivity(limit?): Promise<RecentActivity[]>`

Returns a mixed feed of recent user signups, comments, and votes.

async getRecentActivity(limit: number = 10): Promise<RecentActivity[]>

Parameter	Type	Default	Range
`limit`	`number`	`10`	1 -- 500

SQL pattern: Uses parenthesized UNION ALL subqueries, each with their own ORDER BY ... DESC LIMIT N clause, pulling roughly one-third of the total limit from each source (users, comments, votes). The final result is ordered by activity_time DESC and limited.

`getBatchAnalytics(options?): Promise<BatchResult>`

Executes all four analytics queries in parallel via Promise.all for optimal dashboard loading performance.

async getBatchAnalytics(options?: {
  userGrowthMonths?: number;     // default 12, max 120
  activityTrendDays?: number;    // default 7, max 365
  topItemsLimit?: number;        // default 10, max 1000
  recentActivityLimit?: number;  // default 10, max 500
}): Promise<{
  userGrowth: UserGrowthTrend[];
  activityTrends: ActivityTrend[];
  topItems: TopItem[];
  recentActivity: RecentActivity[];
}>

All input parameters are clamped to safe bounds before execution.

Cache Management

`clearCache(): Promise<void>`

Clears all cached data. Useful after data mutations that affect analytics.

`invalidateCache(pattern): Promise<void>`

Selectively removes cache entries whose keys contain the given pattern string.

await repo.invalidateCache('user_growth');  // clears user growth caches

Performance Monitoring

`getQueryPerformanceStats(): Promise<PerfStats>`

Returns cache hit rate and query timing statistics.

async getQueryPerformanceStats(): Promise<{
  cacheHitRate: number;       // 0.0 - 1.0
  totalQueries: number;
  cachedQueries: number;
  averageQueryTime: number;   // milliseconds
}>

Note: Currently returns mock data. In production, integrate with actual performance monitoring.

Input Validation

All public methods clamp numeric inputs to safe bounds using Math.max and Math.min to prevent excessively large queries:

Months: 1 to 120
Days: 1 to 365
Limits: 1 to 500/1000

Usage Example

import { AdminAnalyticsOptimizedRepository } from '@/lib/repositories/admin-analytics-optimized.repository';

const analytics = new AdminAnalyticsOptimizedRepository();

// Load everything for the dashboard in one call
const dashboard = await analytics.getBatchAnalytics({
  userGrowthMonths: 6,
  activityTrendDays: 30,
  topItemsLimit: 5,
  recentActivityLimit: 20,
});

// Individual queries (cached)
const trends = await analytics.getUserGrowthTrends(12);
const activity = await analytics.getActivityTrends(7);

// Cache management
await analytics.clearCache();

File	Relationship
`lib/db/drizzle.ts`	Database connection (`db`) and Drizzle instance
`drizzle-orm`	`sql` template tag for parameterized queries
`lib/repositories/client-dashboard.repository.ts`	Client-facing dashboard (complementary)

Architecture Overview​

Exported Interfaces​

UserGrowthTrend​

ActivityTrend​

TopItem​

RecentActivity​

Cache System​

Cache Keys and TTLs​

Cache Methods​

Query Methods​

getUserGrowthTrends(months?): Promise<UserGrowthTrend[]>​

getActivityTrends(days?): Promise<ActivityTrend[]>​

getTopItems(limit?): Promise<TopItem[]>​

getRecentActivity(limit?): Promise<RecentActivity[]>​

getBatchAnalytics(options?): Promise<BatchResult>​

Cache Management​

clearCache(): Promise<void>​

invalidateCache(pattern): Promise<void>​

Performance Monitoring​

getQueryPerformanceStats(): Promise<PerfStats>​

Input Validation​

Usage Example​

Related Files​