Changed session durations metric to classic histogram, added

last_activity to calculate monthly active users

3 files changed, 35 insertions, 13 deletions

diff --git a/internal/data/members.sql.go b/internal/data/members.sql.go
index 018d6a5..4ffd6ce 100644
--- a/internal/data/members.sql.go
+++ b/internal/data/members.sql.go
@@ -58,7 +58,7 @@ func (q *Queries) FilterUsersInNetwork(ctx context.Context, arg FilterUsersInNet
 
 const getBannedMembers = `-- name: GetBannedMembers :many
 SELECT
-  users.id, users.name, users.public_key, users.description, users.is_public_dm, users.is_deleted,
+  users.id, users.name, users.public_key, users.description, users.is_public_dm, users.is_deleted, users.last_activity,
   members.user_id, members.network_id, members.joined_at, members.is_member, members.is_admin, members.is_muted, members.is_banned, members.ban_reason
 FROM members
 JOIN users ON users.id = members.user_id
@@ -86,6 +86,7 @@ func (q *Queries) GetBannedMembers(ctx context.Context, networkID snowflake.ID)
 			&i.User.Description,
 			&i.User.IsPublicDM,
 			&i.User.IsDeleted,
+			&i.User.LastActivity,
 			&i.Member.UserID,
 			&i.Member.NetworkID,
 			&i.Member.JoinedAt,
@@ -136,7 +137,7 @@ func (q *Queries) GetMemberById(ctx context.Context, arg GetMemberByIdParams) (M
 
 const getNetworkMembers = `-- name: GetNetworkMembers :many
 SELECT
-  users.id, users.name, users.public_key, users.description, users.is_public_dm, users.is_deleted,
+  users.id, users.name, users.public_key, users.description, users.is_public_dm, users.is_deleted, users.last_activity,
   members.user_id, members.network_id, members.joined_at, members.is_member, members.is_admin, members.is_muted, members.is_banned, members.ban_reason
 FROM members
 JOIN users ON users.id = members.user_id
@@ -164,6 +165,7 @@ func (q *Queries) GetNetworkMembers(ctx context.Context, networkID snowflake.ID)
 			&i.User.Description,
 			&i.User.IsPublicDM,
 			&i.User.IsDeleted,
+			&i.User.LastActivity,
 			&i.Member.UserID,
 			&i.Member.NetworkID,
 			&i.Member.JoinedAt,
diff --git a/internal/data/models.go b/internal/data/models.go
index f052fcd..47a7e9d 100644
--- a/internal/data/models.go
+++ b/internal/data/models.go
@@ -75,12 +75,13 @@ type TrustedUser struct {
 }
 
 type User struct {
-	ID          snowflake.ID
-	Name        string
-	PublicKey   ed25519.PublicKey
-	Description string
-	IsPublicDM  bool
-	IsDeleted   bool
+	ID           snowflake.ID
+	Name         string
+	PublicKey    ed25519.PublicKey
+	Description  string
+	IsPublicDM   bool
+	IsDeleted    bool
+	LastActivity *int64
 }
 
 type UserData struct {
diff --git a/internal/data/users.sql.go b/internal/data/users.sql.go
index 1ba8f9d..97d622e 100644
--- a/internal/data/users.sql.go
+++ b/internal/data/users.sql.go
@@ -19,7 +19,7 @@ INSERT INTO users (
 ) VALUES (
   ?, ?, ?
 )
-RETURNING id, name, public_key, description, is_public_dm, is_deleted
+RETURNING id, name, public_key, description, is_public_dm, is_deleted, last_activity
 `
 
 type CreateUserParams struct {
@@ -38,6 +38,7 @@ func (q *Queries) CreateUser(ctx context.Context, arg CreateUserParams) (User, e
 		&i.Description,
 		&i.IsPublicDM,
 		&i.IsDeleted,
+		&i.LastActivity,
 	)
 	return i, err
 }
@@ -54,7 +55,7 @@ func (q *Queries) DeleteUser(ctx context.Context, id snowflake.ID) error {
 }
 
 const getUserById = `-- name: GetUserById :one
-SELECT id, name, public_key, description, is_public_dm, is_deleted FROM users
+SELECT id, name, public_key, description, is_public_dm, is_deleted, last_activity FROM users
 WHERE id = ? AND is_deleted = false
 `
 
@@ -68,12 +69,13 @@ func (q *Queries) GetUserById(ctx context.Context, id snowflake.ID) (User, error
 		&i.Description,
 		&i.IsPublicDM,
 		&i.IsDeleted,
+		&i.LastActivity,
 	)
 	return i, err
 }
 
 const getUserByPublicKey = `-- name: GetUserByPublicKey :one
-SELECT id, name, public_key, description, is_public_dm, is_deleted FROM users
+SELECT id, name, public_key, description, is_public_dm, is_deleted, last_activity FROM users
 WHERE public_key = ?
 `
 
@@ -87,6 +89,7 @@ func (q *Queries) GetUserByPublicKey(ctx context.Context, publicKey ed25519.Publ
 		&i.Description,
 		&i.IsPublicDM,
 		&i.IsDeleted,
+		&i.LastActivity,
 	)
 	return i, err
 }
@@ -104,7 +107,7 @@ func (q *Queries) GetUserData(ctx context.Context, userID snowflake.ID) (string,
 }
 
 const getUsersByIds = `-- name: GetUsersByIds :many
-SELECT id, name, public_key, description, is_public_dm, is_deleted FROM users
+SELECT id, name, public_key, description, is_public_dm, is_deleted, last_activity FROM users
 WHERE id IN (/*SLICE:ids*/?)
 `
 
@@ -134,6 +137,7 @@ func (q *Queries) GetUsersByIds(ctx context.Context, ids []snowflake.ID) ([]User
 			&i.Description,
 			&i.IsPublicDM,
 			&i.IsDeleted,
+			&i.LastActivity,
 		); err != nil {
 			return nil, err
 		}
@@ -177,7 +181,7 @@ const updateUser = `-- name: UpdateUser :one
 UPDATE users SET
   name = ?, description = ?, is_public_dm = ?
 WHERE id = ?
-RETURNING id, name, public_key, description, is_public_dm, is_deleted
+RETURNING id, name, public_key, description, is_public_dm, is_deleted, last_activity
 `
 
 type UpdateUserParams struct {
@@ -202,6 +206,21 @@ func (q *Queries) UpdateUser(ctx context.Context, arg UpdateUserParams) (User, e
 		&i.Description,
 		&i.IsPublicDM,
 		&i.IsDeleted,
+		&i.LastActivity,
 	)
 	return i, err
 }
+
+const updateUserLastActivity = `-- name: UpdateUserLastActivity :exec
+UPDATE users SET last_activity = ? WHERE id = ?
+`
+
+type UpdateUserLastActivityParams struct {
+	LastActivity *int64
+	ID           snowflake.ID
+}
+
+func (q *Queries) UpdateUserLastActivity(ctx context.Context, arg UpdateUserLastActivityParams) error {
+	_, err := q.db.ExecContext(ctx, updateUserLastActivity, arg.LastActivity, arg.ID)
+	return err
+}