Skip to content

Account Dedup + Merge — Implementation Plan

Status: Building per ADR-022 (Accepted 2026-06-19, amended 2026-06-20). Supabase-native phone auth: phone as a verified login + recovery factor, GoTrue-native uniqueness, linkIdentity() for OAuth consolidation, a partial unique index as a DB backstop. Priority: get verified phones onto accounts (recovery); dedup-prevention is the free consequence. Spec: spec.md. Research: research.md.

What we're building (plain terms)

Two goals, in priority order:

  1. Verified phones on accounts — so a member who forgets, or loses access to, the email they signed up with can still log in (phone-OTP recovery). This is the point.
  2. One person → one account — once phones are verified, Supabase blocks a second account on the same number for free.

The handful of pre-existing duplicate accounts are cleaned up manually, with Aaron (spec Part B / Phase B below) — not part of this build.

How a verified phone flows through the system

updateUser({ phone }) → SMS OTP (Supabase's Twilio provider) → verifyOtp({ type: 'phone_change' }) sets auth.users.phone + phone_confirmed_at. A trigger on auth.users UPDATE mirrors those to public.users.phone + phone_verified_at (the app reads public.users). The custom_access_token_hook adds a phone_verified JWT claim; the authenticated layout gates on it.

Keep / discard from the earlier (superseded) work

Keep: Phase 0 schema (phone_verified_at, account_merges); the user audit-phone-dupes script + the 28-cluster characterization; Part B manual remediation.

Discard (unwound in Phase 1): PhoneVerificationService, PhoneIdentityService, the /api/auth/phone/{start,check}-verification routes, and their tests — OTP and dedup are now Supabase's job. No app-level OTP routes; no raw-SQL dedup query.

TDD throughout (Aaron's rule). Each phase ships independently. Characterization (2026-06-19, user audit-phone-dupes): 28 clusters — 22 empty-dupes, 5 split, 0 both-active-subs, 1 noise. The merge tool was dropped (over-built for a one-time job); remediation is manual and case-by-case. See spec.md Part B.

Phase 0 — Schema foundation ✅ (done)

  • [x] phoneVerifiedAt DateTime? @map("phone_verified_at") on User.
  • [x] AccountMerge model (account_merges, append-only audit, bare Uuid columns, winner/loser indexes).
  • [x] Migration 20260619082900_add_phone_verified_at_and_account_merge; prisma generate; typecheck.

Phase 1 — Unwind superseded code ✅ (done, f4de89c3a)

  • [x] Delete PhoneVerificationService, PhoneIdentityService, /api/auth/phone/start-verification, /api/auth/phone/check-verification.
  • [x] Delete their tests: PhoneVerificationService.unit.test.ts, PhoneIdentityService.unit.test.ts, phone-verification.api.test.ts.
  • [x] Remove the two services from the auth barrel (services/auth/index.ts).
  • [x] Typecheck clean.

No behavior change — removed the self-managed approach.

Phase 2 — Supabase config + SMS provider ✅ (done, f3d58472d)

  • [x] config.toml: enable_manual_linking = true; [auth.sms.twilio_verify] enabled = true; [auth.sms] enable_signup = true (corrected 2026-07-10: this is GoTrue's phone-provider toggle and gates ALL of /otp — false kills phone login with phone_provider_disabled; email-required is enforced by the handle_new_user guard instead, migration 20260710150000); [auth.sms.test_otp] for local.
  • [x] [auth.sms] enable_confirmations = true — critical: with it false, GoTrue auto-confirms a phone change with no OTP (would mark phones "verified" for free). Caught during local testing.
  • [ ] Aaron — prod (Supabase dashboard, separate): config.toml governs the LOCAL stack only (no remote link/branching). In the dashboard: enable Twilio Verify + creds, require phone confirmations, enable manual linking, and enable the Phone provider (it gates phone login; phone signups are blocked by the handle_new_user email guard, not the provider toggle).
  • [x] Verified a local OTP round-trip via test_otp.

Local-testing notes: test_otp keys must be GoTrue's normalized form (digits, no +, e.g. 15555550123). test_otp bypasses the provider for updateUser/verifyOtp, so no Twilio creds are needed locally for that number. Corrected 2026-07-10: the earlier "Unsupported phone provider is a local GoTrue limitation" conclusion was wrong — the cause was config.toml's env(SUPABASE_AUTH_SMS_TWILIO_*) references passing through as literal placeholder strings because the vars weren't in the CLI's environment at supabase start. The CLI auto-loads supabase/.env for that substitution — the Twilio creds (incl. TWILIO_VERIFY_SERVICE_SID) live there alongside the Google OAuth secret, and scripts/worktree.sh copies the file into new worktrees. With it present, real-number sends work locally, including the signInWithOtp send path.

Phase 3 — Mirror trigger + JWT claim + index (DB) ✅ (done, 7b97d80f6)

Hand-written raw-SQL migration 20260620095938_add_phone_mirror_trigger_and_verified_claim in apps/api/prisma/migrations/ (prod deploys via prisma migrate deploy; chronologically last so its CREATE OR REPLACE wins all pipelines).

  • [x] Trigger mirror_auth_user_phone() (AFTER UPDATE on auth.users, IS DISTINCT guard on phone/phone_confirmed_at): mirrors + normalizes (+1XXXXXXXXXX) onto public.users matched by id (= auth.users.id post-unify). SECURITY DEFINER, search_path=''.
  • [x] custom_access_token_hook gains app_metadata.phone_verified (reproduces the current body + the new claim).
  • [x] Partial unique index users(phone) WHERE phone_verified_at IS NOT NULL AND deleted_at IS NULL — backstop (ADR-022 amendment).
  • [x] Validated against the live local GoTrue stack: mirror + normalize, the index rejecting a second verified dupe (fires inside GoTrue's verifyOtp confirm), the no-op guard, and the phone_verified claim (false→true) all pass.

Phase 4 + 5 — Verify screen + gate (CONVERGED) ✅ (done, 2b23f48e8, 910a2f33f, 8e146bcce)

Phase 4 and 5 collapsed into one screen + one gate (Aaron, 2026-06-20): new signups and existing members both reach phone verification the same way — the gate redirects any authenticated, non-exempt user with phone_verified = false to /auth/verify-phone. No per-form OTP wiring into complete-profile/AuthForm signup is needed; the gate catches everyone uniformly. Auth becomes multi-step (signup/login → complete-profile for name → verify-phone for OTP → app).

  • [x] Reusable PhoneVerification molecule (phone → updateUser({phone}), HeroUI InputOtp → verifyOtp({type:'phone_change'})), duplicate detection via err.code === 'phone_exists' (routes to existing login), resend, Sentry on unexpected errors. Unit-tested.
  • [x] /auth/verify-phone screen (AuthSurface) — prefills the known phone, skips out if already verified, refreshes the session and routes on success.
  • [x] phoneVerified on SessionData from the phone_verified JWT claim (dal.ts).
  • [x] Dark-launch feature flag isPhoneAuthEnabled() (Edge Config phoneAuth + PHONE_AUTH env, mirrors maintenance mode), off by default. Gates the whole phone-auth surface: the verify gate (account layout + all three checkout pages, with staff/admin + active impersonation exempt), the /auth/verify-phone and /auth/phone-login pages, and the "Can't access your email?" recovery link on the sign-in tab. Flag-test green.
  • [x] Outage fallback (Decision 7) handled operationally by the flag (flip off if SMS is down, the kill switch) instead of an in-code skip.
  • [ ] OAuth catch-and-clean (delete the empty just-created fork on a duplicate) — deferred; the gate + manual Part B cover dupes. Note (verified locally): auth.admin.deleteUser does NOT cascade-delete the public.users row, so a catch-and-clean must delete both.

Activation prerequisites (Aaron, before flipping the flag): deploy the migration to prod (prisma migrate deploy), configure Twilio Verify + require phone confirmations + manual linking + enable the Phone provider in the Supabase dashboard (phone signups stay impossible via the handle_new_user email guard). Then flip phoneAuth on. The flag is also the rollback/kill switch.

Phase 6 — Phone-OTP recovery login (the payoff) ✅ (done, efa7591d1)

  • [x] "Can't access your email?" link on the AuthForm sign-in tab → /auth/phone-login: phone → signInWithOtp({ phone }, { shouldCreateUser: false }) → OTP → verifyOtp({ type: 'sms' }) → session → role-based/returnTo redirect. Reusable PhoneLogin molecule.
  • [x] Unknown/unverified numbers can't create an account (shouldCreateUser:false client-side + the handle_new_user email-required guard at the DB layer); friendly message, same enumeration posture as check-account-state. Verified live 2026-07-10: login send 200, unknown number otp_disabled, raw phone-signup attempt rejected with zero orphan rows, full send→verify→session round trip.
  • [x] Unit-tested (E.164 send, unknown-number message, sms verify → onSuccess, invalid code). Local note: the signInWithOtp send needs real Twilio creds in supabase/.env (see corrected local-testing notes above). Verified live 2026-07-10 with a real number end to end.

Phase 7 — Provider consolidation ✅ (done)

  • [x] SignInMethods section on /account/profile (behind the phoneAuth flag): getUserIdentities() lists what's linked, linkIdentity({ provider }) for Google/Apple (redirects through /auth/callback?next=/account/profile), unlinkIdentity() shown only at ≥2 identities. Display-only phone row (Verify link when unverified). Identity-taken errors surfaced ("already connected to another account").
  • [x] Tests: identity listing, link call shape (provider + callback redirect), unlink guard at 1 identity, unlink + refresh at ≥2, phone row states, identity-taken error.

Done when: a member can attach a second provider to their one account. ✅

Team decision (2026-07-10): phone-OTP login is a first-class login method (visible "Sign in with phone" in the sign-in provider group), not recovery-only; email remains always required ([auth.sms] enable_signup = false unchanged).

Phase 8 — Consistent onboarding steps (2026-07-10) ✅ (done)

Aaron: make the steps identical for every auth method and collect each fact exactly once.

  • [x] Step 1 credentials-only: name/phone fields removed from the signup tab (compactSignup behavior is now universal; the context flag only affects modal width).
  • [x] SMS marketing consent moved off the signup tab entirely (reviewer finding: with no phone at step 1, the consent PATCH hit ConsentService with an empty contact point and 500'd silently). Consent now lives only at complete-profile, next to the phone it applies to; signup keeps the ToS/privacy line and syncs email opt-in only.
  • [x] Step 2 /auth/complete-profile collects name + phone for all methods. Flag on: saves name only, fires the phone-change OTP, and shows the code step inline (step 3); phone reaches public.users only via the mirror; consent sync runs post-verify. Flag off: prior attribute-save behavior.
  • [x] Shared usePhoneChangeOtp hook (send/verify/resend) backs both PhoneVerification and complete-profile.
  • [x] DB invariant: clear_phone_verified_on_phone_change BEFORE UPDATE trigger nulls phone_verified_at on any direct phone write (migration 20260710113000, validated against the local stack: direct write clears, mirror write keeps, unrelated update keeps).

  • [x] Tests: AuthForm credentials-only assertions; CompleteProfileContent flag-off save shape, flag-on name-only PATCH + OTP send + code step, duplicate-phone stay, verify → refresh → redirect.

  • [x] Profile page phone management (same day, Aaron: no deferrals): with the flag on, the phone field stays editable but saving a changed number fires the OTP and a code-entry modal instead of writing it (Aaron's UX call: edit-then-confirm, matching the email field's pattern; the first read-only-field-plus-button version was scrapped). Inline Verify action for an unverified saved number. Phone never rides the profile save payload. GET /profile now returns phoneVerifiedAt. SignInMethods shows the phone row only when verified, display-only.

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

Not a built tool. Characterization (user audit-phone-dupes, 2026-06-19): 28 clusters — 22 empty-dupes, 5 split, 0 both-active-subs, 1 noise. Resolved case-by-case, together with Aaron, reasoning about each cluster. No automated writes to prod users.

  • [x] Characterize the clusters — scripts/user.ts audit-phone-dupes (read-only).
  • [ ] Empty-dupes (22): per cluster, confirm the shells have zero reservations/credits/subs, then delete them. Never auto-touch internal @metrognome.com accounts.
  • [ ] Split (5): per cluster, decide the survivor and reparent the loser's FKs in one withTransaction (audit.source='staff:account-merge'): reservations, credit balances/transfers, access codes, consent events/states, roles (dedup), waitlist, attribution, profile image; carry phone_verified_at + winner-null profile fields; re-point stripe_customer_id if the loser holds the active sub (vacate first, @unique); soft-delete loser, then auth.admin.deleteUser(loserId), revokeUserSessions(winnerId); write an AccountMerge row.
  • [ ] Before any delete: enumerate every user_id FK in schema.prisma and assert the loser has no remaining inbound references.
  • [ ] Re-run audit-phone-dupes after each batch.

Done when: the existing clusters are resolved (with Aaron), each audited.

Suggested sequence

1 (unwind) ✅ → 2 (config) ✅ → 3 (DB) ✅ → 4+5 (verify screen + flag-gated gate) ✅ → 6 (recovery payoff) ✅ → 7 (linkIdentity, follow-up). Dedup-prevention goes live when the flag is flipped (after the prod prerequisites). Phase B is independent and manual. Shipping as a single PR (Aaron); Phase 7 deferred to a follow-up.

Risks / watch-items

  • Phone is now a login vector (SIM-swap). Account-takeover path that didn't exist with email/OAuth-only. Mitigate with GoTrue rate limits (auth.rate_limit) + the provider's fraud signals. Accepted tradeoff (ADR-022).
  • Transition dedup gap. GoTrue's uniqueness catches collisions only between accounts that have verified a phone. Existing accounts' phones are unverified until backfill (Phase 5), so a brand-new duplicate of a not-yet-verified member can still slip through during rollout. Low harm at our scale; the existing handful are cleaned in Phase B regardless. The backfill closes the gap as the base verifies.
  • Backfill verifies whichever account the member is signed into, not necessarily their "main" one. For a pre-existing duplicate, "phone on the right account" is settled in Phase B, not by the backfill. Don't read "everyone has a verified phone" as "every duplicate resolved."
  • Mirror-trigger correctness. If the auth→public trigger drifts or is bypassed, public.users could disagree with auth.users. The partial unique index is the loud backstop; verify the trigger fires on both phone_confirmed_at-set and phone-change.
  • phone_change column isn't unique in GoTrue (a documented Supabase gotcha) — only relevant to number recycling, which is out of scope (ADR-022). We only ever call updateUser on the logged-in user's own session.
  • OAuth catch-and-clean must never delete an account with real data — guard on zero reservations/credits/subs before deleting an empty fork.
  • FK enumeration completeness (Phase B split cases) — a missed user_id FK orphans data on a reparent. Enumerate the schema exhaustively; assert no remaining inbound references before delete.
  • No prod-user writes without Aaron — remediation is manual and reasoned per cluster.
  • db.users soft-delete is a hard cascade delete (Prisma) — a Phase B reparent must complete before delete or data is lost.
  • Shared real numbers (bands/couples) are forced to distinct numbers — expect a few support cases; no technical exception.