In the first version of this case, I deliberately kept things focused. The goal was to prove that a user could come in through Google, complete a minimal onboarding flow, receive ORDS client credentials, and actually use those credentials against a protected endpoint. That loop, once it closes, already carries a lot of value, and if you have not seen that part yet, it is worth starting there before going further.
But once the initial version was in place, it became clear that the design was still assuming something that does not hold very well outside controlled demos: that users will always be comfortable using a social identity provider as their entry point.
That assumption breaks faster than it seems.
In real scenarios, especially when you move closer to B2B or hybrid SaaS environments, users often prefer not to tie access to their personal social accounts. Sometimes it is a matter of policy, sometimes it is a matter of trust, and sometimes it is simply friction. Whatever the reason, the effect is the same: if social login is the only door available, you end up limiting adoption instead of simplifying onboarding.
This second iteration of the case comes directly from that realization.
Link for previous post: https://apexfromthefield.com/?p=297
Link for GitHub: https://github.com/denioflavio/apex-auth-lab-google-ords
Expanding the entry point without breaking the model
Instead of replacing the original flow, the idea here was to expand it without compromising the core design that was already working. The application now supports three entry paths:
- a custom login with local credentials
- Google Social Sign-In
- Facebook Social Sign-In (I have added this just for fun)
The key point is not that there are more buttons on the login page. The key point is that all these paths must converge into the exact same model once authentication is done.
The moment you allow multiple identity providers, you are no longer just authenticating users. You are effectively managing identity reconciliation.
The moment identity stops being trivial
With a single provider, identity feels simple. You get a stable identifier (google_sub in the first version), you map it, and you move on.
With multiple providers, the same person can arrive through completely different identities, and that forces a decision.
If a user signs up with Google today and comes back tomorrow using Facebook with the same email, are those two users or one? If someone creates a local account and later clicks “Continue with Google”, should the system merge them, block them, or silently duplicate them?
This is where many implementations start to drift into inconsistent behavior.
In this case, I chose to be explicit and a bit conservative. Email is normalized and treated as a cross-provider uniqueness boundary. Not as the primary identity key, but as a guardrail to prevent fragmentation.
The enforcement happens inside the authentication layer, not scattered across pages.
select auth_provider
into l_existing_provider
from app_users
where lower(email) = lower(p_email)
and auth_provider <> p_provider;
-- if have found, raise an error
raise_application_error(
-20001,
'This email is already registered using ' || l_existing_provider
);
This means that when a user attempts to authenticate using a different provider for an email that already exists, the flow is stopped and explained, instead of silently creating a second account.
The auth layer became a real component
In the first version, the authentication logic was relatively thin. Now it needed to evolve into something that could handle multiple providers coherently.
That is where the APP_MULTI_AUTH package comes in.
It centralizes:
- custom authentication
- post-login logic per provider
- account creation
- duplicate detection
- social profile completion
All providers eventually flow through the same orchestration layer
-- authentication orchestration
APP_MULTI_AUTH.AUTHENTICATE_CUSTOM
APP_MULTI_AUTH.POST_LOGIN_GOOGLE
APP_MULTI_AUTH.POST_LOGIN_FACEBOOK
-- onboarding + provisioning
APP_USER_API.COMPLETE_REGISTRATIONThis was not about adding complexity. It was about preventing the complexity from leaking everywhere else.
Custom login is not a fallback, it is a first-class path
One of the most important shifts in this version is that custom login is not treated as an exception anymore.
The user can now:
- open the app without any social dependency
- create an account with email and password
- complete the same onboarding model
- receive ORDS client credentials immediately
And from that point on, the behavior is identical.
app_multi_auth.create_custom_user(
p_email => :P11_EMAIL,
p_full_name => :P11_FULL_NAME,
p_birth_date => :P11_BIRTH_DATE,
p_phone_number => :P11_PHONE_NUMBER,
p_password => :P11_PASSWORD,
p_out_app_user_id => :G_APP_USER_ID,
p_out_client_name => :G_ORDS_CLIENT_NAME,
p_out_client_id => :G_ORDS_CLIENT_ID,
p_out_client_secret => :G_ORDS_CLIENT_SECRET,
p_out_created_now_flag => :G_CREDS_CREATED_NOW
);That call does exactly what the social flow does after profile completion: it creates the application user and provisions the ORDS client in one step.
Facebook introduces a subtle but important difference
Google was a natural choice for the first version because it is predictable in terms of identity data. Facebook adds a nuance that forces the flow to be more careful.
Email is not guaranteed.
That small detail changes the onboarding logic in a meaningful way. Since email is being used as a cross-provider uniqueness constraint, the system cannot proceed blindly when the provider does not return it.
The solution is simple but intentional:
- if email is present, proceed normally
- if email is missing, require the user to provide it
- do not complete onboarding without a verified email
This ensures that identity reconciliation remains reliable across providers
One user, still one client
Even with multiple login paths, one rule remained unchanged: one application user maps to one active ORDS OAuth client.
This avoids a cascade of duplicated credentials and keeps the API layer stable.
select c.ords_client_id
into l_client_id
from app_user_oauth_clients c
where c.app_user_id = :G_APP_USER_ID
and c.active_flag = 'Y';
If a client already exists, it is reused. If not, it is created.
No duplication and no silent regeneration.
The flow is more flexible, but also more honest
The first version proved that the onboarding and API access loop could be closed.
This version proves that the same loop can survive real-world variation:
- different identity providers
- users avoiding social login
- duplicate account attempts
- inconsistent identity data
All of that still converges into a single outcome:
- one application user
- one ORDS client
- one consistent way to access the API
The system is no longer assuming ideal conditions. It is explicitly handling the cases where those conditions do not exist.
Where this naturally goes next
Even in this version, there are still deliberate simplifications.
There is no client rotation flow, no administrative UI, no multi-client-per-user strategy. Those are valid next steps, but they would dilute the clarity of the core idea if introduced too early.
What this version adds is not more features. It adds resilience to the original design.
And that is usually the step where a clean demo starts to look like something you could actually reuse.
Maybe I’ll make a part 3 😉
Deixe um comentário