Path: src/db/v1/queries/app.rs

• async fn list_apps
• async fn get_app_with_instances
• async fn count_apps
• async fn get_app_by_id
• async fn get_apps_by_org
• async fn create_app
• async fn update_app
• async fn delete_app
• async fn set_maintenance_mode

pub async fn list_apps(pool: &Pool<MySql>, page: i64, per_page: i64) -> anyhow::Result<Vec<AppWithInstanceCount>> {
    println!("Attempting to fetch apps with instance counts from database...");

Retrieves a paginated list of applications from the database. This function fetches a subset of applications based on pagination parameters, ordering them by their ID in ascending order. Pagination helps manage large datasets by retrieving only a specific "page" of results.

• pool - Database connection pool for executing the query
• page - Zero-based page number (e.g., 0 for first page, 1 for second page)
• per_page - Number of records to fetch per page

• Ok(Vec<App>) - Successfully retrieved list of applications
• Err(anyhow::Error) - Failed to fetch applications, with context

let apps = list_apps(&pool, 0, 10).await?; // Get first 10 apps

pub async fn get_app_with_instances(pool: &Pool<MySql>, app_id: i64) -> anyhow::Result<AppWithInstances> {
    // First fetch the app data
    let app = sqlx::query_as::<_, App>(
        "SELECT * FROM apps WHERE id = ?",
    )
    .bind(app_id)
    .fetch_one(pool)
    .await
    .context("Failed to fetch app")?;

Retrieves a specific application along with its associated instances. This function fetches an application by its ID and also retrieves all instances associated with that application. The results are combined into a single structure for easier access.

• pool - Database connection pool for executing the query
• app_id - Unique identifier of the application to retrieve

• Ok(AppWithInstances) - Successfully retrieved application and its instances
• Err(anyhow::Error) - Failed to fetch application or instances, with context

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

Counts the total number of applications in the database. This function retrieves the total count of applications, which can be useful for pagination or reporting purposes.

pub async fn get_app_by_id(pool: &Pool<MySql>, id: i64) -> anyhow::Result<App> {
    let app = sqlx::query_as::<_, App>("SELECT * FROM apps WHERE id = ?")
        .bind(id)
        .fetch_one(pool)
        .await
        .context("Failed to fetch app")?;

Retrieves a specific application by its unique identifier. This function fetches a single application record matching the provided ID. It's typically used for retrieving detailed information about a specific application.

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

• Ok(App) - Successfully retrieved application
• Err(anyhow::Error) - Failed to fetch application (including if not found)

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

pub async fn get_apps_by_org(pool: &Pool<MySql>, org_id: i64) -> anyhow::Result<Vec<App>> {
    let apps =
        sqlx::query_as::<_, App>("SELECT * FROM apps WHERE org_id = ? ORDER BY created_at DESC")
            .bind(org_id)
            .fetch_all(pool)
            .await
            .context("Failed to fetch org apps")?;

Retrieves all applications belonging to a specific organization. This function fetches all applications associated with the provided organization ID, ordered by creation date in descending order (newest first). It's typically used to display all applications owned by an organization.

• pool - Database connection pool for executing the query
• org_id - Organization identifier to filter applications by

• Ok(Vec<App>) - Successfully retrieved list of applications for the organization
• Err(anyhow::Error) - Failed to fetch applications

This function will return an empty vector if the organization exists but has no applications.

pub async fn create_app(
    pool: &Pool<MySql>,
    name: &str,
    org_id: i64,
    git_repo: Option<&str>,
    git_branch: Option<&str>,
    container_image_url: Option<&str>,
    region_id: Option<i64>,
) -> anyhow::Result<App> {
    // ... function body
}

Creates a new application in the database. This function inserts a new application record with the provided parameters. It handles both required fields (name, organization ID) and optional fields. The application is created with maintenance mode disabled by default.

• pool - Database connection pool for executing the query
• name - Name of the application
• org_id - Organization ID that the application belongs to
• git_repo - Optional URL of the Git repository for the application
• git_branch - Optional branch name in the Git repository
• container_image_url - Optional URL for a container image
• region_id - Optional ID of the deployment region

• Ok(App) - Successfully created application, including database-assigned fields
• Err(anyhow::Error) - Failed to create application

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_app(
    pool: &Pool<MySql>,
    id: i64,
    name: Option<&str>,
    git_repo: Option<&str>,
    git_branch: Option<&str>,
    container_image_url: Option<&str>,
    region_id: Option<i64>,
    maintenance_mode: Option<bool>,
) -> anyhow::Result<App> {
    // ... function body
}

Updates an existing application in the database. This function modifies an application record with the provided parameters. It uses a dynamic SQL query that only updates fields for which values are provided, leaving other fields unchanged. The updated_at timestamp is always updated to reflect the modification time.

• pool - Database connection pool for executing the query
• id - Unique identifier of the application to update
• name - Optional new name for the application
• git_repo - Optional new Git repository URL
• git_branch - Optional new Git branch
• container_image_url - Optional new container image URL
• region_id - Optional new region ID
• maintenance_mode - Optional new maintenance mode status

• Ok(App) - Successfully updated application with all current values
• Err(anyhow::Error) - Failed to update application

The function dynamically constructs the UPDATE SQL statement based on which parameters have values. This ensures the query only updates the fields that need to change, improving efficiency and reducing the risk of unintended changes.

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 delete_app(pool: &Pool<MySql>, id: i64) -> anyhow::Result<()> {
    let mut tx = pool.begin().await?;

Deletes an application from the database. This function permanently removes an application record with the specified ID. The operation is performed within a transaction to ensure data consistency.

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

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

This operation is irreversible. Once an application is deleted, all associated data that depends on the application's existence may become invalid.

This function does not verify if the application exists before attempting deletion. If the application does not exist, the operation will still succeed (as far as SQL is concerned), but no rows will be affected.

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

Sets the maintenance mode status for an application. This function updates only the maintenance_mode field of an application, making it a more efficient alternative to update_app when only this field needs to change. When an application is in maintenance mode, it typically displays a maintenance page to users instead of normal operation.

• pool - Database connection pool for executing the query
• id - Unique identifier of the application to update
• maintenance_mode - Whether maintenance mode should be enabled (true) or disabled (false)

• Ok(App) - Successfully updated application with the new maintenance mode status
• Err(anyhow::Error) - Failed to update maintenance mode

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.