Path: src/schemas/v1/db/queries/user.rs

• async fn list_users
• async fn count_users
• async fn get_user_by_id
• async fn get_user_by_email
• async fn create_user
• async fn update_user
• async fn update_user_pii
• async fn update_user_meta
• async fn update_user_security
• async fn soft_delete_user
• async fn delete_user
• async fn record_login_attempt
• async fn login_user
• async fn create_session
• async fn invalidate_session
• async fn invalidate_all_user_sessions
• async fn get_user_sessions
• async fn is_session_valid
• async fn get_user_meta
• async fn get_user_pii

pub async fn list_users(
    pool: &Pool<MySql>,
    page: i64,
    per_page: i64,
) -> anyhow::Result<Vec<User>> {
    // ... function body
}

Retrieves all users in the system, ordered by creation time. This function fetches all user records from the database, with the most recently created users first. It provides a complete view of all users registered in the system.

• pool - Database connection pool for executing the query

• Ok(Vec<User>) - Successfully retrieved list of users
• Err(anyhow::Error) - Failed to fetch users

Common use cases include: - Administrative dashboards showing all system users - User management interfaces - User activity reports and analytics

pub async fn count_users(pool: &Pool<MySql>) -> anyhow::Result<i64> {
    let count = sqlx::query_scalar::<_, i64>("SELECT COUNT(*) FROM users WHERE deleted_at IS NULL")
        .fetch_one(pool)
        .await
        .context("Failed to count users")?;

Counts the total number of users in the system.

pub async fn get_user_by_id(pool: &Pool<MySql>, id: i64) -> anyhow::Result<User> {
    let user = sqlx::query_as::<_, User>("SELECT * FROM users WHERE id = ? AND deleted_at IS NULL")
        .bind(id)
        .fetch_one(pool)
        .await
        .context("Failed to fetch user")?;

Retrieves a specific user by their unique identifier. This function fetches detailed information about a single user record. It's typically used when specific user details are needed, such as for displaying user profiles or performing user-specific operations.

• pool - Database connection pool for executing the query
• id - Unique identifier of the user to retrieve

• Ok(User) - Successfully retrieved user information
• Err(anyhow::Error) - Failed to fetch user (including if not found)

Returns an error if no user with the given ID exists or if a database error occurs during the query execution.

pub async fn get_user_by_email(pool: &Pool<MySql>, email: &str) -> anyhow::Result<User> {
    let user = sqlx::query_as::<_, User>("SELECT * FROM users WHERE email = ? AND deleted_at IS NULL")
        .bind(email)
        .fetch_one(pool)
        .await
        .context("Failed to fetch user by email")?;

Retrieves a user by their email address. This function fetches a user record based on their email address, which serves as a unique identifier in many authentication and user management scenarios. It's commonly used during login processes and for email verification.

• pool - Database connection pool for executing the query
• email - Email address of the user to retrieve

• Ok(User) - Successfully retrieved user information
• Err(anyhow::Error) - Failed to fetch user (including if not found)

Returns an error if no user with the given email exists or if a database error occurs during the query execution.

pub async fn create_user(
    pool: &Pool<MySql>,
    email: &str,
    password: &str, // Should be pre-hashed
    salt: &str,
) -> anyhow::Result<User> {
    // ... function body
}

Creates a new user in the system. This function inserts a new user record with the provided information. New users are created with the 'pending' status by default.

• pool - Database connection pool for executing the query
• email - User's email address (must be unique)
• password - User's password (should be pre-hashed for security)
• salt - Cryptographic salt used in the password hashing process

• Ok(User) - Successfully created user record
• Err(anyhow::Error) - Failed to create user record

This function expects the password to be pre-hashed before being passed in. It does not perform any password hashing itself, as this is typically handled by a higher-level security service. Never pass plain text passwords to this function.

This function uses a database transaction to ensure atomicity of the operation. If any part of the operation fails, the entire operation is rolled back.

pub async fn update_user(
    pool: &Pool<MySql>,
    id: i64,
    email: Option<&str>,
    active: Option<bool>,
    status: Option<&str>,
) -> anyhow::Result<User> {
    // ... function body
}

Updates an existing user's information. This function modifies a user record with the provided information. It supports partial updates, allowing you to update only the fields that need changing.

• pool - Database connection pool for executing the query
• id - Unique identifier of the user to update
• email - Optional new email address for the user
• active - Optional new active status for the user
• status - Optional new status (active, deactivated, suspended, pending)

• Ok(User) - Successfully updated user record
• Err(anyhow::Error) - Failed to update user

This function dynamically builds an SQL query based on which parameters are provided. Only the fields specified with Some values will be updated, while None values are ignored.

This function uses a database transaction to ensure atomicity of the operation. If any part of the operation fails, the entire operation is rolled back.

This function does not support updating passwords. Password updates should be handled by a dedicated function with appropriate security measures.

pub async fn update_user_pii(
    pool: &Pool<MySql>,
    user_id: i64,
    first_name: Option<&str>,
    last_name: Option<&str>,
    full_name: Option<&str>,
) -> anyhow::Result<()> {
    // ... function body
}

Updates a user's personal information. This function updates the user's personal information in the user_pii table.

• pool - Database connection pool for executing the query
• user_id - Unique identifier of the user
• first_name - Optional new first name
• last_name - Optional new last name
• full_name - Optional new full name (can be generated if first and last are provided)

• Ok(()) - Successfully updated user PII
• Err(anyhow::Error) - Failed to update user PII

pub async fn update_user_meta(
    pool: &Pool<MySql>,
    user_id: i64,
    timezone: Option<&str>,
    language: Option<&str>,
    theme: Option<&str>,
    onboarding_completed: Option<bool>,
) -> anyhow::Result<()> {
    // ... function body
}

Updates a user's preferences and metadata. This function updates the user's preferences in the user_meta table.

• pool - Database connection pool for executing the query
• user_id - Unique identifier of the user
• timezone - Optional new timezone preference
• language - Optional new language preference
• theme - Optional new theme preference
• onboarding_completed - Optional flag indicating if onboarding is completed

• Ok(()) - Successfully updated user metadata
• Err(anyhow::Error) - Failed to update user metadata

pub async fn update_user_security(
    pool: &Pool<MySql>,
    id: i64,
    password: Option<&str>, 
    salt: Option<&str>,
    two_factor_enabled: Option<bool>,
    two_factor_verified: Option<bool>,
) -> anyhow::Result<User> {
    // ... function body
}

Updates a user's security settings. This function updates security-related fields in the users table.

• pool - Database connection pool for executing the query
• id - Unique identifier of the user
• password - Optional new password (should be pre-hashed)
• salt - Optional new salt (should be provided if password is)
• two_factor_enabled - Optional flag to enable/disable two-factor authentication
• two_factor_verified - Optional flag indicating 2FA verification status

• Ok(User) - Successfully updated user security settings
• Err(anyhow::Error) - Failed to update user security settings

pub async fn soft_delete_user(pool: &Pool<MySql>, id: i64) -> anyhow::Result<()> {
    let mut tx = pool.begin().await?;

Soft deletes a user from the system. This function marks a user as deleted by setting the deleted_at timestamp, but does not actually remove the record from the database.

• pool - Database connection pool for executing the query
• id - Unique identifier of the user to delete

• Ok(()) - Successfully marked the user as deleted
• Err(anyhow::Error) - Failed to mark the user as deleted

This operation is reversible by clearing the deleted_at field. The function preserves user data while making it inactive in the system.

pub async fn delete_user(pool: &Pool<MySql>, id: i64) -> anyhow::Result<()> {
    let mut tx = pool.begin().await?;

Hard deletes a user from the system. This function permanently removes a user record from the database. It should be used with caution, as it typically has significant implications for data integrity and user experience.

• pool - Database connection pool for executing the query
• id - Unique identifier of the user to delete

• Ok(()) - Successfully deleted the user
• Err(anyhow::Error) - Failed to delete the user

This operation is irreversible. Consider the implications before deleting users, especially those with existing data or relationships in the system. For many applications, it may be preferable to deactivate users by setting their 'active' flag to false or using soft_delete_user rather than deleting them completely.

Due to CASCADE DELETE constraints in the database, this will automatically delete all related records in user_meta, user_pii, user_sessions, and other tables with foreign key relationships to the user.

pub async fn record_login_attempt(
    pool: &Pool<MySql>, 
    id: i64, 
    successful: bool
) -> anyhow::Result<User> {
    // ... function body
}

Records a user login attempt. This function updates login-related fields like last_login_at and login_attempts. It's used during authentication to track login activity and manage security features like account lockouts after too many failed attempts.

• pool - Database connection pool for executing the query
• id - User ID
• successful - Whether the login attempt was successful

• Ok(User) - Updated user record
• Err(anyhow::Error) - Failed to update login information

pub async fn login_user(
    pool: &Pool<MySql>,
    email: &str,
    password: &str, // Should be pre-hashed
) -> anyhow::Result<User> {
    // ... function body
}

Authenticates a user using email and password. This function attempts to retrieve a user record with the provided email and hashed password combination. It's used during login processes to verify user credentials.

• pool - Database connection pool for executing the query
• email - Email address entered by the user
• password - Password entered by the user (should be pre-hashed)

• Ok(User) - Successfully authenticated user
• Err(anyhow::Error) - Authentication failed or user doesn't exist

This function expects the password to be pre-hashed before being passed in. It does not perform any password hashing itself, as this is typically handled by a higher-level security service that: 1. Retrieves the user and their salt using get_user_by_email 2. Uses the salt to hash the provided password 3. Calls this function with the properly hashed password

For security reasons, this function provides a generic error message regardless of whether the email wasn't found or the password was incorrect. This prevents information leakage about existing email addresses.

This function checks if the account is locked before attempting authentication.

pub async fn create_session(
    pool: &Pool<MySql>,
    user_id: i64,
    session_token: &str,
    refresh_token: Option<&str>,
    ip_address: &str,
    user_agent: &str,
    expires_at: chrono::DateTime<chrono::Utc>,
) -> anyhow::Result<i64> {
    // ... function body
}

Creates a new user session. This function creates a new session for a user after successful authentication.

• pool - Database connection pool for executing the query
• user_id - ID of the authenticated user
• session_token - Generated session token
• refresh_token - Optional refresh token
• ip_address - Client IP address
• user_agent - Client user agent string
• expires_at - Session expiration time

• Ok(i64) - ID of the created session
• Err(anyhow::Error) - Failed to create session

pub async fn invalidate_session(
    pool: &Pool<MySql>,
    session_token: &str,
) -> anyhow::Result<()> {
    // ... function body
}

Invalidates a user session. This function marks a session as inactive, effectively logging the user out.

• pool - Database connection pool for executing the query
• session_token - The session token to invalidate

• Ok(()) - Successfully invalidated the session
• Err(anyhow::Error) - Failed to invalidate the session

pub async fn invalidate_all_user_sessions(
    pool: &Pool<MySql>,
    user_id: i64,
) -> anyhow::Result<()> {
    // ... function body
}

Invalidates all sessions for a user. This function marks all of a user's sessions as inactive, effectively logging them out of all devices.

• pool - Database connection pool for executing the query
• user_id - The ID of the user whose sessions should be invalidated

• Ok(()) - Successfully invalidated all sessions
• Err(anyhow::Error) - Failed to invalidate sessions

pub async fn get_user_sessions(
    pool: &Pool<MySql>,
    user_id: i64,
) -> anyhow::Result<Vec<UserSession>> {
    // ... function body
}

Retrieves a list of all active sessions for a user.

pub async fn is_session_valid(
    pool: &Pool<MySql>,
    session_token: &str,
) -> anyhow::Result<bool> {
    // ... function body
}

Is session valid?

pub async fn get_user_meta(pool: &Pool<MySql>, user_id: i64) -> Result<UserMeta, anyhow::Error> {
    // First try to find existing meta
    let meta = sqlx::query_as::<_, UserMeta>(
        r#"
        SELECT 
            id, user_id, timezone, language, theme, 
            notification_preferences, profile_image, dashboard_layout, 
            onboarding_completed, created_at, updated_at
        FROM user_meta
        WHERE user_id = ?
        "#
    )
    .bind(user_id)
    .fetch_optional(pool)
    .await?;

Get user meta information

pub async fn get_user_pii(pool: &Pool<MySql>, user_id: i64) -> Result<UserPii, anyhow::Error> {
    // Try to find existing PII
    let pii = sqlx::query_as::<_, UserPii>(
        r#"
        SELECT 
            id, user_id, first_name, last_name, full_name, 
            identity_verified, identity_verification_date, 
            identity_verification_method, created_at, updated_at
        FROM user_pii
        WHERE user_id = ?
        "#
    )
    .bind(user_id)
    .fetch_optional(pool)
    .await?;

Get user PII (Personally Identifiable Information)