Report & Moderation Queries Deep Dive

Comprehensive reference for all content reporting and user moderation database query functions, including report CRUD, moderation history tracking, user status management (warn, suspend, ban), and reporting statistics.

Overview

The report and moderation query layer is organized into two complementary modules:

• report.queries.ts -- Content report CRUD, paginated listing with search and filters, report statistics by status/type/reason, and duplicate report prevention
• moderation.queries.ts -- Moderation history logging, user moderation actions (warn, suspend, ban, unsuspend, unban), and user status helpers

Reports are submitted by client users against content (items or comments). Admins review reports and take moderation actions, which are tracked in a separate moderation history table for audit purposes.

Source Files

lib/db/queries/report.queries.ts
lib/db/queries/moderation.queries.ts

---

Function Reference: report.queries.ts

Types

type ReportWithReporter = Report & {
  reporter: {
    id: string;
    name: string;
    email: string;
    avatar: string | null;
  } | null;
  reviewer: {
    id: string;
    email: string | null;
  } | null;
};

createReport

Creates a new content report. Automatically sets status to PENDING.

async function createReport(data: {
  contentType: ReportContentTypeValues;
  contentId: string;
  reason: ReportReasonValues;
  details?: string;
  reportedBy: string;
}): Promise<Report>

Parameters:

Parameter	Type	Required	Description
contentType	ReportContentTypeValues	Yes	Type of content ('item' or 'comment')
contentId	string	Yes	ID of the reported content
reason	ReportReasonValues	Yes	Reason for reporting ('spam', 'harassment', 'inappropriate', 'other')
details	string	No	Additional details from the reporter
reportedBy	string	Yes	Client profile ID of the reporter

Returns: The created report record

SQL Pattern:

INSERT INTO reports (content_type, content_id, reason, details, reported_by, status)
VALUES (?, ?, ?, ?, ?, 'pending')
RETURNING *;

---

getReportById

Gets a report by ID with reporter and reviewer information. Performs two queries: one for the report with reporter JOIN, and a second for the reviewer if present.

async function getReportById(
  id: string
): Promise<ReportWithReporter | null>

SQL Pattern:

-- Report with reporter info
SELECT reports.*, client_profiles.id, name, email, avatar
FROM reports
LEFT JOIN client_profiles ON reports.reported_by = client_profiles.id
WHERE reports.id = ?
LIMIT 1;

-- Reviewer info (separate query, only if reviewedBy exists)
SELECT id, email FROM users WHERE id = ? LIMIT 1;

Design Note: The reviewer lookup is a separate query to avoid a second LEFT JOIN, since reviewers are admin users from the users table while reporters are from client_profiles.

---

getReports

Gets all reports with pagination, search, and filtering. Returns reports with reporter information.

async function getReports(params: {
  page?: number;
  limit?: number;
  search?: string;
  status?: ReportStatusValues;
  contentType?: ReportContentTypeValues;
  reason?: ReportReasonValues;
}): Promise<{
  reports: ReportWithReporter[];
  total: number;
  page: number;
  totalPages: number;
  limit: number;
}>

Parameters:

Parameter	Type	Required	Default	Description
page	number	No	1	Page number
limit	number	No	10	Results per page
search	string	No	--	Search in content ID, details, reporter name/email
status	ReportStatusValues	No	--	Filter by status
contentType	ReportContentTypeValues	No	--	Filter by content type
reason	ReportReasonValues	No	--	Filter by reason

Returns: Paginated reports list with metadata

SQL Pattern:

-- Count query
SELECT count(*) FROM reports
LEFT JOIN client_profiles ON reports.reported_by = client_profiles.id
WHERE ...;

-- Data query
SELECT reports.*, client_profiles.id, name, email, avatar
FROM reports
LEFT JOIN client_profiles ON reports.reported_by = client_profiles.id
WHERE (content_id ILIKE ? OR details ILIKE ? OR name ILIKE ? OR email ILIKE ?)
  AND status = ?
  AND content_type = ?
  AND reason = ?
ORDER BY reports.created_at DESC
LIMIT ? OFFSET ?;

Search behavior: Uses ILIKE on four fields (contentId, details, reporter name, reporter email) with proper SQL wildcard escaping for %, _, and \ characters.

Performance Note: Reviewer information is not included in list results (reviewer: null) to avoid N+1 queries for the listing view.

---

updateReport

Updates report status, resolution, review note, and reviewer. Automatically manages timestamp fields based on the status change.

async function updateReport(
  id: string,
  data: {
    status?: ReportStatusValues;
    resolution?: ReportResolutionValues;
    reviewNote?: string;
    reviewedBy?: string;
  }
): Promise<Report | null>

Parameters:

Parameter	Type	Required	Description
id	string	Yes	Report ID
status	ReportStatusValues	No	New status
resolution	ReportResolutionValues	No	Resolution type
reviewNote	string	No	Reviewer's note
reviewedBy	string	No	Admin user ID of the reviewer

Automatic timestamp behavior:

• updatedAt is always set to current time
• reviewedAt is set when status changes from PENDING, or when reviewedBy is provided
• resolvedAt is set when status becomes RESOLVED or DISMISSED

SQL Pattern:

UPDATE reports
SET status = ?, resolution = ?, review_note = ?,
    reviewed_by = ?, reviewed_at = ?, resolved_at = ?, updated_at = NOW()
WHERE id = ?
RETURNING *;

---

getReportStats

Gets comprehensive report statistics grouped by status, content type, and reason.

async function getReportStats(): Promise<{
  total: number;
  byStatus: Record<string, number>;
  byContentType: Record<string, number>;
  byReason: Record<string, number>;
  pendingCount: number;
  resolvedCount: number;
}>

Returns:

• total -- Total number of reports
• byStatus -- Counts for each status (pending, reviewed, resolved, dismissed)
• byContentType -- Counts for each content type (item, comment)
• byReason -- Counts for each reason (spam, harassment, inappropriate, other)
• pendingCount -- Shortcut for pending reports count
• resolvedCount -- Combined count of resolved + dismissed reports

SQL Pattern:

-- Total count
SELECT count(*) FROM reports;

-- By status
SELECT status, count(*) FROM reports GROUP BY status;

-- By content type
SELECT content_type, count(*) FROM reports GROUP BY content_type;

-- By reason
SELECT reason, count(*) FROM reports GROUP BY reason;

Note: All four GROUP BY queries are run sequentially. Default zero values are set for all known enum values before populating from results.

---

hasUserReportedContent

Checks if a user has already reported specific content. Used to prevent duplicate reports.

async function hasUserReportedContent(
  reportedBy: string,
  contentType: ReportContentTypeValues,
  contentId: string
): Promise<boolean>

Parameters:

Parameter	Type	Required	Description
reportedBy	string	Yes	Client profile ID
contentType	ReportContentTypeValues	Yes	Content type
contentId	string	Yes	Content ID

Returns: true if the user has already reported this content

SQL Pattern:

SELECT id FROM reports
WHERE reported_by = ? AND content_type = ? AND content_id = ?
LIMIT 1;

---

Function Reference: moderation.queries.ts

Types

type ModerationHistoryWithDetails = ModerationHistoryRecord & {
  user: {
    id: string;
    name: string;
    email: string;
  } | null;
  performedByUser: {
    id: string;
    email: string | null;
  } | null;
};

Moderation History

createModerationHistory

Creates a new moderation history entry, recording an action taken against a user.

async function createModerationHistory(data: {
  userId: string;
  action: ModerationActionValues;
  reason?: string;
  reportId?: string;
  performedBy?: string;
  contentType?: ReportContentTypeValues;
  contentId?: string;
  details?: Record<string, unknown>;
}): Promise<ModerationHistoryRecord>

Parameters:

Parameter	Type	Required	Description
userId	string	Yes	Client profile ID of the target user
action	ModerationActionValues	Yes	Action taken (e.g., warn, suspend, ban)
reason	string	No	Reason for the action
reportId	string	No	Associated report ID
performedBy	string	No	Admin user ID who performed the action
contentType	ReportContentTypeValues	No	Content type related to the action
contentId	string	No	Content ID related to the action
details	Record<string, unknown>	No	Additional structured data

Returns: The created moderation history record

SQL Pattern:

INSERT INTO moderation_history (user_id, action, reason, report_id, performed_by,
  content_type, content_id, details)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
RETURNING *;

---

getModerationHistoryByUser

Gets the moderation history for a specific user, with user details and performer information.

async function getModerationHistoryByUser(
  userId: string,
  limit: number = 50
): Promise<ModerationHistoryWithDetails[]>

Parameters:

Parameter	Type	Required	Default	Description
userId	string	Yes	--	Client profile ID
limit	number	No	50	Maximum records to return

Returns: Array of moderation history entries with user and performer details

SQL Pattern:

SELECT moderation_history.*, client_profiles.id, name, email
FROM moderation_history
LEFT JOIN client_profiles ON moderation_history.user_id = client_profiles.id
WHERE moderation_history.user_id = ?
ORDER BY moderation_history.created_at DESC
LIMIT ?;

-- Per record: performer lookup
SELECT id, email FROM users WHERE id = ? LIMIT 1;

Note: Performer information is enriched per-record via Promise.all, resulting in N+1 queries. The performer is an admin user, while the target is a client_profile.

---

getModerationHistoryByReport

Gets all moderation history entries related to a specific report.

async function getModerationHistoryByReport(
  reportId: string
): Promise<ModerationHistoryWithDetails[]>

SQL Pattern: Same as getModerationHistoryByUser but filtered by report_id instead of user_id, with no limit applied.

---

User Status Management

incrementWarningCount

Atomically increments the warning count on a client profile.

async function incrementWarningCount(
  userId: string
): Promise<ClientProfile>

SQL Pattern:

UPDATE client_profiles
SET warning_count = COALESCE(warning_count, 0) + 1,
    updated_at = NOW()
WHERE id = ?
RETURNING *;

Note: Uses COALESCE for null-safe increment, handling cases where warningCount has never been set.

---

suspendUser

Suspends a user by setting their status to 'suspended' and recording the suspension timestamp.

async function suspendUser(userId: string): Promise<ClientProfile>

SQL Pattern:

UPDATE client_profiles
SET status = 'suspended', suspended_at = NOW(), updated_at = NOW()
WHERE id = ?
RETURNING *;

---

unsuspendUser

Restores a suspended user to active status.

async function unsuspendUser(userId: string): Promise<ClientProfile>

SQL Pattern:

UPDATE client_profiles
SET status = 'active', suspended_at = NULL, updated_at = NOW()
WHERE id = ?
RETURNING *;

---

banUser

Bans a user by setting their status to 'banned' and recording the ban timestamp.

async function banUser(userId: string): Promise<ClientProfile>

SQL Pattern:

UPDATE client_profiles
SET status = 'banned', banned_at = NOW(), updated_at = NOW()
WHERE id = ?
RETURNING *;

---

unbanUser

Restores a banned user to active status.

async function unbanUser(userId: string): Promise<ClientProfile>

SQL Pattern:

UPDATE client_profiles
SET status = 'active', banned_at = NULL, updated_at = NOW()
WHERE id = ?
RETURNING *;

---

Profile Lookups (Moderation Context)

getClientProfileById

Gets a client profile by ID. Used within the moderation flow to check current user status.

async function getClientProfileById(
  id: string
): Promise<ClientProfile | null>

---

getClientProfileByUserId

Gets a client profile by the authentication user ID.

async function getClientProfileByUserId(
  userId: string
): Promise<ClientProfile | null>

---

User Status Helpers

isUserBlocked

Synchronous helper that checks if a user status indicates the account is blocked.

function isUserBlocked(status: string | null): boolean
// Returns: status === 'suspended' || status === 'banned'

---

getBlockReasonMessage

Returns a user-facing message explaining why the account is restricted.

function getBlockReasonMessage(status: string | null): string

Returns:

• 'suspended' -- "Your account is currently suspended. You cannot perform this action."
• 'banned' -- "Your account has been banned. You cannot perform this action."
• Other -- "Your account is restricted. You cannot perform this action."

---

Enum Reference

Report Status

Value	Description
PENDING	Newly submitted, awaiting review
REVIEWED	Reviewed by admin, action pending
RESOLVED	Resolved (action taken)
DISMISSED	Dismissed (no action needed)

Report Content Type

Value	Description
ITEM	Report against an item
COMMENT	Report against a comment

Report Reason

Value	Description
SPAM	Spam content
HARASSMENT	Harassment
INAPPROPRIATE	Inappropriate content
OTHER	Other reason

---

Performance Notes

1. Search escaping -- getReports properly escapes SQL wildcards (%, _, \) in search terms before using them in ILIKE patterns.

2. Separate reviewer lookup -- getReportById performs a second query for reviewer info only when reviewedBy is present, avoiding unnecessary JOINs across two different user tables.

3. List optimization -- getReports omits reviewer data in list results (reviewer: null) to avoid N+1 queries when displaying report lists.

4. N+1 for performer details -- getModerationHistoryByUser and getModerationHistoryByReport enrich performer details per-record via Promise.all. For high-volume moderation logs, consider batching performer lookups.

5. Atomic increment -- incrementWarningCount uses COALESCE for null-safe SQL increment, ensuring correctness even for profiles that have never been warned.

6. Status symmetry -- Suspend/ban operations set both status and a corresponding timestamp. Unsuspend/unban restore status to 'active' and clear the timestamp to null.

Usage Examples

Submitting a content report

import { createReport, hasUserReportedContent } from '@/lib/db/queries';

const alreadyReported = await hasUserReportedContent(
  clientProfileId, 'comment', commentId
);

if (alreadyReported) {
  throw new Error('You have already reported this content');
}

await createReport({
  contentType: 'comment',
  contentId: commentId,
  reason: 'spam',
  details: 'This comment is promoting a scam website',
  reportedBy: clientProfileId,
});

Reviewing and resolving a report

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

// Mark as reviewed
await updateReport(reportId, {
  status: 'reviewed',
  reviewedBy: adminUserId,
  reviewNote: 'Confirmed spam content',
});

// Resolve with action
await updateReport(reportId, {
  status: 'resolved',
  resolution: 'content_removed',
});

Taking moderation action

import {
  createModerationHistory,
  incrementWarningCount,
  suspendUser,
} from '@/lib/db/queries';

// Issue a warning
await incrementWarningCount(clientProfileId);
await createModerationHistory({
  userId: clientProfileId,
  action: 'warning',
  reason: 'Posting spam content',
  reportId: reportId,
  performedBy: adminUserId,
});

// Suspend after repeated violations
await suspendUser(clientProfileId);
await createModerationHistory({
  userId: clientProfileId,
  action: 'suspend',
  reason: 'Multiple spam violations',
  performedBy: adminUserId,
});

Checking if a user can perform actions

import { getClientProfileById, isUserBlocked, getBlockReasonMessage } from '@/lib/db/queries';

const profile = await getClientProfileById(clientProfileId);

if (profile && isUserBlocked(profile.status)) {
  const message = getBlockReasonMessage(profile.status);
  throw new Error(message);
}

Viewing moderation dashboard stats

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

const stats = await getReportStats();

console.log(`Total reports: ${stats.total}`);
console.log(`Pending: ${stats.pendingCount}`);
console.log(`Resolved: ${stats.resolvedCount}`);
console.log(`Spam reports: ${stats.byReason.spam}`);