From 24c0224dd90533bc962d5e1a198cdc464018176c Mon Sep 17 00:00:00 2001 From: Kyren223 Date: Sat, 19 Jul 2025 11:31:07 +0300 Subject: Changed session durations metric to classic histogram, added last_activity to calculate monthly active users --- internal/data/members.sql.go | 6 +++-- internal/data/models.go | 13 +++++----- internal/data/users.sql.go | 29 ++++++++++++++++++---- internal/server/api/api.go | 15 +++++++++++ .../20250718210459_users_last_activity.sql | 5 ++++ internal/server/metrics/metrics.go | 24 +++++++++++++++--- internal/server/server.go | 5 ++++ query/users.sql | 3 +++ 8 files changed, 83 insertions(+), 17 deletions(-) create mode 100644 internal/server/api/migrations/20250718210459_users_last_activity.sql 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 = ?; -- cgit v1.3.1