To help users gauge their license usage, we'd like to show a graph of active, dormant and suspended users over time.

While clarifying Daily Active User statistics in the frontend, we realised that we cannot yet show a graph of license utilization over time, because the database does not track when a user transitions from one state to the next. See below:

Potential partial blocker that I need input on. In short, I need to propose that we opt for a point in time gauge instead of a graph over time for total users against seat count. At least for this release.

This is because:

We don't track history for when users change status. We can't accurately display historical license count, because we only know whether a user is dormant or active today. We don't know when that changed. The same goes for when users where deleted. We know whether or not they were deleted. We don't know when they were deleted.

I can get a gauge done by release. The changes that need to be made to the db schema to support historical stats for dormancy and deletion require more consideration.

cc @matifali @chrifro @mafredri

Originally posted by @SasSwart in #15297

To unlock this, we need to:

• Update the database to track user status changes over time, probably using a user_status_changes table
• Update existing queries to use the new schema in a backward compatible way
• Write a new query that returns license utilization over time
• provide an endpoint that calls this query and returns the resulting timeline
• update the frontend to consume and graph this information

Should have:

• Add a new "Registered Users" chart as the first graph on the top with the upper limit bar, with the existing active users graph below it with no upper limit bar

Nice to have:

• On the registered users graph, overlay "suspended" and "dormant" users. We may want to be careful here since SCIM creates a ton of initial dormant users who never log in so we would want to filter those out and only have dormant users who logged in.
• Change Daily Active Users to configurable by day, week, and month just like template insights. Weekly is much more useful IMO