Changed session durations metric to classic histogram, added

last_activity to calculate monthly active users

8 files changed, 83 insertions, 17 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
+}
diff --git a/internal/server/api/api.go b/internal/server/api/api.go
index 5159186..6c602ea 100644
--- a/internal/server/api/api.go
+++ b/internal/server/api/api.go
@@ -11,6 +11,7 @@ import (
 	"strconv"
 	"strings"
 	"sync"
+	"time"
 
 	"github.com/kyren223/eko/internal/data"
 	"github.com/kyren223/eko/internal/packet"
@@ -1703,3 +1704,17 @@ func DeviceAnalytics(ctx context.Context, sess *session.Session, request *packet
 
 	return nil
 }
+
+func SetLastUserActivity(ctx context.Context, sess *session.Session) {
+	queries := data.New(db)
+	now := time.Now().UnixMilli()
+	err := queries.UpdateUserLastActivity(ctx, data.UpdateUserLastActivityParams{
+		LastActivity: &now,
+		ID:           sess.ID(),
+	})
+	if err != nil {
+		slog.ErrorContext(ctx, "database error", "error", err)
+	} else {
+		slog.DebugContext(ctx, "set user activity", "now", now)
+	}
+}
diff --git a/internal/server/api/migrations/20250718210459_users_last_activity.sql b/internal/server/api/migrations/20250718210459_users_last_activity.sql
new file mode 100644
index 0000000..2fbc2c8
--- /dev/null
+++ b/internal/server/api/migrations/20250718210459_users_last_activity.sql
@@ -0,0 +1,5 @@
+-- +goose Up
+ALTER TABLE users ADD COLUMN last_activity INTEGER;
+
+-- +goose Down
+ALTER TABLE users DROP COLUMN last_activity;
diff --git a/internal/server/metrics/metrics.go b/internal/server/metrics/metrics.go
index d465b6c..e317be6 100644
--- a/internal/server/metrics/metrics.go
+++ b/internal/server/metrics/metrics.go
@@ -56,9 +56,25 @@ var UsersActive = promauto.NewGauge(prometheus.GaugeOpts{
 	Help:      "The total number of active users",
 })
 
+const (
+	Minute = 60
+	Hour   = 60 * Minute
+	Day    = 24 * Hour
+)
+
 var SessionDuration = promauto.NewHistogramVec(prometheus.HistogramOpts{
-	Namespace:                   namespace,
-	Name:                        "session_duration_seconds",
-	Help:                        "The duration in seconds of an authenticated session",
-	NativeHistogramBucketFactor: 1.00271,
+	Namespace: namespace,
+	Name:      "session_duration_seconds",
+	Help:      "The duration in seconds of an authenticated session",
+	Buckets: []float64{
+		1, 30, 5 * Minute, 10 * Minute, 30 * Minute,
+		Hour, 2 * Hour, 3 * Hour, 4 * Hour,
+		5 * Hour, 6 * Hour, 7 * Hour, 8 * Hour,
+		10 * Hour, 12 * Hour, 14 * Hour, 16 * Hour,
+		18 * Hour, 20 * Hour, 22 * Hour, Day,
+		2 * Day, 7 * Day, 14 * Day, 28 * Day,
+	},
+	// NativeHistogramBucketFactor:     1.00271,
+	// NativeHistogramMaxBucketNumber:  100,
+	// NativeHistogramMinResetDuration: time.Hour,
 }, []string{"os", "arch", "term", "colorterm"})
diff --git a/internal/server/server.go b/internal/server/server.go
index d94dca6..f38a570 100644
--- a/internal/server/server.go
+++ b/internal/server/server.go
@@ -528,6 +528,9 @@ func processAuthenticatedRequests(ctx context.Context, sess *session.Session, re
 		response = nil
 	}
 
+	// TODO: add a timeout for this (even tho it should be super fast)
+	api.SetLastUserActivity(ctx, sess)
+
 	return response
 }
 
@@ -690,9 +693,11 @@ func (s *server) handleSessionMetrics(ctx context.Context, sess *session.Session
 		metrics.SessionDuration.WithLabelValues(
 			analytics.OS, analytics.Arch, analytics.Term, analytics.Colorterm,
 		).Observe(duration.Seconds())
+		slog.DebugContext(ctx, "observed session duration", "session_duration", duration.Seconds())
 	} else {
 		metrics.SessionDuration.WithLabelValues(
 			"", "", "", "",
 		).Observe(duration.Seconds())
+		slog.DebugContext(ctx, "observed session duration (empty)", "session_duration", duration.Seconds())
 	}
 }
diff --git a/query/users.sql b/query/users.sql
index d2a4a92..50ec9af 100644
--- a/query/users.sql
+++ b/query/users.sql
@@ -44,3 +44,6 @@ WHERE user_id = ?;
 -- name: GetUsersByIds :many
 SELECT * FROM users
 WHERE id IN (sqlc.slice('ids'));
+
+-- name: UpdateUserLastActivity :exec
+UPDATE users SET last_activity = ? WHERE id = ?;