add `latest_note` table

3 files changed, 49 insertions, 0 deletions

diff --git a/packages/backend/src/models/LatestNote.ts b/packages/backend/src/models/LatestNote.ts
new file mode 100644
index 0000000000..9d56b82620
--- /dev/null
+++ b/packages/backend/src/models/LatestNote.ts
@@ -0,0 +1,37 @@
+import { PrimaryColumn, Entity, JoinColumn, Column, ManyToOne } from 'typeorm';
+import { MiUser } from '@/models/User.js';
+import { MiNote } from '@/models/Note.js';
+
+/**
+ * Maps a user to the most recent post by that user.
+ * Public, home-only, and followers-only posts are included.
+ * DMs are not counted.
+ */
+@Entity('latest_note')
+export class LatestNote {
+	@PrimaryColumn({
+		name: 'user_id',
+		type: 'varchar' as const,
+		length: 32,
+	})
+	public userId: string;
+
+	@ManyToOne(() => MiUser, {
+		onDelete: 'CASCADE',
+	})
+	@JoinColumn()
+	public user: MiUser | null;
+
+	@Column({
+		name: 'note_id',
+		type: 'varchar' as const,
+		length: 32,
+	})
+	public noteId: string;
+
+	@ManyToOne(() => MiNote, {
+		onDelete: 'CASCADE',
+	})
+	@JoinColumn()
+	public note: MiNote | null;
+}
diff --git a/packages/backend/src/models/RepositoryModule.ts b/packages/backend/src/models/RepositoryModule.ts
index 1eaeb86df6..f44334d84e 100644
--- a/packages/backend/src/models/RepositoryModule.ts
+++ b/packages/backend/src/models/RepositoryModule.ts
@@ -7,6 +7,7 @@ import type { Provider } from '@nestjs/common';
 import { Module } from '@nestjs/common';
 import { DI } from '@/di-symbols.js';
 import {
+	LatestNote,
 	MiAbuseReportNotificationRecipient,
 	MiAbuseUserReport,
 	MiAccessToken,
@@ -118,6 +119,12 @@ const $avatarDecorationsRepository: Provider = {
 	inject: [DI.db],
 };
 
+const $latestNotesRepository: Provider = {
+	provide: DI.latestNotesRepository,
+	useFactory: (db: DataSource) => db.getRepository(LatestNote).extend(miRepository as MiRepository<LatestNote>),
+	inject: [DI.db],
+};
+
 const $noteFavoritesRepository: Provider = {
 	provide: DI.noteFavoritesRepository,
 	useFactory: (db: DataSource) => db.getRepository(MiNoteFavorite).extend(miRepository as MiRepository<MiNoteFavorite>),
@@ -511,6 +518,7 @@ const $reversiGamesRepository: Provider = {
 		$announcementReadsRepository,
 		$appsRepository,
 		$avatarDecorationsRepository,
+		$latestNotesRepository,
 		$noteFavoritesRepository,
 		$noteThreadMutingsRepository,
 		$noteReactionsRepository,
@@ -583,6 +591,7 @@ const $reversiGamesRepository: Provider = {
 		$announcementReadsRepository,
 		$appsRepository,
 		$avatarDecorationsRepository,
+		$latestNotesRepository,
 		$noteFavoritesRepository,
 		$noteThreadMutingsRepository,
 		$noteReactionsRepository,
diff --git a/packages/backend/src/models/_.ts b/packages/backend/src/models/_.ts
index f7646dce2a..1a79aeb80d 100644
--- a/packages/backend/src/models/_.ts
+++ b/packages/backend/src/models/_.ts
@@ -10,6 +10,7 @@ import { RelationIdLoader } from 'typeorm/query-builder/relation-id/RelationIdLo
 import { RawSqlResultsToEntityTransformer } from 'typeorm/query-builder/transformer/RawSqlResultsToEntityTransformer.js';
 import { ObjectUtils } from 'typeorm/util/ObjectUtils.js';
 import { OrmUtils } from 'typeorm/util/OrmUtils.js';
+import { LatestNote } from '@/models/LatestNote.js';
 import { MiAbuseUserReport } from '@/models/AbuseUserReport.js';
 import { MiAbuseReportNotificationRecipient } from '@/models/AbuseReportNotificationRecipient.js';
 import { MiAccessToken } from '@/models/AccessToken.js';
@@ -126,6 +127,7 @@ export const miRepository = {
 } satisfies MiRepository<ObjectLiteral>;
 
 export {
+	LatestNote,
 	MiAbuseUserReport,
 	MiAbuseReportNotificationRecipient,
 	MiAccessToken,
@@ -224,6 +226,7 @@ export type GalleryPostsRepository = Repository<MiGalleryPost> & MiRepository<Mi
 export type HashtagsRepository = Repository<MiHashtag> & MiRepository<MiHashtag>;
 export type InstancesRepository = Repository<MiInstance> & MiRepository<MiInstance>;
 export type MetasRepository = Repository<MiMeta> & MiRepository<MiMeta>;
+export type LatestNoteRepository = Repository<LatestNote> & MiRepository<LatestNote>;
 export type ModerationLogsRepository = Repository<MiModerationLog> & MiRepository<MiModerationLog>;
 export type MutingsRepository = Repository<MiMuting> & MiRepository<MiMuting>;
 export type RenoteMutingsRepository = Repository<MiRenoteMuting> & MiRepository<MiRenoteMuting>;