dashboard-api: bootstrapUserWithIdentity creates new empty account for pre-Ory users on first login

Summary

When migrating a self-hosted deployment from a non-Ory authentication system to Ory (Hydra), existing users have no user_identities row. On their first Ory login, bootstrapUserWithIdentity finds no matching (oidc_iss, oidc_sub) entry and creates a brand-new user + team, leaving all their existing API keys, template sandboxes, and team memberships invisible.

Affected scenario

Self-hosted deployments that:

• Had users created before Ory integration (e.g. via Supabase auth, direct DB insert, or a previous auth system)
• Those users have data (API keys, templates) attached to their existing user_id in public.users
• Those users have no user_identities record linking their Ory OIDC sub to their existing user_id

Root cause

bootstrapUserWithIdentity in utils_team_provisioning.go only checks user_identities by (oidc_iss, oidc_sub):

existing, err := authTxDB.GetUserIdentity(ctx, authqueries.GetUserIdentityParams{
    OidcIss: identity.Issuer,
    OidcSub: identity.Subject,
})
switch {
case err == nil:
    profile.UserID = existing.UserID
case !dberrors.IsNotFoundError(err):
    return provisionedTeam{}, fmt.Errorf("get user identity: %w", err)
// not-found: falls through → profile.UserID = uuid.New() → new empty account
}

When the lookup returns not-found, a fresh UUID is used and a new empty team is created.

Impact

Pre-Ory users log in via Ory for the first time and see an empty dashboard. Their original API keys and template sandboxes are attached to the old user_id which is now orphaned.

Suggested fix

Add an email-based fallback after the identity lookup miss. If the OIDC token carries an email that matches an existing user's default-team email, and that user has no Ory identity yet, reuse their existing user_id and write the new user_identities row:

default:
    // Identity not found: check if a pre-Ory user with this email exists
    // and has not yet been linked to any Ory identity.
    if profile.Email != "" {
        if linkedUserID, found, err := s.authDB.FindUserIDByEmail(ctx, profile.Email); err == nil && found {
            rows, err := authTxDB.GetUserIdentitiesByUserIDs(ctx, authqueries.GetUserIdentitiesByUserIDsParams{
                OidcIss: identity.Issuer,
                UserIds: []uuid.UUID{linkedUserID},
            })
            if err == nil && len(rows) == 0 {
                // Safe: existing user has no Ory identity yet — merge
                profile.UserID = linkedUserID
            }
        }
    }

The safety guard (len(rows) == 0) ensures the merge only happens for users with no existing Ory identity for this issuer, preventing email-based account takeover for already-linked accounts.

After this change, UpsertPublicIdentity writes the permanent (oidc_iss, oidc_sub) → user_id link, so subsequent logins use the normal identity lookup path.

Notes

• This is a one-time migration path: once user_identities is written on first login, subsequent logins follow the existing fast path
• Requires the Ory-registered email to match public.teams.email for the user's default team (case-insensitive)
• For deployments where emails differ, a manual INSERT INTO public.user_identities before the user's first login remains the alternative

Start in utils_team_provisioning.go at bootstrapUserWithIdentity and trace the GetUserIdentity lookup and UpsertPublicIdentity flow. Implement and verify the pre-Ory email fallback while preserving the existing identity path and its safety guard. Done means a first Ory login reuses the existing user and subsequent logins use the stored identity without creating an empty team.