Identity Fragmentation

Overview

"Identity fragmentation" is when the same real-world person ends up with more than one user_id in the system, so their data (merch orders, sessions, roles) is split across ids instead of living under one. This page documents the two ways it happened historically (ZLY-1497), the code fixes, the prevention mechanism, and the runbook for cleaning up existing duplicates.

Symptom

A user reports that a purchase/order "disappeared" from their history, or that a role (e.g. supplier) they were granted doesn't seem to apply. In both cases, the underlying cause is the same shape: the app queried data using the id from the current login, but the row in question is stamped with a different id belonging to the same person.

Root cause 1 — duplicate Cognito accounts (native + Google)

The Cognito user pool is configured with UsernameAttributes: ["email"], so email uniqueness is enforced only for native (email/password) users. A federated sign-in (e.g. "Continue with Google") creates a separate Cognito user of type EXTERNAL_PROVIDER with username google_<googleUserId> — its email attribute is just a copy from the Google profile and does not participate in that uniqueness check.

AWS does not auto-link a federated user and a native user that share an email; linking is opt-in via AdminLinkProviderForUser, normally wired through a Pre-Sign-Up Lambda trigger. Without that trigger, whichever method a person uses second creates a second, fully independent Cognito user — with its own sub — and every row your app stamps with user.id (merch orders, sessions, carts, etc.) fragments across the two subs depending on which login method was used that day.

Root cause 2 — role-stub DynamoDB records

updateRoles(userId, roles) in @zooly/auth-db used a DynamoDB UpdateItem call keyed by user_id. UpdateItem is an upsert by default — if no item exists for that key, DynamoDB silently creates one.

The admin "manage users" page lists users by Cognito sub and called updateRoles(sub, roles) directly. For a guest-origin user — someone who checked out as a guest before signing up, whose real identity record is keyed guest-<timestamp>-<random> (see Guest Users) — the sub-keyed record doesn't exist yet. The blind update created a stub: a bare { user_id: <sub>, roles: [...], updated_at } record with no cognito_sub, no guest_email, no display_name.

That stub then hijacked identity resolution. /api/me (apps/zooly-auth/app/api/me/route.ts) resolves an identity by trying the sub first, then falling back to an email lookup:

// Get identity from DynamoDB — try by user_id (PK) first, then by email
// (linked guest users have user_id != cognito_sub, so PK lookup fails)
let identity = await getIdentity(userInfo.sub).catch(() => null);
if (!identity && userInfo.email) {
  identity = await findIdentityByEmail(userInfo.email).catch(() => null);
}

Once the stub existed, the sub lookup succeeded (on the stub), so the email fallback never ran — identityId resolved to the sub instead of the guest-* id, and every guest-era order (stored under guest-*) disappeared from that user's history. New roles granted this way also silently overwrote nothing on the real record, so admin role changes looked like they "didn't stick."

Code fixes (prevention)

1. updateRoles can no longer create records

// apps/zooly-auth/src/db/identities.ts
new UpdateCommand({
  TableName: tableName,
  Key: { user_id: userId },
  ConditionExpression: "attribute_exists(user_id)", // <-- guard
  UpdateExpression: "SET #roles = :roles, updated_at = :updated_at",
  // ...
})

If no identity exists for userId, the call now throws ConditionalCheckFailedException instead of silently fabricating a stub.

2. The roles route resolves the real identity first

PATCH /api/admin/users/:id/roles now mirrors /api/me's resolution order — sub lookup, then Cognito-email → findIdentityByEmail fallback — and updates whichever record it finds. Only if truly no identity exists does it create one, and it creates a complete record (with cognito_sub set), never a roles-only stub.

3. The admin users list surfaces real roles for guest-origin users

The list endpoint batch-fetches identities by sub, which still misses guest-origin users. It now falls back to an email lookup for any sub not found in the batch result, so an admin sees the user's actual roles instead of an empty array (which was the UI signal that nudged admins into re-granting a role and recreating the stub).

Code fix — Cognito auto-link Lambda

To stop new native/Google duplicates from forming, a Lambda (apps/zooly-auth/lambda/cognito-autolink/index.mjs) is wired to two Cognito triggers on the user pool:

• PreSignUp_ExternalProvider — fires when a Google sign-in is about to create a new user. If a native user with the same email already exists, the Lambda links the Google identity into it via AdminLinkProviderForUser instead of letting a second user get created.
• PostConfirmation_ConfirmSignUp — fires after a native signup is confirmed. If a standalone Google-only user with the same email already exists (the "Google first, native later" ordering), the Lambda deletes it and links its Google identity into the just-created native user.

Both handlers never block sign-in on a linking failure — errors are caught and logged, and the event is always returned so the user can still authenticate. Any residual duplicate is caught by the cleanup script below on its next run.

Deployed via apps/zooly-auth/scripts/deploy-cognito-autolink.sh (idempotent — creates/updates the IAM role, Lambda, invoke permission, and pool trigger config). The script rebuilds the update-user-pool request from a fresh describe-user-pool call so it never clobbers unrelated pool settings (password policy, MFA, etc.) — update-user-pool resets any field you don't explicitly pass.

Immediate mitigation — verified-email fallback in history queries

Fixing the root causes stops new fragmentation, but it doesn't retroactively fix already-fragmented data. As a resilient, immediate mitigation that works even before a cleanup runs, the merch history endpoint (apps/zooly-app/app/api/merch/history/route.ts) looks orders/sessions/experiences up by three dimensions instead of one:

1. user.id (the current login's Cognito sub)
2. user.identityId (the DynamoDB-resolved identity, when different)
3. The Cognito-verified email — getMerchOrdersByEmail, getMerchSessionsByDelayedEmail, getMerchExperiencesByEmail in packages/db

Order confirmations are sent to merch_order.email, so any order placed with a verified email is guaranteed to surface for that email's owner regardless of which fragmented id it's stamped with. This is a general pattern worth reusing: when a lookup key can fragment, add a secondary lookup on a value that's stable across the fragmentation (here, the verified email).

Because these lookups filter on lower(email), expression indexes back them (merch_order_email_lower_idx, merch_session_delayed_email_lower_idx, merch_experience_email_lower_idx — see packages/db/drizzle/0167_merch_email_lookup_indexes.sql) so they don't sequential-scan the (large) sessions/orders tables.

Cleanup — uniting existing duplicates

apps/zooly-auth/scripts/unite-duplicate-identities.ts is the one-time (and re-runnable) cleanup for duplicates that already exist. It:

1. Scans Cognito for emails that own both a native user and a google_* federated user. Canonical = the native user. Deletes the Google user and calls AdminLinkProviderForUser so future Google sign-ins resolve to the native account.
2. Scans DynamoDB for multiple identity records sharing an "effective sub" (cognito_sub ?? user_id) — this catches both the merged Cognito duplicates and the bare role-stubs (which have no cognito_sub, so they're matched to their guest-* counterpart via user_id). Merges roles (union) and backfills display_name / avatar_url / guest_email onto the canonical record, then deletes the retired one.
3. Emits a {retiredUserId: canonicalUserId} map to a generated file (packages/db/src/data-migrations/migrations/0014-user-id-remap.generated.ts) consumed by a Postgres data migration (0014-unite-duplicate-user-ids.ts), which remaps user_id (and cart owner_user_id) across every merch table so historical rows move onto the canonical id too — not just newly-created ones. Future runs that find new duplicates should use the next free migration id (check packages/db/src/data-migrations/registry.ts).

Running it

# 1. Preview — always run this first and review the output.
set -a && source .env.auth && set +a && AWS_PROFILE=zooly \
  npx tsx apps/zooly-auth/scripts/unite-duplicate-identities.ts --dry-run

# 2. Apply for real (mutates production Cognito + DynamoDB).
set -a && source .env.auth && set +a && AWS_PROFILE=zooly \
  npx tsx apps/zooly-auth/scripts/unite-duplicate-identities.ts

The Postgres migration is idempotent (WHERE user_id = <retired> matches nothing once applied) and handles the one tricky edge case: merch_cart has a partial unique index allowing at most one active cart per owner, so if both the retired and canonical id happen to own an active cart, the migration keeps the most-recently-touched one and abandons the other before remapping.

Verifying identity health

Quick DynamoDB scan to check for stub records (no cognito_sub, keyed by what looks like a Cognito sub rather than guest-*):

aws dynamodb scan --profile zooly --region us-east-1 \
  --table-name zooly-auth-identities \
  --filter-expression "attribute_not_exists(cognito_sub) and not contains(user_id, :g)" \
  --expression-attribute-values '{":g":{"S":"guest-"}}'

Quick check for a specific email having more than one Cognito user:

aws cognito-idp list-users --profile zooly --region us-east-1 \
  --user-pool-id <pool-id> --filter 'email = "someone@example.com"'

If either turns up results, re-run the dry-run of unite-duplicate-identities.ts — it's safe to run repeatedly and only reports/acts on genuine duplicates.