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, UserRow, 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            UserRow,
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        .map(User::try_from)
29        .transpose()?;
30
31        Ok(row)
32    }
33
34    pub async fn find_by_email(&self, email: &str) -> Result<Option<User>> {
35        let email = crate::models::normalise_email(email);
36        let deleted_status = UserStatus::Deleted.as_str();
37        let row = sqlx::query_as!(
38            UserRow,
39            r#"
40            SELECT id, name, email, full_name, display_name, status, email_verified,
41                   roles, avatar_url, is_bot, is_scanner, created_at, updated_at
42            FROM users
43            WHERE email = $1 AND status != $2
44            "#,
45            email,
46            deleted_status
47        )
48        .fetch_optional(&*self.pool)
49        .await?
50        .map(User::try_from)
51        .transpose()?;
52
53        Ok(row)
54    }
55
56    pub async fn find_by_name(&self, name: &str) -> Result<Option<User>> {
57        let deleted_status = UserStatus::Deleted.as_str();
58        let row = sqlx::query_as!(
59            UserRow,
60            r#"
61            SELECT id, name, email, full_name, display_name, status, email_verified,
62                   roles, avatar_url, is_bot, is_scanner, created_at, updated_at
63            FROM users
64            WHERE name = $1 AND status != $2
65            ORDER BY created_at ASC
66            LIMIT 1
67            "#,
68            name,
69            deleted_status
70        )
71        .fetch_optional(&*self.pool)
72        .await?
73        .map(User::try_from)
74        .transpose()?;
75
76        Ok(row)
77    }
78
79    pub async fn list_by_role(&self, role: UserRole) -> Result<Vec<User>> {
80        let deleted_status = UserStatus::Deleted.as_str();
81        let rows = sqlx::query_as!(
82            UserRow,
83            r#"
84            SELECT id, name, email, full_name, display_name, status, email_verified,
85                   roles, avatar_url, is_bot, is_scanner, created_at, updated_at
86            FROM users
87            WHERE $1 = ANY(roles) AND status != $2
88            ORDER BY created_at DESC
89            "#,
90            role.as_str(),
91            deleted_status
92        )
93        .fetch_all(&*self.pool)
94        .await?
95        .into_iter()
96        .map(User::try_from)
97        .collect::<Result<Vec<_>>>()?;
98
99        Ok(rows)
100    }
101
102    pub async fn find_first_user(&self) -> Result<Option<User>> {
103        let deleted_status = UserStatus::Deleted.as_str();
104        let row = sqlx::query_as!(
105            UserRow,
106            r#"
107            SELECT id, name, email, full_name, display_name, status, email_verified,
108                   roles, avatar_url, is_bot, is_scanner, created_at, updated_at
109            FROM users
110            WHERE status != $1
111            ORDER BY created_at ASC
112            LIMIT 1
113            "#,
114            deleted_status
115        )
116        .fetch_optional(&*self.pool)
117        .await?
118        .map(User::try_from)
119        .transpose()?;
120
121        Ok(row)
122    }
123
124    pub async fn find_first_admin(&self) -> Result<Option<User>> {
125        let deleted_status = UserStatus::Deleted.as_str();
126        let admin_role = UserRole::Admin.as_str();
127        let row = sqlx::query_as!(
128            UserRow,
129            r#"
130            SELECT id, name, email, full_name, display_name, status, email_verified,
131                   roles, avatar_url, is_bot, is_scanner, created_at, updated_at
132            FROM users
133            WHERE $1 = ANY(roles) AND status != $2
134            ORDER BY created_at ASC
135            LIMIT 1
136            "#,
137            admin_role,
138            deleted_status
139        )
140        .fetch_optional(&*self.pool)
141        .await?
142        .map(User::try_from)
143        .transpose()?;
144
145        Ok(row)
146    }
147
148    pub async fn find_authenticated_user(&self, user_id: &UserId) -> Result<Option<User>> {
149        let active_status = UserStatus::Active.as_str();
150        let row = sqlx::query_as!(
151            UserRow,
152            r#"
153            SELECT id, name, email, full_name, display_name, status, email_verified,
154                   roles, avatar_url, is_bot, is_scanner, created_at, updated_at
155            FROM users
156            WHERE id = $1 AND status = $2
157            "#,
158            user_id.as_str(),
159            active_status
160        )
161        .fetch_optional(&*self.pool)
162        .await?
163        .map(User::try_from)
164        .transpose()?;
165
166        Ok(row)
167    }
168}
169
170impl UserRepository {
171    pub async fn missing_ids(&self, candidates: &[UserId]) -> Result<Vec<UserId>> {
172        let ids: Vec<String> = candidates.iter().map(ToString::to_string).collect();
173        let rows = sqlx::query_scalar!(
174            r#"
175            SELECT candidate AS "candidate!"
176            FROM UNNEST($1::text[]) AS candidate
177            WHERE NOT EXISTS (SELECT 1 FROM users WHERE users.id = candidate)
178            "#,
179            &ids[..]
180        )
181        .fetch_all(&*self.pool)
182        .await?;
183        Ok(rows.into_iter().map(UserId::new).collect())
184    }
185}
systemprompt_users/repository/user/find.rs

systemprompt_users/repository/user/
find.rs
