Omar

Add a test for the empty case specifically, because it is the one that reaches production.

The list is populated in every environment where anyone tested, and then one day an upstream step produces nothing, the variable is empty, and the model either fails on syntax or — much worse — silently filters nothing and rewrites the table with everything.

Which of those two happens depends on how you wrote the macro, and it is worth deciding deliberately rather than discovering.

What has happened is that the provider moved from a bespoke OAuth 2.0 arrangement to standard OpenID Connect, and retired the old scope names and endpoints along with it. Several large providers have made this same move, so this is worth understanding once rather than as a one-off fix.

The difference, briefly. Plain OAuth 2.0 is an authorisation protocol — it gets you a token to call an API. Everything about identity was bolted on top: provider-specific scope names, and a provider-specific endpoint you called with the token to find out who the user was. Every provider did it differently, which is why social login integrations were all bespoke.

OpenID Connect is a standard authentication layer over OAuth 2.0. It defines:

• standard scopes — an identity scope, a profile scope and an email scope
• an ID token, a signed token containing the user's identity claims, returned alongside the access token
• a standard user info endpoint
• a discovery document at a well-known URL describing all of the above

The migration, concretely:

1. Replace the old provider-specific scopes with the standard ones. Request the identity scope plus whichever of profile and email you need.
2. Stop calling the old profile and email endpoints. The identity information now arrives in the ID token, or from the standard user info endpoint. For most sign-in flows you do not need a second call at all — the ID token has what you need, which is one fewer round trip.
3. Validate the ID token properly. This is not optional and it is where people get it wrong. Verify the signature against the provider's published keys, and check the issuer, the audience and the expiry. Use a library; do not hand-roll this.
4. Check the app registration. These providers usually require you to enable the relevant product or permission on the application before the new scopes will be granted. An unchanged registration is frequently the reason the scopes are still rejected after you have updated the code.
5. Watch the stable user identifier. The claim carrying the subject identifier may differ from whatever id you were storing before. If it does, you need a migration path or existing users cannot sign in — which is a far worse outage than the current one. Verify this on a test account before deploying.

Point five is the one that turns a routine migration into an incident. Check it first.

On building so the next change hurts less, which is the more valuable half:

• Use a library that speaks OpenID Connect generically and reads the provider's discovery document, rather than one integration per provider. Then a provider changing its endpoints is a document they publish, not a release you ship.
• Store identities as rows, not columns. A user has many linked identities, each with a provider name and a subject identifier. Removing or replacing a provider is then a data operation and nobody is orphaned.
• Always offer a second way in. A password, a magic link, anything. When a provider breaks, affected users have a route that does not involve you shipping a fix under pressure.
• Do not key accounts on email alone. Email is not stable, is not always returned, and is not always verified. Use the provider subject as the key and treat email as an attribute.

The good news is that having done this once for a provider that has moved to OpenID Connect, the next one is genuinely mechanical, because it is the same protocol.