Follow/Unfollow Transactional Logic

Non-transactional follow mutations create race conditions (CWE-362) that permanently corrupt follower counts — a user can end up showing 5,000 followers in their count column while the actual follows row count is 4,200. Worse, without a unique constraint on (followerId, followedId), concurrent double-taps produce duplicate follow rows that inflate counts and break feed queries. Self-following bypasses are both a data integrity failure and an embarrassing product defect. These are not edge-case concerns: follow endpoints are hammered at scale, making TOCTOU collisions routine.

Add @@unique([followerId, followedId]) to your Prisma Follow model to enforce deduplication at the database level, then wrap every follow mutation in a db.$transaction that updates both the relationship and the denormalized counts atomically.

// app/api/users/[id]/follow/route.ts
export async function POST(req: Request, { params }: { params: { id: string } }) {
  const userId = getCurrentUserId()
  if (userId === params.id) {
    return Response.json({ error: 'Cannot follow yourself' }, { status: 400 })
  }

  await db.$transaction([
    db.follow.create({ data: { followerId: userId, followedId: params.id } }),
    db.user.update({ where: { id: userId }, data: { following_count: { increment: 1 } } }),
    db.user.update({ where: { id: params.id }, data: { followers_count: { increment: 1 } } })
  ])

  return Response.json({ success: true })
}

The unique constraint ensures a duplicate follow throws a Prisma P2002 error before the transaction commits, eliminating the race window entirely.

• ID: community-social-engagement.follow-graph.follow-mutation-logic

• Severity: critical

• What to look for: Enumerate all relevant files and Find the API endpoints or server actions for following/unfollowing users. Look for POST /api/users/[id]/follow, DELETE /api/users/[id]/follow, or server actions like followUser, unfollowUser. Verify that the operations are transactional: creating the follow relationship AND updating follower/following counts happen atomically. Check for guards against duplicate follows (unique constraint) and self-following.

• Pass criteria: No more than 0 violations are acceptable. Follow/unfollow endpoints exist. Database mutations use transactions or have a unique constraint preventing duplicate follows. Logic prevents users from following themselves.

• Fail criteria: No follow endpoints, or following is not transactional (counts can become out of sync), or duplicate follows are possible, or a user can follow themselves.

• Skip (N/A) when: The platform has no follow feature.

• Detail on fail: "Follow logic at POST /api/follow does not use transactions — follower count and follow relationship can desynchronize" or "Unique constraint missing on (followerId, followedId) — same user can follow another multiple times"

• Remediation: Implement follow logic with transactions and constraints:

  // Prisma schema
  model Follow {
    id String @id @default(cuid())
    followerId String
    followedId String
    createdAt DateTime @default(now())
  
    follower User @relation(name: "following", fields: [followerId], references: [id], onDelete: Cascade)
    followed User @relation(name: "followers", fields: [followedId], references: [id], onDelete: Cascade)
  
    @@unique([followerId, followedId]) // Prevent duplicates
    @@index([followedId]) // For efficient "followers" queries
  }
  
  // app/api/users/[id]/follow/route.ts
  export async function POST(req: Request, { params }) {
    const userId = getCurrentUserId()
    const targetId = params.id
  
    if (userId === targetId) {
      return Response.json({ error: 'Cannot follow yourself' }, { status: 400 })
    }
  
    const follow = await db.follow.create({
      data: { followerId: userId, followedId: targetId }
    })
  
    // Update counts (Prisma transactions)
    await db.$transaction([
      db.follow.create({ data: { followerId: userId, followedId: targetId } }),
      db.user.update({ where: { id: userId }, data: { following_count: { increment: 1 } } }),
      db.user.update({ where: { id: targetId }, data: { followers_count: { increment: 1 } } })
    ])
  
    return Response.json({ success: true })
  }
  
  export async function DELETE(req: Request, { params }) {
    const userId = getCurrentUserId()
    const targetId = params.id
  
    await db.$transaction([
      db.follow.delete({ where: { followerId_followedId: { followerId: userId, followedId: targetId } } }),
      db.user.update({ where: { id: userId }, data: { following_count: { decrement: 1 } } }),
      db.user.update({ where: { id: targetId }, data: { followers_count: { decrement: 1 } } })
    ])
  
    return Response.json({ success: true })
  }