track latest note for each user

2 files changed, 63 insertions, 3 deletions

diff --git a/packages/backend/src/core/NoteCreateService.ts b/packages/backend/src/core/NoteCreateService.ts
index c252336f99..17631eea89 100644
--- a/packages/backend/src/core/NoteCreateService.ts
+++ b/packages/backend/src/core/NoteCreateService.ts
@@ -14,7 +14,8 @@ import { extractCustomEmojisFromMfm } from '@/misc/extract-custom-emojis-from-mf
 import { extractHashtags } from '@/misc/extract-hashtags.js';
 import type { IMentionedRemoteUsers } from '@/models/Note.js';
 import { MiNote } from '@/models/Note.js';
-import type { ChannelFollowingsRepository, ChannelsRepository, FollowingsRepository, InstancesRepository, MiFollowing, MutingsRepository, NotesRepository, NoteThreadMutingsRepository, UserListMembershipsRepository, UserProfilesRepository, UsersRepository } from '@/models/_.js';
+import { LatestNote } from '@/models/LatestNote.js';
+import type { ChannelFollowingsRepository, ChannelsRepository, FollowingsRepository, InstancesRepository, LatestNotesRepository, MiFollowing, MutingsRepository, NotesRepository, NoteThreadMutingsRepository, UserListMembershipsRepository, UserProfilesRepository, UsersRepository } from '@/models/_.js';
 import type { MiDriveFile } from '@/models/DriveFile.js';
 import type { MiApp } from '@/models/App.js';
 import { concat } from '@/misc/prelude/array.js';
@@ -170,6 +171,9 @@ export class NoteCreateService implements OnApplicationShutdown {
 		@Inject(DI.notesRepository)
 		private notesRepository: NotesRepository,
 
+		@Inject(DI.latestNotesRepository)
+		private latestNotesRepository: LatestNotesRepository,
+
 		@Inject(DI.mutingsRepository)
 		private mutingsRepository: MutingsRepository,
 
@@ -514,6 +518,8 @@ export class NoteCreateService implements OnApplicationShutdown {
 				await this.notesRepository.insert(insert);
 			}
 
+			await this.updateLatestNote(insert);
+
 			return insert;
 		} catch (e) {
 			// duplicate key error
@@ -1125,4 +1131,21 @@ export class NoteCreateService implements OnApplicationShutdown {
 	public onApplicationShutdown(signal?: string | undefined): void {
 		this.dispose();
 	}
+
+	private async updateLatestNote(note: MiNote) {
+		// Ignore DMs
+		if (note.visibility === 'specified') return;
+
+		// Make sure that this isn't an *older* post.
+		// We can get older posts through replies, lookups, etc.
+		const currentLatest = await this.latestNotesRepository.findOneBy({ userId: note.userId });
+		if (currentLatest != null && currentLatest.userId >= note.id) return;
+
+		// Record this as the latest note for the given user
+		const latestNote = new LatestNote({
+			userId: note.userId,
+			noteId: note.id,
+		});
+		await this.latestNotesRepository.upsert(latestNote, ['userId']);
+	}
 }
diff --git a/packages/backend/src/core/NoteDeleteService.ts b/packages/backend/src/core/NoteDeleteService.ts
index 7ce6d7c605..898e164966 100644
--- a/packages/backend/src/core/NoteDeleteService.ts
+++ b/packages/backend/src/core/NoteDeleteService.ts
@@ -3,11 +3,12 @@
  * SPDX-License-Identifier: AGPL-3.0-only
  */
 
-import { Brackets, In } from 'typeorm';
+import { Brackets, In, Not } from 'typeorm';
 import { Injectable, Inject } from '@nestjs/common';
 import type { MiUser, MiLocalUser, MiRemoteUser } from '@/models/User.js';
 import type { MiNote, IMentionedRemoteUsers } from '@/models/Note.js';
-import type { InstancesRepository, NotesRepository, UsersRepository } from '@/models/_.js';
+import { LatestNote } from '@/models/LatestNote.js';
+import type { InstancesRepository, LatestNotesRepository, NotesRepository, UsersRepository } from '@/models/_.js';
 import { RelayService } from '@/core/RelayService.js';
 import { FederatedInstanceService } from '@/core/FederatedInstanceService.js';
 import { DI } from '@/di-symbols.js';
@@ -38,6 +39,9 @@ export class NoteDeleteService {
 		@Inject(DI.notesRepository)
 		private notesRepository: NotesRepository,
 
+		@Inject(DI.latestNotesRepository)
+		private latestNotesRepository: LatestNotesRepository,
+
 		@Inject(DI.instancesRepository)
 		private instancesRepository: InstancesRepository,
 
@@ -148,6 +152,8 @@ export class NoteDeleteService {
 			userId: user.id,
 		});
 
+		await this.updateLatestNote(note);
+
 		if (deleter && (note.userId !== deleter.id)) {
 			const user = await this.usersRepository.findOneByOrFail({ id: note.userId });
 			this.moderationLogService.log(deleter, 'deleteNote', {
@@ -229,4 +235,35 @@ export class NoteDeleteService {
 			this.apDeliverManagerService.deliverToUser(user, content, remoteUser);
 		}
 	}
+
+	private async updateLatestNote(note: MiNote) {
+		// If it's a DM, then it can't possibly be the latest note so we can safely skip this.
+		if (note.visibility === 'specified') return;
+
+		// Find the newest remaining note for the user
+		const nextLatest = await this.notesRepository
+			.createQueryBuilder()
+			.select()
+			.where({
+				userId: note.userId,
+				visibility: Not('specified'),
+			})
+			.orderBy({ id: 'DESC' })
+			.getOne();
+		if (!nextLatest) return;
+
+		// Record it as the latest
+		const latestNote = new LatestNote({
+			userId: note.userId,
+			noteId: nextLatest.id,
+		});
+
+		// We use an upsert because this deleted note might not have been the newest.
+		// In that case, the latest note may already be populated for this user.
+		// We want postgres to do nothing instead of replacing the value or returning an error.
+		await this.latestNotesRepository.upsert(latestNote, {
+			conflictPaths: ['userId'],
+			skipUpdateIfNoValuesChanged: true,
+		});
+	}
 }