Account Dedup + Merge

Architecture: ADR-022 (Accepted 2026-06-19, amended 2026-06-20). The verification layer is Supabase-native phone auth — phone is a verified login + recovery factor, dedup is enforced by GoTrue's native phone uniqueness (with a thin partial unique index as a DB backstop), and OAuth providers consolidate via linkIdentity(). Part A, Constraints, and Decisions 1 & 5 below now reflect this. Part B (manual remediation) is unchanged. Number recycling is out of scope.

Make verified phone number the canonical person-key so a person resolves to one account, and clean up the duplicate accounts that already exist. Two coupled workstreams: (A) prevention — phone-verified dedup at signup (the build); (B) remediation — resolving the existing duplicate clusters case-by-case, manually, with Aaron (deferred; no automated tool). They share one idea: a person is identified by a verified phone, not by whichever email an OAuth provider happened to hand us.

Why

Supabase forks a new auth user whenever a sign-in identity's verified email doesn't match an existing user. Apple "Hide My Email" guarantees this (the relay address never matches); Google-vs-email with different addresses does too. Result on prod: 26 phone numbers with 2+ accounts, 9 of them Apple+other, with studios and live Stripe subscriptions fragmented across a person's accounts. Members log into the "wrong" account and can't see their studio (Juan's report). There is no native Supabase merge and no dedup key today, so this grows with every Apple signup. See research.md.

A second driver: per-account offers gate on userId (PromoRedemption is @@unique([userId, offerId]); Offer.maxRedemptionsPerUser caps per account), and an account costs only an email, so one person can mint several accounts and claim a free-credit or comp offer once per account. Strict-unique verified phone closes that cheap version. The residual (distinct real people, e.g. four bandmates with four phones, each redeeming) is not solved here and stays a soft policy matter; org-scoped eligibility would be the lever, but org membership cannot be forced today.

Constraints

Phone is a Supabase-native login + recovery factor and the dedup key (ADR-022). We enable Supabase phone auth with Twilio as the SMS provider; phone is verified through the native flows (updateUser({phone}) → verifyOtp({type:'phone_change'})), not a self-managed Twilio Verify attribute. We keep [auth.sms] enable_signup = false — phone is a login/recovery method for existing accounts, not a signup method (signup stays email/OAuth).
GoTrue enforces phone uniqueness — one auth.users row per phone. updateUser({phone}) rejects a number already on another account (AuthApiError: ...already been registered) before an SMS is sent; that rejection is our dedup signal. A thin partial unique index on public.users(phone) WHERE phone_verified_at IS NOT NULL AND deleted_at IS NULL is a DB-level backstop, not the primary mechanism (ADR-022 amendment).
No native Supabase user-merge. linkIdentity() only adds a provider to the currently logged-in user (requires enable_manual_linking = true); it cannot fuse two pre-existing accounts. No admin API reassigns an identity between users. → Merging existing dupes is an app-level FK-reparent + delete-loser operation, done case-by-case (Part B).
Auto-link can't be relied on. It fires only on matching verified email; Apple relay defeats it. → Dedup keys on phone, enforced by GoTrue.
Verified phone lives in auth.users; the app reads public.users. updateUser/verifyOtp set auth.users.phone + phone_confirmed_at. A trigger on auth.users UPDATE mirrors these to public.users.phone + phone_verified_at, and the custom_access_token_hook exposes a phone_verified JWT claim so the session gate reads it without a DB hit.
users.id = auth.users.id. Merging (Part B) keeps the survivor's id; the loser's FKs (reservations.user_id, credits, access codes, consent, roles, stripe_customer_id, …) must be re-parented onto the survivor before the loser is deleted.
Stripe subscriptions can't move between customers. A merge re-points the survivor at the Stripe customer holding the active subscription (stripe_customer_id is @unique → vacate the loser first). Cancel/recreate is disallowed. Both-have-active-subs is flagged for manual reconciliation, never auto-resolved.
Deleting the loser fires the auth-sync delete path and cascades app data — so reparent must complete first, in one transaction, with audit.

Three coupled capabilities, all on Supabase's native phone flows:

A1 — Attach + verify phone at profile completion (dedup at the source)

At complete-profile (OAuth) and email/password signup, the client calls supabase.auth.updateUser({ phone }). Supabase sends an SMS OTP via Twilio.
The user enters the code; the client calls supabase.auth.verifyOtp({ phone, token, type: 'phone_change' }). On success auth.users.phone + phone_confirmed_at are set, and the mirror trigger sets public.users.phone + phone_verified_at.
Dedup is automatic: if the phone is already on another account, updateUser({ phone }) fails with AuthApiError: ...already been registered before any SMS is sent. The client catches this and routes the person to their existing account: "You already have an account — sign in with {provider hint}." No second usable account is created.

Verified phone is strictly unique (decision): one verified, non-deleted account per number — enforced by GoTrue, with the partial unique index as a backstop. No shared-number exceptions; bands/couples use distinct numbers.

Two timing realities for "stop the fork": - Email/password: the account doesn't exist until signup completes; phone attach happens right after, and a taken phone is rejected → the person is routed to their existing login. - OAuth (Apple/Google): Supabase creates the auth user the instant the provider handshake completes — before we collect a phone — so the fork already exists by the time we reach complete-profile. When the entered phone is rejected as taken, it's catch-and-clean: refuse to finish onboarding as a new member, sign the user out, route them to their existing login, and delete the empty just-created fork (only if it has zero reservations/credits/subscriptions). before_user_created can't prevent the fork (no phone exists yet at OAuth time).

A "can't access your email?" path on the login screen: supabase.auth.signInWithOtp({ phone }, { shouldCreateUser: false }) → verifyOtp({ phone, token, type: 'sms' }). shouldCreateUser: false (and [auth.sms] enable_signup = false server-side) keeps this login-only — it signs in an existing account whose phone is verified, and never mints a new one. This is the answer to the real problem: a member who forgot which email they used, or lost access to it, gets back into their account.

A3 — Provider consolidation

On the account screen, offer supabase.auth.linkIdentity({ provider }) (Apple/Google) so a member can attach a second provider to their one account going forward, reducing future fragmentation. Requires enable_manual_linking = true.

Surfacing "you already have an account" extends the existing check-account-state enumeration-tradeoff precedent (account existence is already discoverable by design for stub routing).

Acceptance criteria (A)

Priority order: getting verified phones onto accounts (1, 7) and the recovery login (2) are the point; dedup-prevention (4) is the free consequence.

A new member cannot reach a completed profile without a phone proven via SMS OTP (updateUser({phone}) → verifyOtp({type:'phone_change'})), which sets phone_verified_at.
A member who can't access their email can sign in with a phone OTP (signInWithOtp({phone}, {shouldCreateUser:false}) → verifyOtp({type:'sms'})) and land on their existing account. Never mints a new account.
A member can attach a second OAuth provider (Apple/Google) to their account via linkIdentity().
Verifying a phone already verified on another non-deleted account fails (GoTrue rejects it), the second account is not completed, and the person is routed to sign in instead. (This catches collisions between accounts that have verified a phone; the existing unverified duplicates are handled manually — Part B.)
OTP send + verify are rate-limited (GoTrue auth.rate_limit + the provider's fraud controls) and failures are observable (Sentry).
Existing flows that set phone unverified (staff stub creation, comp/quick-signup) are unaffected; those remain phone_verified_at = null.
Retro-verification gate (the backfill — the priority): an authenticated member with phone_verified_at = null is sent to a blocking /auth/verify-phone interstitial and cannot reach app routes until verified. Enforced server-side via the phone_verified JWT claim in the authenticated layout, not just client routing. Staff/admin and active-impersonation sessions are exempt (so support isn't locked out).
Outage fallback: if SMS is unavailable, profile completion still succeeds with phone_verified_at = null (flagged, swept later) — a real signup is never lost to an SMS outage (Decision 7).

Part B — Existing-duplicate remediation (deferred, manual)

The existing clusters are resolved case-by-case, manually, together with Aaron — not via a built staff tool. Characterization (user audit-phone-dupes, 2026-06-19, 717 non-deleted users, 28 phone clusters) showed the work is small and the dangerous case is absent:

Category	Count	Action
empty-dupes (one real account, rest empty)	22	delete the empty shells
split (real data on 2+ accounts)	5	reparent (each is 1–2 reservations + small leftover credits)
both-active-subs (two live subscriptions)	0	none exist
all-empty (no data anywhere)	1	noise/test

Several "empty-dupe" clusters are internal @metrognome.com test accounts, so the real-customer set is smaller still. Zero both-active-subs means no Stripe subscription ever has to move between customers.

Approach. No automated script writes to prod users. When remediation is addressed, each cluster is reasoned about individually (which account survives, what reparents, any special case) and handled together — the reparent for a split case is the same FK work below, run deliberately per cluster, each writing an AccountMerge audit row. Until then, support routes a confused member to the account holding their studio.

Reparent (when a split cluster is handled): move the loser's FKs (reservations, credit balances/transfers, access codes, consent events/states, roles deduped, waitlist, attribution, profile image, …) onto the survivor in one transaction; carry phoneVerifiedAt + winner-null profile fields; if the loser holds the stripe_customer_id with the active sub, re-point it to the survivor (vacate first, @unique); soft-delete the loser, then delete its Supabase auth user; write an AccountMerge row (audit.source='staff:account-merge').

Acceptance criteria (B)

No remediation writes to prod users without Aaron, run together.
Empty shells are only deleted when the account has zero reservations, credits, and subscriptions; internal @metrognome.com accounts are never auto-touched.
A handled split cluster ends with all data on the survivor, the loser gone, and an AccountMerge audit row.
The existing clusters do not block the partial unique index: they're all unverified (phone_verified_at IS NULL) so they don't violate a verified-scoped index (ADR-022 amendment). Remediation and the index are independent.

Data shapes (Prisma deltas)

model User {
  // ...existing...
  phoneVerifiedAt DateTime? @map("phone_verified_at") @db.Timestamptz(6)
}

model AccountMerge {
  id            String   @id @default(uuid())
  winnerUserId  String   @map("winner_user_id") @db.Uuid
  loserUserId   String   @map("loser_user_id")  @db.Uuid   // retained for audit even after delete
  migrated      Json                                        // counts per entity type
  stripeAction  String   @map("stripe_action")              // 'repointed' | 'none' | 'manual_required'
  performedBy   String   @map("performed_by") @db.Uuid
  createdAt     DateTime @default(now()) @map("created_at") @db.Timestamptz(6)
  @@map("account_merges")
}

AccountMerge is the audit record for the manual remediations (Part B), not a tool's output. OTP state lives in Supabase/GoTrue (its auth.users.phone, phone_change, phone_confirmed_at); we keep no local OTP table. phone_verified_at on public.users is mirrored from auth.users.phone_confirmed_at by a trigger on auth.users UPDATE — the app reads public.users, GoTrue owns the verification. No new app env (Twilio is configured as Supabase's SMS provider, not called directly).

API surface

No MG API routes. Part A runs entirely on the Supabase client SDK against the user's browser session:

# attach + verify (complete-profile, email signup, and the /auth/verify-phone backfill)
supabase.auth.updateUser({ phone })                                 → GoTrue sends SMS OTP via the provider
supabase.auth.verifyOtp({ phone, token, type: 'phone_change' })     → sets auth.users.phone + phone_confirmed_at
                                                                      (mirror trigger → public.users.phone + phone_verified_at)
# recovery login (login screen "can't access your email?")
supabase.auth.signInWithOtp({ phone }, { shouldCreateUser: false }) → GoTrue sends SMS OTP (login-only)
supabase.auth.verifyOtp({ phone, token, type: 'sms' })              → session for the existing account
# provider consolidation (account screen)
supabase.auth.linkIdentity({ provider })                            → adds Apple/Google to the logged-in account

Duplicate detection is GoTrue's own uniqueness check on auth.users.phone — updateUser({phone}) throws AuthApiError: ...already been registered (before any SMS), which the client catches.

Part B (remediation) has no API surface — it is manual, case-by-case, with no self-serve or staff endpoint.

Flow

sequenceDiagram
  participant U as Member
  participant W as Web (complete-profile / verify-phone)
  participant GT as Supabase GoTrue
  participant T as Twilio (Supabase's SMS provider)
  participant DB as public.users (via mirror trigger)
  U->>W: enters phone
  W->>GT: updateUser({ phone })
  alt phone already verified on another account
    GT-->>W: AuthApiError "already been registered"
    W-->>U: "You already have an account — sign in instead"
  else available
    GT->>T: send SMS OTP
    U->>W: enters code
    W->>GT: verifyOtp({ phone, token, type: 'phone_change' })
    alt code valid
      GT->>GT: set auth.users.phone + phone_confirmed_at
      GT->>DB: trigger mirrors → phone, phone_verified_at
      W-->>U: verified; continue
    else invalid
      GT-->>W: invalid/expired code
    end
  end

Security & privacy

OTP send + verify are rate-limited by GoTrue (auth.rate_limit: sms_sent, token_verifications) and the SMS provider's fraud controls; Sentry on anomalies.
Phone becomes a login vector → SIM-swap / SMS interception is now an account-takeover path (it wasn't with email/OAuth-only). Accepted tradeoff (ADR-022); mitigate with the rate limits above and the provider's fraud signals.
A duplicate collision reveals only that an account exists for the number (the person is told to sign in) — bounded enumeration, consistent with check-account-state's documented tradeoff. We do not surface the other account's email.
Remediation runs manually with Aaron (no self-serve or automated merge); each merge is audited (AccountMerge + audit_logs) and atomic per cluster.

Out of scope

Phone as a signup method — phone is login/recovery for existing accounts only. Enforced by the handle_new_user email-required guard + client shouldCreateUser:false (the GoTrue provider toggle itself must stay ON because it gates phone login; corrected 2026-07-10).
Number recycling (negligible at our scale, ADR-022; handled ad hoc if it ever arises).
SMS marketing / notification content (separate sms feature).
MFA.
Automated, unattended merging, and a reusable staff merge UI/endpoint (remediation is manual and case-by-case — see Part B).

Decisions (resolved)

Phone verification mechanism: Supabase-native phone auth (ADR-022) — phone is a verified login + recovery credential, with Twilio configured as Supabase's SMS provider. The OTP is driven by GoTrue (updateUser/verifyOtp/signInWithOtp), not called by us. Superseded the original plan (self-managed Twilio Verify, phone-as-attribute, app-level OTP routes), which reimplemented uniqueness Supabase gives for free and offered no recovery login. Also rejected: an SMS shortcode — solves high-volume marketing throughput we don't have.
Dedup action on match: stop the fork + route to existing login (email/password: prevent; OAuth: catch-and-clean). Alt: allow creation, queue for staff-merge — weaker, lets dupes form.
Remediation ownership: manual, case-by-case, done with Aaron — no built tool (revised 2026-06-19 after characterization showed 22 trivial deletes + 5 tiny reparents + 0 both-active-subs; a reusable dry-run staff UI is over-built for a one-time job). Alt rejected: the spec's original staff merge tool; automated-by-phone-match.
Stripe on a split-cluster reparent: re-point stripe_customer_id; both-active-subs would be manual — but the characterization found zero such cases. Alt: cancel/recreate — rejected (billing disruption).
Phone uniqueness: strictly unique — one account per verified phone, no shared-number exception (Aaron, 2026-06-08). Enforced by GoTrue natively (ADR-022); a thin partial unique index on users(phone) WHERE phone_verified_at IS NOT NULL AND deleted_at IS NULL is a DB backstop for out-of-band writes, not the primary mechanism (ADR-022 amendment, 2026-06-20).
Default winner heuristic: account holding the active Stripe subscription; tiebreak newest (most-recent login, then newest createdAt) — not oldest (Aaron, 2026-06-08).
Verify-outage fallback: allow profile completion with phoneVerifiedAt=null, flag unverified, sweep later — don't lose a real signup to an SMS outage (Aaron, 2026-06-08).
Existing dupes: deferred — left untouched until they must be addressed, then resolved case-by-case together with Aaron, reasoning about each cluster (revised 2026-06-19; characterized at 28 clusters, see Part B). No automated prod-user writes. Interim for support: route a member to the account holding their studio.
Retroactive verification — required + blocking, and the priority of this build (Aaron, 2026-06-08; reaffirmed 2026-06-20): existing users with phone_verified_at = null are blocked behind an interstitial "verify it's you" screen (phone + OTP) on next authenticated load, before they can reach the app — the standard modern step-up pattern. This is the point of the feature (getting verified phones onto accounts so phone recovery works); dedup-prevention is the free consequence once the base is verified. Not a dismissible nag. Caveat: the interstitial verifies whichever account the member is signed into; for the handful of pre-existing duplicates, "phone on the right account" is settled in the manual Part B pass, not by the backfill.

Open questions

None — all resolved (see Decisions). Remaining detail is rollout sequencing, captured in plan.md.