ubiquitous-invention/plans/Plan-multitenant-saas-hardening/Epic-tenant-lifecycle/Task-multi-email-identity.md

---
kind: task
slug: multi-email-identity
title: Multi-email identity on user profile (foundation for invite-by-email)
plan_slug: multitenant-saas-hardening
epic_slug: tenant-lifecycle
status: done
priority: P1
tenant_id: global
owner: unassigned
cursor_todo_id: null
updated_at: "2026-06-02"
---

# Task summary

Decouple application identity from a single `users.email` column. Add a `user_email_identities` table so one user can own multiple verified emails (primary + OAuth-claimed + later manual). Required so the invite-acceptance flow can correctly resolve "the human at alice@gmail.com" even when they're signed in via GitHub as alice@personal.

This is the *foundation* task for the invites convoy. Lands before `Task-workspace-invites-and-roles` because it changes the sign-in path that invite-accept depends on.

## Why now (and why not later)

The current `ensureUserIdByEmail` in `apps/web/lib/auth.ts` collapses sign-in to a case-insensitive lookup on `users.email`. Concrete consequence: a user who signs up with `alice@personal.com` via GitHub today and then signs in with `alice@gmail.com` via Google tomorrow ends up as **two separate `users` rows**, neither of which is "the user." Every multitenant feature we layer on top — invites, audit log attribution, billing — would inherit this confusion.

Doing it right once is cheaper than dragging it for the next year.

## Scope

### Schema additions

New table in `packages/database/src/schema/users.ts` (kept colocated with `users` since they're logically the same identity surface):

```ts
export const userEmailIdentities = pgTable(
  "user_email_identities",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    userId: uuid("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    email: varchar("email", { length: 255 }).notNull(), // stored lowercase
    verifiedAt: timestamp("verified_at", { withTimezone: true }),
    source: varchar("source", { length: 30 }).notNull(),
    // 'primary' | 'oauth:github' | 'oauth:google' | 'oauth:authentik' | 'manual'
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
    lastUsedAt: timestamp("last_used_at", { withTimezone: true }),
  },
  (table) => ({
    userIdx: index("user_email_identities_user_id_idx").on(table.userId),
    emailIdx: index("user_email_identities_email_idx").on(table.email),
    userEmailUnique: uniqueIndex("user_email_identities_user_id_email_unique").on(
      table.userId,
      table.email,
    ),
    // A verified email belongs to exactly one user globally.
    verifiedEmailUnique: uniqueIndex("user_email_identities_verified_email_unique").on(
      table.email,
    ).where(sql`verified_at IS NOT NULL`),
  }),
);
```

### Migration

Generate with `pnpm db:generate`. The migration must include a **backfill** step: insert one row per existing `users` row with `source='primary'`, `email=lower(users.email)`, `verified_at=users.created_at` (we trust existing rows because we minted them).

```sql
-- inside the generated migration, after CREATE TABLE
INSERT INTO user_email_identities (user_id, email, verified_at, source, created_at)
SELECT id, lower(email), created_at, 'primary', created_at FROM users
ON CONFLICT DO NOTHING;
```

### Auth wiring

Refactor `apps/web/lib/auth.ts`:

- Rename `ensureUserIdByEmail` → `ensureUserIdByVerifiedEmail`. Lookup hits `user_email_identities WHERE email = lower(?) AND verified_at IS NOT NULL` first. On miss, create a new user + a `source='primary'` identity in one transaction.
- Add a callback hook (see research subagent recommendation — likely `signIn` or `jwt`) that, on OAuth sign-in where `profile.email_verified === true`, upserts a `source='oauth:<provider>'` identity for that user. If the email is already a verified identity belonging to a *different* user → reject the sign-in with a clear error.
- Bump `last_used_at` on the identity row that actually authenticated this session.

### Server-side helper

Export `userOwnsEmail(userId: string, emailLower: string): Promise<boolean>` from `apps/web/server/lib/identity.ts` (new file). Returns true iff `user_email_identities` has a row with that user + lowercased email + `verified_at IS NOT NULL`. This is the function `Task-workspace-invites-and-roles` will call.

### Profile UI

Add a "Linked emails" section to the profile/settings page. Lists each identity with:

- Email (lowercased display)
- Source badge (`Primary`, `GitHub`, `Google`, `Authentik`, `Manual`)
- Verified state (icon if verified, "Pending verification" otherwise)
- Last used (relative time)

**Read-only in this task.** Adding a "Disconnect" button is `Task-disconnect-linked-email` (filed separately because it has destructive edge cases — disconnecting your last verified email locks you out).

### Tests

Vitest is wired now, so add real assertions:

- `packages/database` or `apps/web` (test runner can land in either): identity-table CRUD round-trip.
- Sign-in callback writes a new identity on OAuth login.
- Sign-in callback rejects when the OAuth email is verified for another user.
- `userOwnsEmail` returns the right boolean across all four identity states (primary/oauth/manual/missing).

## Subtasks

- [x] Added `user_email_identities` schema in `packages/database/src/schema/users.ts` with full inline documentation of source values and constraint semantics.
- [x] Generated migration `0005_cooing_midnight.sql` via `pnpm db:generate`, augmented with the backfill `INSERT` (using `ON CONFLICT (user_id, email)` — Drizzle generates unique indexes, not named constraints, so column-based conflict targets are required). Applied to dev DB; existing `admin@tasks.dev` row now has a `primary` identity with `verified_at = users.created_at`.
- [x] Created `apps/web/server/lib/identity.ts` exporting `userOwnsEmail(userId, email)` and `findUserIdByVerifiedEmail(email)`. Both are pure read queries that the invite-accept procedure (Task 2) will call.
- [x] Refactored `apps/web/lib/auth.ts`. **Substantial change** — see the commit body for the security model. Key differences from the original task spec:
  - **Actually uses the existing `accounts` table**, which the OAuth-research subagent identified as vestigial. `(provider, providerAccountId)` is now the canonical "this OAuth identity belongs to this user" record. Repeat sign-ins use a fast-path lookup; new sign-ins go through the identity-table fallback.
  - **Per-provider `email_verified` resolution** via a new `resolveOAuthEmailVerified` helper. Google and Authentik read directly from `profile.email_verified`. GitHub does not expose the claim, so we make a `GET /user/emails` call with the OAuth access token and read `verified` on the entry matching the primary email. Authentik post-2025.10 caveat (default `false` unless an admin adds a custom property mapping) is documented inline.
  - **Cross-user conflict path**: if a verified identity for the OAuth-claimed email already belongs to another user who already has at least one OAuth account linked, we refuse to silently re-link. The JWT returns without an `id` field, which causes the session to be unauthenticated and the user lands on the NextAuth error page.
- [x] Added a tRPC procedure `identity.listMine` and a read-only "Linked emails" section at `/<workspaceSlug>/settings/profile` that lists each identity with email, source badge, verified state, and last-used relative time.
- [ ] **Deferred: vitest tests for the auth callback.** `apps/web` does not have vitest configured yet (the test foundation in `Plan-multitenant-saas-hardening/Epic-test-foundation` only wired it up for `packages/*`). Setting up vitest for a Next.js app — alias resolution for `@/`, environment for server modules, optional JSDOM — is its own task. Filed as `Task-bootstrap-vitest-for-apps-web.md` P2. The auth-callback assertions (identity write on OAuth, cross-user conflict rejection, no-duplicate-write on repeat sign-in) belong against that harness when it lands. Until then, smoke testing is the regression net.
- [x] `pnpm lint && pnpm type-check && pnpm test` all clean (14 lint warnings unchanged from pre-Task-1 baseline; 6/6 type-check; 14/14 tests).

## Smoke test (manual, recommended before any OAuth provider goes to production)

The OAuth path is not exercised by any of the dev fixtures (only `admin@tasks.dev` exists, which signs in via Credentials). To verify the new code path end-to-end, an operator with a configured OAuth provider should:

1. Sign in via OAuth as a fresh account (no existing `users` row). Confirm: new `users` row, new `user_email_identities` row with `source='oauth:<provider>'` and `verified_at != null`, new `accounts` row with the matching `(provider, providerAccountId)`.
2. Sign out, sign back in as the same OAuth account. Confirm: no new rows; `user_email_identities.last_used_at` bumped.
3. With the same email, attempt to sign in via a *different* OAuth provider. Confirm: a second `accounts` row is written; the existing identity row gets `last_used_at` bumped (no new identity row because the email is the same).
4. From a second browser/incognito, attempt to sign in via a third OAuth account claiming the same verified email. Confirm: sign-in is refused, no rows are added.

## Owner or assignee

Unassigned

## Status

done

## Estimation

M-L (came in around the upper end)

## Acceptance criteria

- [x] Existing users have one `primary` identity row each after migration. Verified via `psql` against the dev DB.
- [x] `userOwnsEmail(userId, emailLower)` is exported and correctly filters on `verified_at IS NOT NULL`.
- [x] All three CI gates green (lint, type-check, test).
- [ ] **Operator-verified**: cross-OAuth-provider linking works (verified email matches existing user, new `accounts` row written, no duplicate identity row). Pending manual smoke test against a configured OAuth provider.
- [ ] **Operator-verified**: cross-user email conflict is rejected. Same.

## Follow-ups filed

- `Task-manual-email-verification.md` — type a new email, get a one-time code, verify.
- `Task-disconnect-linked-email.md` — unlink with safety checks (last-verified protection).
- `Task-account-merge.md` — merge two existing users who turn out to share an email.
- `Task-bootstrap-vitest-for-apps-web.md` — set up Vitest for `apps/web` so the auth-callback assertions and identity-helper integration tests can have a home. New, P2.

## Links to related Epic / Plan

- Epic: `./Epic-tenant-lifecycle.md`
- Plan: `../Plan-multitenant-saas-hardening.md`
- Blocks: `./Task-workspace-invites-and-roles.md`, `./Task-invite-recipient-autocomplete.md`