Account Email Change

A user's login email is auth.users.email. Historically it was read-only in the UI ("contact support") with no mechanism behind it, and roughly half the app reads a denormalised copy (user_profiles.email) that a signup-time trigger wrote once and nothing ever updated. This flow makes the email self-serviceable and keeps every copy and connected service in step.

Key source files:

• Initiate: src/routes/(app)/settings/+page.server.ts (updateEmail action) + the Email address card in +page.svelte
• Confirm: src/routes/(auth)/confirm-email-change/+page.server.ts + +page.svelte
• DB trigger: supabase/migrations/20260903120000_user_email_change_sync.sql, CRM primary follow-up 20260904090000_user_email_change_crm_primary.sql
• Fan-out: src/lib/server/email-change/fanout.ts + config.ts; FreeScout main-address sequence freescout-leg.ts; shared ownership rule src/lib/support/ownership.ts
• Backstop endpoint: src/api/routes/internal/email-change.ts, drained by workers/lifecycle-manager (daily lane)

The flow

1. Initiate (verified, mutates nothing)

updateEmail validates the input and calls supabase.auth.updateUser({ email }, { emailRedirectTo }). Under secure email change this sends a confirmation to BOTH the current and the new address and changes nothing yet. Because completing the change requires the link sent to the current inbox, a walked-away session cannot move the account. No password re-auth is required; the current-inbox gate is the protection.

No enumeration. If the new address already belongs to another account, gotrue answers email_exists. The action returns exactly the success response for that case and sends nothing, the same posture as forgot-password, so a signed-in caller cannot learn which addresses have accounts. The repeat behaviour is identical too: before every call to gotrue the action calls claim_email_change_window(), a security-definer RPC that atomically checks and stamps a 60-second window in user_email_change_requests, a table no client can read or write. Exactly one request claims the window; every other inside it is a 429, for a registered and a vacant target alike, and parallel submits cannot all pass the check. The gate fails closed if the RPC errors. The event is logged by user id so support can explain a "no email arrived" report. Residual: the response time of the upstream call differs between the two paths; the one-per-minute throttle bounds how many samples a caller can take.

2. Confirm (verify-at-submit)

The email links land on /confirm-email-change carrying token_hash. The token is redeemed only on form submit (never on the GET), so an email security scanner fetching the link burns nothing. This mirrors the password-reset flow (a live reset died to a scanner GET on 2026-08-27).

Completion signal. gotrue answers the FIRST of the two confirmations with a message-only 200 and no session; auth-js then shapes that body as data.user (its user ?? data fallback), so a truthy data.user proves nothing. The route treats the session gotrue returns as the signal: it is issued only once both links are confirmed and the address has applied.

No session is ever written. The verify runs through a throwaway client with persistence off, never the cookie-backed one. Confirming does not need a session (the verify applies the change server-side), and creating one would turn a link pressed in the wrong browser into login CSRF (a victim confirming an attacker's second link would be signed in as the attacker) and a stolen link into a ready-made session. The confirming browser is left exactly as it was; the user signs in normally with the new address. The page also sends Referrer-Policy: no-referrer so the token in its query string cannot leak through an outbound click.

3. Sync the database (one transaction)

on_auth_user_email_changed fires AFTER UPDATE OF email ON auth.users, the first and only UPDATE trigger on auth.users (the four existing ones are all AFTER INSERT). In one transaction it:

• updates user_profiles.email (the copy every notification/billing recipient resolves from);
• writes user_email_history (old → new, timestamped);
• re-links unlinked episode_guests / podcast_guests rows under either address (the signup-time linkers only fire on INSERT). podcast_guests rows are linked one per savepoint, so a UNIQUE(podcast_id, user_id) collision skips that row only;
• rewrites pending team_invitations off the old address, one per savepoint, so a seat-cap or duplicate rejection skips that invitation rather than aborting the change (and leaves the others rewritten);
• makes the new address the CRM contact's PRIMARY (crm_contacts.email) and KEEPS the old one as a non-primary crm_contact_emails alias, so the admin CRM shows the address the person uses while suppression and support-thread routing for the old address survive. The work is in crm_contact_adopt_address() (migration 20260904090000): updating crm_contacts.email fires the alias-sync trigger, which drops the old alias and inserts or PROMOTES the new one (never stealing an address that routes to a different contact), and the old address is then re-registered as non-primary. If another live contact already holds the new address as ITS primary (the person wrote to support from it before changing), this contact keeps its old primary and the new address is registered as an alias only where it is free; the operator fix is a merge in the admin CRM. The contact is found by user id, or, when the nightly link has not run yet, through the old address as the contact's PRIMARY alias (mirroring the sync's rule that only the primary-address holder may take a shared contact), and in that case it is CLAIMED with a race-safe conditional update so an unclaimed contact is never left behind for whoever next holds the old address. A user whose old login is only a merge-absorbed alias neither claims nor extends that contact; the nightly sync mints them their own. The CRM directory search also matches through the alias table, so a contact is found under every address it has held;
• enqueues one user_email_change_events outbox row.

4. Fan out to connected services

processEmailChangeEventsForUser (confirm-time, immediate) and drainPendingEmailChangeEvents (the daily backstop) run the same three legs, each best-effort with a per-leg *_synced_at marker so a retry skips what already succeeded.

The target is the user's current address, re-read from auth.users at processing time, never the event's recorded one. A delayed retry of an old event therefore cannot resurrect a superseded address (A→B failed, the user moved B→C, the A→B retry pushes C). An event whose old address is current again (a round trip) completes without touching any service. Retry budgets are per leg (stripe_attempts, resend_attempts, freescout_attempts, max 8 each): a leg's counter moves only when that leg was actually tried, so neither an unconfigured leg (recorded as not configured) nor a leg behind another leg's failures can be retired before it was ever attempted.

Leg	Action	If it fails
Stripe	customers.update(id, { email }) for every account the user owns	receipts/invoices/dunning keep going to the old address
Resend	create new contact carrying the old one's global unsubscribe flag and every topic state, then remove old (Resend cannot rename); a contact already at the new address is reused and pulled DOWN to the stricter consent (an opt-out on either side wins, globally and per topic); removal is required for success	marketing keeps the old address
FreeScout	make the new address the customer's MAIN address, re-point the user's own conversations to it, keep the old address on the record (the sequence below)	agents keep replying to the old address; inbound from the new one mints a duplicate

The link to each service is otherwise by ID (Stripe customer id) or handled by the CRM alias, so subscription/billing linkage and existing support-thread visibility survive regardless of the fan-out.

The FreeScout main address (src/lib/server/email-change/freescout-leg.ts). FreeScout has no "main email" field: the main address, which it stamps on every new conversation as customer_email and which an agent's reply defaults to, is simply the customer's first (lowest-id) email row. Only attaching the new address (what the first version did) leaves the old one main, so agents kept replying to an address the user had just left. Two more facts from the FreeScout and API-module source shape the sequence: an address removed from a customer while some conversation still carries it as customer_email is not deleted but moved onto a brand-new blank customer, and a conversation's customer_email can only be rewritten by moving the conversation to a DIFFERENT customer (which stamps that customer's main address). The leg therefore, idempotently:

1. attaches the new address to the customer;
2. if it is not yet main: parks the user's OWN conversations opened under the old address on a permanent parking customer ([email protected], created on first use), then replaces the customer's address list with just the new address, so the old row, now unreferenced by anything of the user's, is deleted;
3. re-adds the old address (and any other address the record carried) as fresh, later rows;
4. moves every parked conversation of this user back, which stamps the new main address on each. The mirror rows were bound to the user (created_by_user_id) before anything moved, so the address can no longer decide them.

Which record is the user's: the one whose MAIN address is the address they are leaving. A record that merely carries the old address as a secondary belongs to someone else (typically the address's previous holder, whose record this very leg left with their new address first) and is never rewritten; the user then gets a record of their own on the new address and only their own conversations move onto it. Ownership is never decided by address alone: only conversations the mirror attributes to the user (mirrorRowOwnedBy, shared with the support routes in src/lib/support/ownership.ts) are touched, and they are bound to the user (created_by_user_id) BEFORE anything moves them, so no address rewrite can disown them. A previous holder's conversations on a recycled address stay exactly where they were; in that case the old address ends up on the split record FreeScout created and the leg completes with a note in last_error saying so. When a separate record already leads with the new address (the user wrote in from it before changing), the user's conversations are moved onto it and the note asks for a merge in FreeScout. The parking sweep runs on every path that has a record of the user's to restore onto, so a run interrupted anywhere is finished by the next one whichever record the retry resolves to. A path with no such record (both addresses now on strangers' records, or on none, while something of the user's is still parked) never moves a parked conversation onto a record that is not the user's, which would make a stranger's address its reply-to; it fails closed, names the conversations in last_error, and stays retryable. A user with no display name gets a minted record named from their address. Kept addresses are re-added straight after the swap, before the un-park, so an interruption cannot lose them. The parking address is on the reserved .invalid TLD and is blocked by the CRM sender filter (the webhook and the backfill script, which also leaves parked conversations alone), so it can never mint a contact or a mirror row.

Support ownership after a change. Two things decide whether a mirrored conversation (crm_support_threads) is a user's, in src/api/routes/support/index.ts:

• A ticket opened in-app carries created_by_user_id, stamped by the app at creation (and stamped later onto every conversation the FreeScout leg re-points, which it has just verified as the user's). That binding is authoritative and cannot be moved by address recycling. (The FreeScout webhook also mirrors the conversation, but it knows only the address; it records customer_email ONCE, on first sight, and never writes the user binding. FreeScout reports the customer's current MAIN address on every event, so overwriting it would rewrite an old conversation to an address the user did not hold when it was opened.)
• An inbound, webhook-only row has only a contact, chosen by address. It is the user's when it is their OWN contact (resolveOwnedContactId: linked by user id first, otherwise only an alias nobody else has claimed) AND the address it was opened under was THEIRS AT THAT TIME. The routes rebuild each address's tenure intervals from user_email_history (the sign-up address from the ACCOUNT'S CREATION until the first change away, each later address from the change that brought it until the change that took it away, the current one until now) and require the row's creation time to fall inside one. No address was the user's before the account existed: a conversation older than the account was opened by whoever held the address then, even when the nightly sync later linked that person's unclaimed contact to this account. The creation time comes from auth.users (carried by requireAuth(), else read through the service-role client), the one clock a user cannot move; unknown resolves to "nothing vouches". An address the user gave up, someone else held for a while, and the user later took back has a gap in its tenure; a conversation opened in that gap is the other person's, even though the address is the user's current one again.

The live FreeScout check then accepts the same address set. This is what makes keeping the old alias safe in both directions: the previous holder cannot see tickets the address's new holder opens, and the new holder cannot see the previous holder's. FreeScout itself keys customers by address, so a recycled address can still merge two people into one help-desk customer record there; that is a help-desk data-model limit, not an app authorisation path.

Durability

user_email_change_events is a transactional outbox (service-role/RLS-only, like feed_invalidation_outbox). The confirm route processes it inline; lifecycle-manager's daily lane calls POST /api/internal/email-change/drain (shared bearer EMAIL_SYNC_INTERNAL_SECRET) to retry any leg that failed. A row is selected for retry while any unsynced leg still has budget; only when every remaining leg has exhausted MAX_LEG_ATTEMPTS does it drop out of automatic retry, staying in the table with its last_error for an operator. All connected-service credentials live in the app worker, so the worker only nudges (same pattern as the RSS _internal/invalidate lane).

Timeline

Both halves of a change show on the admin CRM timeline:

• Email change requested (account.email_change_requested): written to crm_events by the settings action after the auth call, as a side effect that can never fail the request. The payload records the target address and whether the auth service accepted the send; an address already on another account gets the same success response and no send, and the timeline says so. That is the fact behind a "no confirmation email arrived" ticket. It shows on the contact page, and on the account page only under the timeline's sole-account rule (the contact is linked to exactly one account), the same rule every other ingest writer follows. Repeats of the same request with the same outcome within an hour collapse into one row; a changed outcome (the target freed up and the retry was sent) keeps its own row. The target address is stored deliberately, behind the admin perimeter, because it is the detail an agent needs.
• Email address changed (account.email_changed): not stored twice. user_email_history is already the audit row, so the timeline projects it at read time (projectEmailChanges in src/api/routes/admin/crm.ts), which also means every change made before this existed shows up with no backfill. It shows on the contact page for the contact's linked user, and on the account page for every member, bounded by membership in the query itself (so the page limit counts only eligible rows): a change made before the person joined the account is their history, not the account's.

Production configuration (outside the repo)

• Supabase dashboard → Auth → Providers → Email: "Secure email change" must be ON (the double-confirm both-addresses flow). Mirror the local double_confirm_changes = true.
• Supabase dashboard → Auth → Email Templates → Change Email Address: the link href must be the value below (same RedirectTo convention as the Reset Password template; the app passes the per-host /confirm-email-change URL as emailRedirectTo). Local twin: supabase/templates/email-change.html.

{{ .RedirectTo }}?token_hash={{ .TokenHash }}&type=email_change

• Supabase dashboard → Auth → URL Configuration → Redirect URLs: https://my.show.fm/confirm-email-change must be allowed. The existing https://my.show.fm/** wildcard covers it.
• Secrets: set RESEND_AUDIENCE_ID on the app worker (if not already) and the same EMAIL_SYNC_INTERNAL_SECRET on both the app and lifecycle-manager workers.
• After migration 20260904090000: it re-opens the FreeScout leg on every outbox row the first version already processed, so the customer's main address is switched by the next daily drain. To run it now instead of waiting: POST https://my.show.fm/api/internal/email-change/drain with Authorization: Bearer <EMAIL_SYNC_INTERNAL_SECRET>. The same migration backfills the CRM primary address for those changes. ORDER MATTERS in production: promote the app worker BEFORE pushing the migration. The drain runs inside the app, so an old app draining the re-opened rows would re-run the attach-only leg and stamp them complete again. The crm-sync worker value-imports the sender filter and is part of the affected set.
• FreeScout: nothing to configure. The leg creates the parking customer (show.fm address-change parking, [email protected]) on first use; leave it in place. The FreeScout API cannot delete or merge customers, so a blank split record left behind for a genuinely shared address, or a second customer that already held the new address, is merged or deleted by an agent in FreeScout; the outbox row's last_error names the customer ids.

The Stripe portal side door

The billing portal previously allowed customer_update of email, letting a customer edit their Stripe email with nothing syncing it back (no customer.updated handler). src/api/routes/stripe/portal.ts now omits email from the inline config's allowed_updates and rejects any preset that re-enables it. Email changes go through this verified flow only.