Skip to content

Account Dedup + Merge — Research

Problem (observed 2026-06-08)

Duplicate user accounts. A person ends up with 2+ users rows because Supabase forks a new auth user whenever a sign-in identity's verified email doesn't match an existing user.

Scope, by phone-number clustering on prod (689 non-deleted users):

  • 26 phone numbers have 2+ accounts.
  • 9 are "Apple + another provider" — the category Juan flagged.
  • The other ~17 are Google+email / double-email / double-Google — same root cause, different providers.
  • A few are noise (internal test accounts).

Confirmed examples:

Person Studio Account holding the studio (Stripe sub) Empty duplicates
Mark Sammons MG6-SMO216 🍎 Apple (@privaterelay, only one logged into) 2 Google, 0 reservations
Deven Fornof MG9-SMO43 🍎 Apple (@privaterelay) 2 Google, 0 reservations

Note: which account holds the live subscription varies across the 9 (sometimes Apple, sometimes the email/Google account). Merge direction is per-case, not fixed.

Root cause

  • Supabase auto-links a new identity to an existing user only when the email matches and is verified ("it would be an insecure practice to automatically link an identity to a user with an unverified email address").
  • Apple "Hide My Email" issues an @privaterelay.appleid.com address that can never match the user's real email → auto-link never fires → a second account is always created.
  • Google-with-email-A then email/password-with-email-B (different addresses) forks too.
  • Nothing ties a person's accounts together by a stable key (e.g. phone). complete-profile collects phone but only validates format, never ownership, and never dedups on it.

Supabase capabilities (from docs, June 2026)

  • No native "merge two users." Confirmed across identity-linking and admin (deleteUser/updateUserById/listUsers) docs. No admin method reassigns an identity between users.
  • linkIdentity() adds a provider to the currently logged-in user only. It cannot fuse two pre-existing accounts, and it requires enable_manual_linking = true (currently false in our config.toml).
  • unlinkIdentity() needs the user logged in with ≥2 identities.
  • Phone auth (signInWithOtp({phone}) + verifyOtp({type:'sms'})) makes phone a login method. Providers: Twilio, Twilio Verify, MessageBird, Vonage. Our config.toml: [auth.sms] enable_signup=false, phone confirmations off — phone is not a credential today, and we don't want it to become one.
  • Admin delete: auth.admin.deleteUser(id, shouldSoftDelete) — hard by default; soft keeps a hashed id, irreversible. (Our app's softDeleteById is actually a hard Prisma delete with cascade — see users.md gotcha.)

Our current state (relevant code)

  • users.id = auth.users.id (unified, per permission-redesign). A merge must re-parent the loser's FKs (reservations.user_id, credits, access codes, consent, roles, stripe_customer_id, etc.) onto the survivor, then delete the loser.
  • Twilio already wired: apps/api/src/services/sms/SmsService.ts (send-only via TWILIO_MESSAGING_SERVICE_SID), inbound webhook at webhooks/twilio/incoming/. Env: TWILIO_ACCOUNT_SID, TWILIO_AUTH_TOKEN, TWILIO_MESSAGING_SERVICE_SID, TWILIO_WEBHOOK_URL. No OTP/Verify yet.
  • check-account-state (/api/auth/check-account-state, returns none|stub|claimed, intentionally enumeration-leaking) is the precedent for a public identity-existence check the phone-dedup gate extends.
  • Auth-sync webhook (auth.users → public.users) and handle_new_user trigger govern user lifecycle; a merge interacts with both (deleting the loser fires the delete path).
  • complete-profile (CompleteProfileContent.tsx) is where OAuth users supply name+phone post-signup — the natural verification gate.

Stripe constraint

A Stripe subscription cannot be moved between customers. Merge options: - Re-point the surviving MG user at the Stripe customer that already holds the active subscription (move stripe_customer_id to the survivor; users.stripe_customer_id is @unique, so the loser must be vacated first). Subscription + history stay intact. Preferred. - Cancel + recreate on the survivor's customer → billing disruption, proration mess. Avoid. - Both accounts have active subs/customers → no safe automation; flag for manual Stripe reconciliation.

Twilio Verify vs rolling our own OTP

Twilio Verify is a managed OTP product: generates/expires codes, rate-limits, fraud signals, multi-channel (SMS/voice/WhatsApp), handles the sender (toll-free / short code). Adds one env var (TWILIO_VERIFY_SERVICE_SID) and two API calls (verifications.create, verificationChecks.create). Rolling our own on SmsService means storing/expiring codes, rate-limiting, and fraud handling ourselves — more code, weaker. → Use Twilio Verify.