How to keep an authorization server in sync with an external database?

Question

My current system looks like this:

• Authorisation Server (AWS Cognito User Pool) containing user info (id, username, email)
• MySQL Database containing non-auth related user info (eg. friend connections)
• API (Used by an application to retrieve data from the MySQL Database and/or Authorization Server)

When a user creates an account, a new user is created in the Authorisation Server and a "link" is made in the MySQL Database using the new user id. This link is made via an API call to my API, which then inserts a new row into the MySQL Database.

Problem:

The problem is that is is very expensive (time wise) when I want to get a users profile, I have to make a call to query the database to get the users non-auth related info and then make a call to get the users info such as (username, email).

Proposed solution:

Create a "copy" of the users personal info in the MySQL database. However, I can see a potential issue with this approach. For example, if a user updates their username and the request to the Authorisation Server to update it is successful, but the MySQL database request failed (connection failure etc), then the two systems will be out of sync.

How can I prevent this synchronization issue?

Answer 1

2-phase commits work just fine for these scenarios. Change the record in the database first, change Cognito, and then commit the transaction. If the cognito change failed, roll it back and surface an error. You can always have 1 "unsafe" transaction and still maintain transactional integrity.