# Connection Counts Denormalization/Caching

- **Pattern:** `ab-000763` (`community-social-engagement.follow-graph.connection-counts-integrity`)
- **Severity:** medium
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000763
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000763)

## Why it matters

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.

## Severity rationale

Medium because live count aggregation degrades read performance predictably as the follow graph grows, but does not cause data loss or security exposure.

## Remediation

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.

```ts
// 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.

## Detection

- **ID:** `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:

  ```ts
  // 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
  }
  ```

## External references

- iso-25010 performance-efficiency

Taxons: performance, cost-efficiency

HTML version: https://auditbuffet.com/patterns/ab-000763