feat: introduce retention-rate aggregation

3 files changed, 47 insertions, 1 deletions

diff --git a/packages/backend/src/models/RepositoryModule.ts b/packages/backend/src/models/RepositoryModule.ts
index 9edef10e87..e22f0517ca 100644
--- a/packages/backend/src/models/RepositoryModule.ts
+++ b/packages/backend/src/models/RepositoryModule.ts
@@ -1,6 +1,6 @@
 import { Module } from '@nestjs/common';
 import { DI } from '@/di-symbols.js';
-import { User, Note, Announcement, AnnouncementRead, App, NoteFavorite, NoteThreadMuting, NoteReaction, NoteUnread, Notification, Poll, PollVote, UserProfile, UserKeypair, UserPending, AttestationChallenge, UserSecurityKey, UserPublickey, UserList, UserListJoining, UserGroup, UserGroupJoining, UserGroupInvitation, UserNotePining, UserIp, UsedUsername, Following, FollowRequest, Instance, Emoji, DriveFile, DriveFolder, Meta, Muting, Blocking, SwSubscription, Hashtag, AbuseUserReport, RegistrationTicket, AuthSession, AccessToken, Signin, MessagingMessage, Page, PageLike, GalleryPost, GalleryLike, ModerationLog, Clip, ClipNote, Antenna, AntennaNote, PromoNote, PromoRead, Relay, MutedNote, Channel, ChannelFollowing, ChannelNotePining, RegistryItem, Webhook, Ad, PasswordResetRequest } from './index.js';
+import { User, Note, Announcement, AnnouncementRead, App, NoteFavorite, NoteThreadMuting, NoteReaction, NoteUnread, Notification, Poll, PollVote, UserProfile, UserKeypair, UserPending, AttestationChallenge, UserSecurityKey, UserPublickey, UserList, UserListJoining, UserGroup, UserGroupJoining, UserGroupInvitation, UserNotePining, UserIp, UsedUsername, Following, FollowRequest, Instance, Emoji, DriveFile, DriveFolder, Meta, Muting, Blocking, SwSubscription, Hashtag, AbuseUserReport, RegistrationTicket, AuthSession, AccessToken, Signin, MessagingMessage, Page, PageLike, GalleryPost, GalleryLike, ModerationLog, Clip, ClipNote, Antenna, AntennaNote, PromoNote, PromoRead, Relay, MutedNote, Channel, ChannelFollowing, ChannelNotePining, RegistryItem, Webhook, Ad, PasswordResetRequest, RetentionAggregation } from './index.js';
 import type { DataSource } from 'typeorm';
 import type { Provider } from '@nestjs/common';
 
@@ -382,6 +382,12 @@ const $passwordResetRequestsRepository: Provider = {
 	inject: [DI.db],
 };
 
+const $retentionAggregationsRepository: Provider = {
+	provide: DI.retentionAggregationsRepository,
+	useFactory: (db: DataSource) => db.getRepository(RetentionAggregation),
+	inject: [DI.db],
+};
+
 @Module({
 	imports: [
 	],
@@ -449,6 +455,7 @@ const $passwordResetRequestsRepository: Provider = {
 		$webhooksRepository,
 		$adsRepository,
 		$passwordResetRequestsRepository,
+		$retentionAggregationsRepository,
 	],
 	exports: [
 		$usersRepository,
@@ -514,6 +521,7 @@ const $passwordResetRequestsRepository: Provider = {
 		$webhooksRepository,
 		$adsRepository,
 		$passwordResetRequestsRepository,
+		$retentionAggregationsRepository,
 	],
 })
 export class RepositoryModule {}
diff --git a/packages/backend/src/models/entities/RetentionAggregation.ts b/packages/backend/src/models/entities/RetentionAggregation.ts
new file mode 100644
index 0000000000..c79b762d71
--- /dev/null
+++ b/packages/backend/src/models/entities/RetentionAggregation.ts
@@ -0,0 +1,35 @@
+import { Entity, PrimaryColumn, Index, Column } from 'typeorm';
+import { id } from '../id.js';
+import type { User } from './User.js';
+
+@Entity()
+export class RetentionAggregation {
+	@PrimaryColumn(id())
+	public id: string;
+
+	@Index()
+	@Column('timestamp with time zone', {
+		comment: 'The created date of the Note.',
+	})
+	public createdAt: Date;
+
+	@Column('timestamp with time zone', {
+		comment: 'The updated date of the GalleryPost.',
+	})
+	public updatedAt: Date;
+
+	@Column({
+		...id(),
+		array: true,
+	})
+	public userIds: User['id'][];
+
+	@Column('integer', {
+	})
+	public usersCount: number;
+
+	@Column('jsonb', {
+		default: {},
+	})
+	public data: Record<string, number>;
+}
diff --git a/packages/backend/src/models/index.ts b/packages/backend/src/models/index.ts
index 7fde3fbedb..ca7a7c9e56 100644
--- a/packages/backend/src/models/index.ts
+++ b/packages/backend/src/models/index.ts
@@ -61,6 +61,7 @@ import { UserPublickey } from '@/models/entities/UserPublickey.js';
 import { UserSecurityKey } from '@/models/entities/UserSecurityKey.js';
 import { Webhook } from '@/models/entities/Webhook.js';
 import { Channel } from '@/models/entities/Channel.js';
+import { RetentionAggregation } from '@/models/entities/RetentionAggregation.js';
 import type { Repository } from 'typeorm';
 
 export {
@@ -127,6 +128,7 @@ export {
 	UserSecurityKey,
 	Webhook,
 	Channel,
+	RetentionAggregation,
 };
 
 export type AbuseUserReportsRepository = Repository<AbuseUserReport>;
@@ -192,3 +194,4 @@ export type UserPublickeysRepository = Repository<UserPublickey>;
 export type UserSecurityKeysRepository = Repository<UserSecurityKey>;
 export type WebhooksRepository = Repository<Webhook>;
 export type ChannelsRepository = Repository<Channel>;
+export type RetentionAggregationsRepository = Repository<RetentionAggregation>;