OAuth and Audit Logs

At my company we have our own identity server 4 implementation that we use to issue access tokens and grant access to our API's. The problem that I'm facing is that we currently have audit field on every one of our database entities in the form of

CreatedByUserId BIGINT NOT NULL
CreatedDateTime DATETIME NOT NULL
UpdatedByUserId BIGINT NOT NULL
UpdatedDateTime DATETIME NOT NULL

Up until recently this has been fine. We have some database middleware that reads the sub claim from our access token and uses it to populate the audit fields. But we now have need to run an automated job that will update the database every 30 minutes. The problem is that this now raises the question, if I have processes or receive api calls via the client credentials flow where there is no user present in the interaction, how should I go about logging that it was "Service X" that updated this entity or "OtherCompany.ApiClient" that inserted that record. What can I do? How can I audit both users and non-users? Even if it required schema changes I am in need of ideas.

The main use case for these audit fields is that we have certain pages in our application where the business has asked that we display which user last updated an entity and when. And obviously these audit fields are not our true source of truth, as we do actual per-property audit logging behind the scenes when data changes.

One idea I had was to add a CreatedByUsername and UpdatedByUsername fields to tables, and then use information from the access token (either the user's username or the display name of the client in the case of client credentials tokens) to populate those values. That way we have all of the display information we need right there at row level. The only problem with this approach is a lot of our nurses are female, and females tend to change their name when they get married. So either they have to search for entities based on both of their last names, or we have to propogate a username updated event when they change their name to retroactively change the name in every other service.

Any ideas?

A user ID should not be a name. I always try to use subject claims in access tokens that are opaque, such as a UUID. So there is no impact when names change, which they will.

In your domain specific data there may be a different historical user ID, such as a database prinary key, to represent a nurse, that is mapped to resources representing patient care. If so, this might be a better user ID to use in your audit records.

Bear in mind also that personal data such as user names is often subject to regulations like GDPR these days. A good practice is to store such fields only in the authorization server, and return them only to application level components temporarily, in OAuth tokens. I would avoid denormalized name fields.

What the application needs to display is a report for humans. This is not the same as the best storage format. I would manage that by some kind of join, either in code or a database.

The backend job is not really a user. One option might be to leave the audit user null for such updates, then interpret that specially. Another option, that might sumplify a report implementation, might be to create some kind of pseudo user record for the backend job.

Building upon @Gary instead of using the user's name, generate and assign UUIDs as unique identifiers for users. This is can be considered primary to avoids issues related to name changes.

The audit logs should primarily focus on the user's unique identifier (UUID) rather than their personal details.

To display the last updated information and user details in the application reports, perform joins between the audit logs and the user data based on the unique user ID (UUID).