prepr

* Fetch project analytics events

* fix

* post-query ua bucketing

* fmt

.github/ISSUE_TEMPLATE/5-feature-request.yml

@@ -9,8 +9,6 @@ body:
       options:
         - label: I checked the [existing issues](https://github.com/modrinth/code/issues?q=is%3Aissue) for duplicate feature requests
           required: true
-        - label: I have checked that this feature request is not on our [roadmap](https://roadmap.modrinth.com)
-          required: true
   - type: dropdown
     id: projects
     attributes:

apps/frontend/src/components/ui/moderation/checklist/ModerationChecklist.vue

@@ -701,11 +701,11 @@ async function navigateToNextUnlockedProject(): Promise<boolean> {
 
 	// Quick re-check if close to expiry (last 5 seconds of TTL)
 	if (now - next.validatedAt > PREFETCH_STALE_MS - 5000) {
-		const recheck = await moderationQueue.checkLock(next.projectId)
-		if (recheck.locked && !recheck.expired) {
-			// Project got locked, remove from queue and try next
+		const recheckResults = await batchCheckQueueCandidates([next.projectId])
+		const recheck = recheckResults.get(next.projectId)
+		if (!isEligibleQueueCandidate(recheck)) {
 			prefetchQueue.value.shift()
-			return navigateToNextUnlockedProject() // Recurse to try next
+			return navigateToNextUnlockedProject()
 		}
 	}
 
@@ -717,33 +717,14 @@ async function navigateToNextUnlockedProject(): Promise<boolean> {
 		next.skippedIds.map((id) => moderationQueue.completeCurrentProject(id, 'skipped')),
 	)
 
-	if (next.skippedIds.length > 0) {
-		addNotification({
-			title: 'Skipped locked projects',
-			text: `Skipped ${next.skippedIds.length} project(s) being moderated by others.`,
-			type: 'info',
-		})
-	}
+	notifySkippedQueueProjects(next.skippedIds.length)
 
-	// Trigger prefetch replenishment in background (don't await)
 	maintainPrefetchQueue()
 
-	// Navigate to canonical URL if we have metadata (avoids middleware redirect)
-	if (next.slug && next.projectType) {
-		const urlType = getProjectTypeForUrlShorthand(next.projectType, [], tags.value)
-
-		navigateTo({
-			path: `/${urlType}/${next.slug}`,
-			state: { showChecklist: true },
-		})
-	} else {
-		// Fallback: use project ID (will trigger middleware redirect)
-		navigateTo({
-			name: 'type-project',
-			params: { type: 'project', project: next.projectId },
-			state: { showChecklist: true },
-		})
-	}
+	navigateToQueueProject(
+		{ slug: next.slug, projectType: next.projectType, locked: false, isProcessing: true },
+		next.projectId,
+	)
 	return true
 }
 
@@ -869,53 +850,107 @@ function reviewAnyway() {
 	maintainPrefetchQueue()
 }
 
-// Batch check locks and fetch project metadata in parallel
-interface LockCheckResult {
+// Batch check locks, processing status, and fetch project metadata in parallel
+interface QueueCandidateCheck {
 	locked: boolean
 	expired?: boolean
 	isOwnLock?: boolean
 	slug?: string
 	projectType?: string
+	status?: string
+	isProcessing: boolean
 }
 
-async function batchCheckLocksWithMetadata(
+function isEligibleQueueCandidate(result: QueueCandidateCheck | undefined): boolean {
+	if (!result?.isProcessing) return false
+	return !result.locked || !!result.expired || !!result.isOwnLock
+}
+
+function notifySkippedQueueProjects(count: number) {
+	if (count <= 0) return
+	addNotification({
+		title: 'Skipped projects',
+		text: `Skipped ${count} project(s) already moderated or locked by others.`,
+		type: 'info',
+	})
+}
+
+function navigateToQueueProject(result: QueueCandidateCheck, projectId: string) {
+	if (result.slug && result.projectType) {
+		const urlType = getProjectTypeForUrlShorthand(result.projectType, [], tags.value)
+		navigateTo({
+			path: `/${urlType}/${result.slug}`,
+			state: { showChecklist: true },
+		})
+	} else {
+		navigateTo({
+			name: 'type-project',
+			params: { type: 'project', project: projectId },
+			state: { showChecklist: true },
+		})
+	}
+}
+
+async function batchCheckQueueCandidates(
 	projectIds: string[],
-): Promise<Map<string, LockCheckResult>> {
-	const results = new Map<string, LockCheckResult>()
+): Promise<Map<string, QueueCandidateCheck>> {
+	const results = new Map<string, QueueCandidateCheck>()
 
-	// Check locks and fetch minimal project data in parallel
 	const checks = await Promise.allSettled(
 		projectIds.map(async (id) => {
-			// Parallel: check lock AND fetch project metadata
 			const [lockResponse, projectData] = await Promise.all([
 				moderationQueue.checkLock(id),
 				useBaseFetch(`project/${id}`, { method: 'GET' }).catch(() => null),
 			])
 
+			const status = (projectData as { status?: string } | null)?.status
+
 			return {
 				id,
 				locked: lockResponse.locked,
 				expired: lockResponse.expired,
 				isOwnLock: lockResponse.is_own_lock,
-				slug: (projectData as { slug?: string })?.slug,
-				projectType: (projectData as { project_type?: string })?.project_type,
+				slug: (projectData as { slug?: string } | null)?.slug,
+				projectType: (projectData as { project_type?: string } | null)?.project_type,
+				status,
+				isProcessing: projectData === null ? true : status === 'processing',
 			}
 		}),
 	)
 
-	// Use forEach with index to avoid indexOf bug on PromiseSettledResult
 	checks.forEach((result, index) => {
 		if (result.status === 'fulfilled') {
 			results.set(result.value.id, result.value)
 		} else {
-			// On error, mark as needing fallback (no metadata)
-			results.set(projectIds[index], { locked: false })
+			results.set(projectIds[index], { locked: false, isProcessing: true })
 		}
 	})
 
 	return results
 }
 
+async function findNextEligibleQueueProject(candidateIds: string[]) {
+	const skippedIds: string[] = []
+	let checkedCount = 0
+
+	while (checkedCount < candidateIds.length) {
+		const batch = candidateIds.slice(checkedCount, checkedCount + PREFETCH_BATCH_SIZE)
+		checkedCount += batch.length
+
+		const results = await batchCheckQueueCandidates(batch)
+
+		for (const id of batch) {
+			const result = results.get(id)
+			if (isEligibleQueueCandidate(result)) {
+				return { projectId: id, result: result!, skippedIds: [...skippedIds] }
+			}
+			skippedIds.push(id)
+		}
+	}
+
+	return null
+}
+
 // Maintain a queue of prefetched unlocked projects for instant navigation
 async function maintainPrefetchQueue() {
 	if (isPrefetching.value) return
@@ -947,13 +982,10 @@ async function maintainPrefetchQueue() {
 		const remainingItems =
 			currentIndex >= 0 ? queueItems.slice(currentIndex + 1) : queueItems.slice(1)
 
-		const candidateIds = remainingItems
-			.filter((id) => !prefetchedIds.has(id))
-			.slice(0, PREFETCH_BATCH_SIZE * 2) // Check up to 10 candidates
+		const candidateIds = remainingItems.filter((id) => !prefetchedIds.has(id))
 
 		if (candidateIds.length === 0) return
 
-		// 5. Batch check locks AND fetch metadata in parallel
 		const skippedIds: string[] = []
 		let checkedCount = 0
 
@@ -964,31 +996,18 @@ async function maintainPrefetchQueue() {
 			const batch = candidateIds.slice(checkedCount, checkedCount + PREFETCH_BATCH_SIZE)
 			checkedCount += batch.length
 
-			const results = await batchCheckLocksWithMetadata(batch)
+			const results = await batchCheckQueueCandidates(batch)
 
 			for (const id of batch) {
 				const result = results.get(id)
-				// Treat as unlocked if: not locked, OR expired, OR it's our own lock
-				if (!result?.locked || result?.expired || result?.isOwnLock) {
-					// Found unlocked project with metadata
-					if (result?.slug && result?.projectType) {
-						prefetchQueue.value.push({
-							projectId: id,
-							slug: result.slug,
-							projectType: result.projectType,
-							validatedAt: Date.now(),
-							skippedIds: [...skippedIds],
-						})
-					} else {
-						// No metadata - still add but will need fallback navigation
-						prefetchQueue.value.push({
-							projectId: id,
-							slug: '', // Empty = use fallback
-							projectType: '',
-							validatedAt: Date.now(),
-							skippedIds: [...skippedIds],
-						})
-					}
+				if (isEligibleQueueCandidate(result)) {
+					prefetchQueue.value.push({
+						projectId: id,
+						slug: result?.slug ?? '',
+						projectType: result?.projectType ?? '',
+						validatedAt: Date.now(),
+						skippedIds: [...skippedIds],
+					})
 
 					if (prefetchQueue.value.length >= PREFETCH_TARGET_COUNT) break
 				} else {
@@ -1004,8 +1023,6 @@ async function maintainPrefetchQueue() {
 // Debounced prefetch to prevent spam from rapid stage changes
 const debouncedPrefetch = useDebounceFn(maintainPrefetchQueue, 300)
 
-const MAX_SKIP_ATTEMPTS = 10
-
 async function skipToNextProject() {
 	// Skip the current project
 	const currentProjectId = projectV2.value?.id
@@ -1029,60 +1046,28 @@ async function skipToNextProject() {
 
 	debug('[skipToNextProject] No prefetch, entering fallback with batch checking')
 
-	// Fallback: batch check remaining projects with metadata (excluding current)
-	const remainingIds: string[] = []
-	const queueItems = moderationQueue.currentQueue.items
-
-	// Build list of remaining projects, excluding current
-	for (const id of queueItems) {
-		if (id === currentProjectId) continue
-		if (remainingIds.length >= MAX_SKIP_ATTEMPTS) break
-		remainingIds.push(id)
-	}
+	const remainingIds = moderationQueue.currentQueue.items.filter((id) => id !== currentProjectId)
 
 	if (remainingIds.length > 0) {
-		const results = await batchCheckLocksWithMetadata(remainingIds)
+		const next = await findNextEligibleQueueProject(remainingIds)
 
-		let skippedCount = 0
-		for (const id of remainingIds) {
-			const result = results.get(id)
-			// Treat as unlocked if: not locked, OR expired, OR it's our own lock
-			if (!result?.locked || result?.expired || result?.isOwnLock) {
-				// Found unlocked - skip the locked ones before it
-				if (skippedCount > 0) {
-					addNotification({
-						title: 'Skipped locked projects',
-						text: `Skipped ${skippedCount} project(s) being moderated by others.`,
-						type: 'info',
-					})
-				}
-
-				// Navigate to canonical URL if we have metadata
-				if (result?.slug && result?.projectType) {
-					const urlType = getProjectTypeForUrlShorthand(result.projectType, [], tags.value)
-					navigateTo({
-						path: `/${urlType}/${result.slug}`,
-						state: { showChecklist: true },
-					})
-				} else {
-					// Fallback: use project ID
-					navigateTo({
-						name: 'type-project',
-						params: { type: 'project', project: id },
-						state: { showChecklist: true },
-					})
-				}
-				return
-			}
-			await moderationQueue.completeCurrentProject(id, 'skipped')
-			skippedCount++
+		if (next) {
+			await Promise.all(
+				next.skippedIds.map((id) => moderationQueue.completeCurrentProject(id, 'skipped')),
+			)
+			notifySkippedQueueProjects(next.skippedIds.length)
+			navigateToQueueProject(next.result, next.projectId)
+			return
 		}
 
-		// All checked were locked
-		debug('[skipToNextProject] All projects were locked, skippedCount:', skippedCount)
+		await Promise.all(
+			remainingIds.map((id) => moderationQueue.completeCurrentProject(id, 'skipped')),
+		)
+
+		debug('[skipToNextProject] No eligible projects in queue')
 		addNotification({
-			title: 'All projects locked',
-			text: 'All remaining projects are currently being moderated by others.',
+			title: 'No projects available',
+			text: 'All remaining projects are already moderated or locked by others.',
 			type: 'warning',
 		})
 	}
@@ -1298,8 +1283,17 @@ onMounted(async () => {
 	document.addEventListener('visibilitychange', handleVisibilityChange)
 	notifications.setNotificationLocation('left')
 
-	// Check if project has already been reviewed (not in processing status)
 	if (projectV2.value.status !== 'processing') {
+		if (moderationQueue.isQueueMode && moderationQueue.queueLength > 1) {
+			addNotification({
+				title: 'Project already moderated',
+				text: 'Skipping to the next project in the queue.',
+				type: 'info',
+			})
+			await skipToNextProject()
+			return
+		}
+
 		alreadyReviewed.value = true
 		return
 	}
@@ -2085,72 +2079,36 @@ async function endChecklist(status?: string) {
 			})
 		}
 	} else {
-		// Use prefetched data if available for instant navigation
 		if (!(await navigateToNextUnlockedProject())) {
-			// Fallback: batch check remaining projects with metadata
-			const remainingIds: string[] = []
 			const currentProjectId = projectV2.value?.id
-			const queueItems = moderationQueue.currentQueue.items
+			const remainingIds = moderationQueue.currentQueue.items.filter(
+				(id) => id !== currentProjectId,
+			)
 
-			// Build list of remaining projects, excluding current
-			for (const id of queueItems) {
-				if (id === currentProjectId) continue
-				if (remainingIds.length >= MAX_SKIP_ATTEMPTS) break
-				remainingIds.push(id)
-			}
-
-			let foundUnlocked = false
+			let foundEligible = false
 			if (remainingIds.length > 0) {
-				const results = await batchCheckLocksWithMetadata(remainingIds)
+				const next = await findNextEligibleQueueProject(remainingIds)
 
-				let skippedCount = 0
-				for (const id of remainingIds) {
-					const result = results.get(id)
-					// Treat as unlocked if: not locked, OR expired, OR it's our own lock
-					if (!result?.locked || result?.expired || result?.isOwnLock) {
-						// Found unlocked - skip the locked ones before it
-						if (skippedCount > 0) {
-							addNotification({
-								title: 'Skipped locked projects',
-								text: `Skipped ${skippedCount} project(s) being moderated by others.`,
-								type: 'info',
-							})
-						}
-
-						// Navigate to canonical URL if we have metadata
-						if (result?.slug && result?.projectType) {
-							const urlType = getProjectTypeForUrlShorthand(result.projectType, [], tags.value)
-							navigateTo({
-								path: `/${urlType}/${result.slug}`,
-								state: { showChecklist: true },
-							})
-						} else {
-							// Fallback: use project ID
-							navigateTo({
-								name: 'type-project',
-								params: { type: 'project', project: id },
-								state: { showChecklist: true },
-							})
-						}
-						foundUnlocked = true
-						break
-					}
-					await moderationQueue.completeCurrentProject(id, 'skipped')
-					skippedCount++
-				}
-
-				// If no unlocked projects found, show notification
-				if (!foundUnlocked && skippedCount > 0) {
+				if (next) {
+					await Promise.all(
+						next.skippedIds.map((id) => moderationQueue.completeCurrentProject(id, 'skipped')),
+					)
+					notifySkippedQueueProjects(next.skippedIds.length)
+					navigateToQueueProject(next.result, next.projectId)
+					foundEligible = true
+				} else {
+					await Promise.all(
+						remainingIds.map((id) => moderationQueue.completeCurrentProject(id, 'skipped')),
+					)
 					addNotification({
-						title: 'All projects locked',
-						text: 'All remaining projects are currently being moderated by others.',
+						title: 'No projects available',
+						text: 'All remaining projects are already moderated or locked by others.',
 						type: 'warning',
 					})
 				}
 			}
 
-			// If no unlocked projects found, go back to moderation queue
-			if (!foundUnlocked) {
+			if (!foundEligible) {
 				await navigateTo({
 					name: 'moderation',
 				})

apps/frontend/src/pages/moderation/index.vue

@@ -452,9 +452,21 @@ const filteredProjects = computed(() => {
 	const filtered = [...typeFiltered.value]
 
 	if (currentSortType.value === 'Most external deps') {
-		filtered.sort((a, b) => b.external_dependencies_count - a.external_dependencies_count)
+		filtered.sort((a, b) => {
+			const depsDiff = b.external_dependencies_count - a.external_dependencies_count
+			if (depsDiff !== 0) return depsDiff
+			const dateA = new Date(a.project.queued || a.project.published || 0).getTime()
+			const dateB = new Date(b.project.queued || b.project.published || 0).getTime()
+			return dateA - dateB
+		})
 	} else if (currentSortType.value === 'Least external deps') {
-		filtered.sort((a, b) => a.external_dependencies_count - b.external_dependencies_count)
+		filtered.sort((a, b) => {
+			const depsDiff = a.external_dependencies_count - b.external_dependencies_count
+			if (depsDiff !== 0) return depsDiff
+			const dateA = new Date(a.project.queued || a.project.published || 0).getTime()
+			const dateB = new Date(b.project.queued || b.project.published || 0).getTime()
+			return dateA - dateB
+		})
 	} else if (currentSortType.value === 'Oldest') {
 		filtered.sort((a, b) => {
 			const dateA = new Date(a.project.queued || a.project.published || 0).getTime()
@@ -503,7 +515,7 @@ function goToPage(page: number) {
 	currentPage.value = page
 }
 
-async function findFirstUnlockedProject(): Promise<ModerationProject | null> {
+async function findFirstEligibleProject(): Promise<ModerationProject | null> {
 	let skippedCount = 0
 
 	while (moderationQueue.hasItems) {
@@ -513,24 +525,30 @@ async function findFirstUnlockedProject(): Promise<ModerationProject | null> {
 		const project = filteredProjects.value.find((p) => p.project.id === currentId)
 		if (!project) {
 			await moderationQueue.completeCurrentProject(currentId, 'skipped')
+			skippedCount++
+			continue
+		}
+
+		if (project.project.status !== 'processing') {
+			await moderationQueue.completeCurrentProject(currentId, 'skipped')
+			skippedCount++
 			continue
 		}
 
 		try {
 			const lockStatus = await moderationQueue.checkLock(currentId)
 
-			if (!lockStatus.locked || lockStatus.expired) {
+			if (!lockStatus.locked || lockStatus.expired || lockStatus.is_own_lock) {
 				if (skippedCount > 0) {
 					addNotification({
-						title: 'Skipped locked projects',
-						text: `Skipped ${skippedCount} project(s) being moderated by others.`,
+						title: 'Skipped projects',
+						text: `Skipped ${skippedCount} project(s) already moderated or locked by others.`,
 						type: 'info',
 					})
 				}
 				return project
 			}
 
-			// Project is locked, skip it
 			await moderationQueue.completeCurrentProject(currentId, 'skipped')
 			skippedCount++
 		} catch {
@@ -538,6 +556,14 @@ async function findFirstUnlockedProject(): Promise<ModerationProject | null> {
 		}
 	}
 
+	if (skippedCount > 0) {
+		addNotification({
+			title: 'Skipped projects',
+			text: `Skipped ${skippedCount} project(s) already moderated or locked by others.`,
+			type: 'info',
+		})
+	}
+
 	return null
 }
 
@@ -549,12 +575,12 @@ async function moderateAllInFilter() {
 	await moderationQueue.setQueue(projectIds)
 
 	// Find first unlocked project
-	const targetProject = await findFirstUnlockedProject()
+	const targetProject = await findFirstEligibleProject()
 
 	if (!targetProject) {
 		addNotification({
-			title: 'All projects locked',
-			text: 'All projects in queue are currently being moderated by others.',
+			title: 'No projects available',
+			text: 'All projects in queue are already moderated or locked by others.',
 			type: 'warning',
 		})
 		return
@@ -585,13 +611,12 @@ async function startFromProject(projectId: string) {
 		await moderationQueue.setQueue(projectIds)
 	}
 
-	// Find first unlocked project
-	const targetProject = await findFirstUnlockedProject()
+	const targetProject = await findFirstEligibleProject()
 
 	if (!targetProject) {
 		addNotification({
-			title: 'All projects locked',
-			text: 'All projects in queue are currently being moderated by others.',
+			title: 'No projects available',
+			text: 'All projects in queue are already moderated or locked by others.',
 			type: 'warning',
 		})
 		return

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

@@ -0,0 +1,36 @@
+{
+  "db_name": "PostgreSQL",
+  "query": "\n        SELECT\n            t.mod_id AS \"project_id!\",\n            tm.created,\n            tm.body AS \"body: sqlx::types::Json<MessageBody>\"\n        FROM threads_messages tm\n        INNER JOIN threads t ON t.id = tm.thread_id\n        WHERE\n            t.mod_id = ANY($1)\n            AND tm.body->>'type' = 'status_change'\n            AND tm.created BETWEEN $2 AND $3\n        ",
+  "describe": {
+    "columns": [
+      {
+        "ordinal": 0,
+        "name": "project_id!",
+        "type_info": "Int8"
+      },
+      {
+        "ordinal": 1,
+        "name": "created",
+        "type_info": "Timestamptz"
+      },
+      {
+        "ordinal": 2,
+        "name": "body: sqlx::types::Json<MessageBody>",
+        "type_info": "Jsonb"
+      }
+    ],
+    "parameters": {
+      "Left": [
+        "Int8Array",
+        "Timestamptz",
+        "Timestamptz"
+      ]
+    },
+    "nullable": [
+      true,
+      false,
+      false
+    ]
+  },
+  "hash": "a5141c0435441f062231c842cb5db5e0c78f8f3896c1e9f2b2b56cce41fa1591"
+}

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

@@ -9,7 +9,7 @@
 
 mod old;
 
-use std::{num::NonZeroU64, sync::LazyLock};
+use std::{collections::HashMap, num::NonZeroU64, sync::LazyLock};
 
 use crate::database::PgPool;
 use actix_web::{HttpRequest, post, web};
@@ -21,19 +21,24 @@ use rust_decimal::Decimal;
 use serde::{Deserialize, Deserializer, Serialize, Serializer, de::Error as _};
 
 use crate::{
-    auth::{AuthenticationError, get_user_from_headers},
+    auth::{
+        AuthenticationError, checks::filter_visible_version_ids,
+        get_user_from_headers,
+    },
     database::{
         self, DBProject,
         models::{
             DBAffiliateCode, DBAffiliateCodeId, DBProjectId, DBUser, DBUserId,
-            DBVersionId,
+            DBVersion, DBVersionId,
         },
         redis::RedisPool,
     },
     models::{
         ids::{AffiliateCodeId, ProjectId, VersionId},
         pats::Scopes,
+        projects::ProjectStatus,
         teams::ProjectPermissions,
+        threads::MessageBody,
         v3::analytics::DownloadReason,
     },
     queue::session::AuthQueue,
@@ -255,17 +260,46 @@ pub const MAX_TIME_SLICES: usize = 1024;
 
 /// Response for a [`GetRequest`].
 #[derive(Debug, Default, Serialize, Deserialize, utoipa::ToSchema)]
-pub struct FetchResponse {
+pub struct GetResponse {
     /// List of N [`TimeSlice`]s, where each slice represents an equal
     /// time interval of metrics collection. The number of slices is determined
     /// by [`GetRequest::time_range`].
     pub metrics: Vec<TimeSlice>,
+    /// List of events associated with projects that were requested.
+    pub project_events: Vec<ProjectAnalyticsEvent>,
 }
 
 /// Single time interval of metrics collection.
 #[derive(Debug, Clone, Default, Serialize, Deserialize, utoipa::ToSchema)]
 pub struct TimeSlice(pub Vec<AnalyticsData>);
 
+/// Notable update to a project which may reflect in analytics metrics.
+#[derive(Debug, Clone, Serialize, Deserialize, utoipa::ToSchema)]
+pub struct ProjectAnalyticsEvent {
+    /// ID of the event's project.
+    pub project_id: ProjectId,
+    /// When the event occurred.
+    pub timestamp: DateTime<Utc>,
+    #[serde(flatten)]
+    pub kind: ProjectAnalyticsEventKind,
+}
+
+#[derive(Debug, Clone, Serialize, Deserialize, utoipa::ToSchema)]
+#[serde(tag = "kind", rename_all = "snake_case")]
+pub enum ProjectAnalyticsEventKind {
+    /// New version of this project was uploaded.
+    VersionUploaded {
+        version_id: VersionId,
+        version_name: String,
+        version_number: String,
+    },
+    /// Project changed status.
+    StatusChanged {
+        status_from: ProjectStatus,
+        status_to: ProjectStatus,
+    },
+}
+
 /// Metrics collected in a single [`TimeSlice`].
 #[derive(Debug, Clone, Serialize, Deserialize, utoipa::ToSchema)]
 #[serde(untagged)] // the presence of `source_project`, `source_affiliate_code` determines the kind
@@ -351,7 +385,7 @@ pub struct ProjectDownloads {
     downloads: u64,
 }
 
-#[derive(Debug, Clone, PartialEq, Eq, utoipa::ToSchema)]
+#[derive(Debug, Clone, PartialEq, Eq, Hash, utoipa::ToSchema)]
 pub enum DownloadSource {
     Website,
     ModrinthApp,
@@ -665,7 +699,7 @@ mod query {
 
 /// Fetches analytics data for the authorized user's projects.
 #[utoipa::path(
-    responses((status = OK, body = inline(FetchResponse))),
+    responses((status = OK, body = inline(GetResponse))),
 )]
 #[post("")]
 pub async fn fetch_analytics(
@@ -675,7 +709,7 @@ pub async fn fetch_analytics(
     redis: web::Data<RedisPool>,
     session_queue: web::Data<AuthQueue>,
     clickhouse: web::Data<clickhouse::Client>,
-) -> Result<web::Json<FetchResponse>, ApiError> {
+) -> Result<web::Json<GetResponse>, ApiError> {
     let (scopes, user) = get_user_from_headers(
         &http_req,
         &**pool,
@@ -768,6 +802,44 @@ pub async fn fetch_analytics(
         .iter()
         .map(|version| DBProjectId(version.mod_id))
         .collect::<Vec<_>>();
+    let parent_version_data =
+        DBVersion::get_many(&parent_version_ids, &**pool, &redis).await?;
+    let visible_version_ids = filter_visible_version_ids(
+        parent_version_data
+            .iter()
+            .map(|version| &version.inner)
+            .collect(),
+        &Some(user.clone()),
+        &pool,
+        &redis,
+    )
+    .await?;
+    let mut project_events = parent_version_data
+        .iter()
+        .filter(|version| {
+            visible_version_ids.contains(&version.inner.id)
+                && version.inner.date_published >= req.time_range.start
+                && version.inner.date_published <= req.time_range.end
+        })
+        .map(|version| ProjectAnalyticsEvent {
+            project_id: version.inner.project_id.into(),
+            timestamp: version.inner.date_published,
+            kind: ProjectAnalyticsEventKind::VersionUploaded {
+                version_id: version.inner.id.into(),
+                version_name: version.inner.name.clone(),
+                version_number: version.inner.version_number.clone(),
+            },
+        })
+        .collect::<Vec<_>>();
+    project_events.extend(
+        fetch_project_status_change_events(
+            &project_ids,
+            &req.time_range,
+            &pool,
+        )
+        .await?,
+    );
+    project_events.sort_by_key(|event| event.timestamp);
 
     let affiliate_code_ids =
         DBAffiliateCode::get_by_affiliate(user.id.into(), &**pool)
@@ -830,9 +902,8 @@ pub async fn fetch_analytics(
         use ProjectDownloadsField as F;
         let uses = |field| metrics.bucket_by.contains(&field);
 
-        query_clickhouse::<query::DownloadRow>(
+        query_clickhouse_downloads(
             &mut query_clickhouse_cx,
-            query::DOWNLOADS,
             &[
                 ("use_project_id", uses(F::ProjectId)),
                 ("use_domain", uses(F::Domain)),
@@ -844,37 +915,6 @@ pub async fn fetch_analytics(
                 ("use_game_version", uses(F::GameVersion)),
                 ("use_loader", uses(F::Loader)),
             ],
-            |row| row.bucket,
-            |row| {
-                let country = if uses(F::Country) {
-                    Some(condense_country(row.country, row.downloads))
-                } else {
-                    None
-                };
-                AnalyticsData::Project(ProjectAnalytics {
-                    source_project: row.project_id.into(),
-                    metrics: ProjectMetrics::Downloads(ProjectDownloads {
-                        domain: none_if_empty(row.domain),
-                        user_agent: if uses(F::UserAgent) {
-                            normalize_download_source(&row.user_agent)
-                        } else {
-                            None
-                        },
-                        version_id: none_if_zero_version_id(row.version_id),
-                        monetized: match row.monetized {
-                            0 => Some(false),
-                            1 => Some(true),
-                            _ => None,
-                        },
-                        country,
-                        reason: none_if_empty(row.reason)
-                            .and_then(|s| s.parse().ok()),
-                        game_version: none_if_empty(row.game_version),
-                        loader: none_if_empty(row.loader),
-                        downloads: row.downloads,
-                    }),
-                })
-            },
         )
         .await?;
     }
@@ -1103,8 +1143,9 @@ pub async fn fetch_analytics(
         }
     }
 
-    Ok(web::Json(FetchResponse {
+    Ok(web::Json(GetResponse {
         metrics: time_slices,
+        project_events,
     }))
 }
 
@@ -1195,6 +1236,57 @@ fn condense_country(country: String, count: u64) -> String {
     }
 }
 
+async fn fetch_project_status_change_events(
+    project_ids: &[DBProjectId],
+    time_range: &TimeRange,
+    pool: &PgPool,
+) -> Result<Vec<ProjectAnalyticsEvent>, ApiError> {
+    let project_id_values =
+        project_ids.iter().map(|id| id.0).collect::<Vec<_>>();
+
+    let rows = sqlx::query!(
+        r#"
+        SELECT
+            t.mod_id AS "project_id!",
+            tm.created,
+            tm.body AS "body: sqlx::types::Json<MessageBody>"
+        FROM threads_messages tm
+        INNER JOIN threads t ON t.id = tm.thread_id
+        WHERE
+            t.mod_id = ANY($1)
+            AND tm.body->>'type' = 'status_change'
+            AND tm.created BETWEEN $2 AND $3
+        "#,
+        &project_id_values,
+        time_range.start,
+        time_range.end,
+    )
+    .fetch_all(&**pool)
+    .await?;
+
+    Ok(rows
+        .into_iter()
+        .filter_map(|row| {
+            let MessageBody::StatusChange {
+                old_status,
+                new_status,
+            } = row.body.0
+            else {
+                return None;
+            };
+
+            Some(ProjectAnalyticsEvent {
+                project_id: DBProjectId(row.project_id).into(),
+                timestamp: row.created,
+                kind: ProjectAnalyticsEventKind::StatusChanged {
+                    status_from: old_status,
+                    status_to: new_status,
+                },
+            })
+        })
+        .collect())
+}
+
 struct QueryClickhouseContext<'a> {
     clickhouse: &'a clickhouse::Client,
     req: &'a GetRequest,
@@ -1205,6 +1297,107 @@ struct QueryClickhouseContext<'a> {
     affiliate_code_ids: &'a [DBAffiliateCodeId],
 }
 
+#[derive(Debug, Clone, PartialEq, Eq, Hash)]
+struct DownloadBucket {
+    bucket: u64,
+    project_id: DBProjectId,
+    domain: Option<String>,
+    user_agent: Option<DownloadSource>,
+    version_id: Option<DBVersionId>,
+    monetized: Option<bool>,
+    country: Option<String>,
+    reason: Option<DownloadReason>,
+    game_version: Option<String>,
+    loader: Option<String>,
+}
+
+async fn query_clickhouse_downloads(
+    cx: &mut QueryClickhouseContext<'_>,
+    use_columns: &[(&str, bool)],
+) -> Result<(), ApiError> {
+    let mut query = cx
+        .clickhouse
+        .query(query::DOWNLOADS)
+        .param("time_range_start", cx.req.time_range.start.timestamp())
+        .param("time_range_end", cx.req.time_range.end.timestamp())
+        .param("time_slices", cx.time_slices.len())
+        .param("project_ids", cx.project_ids)
+        .param("parent_version_ids", cx.parent_version_ids)
+        .param("parent_version_project_ids", cx.parent_version_project_ids)
+        .param("affiliate_code_ids", cx.affiliate_code_ids);
+    for (param_name, used) in use_columns {
+        query = query.param(param_name, used)
+    }
+
+    let uses = |name| {
+        use_columns
+            .iter()
+            .any(|(column_name, used)| *column_name == name && *used)
+    };
+    let mut cursor = query.fetch::<query::DownloadRow>()?;
+    let mut buckets = HashMap::<DownloadBucket, u64>::new();
+
+    while let Some(row) = cursor.next().await? {
+        let key = DownloadBucket {
+            bucket: row.bucket,
+            project_id: row.project_id,
+            domain: uses("use_domain").then(|| row.domain.clone()),
+            user_agent: uses("use_user_agent")
+                .then(|| normalize_download_source(&row.user_agent))
+                .flatten(),
+            version_id: uses("use_version_id").then_some(row.version_id),
+            monetized: if uses("use_monetized") {
+                match row.monetized {
+                    0 => Some(false),
+                    1 => Some(true),
+                    _ => None,
+                }
+            } else {
+                None
+            },
+            country: uses("use_country").then(|| row.country.clone()),
+            reason: if uses("use_reason") {
+                none_if_empty(row.reason.clone()).and_then(|s| s.parse().ok())
+            } else {
+                None
+            },
+            game_version: uses("use_game_version")
+                .then(|| row.game_version.clone()),
+            loader: uses("use_loader").then(|| row.loader.clone()),
+        };
+
+        *buckets.entry(key).or_default() += row.downloads;
+    }
+
+    for (key, downloads) in buckets {
+        let bucket = key.bucket as usize;
+        add_to_time_slice(
+            cx.time_slices,
+            bucket,
+            AnalyticsData::Project(ProjectAnalytics {
+                source_project: key.project_id.into(),
+                metrics: ProjectMetrics::Downloads(ProjectDownloads {
+                    domain: key.domain.and_then(none_if_empty),
+                    user_agent: key.user_agent,
+                    version_id: key
+                        .version_id
+                        .and_then(none_if_zero_version_id),
+                    monetized: key.monetized,
+                    country: key
+                        .country
+                        .map(|country| condense_country(country, downloads)),
+                    reason: key.reason,
+                    game_version: key.game_version.and_then(none_if_empty),
+                    loader: key.loader.and_then(none_if_empty),
+                    downloads,
+                }),
+            }),
+        )?;
+    }
+
+    Ok(())
+}
+
 async fn query_clickhouse<Row>(
     cx: &mut QueryClickhouseContext<'_>,
     query: &str,
@@ -1395,7 +1588,7 @@ mod tests {
         let test_project_2 = ProjectId(456);
         let test_project_3 = ProjectId(789);
 
-        let src = FetchResponse {
+        let src = GetResponse {
             metrics: vec![
                 TimeSlice(vec![
                     AnalyticsData::Project(ProjectAnalytics {
@@ -1422,6 +1615,7 @@ mod tests {
                     }),
                 })]),
             ],
+            project_events: vec![],
         };
         let target = json!({
             "metrics": [
@@ -1446,7 +1640,8 @@ mod tests {
                         "revenue": "200.00",
                     }
                 ]
-            ]
+            ],
+            "project_events": []
         });
 
         assert_eq!(serde_json::to_value(src).unwrap(), target);

packages/app-lib/src/api/jre.rs

@@ -181,12 +181,29 @@ pub async fn test_jre(
     Ok(version == major_version)
 }
 
-// Gets maximum memory in KiB.
-pub async fn get_max_memory() -> crate::Result<u64> {
-    Ok(sysinfo::System::new_with_specifics(
+fn system_memory_bytes() -> u64 {
+    sysinfo::System::new_with_specifics(
         RefreshKind::nothing()
             .with_memory(MemoryRefreshKind::nothing().with_ram()),
     )
     .total_memory()
-        / 1024)
+}
+
+/// Recommended default max heap (MiB) for new instances based on system RAM.
+pub fn default_memory_max_mb() -> u32 {
+    const BYTES_PER_GIB: u64 = 1024 * 1024 * 1024;
+    let system_gib = system_memory_bytes() / BYTES_PER_GIB;
+
+    if system_gib < 8 {
+        1024 * 2
+    } else if system_gib >= 24 {
+        1024 * 6
+    } else {
+        1024 * 4
+    }
+}
+
+// Gets maximum memory in KiB.
+pub async fn get_max_memory() -> crate::Result<u64> {
+    Ok(system_memory_bytes() / 1024)
 }

packages/app-lib/src/state/settings.rs

@@ -64,7 +64,7 @@ pub enum FeatureFlag {
 }
 
 impl Settings {
-    const CURRENT_VERSION: usize = 2;
+    const CURRENT_VERSION: usize = 3;
 
     pub async fn get(
         exec: impl sqlx::Executor<'_, Database = sqlx::Sqlite>,
@@ -295,6 +295,16 @@ impl Settings {
 
                 self.version = 2;
             }
+            2 => {
+                // Update old default memory setting from 2GB to 4GB (depending on system memory)
+                const LEGACY_DEFAULT_MEMORY_MB: u32 = 2048;
+                if self.memory.maximum == LEGACY_DEFAULT_MEMORY_MB {
+                    self.memory.maximum =
+                        crate::api::jre::default_memory_max_mb();
+                }
+
+                self.version = 3;
+            }
             version => {
                 return Err(crate::ErrorKind::OtherError(format!(
                     "Invalid settings version: {version}"