systemprompt_users/repository/user/
stats.rs

1//! User count aggregates by status and role.
2//!
3//! Copyright (c) systemprompt.io — Business Source License 1.1.
4//! See <https://systemprompt.io> for licensing details.
5
6use chrono::{DateTime, Utc};
7
8use crate::error::Result;
9use crate::models::{UserStats, UserStatus};
10use crate::repository::UserRepository;
11
12#[derive(sqlx::FromRow)]
13struct StatusCount {
14    status: Option<String>,
15    cnt: Option<i64>,
16}
17
18#[derive(sqlx::FromRow)]
19struct RoleCount {
20    role: Option<String>,
21    cnt: Option<i64>,
22}
23
24#[derive(sqlx::FromRow)]
25struct StatsRow {
26    total: Option<i64>,
27    created_24h: Option<i64>,
28    created_7d: Option<i64>,
29    created_30d: Option<i64>,
30    active: Option<i64>,
31    suspended: Option<i64>,
32    admins: Option<i64>,
33    anonymous: Option<i64>,
34    bots: Option<i64>,
35    oldest_user: Option<DateTime<Utc>>,
36    newest_user: Option<DateTime<Utc>>,
37}
38
39impl UserRepository {
40    pub async fn count_by_status(&self) -> Result<Vec<(String, i64)>> {
41        let deleted_status = UserStatus::Deleted.as_str();
42        let rows = sqlx::query_as!(
43            StatusCount,
44            r#"
45            SELECT status, COUNT(*)::bigint as cnt
46            FROM users
47            WHERE status != $1
48            GROUP BY status
49            ORDER BY cnt DESC
50            "#,
51            deleted_status
52        )
53        .fetch_all(&*self.pool)
54        .await?;
55
56        Ok(rows
57            .into_iter()
58            .filter_map(|r| Some((r.status?, r.cnt?)))
59            .collect())
60    }
61
62    pub async fn count_by_role(&self) -> Result<Vec<(String, i64)>> {
63        let deleted_status = UserStatus::Deleted.as_str();
64        let rows = sqlx::query_as!(
65            RoleCount,
66            r#"
67            SELECT role, COUNT(*)::bigint as cnt
68            FROM users, UNNEST(roles) as role
69            WHERE status != $1
70            GROUP BY role
71            ORDER BY cnt DESC
72            "#,
73            deleted_status
74        )
75        .fetch_all(&*self.pool)
76        .await?;
77
78        Ok(rows
79            .into_iter()
80            .filter_map(|r| Some((r.role?, r.cnt?)))
81            .collect())
82    }
83
84    pub async fn get_stats(&self) -> Result<UserStats> {
85        let deleted_status = UserStatus::Deleted.as_str();
86        let row = sqlx::query_as!(
87            StatsRow,
88            r#"
89            SELECT
90                COUNT(*)::bigint as total,
91                COUNT(*) FILTER (WHERE created_at >= NOW() - INTERVAL '24 hours')::bigint as created_24h,
92                COUNT(*) FILTER (WHERE created_at >= NOW() - INTERVAL '7 days')::bigint as created_7d,
93                COUNT(*) FILTER (WHERE created_at >= NOW() - INTERVAL '30 days')::bigint as created_30d,
94                COUNT(*) FILTER (WHERE status = 'active')::bigint as active,
95                COUNT(*) FILTER (WHERE status = 'suspended')::bigint as suspended,
96                COUNT(*) FILTER (WHERE 'admin' = ANY(roles))::bigint as admins,
97                COUNT(*) FILTER (WHERE 'anonymous' = ANY(roles))::bigint as anonymous,
98                COUNT(*) FILTER (WHERE is_bot = true)::bigint as bots,
99                MIN(created_at) as oldest_user,
100                MAX(created_at) as newest_user
101            FROM users
102            WHERE status != $1
103            "#,
104            deleted_status
105        )
106        .fetch_one(&*self.pool)
107        .await?;
108
109        Ok(UserStats {
110            total: row.total.unwrap_or(0),
111            created_24h: row.created_24h.unwrap_or(0),
112            created_7d: row.created_7d.unwrap_or(0),
113            created_30d: row.created_30d.unwrap_or(0),
114            active: row.active.unwrap_or(0),
115            suspended: row.suspended.unwrap_or(0),
116            admins: row.admins.unwrap_or(0),
117            anonymous: row.anonymous.unwrap_or(0),
118            bots: row.bots.unwrap_or(0),
119            oldest_user: row.oldest_user,
120            newest_user: row.newest_user,
121        })
122    }
123}
systemprompt_users/repository/user/stats.rs

systemprompt_users/repository/user/
stats.rs
