I'm looking for a reliable way to query my MAU counts. The reason for this is that it's a B2B/B2C hybrid SaaS app that I'm building and one of the utilisation metrics I want to bill my clients for is their proportion of my MAUs I'm perfectly happy joining

auth.users

to my own account table and joining this to my organisation_users and organisation tables etc – I've got all that and I can attribute what I think is an MAU to an organisation. Below is a massively simplified version of the query which cuts out all the organisation/user joins and just joins

auth.users

to

auth.refresh_tokens

select 
  count(distinct auth.users.email)
from auth.users
  inner join auth.refresh_tokens on auth.refresh_tokens.user_id::uuid = auth.users.id
where auth.refresh_tokens.created_at > (current_timestamp - interval '1 months')

You might be asking why I'm joining to

refresh_tokens

and not just using

auth.users.last_sign_in_at

? Well I noticed that

auth.users.last_sign_in_at

is literally timestamping the last actual sign in and not updating for a returning user like I personally expected (for example, I have a user who was definitely active this month but

auth.users.last_sign_in_at

is showing a date in April. The only thing I could find to test if a user was effectively an MAU this month was to see if they had a refresh token created in the last month... Is this reliable and a good way of doing this? How does Supabase actually calculate an MAU? As an aside, is anyone else's MAU reporting in the dashboard under settings > usage showing zero MAUs this month? (I'm meaning to open an ticket)

https://github.com/supabase/gotrue/blob/42bb1e0310cd4407a49913ac392e4da1be6f4ccd/internal/metering/record.go#L10 I believe that is where Supabase logs a user access (called from token endpoint) but I don't know for sure. The can't use tables in the database to meter as you could change them... A Mau is a unique signed in user accessed the site during the month. So if you have a 1000 users and the all access that month (once or many times) it is 1000. If only 100 access then 100.

Ok, so in theory there's no reason I can't use database tables to meter the users as my clients can't manipulate them...

If you can figure out the pattern yes.

Oh that's a good shout, ta!

Hey! I don't think it's a wise idea to use a state table like

refresh_tokens

to derive metrics. We are very likely to change the table's schema which will break your queries. We don't provide many guarantees on the table's schema at this point, as the Auth product is being very actively worked on and some changes are inevitable. There's a table called

audit_log_entries

which is better suited for your use case, and probably less likely to be affected as it's not a table that derives state.

Amazing thank you, Terry said he'd circulated this 🙏🏻 Yeah I literally just need a safe way to acknowledge the user returning in a given month, so I'll take a look at this

@stojan will these changes impact supabase-js auth library?

@stojan does audit_log_entries ever get cleaned up? Mines a non-production project at the mo and I can see 12k+ records dating back to September when I spun the project up?

No this doesn't get cleaned up.

Also, as I can only join on

audit_log_entires.payload.actor_id

is this going to get expensive timewise? This works but it doesn't feel right...

select 
  count(distinct auth.users.email)
from auth.users
  inner join auth.audit_log_entries on (audit_log_entries.payload ->> 'actor_id')::uuid = auth.users.id
where auth.audit_log_entries.created_at > (current_timestamp - interval '1 months')

You can run an

EXPLAIN ANALYZE

on the query to see how long it will take.

As in, I assume I shouldn't be cleaning it up myself, I know the system currently doesn't, but hypothetically, should the system maybe do it? Obviously my current 12k of records isn't a problem but it's not hard to see this filling up to the point where using it becomes problematic? I dunno, hence just me chucking it out there 😅

I'm not sure how it would become problematic other than using up space in the database. The system wouldn't clean it up as its a good way to keep track of a user's auth actions over time.

Joining auth.users to auth.refresh_tokens Planning Time: 8.991 ms Execution Time: 21.304 ms Joining auth.users to auth.audit_log_entries (using payload json field) Planning Time: 10.035 ms Execution Time: 248.324 ms ^^^ I assume this is only going to get exponentially worse as audit_log_entires grows in size... I was hoping to avoid having to write my own means of logging and attributing a user to one of my organisations is all, but it feels like using audit_log_entires, while much safer, probably isn't an option either?

What information would this column contain?

It would be a timestamp of the last time a user basically triggers what you call an auth request (and thus count them as an MAU I presume)? All I'm trying to do is calculate which org in my product I need to attribute one of my MAUs to. I'm simplifying what I'm trying to do so I don't literally share my entire structure but basically I'm trying to:

select 
  count(distinct auth.users.email)
from auth.users
  inner join auth.audit_log_entries on (audit_log_entries.payload ->> 'actor_id')::uuid = auth.users.id
  inner join ... -- do some more joins which let me link users to organisations
where auth.audit_log_entries.created_at > (current_timestamp - interval '1 months') -- active in current rolling month
  and organisation.id = p_org_id -- function parameter;

Ideally the number I get back here will be the number of MAUs that I can attribute to a given organisation in my system

Wouldn't this be the same as

last_sign_in_at

in the

auth.users

table?

No because I've seen this is either incorrect or not working. Perhaps I need to open a ticket afterall? Currently my MAUs are not reporting anyway which I've been meaning to log with support But in short, my auth.users.last_sign_in_at hasn't updated for any user since 19th May, but I'm signed in right now, so I assumed this was only updating on actual signInwithOtp for example and not when I hit the auth endpoint

Yeah I think something might be out of sync there, you should definitely open a support issue.

Can you confirm then that auth.users.last_sign_in_at should update everytime a user effectively auths via their token or refresh token? If I open my app now and I'm already logged in and I pass auth, should that column update?

What does "I pass auth" mean in this instance? I don't think this column gets updated if you already have a refresh token that will refresh the access token. But I think the refresh process should update the

updated_at

column.

Ok so I just checked the user in question and their updated_at is yesterday. I've just opened my app and the user is logged in etc and updated_at hasn't updated to today's date but then my JWT expiry is quite high, so I assume this is updated only when a refresh token is actually used perhaps?

It is safe to clean this table up.

auth.audit_log_entries yeah?

Yeah, so you could just clean it up every 2 months and the query above should be a lot faster and gives you the data you are after.

Ok that's good to know!

The

audit_log_entries

just contain a lot more information which could be useful in certain circumstances.

Thanks!

@GaryLake We'll probably have something similar in

auth.sessions

on this, but can't guarantee yet