Admin & Dashboard Queries Deep Dive

Comprehensive reference for admin dashboard data, client management, advanced search, statistics, and reporting query functions.

Overview

The admin query layer powers the administration dashboard with optimized queries across two primary modules:

dashboard.queries.ts -- Dashboard statistics, engagement metrics, weekly/daily charts, and top-performing items
client.queries.ts (admin section) -- Client listing with pagination, admin dashboard data, advanced search with 20+ filter dimensions, and comprehensive statistics

Source Files

lib/db/queries/dashboard.queries.ts
lib/db/queries/client.queries.ts

Function Reference: dashboard.queries.ts

`getVotesReceivedCount`

Gets total votes received on a set of items.

async function getVotesReceivedCount(itemSlugs: string[]): Promise<number>

Parameters:

Parameter	Type	Required	Description
`itemSlugs`	`string[]`	Yes	Array of item slugs to count for

Returns: Promise<number> -- Total vote count

SQL Pattern:

SELECT count(*) FROM votes WHERE item_id IN (...);

`getCommentsReceivedCount`

Gets total non-deleted comments received on a set of items.

async function getCommentsReceivedCount(itemSlugs: string[]): Promise<number>

Parameters:

Parameter	Type	Required	Description
`itemSlugs`	`string[]`	Yes	Array of item slugs to count for

SQL Pattern:

SELECT count(*) FROM comments
WHERE item_id IN (...) AND deleted_at IS NULL;

`getUniqueItemsInteractedCount`

Gets count of unique items a user has interacted with (voted or commented on).

async function getUniqueItemsInteractedCount(
  clientProfileId: string
): Promise<number>

Parameters:

Parameter	Type	Required	Description
`clientProfileId`	`string`	Yes	Client profile ID

Returns: Promise<number> -- Count of unique items (approximate, may double-count items with both vote and comment)

SQL Pattern: Runs two COUNT(DISTINCT item_id) queries on votes and comments tables, then sums results.

Note: This is an approximation metric. For exact unique counts, a UNION query would be needed.

`getUserTotalActivityCount`

Gets a user's total activity count (votes + comments made by user).

async function getUserTotalActivityCount(
  clientProfileId: string
): Promise<number>

Parameters:

Parameter	Type	Required	Description
`clientProfileId`	`string`	Yes	Client profile ID

`getWeeklyEngagementData`

Gets weekly engagement data (votes and comments received on user's items) for charting.

async function getWeeklyEngagementData(
  itemSlugs: string[],
  weeks: number = 12
): Promise<Array<{ week: string; votes: number; comments: number }>>

Parameters:

Parameter	Type	Required	Default	Description
`itemSlugs`	`string[]`	Yes	--	Array of item slugs
`weeks`	`number`	No	`12`	Number of weeks to fetch

Returns: Array of { week: 'W1', votes: 5, comments: 3 } objects

SQL Pattern:

SELECT to_char(created_at, 'IYYY-IW') as week, count(*)
FROM votes
WHERE item_id IN (...) AND created_at >= ?
GROUP BY to_char(created_at, 'IYYY-IW')
ORDER BY to_char(created_at, 'IYYY-IW');

Performance Notes:

Runs two separate GROUP BY queries for votes and comments, then merges in-memory
Uses ISO week format (IYYY-IW) for consistent week numbering across year boundaries
Returns empty data (zeros) for weeks with no activity

`getDailyActivityData`

Gets daily activity data for activity chart (last N days).

async function getDailyActivityData(
  clientProfileId: string,
  itemSlugs: string[],
  days: number = 7
): Promise<Array<{
  date: string;
  submissions: number;
  views: number;
  engagement: number;
}>>

Parameters:

Parameter	Type	Required	Default	Description
`clientProfileId`	`string`	Yes	--	Client profile ID
`itemSlugs`	`string[]`	Yes	--	Item slugs
`days`	`number`	No	`7`	Number of days

Returns: Daily array with { date: 'Mon', submissions: 0, views: 0, engagement: 5 }

SQL Pattern: Uses EXTRACT(DOW FROM created_at) for locale-independent day-of-week grouping.

Note: submissions and views fields are placeholders (always 0); actual implementation is in the repository layer.

`getTopItemsEngagement`

Gets top performing items ranked by total engagement (votes + comments).

async function getTopItemsEngagement(
  itemSlugs: string[],
  limit: number = 5
): Promise<Array<{ itemId: string; votes: number; comments: number }>>

Parameters:

Parameter	Type	Required	Default	Description
`itemSlugs`	`string[]`	Yes	--	Array of item slugs
`limit`	`number`	No	`5`	Maximum items to return

Returns: Array sorted by total engagement descending

`getRecentSubmissionsCount`

Placeholder function. Items are stored in Git, not the database.

async function getRecentSubmissionsCount(_days: number = 7): Promise<number>

Returns: Always 0. Actual implementation is in the repository layer.

Function Reference: client.queries.ts (Admin Section)

`getClientProfiles`

Gets paginated client profiles with authentication data. Excludes admin users from results.

async function getClientProfiles(params: {
  page?: number;
  limit?: number;
  search?: string;
  status?: string;
  plan?: string;
  accountType?: string;
  provider?: string;
}): Promise<{
  profiles: ClientProfileWithAuth[];
  total: number;
  page: number;
  totalPages: number;
  limit: number;
}>

SQL Pattern:

Uses LEFT JOIN on userRoles and roles to exclude admin users (WHERE roles.id IS NULL)
Uses subquery for accountProvider to avoid duplicate rows from multiple OAuth accounts
Supports ILIKE search across username, displayName, company, name, and email
Provider filter uses an EXISTS subquery on the accounts table

`getClientProfileStats`

Gets basic client profile statistics.

async function getClientProfileStats(): Promise<{
  total: number;
  active: number;
  inactive: number;
  byPlan: Record<string, number>;
  byAccountType: Record<string, number>;
}>

`getEnhancedClientStats`

Gets comprehensive statistics with multiple dimensions including provider distribution, activity metrics, and growth rates.

async function getEnhancedClientStats(): Promise<{
  overview: { total, active, inactive, suspended, trial };
  byProvider: { credentials, google, github, facebook, twitter, linkedin, other };
  byPlan: Record<string, number>;
  byAccountType: Record<string, number>;
  byStatus: Record<string, number>;
  activity: { newThisWeek, newThisMonth, activeThisWeek, activeThisMonth };
  growth: { weeklyGrowth, monthlyGrowth };
}>

SQL Pattern: Uses a multi-dimensional GROUP BY across status, plan, accountType, and provider in a single query, then calculates activity metrics with date-range filters.

Performance Notes:

Executes multiple queries for different metrics
All queries exclude admin users via LEFT JOIN on roles
Growth rates are calculated as percentages of total

`getAdminDashboardData`

Optimized admin dashboard endpoint that returns clients, statistics, and pagination in a single call. Reduces database round trips.

async function getAdminDashboardData(params: {
  page: number;
  limit: number;
  search?: string;
  status?: string;
  plan?: string;
  accountType?: string;
  provider?: string;
  createdAfter?: Date;
  createdBefore?: Date;
  updatedAfter?: Date;
  updatedBefore?: Date;
}): Promise<{
  clients: ClientProfileWithAuth[];
  stats: { /* full EnhancedClientStats */ };
  pagination: { page, totalPages, total, limit };
}>

Supported Filters:

Text search (ILIKE across 5 fields)
Status, plan, account type
Auth provider (EXISTS subquery)
Date range (created/updated after/before)

`advancedClientSearch`

Full-featured search with 20+ filter dimensions, sorting, and metadata tracking.

async function advancedClientSearch(params: {
  page: number;
  limit: number;
  search?: string;
  status?: string;
  plan?: string;
  accountType?: string;
  provider?: string;
  dateRange?: { startDate?: Date; endDate?: Date };
  createdAfter?: Date;
  createdBefore?: Date;
  updatedAfter?: Date;
  updatedBefore?: Date;
  emailDomain?: string;
  companySearch?: string;
  locationSearch?: string;
  industrySearch?: string;
  minSubmissions?: number;
  maxSubmissions?: number;
  hasAvatar?: boolean;
  hasWebsite?: boolean;
  hasPhone?: boolean;
  emailVerified?: boolean;
  twoFactorEnabled?: boolean;
  sortBy?: 'createdAt' | 'updatedAt' | 'name' | 'email' | 'company' | 'totalSubmissions';
  sortOrder?: 'asc' | 'desc';
}): Promise<{
  clients: ClientProfileWithAuth[];
  pagination: { page, totalPages, total, limit };
  searchMetadata: { appliedFilters: string[], searchTime: number, resultCount: number };
}>

Unique Features:

Email domain filter: ILIKE '%@example.com%'
Boolean presence filters: hasAvatar, hasWebsite, hasPhone check for non-null, non-empty values
Numeric range filters: minSubmissions, maxSubmissions
Search metadata: Returns list of applied filters and result count
Configurable sorting: 6 sort fields with ascending/descending order

Performance Notes:

All ILIKE patterns use proper SQL escaping for special characters (%, _, \)
Admin users are excluded via LEFT JOIN pattern
Uses COUNT(DISTINCT) for accurate totals with JOINs

Performance Notes

Admin exclusion pattern -- All listing queries use LEFT JOIN userRoles / roles WHERE roles.id IS NULL to consistently exclude admin users from client views.
Subquery for provider -- Uses (SELECT provider FROM accounts WHERE ... LIMIT 1) subquery instead of JOIN to avoid row multiplication from multiple OAuth accounts.
Proper SQL escaping -- All text search inputs are escaped for ILIKE special characters (%, _, \) to prevent SQL injection through search terms.
ISO week handling -- getWeeklyEngagementData uses a custom getISOWeekString helper that matches PostgreSQL's to_char(date, 'IYYY-IW') for correct ISO week year handling at year boundaries.

Usage Examples

Loading the admin dashboard

import { getAdminDashboardData } from '@/lib/db/queries';

const dashboard = await getAdminDashboardData({
  page: 1,
  limit: 25,
  search: 'acme',
  status: 'active',
});

// dashboard.clients -- paginated client list
// dashboard.stats   -- full statistics
// dashboard.pagination -- page metadata

Advanced client search

import { advancedClientSearch } from '@/lib/db/queries';

const results = await advancedClientSearch({
  page: 1,
  limit: 10,
  emailDomain: 'company.com',
  plan: 'premium',
  hasAvatar: true,
  sortBy: 'totalSubmissions',
  sortOrder: 'desc',
});

console.log(results.searchMetadata.appliedFilters);
// ['Email domain: company.com', 'Plan: premium', 'Has avatar: true']

Overview​

Source Files​

Function Reference: dashboard.queries.ts​

getVotesReceivedCount​

getCommentsReceivedCount​

getUniqueItemsInteractedCount​

getUserTotalActivityCount​

getWeeklyEngagementData​

getDailyActivityData​

getTopItemsEngagement​

getRecentSubmissionsCount​

Function Reference: client.queries.ts (Admin Section)​

getClientProfiles​

getClientProfileStats​

getEnhancedClientStats​

getAdminDashboardData​

advancedClientSearch​

Performance Notes​

Usage Examples​

Loading the admin dashboard​

Advanced client search​

Overview

Source Files

Function Reference: dashboard.queries.ts

`getVotesReceivedCount`

`getCommentsReceivedCount`

`getUniqueItemsInteractedCount`

`getUserTotalActivityCount`

`getWeeklyEngagementData`

`getDailyActivityData`

`getTopItemsEngagement`

`getRecentSubmissionsCount`

Function Reference: client.queries.ts (Admin Section)

`getClientProfiles`

`getClientProfileStats`

`getEnhancedClientStats`

`getAdminDashboardData`

`advancedClientSearch`

Performance Notes

Usage Examples

Loading the admin dashboard

Advanced client search