bug: `uniqueViewers` count in `/api/analytics/overview` is inflated — `groupBy(['viewerId', 'viewerIp'])` produces a Cartesian product, not a true distinct-viewer count

[Srejoye]

Description:

The /api/analytics/overview endpoint computes unique viewer count via:

const uniqueViewersQuery = await app.prisma.cardView.groupBy({
  by: ['viewerId', 'viewerIp'],
  where: { ownerId: userId },
});
const uniqueViewers = uniqueViewersQuery.length;

Grouping by the composite key (viewerId, viewerIp) means a single authenticated viewer who visits from two different IP addresses generates two group rows, and an anonymous viewer (viewerId = null) with three different IPs generates three rows — all counted as separate unique viewers. The result can massively overcount uniqueness, especially for anonymous visitors where viewerId is null and viewerIp varies (mobile networks, VPNs, proxies).

The comment in the code even acknowledges that the correct query is COUNT(DISTINCT viewer_id), but the implemented workaround does not actually approximate that.

Affected file: apps/backend/src/routes/analytics.ts (lines 72–78)

Steps to reproduce:

1. A logged-in viewer visits a profile from two different network connections (two IPs).
2. uniqueViewers returns 2 for that single viewer.
3. Ten anonymous web scrapers visiting from different IPs each increment the counter by 1, regardless of whether they are the same agent.

[Srejoye]

Hi, requesting assignment under GSSoC'26. Technical breakdown:

Root cause:
apps/backend/src/routes/analytics.ts L73–74 uses groupBy(['viewerId', 'viewerIp']). Because both columns participate in the grouping key, uniqueness is evaluated per (viewerId, viewerIp) pair, not per viewer. The SQL emitted is effectively GROUP BY viewer_id, viewer_ip, not COUNT(DISTINCT viewer_id).

Affected modules/files:

• apps/backend/src/routes/analytics.ts — query logic
• apps/backend/src/__tests__/analytics.test.ts — new test cases needed

Proposed fix approach (choose one):

• Option A (raw query): Replace groupBy with app.prisma.$queryRaw executing SELECT COUNT(DISTINCT viewer_id) FROM card_views WHERE owner_id = $1. Handle null viewer_id exclusion or inclusion intentionally.
• Option B (Prisma groupBy on single key): groupBy({ by: ['viewerId'], where: { ownerId: userId, viewerId: { not: null } } }) to count only authenticated unique viewers, and document that anonymous visits are excluded.
• Decide whether anonymous viewers (null viewerId) should be counted as one bucket or zero, and make that explicit in the response schema.

Regression/unit test considerations:

• Seed one authenticated viewer with 5 views from 3 distinct IPs → uniqueViewers must equal 1.
• Seed 3 anonymous views from 3 distinct IPs → documented exclusion or explicit anonymous count.
• Existing tests should not assert on the inflated value.

Requesting assignment. Thank you!

[Harxhit]

@Srejoye I have assigned this issue to you.