Connection Counts Denormalization/Caching

Running COUNT(*) on the follows table on every profile page load is a full aggregate scan whose cost grows with your total follow graph size. At 1 million follow relationships, each profile render triggers a scan of millions of rows just to display two integers. This is a denormalization failure under ISO 25010:2011 performance-efficiency: counts that change infrequently (relative to page load frequency) should never be computed live. Stale or incorrect counts are a secondary concern — ghost followers or inflated numbers damage trust in your platform's data integrity.

Add followers_count and following_count integer columns to your User model with @default(0), then update them inside every follow/unfollow transaction so they never diverge from the actual relationship count.

// Prisma migration
// model User {
//   followers_count Int @default(0)
//   following_count Int @default(0)
// }

// lib/follow.ts — transactional update
export async function followUser(followerId: string, followedId: string) {
  await db.$transaction([
    db.follow.create({ data: { followerId, followedId } }),
    db.user.update({ where: { id: followerId }, data: { following_count: { increment: 1 } } }),
    db.user.update({ where: { id: followedId }, data: { followers_count: { increment: 1 } } })
  ])
}

Profile queries then read from the denormalized column instead of aggregating, making follower count fetches O(1) regardless of graph size.

• ID: community-social-engagement.follow-graph.connection-counts-integrity

• Severity: medium

• What to look for: Enumerate all relevant files and Check the User schema for denormalized count fields like followers_count, following_count. Check whether these are updated alongside follow/unfollow mutations. Alternatively, look for cached count values (Redis keys like user:{id}:followers:count). Verify that simple COUNT(*) queries are not being executed on every page load.

• Pass criteria: No more than 0 violations are acceptable. Denormalized counts exist on the User model and are updated transactionally with follow/unfollow. OR cached counts are maintained with invalidation logic. Counts are fetched from the denormalized field or cache, not from COUNT(*).

• Fail criteria: Profile pages execute COUNT(*) on follow relationships to display counts, or counts are missing and computed on-the-fly.

• Skip (N/A) when: Follower/following counts are not displayed.

• Detail on fail: "User profile queries Count(*) on the follows table every request — slow with thousands of followers" or "No followers_count field in User schema; counts are computed dynamically"

• Remediation: Add denormalized count fields and update them transactionally:

  // Prisma schema
  model User {
    id String @id
    name String
    followers_count Int @default(0)
    following_count Int @default(0)
    // ...
  }
  
  // Update transactionally on follow
  await db.$transaction([
    db.follow.create({ data: { followerId, followedId } }),
    db.user.update({ where: { id: followerId }, data: { following_count: { increment: 1 } } }),
    db.user.update({ where: { id: followedId }, data: { followers_count: { increment: 1 } } })
  ])
  
  // Use cached counts in UI
  export async function getProfile(userId: string) {
    const user = await db.user.findUnique({
      where: { id: userId },
      select: { id: true, name: true, followers_count: true, following_count: true }
    })
    return user
  }