coder · ammario · Sep 26, 2022 · Sep 23, 2022 · Sep 24, 2022 · Sep 24, 2022
diff --git a/coderd/database/databasefake/databasefake.go b/coderd/database/databasefake/databasefake.go
@@ -1956,6 +1956,22 @@ func (q *fakeQuerier) UpdateUserStatus(_ context.Context, arg database.UpdateUse
 	return database.User{}, sql.ErrNoRows
 }
 
+func (q *fakeQuerier) UpdateUserLastSeenAt(_ context.Context, arg database.UpdateUserLastSeenAtParams) (database.User, error) {
+	q.mutex.Lock()
+	defer q.mutex.Unlock()
+
+	for index, user := range q.users {
+		if user.ID != arg.ID {
+			continue
+		}
+		user.LastSeenAt = arg.LastSeenAt
+		user.UpdatedAt = arg.UpdatedAt
+		q.users[index] = user
+		return user, nil
+	}
+	return database.User{}, sql.ErrNoRows
+}
+
 func (q *fakeQuerier) UpdateUserHashedPassword(_ context.Context, arg database.UpdateUserHashedPasswordParams) error {
 	q.mutex.Lock()
 	defer q.mutex.Unlock()

diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000053_last_seen_at.down.sql b/coderd/database/migrations/000053_last_seen_at.down.sql
@@ -0,0 +1,2 @@
+ALTER TABLE ONLY users
+	DROP COLUMN last_seen_at;
diff --git a/coderd/database/migrations/000053_last_seen_at.up.sql b/coderd/database/migrations/000053_last_seen_at.up.sql
@@ -0,0 +1,2 @@
+ALTER TABLE ONLY users
+	ADD COLUMN last_seen_at timestamp NOT NULL DEFAULT '0001-01-01 00:00:00+00:00';
diff --git a/coderd/database/models.go b/coderd/database/models.go
diff --git a/coderd/database/querier.go b/coderd/database/querier.go
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/users.sql b/coderd/database/queries/users.sql
@@ -157,6 +157,15 @@ SET
 WHERE
 	id = $1 RETURNING *;
 
+-- name: UpdateUserLastSeenAt :one
+UPDATE
+	users
+SET
+	last_seen_at = $2,
+	updated_at = $3
+WHERE
+	id = $1 RETURNING *;
+
 
 -- name: GetAuthorizationUserRoles :one
 -- This function returns roles for authorization purposes. Implied member roles

diff --git a/coderd/httpmw/apikey.go b/coderd/httpmw/apikey.go
@@ -317,6 +317,22 @@ func ExtractAPIKey(cfg ExtractAPIKeyConfig) func(http.Handler) http.Handler {
 						return
 					}
 				}
+
+				// We only want to update this occasionally to reduce DB write
+				// load. We update alongside the UserLink and APIKey since it's
+				// easier on the DB to colocate writes.
+				_, err = cfg.DB.UpdateUserLastSeenAt(ctx, database.UpdateUserLastSeenAtParams{
+					ID:         key.UserID,
+					LastSeenAt: database.Now(),
+					UpdatedAt:  database.Now(),
+				})
+				if err != nil {
+					write(http.StatusInternalServerError, codersdk.Response{
+						Message: internalErrorMessage,
+						Detail:  fmt.Sprintf("update user last_seen_at: %s", err.Error()),
+					})
+					return
+				}
 			}
 
 			// If the key is valid, we also fetch the user roles and status.

diff --git a/coderd/users.go b/coderd/users.go
@@ -1210,6 +1210,7 @@ func convertUser(user database.User, organizationIDs []uuid.UUID) codersdk.User
 		ID:              user.ID,
 		Email:           user.Email,
 		CreatedAt:       user.CreatedAt,
+		LastSeenAt:      user.LastSeenAt,
 		Username:        user.Username,
 		Status:          codersdk.UserStatus(user.Status),
 		OrganizationIDs: organizationIDs,

diff --git a/coderd/users_test.go b/coderd/users_test.go
@@ -65,6 +65,35 @@ func TestFirstUser(t *testing.T) {
 		_ = coderdtest.CreateFirstUser(t, client)
 	})
 
+	t.Run("LastSeenAt", func(t *testing.T) {
+		t.Parallel()
+		ctx, cancel := context.WithTimeout(context.Background(), testutil.WaitLong)
+		defer cancel()
+
+		client := coderdtest.New(t, nil)
+		firstUserResp := coderdtest.CreateFirstUser(t, client)
+
+		firstUser, err := client.User(ctx, firstUserResp.UserID.String())
+		require.NoError(t, err)
+
+		_ = coderdtest.CreateAnotherUser(t, client, firstUserResp.OrganizationID)
+
+		allUsers, err := client.Users(ctx, codersdk.UsersRequest{})
+		require.NoError(t, err)
+
+		require.Len(t, allUsers, 2)
+
+		// We sent the "GET Users" request with the first user, but the second user
+		// should be Never since they haven't performed a request.
+		for _, user := range allUsers {
+			if user.ID == firstUser.ID {
+				require.WithinDuration(t, firstUser.LastSeenAt, database.Now(), testutil.WaitShort)
+			} else {
+				require.Zero(t, user.LastSeenAt)
+			}
+		}
+	})
+
 	t.Run("AutoImportsTemplates", func(t *testing.T) {
 		t.Parallel()
 

diff --git a/codersdk/users.go b/codersdk/users.go
@@ -43,10 +43,12 @@ type UsersRequest struct {
 
 // User represents a user in Coder.
 type User struct {
-	ID              uuid.UUID   `json:"id" validate:"required" table:"id"`
-	Username        string      `json:"username" validate:"required" table:"username"`
-	Email           string      `json:"email" validate:"required" table:"email"`
-	CreatedAt       time.Time   `json:"created_at" validate:"required" table:"created at"`
+	ID         uuid.UUID `json:"id" validate:"required" table:"id"`
+	Username   string    `json:"username" validate:"required" table:"username"`
+	Email      string    `json:"email" validate:"required" table:"email"`
+	CreatedAt  time.Time `json:"created_at" validate:"required" table:"created at"`
+	LastSeenAt time.Time `json:"last_seen_at"`
+
 	Status          UserStatus  `json:"status" table:"status"`
 	OrganizationIDs []uuid.UUID `json:"organization_ids"`
 	Roles           []Role      `json:"roles"`

diff --git a/enterprise/audit/table.go b/enterprise/audit/table.go
@@ -84,6 +84,7 @@ var AuditableResources = auditMap(map[any]map[string]Action{
 		"rbac_roles":      ActionTrack,
 		"login_type":      ActionIgnore,
 		"avatar_url":      ActionIgnore,
+		"last_seen_at":    ActionIgnore,
 		"deleted":         ActionTrack,
 	},
 	&database.Workspace{}: {

diff --git a/site/src/api/typesGenerated.ts b/site/src/api/typesGenerated.ts
@@ -510,6 +510,7 @@ export interface User {
   readonly username: string
   readonly email: string
   readonly created_at: string
+  readonly last_seen_at: string
   readonly status: UserStatus
   readonly organization_ids: string[]
   readonly roles: Role[]

diff --git a/...nts/WorkspacesTable/WorkspaceLastUsed.tsx → site/src/components/LastUsed/LastUsed.tsx b/...nts/WorkspacesTable/WorkspaceLastUsed.tsx → site/src/components/LastUsed/LastUsed.tsx
@@ -8,11 +8,11 @@ import { colors } from "theme/colors"
 
 dayjs.extend(relativeTime)
 
-interface WorkspaceLastUsedProps {
+interface LastUsedProps {
   lastUsedAt: string
 }
 
-export const WorkspaceLastUsed: FC<WorkspaceLastUsedProps> = ({ lastUsedAt }) => {
+export const LastUsed: FC<LastUsedProps> = ({ lastUsedAt }) => {
   const theme: Theme = useTheme()
   const styles = useStyles()
 

diff --git a/site/src/components/UsersTable/UsersTable.tsx b/site/src/components/UsersTable/UsersTable.tsx
@@ -14,6 +14,7 @@ export const Language = {
   usernameLabel: "User",
   rolesLabel: "Roles",
   statusLabel: "Status",
+  lastSeenLabel: "Last Seen",
 }
 
 export interface UsersTableProps {
@@ -50,6 +51,7 @@ export const UsersTable: FC<React.PropsWithChildren<UsersTableProps>> = ({
           <TableRow>
             <TableCell width="50%">{Language.usernameLabel}</TableCell>
             <TableCell width="25%">{Language.statusLabel}</TableCell>
+            <TableCell width="50%">{Language.lastSeenLabel}</TableCell>
             <TableCell width="25%">
               <Stack direction="row" spacing={1} alignItems="center">
                 <span>{Language.rolesLabel}</span>

diff --git a/site/src/components/UsersTable/UsersTableBody.tsx b/site/src/components/UsersTable/UsersTableBody.tsx
@@ -2,6 +2,7 @@ import Box from "@material-ui/core/Box"
 import { makeStyles } from "@material-ui/core/styles"
 import TableCell from "@material-ui/core/TableCell"
 import TableRow from "@material-ui/core/TableRow"
+import { LastUsed } from "components/LastUsed/LastUsed"
 import { FC } from "react"
 import * as TypesGen from "../../api/typesGenerated"
 import { combineClasses } from "../../util/combineClasses"
@@ -101,6 +102,9 @@ export const UsersTableBody: FC<React.PropsWithChildren<UsersTableBodyProps>> =
             >
               {user.status}
             </TableCell>
+            <TableCell>
+              <LastUsed lastUsedAt={user.last_seen_at} />
+            </TableCell>
             <TableCell>
               {canEditUsers ? (
                 <RoleSelect
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,2 @@
		ALTER TABLE ONLY users
		DROP COLUMN last_seen_at;
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,2 @@
		ALTER TABLE ONLY users
		ADD COLUMN last_seen_at timestamp NOT NULL DEFAULT '0001-01-01 00:00:00+00:00';