merge: prepare release 2025.4.3 (!1125)

View MR for information: https://activitypub.software/TransFem-org/Sharkey/-/merge_requests/1125

Approved-by: Marie <github@yuugi.dev>
Approved-by: Julia <julia@insertdomain.name>

8 files changed, 176 insertions, 13 deletions

diff --git a/packages/backend/src/models/AbuseUserReport.ts b/packages/backend/src/models/AbuseUserReport.ts
index d43ebf9342..8f8d759004 100644
--- a/packages/backend/src/models/AbuseUserReport.ts
+++ b/packages/backend/src/models/AbuseUserReport.ts
@@ -4,6 +4,7 @@
  */
 
 import { PrimaryColumn, Entity, Index, JoinColumn, Column, ManyToOne } from 'typeorm';
+import { MiInstance } from '@/models/Instance.js';
 import { id } from './util/id.js';
 import { MiUser } from './User.js';
 
@@ -88,11 +89,31 @@ export class MiAbuseUserReport {
 	})
 	public targetUserHost: string | null;
 
+	@ManyToOne(() => MiInstance, {
+		// TODO create a foreign key constraint after hazelnoot/labs/persisted-instance-blocks is merged
+		createForeignKeyConstraints: false,
+	})
+	@JoinColumn({
+		name: 'targetUserHost',
+		referencedColumnName: 'host',
+	})
+	public targetUserInstance: MiInstance | null;
+
 	@Index()
 	@Column('varchar', {
 		length: 128, nullable: true,
 		comment: '[Denormalized]',
 	})
 	public reporterHost: string | null;
+
+	@ManyToOne(() => MiInstance, {
+		// TODO create a foreign key constraint after hazelnoot/labs/persisted-instance-blocks is merged
+		createForeignKeyConstraints: false,
+	})
+	@JoinColumn({
+		name: 'reporterHost',
+		referencedColumnName: 'host',
+	})
+	public reporterInstance: MiInstance | null;
 	//#endregion
 }
diff --git a/packages/backend/src/models/Following.ts b/packages/backend/src/models/Following.ts
index 62cbc29f26..0aa1b13976 100644
--- a/packages/backend/src/models/Following.ts
+++ b/packages/backend/src/models/Following.ts
@@ -4,6 +4,7 @@
  */
 
 import { PrimaryColumn, Entity, Index, JoinColumn, Column, ManyToOne } from 'typeorm';
+import { MiInstance } from '@/models/Instance.js';
 import { id } from './util/id.js';
 import { MiUser } from './User.js';
 
@@ -66,6 +67,16 @@ export class MiFollowing {
 	})
 	public followerHost: string | null;
 
+	@ManyToOne(() => MiInstance, {
+		onDelete: 'CASCADE',
+	})
+	@JoinColumn({
+		name: 'followerHost',
+		foreignKeyConstraintName: 'FK_following_followerHost',
+		referencedColumnName: 'host',
+	})
+	public followerInstance: MiInstance | null;
+
 	@Column('varchar', {
 		length: 512, nullable: true,
 		comment: '[Denormalized]',
@@ -85,6 +96,16 @@ export class MiFollowing {
 	})
 	public followeeHost: string | null;
 
+	@ManyToOne(() => MiInstance, {
+		onDelete: 'CASCADE',
+	})
+	@JoinColumn({
+		name: 'followeeHost',
+		foreignKeyConstraintName: 'FK_following_followeeHost',
+		referencedColumnName: 'host',
+	})
+	public followeeInstance: MiInstance | null;
+
 	@Column('varchar', {
 		length: 512, nullable: true,
 		comment: '[Denormalized]',
diff --git a/packages/backend/src/models/Instance.ts b/packages/backend/src/models/Instance.ts
index c64ebb1b3b..0cde4b75fc 100644
--- a/packages/backend/src/models/Instance.ts
+++ b/packages/backend/src/models/Instance.ts
@@ -6,6 +6,7 @@
 import { Entity, PrimaryColumn, Index, Column } from 'typeorm';
 import { id } from './util/id.js';
 
+@Index('IDX_instance_host_key', { synchronize: false }) // ((lower(reverse("host"::text)) || '.'::text)
 @Entity('instance')
 export class MiInstance {
 	@PrimaryColumn(id())
@@ -98,6 +99,56 @@ export class MiInstance {
 	})
 	public suspensionState: 'none' | 'manuallySuspended' | 'goneSuspended' | 'autoSuspendedForNotResponding';
 
+	/**
+	 * True if this instance is blocked from federation.
+	 */
+	@Column('boolean', {
+		nullable: false,
+		default: false,
+		comment: 'True if this instance is blocked from federation.',
+	})
+	public isBlocked: boolean;
+
+	/**
+	 * True if this instance is allow-listed.
+	 */
+	@Column('boolean', {
+		nullable: false,
+		default: false,
+		comment: 'True if this instance is allow-listed.',
+	})
+	public isAllowListed: boolean;
+
+	/**
+	 * True if this instance is part of the local bubble.
+	 */
+	@Column('boolean', {
+		nullable: false,
+		default: false,
+		comment: 'True if this instance is part of the local bubble.',
+	})
+	public isBubbled: boolean;
+
+	/**
+	 * True if this instance is silenced.
+	 */
+	@Column('boolean', {
+		nullable: false,
+		default: false,
+		comment: 'True if this instance is silenced.',
+	})
+	public isSilenced: boolean;
+
+	/**
+	 * True if this instance is media-silenced.
+	 */
+	@Column('boolean', {
+		nullable: false,
+		default: false,
+		comment: 'True if this instance is media-silenced.',
+	})
+	public isMediaSilenced: boolean;
+
 	@Column('varchar', {
 		length: 64, nullable: true,
 		comment: 'The software of the Instance.',
diff --git a/packages/backend/src/models/Note.ts b/packages/backend/src/models/Note.ts
index ee2098216d..bbe183cfbb 100644
--- a/packages/backend/src/models/Note.ts
+++ b/packages/backend/src/models/Note.ts
@@ -5,12 +5,15 @@
 
 import { Entity, Index, JoinColumn, Column, PrimaryColumn, ManyToOne } from 'typeorm';
 import { noteVisibilities } from '@/types.js';
+import { MiInstance } from '@/models/Instance.js';
 import { id } from './util/id.js';
 import { MiUser } from './User.js';
 import { MiChannel } from './Channel.js';
 import type { MiDriveFile } from './DriveFile.js';
 
 @Index('IDX_724b311e6f883751f261ebe378', ['userId', 'id'])
+@Index('IDX_note_userHost_id', { synchronize: false }) // (userHost, id desc)
+@Index('IDX_note_for_timelines', { synchronize: false }) // (id desc, channelId, visibility, userHost)
 @Entity('note')
 export class MiNote {
 	@PrimaryColumn(id())
@@ -130,6 +133,7 @@ export class MiNote {
 	})
 	public uri: string | null;
 
+	@Index('IDX_note_url')
 	@Column('varchar', {
 		length: 512, nullable: true,
 		comment: 'The human readable url of a note. it will be null when the note is local.',
@@ -215,13 +219,22 @@ export class MiNote {
 	public processErrors: string[] | null;
 
 	//#region Denormalized fields
-	@Index()
 	@Column('varchar', {
 		length: 128, nullable: true,
 		comment: '[Denormalized]',
 	})
 	public userHost: string | null;
 
+	@ManyToOne(() => MiInstance, {
+		onDelete: 'CASCADE',
+	})
+	@JoinColumn({
+		name: 'userHost',
+		foreignKeyConstraintName: 'FK_note_userHost',
+		referencedColumnName: 'host',
+	})
+	public userInstance: MiInstance | null;
+
 	@Column({
 		...id(),
 		nullable: true,
@@ -235,6 +248,16 @@ export class MiNote {
 	})
 	public replyUserHost: string | null;
 
+	@ManyToOne(() => MiInstance, {
+		onDelete: 'CASCADE',
+	})
+	@JoinColumn({
+		name: 'replyUserHost',
+		foreignKeyConstraintName: 'FK_note_replyUserHost',
+		referencedColumnName: 'host',
+	})
+	public replyUserInstance: MiInstance | null;
+
 	@Column({
 		...id(),
 		nullable: true,
@@ -247,6 +270,16 @@ export class MiNote {
 		comment: '[Denormalized]',
 	})
 	public renoteUserHost: string | null;
+
+	@ManyToOne(() => MiInstance, {
+		onDelete: 'CASCADE',
+	})
+	@JoinColumn({
+		name: 'renoteUserHost',
+		foreignKeyConstraintName: 'FK_note_renoteUserHost',
+		referencedColumnName: 'host',
+	})
+	public renoteUserInstance: MiInstance | null;
 	//#endregion
 
 	constructor(data: Partial<MiNote>) {
diff --git a/packages/backend/src/models/User.ts b/packages/backend/src/models/User.ts
index 46f8e84a94..f40bb41a22 100644
--- a/packages/backend/src/models/User.ts
+++ b/packages/backend/src/models/User.ts
@@ -3,10 +3,12 @@
  * SPDX-License-Identifier: AGPL-3.0-only
  */
 
-import { Entity, Column, Index, OneToOne, JoinColumn, PrimaryColumn } from 'typeorm';
+import { Entity, Column, Index, OneToOne, JoinColumn, PrimaryColumn, ManyToOne } from 'typeorm';
 import { type UserUnsignedFetchOption, userUnsignedFetchOptions } from '@/const.js';
+import { MiInstance } from '@/models/Instance.js';
 import { id } from './util/id.js';
 import { MiDriveFile } from './DriveFile.js';
+import type { MiUserProfile } from './UserProfile.js';
 
 @Entity('user')
 @Index(['usernameLower', 'host'], { unique: true })
@@ -292,6 +294,16 @@ export class MiUser {
 	})
 	public host: string | null;
 
+	@ManyToOne(() => MiInstance, {
+		onDelete: 'CASCADE',
+	})
+	@JoinColumn({
+		name: 'host',
+		foreignKeyConstraintName: 'FK_user_host',
+		referencedColumnName: 'host',
+	})
+	public instance: MiInstance | null;
+
 	@Column('varchar', {
 		length: 512, nullable: true,
 		comment: 'The inbox URL of the User. It will be null if the origin of the user is local.',
@@ -378,6 +390,15 @@ export class MiUser {
 	})
 	public allowUnsignedFetch: UserUnsignedFetchOption;
 
+	@Column('text', {
+		name: 'attributionDomains',
+		array: true, default: '{}',
+	})
+	public attributionDomains: string[];
+
+	@OneToOne('user_profile', (profile: MiUserProfile) => profile.user)
+	public userProfile: MiUserProfile | null;
+
 	constructor(data: Partial<MiUser>) {
 		if (data == null) return;
 
diff --git a/packages/backend/src/models/UserProfile.ts b/packages/backend/src/models/UserProfile.ts
index 29c453dd71..6ee72e6ddd 100644
--- a/packages/backend/src/models/UserProfile.ts
+++ b/packages/backend/src/models/UserProfile.ts
@@ -17,7 +17,7 @@ export class MiUserProfile {
 	@PrimaryColumn(id())
 	public userId: MiUser['id'];
 
-	@OneToOne(type => MiUser, {
+	@OneToOne(() => MiUser, user => user.userProfile, {
 		onDelete: 'CASCADE',
 	})
 	@JoinColumn()
diff --git a/packages/backend/src/models/json-schema/federation-instance.ts b/packages/backend/src/models/json-schema/federation-instance.ts
index 57d4466ffa..fd6eddf594 100644
--- a/packages/backend/src/models/json-schema/federation-instance.ts
+++ b/packages/backend/src/models/json-schema/federation-instance.ts
@@ -135,5 +135,9 @@ export const packedFederationInstanceSchema = {
 			type: 'string',
 			optional: true, nullable: true,
 		},
+		isBubbled: {
+			type: 'boolean',
+			optional: false, nullable: false,
+		},
 	},
 } as const;
diff --git a/packages/backend/src/models/json-schema/user.ts b/packages/backend/src/models/json-schema/user.ts
index 964a179244..1678cab067 100644
--- a/packages/backend/src/models/json-schema/user.ts
+++ b/packages/backend/src/models/json-schema/user.ts
@@ -73,6 +73,16 @@ export const packedUserLiteSchema = {
 			type: 'string',
 			nullable: true, optional: false,
 		},
+		description: {
+			type: 'string',
+			nullable: true, optional: false,
+			example: 'Hi masters, I am Ai!',
+		},
+		createdAt: {
+			type: 'string',
+			nullable: false, optional: false,
+			format: 'date-time',
+		},
 		avatarDecorations: {
 			type: 'array',
 			nullable: false, optional: false,
@@ -200,6 +210,10 @@ export const packedUserLiteSchema = {
 					type: 'string',
 					nullable: true, optional: false,
 				},
+				isSilenced: {
+					type: 'boolean',
+					nullable: false, optional: false,
+				},
 			},
 		},
 		emojis: {
@@ -236,6 +250,14 @@ export const packedUserLiteSchema = {
 				},
 			},
 		},
+		attributionDomains: {
+			type: 'array',
+			nullable: false, optional: false,
+			items: {
+				type: 'string',
+				nullable: false, optional: false,
+			},
+		},
 	},
 } as const;
 
@@ -266,11 +288,6 @@ export const packedUserDetailedNotMeOnlySchema = {
 				nullable: false, optional: false,
 			},
 		},
-		createdAt: {
-			type: 'string',
-			nullable: false, optional: false,
-			format: 'date-time',
-		},
 		updatedAt: {
 			type: 'string',
 			nullable: true, optional: false,
@@ -312,11 +329,6 @@ export const packedUserDetailedNotMeOnlySchema = {
 			nullable: false, optional: false,
 			example: false,
 		},
-		description: {
-			type: 'string',
-			nullable: true, optional: false,
-			example: 'Hi masters, I am Ai!',
-		},
 		location: {
 			type: 'string',
 			nullable: true, optional: false,