Three compounding data integrity issues: device fingerprinting, funnel grouping, and profile event attribution

[techxpert99]

Three compounding data integrity issues: device fingerprinting, funnel grouping, and profile event attribution

These three bugs are independent in cause but compound in effect — a user who sees a wrong funnel count, clicks "View Users" to investigate, and then opens a profile will encounter wrong numbers, the wrong people, and the wrong events at each step.

---

Bug 1 — Device/session ID is computed server-side from IP + UA, breaking on NAT and server-side senders

OpenPanel computes device_id entirely server-side:

device_id = sha256(user_agent + ":" + ip + ":" + origin + ":" + salt)
session_id = sha256("sess:v1:" + projectId + deviceId + 30min_bucket)

No client-generated token (cookie, localStorage) is involved. This breaks in two common real-world scenarios:

NAT / campus networks — many users share one public IP. Students on a university WiFi, users on a corporate network, or mobile users on a carrier NAT all get the same device_id if they share a browser version. Their events, sessions, and profiles contaminate each other.

Server-side event senders — any backend worker sending events (post-purchase hooks, lifecycle events, etc.) runs from a fixed server IP. All events sent from that server in a 30-minute window get the same device_id and session_id regardless of how many distinct end-users they represent.

The __deviceId override is insufficient

properties.__deviceId can override the IP+UA hash per-event, which does isolate device_id per user. However, when it is used, the server sets session_id = '' for all overridden events:

// apps/api/src/utils/ids.ts
if (overrideDeviceId) {
  return { deviceId: overrideDeviceId, sessionId: '' };
}

All server-side events from all users collapse into the same empty-string session. Session-level analytics (session counts, session-scoped funnels, retention) remains broken for any server-side sender.

Proposed fix

Support a __sessionId property override alongside __deviceId:

if (overrideDeviceId) {
  const sessionId = payload.properties?.__sessionId ?? '';
  return { deviceId: overrideDeviceId, sessionId };
}

Longer term, the web SDK should generate and persist a client-side UUID (localStorage) and use that as device_id rather than relying on IP+UA hashing, which is the standard approach taken by Mixpanel, Amplitude, and PostHog.

---

Bug 2 — getFunnelProfiles ignores funnelGroup, always groups the CTE by session_id

File: packages/trpc/src/routers/chart.ts

When a funnel is configured with funnelGroup: 'profile_id', the completion count (getFunnel) and the "View Users" list (getFunnelProfiles) use different internal grouping:

Procedure	CTE grouping	Result
getFunnel (count)	profile_id ✓	Cross-session windowing
getFunnelProfiles (user list)	session_id ✗	Within-session windowing only

buildFunnelCte defaults to session_id when group is omitted. getFunnel passes it correctly; getFunnelProfiles does not:

// getFunnelProfiles — missing `group`
const funnelCte = funnelService.buildFunnelCte({
  projectId,
  startDate,
  endDate,
  eventSeries: eventSeries as IChartEvent[],
  funnelWindowMilliseconds,
  timezone,
  additionalSelects: breakdownSelects,
  additionalGroupBy: breakdownGroupBy,
  // group is NOT passed — silently defaults to session_id
});

In production: a 3-step funnel with funnelGroup: profile_id showed Completed: 2, but "View Users" returned 4 profiles with zero overlap with the 2 correct profiles.

Fix

const funnelCte = funnelService.buildFunnelCte({
  ...
  group, // ← add this
});

---

Bug 3 — Profile events page shows events belonging to other users

File: packages/db/src/services/event.service.ts

The profile events query expands by device_id, not just profile_id:

sb.where.deviceId = `(
  device_id IN (
    SELECT device_id FROM ${TABLE_NAMES.events}
    WHERE project_id = ? AND device_id != '' AND profile_id = ?
    GROUP BY device_id
  )
  OR profile_id = ?
)`;

The intent is legitimate identity stitching — linking pre-login anonymous events to the identified profile. But when device_id is shared across users (Bug 1), this query returns every event ever sent from that device, regardless of which profile_id those events carry. A profile page ends up showing events belonging to completely unrelated users.

Short-term fix (caller side)

Server-side senders should pass __deviceId: userId per event so each user gets a distinct device_id.

Long-term fix (OpenPanel core)

Restrict the device_id expansion to truly anonymous events only, preventing identified events from other profiles leaking through:

WHERE (
  device_id IN (
    SELECT device_id FROM events
    WHERE project_id = ? AND device_id != '' AND profile_id = ?
    GROUP BY device_id
  )
  AND profile_id = ''   -- only pull anonymous events, not other users' identified events
  OR profile_id = ?
)

This preserves the stitching use-case while closing the cross-user leak.

---

Environment

• Commit: b467a6ce (March 23 2026)
• Affected files:
  • apps/api/src/utils/ids.ts
  • packages/trpc/src/routers/chart.ts — getFunnelProfiles
  • packages/db/src/services/event.service.ts — profile events query
• Trigger conditions:
  • Bug 1: any deployment with server-side event senders or users behind NAT
  • Bug 2: any funnel with funnelGroup: 'profile_id'
  • Bug 3: any profile that has received events from a shared device_id