Via the API, I need to determine whether user ids have already confirmed their email address or not. Using DataExplorer I am querying the email_tokens table, but am confused about how to interpret multiple rows for a given user id. When there are multiple rows, N-1 of them have expired = true, but the row with expired = false doesn’t seem to be the current state of whether they have confirmed their email address. For example there are verified users whose email_tokens row with expired = false showing confirmed = false Probably going about this the wrong way. Please advise. Thank you!

If you have control over your system, then you might use a Discourse web hook to notify your system that the user is activated. But I’m pretty sure that the easy solution is You can check the active field in the user record. I’m pretty sure that it doesn’t get set until they’ve validated their email address. http://localhost:3000/admin/users/1/jay.json will give you a record like: { id: 1, username: "jay", name: "Jay User", avatar_template: "/user_avatar/localhost/jay/{size}/3_2.png", email: "jay@nowehre.invalid", secondary_emails: [ ], active: true, admin: true, moderator: false, last_seen_at: "2020-11-04T20:13:04.240Z", last_emailed_at: null, created_at: "2020-10-05T21:14:52.570Z", last_seen_age: 27.86773951, last_emailed_age: null, created_at_age: 2588319.53785305, trust_level: 1, ..... },

[image] ds.discourse: I need to determine whether user ids have already confirmed their email address or not. @michebs is it possible to track this using a Data Explorer query?

Hi David, When the user confirms the email, the confirmed column is updated, so by adding it as true to the filter, you will list only confirmed emails. Hope this helps. SELECT user, email FROM email_tokens WHERE expired = false AND confirmed = true

What are you trying to accomplish?

@michebs - Yes. This is the query that I first tried, but found there is a small subset of our users that are active users, but are not returned by this query. Yet looking at one of those users on their Admin Users page, their email is valid (is not flagged not verified ). @pfaffman - We use this data to drive one of the state transitions in the services we provide to our members both on the site. When a new member signs up and requests services, we want to first know that we can contact them to provide those services.

To show an example, with this query: SELECT e.user_id, e.expired, e.confirmed, e.email FROM email_tokens as e WHERE e.user_id = 33 Looking at one of our most active users returns: user_id,expired,confirmed,email 33,false,false,somebody@somewhere.com 33,true,false,somebody@somewhere.com 33,true,true,somebody@somewhere.com 33,true,false,somebody@somewhere.com 33,true,false,somebody@somewhere.com 33,true,false,somebody@somewhere.com 33,true,false,somebody@somewhere.com 33,true,false,somebody@somewhere.com 33,true,false,somebody@somewhere.com 33,true,false,somebody@somewhere.com 33,true,false,somebody@somewhere.com 33,true,false,somebody@somewhere.com 33,true,false,somebody@somewhere.com 33,true,true,somebody@somewhere.com The only non-expired row shows the email as non-confirmed.

Check user email confirmed via DataExplorer

Product Support

ds.discourse (David) November 4, 2020, 8:25pm 9

users.active looks like exactly what I need. Thank you!

Topic		Replies	Views
API Filter users by emails, including secondary emails Development rest-api	2	484	February 29, 2024
Pulling Emails via API Product Support rest-api	6	243	September 2, 2025
OAuth Email missing in data explorer Product Support	3	1278	August 10, 2017
Collating email IDs of all users from topic comments Product Support	1	373	May 9, 2022
Active user after changing email Development rest-api	1	654	March 29, 2019

Check user email confirmed via DataExplorer

Related topics