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 ++++++++++++++++++++++++----- 3 files changed, 35 insertions(+), 13 deletions(-) (limited to 'internal/data') 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 +} -- cgit v1.3.1