https://supabase.com/ logo
Querying MAUs
g

GaryLake

05/25/2023, 4:31 PM
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)
g

garyaustin

05/25/2023, 4:57 PM
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.

https://cdn.discordapp.com/attachments/1111330747836543097/1111337215201660999/image.png

g

GaryLake

05/25/2023, 5:09 PM
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...
g

garyaustin

05/25/2023, 5:10 PM
If you can figure out the pattern yes.
Also remember Supabase can and will change tables in that schema. They changed the token table last year to work differently and some columns changed (if I recall correctly).
g

GaryLake

05/25/2023, 5:25 PM
Oh that's a good shout, ta!
s

stojan

05/25/2023, 5:58 PM
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.
(btw I'm on the Auth team)
g

GaryLake

05/25/2023, 6:53 PM
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
v

ven

05/25/2023, 7:08 PM
@stojan will these changes impact supabase-js auth library?
g

GaryLake

05/26/2023, 8:28 AM
@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?
s

silentworks

05/26/2023, 8:42 AM
No this doesn't get cleaned up.
g

GaryLake

05/26/2023, 8:42 AM
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')
And just chucking it out there, should it?
s

silentworks

05/26/2023, 8:45 AM
You can run an
EXPLAIN ANALYZE
on the query to see how long it will take.
As in should you clean it up or should the system be cleaning it up?
g

GaryLake

05/26/2023, 8:48 AM
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 😅
Or is it safe for me to clean up – I'm currently cleaning up my cron.job_run_details table myself...
s

silentworks

05/26/2023, 8:51 AM
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.
g

GaryLake

05/26/2023, 8:54 AM
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?
@stojan @silentworks if auth is very actively being worked on, any chance we could get a last_auth_request column on the auth.users table? 😇🙏🏻
Eitherway, thanks both for your time here, really appreciate the response
s

silentworks

05/26/2023, 9:00 AM
What information would this column contain?
g

GaryLake

05/26/2023, 9:04 AM
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
Ideally auth.users.last_auth_request would be the last time a user triggered whatever triggers what you call auth requests as per here:

https://cdn.discordapp.com/attachments/1111330747836543097/1111580852154023946/image.png

s

silentworks

05/26/2023, 9:08 AM
Wouldn't this be the same as
last_sign_in_at
in the
auth.users
table?
Ignore my last message, I just read your original post again.
I wonder if combining the
last_sign_in_at
with the
updated_at
column could do the trick as I think this column gets updated for returning users too.
g

GaryLake

05/26/2023, 9:12 AM
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

https://cdn.discordapp.com/attachments/1111330747836543097/1111582510887997500/image.png

s

silentworks

05/26/2023, 9:12 AM
Yeah I think something might be out of sync there, you should definitely open a support issue.
g

GaryLake

05/26/2023, 9:13 AM
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?
s

silentworks

05/26/2023, 9:16 AM
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.
Can you check what's the date on the
auth.users
updated_at
column for the user you said definitely logged in during that month but the
last_sign_in_at
column hasn't changed?
g

GaryLake

05/26/2023, 9:20 AM
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?
s

silentworks

05/26/2023, 9:20 AM
It is safe to clean this table up.
g

GaryLake

05/26/2023, 9:20 AM
auth.audit_log_entries yeah?
s

silentworks

05/26/2023, 9:21 AM
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.
g

GaryLake

05/26/2023, 9:22 AM
Ok that's good to know!
However, I do think the auth.users.updated_at could be safe to use now that you've pointed it out
Removes the nasty join too
s

silentworks

05/26/2023, 9:24 AM
The
audit_log_entries
just contain a lot more information which could be useful in certain circumstances.
Do note you should use this in combination with
last_sign_in_at
or
created_at
as you could have a user who has never signed in but their
updated_at
was set at the same time as their
created_at
.
g

GaryLake

05/26/2023, 9:29 AM
Thanks!
s

stojan

05/27/2023, 4:00 PM
@GaryLake We'll probably have something similar in
auth.sessions
on this, but can't guarantee yet