SSO identity model

Audience: TokenTimer Enterprise system administrators and operators who need to understand how SSO identity is tracked across providers and over time.

This document explains how SSO users are linked to the internal users table, what happens when emails change in the IdP, and how local-auth and SSO accounts coexist.

Tables

users                                   external_identities
+------+----------+----------+          +---------+----------------+--------+--------------+
| id   | email    | auth_method  <---  | user_id | provider_slug  | subject_id            |
+------+----------+----------+          +---------+----------------+--------+--------------+
                                        UNIQUE (provider_slug, subject_id)
                                        ON DELETE CASCADE -> users

• external_identities.(provider_slug, subject_id) is the canonical identity key.
• users.id is the internal account.
• users.email is profile data, not identity. It can change.
• users.auth_method tells you how the user was bootstrapped (local or sso). It is not updated on every login and does not flip between providers; the source of truth for which IdPs a user has linked is the external_identities table.

A single user can hold multiple external_identities rows: one per provider slug they have linked. We never duplicate (provider_slug, subject_id) (UNIQUE), so the same IdP subject can only point at one TokenTimer user.

users.auth_method semantics

Scenario	auth_method after the event
Local account created via admin bootstrap / invite.	local
First SSO login from a local account (linked).	sso (set once, then sticky)
First SSO login creating a brand-new account.	sso
Second SSO login via a different provider.	sso (no change)

We deliberately do not store the protocol enum (saml / oidc) on users.auth_method because a user with both providers linked would otherwise flip the column on every login and lose historical signal. Read external_identities.provider_slug and external_identities.protocol instead.

Login resolution algorithm

For every SSO callback:

1. Extract subject_id from the configured claim path. OIDC binds the
   subject to the ID Token `sub` (UserInfo claims are only merged in
   when the UserInfo `sub` matches the ID Token `sub`).
2. SELECT FROM external_identities
    WHERE provider_slug = $1 AND subject_id = $2.
   HIT  -> return that user_id. Update last_seen_at.
   MISS -> step 3.
3. Fall back to email match:
   SELECT FROM users WHERE LOWER(email) = LOWER($1).
   HIT -> the email-fallback link is only written when ALL of:
       - allowAutoLink (SSO_ALLOW_AUTO_LINK_LOCAL_ACCOUNTS) is true, AND
       - the IdP confirmed mailbox ownership (SAML: always, the
         assertion is cert-signed; OIDC: email_verified=true), AND
       - the matched user is not already linked to a DIFFERENT subject
         under the same provider.
     Otherwise the resolver refuses with one of:
       - local_account_exists_auto_link_disabled
         (existing local-password account, opt-in missing)
       - sso_account_exists_subject_mismatch
         (existing SSO account, opt-in missing or different subject)
       - email_not_verified_for_link
         (OIDC IdP did not assert email_verified=true)
   MISS -> step 4.
4. Create a new user + new external_identities row in the same TX.

last_seen_at is bumped on every successful resolution so admins can see the freshness of each link.

Email rename

Bob's IdP email changes from bob@old.example to bob@new.example.

Time	What happens
T0	Bob has users.id = 42, email = bob@old.example, external_identities (keycloak, sub-42).
T1	Admin renames Bob's email in the IdP. The IdP keeps emitting sub = sub-42.
T2	Bob logs in. The resolver looks up (keycloak, sub-42), hits, resolves user_id=42. Login succeeds.
T3	TokenTimer updates users.email = bob@new.example from the new email claim.

No new account is created. Tokens, workspace memberships, and audit history follow Bob. Keying on a stable subject (rather than email) is what makes this transparent.

Two providers, same user (cross-provider merge)

Alice has Okta SAML and Azure OIDC, both emitting alice@example.com. Cross-provider merge runs through the email-fallback path, so it requires the admin to opt in via SSO_ALLOW_AUTO_LINK_LOCAL_ACCOUNTS=true.

Time	What happens
T0	Alice has no TokenTimer account.
T1	Alice logs in via Okta SAML. Resolver: (okta, sub-okta-A) miss, email miss, create user_id=10, link (okta, sub-okta-A) -> 10.
T2	Alice logs in via Entra OIDC. Resolver: (entra, oid-azure-A) miss, email hit on user_id=10. With auto-link enabled AND email_verified=true the resolver links (entra, oid-azure-A) -> 10. With auto-link disabled the login is refused with sso_account_exists_subject_mismatch and an admin has to merge the accounts.
T3	When the link succeeds, Alice has one TokenTimer account with two external_identities rows.

Both providers can then be used interchangeably. Logout invalidates only the TokenTimer session, not the IdP session.

Local account collision

Alice already has a local-password TokenTimer account alice@example.com. The org now rolls out Okta SAML.

Default behaviour (SSO_ALLOW_AUTO_LINK_LOCAL_ACCOUNTS=false):

1. Alice clicks "Sign in with SAML".
2. Resolver: subject miss, then email hit with auth_method = 'local'; audit local_account_exists_auto_link_disabled; refuse login.
3. Alice sees the login error directing her to the admin.

Why default-deny? Because if Alice's local password is compromised, a malicious IdP login would silently merge into her account. Default-deny forces an admin decision.

Resolution paths:

• Enable allow auto-link globally under System Settings → Single Sign-On, or override per provider under Manage SSO providers → Advanced. Env equivalent: SSO_ALLOW_AUTO_LINK_LOCAL_ACCOUNTS=true. Alice's next SSO login attaches her IdP subject to the existing local account.
• Or delete the local account first, then let SSO create a fresh one.

Audit trail

Action	When
LOGIN_SUCCESS	After a successful resolution. Metadata includes method (saml or oidc), subject_id, new_user, link_created, previous_auth_method, idp_groups_seen, matched_groups (array), matched_groups_count, admin_granted, workspace_grants_count, workspace_revocations_count.
LOGIN_FAILED	Failure with structured reason: callback_error, no_user, session_login_error, local_account_exists_auto_link_disabled, sso_account_exists_subject_mismatch, email_not_verified_for_link, userinfo_sub_mismatch, missing_email.
MEMBERSHIP_SYNCED_FROM_SSO	When group resolution actually changes membership (see Group mappings).

The external_identities row itself records the raw IdP-asserted email in email_at_link (original casing preserved for forensic correlation with IdP logs), display_name_at_link, and last_seen_at (bumped on every login, regardless of whether the match came from the subject or the email fallback path).

FAQ

Can I manually link two existing accounts into one?

Not today. The auto-link path handles cross-provider matching for new sign-ins only when SSO_ALLOW_AUTO_LINK_LOCAL_ACCOUNTS=true and the IdP confirmed email ownership. A manual "merge accounts" UI for admins is on the backlog.

Can I disable subject-based identity entirely?

Leave the subject path empty in SSO Providers under Claim mapping. The resolver falls back to email-only matching. Not recommended: any IdP-side email rename will create a new TokenTimer account.

Can I have multiple SAML IdPs in one deployment?

Yes. Add one provider row per IdP with a unique slug. Each slug gets its own sign-in button, callback URL, and group mappings.