systemprompt_users/repository/user/
find.rs

1//! User lookup queries.
2//!
3//! Copyright (c) systemprompt.io — Business Source License 1.1.
4//! See <https://systemprompt.io> for licensing details.
5
6use systemprompt_identifiers::UserId;
7
8use crate::error::Result;
9use crate::models::{User, UserRole, UserStatus};
10use crate::repository::UserRepository;
11
12impl UserRepository {
13    pub async fn find_by_id(&self, id: &UserId) -> Result<Option<User>> {
14        let deleted_status = UserStatus::Deleted.as_str();
15        let row = sqlx::query_as!(
16            User,
17            r#"
18            SELECT id, name, email, full_name, display_name, status, email_verified,
19                   roles, avatar_url, is_bot, is_scanner, created_at, updated_at
20            FROM users
21            WHERE id = $1 AND status != $2
22            "#,
23            id.as_str(),
24            deleted_status
25        )
26        .fetch_optional(&*self.pool)
27        .await?;
28
29        Ok(row)
30    }
31
32    pub async fn find_by_email(&self, email: &str) -> Result<Option<User>> {
33        let email = crate::models::normalise_email(email);
34        let deleted_status = UserStatus::Deleted.as_str();
35        let row = sqlx::query_as!(
36            User,
37            r#"
38            SELECT id, name, email, full_name, display_name, status, email_verified,
39                   roles, avatar_url, is_bot, is_scanner, created_at, updated_at
40            FROM users
41            WHERE email = $1 AND status != $2
42            "#,
43            email,
44            deleted_status
45        )
46        .fetch_optional(&*self.pool)
47        .await?;
48
49        Ok(row)
50    }
51
52    pub async fn find_by_name(&self, name: &str) -> Result<Option<User>> {
53        let deleted_status = UserStatus::Deleted.as_str();
54        let row = sqlx::query_as!(
55            User,
56            r#"
57            SELECT id, name, email, full_name, display_name, status, email_verified,
58                   roles, avatar_url, is_bot, is_scanner, created_at, updated_at
59            FROM users
60            WHERE name = $1 AND status != $2
61            ORDER BY created_at ASC
62            LIMIT 1
63            "#,
64            name,
65            deleted_status
66        )
67        .fetch_optional(&*self.pool)
68        .await?;
69
70        Ok(row)
71    }
72
73    pub async fn find_by_role(&self, role: UserRole) -> Result<Vec<User>> {
74        let deleted_status = UserStatus::Deleted.as_str();
75        let rows = sqlx::query_as!(
76            User,
77            r#"
78            SELECT id, name, email, full_name, display_name, status, email_verified,
79                   roles, avatar_url, is_bot, is_scanner, created_at, updated_at
80            FROM users
81            WHERE $1 = ANY(roles) AND status != $2
82            ORDER BY created_at DESC
83            "#,
84            role.as_str(),
85            deleted_status
86        )
87        .fetch_all(&*self.pool)
88        .await?;
89
90        Ok(rows)
91    }
92
93    pub async fn find_first_user(&self) -> Result<Option<User>> {
94        let deleted_status = UserStatus::Deleted.as_str();
95        let row = sqlx::query_as!(
96            User,
97            r#"
98            SELECT id, name, email, full_name, display_name, status, email_verified,
99                   roles, avatar_url, is_bot, is_scanner, created_at, updated_at
100            FROM users
101            WHERE status != $1
102            ORDER BY created_at ASC
103            LIMIT 1
104            "#,
105            deleted_status
106        )
107        .fetch_optional(&*self.pool)
108        .await?;
109
110        Ok(row)
111    }
112
113    pub async fn find_first_admin(&self) -> Result<Option<User>> {
114        let deleted_status = UserStatus::Deleted.as_str();
115        let admin_role = UserRole::Admin.as_str();
116        let row = sqlx::query_as!(
117            User,
118            r#"
119            SELECT id, name, email, full_name, display_name, status, email_verified,
120                   roles, avatar_url, is_bot, is_scanner, created_at, updated_at
121            FROM users
122            WHERE $1 = ANY(roles) AND status != $2
123            ORDER BY created_at ASC
124            LIMIT 1
125            "#,
126            admin_role,
127            deleted_status
128        )
129        .fetch_optional(&*self.pool)
130        .await?;
131
132        Ok(row)
133    }
134
135    pub async fn find_authenticated_user(&self, user_id: &UserId) -> Result<Option<User>> {
136        let active_status = UserStatus::Active.as_str();
137        let row = sqlx::query_as!(
138            User,
139            r#"
140            SELECT id, name, email, full_name, display_name, status, email_verified,
141                   roles, avatar_url, is_bot, is_scanner, created_at, updated_at
142            FROM users
143            WHERE id = $1 AND status = $2
144            "#,
145            user_id.as_str(),
146            active_status
147        )
148        .fetch_optional(&*self.pool)
149        .await?;
150
151        Ok(row)
152    }
153}
154
155impl UserRepository {
156    pub async fn missing_ids(&self, candidates: &[UserId]) -> Result<Vec<UserId>> {
157        let ids: Vec<String> = candidates.iter().map(ToString::to_string).collect();
158        let rows = sqlx::query_scalar!(
159            r#"
160            SELECT candidate AS "candidate!"
161            FROM UNNEST($1::text[]) AS candidate
162            WHERE NOT EXISTS (SELECT 1 FROM users WHERE users.id = candidate)
163            "#,
164            &ids[..]
165        )
166        .fetch_all(&*self.pool)
167        .await?;
168        Ok(rows.into_iter().map(UserId::new).collect())
169    }
170}
systemprompt_users/repository/user/find.rs

systemprompt_users/repository/user/
find.rs
