Skip to main content

Engagement Queries Deep Dive

Comprehensive reference for all engagement-related database query functions, including votes, comments, favorites, views, ratings, and aggregated popularity metrics.

Overview​

The engagement query layer is organized into three specialized modules:

  • engagement.queries.ts -- Bulk engagement metric aggregation for popularity scoring (views, votes, favorites, comments, ratings)
  • vote.queries.ts -- Vote CRUD operations, net score calculations, and vote-based item sorting
  • comment.queries.ts -- Comment CRUD operations with user details, soft deletion, and rating management

Source Files​

lib/db/queries/engagement.queries.ts
lib/db/queries/vote.queries.ts
lib/db/queries/comment.queries.ts

Function Reference: engagement.queries.ts​

ItemEngagementMetrics (Interface)​

interface ItemEngagementMetrics {
  views: number;
  votes: number;       // Net votes (upvotes - downvotes)
  favorites: number;
  comments: number;
  avgRating: number;   // Average rating from comments (0-5)
}

getEngagementMetricsPerItem​

Gets all engagement metrics for multiple items in a single query batch. Optimized for bulk operations like sorting all items by popularity.

async function getEngagementMetricsPerItem(
  itemSlugs: string[]
): Promise<Map<string, ItemEngagementMetrics>>

Parameters:

ParameterTypeRequiredDescription
itemSlugsstring[]YesArray of item slugs

Returns: Promise<Map<string, ItemEngagementMetrics>> -- Map of item slug to full engagement metrics

SQL Pattern: Runs four queries in parallel using Promise.all:

  1. Views per item:

    SELECT item_id, count(*) FROM item_views
    WHERE item_id IN (...) GROUP BY item_id;

  2. Net votes per item (upvotes - downvotes):

    SELECT item_id,
      SUM(CASE WHEN vote_type = 'upvote' THEN 1
               WHEN vote_type = 'downvote' THEN -1
               ELSE 0 END) as net_score
    FROM votes WHERE item_id IN (...) GROUP BY item_id;

  3. Favorites per item:

    SELECT item_slug, count(*) FROM favorites
    WHERE item_slug IN (...) GROUP BY item_slug;

  4. Comments count and average rating:

    SELECT item_id, count(*), COALESCE(AVG(rating), 0) as avg_rating
    FROM comments
    WHERE item_id IN (...) AND deleted_at IS NULL
    GROUP BY item_id;

Performance Notes:

  • All four queries run concurrently via Promise.all
  • Empty array guard avoids unnecessary database calls
  • Results merged in-memory into a single Map
  • Items with no engagement data receive default zeros

getFavoritesPerItem​

Gets favorites count per item.

async function getFavoritesPerItem(
  itemSlugs: string[]
): Promise<Map<string, number>>

Parameters:

ParameterTypeRequiredDescription
itemSlugsstring[]YesArray of item slugs

Returns: Promise<Map<string, number>> -- Map of item slug to favorites count

Note: Queries the favorites table using itemSlug (not itemId), reflecting the schema's naming convention for this table.

getCommentsPerItem​

Gets comments count and average rating per item.

async function getCommentsPerItem(
  itemSlugs: string[]
): Promise<Map<string, { count: number; avgRating: number }>>

Returns: Promise<Map<string, { count: number; avgRating: number }>> -- Map of item slug to comment count and average rating

SQL Pattern:

SELECT item_id, count(*), COALESCE(AVG(rating), 0) as avg_rating
FROM comments
WHERE item_id IN (...) AND deleted_at IS NULL
GROUP BY item_id;

Note: Excludes soft-deleted comments (deleted_at IS NULL).

Function Reference: vote.queries.ts​

createVote​

Creates a new vote. Normalizes the itemId via getItemIdFromSlug before insertion.

async function createVote(vote: InsertVote)

Parameters:

ParameterTypeRequiredDescription
voteInsertVoteYesVote data with item slug

Returns: The created vote record (via RETURNING)

SQL Pattern:

INSERT INTO votes (user_id, item_id, vote_type, ...)
VALUES (?, ?, ?, ...) RETURNING *;

getVoteByUserIdAndItemId​

Gets a user's vote on a specific item.

async function getVoteByUserIdAndItemId(
  userId: string,
  itemSlug: string
)

Parameters:

ParameterTypeRequiredDescription
userIdstringYesUser ID
itemSlugstringYesItem slug

Returns: Vote array (empty if not found, single element if found)

SQL Pattern:

SELECT * FROM votes
WHERE user_id = ? AND item_id = ?
LIMIT 1;

deleteVote​

Permanently deletes a vote by ID.

async function deleteVote(voteId: string)

SQL Pattern:

DELETE FROM votes WHERE id = ?;

getItemsSortedByVotes​

Gets items sorted by total vote count with pagination.

async function getItemsSortedByVotes(
  limit: number = 10,
  offset: number = 0
)

Parameters:

ParameterTypeRequiredDefaultDescription
limitnumberNo10Results per page
offsetnumberNo0Pagination offset

Returns: Array of { itemId: string, voteCount: number } sorted by vote count descending

SQL Pattern:

SELECT item_id, count(id) as vote_count
FROM votes
GROUP BY item_id
ORDER BY vote_count DESC
LIMIT ? OFFSET ?;

getVoteCountForItem​

Gets the net vote score for a single item (upvotes minus downvotes).

async function getVoteCountForItem(itemSlug: string): Promise<number>

Returns: Net vote score (positive = more upvotes, negative = more downvotes, 0 = equal or no votes)

SQL Pattern:

SELECT SUM(CASE
  WHEN vote_type = 'upvote' THEN 1
  WHEN vote_type = 'downvote' THEN -1
  ELSE 0
END) as net_score
FROM votes WHERE item_id = ?;

getVotesPerItem​

Gets net vote scores for multiple items.

async function getVotesPerItem(
  itemSlugs: string[]
): Promise<Map<string, number>>

Returns: Promise<Map<string, number>> -- Map of item slug to net vote score

Function Reference: comment.queries.ts​

createComment​

Creates a new comment. Normalizes the itemId via getItemIdFromSlug.

async function createComment(data: NewComment)

Returns: The created comment record

getCommentsByItemId​

Gets all non-deleted comments for an item with user information, ordered by most recent first.

async function getCommentsByItemId(
  itemSlug: string
): Promise<CommentWithUser[]>

Returns: Array of CommentWithUser including:

  • Comment fields: id, content, rating, createdAt, updatedAt, editedAt, deletedAt
  • User fields: user.id, user.name, user.email, user.image

SQL Pattern:

SELECT comments.*, client_profiles.id, name, email, avatar
FROM comments
INNER JOIN client_profiles ON comments.user_id = client_profiles.id
WHERE comments.item_id = ? AND comments.deleted_at IS NULL
ORDER BY comments.created_at DESC;

getCommentById​

Gets a comment by ID (without user details).

async function getCommentById(id: string)

getCommentWithUserById​

Gets a comment by ID with user information.

async function getCommentWithUserById(
  id: string
): Promise<CommentWithUser | undefined>

updateComment​

Updates comment content and/or rating. Sets both updatedAt and editedAt to track edit history.

async function updateComment(
  id: string,
  data: { content?: string; rating?: number }
)

SQL Pattern:

UPDATE comments
SET content = ?, rating = ?, updated_at = NOW(), edited_at = NOW()
WHERE id = ?
RETURNING *;

Design Note: editedAt is separate from updatedAt to distinguish user edits from system updates. The UI can display "edited" indicators based on editedAt.

updateCommentRating​

Updates only the rating on a comment.

async function updateCommentRating(id: string, rating: number)

deleteComment​

Soft deletes a comment by setting deletedAt.

async function deleteComment(id: string)

SQL Pattern:

UPDATE comments SET deleted_at = NOW() WHERE id = ? RETURNING *;

Shared Types​

CommentWithUser​

type CommentWithUser = {
  id: string;
  content: string;
  rating: number | null;
  userId: string;
  itemId: string;
  createdAt: Date;
  updatedAt: Date;
  editedAt: Date | null;
  deletedAt: Date | null;
  user: {
    id: string;
    name: string | null;
    email: string | null;
    image: string | null;
  };
};

Performance Notes​

  1. Parallel query execution -- getEngagementMetricsPerItem runs all four metric queries concurrently via Promise.all, reducing total latency to the slowest single query.

  2. Net vote scoring -- Uses CASE WHEN expressions in SQL for upvote/downvote calculation, avoiding separate queries for each vote type.

  3. Soft delete filtering -- All comment queries consistently filter deleted_at IS NULL to exclude soft-deleted comments.

  4. Slug normalization -- Both vote.queries.ts and comment.queries.ts normalize item slugs via getItemIdFromSlug before database operations, ensuring consistent key matching.

  5. Empty array guards -- All bulk query functions return immediately with empty Maps when passed empty arrays.

Usage Examples​

Sorting items by popularity​

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

const metrics = await getEngagementMetricsPerItem(allItemSlugs);

const sorted = allItemSlugs.sort((a, b) => {
  const ma = metrics.get(a) ?? { votes: 0, views: 0, favorites: 0, comments: 0 };
  const mb = metrics.get(b) ?? { votes: 0, views: 0, favorites: 0, comments: 0 };
  const scoreA = ma.votes * 3 + ma.favorites * 2 + ma.comments + ma.views * 0.1;
  const scoreB = mb.votes * 3 + mb.favorites * 2 + mb.comments + mb.views * 0.1;
  return scoreB - scoreA;
});

Toggle vote on an item​

import { getVoteByUserIdAndItemId, createVote, deleteVote } from '@/lib/db/queries';

const existingVotes = await getVoteByUserIdAndItemId(userId, 'clockify');

if (existingVotes.length > 0) {
  await deleteVote(existingVotes[0].id);
} else {
  await createVote({ userId, itemId: 'clockify', voteType: 'upvote' });
}

Fetching comments for an item page​

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

const comments = await getCommentsByItemId('toggl');
// Each comment includes user.name and user.image for display