Firebase: Showing a users latest modified doc date in the user table (auth)

I have two tables. One which shows my firebase Auth information (all my users UIDs etc) as well as the useful last signed in and account created epoch time.

However, I have recently discovered that the last signed in is not that useful. What I actually want is the last time they were active as my users have stayed signed in for weeks at at time and is not a good indicator of use.

Now, Firebase doesn't seem to supply a last active time in the user table but in the docs list table I have, it does show me the last modified.

Ideally, what I'd like is to put a column in the user table which has the latest date modified for each user.

Unfortunately this means a few things that experience has shown is quite hard:

  1. link the user ID from table 1 (userTable) with the same user ID in table 2 (docTable).
  2. identify the most recent metadata.lastModified item.
  3. Put it with the corresponding user ID in table 1 (userTable).
  4. Convert it from epoch to todays date.

Individually so far I have managed to do most of these things but I'm stumped as to how to get it to work in it's entirety.

Also very open to hearing completely alternative solutions that don't involve this for me to be able to see who was last active when easily.

Some updates but still not there:

I have already put the following into a JS query which populates userTable with each user's total doc count.

I am thinking maybe I can use the same thing or something similar to build an array of each user's last active document's unix time and then convert it into a date.

The issue here is that there are two bits I don't yet know how to do. The first being adding more function to the query code below to also collect each user's document date modified number (maybe in additional scope?) and the second is to ensure that the unix time it's taken is only the most recent.

Code currently being used to generate the column of doc numbers.

const docs_per_user = => {
  let userID = user.uid
  return userDocList.trigger({
    additionalScope: {
      "userID": userID
  }).then((data)=>{return[userID, Object.keys(data).length]});

return Promise.all(docs_per_user);

Also, here is a screenshot of what I'm hoping to achieve for clarity.

Hey @arkonis!

I imagine the built-in moment library will be good for converting the date once you've grabbed it (e.g. moment(UNIX_TIME).format("DD MMM YYYY")). As far as grabbing the correct time goes, it looks like you're already pulling in the full list of documents for each user could you might try something like the lodash _.max function with a mapper:

_.max(Object.values(data).map(row => row.dateModified))

Let me know what you find!

@Kabirdas thank you.

Could I get a little more help dialling this in as there seem to be a fair number of moving parts.

My current understanding of how it's all working currently to get the doc number total for the userTable is like this:

  • I call on the enquiry 'getUser.user' and map. This is the Firebase Auth and only has details such as user Information and no document information.

  • I then call userDocList.trigger which pulls in all my firestore information.

  • I then align the userID which is the only similarity between the firebase auth and the firestore information and present each uid array linked doc.length into the userTable.

So to do the same, I would need to create a duplicate js query as the one above.

However, it's the placement of the _.max(Object.values(data).map(row => row.dateModified))
that I'm not sure about. Is this to go into this query somewhere or in a seperate query using the returned document information?

The way it is currently working with the other other similar item (total docs) is that it takes the returned promise of docs_per_user which I created here so I assume i'd need to make another variable here or an entirely different query?

I tried below but I'm not familiar enough to understand where _.max(Object.values(data).map(row => row.dateModified)) is meant to go. Not used to define the const/variable as this is the UID we'll need to pair them in the table, so maybe in additional scope or the then?

const most_recent_activity = => {
  let userID = user.uid
  return userDocList.trigger({
    additionalScope: {
      "userID": userID
  }).then((data)=>{return[userID, Object.keys(data).length]});

return Promise.all(most_recent_activity);

As for the moment function, that will work splendidly.

@Kabirdas I've also read through the article you linked. I think it's very useful and I've managed to get it to work (sort of) however, I've run into the issue that the column is in a data tree and not an array so I feel like I'd have to make an object or array of it first or something.

This is what I had in a text box value (just to learn about how -max works)

Last time active: {{ _.max([]) }}```

Have the solution! For anyone looking to do something similar:

The following is my query which both get's me the total doc number for each user (to be placed in the firebase Auth table of my users) and also gathers the most recent 'lastModified' epoch time and places it in a column on the firebase Auth table too. Only issue now is that sorting by something formatted as a date doesn't always seem to work and the way I did it before (moment.unix) didn't seem to like it this time either. So work around is to have a column with epoch time which retool is happy to sort and next to that it's date equivalent.

Here is the JS query:

  • is my firebase Auth query.
  • userDocList is my firestore document query.
const most_recent_active = => {
  let userID = user.uid
  return userDocList.trigger({
    additionalScope: {
      "userID": userID
    let dateModifiedList = => doc.dateModified);
    let most_recent_doc_modification = Math.max(...dateModifiedList);
    return [userID, most_recent_doc_modification, Object.keys(data).length]

return Promise.all(most_recent_active);

And here is a screenshot.

1 Like

Great that you were able to find a solution @arkonis!

Thanks for surfacing the date sorting issue as well. For now, can you try using a column mapper on the epoch column and disable "Sort by mapped value"?