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.