fix linting error with cargo fmt

):

apps/frontend/src/components/ui/project-settings/disclosures/form.ts

@@ -47,7 +47,7 @@ function createLockStatuses(
 	) as Record<DisclosureType, DisclosureLockStatus>
 
 	for (const disclosure of disclosures) {
-		lockStatuses[disclosure.type] = disclosure.lock_status
+		lockStatuses[disclosure.type] = disclosure.lock_status ?? 'unlocked'
 	}
 
 	return lockStatuses

apps/labrinth/.sqlx/query-0d01a3991e7551a8b7936bf8f4cc1760d2e89af99dd71849eda35d6c6820aa43.json

@@ -1,23 +0,0 @@
-{
-  "db_name": "PostgreSQL",
-  "query": "WITH random_id_point AS (\n            SELECT POINT(RANDOM() * ((SELECT MAX(id) FROM mods) - (SELECT MIN(id) FROM mods) + 1) + (SELECT MIN(id) FROM mods), 0) AS point\n        )\n        SELECT id FROM mods\n        WHERE status = ANY($1)\n        ORDER BY POINT(id, 0) <-> (SELECT point FROM random_id_point)\n        LIMIT $2",
-  "describe": {
-    "columns": [
-      {
-        "ordinal": 0,
-        "name": "id",
-        "type_info": "Int8"
-      }
-    ],
-    "parameters": {
-      "Left": [
-        "TextArray",
-        "Int8"
-      ]
-    },
-    "nullable": [
-      false
-    ]
-  },
-  "hash": "0d01a3991e7551a8b7936bf8f4cc1760d2e89af99dd71849eda35d6c6820aa43"
-}

apps/labrinth/.sqlx/query-20da3e21ce6115bd80746be3f6e7273771aed45eea03e46c23ef74a0a59ecfe3.json

@@ -0,0 +1,23 @@
+{
+  "db_name": "PostgreSQL",
+  "query": "WITH random_id_point AS (\n                SELECT POINT(RANDOM() * ((SELECT MAX(id) FROM mods) - (SELECT MIN(id) FROM mods) + 1) + (SELECT MIN(id) FROM mods), 0) AS point\n            )\n            SELECT id FROM mods\n            WHERE status = ANY($1)\n            ORDER BY POINT(id, 0) <-> (SELECT point FROM random_id_point)\n            LIMIT $2",
+  "describe": {
+    "columns": [
+      {
+        "ordinal": 0,
+        "name": "id",
+        "type_info": "Int8"
+      }
+    ],
+    "parameters": {
+      "Left": [
+        "TextArray",
+        "Int8"
+      ]
+    },
+    "nullable": [
+      false
+    ]
+  },
+  "hash": "20da3e21ce6115bd80746be3f6e7273771aed45eea03e46c23ef74a0a59ecfe3"
+}

apps/labrinth/.sqlx/query-c97315540d36355668a1fdd33175b946cd026c718bf4c7d16b23953f6ec5b840.json

@@ -0,0 +1,24 @@
+{
+  "db_name": "PostgreSQL",
+  "query": "WITH random_id_point AS (\n                SELECT POINT(RANDOM() * ((SELECT MAX(id) FROM mods) - (SELECT MIN(id) FROM mods) + 1) + (SELECT MIN(id) FROM mods), 0) AS point\n            )\n            SELECT id FROM mods\n            WHERE status = ANY($1)\n            AND EXISTS (\n                SELECT 1 FROM versions v\n                INNER JOIN loaders_versions lv ON v.id = lv.version_id\n                INNER JOIN loaders_project_types lpt ON lpt.joining_loader_id = lv.loader_id\n                INNER JOIN project_types pt ON pt.id = lpt.joining_project_type_id\n                WHERE v.mod_id = mods.id AND pt.name = $3\n                -- prevents decorrelation, so this stops at the first match instead\n                -- of scanning all versions before the outer sort/limit applies\n                OFFSET 0\n            )\n            ORDER BY POINT(id, 0) <-> (SELECT point FROM random_id_point)\n            LIMIT $2",
+  "describe": {
+    "columns": [
+      {
+        "ordinal": 0,
+        "name": "id",
+        "type_info": "Int8"
+      }
+    ],
+    "parameters": {
+      "Left": [
+        "TextArray",
+        "Int8",
+        "Text"
+      ]
+    },
+    "nullable": [
+      false
+    ]
+  },
+  "hash": "c97315540d36355668a1fdd33175b946cd026c718bf4c7d16b23953f6ec5b840"
+}

apps/labrinth/src/database/models/organization_item.rs

@@ -206,6 +206,31 @@ impl DBOrganization {
         }
     }
 
+    pub async fn get_projects<'a, E>(
+        organization_id: DBOrganizationId,
+        exec: E,
+    ) -> Result<Vec<DBProjectId>, super::DatabaseError>
+    where
+        E: crate::database::Executor<'a, Database = sqlx::Postgres>,
+    {
+        use futures::TryStreamExt;
+
+        let db_projects = sqlx::query!(
+            "
+            SELECT m.id FROM organizations o
+            INNER JOIN mods m ON m.organization_id = o.id
+            WHERE o.id = $1
+            ",
+            organization_id as DBOrganizationId,
+        )
+        .fetch(exec)
+        .map_ok(|m| DBProjectId(m.id))
+        .try_collect::<Vec<_>>()
+        .await?;
+
+        Ok(db_projects)
+    }
+
     pub async fn remove(
         id: DBOrganizationId,
         transaction: &mut PgTransaction<'_>,

apps/labrinth/src/models/v3/disclosures.rs

@@ -111,8 +111,10 @@ impl DisclosureLockStatus {
 pub struct ProjectDisclosureData {
     #[serde(flatten)]
     pub disclosure: ProjectDisclosure,
-    pub set_by_moderator: bool,
-    pub lock_status: DisclosureLockStatus,
+    #[serde(skip_serializing_if = "Option::is_none")]
+    pub set_by_moderator: Option<bool>,
+    #[serde(skip_serializing_if = "Option::is_none")]
+    pub lock_status: Option<DisclosureLockStatus>,
     pub updated_at: DateTime<Utc>,
     #[serde(skip_serializing_if = "Option::is_none")]
     pub updated_by: Option<UserId>,
@@ -124,16 +126,18 @@ impl ProjectDisclosureData {
     pub fn from_db(
         value: DBProjectDisclosure,
         viewer_is_moderator: bool,
+        viewer_is_member: bool,
     ) -> Self {
-        let updated_by = (!value.set_by_moderator || viewer_is_moderator)
-            .then_some(value.updated_by.into());
-
         Self {
             disclosure: value.disclosure,
-            set_by_moderator: value.set_by_moderator,
-            lock_status: value.lock_status,
+            set_by_moderator: (viewer_is_member || viewer_is_moderator)
+                .then_some(value.set_by_moderator),
+            lock_status: (viewer_is_member || viewer_is_moderator)
+                .then_some(value.lock_status),
             updated_at: value.updated_at,
-            updated_by,
+            updated_by: ((!value.set_by_moderator && viewer_is_member)
+                || viewer_is_moderator)
+                .then_some(value.updated_by.into()),
             deleted_at: value.deleted_at,
         }
     }

apps/labrinth/src/models/v3/projects.rs

@@ -444,7 +444,15 @@ impl From<LinkUrl> for Link {
 /// Scheduled - Project is scheduled to be released in the future
 /// Private - Project is approved, but is not viewable to the public
 #[derive(
-    Serialize, Deserialize, Copy, Clone, Eq, PartialEq, Debug, utoipa::ToSchema,
+    Serialize,
+    Deserialize,
+    Copy,
+    Clone,
+    Eq,
+    PartialEq,
+    Hash,
+    Debug,
+    utoipa::ToSchema,
 )]
 #[serde(rename_all = "lowercase")]
 pub enum ProjectStatus {

apps/labrinth/src/routes/internal/moderation/mod.rs

@@ -2,12 +2,16 @@ use super::ApiError;
 use crate::auth::get_user_from_headers;
 use crate::database;
 use crate::database::PgPool;
-use crate::database::models::DBModerationLock;
 use crate::database::models::moderation_external_item;
+use crate::database::models::{
+    DBModerationLock, DBOrganization, DBOrganizationId, DBProject, DBProjectId,
+};
 use crate::models::ids::{OrganizationId, ProjectId};
 use crate::models::projects::{ProjectStatus, VersionStatus};
 use crate::queue::moderation::{ApprovalType, IdentifiedFile, MissingMetadata};
 use crate::queue::session::AuthQueue;
+use crate::routes::v3::organizations::OrganizationIds;
+use crate::routes::v3::users::UserIds;
 use crate::util::error::ApiContext as _;
 use crate::util::error::Context;
 use crate::{
@@ -18,6 +22,7 @@ use actix_web::{HttpRequest, delete, get, post, web};
 use ariadne::ids::{UserId, random_base62};
 use chrono::{DateTime, Utc};
 use eyre::eyre;
+use futures_util::future::try_join_all;
 use serde::{Deserialize, Serialize};
 use std::collections::HashMap;
 use xredis::RedisPool;
@@ -37,6 +42,10 @@ pub fn config(cfg: &mut actix_web::web::ServiceConfig) {
         .service(release_lock)
         .service(release_lock_beacon)
         .service(delete_all_locks)
+        .service(get_user_project_grouped)
+        .service(get_users_project_grouped)
+        .service(get_organization_project_grouped)
+        .service(get_organizations_project_grouped)
         .service(web::scope("/tech-review").configure(tech_review::config))
         .service(
             web::scope("/external-license").configure(external_license::config),
@@ -215,7 +224,7 @@ pub struct DeleteAllLocksResponse {
     pub deleted_count: u64,
 }
 
-/// List projects in the moderation queue.  
+/// List projects in the moderation queue.
 #[utoipa::path(
 	context_path = "/moderation",
 	tag = "moderation",
@@ -1063,7 +1072,7 @@ fn row_to_ownership(
     })
 }
 
-/// Get project moderation metadata.  
+/// Get project moderation metadata.
 #[utoipa::path(
 	context_path = "/moderation",
 	tag = "moderation",
@@ -1226,7 +1235,7 @@ pub enum Judgement {
     },
 }
 
-/// Update project moderation judgements.  
+/// Update project moderation judgements.
 #[utoipa::path(
 	context_path = "/moderation",
 	tag = "moderation",
@@ -1328,7 +1337,7 @@ pub async fn set_project_meta(
     Ok(())
 }
 
-/// Acquire a moderation lock.  
+/// Acquire a moderation lock.
 /// Returns success if acquired, or info about who holds the lock if blocked.
 #[utoipa::path(
 	context_path = "/moderation",
@@ -1393,7 +1402,7 @@ pub async fn acquire_lock(
     }
 }
 
-/// Override a moderation lock.  
+/// Override a moderation lock.
 #[utoipa::path(
 	context_path = "/moderation",
 	tag = "moderation",
@@ -1444,7 +1453,7 @@ pub async fn override_lock(
     }))
 }
 
-/// Get moderation lock status.  
+/// Get moderation lock status.
 #[utoipa::path(
 	context_path = "/moderation",
 	tag = "moderation",
@@ -1511,7 +1520,7 @@ pub async fn get_lock_status(
     }
 }
 
-/// Release a moderation lock.  
+/// Release a moderation lock.
 #[utoipa::path(
 	context_path = "/moderation",
 	tag = "moderation",
@@ -1557,7 +1566,7 @@ pub async fn release_lock(
     Ok(web::Json(LockReleaseResponse { success: released }))
 }
 
-/// Release a moderation lock by beacon.  
+/// Release a moderation lock by beacon.
 ///
 /// For use with `navigator.sendBeacon`, which cannot set `Authorization` or send `DELETE`.
 /// The body must be `text/plain` containing the same token value as the `Authorization` header
@@ -1633,7 +1642,7 @@ pub async fn release_lock_beacon(
     Ok(web::Json(LockReleaseResponse { success: released }))
 }
 
-/// Delete all moderation locks.  
+/// Delete all moderation locks.
 #[utoipa::path(
 	context_path = "/moderation",
 	tag = "moderation",
@@ -1672,3 +1681,287 @@ pub async fn delete_all_locks(
 
     Ok(web::Json(DeleteAllLocksResponse { deleted_count }))
 }
+
+/// Get project id's for a given user with them grouped by their `ProjectStatus`.
+///
+/// Only statuses with at least one project are present in the map.
+#[utoipa::path(
+    context_path = "/moderation",
+    tag = "moderation",
+    security(("bearer_auth" = [])),
+    responses((status = OK, body = HashMap<ProjectStatus, Vec<ProjectId>>))
+)]
+#[get("/user/{user_id}/all-projects-grouped")]
+pub async fn get_user_project_grouped(
+    req: HttpRequest,
+    info: web::Path<(String,)>,
+    pool: web::Data<PgPool>,
+    redis: web::Data<RedisPool>,
+    session_queue: web::Data<AuthQueue>,
+) -> Result<web::Json<HashMap<ProjectStatus, Vec<ProjectId>>>, ApiError> {
+    check_is_moderator_from_headers(
+        &req,
+        &**pool,
+        &redis,
+        &session_queue,
+        Scopes::PROJECT_READ,
+    )
+    .await
+    .wrap_auth_err("authenticating API request")?;
+
+    let target_user =
+        database::models::DBUser::get(&info.into_inner().0, &**pool, &redis)
+            .await
+            .wrap_internal_err("fetching user from database")?
+            .wrap_not_found_err("resource not found")?;
+
+    let counts =
+        user_projects_status_grouped(target_user.id, &**pool, &redis).await?;
+
+    Ok(web::Json(counts))
+}
+
+/// Get project id's for a list of user's with them grouped by their `ProjectStatus`.
+///
+/// Users that don't exist are silently omitted from the response; users
+/// that exist but have no projects are included with an empty map.
+#[utoipa::path(
+    context_path = "/moderation",
+    tag = "moderation",
+    security(("bearer_auth" = [])),
+    params(("ids" = String, Query)),
+    responses((status = OK, body = HashMap<UserId, HashMap<ProjectStatus, Vec<ProjectId>>>))
+)]
+#[get("/users/all-projects-grouped")]
+pub async fn get_users_project_grouped(
+    req: HttpRequest,
+    ids: web::Query<UserIds>,
+    pool: web::Data<PgPool>,
+    redis: web::Data<RedisPool>,
+    session_queue: web::Data<AuthQueue>,
+) -> Result<
+    web::Json<HashMap<UserId, HashMap<ProjectStatus, Vec<ProjectId>>>>,
+    ApiError,
+> {
+    check_is_moderator_from_headers(
+        &req,
+        &**pool,
+        &redis,
+        &session_queue,
+        Scopes::PROJECT_READ,
+    )
+    .await
+    .wrap_auth_err("authenticating API request")?;
+
+    let user_ids = serde_json::from_str::<Vec<String>>(&ids.ids)
+        .wrap_request_err("deserializing JSON data")?;
+
+    if user_ids.is_empty() {
+        return Ok(web::Json(HashMap::new()));
+    }
+
+    let target_users =
+        database::models::DBUser::get_many(&user_ids, &**pool, &redis)
+            .await
+            .wrap_internal_err("fetching users from database")?;
+
+    let pool_ref = &**pool;
+    let redis_ref = &*redis;
+
+    let grouped_projects_by_user =
+        try_join_all(target_users.into_iter().map(|target_user| async move {
+            let counts = user_projects_status_grouped(
+                target_user.id,
+                pool_ref,
+                redis_ref,
+            )
+            .await?;
+
+            Ok::<_, ApiError>((UserId::from(target_user.id), counts))
+        }))
+        .await?
+        .into_iter()
+        .collect::<HashMap<_, _>>();
+
+    Ok(web::Json(grouped_projects_by_user))
+}
+
+/// Get project id's for a given organization with them grouped by their `ProjectStatus`.
+///
+/// Only statuses with at least one project are present in the map.
+#[utoipa::path(
+    context_path = "/moderation",
+    tag = "moderation",
+    security(("bearer_auth" = [])),
+    responses((status = OK, body = HashMap<ProjectStatus, Vec<ProjectId>>))
+)]
+#[get("/organization/{organization_id}/all-projects-grouped")]
+pub async fn get_organization_project_grouped(
+    req: HttpRequest,
+    info: web::Path<(String,)>,
+    pool: web::Data<PgPool>,
+    redis: web::Data<RedisPool>,
+    session_queue: web::Data<AuthQueue>,
+) -> Result<web::Json<HashMap<ProjectStatus, Vec<ProjectId>>>, ApiError> {
+    check_is_moderator_from_headers(
+        &req,
+        &**pool,
+        &redis,
+        &session_queue,
+        Scopes::PROJECT_READ,
+    )
+    .await
+    .wrap_auth_err("authenticating API request")?;
+
+    let target_org = database::models::DBOrganization::get(
+        &info.into_inner().0,
+        &**pool,
+        &redis,
+    )
+    .await
+    .wrap_internal_err("fetching organization from database")?
+    .wrap_not_found_err("resource not found")?;
+
+    let grouped_projects =
+        organization_projects_status_grouped(target_org.id, &**pool, &redis)
+            .await?;
+
+    Ok(web::Json(grouped_projects))
+}
+
+/// Get project id's for a list of organization's with them grouped by their `ProjectStatus`.
+///
+/// Organizations that don't exist are silently omitted from the
+/// response; organizations that exist but have no projects are included
+/// with an empty map.
+#[utoipa::path(
+    context_path = "/moderation",
+    tag = "moderation",
+    security(("bearer_auth" = [])),
+    params(("ids" = String, Query)),
+    responses((status = OK, body = HashMap<OrganizationId, HashMap<ProjectStatus, Vec<ProjectId>>>))
+)]
+#[get("/organizations/all-projects-grouped")]
+pub async fn get_organizations_project_grouped(
+    req: HttpRequest,
+    ids: web::Query<OrganizationIds>,
+    pool: web::Data<PgPool>,
+    redis: web::Data<RedisPool>,
+    session_queue: web::Data<AuthQueue>,
+) -> Result<
+    web::Json<HashMap<OrganizationId, HashMap<ProjectStatus, Vec<ProjectId>>>>,
+    ApiError,
+> {
+    check_is_moderator_from_headers(
+        &req,
+        &**pool,
+        &redis,
+        &session_queue,
+        Scopes::PROJECT_READ,
+    )
+    .await
+    .wrap_auth_err("authenticating API request")?;
+
+    let organization_ids = serde_json::from_str::<Vec<String>>(&ids.ids)
+        .wrap_request_err("deserializing JSON data")?;
+
+    if organization_ids.is_empty() {
+        return Ok(web::Json(HashMap::new()));
+    }
+
+    let target_orgs = database::models::DBOrganization::get_many(
+        &organization_ids,
+        &**pool,
+        &redis,
+    )
+    .await
+    .wrap_internal_err("fetching organizations from database")?;
+
+    let pool_ref = &**pool;
+    let redis_ref = &*redis;
+
+    let grouped_projects_by_org =
+        try_join_all(target_orgs.into_iter().map(|target_org| async move {
+            let counts = organization_projects_status_grouped(
+                target_org.id,
+                pool_ref,
+                redis_ref,
+            )
+            .await?;
+
+            Ok::<_, ApiError>((OrganizationId::from(target_org.id), counts))
+        }))
+        .await?
+        .into_iter()
+        .collect::<HashMap<_, _>>();
+
+    Ok(web::Json(grouped_projects_by_org))
+}
+
+/// Groups the given User projects by their `ProjectStatus`.
+async fn user_projects_status_grouped<'a, E>(
+    user_id: database::models::DBUserId,
+    pool: E,
+    redis: &RedisPool,
+) -> Result<HashMap<ProjectStatus, Vec<ProjectId>>, ApiError>
+where
+    E: database::Executor<'a, Database = sqlx::Postgres>
+        + database::Acquire<'a, Database = sqlx::Postgres>
+        + Copy,
+{
+    let project_ids =
+        database::models::DBUser::get_projects(user_id, pool, redis)
+            .await
+            .wrap_internal_err("fetching user's projects from database")?;
+
+    grouped_projects_for(&project_ids, pool, redis).await
+}
+
+/// Groups the given Organization projects by their `ProjectStatus`.
+async fn organization_projects_status_grouped<'a, E>(
+    organization_id: DBOrganizationId,
+    pool: E,
+    redis: &RedisPool,
+) -> Result<HashMap<ProjectStatus, Vec<ProjectId>>, ApiError>
+where
+    E: database::Executor<'a, Database = sqlx::Postgres>
+        + database::Acquire<'a, Database = sqlx::Postgres>
+        + Copy,
+{
+    let project_ids = DBOrganization::get_projects(organization_id, pool)
+        .await
+        .wrap_internal_err("fetching project IDs from database")?;
+
+    grouped_projects_for(&project_ids, pool, redis).await
+}
+
+/// Groups the given input Projects by their `ProjectStatus`.
+async fn grouped_projects_for<'a, E>(
+    project_ids: &[DBProjectId],
+    pool: E,
+    redis: &RedisPool,
+) -> Result<HashMap<ProjectStatus, Vec<ProjectId>>, ApiError>
+where
+    E: database::Executor<'a, Database = sqlx::Postgres>
+        + database::Acquire<'a, Database = sqlx::Postgres>
+        + Copy,
+{
+    if project_ids.is_empty() {
+        return Ok(HashMap::new());
+    }
+
+    let projects = DBProject::get_many_ids(project_ids, pool, redis)
+        .await
+        .wrap_internal_err("fetching projects from database")?;
+
+    let mut grouped_projects: HashMap<ProjectStatus, Vec<ProjectId>> =
+        HashMap::new();
+    for project in &projects {
+        grouped_projects
+            .entry(project.inner.status)
+            .or_default()
+            .push(project.inner.id.into());
+    }
+
+    Ok(grouped_projects)
+}

apps/labrinth/src/routes/internal/moderation/tech_review.rs

@@ -9,13 +9,17 @@ use itertools::Itertools;
 use serde::{Deserialize, Serialize};
 
 use super::ownership::get_projects_ownership;
+use crate::database::models::{DBOrganization, DBOrganizationId};
+use crate::models::ids::OrganizationId;
+use crate::routes::v3::organizations::OrganizationIds;
+use crate::routes::v3::users::UserIds;
 use crate::{
     auth::check_is_moderator_from_headers,
     database::{
         DBProject,
         models::{
-            DBFileId, DBProjectId, DBThread, DBThreadId, DBUser, DBVersion,
-            DBVersionId, DelphiReportId, DelphiReportIssueDetailsId,
+            DBFileId, DBProjectId, DBThread, DBThreadId, DBUser, DBUserId,
+            DBVersion, DBVersionId, DelphiReportId, DelphiReportIssueDetailsId,
             DelphiReportIssueId,
             delphi_report_item::{
                 DBDelphiReport, DelphiSeverity, DelphiStatus, DelphiVerdict,
@@ -26,7 +30,7 @@ use crate::{
         },
     },
     models::{
-        ids::{FileId, ProjectId, ThreadId, VersionId},
+        ids::{FileId, ProjectId, ThreadId, ThreadMessageId, VersionId},
         pats::Scopes,
         projects::{Project, ProjectStatus},
         threads::{MessageBody, Thread},
@@ -42,7 +46,9 @@ use crate::{
     search::SearchState,
     util::error::Context,
 };
+use ariadne::ids::UserId;
 use eyre::eyre;
+use futures_util::future::try_join_all;
 
 pub mod global;
 
@@ -55,7 +61,11 @@ pub fn config(cfg: &mut actix_web::web::ServiceConfig) {
         .service(submit_report)
         .service(update_issue_details)
         .service(update_global_issue_details)
-        .service(add_report);
+        .service(add_report)
+        .service(get_user_flagged_projects)
+        .service(get_users_flagged_projects)
+        .service(get_organization_flagged_projects)
+        .service(get_organizations_flagged_projects);
 }
 
 /// Arguments for searching project technical reviews.
@@ -204,7 +214,7 @@ pub enum FlagReason {
     Delphi,
 }
 
-/// Get a Delphi report issue.  
+/// Get a Delphi report issue.
 #[utoipa::path(
 	context_path = "/moderation/tech-review",
 	tag = "moderation",
@@ -268,7 +278,7 @@ pub async fn get_issue(
     Ok(web::Json(row.data.0))
 }
 
-/// Get a project technical report.  
+/// Get a project technical report.
 #[utoipa::path(
 	context_path = "/moderation/tech-review",
 	tag = "moderation",
@@ -684,7 +694,7 @@ async fn fetch_project_reports(
     Ok(project_reports)
 }
 
-/// Search projects awaiting technical review.  
+/// Search projects awaiting technical review.
 #[utoipa::path(
 	context_path = "/moderation/tech-review",
 	tag = "moderation",
@@ -896,7 +906,7 @@ pub async fn search_projects(
     }))
 }
 
-/// Get a project technical review report.  
+/// Get a project technical review report.
 #[utoipa::path(
 	context_path = "/moderation/tech-review",
 	tag = "moderation",
@@ -992,7 +1002,7 @@ pub struct SubmitReport {
     pub message: Option<String>,
 }
 
-/// Submit a technical review verdict.  
+/// Submit a technical review verdict.
 ///
 /// Before this is called, all issues for this project's reports must have been
 /// marked as either safe or unsafe. Otherwise, this will error with
@@ -1210,7 +1220,7 @@ pub struct UpdateGlobalIssue {
     pub verdict: DelphiStatus,
 }
 
-/// Update technical review issue details.  
+/// Update technical review issue details.
 ///
 /// This will not automatically reject the project for malware, but just flag
 /// this issue with a verdict.
@@ -1497,7 +1507,7 @@ pub struct AddReport {
     pub file_id: FileId,
 }
 
-/// Add a technical review report.  
+/// Add a technical review report.
 /// does not already exist for it.
 #[utoipa::path(
 	context_path = "/moderation/tech-review",
@@ -1566,3 +1576,331 @@ pub async fn add_report(
 
     Ok(web::Json(report_id))
 }
+
+/// A user's project that is stuck in `processing` or `rejected` because the
+/// most recent technical review verdict posted to its thread was `unsafe`.
+#[derive(Debug, Clone, Serialize, utoipa::ToSchema)]
+pub struct FlaggedProject {
+    pub project_id: ProjectId,
+    pub thread_id: ThreadId,
+    pub status: ProjectStatus,
+    /// The `tech_review` message that carried the `unsafe` verdict.
+    pub message_id: ThreadMessageId,
+    /// When that verdict was posted.
+    pub reviewed: DateTime<Utc>,
+}
+
+/// Get all of a user's `processing`/`rejected` projects whose most recent
+/// `tech_review` thread message was an `unsafe` verdict.
+#[utoipa::path(
+    context_path = "/moderation/tech-review",
+    tag = "moderation",
+    security(("bearer_auth" = [])),
+    responses((status = OK, body = Vec<FlaggedProject>))
+)]
+#[get("/user/{user_id}/flagged-projects")]
+pub async fn get_user_flagged_projects(
+    req: HttpRequest,
+    info: web::Path<(String,)>,
+    pool: web::Data<PgPool>,
+    redis: web::Data<RedisPool>,
+    session_queue: web::Data<AuthQueue>,
+) -> Result<web::Json<Vec<FlaggedProject>>, ApiError> {
+    check_is_moderator_from_headers(
+        &req,
+        &**pool,
+        &redis,
+        &session_queue,
+        Scopes::PROJECT_READ,
+    )
+    .await
+    .wrap_auth_err("authenticating API request")?;
+
+    let target_user = DBUser::get(&info.into_inner().0, &**pool, &redis)
+        .await
+        .wrap_internal_err("fetching user from database")?
+        .wrap_not_found_err("resource not found")?;
+
+    let flagged =
+        user_flagged_projects(target_user.id, &**pool, &redis).await?;
+
+    Ok(web::Json(flagged))
+}
+
+/// Get all of multiple users' `processing`/`rejected` projects whose most
+/// recent `tech_review` thread message was an `unsafe` verdict. Users that
+/// don't exist are silently omitted from the response; users that exist
+/// but have no matching projects are included with an empty list.
+#[utoipa::path(
+    context_path = "/moderation/tech-review",
+    tag = "moderation",
+    security(("bearer_auth" = [])),
+    params(("ids" = String, Query)),
+    responses((status = OK, body = HashMap<UserId, Vec<FlaggedProject>>))
+)]
+#[get("/users/flagged-projects")]
+pub async fn get_users_flagged_projects(
+    req: HttpRequest,
+    ids: web::Query<UserIds>,
+    pool: web::Data<PgPool>,
+    redis: web::Data<RedisPool>,
+    session_queue: web::Data<AuthQueue>,
+) -> Result<web::Json<HashMap<UserId, Vec<FlaggedProject>>>, ApiError> {
+    check_is_moderator_from_headers(
+        &req,
+        &**pool,
+        &redis,
+        &session_queue,
+        Scopes::PROJECT_READ,
+    )
+    .await
+    .wrap_auth_err("authenticating API request")?;
+
+    let user_ids = serde_json::from_str::<Vec<String>>(&ids.ids)
+        .wrap_request_err("deserializing JSON data")?;
+
+    if user_ids.is_empty() {
+        return Ok(web::Json(HashMap::new()));
+    }
+
+    let target_users = DBUser::get_many(&user_ids, &**pool, &redis)
+        .await
+        .wrap_internal_err("fetching users from database")?;
+
+    let pool_ref = &**pool;
+    let redis_ref = &*redis;
+
+    let flagged_by_user =
+        try_join_all(target_users.into_iter().map(|target_user| async move {
+            let flagged =
+                user_flagged_projects(target_user.id, pool_ref, redis_ref)
+                    .await?;
+
+            Ok::<_, ApiError>((UserId::from(target_user.id), flagged))
+        }))
+        .await?
+        .into_iter()
+        .collect::<HashMap<_, _>>();
+
+    Ok(web::Json(flagged_by_user))
+}
+
+/// Get all of an organization's `processing`/`rejected` projects whose most
+/// recent `tech_review` thread message was an `unsafe` verdict.
+#[utoipa::path(
+    context_path = "/moderation/tech-review",
+    tag = "moderation",
+    security(("bearer_auth" = [])),
+    responses((status = OK, body = Vec<FlaggedProject>))
+)]
+#[get("/organization/{organization_id}/flagged-projects")]
+pub async fn get_organization_flagged_projects(
+    req: HttpRequest,
+    info: web::Path<(String,)>,
+    pool: web::Data<PgPool>,
+    redis: web::Data<RedisPool>,
+    session_queue: web::Data<AuthQueue>,
+) -> Result<web::Json<Vec<FlaggedProject>>, ApiError> {
+    check_is_moderator_from_headers(
+        &req,
+        &**pool,
+        &redis,
+        &session_queue,
+        Scopes::PROJECT_READ,
+    )
+    .await
+    .wrap_auth_err("authenticating API request")?;
+
+    let target_org = DBOrganization::get(&info.into_inner().0, &**pool, &redis)
+        .await
+        .wrap_internal_err("fetching organization from database")?
+        .wrap_not_found_err("resource not found")?;
+
+    let flagged =
+        organization_flagged_projects(target_org.id, &**pool, &redis).await?;
+
+    Ok(web::Json(flagged))
+}
+
+/// Get all of multiple organizations' `processing`/`rejected` projects
+/// whose most recent `tech_review` thread message was an `unsafe` verdict.
+/// Organizations that don't exist are silently omitted from the response;
+/// organizations that exist but have no matching projects are included
+/// with an empty list.
+#[utoipa::path(
+    context_path = "/moderation/tech-review",
+    tag = "moderation",
+    security(("bearer_auth" = [])),
+    params(("ids" = String, Query)),
+    responses((status = OK, body = HashMap<OrganizationId, Vec<FlaggedProject>>))
+)]
+#[get("/organizations/flagged-projects")]
+pub async fn get_organizations_flagged_projects(
+    req: HttpRequest,
+    ids: web::Query<OrganizationIds>,
+    pool: web::Data<PgPool>,
+    redis: web::Data<RedisPool>,
+    session_queue: web::Data<AuthQueue>,
+) -> Result<web::Json<HashMap<OrganizationId, Vec<FlaggedProject>>>, ApiError> {
+    check_is_moderator_from_headers(
+        &req,
+        &**pool,
+        &redis,
+        &session_queue,
+        Scopes::PROJECT_READ,
+    )
+    .await
+    .wrap_auth_err("authenticating API request")?;
+
+    let organization_ids = serde_json::from_str::<Vec<String>>(&ids.ids)
+        .wrap_request_err("deserializing JSON data")?;
+
+    if organization_ids.is_empty() {
+        return Ok(web::Json(HashMap::new()));
+    }
+
+    let target_orgs =
+        DBOrganization::get_many(&organization_ids, &**pool, &redis)
+            .await
+            .wrap_internal_err("fetching organizations from database")?;
+
+    let pool_ref = &**pool;
+    let redis_ref = &*redis;
+
+    let flagged_by_org =
+        try_join_all(target_orgs.into_iter().map(|target_org| async move {
+            let flagged = organization_flagged_projects(
+                target_org.id,
+                pool_ref,
+                redis_ref,
+            )
+            .await?;
+
+            Ok::<_, ApiError>((OrganizationId::from(target_org.id), flagged))
+        }))
+        .await?
+        .into_iter()
+        .collect::<HashMap<_, _>>();
+
+    Ok(web::Json(flagged_by_org))
+}
+
+/// Finds a single user's `processing`/`rejected` projects whose most recent
+/// `tech_review` thread message was an `unsafe` verdict. Shared by the
+/// single- and multi-user flagged-projects endpoints.
+async fn user_flagged_projects<'a, E>(
+    user_id: DBUserId,
+    pool: E,
+    redis: &RedisPool,
+) -> Result<Vec<FlaggedProject>, ApiError>
+where
+    E: crate::database::Executor<'a, Database = sqlx::Postgres>
+        + crate::database::Acquire<'a, Database = sqlx::Postgres>
+        + Copy,
+{
+    let project_ids = DBUser::get_projects(user_id, pool, redis)
+        .await
+        .wrap_internal_err("fetching user's projects from database")?;
+
+    flagged_projects_among(&project_ids, pool, redis).await
+}
+
+/// Finds a single organization's `processing`/`rejected` projects whose
+/// most recent `tech_review` thread message was an `unsafe` verdict.
+/// Shared by the single- and multi-organization flagged-projects
+/// endpoints.
+async fn organization_flagged_projects<'a, E>(
+    organization_id: DBOrganizationId,
+    pool: E,
+    redis: &RedisPool,
+) -> Result<Vec<FlaggedProject>, ApiError>
+where
+    E: crate::database::Executor<'a, Database = sqlx::Postgres>
+        + crate::database::Acquire<'a, Database = sqlx::Postgres>
+        + Copy,
+{
+    let project_ids = DBOrganization::get_projects(organization_id, pool)
+        .await
+        .wrap_internal_err("fetching project IDs from database")?;
+
+    flagged_projects_among(&project_ids, pool, redis).await
+}
+
+/// Finds `processing`/`rejected` projects (from the given ids) whose most
+/// recent `tech_review` thread message was an `unsafe` verdict. Shared by
+/// the user- and organization-scoped flagged-projects helpers above.
+async fn flagged_projects_among<'a, E>(
+    project_ids: &[DBProjectId],
+    pool: E,
+    redis: &RedisPool,
+) -> Result<Vec<FlaggedProject>, ApiError>
+where
+    E: crate::database::Executor<'a, Database = sqlx::Postgres>
+        + crate::database::Acquire<'a, Database = sqlx::Postgres>
+        + Copy,
+{
+    if project_ids.is_empty() {
+        return Ok(Vec::new());
+    }
+
+    // Only `processing`/`rejected` projects are relevant, so narrow down
+    // before pulling any thread data.
+    let candidate_projects = DBProject::get_many_ids(project_ids, pool, redis)
+        .await
+        .wrap_internal_err("fetching projects from database")?
+        .into_iter()
+        .filter(|project| {
+            matches!(
+                project.inner.status,
+                ProjectStatus::Processing | ProjectStatus::Rejected
+            )
+        })
+        .collect::<Vec<_>>();
+
+    if candidate_projects.is_empty() {
+        return Ok(Vec::new());
+    }
+
+    let thread_ids = candidate_projects
+        .iter()
+        .map(|project| project.thread_id)
+        .collect::<Vec<_>>();
+
+    let threads = DBThread::get_many(&thread_ids, pool)
+        .await
+        .wrap_internal_err("fetching threads from database")?
+        .into_iter()
+        .map(|thread| (thread.id, thread))
+        .collect::<HashMap<_, _>>();
+
+    Ok(candidate_projects
+        .into_iter()
+        .filter_map(|project| {
+            let thread = threads.get(&project.thread_id)?;
+
+            // `DBThread::get_many` returns messages sorted oldest-first, so
+            // walking backwards finds the most recent `tech_review` entry
+            // regardless of what (if anything) was posted after it.
+            let last_review = thread.messages.iter().rev().find(|message| {
+                matches!(message.body, MessageBody::TechReview { .. })
+            })?;
+
+            let verdict = match &last_review.body {
+                MessageBody::TechReview { verdict } => *verdict,
+                _ => return None,
+            };
+
+            if verdict != DelphiVerdict::Unsafe {
+                return None;
+            }
+
+            Some(FlaggedProject {
+                project_id: project.inner.id.into(),
+                thread_id: project.thread_id.into(),
+                status: project.inner.status,
+                message_id: last_review.id.into(),
+                reviewed: last_review.created,
+            })
+        })
+        .collect())
+}

apps/labrinth/src/routes/v2/projects.rs

@@ -193,7 +193,10 @@ pub async fn random_projects_get(
     pool: web::Data<PgPool>,
     redis: web::Data<RedisPool>,
 ) -> Result<HttpResponse, ApiError> {
-    let count = v3::projects::RandomProjects { count: count.count };
+    let count = v3::projects::RandomProjects {
+        count: count.count,
+        project_type: None,
+    };
 
     let response = v3::projects::random_projects_get(
         web::Query(count),

apps/labrinth/src/routes/v3/disclosures.rs

@@ -73,14 +73,15 @@ pub async fn get_project_disclosures(
     let viewer_is_moderator =
         user_option.as_ref().is_some_and(|user| user.role.is_mod());
 
-    let include_deleted = viewer_is_moderator
+    // Moderators can see regardless of membership, short circuit to avoid extra db call
+    let viewer_is_member = viewer_is_moderator
         || is_team_member_project(&project.inner, &user_option, &pool)
             .await
             .wrap_internal_err("failed to check project team membership")?;
 
     let disclosures = db_models::DBProjectDisclosure::get_many_for_project(
         project.inner.id,
-        include_deleted,
+        viewer_is_moderator || viewer_is_member,
         &***ro_pool,
     )
     .await
@@ -90,7 +91,11 @@ pub async fn get_project_disclosures(
         disclosures: disclosures
             .into_iter()
             .map(|disclosure| {
-                ProjectDisclosureData::from_db(disclosure, viewer_is_moderator)
+                ProjectDisclosureData::from_db(
+                    disclosure,
+                    viewer_is_moderator,
+                    viewer_is_member,
+                )
             })
             .collect(),
     }))

apps/labrinth/src/routes/v3/projects.rs

@@ -44,6 +44,7 @@ use chrono::Utc;
 use eyre::eyre;
 use futures::TryStreamExt;
 use itertools::Itertools;
+use rand::seq::SliceRandom;
 use rust_decimal::Decimal;
 use serde::{Deserialize, Serialize};
 use validator::Validate;
@@ -104,11 +105,15 @@ pub async fn clear_project_cache_and_queue_search(
 pub struct RandomProjects {
     #[validate(range(min = 1, max = 100))]
     pub count: u32,
+    pub project_type: Option<String>,
 }
 
 #[utoipa::path(
 	tag = "projects",
-	params(("count" = u32, Query)),
+	params(
+		("count" = u32, Query),
+		("project_type" = Option<String>, Query),
+	),
 	responses((status = OK))
 )]
 #[get("/projects_random")]
@@ -120,37 +125,84 @@ pub async fn random_projects_get_route(
     random_projects_get(count, pool, redis).await
 }
 
+// Filtered candidates are sparser and unevenly spaced, so the nearest-point pick
+// tends to repeat; oversample a neighborhood and shuffle it down to counter that.
+const RANDOM_PROJECT_TYPE_OVERSAMPLE_FACTOR: u32 = 20;
+
 pub async fn random_projects_get(
-    web::Query(count): web::Query<RandomProjects>,
+    web::Query(params): web::Query<RandomProjects>,
     pool: web::Data<PgPool>,
     redis: web::Data<RedisPool>,
 ) -> Result<HttpResponse, ApiError> {
-    count
+    params
         .validate()
         .map_err(|err| eyre::eyre!(err))
         .wrap_request_err("validating request")?;
 
-    let project_ids = sqlx::query!(
-        // IDs are randomly generated (see the `generate_ids` macro), so fetching a
-        // number of mods nearest to a random point in the ID space is equivalent to
-        // random sampling
-        "WITH random_id_point AS (
-            SELECT POINT(RANDOM() * ((SELECT MAX(id) FROM mods) - (SELECT MIN(id) FROM mods) + 1) + (SELECT MIN(id) FROM mods), 0) AS point
+    let statuses = crate::models::projects::ProjectStatus::iterator()
+        .filter(|x| x.is_searchable())
+        .map(|x| x.to_string())
+        .collect::<Vec<String>>();
+
+    let mut project_ids = if let Some(project_type) = &params.project_type {
+        let fetch_limit = params.count * RANDOM_PROJECT_TYPE_OVERSAMPLE_FACTOR;
+
+        sqlx::query!(
+            // IDs are randomly generated (see the `generate_ids` macro), so fetching a
+            // number of mods nearest to a random point in the ID space is equivalent to
+            // random sampling
+            "WITH random_id_point AS (
+                SELECT POINT(RANDOM() * ((SELECT MAX(id) FROM mods) - (SELECT MIN(id) FROM mods) + 1) + (SELECT MIN(id) FROM mods), 0) AS point
+            )
+            SELECT id FROM mods
+            WHERE status = ANY($1)
+            AND EXISTS (
+                SELECT 1 FROM versions v
+                INNER JOIN loaders_versions lv ON v.id = lv.version_id
+                INNER JOIN loaders_project_types lpt ON lpt.joining_loader_id = lv.loader_id
+                INNER JOIN project_types pt ON pt.id = lpt.joining_project_type_id
+                WHERE v.mod_id = mods.id AND pt.name = $3
+                -- prevents decorrelation, so this stops at the first match instead
+                -- of scanning all versions before the outer sort/limit applies
+                OFFSET 0
+            )
+            ORDER BY POINT(id, 0) <-> (SELECT point FROM random_id_point)
+            LIMIT $2",
+            &statuses,
+            fetch_limit as i32,
+            project_type,
         )
-        SELECT id FROM mods
-        WHERE status = ANY($1)
-        ORDER BY POINT(id, 0) <-> (SELECT point FROM random_id_point)
-        LIMIT $2",
-        &*crate::models::projects::ProjectStatus::iterator()
-            .filter(|x| x.is_searchable())
-            .map(|x| x.to_string())
-            .collect::<Vec<String>>(),
-        count.count as i32,
-    )
-    .fetch(&**pool)
-    .map_ok(|m| db_ids::DBProjectId(m.id))
-    .try_collect::<Vec<_>>()
-    .await.wrap_internal_err("querying random project IDs")?;
+        .fetch(&**pool)
+        .map_ok(|m| db_ids::DBProjectId(m.id))
+        .try_collect::<Vec<_>>()
+        .await
+        .wrap_internal_err("querying random project IDs")?
+    } else {
+        sqlx::query!(
+            // IDs are randomly generated (see the `generate_ids` macro), so fetching a
+            // number of mods nearest to a random point in the ID space is equivalent to
+            // random sampling
+            "WITH random_id_point AS (
+                SELECT POINT(RANDOM() * ((SELECT MAX(id) FROM mods) - (SELECT MIN(id) FROM mods) + 1) + (SELECT MIN(id) FROM mods), 0) AS point
+            )
+            SELECT id FROM mods
+            WHERE status = ANY($1)
+            ORDER BY POINT(id, 0) <-> (SELECT point FROM random_id_point)
+            LIMIT $2",
+            &statuses,
+            params.count as i32,
+        )
+        .fetch(&**pool)
+        .map_ok(|m| db_ids::DBProjectId(m.id))
+        .try_collect::<Vec<_>>()
+        .await
+        .wrap_internal_err("querying random project IDs")?
+    };
+
+    if params.project_type.is_some() {
+        project_ids.shuffle(&mut rand::thread_rng());
+        project_ids.truncate(params.count as usize);
+    }
 
     let projects_data =
         db_models::DBProject::get_many_ids(&project_ids, &**pool, &redis)

packages/api-client/src/modules/labrinth/types.ts

@@ -1352,8 +1352,8 @@ export namespace Labrinth {
 				  }
 
 			export type ProjectDisclosureData = ProjectDisclosure & {
-				set_by_moderator: boolean
-				lock_status: DisclosureLockStatus
+				set_by_moderator?: boolean | null
+				lock_status?: DisclosureLockStatus | null
 				updated_at: string
 				updated_by?: string | null
 				deleted_at?: string | null

packages/app-lib/src/api/instance/shared/client.rs

@@ -823,7 +823,8 @@ pub(super) async fn send_bytes_request(
 ) -> crate::Result<reqwest::Response> {
     let base_url = service_base_url();
     let url = service_url(base_url, path);
-    send_bytes_request_to_url(operation, method, path, &url, body, state).await
+    send_bytes_request_to_url(operation, method, path, &url, body, None, state)
+        .await
 }
 
 pub(super) async fn send_bytes_request_to_url(
@@ -832,6 +833,7 @@ pub(super) async fn send_bytes_request_to_url(
     path: &str,
     url: &str,
     body: Vec<u8>,
+    file_sha512: Option<&str>,
     state: &State,
 ) -> crate::Result<reqwest::Response> {
     let service_origin = url::Url::parse(service_base_url())
@@ -872,18 +874,20 @@ pub(super) async fn send_bytes_request_to_url(
         "Sending shared instances API request"
     );
 
-    let response = shared_instances_client(url)
+    let mut request = shared_instances_client(url)
         .request(method.clone(), url)
         .bearer_auth(credentials.session)
         .header(reqwest::header::CONTENT_TYPE, "application/octet-stream")
-        .body(body)
-        .send()
-        .await
-        .map_err(|error| {
-            crate::ErrorKind::SharedInstancesApiError(
-                error.without_url().to_string(),
-            )
-        })?;
+        .body(body);
+    if let Some(file_sha512) = file_sha512 {
+        request = request.header("x-file-sha512", file_sha512);
+    }
+
+    let response = request.send().await.map_err(|error| {
+        crate::ErrorKind::SharedInstancesApiError(
+            error.without_url().to_string(),
+        )
+    })?;
 
     if response.status().is_success() {
         let request_id = response_request_id(&response);

packages/app-lib/src/api/instance/shared/publish.rs

@@ -6,6 +6,7 @@ use super::*;
 use async_walkdir::WalkDir;
 use async_zip::{Compression, ZipEntryBuilder};
 use futures::StreamExt;
+use sha2::Digest;
 use std::collections::BTreeMap;
 
 #[tracing::instrument]
@@ -1018,12 +1019,18 @@ pub(super) async fn upload_external_files(
                 "Invalid shared instance external file upload URL: {error}"
             ))
         })?;
+        let (bytes, file_sha512) = tokio::task::spawn_blocking(move || {
+            let hash = format!("{:x}", sha2::Sha512::digest(&bytes));
+            (bytes, hash)
+        })
+        .await?;
         let response = send_bytes_request_to_url(
             "upload_external_file",
             Method::PUT,
             upload_url.path(),
             &upload.url,
             bytes,
+            Some(&file_sha512),
             state,
         )
         .await?;