From ca32c231d0aaab9dacaa88a0b0564d626827e797 Mon Sep 17 00:00:00 2001
From: piuvas <mail@piuvas.net>
Date: Mon, 26 May 2025 22:44:29 -0300
Subject: initial link attributions work.

---
 packages/backend/src/models/User.ts             | 6 ++++++
 packages/backend/src/models/json-schema/user.ts | 7 +++++++
 2 files changed, 13 insertions(+)

(limited to 'packages/backend/src/models')

diff --git a/packages/backend/src/models/User.ts b/packages/backend/src/models/User.ts
index 46f8e84a94..d5f572a879 100644
--- a/packages/backend/src/models/User.ts
+++ b/packages/backend/src/models/User.ts
@@ -378,6 +378,12 @@ export class MiUser {
 	})
 	public allowUnsignedFetch: UserUnsignedFetchOption;
 
+	@Column('varchar', {
+		name: 'attributionDomains',
+		length: 128, array: true, default: '{}',
+	})
+	public attributionDomains: string[];
+
 	constructor(data: Partial<MiUser>) {
 		if (data == null) return;
 
diff --git a/packages/backend/src/models/json-schema/user.ts b/packages/backend/src/models/json-schema/user.ts
index 964a179244..9bfb57fc66 100644
--- a/packages/backend/src/models/json-schema/user.ts
+++ b/packages/backend/src/models/json-schema/user.ts
@@ -236,6 +236,13 @@ export const packedUserLiteSchema = {
 				},
 			},
 		},
+		attributionDomains: {
+			type: 'object',
+			nullable: false, optional: false,
+			additionalProperties: {
+				type: 'string',
+			},
+		},
 	},
 } as const;
 
-- 
cgit v1.2.3-freya


From 5545ddf9ddefa5620c58ea34b2e6fdf18fdac30a Mon Sep 17 00:00:00 2001
From: piuvas <mail@piuvas.net>
Date: Tue, 27 May 2025 13:25:06 -0300
Subject: requested changes.

---
 locales/index.d.ts                                 |  4 +--
 packages/backend/src/models/json-schema/user.ts    |  2 +-
 .../backend/src/server/web/UrlPreviewService.ts    | 18 +++++-----
 packages/frontend/src/components/MkUrlPreview.vue  | 41 ++++++++++------------
 packages/frontend/src/pages/settings/profile.vue   |  2 --
 sharkey-locales/en-US.yml                          |  2 +-
 sharkey-locales/pt-PT.yml                          |  2 +-
 7 files changed, 32 insertions(+), 39 deletions(-)

(limited to 'packages/backend/src/models')

diff --git a/locales/index.d.ts b/locales/index.d.ts
index 1070318fa2..f809613e7a 100644
--- a/locales/index.d.ts
+++ b/locales/index.d.ts
@@ -13138,9 +13138,9 @@ export interface Locale extends ILocale {
      */
     "attributionDomainsDescription": string;
     /**
-     * Written by
+     * Written by {user}
      */
-    "writtenBy": string;
+    "writtenBy": ParameterizedString<"user">;
 }
 declare const locales: {
     [lang: string]: Locale;
diff --git a/packages/backend/src/models/json-schema/user.ts b/packages/backend/src/models/json-schema/user.ts
index 9bfb57fc66..82d354cfd0 100644
--- a/packages/backend/src/models/json-schema/user.ts
+++ b/packages/backend/src/models/json-schema/user.ts
@@ -237,7 +237,7 @@ export const packedUserLiteSchema = {
 			},
 		},
 		attributionDomains: {
-			type: 'object',
+			type: 'array',
 			nullable: false, optional: false,
 			additionalProperties: {
 				type: 'string',
diff --git a/packages/backend/src/server/web/UrlPreviewService.ts b/packages/backend/src/server/web/UrlPreviewService.ts
index b50e5d49fd..ccf8fa342d 100644
--- a/packages/backend/src/server/web/UrlPreviewService.ts
+++ b/packages/backend/src/server/web/UrlPreviewService.ts
@@ -28,7 +28,7 @@ import { ApNoteService } from '@/core/activitypub/models/ApNoteService.js';
 import { AuthenticateService, AuthenticationError } from '@/server/api/AuthenticateService.js';
 import { SkRateLimiterService } from '@/server/SkRateLimiterService.js';
 import { BucketRateLimit, Keyed, sendRateLimitHeaders } from '@/misc/rate-limit-utils.js';
-import type { MiLocalUser } from '@/models/User.js';
+import type { MiLocalUser, MiUser } from '@/models/User.js';
 import { getIpHash } from '@/misc/get-ip-hash.js';
 import { isRetryableError } from '@/misc/is-retryable-error.js';
 import type { FastifyRequest, FastifyReply } from 'fastify';
@@ -36,10 +36,7 @@ import type { FastifyRequest, FastifyReply } from 'fastify';
 export type LocalSummalyResult = SummalyResult & {
 	haveNoteLocally?: boolean;
 	linkAttribution?: {
-		name: string,
-		username: string,
-		avatarUrl: string,
-		avatarBlurhash: string,
+		user: MiUser,
 	}
 };
 
@@ -448,6 +445,10 @@ export class UrlPreviewService {
 		}
 
 		const array = fediverseCreator.split('@');
+
+		// make sure we only have username@host.
+		if (array.length !== 2) return;
+
 		const username = array[0].toLowerCase();
 		let host: string | null = array[1];
 		if (host.toLowerCase() === this.config.host) {
@@ -459,14 +460,11 @@ export class UrlPreviewService {
 			const attributionDomains = user.attributionDomains;
 			if (attributionDomains.some(x => `.${url.host.toLowerCase()}`.endsWith(`.${x}`))) {
 				summary.linkAttribution = {
-					name: user.name ?? user.username,
-					username: fediverseCreator,
-					avatarUrl: user.avatarUrl ?? '',
-					avatarBlurhash: user.avatarBlurhash ?? '',
+					user: user,
 				};
 			}
 		} catch {
-			console.warn('user not found: ' + fediverseCreator);
+			this.logger.warn('user not found: ' + fediverseCreator);
 		}
 	}
 
diff --git a/packages/frontend/src/components/MkUrlPreview.vue b/packages/frontend/src/components/MkUrlPreview.vue
index ca8f6848e5..0cba73ef05 100644
--- a/packages/frontend/src/components/MkUrlPreview.vue
+++ b/packages/frontend/src/components/MkUrlPreview.vue
@@ -65,13 +65,14 @@ SPDX-License-Identifier: AGPL-3.0-only
 			</footer>
 		</article>
 	</component>
-	<footer v-if="linkAttribution" :class="$style.footer" style="text-align: right">
-		<a :href="'/@' + linkAttribution.username">
-			<p :class="$style.linkAttribution">{{ i18n.ts.writtenBy }}</p>
-			<MkImgWithBlurhash :class="$style.linkAttributionIcon" :src="linkAttribution.avatarUrl" :hash="linkAttribution.avatarBlurhash" :cover="true" :onlyAvgColor="true"/>
-			<b :class="$style.linkAttribution" style="color: var(--MI_THEME-accent)">{{ linkAttribution.name }}</b>
-		</a>
-	</footer>
+	<I18n v-if="linkAttribution" :src="i18n.ts.writtenBy" :class="$style.linkAttribution" tag="p">
+		<template #user>
+			<MkA v-user-preview="linkAttribution.user.id" :to="userPage(linkAttribution.user)">
+				<MkAvatar :class="$style.linkAttributionIcon" :user="linkAttribution.user"/>
+				<MkUserName :user="linkAttribution.user" style="color: var(--MI_THEME-accent)"/>
+			</MkA>
+		</template>
+	</I18n>
 	<template v-if="showActions">
 		<div v-if="tweetId" :class="$style.action">
 			<MkButton :small="true" inline @click="tweetExpanded = true">
@@ -114,6 +115,7 @@ import { misskeyApi } from '@/utility/misskey-api.js';
 import { warningExternalWebsite } from '@/utility/warning-external-website.js';
 import DynamicNoteSimple from '@/components/DynamicNoteSimple.vue';
 import { $i } from '@/i';
+import { userPage } from '@/filters/user.js';
 
 type SummalyResult = Awaited<ReturnType<typeof summaly>>;
 
@@ -155,10 +157,7 @@ const player = ref<SummalyResult['player']>({
 	allow: [],
 });
 const linkAttribution = ref<{
-	name: string,
-	username: string,
-	avatarUrl: string,
-	avatarBlurhash: string,
+	user: Misskey.entities.User,
 } | null>(null);
 const playerEnabled = ref(false);
 const tweetId = ref<string | null>(null);
@@ -238,10 +237,7 @@ function refresh(withFetch = false) {
 		.then(async (info: SummalyResult & {
 			haveNoteLocally?: boolean,
 			linkAttribution?: {
-				name: string,
-				username: string,
-				avatarUrl: string,
-				avatarBlurhash: string,
+				user: Misskey.entities.User
 			}
 		} | null) => {
 			unknownUrl.value = info == null;
@@ -420,11 +416,11 @@ refresh();
 
 .linkAttributionIcon {
 	display: inline-block;
-	width: 1em;
-	height: 1em;
-	margin-left: 0.5em;
+	width: 16px;
+	height: 16px;
+	margin-left: 0.25em;
 	margin-right: 0.25em;
-	vertical-align: top;
+	vertical-align: middle;
 	border-radius: 50%;
 	* {
 		border-radius: 4px;
@@ -432,11 +428,12 @@ refresh();
 }
 
 .linkAttribution {
+	width: 100%;
 	font-size: 0.8em;
 	display: inline-block;
-	margin: 0;
-	line-height: 16px;
-	vertical-align: top;
+	margin: auto;
+	padding-top: 0.5em;
+	text-align: right;
 }
 
 .action {
diff --git a/packages/frontend/src/pages/settings/profile.vue b/packages/frontend/src/pages/settings/profile.vue
index 9dc7398062..4cc13a2552 100644
--- a/packages/frontend/src/pages/settings/profile.vue
+++ b/packages/frontend/src/pages/settings/profile.vue
@@ -220,7 +220,6 @@ const profile = reactive({
 	isBot: $i.isBot ?? false,
 	isCat: $i.isCat ?? false,
 	speakAsCat: $i.speakAsCat ?? false,
-	attributionDomains: $i.attributionDomains,
 });
 
 watch(() => profile, () => {
@@ -280,7 +279,6 @@ function save() {
 		isBot: !!profile.isBot,
 		isCat: !!profile.isCat,
 		speakAsCat: !!profile.speakAsCat,
-		attributionDomains: !!profile.attributionDomains,
 	}, undefined, {
 		'0b3f9f6a-2f4d-4b1f-9fb4-49d3a2fd7191': {
 			title: i18n.ts.yourNameContainsProhibitedWords,
diff --git a/sharkey-locales/en-US.yml b/sharkey-locales/en-US.yml
index bfd0613cc7..0ad01b96be 100644
--- a/sharkey-locales/en-US.yml
+++ b/sharkey-locales/en-US.yml
@@ -592,4 +592,4 @@ translationTimeoutCaption: "Timeout in milliseconds for translation API requests
 
 attributionDomains: "Attribution Domains"
 attributionDomainsDescription: "A list of domains whose content can be attributed to you on link previews, separated by new-line. Any subdomain will also be valid. The following needs to be on the webpage:"
-writtenBy: "Written by"
+writtenBy: "Written by {user}"
diff --git a/sharkey-locales/pt-PT.yml b/sharkey-locales/pt-PT.yml
index e522125e6c..7220cd2b59 100644
--- a/sharkey-locales/pt-PT.yml
+++ b/sharkey-locales/pt-PT.yml
@@ -9,4 +9,4 @@ pinnedOnly: "Fixado"
 blockingYou: "Bloqueando você"
 attributionDomains: "Domínios de Atribuição"
 attributionDomainsDescription: "Uma lista de domínios cujo conteúdo pode ser atribuído a você em prévias de link, separadas por linha. Qualquer subdomínio também será válido. O código seguinte precisa estar presente na página:"
-writtenBy: "Escrito por"
+writtenBy: "Escrito por {user}"
-- 
cgit v1.2.3-freya


From eb88920f41ec98b073a53dce25a494ed03969d29 Mon Sep 17 00:00:00 2001
From: piuvas <mail@piuvas.net>
Date: Wed, 28 May 2025 19:55:40 -0300
Subject: requested changes.

---
 packages/backend/src/core/activitypub/models/ApPersonService.ts | 4 ++--
 packages/backend/src/models/json-schema/user.ts                 | 3 ++-
 packages/frontend/src/components/MkUrlPreview.vue               | 9 +++++++--
 3 files changed, 11 insertions(+), 5 deletions(-)

(limited to 'packages/backend/src/models')

diff --git a/packages/backend/src/core/activitypub/models/ApPersonService.ts b/packages/backend/src/core/activitypub/models/ApPersonService.ts
index 744b339254..bf5a5e01d5 100644
--- a/packages/backend/src/core/activitypub/models/ApPersonService.ts
+++ b/packages/backend/src/core/activitypub/models/ApPersonService.ts
@@ -433,7 +433,7 @@ export class ApPersonService implements OnModuleInit, OnApplicationShutdown {
 					makeNotesFollowersOnlyBefore: (person as any).makeNotesFollowersOnlyBefore ?? null,
 					makeNotesHiddenBefore: (person as any).makeNotesHiddenBefore ?? null,
 					emojis,
-					attributionDomains: (person as any).attributionDomains,
+					attributionDomains: person.attributionDomains ?? [],
 				})) as MiRemoteUser;
 
 				let _description: string | null = null;
@@ -617,7 +617,7 @@ export class ApPersonService implements OnModuleInit, OnApplicationShutdown {
 			// We use "!== false" to handle incorrect types, missing / null values, and "default to true" logic.
 			hideOnlineStatus: person.hideOnlineStatus !== false,
 			isExplorable: person.discoverable !== false,
-			attributionDomains: person.attributionDomains,
+			attributionDomains: person.attributionDomains ?? [],
 			...(await this.resolveAvatarAndBanner(exist, person.icon, person.image, person.backgroundUrl).catch(() => ({}))),
 		} as Partial<MiRemoteUser> & Pick<MiRemoteUser, 'isBot' | 'isCat' | 'speakAsCat' | 'isLocked' | 'movedToUri' | 'alsoKnownAs' | 'isExplorable'>;
 
diff --git a/packages/backend/src/models/json-schema/user.ts b/packages/backend/src/models/json-schema/user.ts
index 82d354cfd0..96369c3ad9 100644
--- a/packages/backend/src/models/json-schema/user.ts
+++ b/packages/backend/src/models/json-schema/user.ts
@@ -239,8 +239,9 @@ export const packedUserLiteSchema = {
 		attributionDomains: {
 			type: 'array',
 			nullable: false, optional: false,
-			additionalProperties: {
+			items: {
 				type: 'string',
+				nullable: false, optional: false,
 			},
 		},
 	},
diff --git a/packages/frontend/src/components/MkUrlPreview.vue b/packages/frontend/src/components/MkUrlPreview.vue
index 7029e324f4..69a1540600 100644
--- a/packages/frontend/src/components/MkUrlPreview.vue
+++ b/packages/frontend/src/components/MkUrlPreview.vue
@@ -259,8 +259,13 @@ function refresh(withFetch = false) {
 			activityPub.value = info?.activityPub ?? null;
 			linkAttribution.value = info?.linkAttribution ?? null;
 			if (linkAttribution.value) {
-				misskeyApi('users/show', { userId: linkAttribution.value.userId })
-					.then(u => attributionUser.value = u);
+				try {
+					const response = await misskeyApi('users/show', { userId: linkAttribution.value.userId });
+					attributionUser.value = response;
+				} catch {
+					// makes the loading ellipsis vanish.
+					linkAttribution.value = null;
+				}
 			}
 
 			theNote.value = null;
-- 
cgit v1.2.3-freya


From 566f92ab735b489e660bda1ded6106673ab1d892 Mon Sep 17 00:00:00 2001
From: Hazelnoot <acomputerdog@gmail.com>
Date: Sat, 24 May 2025 18:28:07 -0400
Subject: add IDX_instance_host_key

---
 .../1748104955717-index_IDX_instance_host_key.js     | 20 ++++++++++++++++++++
 packages/backend/src/models/Instance.ts              |  1 +
 2 files changed, 21 insertions(+)
 create mode 100644 packages/backend/migration/1748104955717-index_IDX_instance_host_key.js

(limited to 'packages/backend/src/models')

diff --git a/packages/backend/migration/1748104955717-index_IDX_instance_host_key.js b/packages/backend/migration/1748104955717-index_IDX_instance_host_key.js
new file mode 100644
index 0000000000..3c3b779298
--- /dev/null
+++ b/packages/backend/migration/1748104955717-index_IDX_instance_host_key.js
@@ -0,0 +1,20 @@
+/*
+ * SPDX-FileCopyrightText: hazelnoot and other Sharkey contributors
+ * SPDX-License-Identifier: AGPL-3.0-only
+ */
+
+export class IndexIDXInstanceHostKey1748104955717 {
+	name = 'IndexIDXInstanceHostKey1748104955717'
+
+	async up(queryRunner) {
+		await queryRunner.query(`CREATE UNIQUE INDEX "IDX_instance_host_key" ON "instance" (((lower(reverse("host")) || '.')::text) text_pattern_ops)`);
+
+		// Flush all cached Linear Scan Plans and redo statistics for expression index
+		// this is important for Postgres to learn that even in highly complex queries, using this index first can reduce the result set significantly
+		await queryRunner.query(`ANALYZE "instance"`);
+	}
+
+	async down(queryRunner) {
+		await queryRunner.query(`DROP INDEX "IDX_instance_host_key"`);
+	}
+}
diff --git a/packages/backend/src/models/Instance.ts b/packages/backend/src/models/Instance.ts
index c64ebb1b3b..5d8108d423 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 })
 @Entity('instance')
 export class MiInstance {
 	@PrimaryColumn(id())
-- 
cgit v1.2.3-freya


From 45e5749cca8ddf11ba01ab3d669cff9517045a3f Mon Sep 17 00:00:00 2001
From: Hazelnoot <acomputerdog@gmail.com>
Date: Sat, 24 May 2025 18:28:21 -0400
Subject: add instance properties for persisted block data

---
 .../1748105111513-add_instance_block_columns.js    | 89 ++++++++++++++++++++++
 packages/backend/src/models/Instance.ts            | 50 ++++++++++++
 2 files changed, 139 insertions(+)
 create mode 100644 packages/backend/migration/1748105111513-add_instance_block_columns.js

(limited to 'packages/backend/src/models')

diff --git a/packages/backend/migration/1748105111513-add_instance_block_columns.js b/packages/backend/migration/1748105111513-add_instance_block_columns.js
new file mode 100644
index 0000000000..c9087604e5
--- /dev/null
+++ b/packages/backend/migration/1748105111513-add_instance_block_columns.js
@@ -0,0 +1,89 @@
+/*
+ * SPDX-FileCopyrightText: hazelnoot and other Sharkey contributors
+ * SPDX-License-Identifier: AGPL-3.0-only
+ */
+
+/**
+ * @typedef {import('typeorm').MigrationInterface} MigrationInterface
+ * @typedef {{ blockedHosts: string[], silencedHosts: string[], mediaSilencedHosts: string[], federationHosts: string[], bubbleInstances: string[] }} Meta
+ */
+
+/**
+ * @class
+ * @implements {MigrationInterface}
+ */
+export class AddInstanceBlockColumns1748105111513 {
+	name = 'AddInstanceBlockColumns1748105111513'
+
+	async up(queryRunner) {
+		// Schema migration
+		await queryRunner.query(`ALTER TABLE "instance" ADD "isBlocked" boolean NOT NULL DEFAULT false`);
+		await queryRunner.query(`COMMENT ON COLUMN "instance"."isBlocked" IS 'True if this instance is blocked from federation.'`);
+		await queryRunner.query(`ALTER TABLE "instance" ADD "isAllowListed" boolean NOT NULL DEFAULT false`);
+		await queryRunner.query(`COMMENT ON COLUMN "instance"."isAllowListed" IS 'True if this instance is allow-listed.'`);
+		await queryRunner.query(`ALTER TABLE "instance" ADD "isBubbled" boolean NOT NULL DEFAULT false`);
+		await queryRunner.query(`COMMENT ON COLUMN "instance"."isBubbled" IS 'True if this instance is part of the local bubble.'`);
+		await queryRunner.query(`ALTER TABLE "instance" ADD "isSilenced" boolean NOT NULL DEFAULT false`);
+		await queryRunner.query(`COMMENT ON COLUMN "instance"."isSilenced" IS 'True if this instance is silenced.'`);
+		await queryRunner.query(`ALTER TABLE "instance" ADD "isMediaSilenced" boolean NOT NULL DEFAULT false`);
+		await queryRunner.query(`COMMENT ON COLUMN "instance"."isMediaSilenced" IS 'True if this instance is media-silenced.'`);
+
+		// Data migration
+		/** @type {Meta[]} */
+		const metas = await queryRunner.query(`SELECT "blockedHosts", "silencedHosts", "mediaSilencedHosts", "federationHosts", "bubbleInstances" FROM "meta"`);
+		if (metas.length > 0) {
+			/** @type {Meta} */
+			const meta = metas[0];
+
+			// Blocked hosts
+			if (meta.blockedHosts.length > 0) {
+				const pattern = buildPatterns(meta.blockedHosts);
+				await queryRunner.query(`UPDATE "instance" SET "isBlocked" = true WHERE ((lower(reverse("host")) || '.')::text) LIKE ANY ${pattern}`);
+			}
+
+			// Silenced hosts
+			if (meta.silencedHosts.length > 0) {
+				const pattern = buildPatterns(meta.silencedHosts);
+				await queryRunner.query(`UPDATE "instance" SET "isSilenced" = true WHERE ((lower(reverse("host")) || '.')::text) LIKE ANY ${pattern}`);
+			}
+
+			// Media silenced hosts
+			if (meta.mediaSilencedHosts.length > 0) {
+				const pattern = buildPatterns(meta.mediaSilencedHosts);
+				await queryRunner.query(`UPDATE "instance" SET "isMediaSilenced" = true WHERE ((lower(reverse("host")) || '.')::text) LIKE ANY ${pattern}`);
+			}
+
+			// Allow-listed hosts
+			if (meta.federationHosts.length > 0) {
+				const pattern = buildPatterns(meta.federationHosts);
+				await queryRunner.query(`UPDATE "instance" SET "isAllowListed" = true WHERE ((lower(reverse("host")) || '.')::text) LIKE ANY ${pattern}`);
+			}
+
+			// Bubbled hosts
+			if (meta.bubbleInstances.length > 0) {
+				const pattern = buildPatterns(meta.bubbleInstances);
+				await queryRunner.query(`UPDATE "instance" SET "isBubbled" = true WHERE ((lower(reverse("host")) || '.')::text) LIKE ANY ${pattern}`);
+			}
+		}
+	}
+
+	async down(queryRunner) {
+		await queryRunner.query(`ALTER TABLE "instance" DROP COLUMN "isMediaSilenced"`);
+		await queryRunner.query(`ALTER TABLE "instance" DROP COLUMN "isSilenced"`);
+		await queryRunner.query(`ALTER TABLE "instance" DROP COLUMN "isBubbled"`);
+		await queryRunner.query(`ALTER TABLE "instance" DROP COLUMN "isAllowListed"`);
+		await queryRunner.query(`ALTER TABLE "instance" DROP COLUMN "isBlocked"`);
+	}
+}
+
+/**
+ * @param {string[]} input
+ * @returns {string}
+ */
+function buildPatterns(input) {
+	const strings = input
+		.map(i => i.toLowerCase().split('').reverse().join('') + '.%')
+		.map(i => `'${i}'`)
+		.join(', ');
+	return `(array[${strings}]::text[])`;
+}
diff --git a/packages/backend/src/models/Instance.ts b/packages/backend/src/models/Instance.ts
index 5d8108d423..0022e58933 100644
--- a/packages/backend/src/models/Instance.ts
+++ b/packages/backend/src/models/Instance.ts
@@ -99,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.',
-- 
cgit v1.2.3-freya


From 59099a2b2a1d570334df3a0770450c8b4b1522c6 Mon Sep 17 00:00:00 2001
From: Hazelnoot <acomputerdog@gmail.com>
Date: Sat, 24 May 2025 19:10:41 -0400
Subject: add foreign keys to note/user where instance is referenced

---
 .../1748128176881-add_instance_foreign_keys.js     | 30 +++++++++++++++++++++
 packages/backend/src/models/Note.ts                | 31 ++++++++++++++++++++++
 packages/backend/src/models/User.ts                | 13 ++++++++-
 3 files changed, 73 insertions(+), 1 deletion(-)
 create mode 100644 packages/backend/migration/1748128176881-add_instance_foreign_keys.js

(limited to 'packages/backend/src/models')

diff --git a/packages/backend/migration/1748128176881-add_instance_foreign_keys.js b/packages/backend/migration/1748128176881-add_instance_foreign_keys.js
new file mode 100644
index 0000000000..da2de14f18
--- /dev/null
+++ b/packages/backend/migration/1748128176881-add_instance_foreign_keys.js
@@ -0,0 +1,30 @@
+/*
+ * SPDX-FileCopyrightText: hazelnoot and other Sharkey contributors
+ * SPDX-License-Identifier: AGPL-3.0-only
+ */
+
+/**
+ * @typedef {import('typeorm').MigrationInterface} MigrationInterface
+ */
+
+/**
+ * @class
+ * @implements {MigrationInterface}
+ */
+export class AddInstanceForeignKeys1748128176881 {
+	name = 'AddInstanceForeignKeys1748128176881'
+
+	async up(queryRunner) {
+		await queryRunner.query(`ALTER TABLE "user" ADD CONSTRAINT "FK_user_host" FOREIGN KEY ("host") REFERENCES "instance"("host") ON DELETE CASCADE ON UPDATE NO ACTION`);
+		await queryRunner.query(`ALTER TABLE "note" ADD CONSTRAINT "FK_note_userHost" FOREIGN KEY ("userHost") REFERENCES "instance"("host") ON DELETE CASCADE ON UPDATE NO ACTION`);
+		await queryRunner.query(`ALTER TABLE "note" ADD CONSTRAINT "FK_note_replyUserHost" FOREIGN KEY ("replyUserHost") REFERENCES "instance"("host") ON DELETE CASCADE ON UPDATE NO ACTION`);
+		await queryRunner.query(`ALTER TABLE "note" ADD CONSTRAINT "FK_note_renoteUserHost" FOREIGN KEY ("renoteUserHost") REFERENCES "instance"("host") ON DELETE CASCADE ON UPDATE NO ACTION`);
+	}
+
+	async down(queryRunner) {
+		await queryRunner.query(`ALTER TABLE "note" DROP CONSTRAINT "FK_note_renoteUserHost"`);
+		await queryRunner.query(`ALTER TABLE "note" DROP CONSTRAINT "FK_note_replyUserHost"`);
+		await queryRunner.query(`ALTER TABLE "note" DROP CONSTRAINT "FK_note_userHost"`);
+		await queryRunner.query(`ALTER TABLE "user" DROP CONSTRAINT "FK_user_host"`);
+	}
+}
diff --git a/packages/backend/src/models/Note.ts b/packages/backend/src/models/Note.ts
index ee2098216d..fa5839b6ec 100644
--- a/packages/backend/src/models/Note.ts
+++ b/packages/backend/src/models/Note.ts
@@ -5,6 +5,7 @@
 
 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';
@@ -222,6 +223,16 @@ export class MiNote {
 	})
 	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 +246,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 +268,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..55b8f4f4f0 100644
--- a/packages/backend/src/models/User.ts
+++ b/packages/backend/src/models/User.ts
@@ -3,8 +3,9 @@
  * 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';
 
@@ -292,6 +293,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.',
-- 
cgit v1.2.3-freya


From 51128028f47b9c1b13966c0dbbb0c185e7f91c32 Mon Sep 17 00:00:00 2001
From: Hazelnoot <acomputerdog@gmail.com>
Date: Sat, 24 May 2025 22:00:57 -0400
Subject: add foreign keys to following where instance is referenced

---
 ...83887-add_instance_foreign_keys_to_following.js | 26 ++++++++++++++++++++++
 packages/backend/src/models/Following.ts           | 21 +++++++++++++++++
 2 files changed, 47 insertions(+)
 create mode 100644 packages/backend/migration/1748137683887-add_instance_foreign_keys_to_following.js

(limited to 'packages/backend/src/models')

diff --git a/packages/backend/migration/1748137683887-add_instance_foreign_keys_to_following.js b/packages/backend/migration/1748137683887-add_instance_foreign_keys_to_following.js
new file mode 100644
index 0000000000..8f4a977ff5
--- /dev/null
+++ b/packages/backend/migration/1748137683887-add_instance_foreign_keys_to_following.js
@@ -0,0 +1,26 @@
+/*
+ * SPDX-FileCopyrightText: hazelnoot and other Sharkey contributors
+ * SPDX-License-Identifier: AGPL-3.0-only
+ */
+
+/**
+ * @typedef {import('typeorm').MigrationInterface} MigrationInterface
+ */
+
+/**
+ * @class
+ * @implements {MigrationInterface}
+ */
+export class AddInstanceForeignKeysToFollowing1748137683887 {
+	name = 'AddInstanceForeignKeysToFollowing1748137683887'
+
+	async up(queryRunner) {
+		await queryRunner.query(`ALTER TABLE "following" ADD CONSTRAINT "FK_following_followerHost" FOREIGN KEY ("followerHost") REFERENCES "instance"("host") ON DELETE CASCADE ON UPDATE NO ACTION`);
+		await queryRunner.query(`ALTER TABLE "following" ADD CONSTRAINT "FK_following_followeeHost" FOREIGN KEY ("followeeHost") REFERENCES "instance"("host") ON DELETE CASCADE ON UPDATE NO ACTION`);
+	}
+
+	async down(queryRunner) {
+		await queryRunner.query(`ALTER TABLE "following" DROP CONSTRAINT "FK_following_followeeHost"`);
+		await queryRunner.query(`ALTER TABLE "following" DROP CONSTRAINT "FK_following_followerHost"`);
+	}
+}
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]',
-- 
cgit v1.2.3-freya


From 979c7628b1d2b21bd9dd9d13ec0110bde883f074 Mon Sep 17 00:00:00 2001
From: Hazelnoot <acomputerdog@gmail.com>
Date: Thu, 29 May 2025 22:20:21 -0400
Subject: disable status badge strip in admin-user and instance-info

---
 locales/index.d.ts                                 |  28 ++++++
 packages/backend/src/core/UtilityService.ts        |   9 ++
 .../src/core/entities/InstanceEntityService.ts     |   1 +
 .../src/models/json-schema/federation-instance.ts  |   4 +
 .../src/server/api/endpoints/admin/show-user.ts    |   6 ++
 packages/frontend/src/pages/admin-user.vue         | 103 +++++++++++++++++++--
 packages/frontend/src/pages/instance-info.vue      |  58 +++++++++++-
 packages/misskey-js/src/autogen/types.ts           |   2 +
 sharkey-locales/en-US.yml                          |   9 +-
 9 files changed, 211 insertions(+), 9 deletions(-)

(limited to 'packages/backend/src/models')

diff --git a/locales/index.d.ts b/locales/index.d.ts
index 2392d51c45..2f3bc664ff 100644
--- a/locales/index.d.ts
+++ b/locales/index.d.ts
@@ -13145,10 +13145,38 @@ export interface Locale extends ILocale {
      * Last posted: {at}
      */
     "lastPosted": ParameterizedString<"at">;
+    /**
+     * NSFW
+     */
+    "nsfw": string;
     /**
      * Raw
      */
     "raw": string;
+    /**
+     * CW
+     */
+    "cw": string;
+    /**
+     * Media Silenced
+     */
+    "mediaSilenced": string;
+    /**
+     * Bubble
+     */
+    "bubble": string;
+    /**
+     * Verified
+     */
+    "verified": string;
+    /**
+     * Not Verified
+     */
+    "notVerified": string;
+    /**
+     * Hibernated
+     */
+    "hibernated": string;
 }
 declare const locales: {
     [lang: string]: Locale;
diff --git a/packages/backend/src/core/UtilityService.ts b/packages/backend/src/core/UtilityService.ts
index 170afc72dc..ee17906d55 100644
--- a/packages/backend/src/core/UtilityService.ts
+++ b/packages/backend/src/core/UtilityService.ts
@@ -67,6 +67,15 @@ export class UtilityService {
 		return silencedHosts.some(x => `.${host.toLowerCase()}`.endsWith(`.${x}`));
 	}
 
+	@bindThis
+	public isBubbledHost(host: string | null): boolean {
+		if (host == null) return false;
+
+		// TODO remove null conditional after merging lab/persisted-instance-blocks
+		// eslint-disable-next-line @typescript-eslint/no-unnecessary-condition
+		return this.meta.bubbleInstances?.includes(host);
+	}
+
 	@bindThis
 	public concatNoteContentsForKeyWordCheck(content: {
 		cw?: string | null;
diff --git a/packages/backend/src/core/entities/InstanceEntityService.ts b/packages/backend/src/core/entities/InstanceEntityService.ts
index fcc9bed3bd..08a7765d40 100644
--- a/packages/backend/src/core/entities/InstanceEntityService.ts
+++ b/packages/backend/src/core/entities/InstanceEntityService.ts
@@ -62,6 +62,7 @@ export class InstanceEntityService {
 			rejectReports: instance.rejectReports,
 			rejectQuotes: instance.rejectQuotes,
 			moderationNote: iAmModerator ? instance.moderationNote : null,
+			isBubbled: this.utilityService.isBubbledHost(instance.host),
 		};
 	}
 
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/server/api/endpoints/admin/show-user.ts b/packages/backend/src/server/api/endpoints/admin/show-user.ts
index 1579719246..6a77fc177f 100644
--- a/packages/backend/src/server/api/endpoints/admin/show-user.ts
+++ b/packages/backend/src/server/api/endpoints/admin/show-user.ts
@@ -122,6 +122,10 @@ export const meta = {
 				type: 'boolean',
 				optional: false, nullable: false,
 			},
+			isAdministrator: {
+				type: 'boolean',
+				optional: false, nullable: false,
+			},
 			isSystem: {
 				type: 'boolean',
 				optional: false, nullable: false,
@@ -257,6 +261,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 			}
 
 			const isModerator = await this.roleService.isModerator(user);
+			const isAdministrator = await this.roleService.isAdministrator(user);
 			const isSilenced = user.isSilenced || !(await this.roleService.getUserPolicies(user.id)).canPublicNote;
 
 			const _me = await this.usersRepository.findOneByOrFail({ id: me.id });
@@ -289,6 +294,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 				mutedInstances: profile.mutedInstances,
 				notificationRecieveConfig: profile.notificationRecieveConfig,
 				isModerator: isModerator,
+				isAdministrator: isAdministrator,
 				isSystem: isSystemAccount(user),
 				isSilenced: isSilenced,
 				isSuspended: user.isSuspended,
diff --git a/packages/frontend/src/pages/admin-user.vue b/packages/frontend/src/pages/admin-user.vue
index 63352f6ca3..f7db436bc8 100644
--- a/packages/frontend/src/pages/admin-user.vue
+++ b/packages/frontend/src/pages/admin-user.vue
@@ -20,16 +20,11 @@ SPDX-License-Identifier: AGPL-3.0-only
 							<span class="_monospace">{{ user.id }}</span>
 							<button v-tooltip="i18n.ts.copy" class="_textButton" style="margin-left: 0.5em;" @click="copyToClipboard(user.id)"><i class="ti ti-copy"></i></button>
 						</span>
-						<span class="state">
-							<span v-if="!approved" class="silenced">{{ i18n.ts.notApproved }}</span>
-							<span v-if="approved && !user.host" class="moderator">{{ i18n.ts.approved }}</span>
-							<span v-if="suspended" class="suspended">{{ i18n.ts.suspended }}</span>
-							<span v-if="silenced" class="silenced">{{ i18n.ts.silenced }}</span>
-							<span v-if="moderator" class="moderator">{{ i18n.ts.moderator }}</span>
-						</span>
 					</div>
 				</div>
 
+				<SkBadgeStrip v-if="badges.length > 0" :badges="badges"></SkBadgeStrip>
+
 				<MkInfo v-if="isSystem">{{ i18n.ts.isSystemAccount }}</MkInfo>
 
 				<MkFolder v-if="!isSystem">
@@ -248,6 +243,7 @@ SPDX-License-Identifier: AGPL-3.0-only
 import { computed, defineAsyncComponent, watch, ref } from 'vue';
 import * as Misskey from 'misskey-js';
 import { url } from '@@/js/config.js';
+import type { Badge } from '@/components/SkBadgeStrip.vue';
 import MkChart from '@/components/MkChart.vue';
 import MkObjectView from '@/components/MkObjectView.vue';
 import MkTextarea from '@/components/MkTextarea.vue';
@@ -272,6 +268,7 @@ import MkPagination from '@/components/MkPagination.vue';
 import MkInput from '@/components/MkInput.vue';
 import MkNumber from '@/components/MkNumber.vue';
 import { copyToClipboard } from '@/utility/copy-to-clipboard';
+import SkBadgeStrip from '@/components/SkBadgeStrip.vue';
 
 const props = withDefaults(defineProps<{
 	userId: string;
@@ -304,6 +301,98 @@ const filesPagination = {
 	})),
 };
 
+const badges = computed(() => {
+	const arr: Badge[] = [];
+	if (info.value && user.value) {
+		if (info.value.isSuspended) {
+			arr.push({
+				key: 'suspended',
+				label: i18n.ts.suspended,
+				style: 'error',
+			});
+		}
+
+		if (info.value.isSilenced) {
+			arr.push({
+				key: 'silenced',
+				label: i18n.ts.silenced,
+				style: 'warning',
+			});
+		}
+
+		if (info.value.alwaysMarkNsfw) {
+			arr.push({
+				key: 'nsfw',
+				label: i18n.ts.nsfw,
+				style: 'warning',
+			});
+		}
+
+		if (user.value.mandatoryCW) {
+			arr.push({
+				key: 'cw',
+				label: i18n.ts.cw,
+				style: 'warning',
+			});
+		}
+
+		if (info.value.isHibernated) {
+			arr.push({
+				key: 'hibernated',
+				label: i18n.ts.hibernated,
+				style: 'neutral',
+			});
+		}
+
+		if (info.value.isAdministrator) {
+			arr.push({
+				key: 'admin',
+				label: i18n.ts.administrator,
+				style: 'success',
+			});
+		} else if (info.value.isModerator) {
+			arr.push({
+				key: 'mod',
+				label: i18n.ts.moderator,
+				style: 'success',
+			});
+		}
+
+		if (user.value.host == null) {
+			if (info.value.email) {
+				if (info.value.emailVerified) {
+					arr.push({
+						key: 'verified',
+						label: i18n.ts.verified,
+						style: 'success',
+					});
+				} else {
+					arr.push({
+						key: 'not_verified',
+						label: i18n.ts.notVerified,
+						style: 'success',
+					});
+				}
+			}
+
+			if (info.value.approved) {
+				arr.push({
+					key: 'approved',
+					label: i18n.ts.approved,
+					style: 'success',
+				});
+			} else {
+				arr.push({
+					key: 'not_approved',
+					label: i18n.ts.notApproved,
+					style: 'warning',
+				});
+			}
+		}
+	}
+	return arr;
+});
+
 const announcementsStatus = ref<'active' | 'archived'>('active');
 
 const announcementsPagination = {
diff --git a/packages/frontend/src/pages/instance-info.vue b/packages/frontend/src/pages/instance-info.vue
index 5d14b6bf2c..356bb4273b 100644
--- a/packages/frontend/src/pages/instance-info.vue
+++ b/packages/frontend/src/pages/instance-info.vue
@@ -24,6 +24,9 @@ SPDX-License-Identifier: AGPL-3.0-only
 						</span>
 					</div>
 				</div>
+
+				<SkBadgeStrip v-if="badges.length > 0" :badges="badges"></SkBadgeStrip>
+
 				<MkFolder>
 					<template #icon><i class="ph-list-bullets ph-bold ph-lg"></i></template>
 					<template #label>{{ i18n.ts.details }}</template>
@@ -200,10 +203,11 @@ SPDX-License-Identifier: AGPL-3.0-only
 </template>
 
 <script lang="ts" setup>
-import { ref, computed, watch } from 'vue';
+import { ref, computed, watch, useCssModule } from 'vue';
 import * as Misskey from 'misskey-js';
 import type { ChartSrc } from '@/components/MkChart.vue';
 import type { Paging } from '@/components/MkPagination.vue';
+import type { Badge } from '@/components/SkBadgeStrip.vue';
 import MkChart from '@/components/MkChart.vue';
 import MkObjectView from '@/components/MkObjectView.vue';
 import FormLink from '@/components/form/link.vue';
@@ -230,6 +234,9 @@ import { copyToClipboard } from '@/utility/copy-to-clipboard';
 import { acct } from '@/filters/user';
 import MkFolder from '@/components/MkFolder.vue';
 import MkNumber from '@/components/MkNumber.vue';
+import SkBadgeStrip from '@/components/SkBadgeStrip.vue';
+
+const $style = useCssModule();
 
 const props = defineProps<{
 	host: string;
@@ -266,6 +273,55 @@ const isBaseBlocked = computed(() => meta.value && baseDomains.value.some(d => m
 const isBaseSilenced = computed(() => meta.value && baseDomains.value.some(d => meta.value?.silencedHosts.includes(d)));
 const isBaseMediaSilenced = computed(() => meta.value && baseDomains.value.some(d => meta.value?.mediaSilencedHosts.includes(d)));
 
+const badges = computed(() => {
+	const arr: Badge[] = [];
+	if (instance.value) {
+		if (instance.value.isBlocked) {
+			arr.push({
+				key: 'blocked',
+				label: i18n.ts.blocked,
+				style: 'error',
+			});
+		}
+		if (instance.value.isSuspended) {
+			arr.push({
+				key: 'suspended',
+				label: i18n.ts.suspended,
+				style: 'error',
+			});
+		}
+		if (instance.value.isSilenced) {
+			arr.push({
+				key: 'silenced',
+				label: i18n.ts.silenced,
+				style: 'warning',
+			});
+		}
+		if (instance.value.isMediaSilenced) {
+			arr.push({
+				key: 'media_silenced',
+				label: i18n.ts.mediaSilenced,
+				style: 'warning',
+			});
+		}
+		if (instance.value.isNSFW) {
+			arr.push({
+				key: 'nsfw',
+				label: i18n.ts.nsfw,
+				style: 'warning',
+			});
+		}
+		if (instance.value.isBubbled) {
+			arr.push({
+				key: 'bubbled',
+				label: i18n.ts.bubble,
+				style: 'success',
+			});
+		}
+	}
+	return arr;
+});
+
 const usersPagination = {
 	endpoint: iAmModerator ? 'admin/show-users' : 'users',
 	limit: 10,
diff --git a/packages/misskey-js/src/autogen/types.ts b/packages/misskey-js/src/autogen/types.ts
index 55302960dc..13fc56ca53 100644
--- a/packages/misskey-js/src/autogen/types.ts
+++ b/packages/misskey-js/src/autogen/types.ts
@@ -5294,6 +5294,7 @@ export type components = {
       rejectReports: boolean;
       rejectQuotes: boolean;
       moderationNote?: string | null;
+      isBubbled: boolean;
     };
     GalleryPost: {
       /**
@@ -11209,6 +11210,7 @@ export type operations = {
               }]>;
             };
             isModerator: boolean;
+            isAdministrator: boolean;
             isSystem: boolean;
             isSilenced: boolean;
             isSuspended: boolean;
diff --git a/sharkey-locales/en-US.yml b/sharkey-locales/en-US.yml
index 586203099f..e219872b71 100644
--- a/sharkey-locales/en-US.yml
+++ b/sharkey-locales/en-US.yml
@@ -594,4 +594,11 @@ followingPub: "Following (Pub)"
 followersSub: "Followers (Sub)"
 wellKnownResources: "Well-known resources"
 lastPosted: "Last posted: {at}"
-raw: 'Raw'
+nsfw: "NSFW"
+raw: "Raw"
+cw: "CW"
+mediaSilenced: "Media Silenced"
+bubble: "Bubble"
+verified: "Verified"
+notVerified: "Not Verified"
+hibernated: "Hibernated"
-- 
cgit v1.2.3-freya


From 472c7b7f347b73ced3fda490f733f49bfb1b8e86 Mon Sep 17 00:00:00 2001
From: Hazelnoot <acomputerdog@gmail.com>
Date: Tue, 3 Jun 2025 17:11:51 -0400
Subject: add instance.isSilenced to PackedUserLite

---
 packages/backend/src/core/entities/UserEntityService.ts | 1 +
 packages/backend/src/models/json-schema/user.ts         | 4 ++++
 packages/misskey-js/src/autogen/types.ts                | 1 +
 3 files changed, 6 insertions(+)

(limited to 'packages/backend/src/models')

diff --git a/packages/backend/src/core/entities/UserEntityService.ts b/packages/backend/src/core/entities/UserEntityService.ts
index feddb8fa94..c030f9bfa4 100644
--- a/packages/backend/src/core/entities/UserEntityService.ts
+++ b/packages/backend/src/core/entities/UserEntityService.ts
@@ -616,6 +616,7 @@ export class UserEntityService implements OnModuleInit {
 				iconUrl: instance.iconUrl,
 				faviconUrl: instance.faviconUrl,
 				themeColor: instance.themeColor,
+				isSilenced: instance.isSilenced,
 			} : undefined) : undefined,
 			followersCount: followersCount ?? 0,
 			followingCount: followingCount ?? 0,
diff --git a/packages/backend/src/models/json-schema/user.ts b/packages/backend/src/models/json-schema/user.ts
index 964a179244..58a6565b9f 100644
--- a/packages/backend/src/models/json-schema/user.ts
+++ b/packages/backend/src/models/json-schema/user.ts
@@ -200,6 +200,10 @@ export const packedUserLiteSchema = {
 					type: 'string',
 					nullable: true, optional: false,
 				},
+				isSilenced: {
+					type: 'boolean',
+					nullable: false, optional: false,
+				},
 			},
 		},
 		emojis: {
diff --git a/packages/misskey-js/src/autogen/types.ts b/packages/misskey-js/src/autogen/types.ts
index d5712cdbed..b10e0224da 100644
--- a/packages/misskey-js/src/autogen/types.ts
+++ b/packages/misskey-js/src/autogen/types.ts
@@ -4281,6 +4281,7 @@ export type components = {
         iconUrl: string | null;
         faviconUrl: string | null;
         themeColor: string | null;
+        isSilenced: boolean;
       };
       emojis: {
         [key: string]: string;
-- 
cgit v1.2.3-freya


From 9d45041bd8d8c59e7e555f0f3aed945394c7989c Mon Sep 17 00:00:00 2001
From: Hazelnoot <acomputerdog@gmail.com>
Date: Tue, 3 Jun 2025 18:43:42 -0400
Subject: replace index on note.userHost

---
 .../1748990452958-replace_note-userHost_index.js   | 22 ++++++++++++++++++++++
 packages/backend/src/models/Note.ts                |  2 +-
 2 files changed, 23 insertions(+), 1 deletion(-)
 create mode 100644 packages/backend/migration/1748990452958-replace_note-userHost_index.js

(limited to 'packages/backend/src/models')

diff --git a/packages/backend/migration/1748990452958-replace_note-userHost_index.js b/packages/backend/migration/1748990452958-replace_note-userHost_index.js
new file mode 100644
index 0000000000..55aadd8136
--- /dev/null
+++ b/packages/backend/migration/1748990452958-replace_note-userHost_index.js
@@ -0,0 +1,22 @@
+/*
+ * SPDX-FileCopyrightText: hazelnoot and other Sharkey contributors
+ * SPDX-License-Identifier: AGPL-3.0-only
+ */
+
+export class ReplaceNoteUserHostIndex1748990452958 {
+	name = 'ReplaceNoteUserHostIndex1748990452958'
+
+	async up(queryRunner) {
+		await queryRunner.query(`DROP INDEX "public"."IDX_7125a826ab192eb27e11d358a5"`);
+		await queryRunner.query(`
+			create index "IDX_note_userHost_id"
+			on "note" ("userHost", "id" desc)
+			nulls not distinct`);
+		await queryRunner.query(`comment on index "IDX_note_userHost_id" is 'User host with ID included'`);
+	}
+
+	async down(queryRunner) {
+		await queryRunner.query(`drop index if exists "IDX_note_userHost_id"`);
+		await queryRunner.query(`CREATE INDEX "IDX_7125a826ab192eb27e11d358a5" ON "note" ("userHost") `);
+	}
+}
diff --git a/packages/backend/src/models/Note.ts b/packages/backend/src/models/Note.ts
index fa5839b6ec..b9a6f576dc 100644
--- a/packages/backend/src/models/Note.ts
+++ b/packages/backend/src/models/Note.ts
@@ -12,6 +12,7 @@ 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)
 @Entity('note')
 export class MiNote {
 	@PrimaryColumn(id())
@@ -216,7 +217,6 @@ export class MiNote {
 	public processErrors: string[] | null;
 
 	//#region Denormalized fields
-	@Index()
 	@Column('varchar', {
 		length: 128, nullable: true,
 		comment: '[Denormalized]',
-- 
cgit v1.2.3-freya


From 99e3d4506232f13e311afe1a5bd942b721b2cabe Mon Sep 17 00:00:00 2001
From: Hazelnoot <acomputerdog@gmail.com>
Date: Tue, 3 Jun 2025 19:05:24 -0400
Subject: create IDX_note_for_timelines

---
 .../1748991828473-create-IDX_note_for_timelines.js    | 19 +++++++++++++++++++
 packages/backend/src/models/Note.ts                   |  1 +
 2 files changed, 20 insertions(+)
 create mode 100644 packages/backend/migration/1748991828473-create-IDX_note_for_timelines.js

(limited to 'packages/backend/src/models')

diff --git a/packages/backend/migration/1748991828473-create-IDX_note_for_timelines.js b/packages/backend/migration/1748991828473-create-IDX_note_for_timelines.js
new file mode 100644
index 0000000000..2ea7fe95d2
--- /dev/null
+++ b/packages/backend/migration/1748991828473-create-IDX_note_for_timelines.js
@@ -0,0 +1,19 @@
+/*
+ * SPDX-FileCopyrightText: hazelnoot and other Sharkey contributors
+ * SPDX-License-Identifier: AGPL-3.0-only
+ */
+
+export class CreateIDXNoteForTimelines1748991828473 {
+	async up(queryRunner) {
+		await queryRunner.query(`
+			create index "IDX_note_for_timelines"
+			on "note" ("id" desc, "channelId", "visibility", "userHost")
+			include ("userId", "userHost", "replyId", "replyUserId", "replyUserHost", "renoteId", "renoteUserId", "renoteUserHost")
+			NULLS NOT DISTINCT`);
+		await queryRunner.query(`comment on index "IDX_note_for_timelines" is 'Covering index for timeline queries'`);
+	}
+
+	async down(queryRunner) {
+		await queryRunner.query(`DROP INDEX "IDX_note_for_timelines"`);
+	}
+}
diff --git a/packages/backend/src/models/Note.ts b/packages/backend/src/models/Note.ts
index b9a6f576dc..90b874f29a 100644
--- a/packages/backend/src/models/Note.ts
+++ b/packages/backend/src/models/Note.ts
@@ -13,6 +13,7 @@ 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())
-- 
cgit v1.2.3-freya


From d34c4df031e9c03722af9a2743015fc53de58d73 Mon Sep 17 00:00:00 2001
From: Hazelnoot <acomputerdog@gmail.com>
Date: Tue, 3 Jun 2025 19:08:20 -0400
Subject: create IDX_instance_host_filters

---
 .../1748992017688-create-IDX_instance_host_filters.js   | 17 +++++++++++++++++
 packages/backend/src/models/Instance.ts                 |  3 ++-
 2 files changed, 19 insertions(+), 1 deletion(-)
 create mode 100644 packages/backend/migration/1748992017688-create-IDX_instance_host_filters.js

(limited to 'packages/backend/src/models')

diff --git a/packages/backend/migration/1748992017688-create-IDX_instance_host_filters.js b/packages/backend/migration/1748992017688-create-IDX_instance_host_filters.js
new file mode 100644
index 0000000000..76cf16a6de
--- /dev/null
+++ b/packages/backend/migration/1748992017688-create-IDX_instance_host_filters.js
@@ -0,0 +1,17 @@
+/*
+ * SPDX-FileCopyrightText: hazelnoot and other Sharkey contributors
+ * SPDX-License-Identifier: AGPL-3.0-only
+ */
+
+export class CreateIDXInstanceHostFilters1748992017688 {
+	async up(queryRunner) {
+		await queryRunner.query(`
+			create index "IDX_instance_host_filters"
+			on "instance" ("host", "isBlocked", "isSilenced", "isMediaSilenced", "isAllowListed", "isBubbled", "suspensionState")`);
+		await queryRunner.query(`comment on index "IDX_instance_host_filters" is 'Covering index for host filter queries'`);
+	}
+
+	async down(queryRunner) {
+		await queryRunner.query(`DROP INDEX "IDX_instance_host_filters"`);
+	}
+}
diff --git a/packages/backend/src/models/Instance.ts b/packages/backend/src/models/Instance.ts
index 0022e58933..c9200e1e35 100644
--- a/packages/backend/src/models/Instance.ts
+++ b/packages/backend/src/models/Instance.ts
@@ -6,7 +6,8 @@
 import { Entity, PrimaryColumn, Index, Column } from 'typeorm';
 import { id } from './util/id.js';
 
-@Index('IDX_instance_host_key', { synchronize: false })
+@Index('IDX_instance_host_key', { synchronize: false }) // ((lower(reverse("host"::text)) || '.'::text)
+@Index('IDX_instance_host_filters', { synchronize: false }) // ("host", "isBlocked", "isSilenced", "isMediaSilenced", "isAllowListed", "isBubbled", "suspensionState")
 @Entity('instance')
 export class MiInstance {
 	@PrimaryColumn(id())
-- 
cgit v1.2.3-freya


From 71f60d519b6f8e655737136484658ea0f1d00507 Mon Sep 17 00:00:00 2001
From: Hazelnoot <acomputerdog@gmail.com>
Date: Wed, 28 May 2025 01:33:55 -0400
Subject: add targetInstance to abuse report schema

---
 .../core/entities/AbuseUserReportEntityService.ts  | 27 +++++++++++++++++++---
 packages/backend/src/models/AbuseUserReport.ts     | 21 +++++++++++++++++
 .../api/endpoints/admin/abuse-user-reports.ts      |  5 ++++
 packages/misskey-js/src/autogen/types.ts           |  1 +
 4 files changed, 51 insertions(+), 3 deletions(-)

(limited to 'packages/backend/src/models')

diff --git a/packages/backend/src/core/entities/AbuseUserReportEntityService.ts b/packages/backend/src/core/entities/AbuseUserReportEntityService.ts
index a6c00d89b9..c1d877aa12 100644
--- a/packages/backend/src/core/entities/AbuseUserReportEntityService.ts
+++ b/packages/backend/src/core/entities/AbuseUserReportEntityService.ts
@@ -5,13 +5,14 @@
 
 import { Inject, Injectable } from '@nestjs/common';
 import { DI } from '@/di-symbols.js';
-import type { AbuseUserReportsRepository, MiUser } from '@/models/_.js';
+import type { AbuseUserReportsRepository, InstancesRepository, MiInstance, MiUser } from '@/models/_.js';
 import { awaitAll } from '@/misc/prelude/await-all.js';
 import type { MiAbuseUserReport } from '@/models/AbuseUserReport.js';
 import { bindThis } from '@/decorators.js';
 import { IdService } from '@/core/IdService.js';
 import type { Packed } from '@/misc/json-schema.js';
 import { UserEntityService } from './UserEntityService.js';
+import { InstanceEntityService } from './InstanceEntityService.js';
 
 @Injectable()
 export class AbuseUserReportEntityService {
@@ -19,6 +20,10 @@ export class AbuseUserReportEntityService {
 		@Inject(DI.abuseUserReportsRepository)
 		private abuseUserReportsRepository: AbuseUserReportsRepository,
 
+		@Inject(DI.instancesRepository)
+		private instancesRepository: InstancesRepository,
+
+		private readonly instanceEntityService: InstanceEntityService,
 		private userEntityService: UserEntityService,
 		private idService: IdService,
 	) {
@@ -30,6 +35,7 @@ export class AbuseUserReportEntityService {
 		hint?: {
 			packedReporter?: Packed<'UserDetailedNotMe'>,
 			packedTargetUser?: Packed<'UserDetailedNotMe'>,
+			packedTargetInstance?: Packed<'FederationInstance'>,
 			packedAssignee?: Packed<'UserDetailedNotMe'>,
 		},
 		me?: MiUser | null,
@@ -51,7 +57,16 @@ export class AbuseUserReportEntityService {
 			targetUser: hint?.packedTargetUser ?? this.userEntityService.pack(report.targetUser ?? report.targetUserId, me, {
 				schema: 'UserDetailedNotMe',
 			}),
-			assignee: report.assigneeId ? hint?.packedAssignee ?? this.userEntityService.pack(report.assignee ?? report.assigneeId, null, {
+			// return hint, or pack by relation, or fetch and pack by id, or null
+			targetInstance: hint?.packedTargetInstance ?? (
+				report.targetUserInstance
+					? this.instanceEntityService.pack(report.targetUserInstance, me)
+					: report.targetUserHost
+						? this.instancesRepository.findOneBy({ host: report.targetUserHost }).then(instance => instance
+							? this.instanceEntityService.pack(instance, me)
+							: null)
+						: null),
+			assignee: report.assigneeId ? hint?.packedAssignee ?? this.userEntityService.pack(report.assignee ?? report.assigneeId, me, {
 				schema: 'UserDetailedNotMe',
 			}) : null,
 			forwarded: report.forwarded,
@@ -73,12 +88,18 @@ export class AbuseUserReportEntityService {
 			me,
 			{ schema: 'UserDetailedNotMe' },
 		).then(users => new Map(users.map(u => [u.id, u])));
+		const _targetInstances = reports
+			.map(({ targetUserInstance, targetUserHost }) => targetUserInstance ?? targetUserHost)
+			.filter((i): i is MiInstance | string => i != null);
+		const _instanceMap = await this.instanceEntityService.packMany(await this.instanceEntityService.fetchInstancesByHost(_targetInstances), me)
+			.then(instances => new Map(instances.map(i => [i.host, i])));
 		return Promise.all(
 			reports.map(report => {
 				const packedReporter = _userMap.get(report.reporterId);
 				const packedTargetUser = _userMap.get(report.targetUserId);
+				const packedTargetInstance = report.targetUserHost ? _instanceMap.get(report.targetUserHost) : undefined;
 				const packedAssignee = report.assigneeId != null ? _userMap.get(report.assigneeId) : undefined;
-				return this.pack(report, { packedReporter, packedTargetUser, packedAssignee }, me);
+				return this.pack(report, { packedReporter, packedTargetUser, packedAssignee, packedTargetInstance }, me);
 			}),
 		);
 	}
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/server/api/endpoints/admin/abuse-user-reports.ts b/packages/backend/src/server/api/endpoints/admin/abuse-user-reports.ts
index ad0bbb5647..f052d3dd14 100644
--- a/packages/backend/src/server/api/endpoints/admin/abuse-user-reports.ts
+++ b/packages/backend/src/server/api/endpoints/admin/abuse-user-reports.ts
@@ -69,6 +69,11 @@ export const meta = {
 					nullable: false, optional: false,
 					ref: 'UserDetailedNotMe',
 				},
+				targetInstance: {
+					type: 'object',
+					nullable: true, optional: false,
+					ref: 'FederationInstance',
+				},
 				assignee: {
 					type: 'object',
 					nullable: true, optional: false,
diff --git a/packages/misskey-js/src/autogen/types.ts b/packages/misskey-js/src/autogen/types.ts
index 5e91fb14ac..b1d5f63a8b 100644
--- a/packages/misskey-js/src/autogen/types.ts
+++ b/packages/misskey-js/src/autogen/types.ts
@@ -6198,6 +6198,7 @@ export type operations = {
               assigneeId: string | null;
               reporter: components['schemas']['UserDetailedNotMe'];
               targetUser: components['schemas']['UserDetailedNotMe'];
+              targetInstance: components['schemas']['FederationInstance'] | null;
               assignee: components['schemas']['UserDetailedNotMe'] | null;
               forwarded: boolean;
               /** @enum {string|null} */
-- 
cgit v1.2.3-freya


From 23302fe7d8de4923f4748f6c64d39e3f57c9d98f Mon Sep 17 00:00:00 2001
From: Hazelnoot <acomputerdog@gmail.com>
Date: Wed, 28 May 2025 02:02:27 -0400
Subject: add relation from user->user_profile to speed up
 UserEntityService.pack and packMany

---
 .../backend/src/core/entities/UserEntityService.ts | 34 +++++++++++++++++-----
 packages/backend/src/models/User.ts                |  4 +++
 packages/backend/src/models/UserProfile.ts         |  2 +-
 3 files changed, 32 insertions(+), 8 deletions(-)

(limited to 'packages/backend/src/models')

diff --git a/packages/backend/src/core/entities/UserEntityService.ts b/packages/backend/src/core/entities/UserEntityService.ts
index 3524119ba1..326baaefd4 100644
--- a/packages/backend/src/core/entities/UserEntityService.ts
+++ b/packages/backend/src/core/entities/UserEntityService.ts
@@ -487,7 +487,10 @@ export class UserEntityService implements OnModuleInit {
 			includeSecrets: false,
 		}, options);
 
-		const user = typeof src === 'object' ? src : await this.usersRepository.findOneByOrFail({ id: src });
+		const user = typeof src === 'object' ? src : await this.usersRepository.findOneOrFail({
+			where: { id: src },
+			relations: { userProfile: true },
+		});
 
 		// migration
 		if (user.avatarId != null && user.avatarUrl === null) {
@@ -521,7 +524,7 @@ export class UserEntityService implements OnModuleInit {
 		const iAmModerator = me ? await this.roleService.isModerator(me as MiUser) : false;
 
 		const profile = isDetailed
-			? (opts.userProfile ?? await this.userProfilesRepository.findOneByOrFail({ userId: user.id }))
+			? (opts.userProfile ?? user.userProfile ?? await this.userProfilesRepository.findOneByOrFail({ userId: user.id }))
 			: null;
 
 		let relation: UserRelation | null = null;
@@ -556,7 +559,7 @@ export class UserEntityService implements OnModuleInit {
 			}
 		}
 
-		const mastoapi = !isDetailed ? opts.userProfile ?? await this.userProfilesRepository.findOneByOrFail({ userId: user.id }) : null;
+		const mastoapi = !isDetailed ? opts.userProfile ?? user.userProfile ?? await this.userProfilesRepository.findOneByOrFail({ userId: user.id }) : null;
 
 		const followingCount = profile == null ? null :
 			(profile.followingVisibility === 'public') || isMe || iAmModerator ? user.followingCount :
@@ -785,8 +788,13 @@ export class UserEntityService implements OnModuleInit {
 		const _users = users.filter((user): user is MiUser => typeof user !== 'string');
 		if (_users.length !== users.length) {
 			_users.push(
-				...await this.usersRepository.findBy({
-					id: In(users.filter((user): user is string => typeof user === 'string')),
+				...await this.usersRepository.find({
+					where: {
+						id: In(users.filter((user): user is string => typeof user === 'string')),
+					},
+					relations: {
+						userProfile: true,
+					},
 				}),
 			);
 		}
@@ -800,8 +808,20 @@ export class UserEntityService implements OnModuleInit {
 		let pinNotes: Map<MiUser['id'], MiUserNotePining[]> = new Map();
 
 		if (options?.schema !== 'UserLite') {
-			profilesMap = await this.userProfilesRepository.findBy({ userId: In(_userIds) })
-				.then(profiles => new Map(profiles.map(p => [p.userId, p])));
+			const _profiles: MiUserProfile[] = [];
+			const _profilesToFetch: string[] = [];
+			for (const user of _users) {
+				if (user.userProfile) {
+					_profiles.push(user.userProfile);
+				} else {
+					_profilesToFetch.push(user.id);
+				}
+			}
+			if (_profilesToFetch.length > 0) {
+				const fetched = await this.userProfilesRepository.findBy({ userId: In(_profilesToFetch) });
+				_profiles.push(...fetched);
+			}
+			profilesMap = new Map(_profiles.map(p => [p.userId, p]));
 
 			const meId = me ? me.id : null;
 			if (meId) {
diff --git a/packages/backend/src/models/User.ts b/packages/backend/src/models/User.ts
index 3ef5817672..2f13400944 100644
--- a/packages/backend/src/models/User.ts
+++ b/packages/backend/src/models/User.ts
@@ -8,6 +8,7 @@ import { type UserUnsignedFetchOption, userUnsignedFetchOptions } from '@/const.
 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 })
@@ -395,6 +396,9 @@ export class MiUser {
 	})
 	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()
-- 
cgit v1.2.3-freya


From b05ccbc3aca8e008555736f25760f9f2b1b2a5e7 Mon Sep 17 00:00:00 2001
From: Hazelnoot <acomputerdog@gmail.com>
Date: Wed, 28 May 2025 02:03:05 -0400
Subject: add relations from abuse_user_report->user_profile to speed up
 admin/abuse-user-reports endpoint

---
 packages/backend/src/models/AbuseUserReport.ts     | 22 ++++++++++++++++++++++
 .../api/endpoints/admin/abuse-user-reports.ts      | 10 +++++++++-
 2 files changed, 31 insertions(+), 1 deletion(-)

(limited to 'packages/backend/src/models')

diff --git a/packages/backend/src/models/AbuseUserReport.ts b/packages/backend/src/models/AbuseUserReport.ts
index 8f8d759004..c1a44c3d40 100644
--- a/packages/backend/src/models/AbuseUserReport.ts
+++ b/packages/backend/src/models/AbuseUserReport.ts
@@ -5,6 +5,7 @@
 
 import { PrimaryColumn, Entity, Index, JoinColumn, Column, ManyToOne } from 'typeorm';
 import { MiInstance } from '@/models/Instance.js';
+import { MiUserProfile } from '@/models/UserProfile.js';
 import { id } from './util/id.js';
 import { MiUser } from './User.js';
 
@@ -25,6 +26,13 @@ export class MiAbuseUserReport {
 	@JoinColumn()
 	public targetUser: MiUser | null;
 
+	@ManyToOne(() => MiUserProfile, {
+		onDelete: 'CASCADE',
+		createForeignKeyConstraints: false,
+	})
+	@JoinColumn({ name: 'targetUserId', referencedColumnName: 'userId' })
+	public targetUserProfile: MiUserProfile | null;
+
 	@Index()
 	@Column(id())
 	public reporterId: MiUser['id'];
@@ -35,6 +43,13 @@ export class MiAbuseUserReport {
 	@JoinColumn()
 	public reporter: MiUser | null;
 
+	@ManyToOne(() => MiUserProfile, {
+		onDelete: 'CASCADE',
+		createForeignKeyConstraints: false,
+	})
+	@JoinColumn({ name: 'reporterId', referencedColumnName: 'userId' })
+	public reporterProfile: MiUserProfile | null;
+
 	@Column({
 		...id(),
 		nullable: true,
@@ -47,6 +62,13 @@ export class MiAbuseUserReport {
 	@JoinColumn()
 	public assignee: MiUser | null;
 
+	@ManyToOne(() => MiUserProfile, {
+		onDelete: 'CASCADE',
+		createForeignKeyConstraints: false,
+	})
+	@JoinColumn({ name: 'assigneeId', referencedColumnName: 'userId' })
+	public assigneeProfile: MiUserProfile | null;
+
 	@Index()
 	@Column('boolean', {
 		default: false,
diff --git a/packages/backend/src/server/api/endpoints/admin/abuse-user-reports.ts b/packages/backend/src/server/api/endpoints/admin/abuse-user-reports.ts
index f052d3dd14..f9aa3b7ae5 100644
--- a/packages/backend/src/server/api/endpoints/admin/abuse-user-reports.ts
+++ b/packages/backend/src/server/api/endpoints/admin/abuse-user-reports.ts
@@ -120,7 +120,15 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 		private queryService: QueryService,
 	) {
 		super(meta, paramDef, async (ps, me) => {
-			const query = this.queryService.makePaginationQuery(this.abuseUserReportsRepository.createQueryBuilder('report'), ps.sinceId, ps.untilId);
+			const query = this.queryService.makePaginationQuery(this.abuseUserReportsRepository.createQueryBuilder('report'), ps.sinceId, ps.untilId)
+				.leftJoinAndSelect('report.targetUser', 'targetUser')
+				.leftJoinAndSelect('report.targetUserProfile', 'targetUserProfile')
+				.leftJoinAndSelect('report.targetUserInstance', 'targetUserInstance')
+				.leftJoinAndSelect('report.reporter', 'reporter')
+				.leftJoinAndSelect('report.reporterUserProfile', 'reporterUserProfile')
+				.leftJoinAndSelect('report.assignee', 'assignee')
+				.leftJoinAndSelect('report.assigneeUserProfile', 'assigneeUserProfile')
+			;
 
 			switch (ps.state) {
 				case 'resolved': query.andWhere('report.resolved = TRUE'); break;
-- 
cgit v1.2.3-freya


From b1876bf06e92acda34270cf2582559b2f4cc1001 Mon Sep 17 00:00:00 2001
From: Hazelnoot <acomputerdog@gmail.com>
Date: Wed, 28 May 2025 03:08:34 -0400
Subject: remove report -> profile relations to avoid TypeORM bug
 https://github.com/typeorm/typeorm/issues/10469

---
 packages/backend/src/models/AbuseUserReport.ts     | 22 ----------------------
 .../api/endpoints/admin/abuse-user-reports.ts      |  6 +++---
 2 files changed, 3 insertions(+), 25 deletions(-)

(limited to 'packages/backend/src/models')

diff --git a/packages/backend/src/models/AbuseUserReport.ts b/packages/backend/src/models/AbuseUserReport.ts
index c1a44c3d40..8f8d759004 100644
--- a/packages/backend/src/models/AbuseUserReport.ts
+++ b/packages/backend/src/models/AbuseUserReport.ts
@@ -5,7 +5,6 @@
 
 import { PrimaryColumn, Entity, Index, JoinColumn, Column, ManyToOne } from 'typeorm';
 import { MiInstance } from '@/models/Instance.js';
-import { MiUserProfile } from '@/models/UserProfile.js';
 import { id } from './util/id.js';
 import { MiUser } from './User.js';
 
@@ -26,13 +25,6 @@ export class MiAbuseUserReport {
 	@JoinColumn()
 	public targetUser: MiUser | null;
 
-	@ManyToOne(() => MiUserProfile, {
-		onDelete: 'CASCADE',
-		createForeignKeyConstraints: false,
-	})
-	@JoinColumn({ name: 'targetUserId', referencedColumnName: 'userId' })
-	public targetUserProfile: MiUserProfile | null;
-
 	@Index()
 	@Column(id())
 	public reporterId: MiUser['id'];
@@ -43,13 +35,6 @@ export class MiAbuseUserReport {
 	@JoinColumn()
 	public reporter: MiUser | null;
 
-	@ManyToOne(() => MiUserProfile, {
-		onDelete: 'CASCADE',
-		createForeignKeyConstraints: false,
-	})
-	@JoinColumn({ name: 'reporterId', referencedColumnName: 'userId' })
-	public reporterProfile: MiUserProfile | null;
-
 	@Column({
 		...id(),
 		nullable: true,
@@ -62,13 +47,6 @@ export class MiAbuseUserReport {
 	@JoinColumn()
 	public assignee: MiUser | null;
 
-	@ManyToOne(() => MiUserProfile, {
-		onDelete: 'CASCADE',
-		createForeignKeyConstraints: false,
-	})
-	@JoinColumn({ name: 'assigneeId', referencedColumnName: 'userId' })
-	public assigneeProfile: MiUserProfile | null;
-
 	@Index()
 	@Column('boolean', {
 		default: false,
diff --git a/packages/backend/src/server/api/endpoints/admin/abuse-user-reports.ts b/packages/backend/src/server/api/endpoints/admin/abuse-user-reports.ts
index bf71cb82c4..b8200c09aa 100644
--- a/packages/backend/src/server/api/endpoints/admin/abuse-user-reports.ts
+++ b/packages/backend/src/server/api/endpoints/admin/abuse-user-reports.ts
@@ -122,12 +122,12 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 		super(meta, paramDef, async (ps, me) => {
 			const query = this.queryService.makePaginationQuery(this.abuseUserReportsRepository.createQueryBuilder('report'), ps.sinceId, ps.untilId)
 				.leftJoinAndSelect('report.targetUser', 'targetUser')
-				.leftJoinAndSelect('report.targetUserProfile', 'targetUserProfile')
+				.leftJoinAndSelect('targetUser.userProfile', 'targetUserProfile')
 				.leftJoinAndSelect('report.targetUserInstance', 'targetUserInstance')
 				.leftJoinAndSelect('report.reporter', 'reporter')
-				.leftJoinAndSelect('report.reporterProfile', 'reporterProfile')
+				.leftJoinAndSelect('reporter.userProfile', 'reporterProfile')
 				.leftJoinAndSelect('report.assignee', 'assignee')
-				.leftJoinAndSelect('report.assigneeProfile', 'assigneeProfile')
+				.leftJoinAndSelect('assignee.userProfile', 'assigneeProfile')
 			;
 
 			switch (ps.state) {
-- 
cgit v1.2.3-freya


From 5f51f7878ea520956896337885789041647c9b25 Mon Sep 17 00:00:00 2001
From: Hazelnoot <acomputerdog@gmail.com>
Date: Fri, 6 Jun 2025 13:03:25 -0400
Subject: create IDX_note_url over note.url

---
 .../migration/1749229288946-create-IDX_note_url.js       | 16 ++++++++++++++++
 packages/backend/src/models/Note.ts                      |  1 +
 2 files changed, 17 insertions(+)
 create mode 100644 packages/backend/migration/1749229288946-create-IDX_note_url.js

(limited to 'packages/backend/src/models')

diff --git a/packages/backend/migration/1749229288946-create-IDX_note_url.js b/packages/backend/migration/1749229288946-create-IDX_note_url.js
new file mode 100644
index 0000000000..4b2fc25cf7
--- /dev/null
+++ b/packages/backend/migration/1749229288946-create-IDX_note_url.js
@@ -0,0 +1,16 @@
+/*
+ * SPDX-FileCopyrightText: hazelnoot and other Sharkey contributors
+ * SPDX-License-Identifier: AGPL-3.0-only
+ */
+
+export class CreateIDXNoteUrl1749229288946 {
+	name = 'CreateIDXNoteUrl1749229288946'
+
+	async up(queryRunner) {
+		await queryRunner.query(`CREATE INDEX IF NOT EXISTS "IDX_note_url" ON "note" ("url") `);
+	}
+
+	async down(queryRunner) {
+		await queryRunner.query(`DROP INDEX "public"."IDX_note_url"`);
+	}
+}
diff --git a/packages/backend/src/models/Note.ts b/packages/backend/src/models/Note.ts
index 90b874f29a..bbe183cfbb 100644
--- a/packages/backend/src/models/Note.ts
+++ b/packages/backend/src/models/Note.ts
@@ -133,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.',
-- 
cgit v1.2.3-freya


From 3bf0a737c839f0b7a7ea93e16472f02ebff596ee Mon Sep 17 00:00:00 2001
From: Hazelnoot <acomputerdog@gmail.com>
Date: Fri, 6 Jun 2025 13:17:20 -0400
Subject: match attributionDomains entity to database schema

---
 .../backend/src/core/activitypub/models/ApPersonService.ts   | 12 ++++++++++--
 packages/backend/src/models/User.ts                          |  4 ++--
 packages/backend/src/server/api/endpoints/i/update.ts        | 12 +++++++++---
 3 files changed, 21 insertions(+), 7 deletions(-)

(limited to 'packages/backend/src/models')

diff --git a/packages/backend/src/core/activitypub/models/ApPersonService.ts b/packages/backend/src/core/activitypub/models/ApPersonService.ts
index dde5762f53..631e86c8a8 100644
--- a/packages/backend/src/core/activitypub/models/ApPersonService.ts
+++ b/packages/backend/src/core/activitypub/models/ApPersonService.ts
@@ -445,7 +445,11 @@ export class ApPersonService implements OnModuleInit, OnApplicationShutdown {
 					makeNotesFollowersOnlyBefore: (person as any).makeNotesFollowersOnlyBefore ?? null,
 					makeNotesHiddenBefore: (person as any).makeNotesHiddenBefore ?? null,
 					emojis,
-					attributionDomains: (Array.isArray(person.attributionDomains) && person.attributionDomains.every(x => typeof x === 'string')) ? person.attributionDomains : [],
+					attributionDomains: Array.isArray(person.attributionDomains)
+						? person.attributionDomains
+							.filter((a: unknown) => typeof(a) === 'string' && a.length > 0 && a.length <= 128)
+							.slice(0, 32)
+						: [],
 				})) as MiRemoteUser;
 
 				let _description: string | null = null;
@@ -629,7 +633,11 @@ export class ApPersonService implements OnModuleInit, OnApplicationShutdown {
 			// We use "!== false" to handle incorrect types, missing / null values, and "default to true" logic.
 			hideOnlineStatus: person.hideOnlineStatus !== false,
 			isExplorable: person.discoverable !== false,
-			attributionDomains: (Array.isArray(person.attributionDomains) && person.attributionDomains.every(x => typeof x === 'string')) ? person.attributionDomains : [],
+			attributionDomains: Array.isArray(person.attributionDomains)
+				? person.attributionDomains
+					.filter((a: unknown) => typeof(a) === 'string' && a.length > 0 && a.length <= 128)
+					.slice(0, 32)
+				: [],
 			...(await this.resolveAvatarAndBanner(exist, person.icon, person.image, person.backgroundUrl).catch(() => ({}))),
 		} as Partial<MiRemoteUser> & Pick<MiRemoteUser, 'isBot' | 'isCat' | 'speakAsCat' | 'isLocked' | 'movedToUri' | 'alsoKnownAs' | 'isExplorable'>;
 
diff --git a/packages/backend/src/models/User.ts b/packages/backend/src/models/User.ts
index 2f13400944..f40bb41a22 100644
--- a/packages/backend/src/models/User.ts
+++ b/packages/backend/src/models/User.ts
@@ -390,9 +390,9 @@ export class MiUser {
 	})
 	public allowUnsignedFetch: UserUnsignedFetchOption;
 
-	@Column('varchar', {
+	@Column('text', {
 		name: 'attributionDomains',
-		length: 128, array: true, default: '{}',
+		array: true, default: '{}',
 	})
 	public attributionDomains: string[];
 
diff --git a/packages/backend/src/server/api/endpoints/i/update.ts b/packages/backend/src/server/api/endpoints/i/update.ts
index dad605f151..5a160d632c 100644
--- a/packages/backend/src/server/api/endpoints/i/update.ts
+++ b/packages/backend/src/server/api/endpoints/i/update.ts
@@ -263,9 +263,15 @@ export const paramDef = {
 			enum: userUnsignedFetchOptions,
 			nullable: false,
 		},
-		attributionDomains: { type: 'array', items: {
-			type: 'string',
-		} },
+		attributionDomains: {
+			type: 'array',
+			items: {
+				type: 'string',
+				minLength: 1,
+				maxLength: 128,
+			},
+			maxLength: 32,
+		},
 	},
 } as const;
 
-- 
cgit v1.2.3-freya


From d0cf1cc15f998603c95ffca21c0d3feadc240a9d Mon Sep 17 00:00:00 2001
From: Hazelnoot <acomputerdog@gmail.com>
Date: Fri, 6 Jun 2025 23:35:25 -0400
Subject: remove IDX_instance_host_filters as it slowed down queries more than
 it helped

---
 .../1749267016885-remove-IDX_instance_host_filters.js   | 17 +++++++++++++++++
 packages/backend/src/models/Instance.ts                 |  1 -
 2 files changed, 17 insertions(+), 1 deletion(-)
 create mode 100644 packages/backend/migration/1749267016885-remove-IDX_instance_host_filters.js

(limited to 'packages/backend/src/models')

diff --git a/packages/backend/migration/1749267016885-remove-IDX_instance_host_filters.js b/packages/backend/migration/1749267016885-remove-IDX_instance_host_filters.js
new file mode 100644
index 0000000000..d0a4e4f91e
--- /dev/null
+++ b/packages/backend/migration/1749267016885-remove-IDX_instance_host_filters.js
@@ -0,0 +1,17 @@
+/*
+ * SPDX-FileCopyrightText: hazelnoot and other Sharkey contributors
+ * SPDX-License-Identifier: AGPL-3.0-only
+ */
+
+export class RemoveIDXInstanceHostFilters1749267016885 {
+	async up(queryRunner) {
+		await queryRunner.query(`DROP INDEX IF EXISTS "IDX_instance_host_filters"`);
+	}
+
+	async down(queryRunner) {
+		await queryRunner.query(`
+			create index "IDX_instance_host_filters"
+			on "instance" ("host", "isBlocked", "isSilenced", "isMediaSilenced", "isAllowListed", "isBubbled", "suspensionState")`);
+		await queryRunner.query(`comment on index "IDX_instance_host_filters" is 'Covering index for host filter queries'`);
+	}
+}
diff --git a/packages/backend/src/models/Instance.ts b/packages/backend/src/models/Instance.ts
index c9200e1e35..0cde4b75fc 100644
--- a/packages/backend/src/models/Instance.ts
+++ b/packages/backend/src/models/Instance.ts
@@ -7,7 +7,6 @@ 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)
-@Index('IDX_instance_host_filters', { synchronize: false }) // ("host", "isBlocked", "isSilenced", "isMediaSilenced", "isAllowListed", "isBubbled", "suspensionState")
 @Entity('instance')
 export class MiInstance {
 	@PrimaryColumn(id())
-- 
cgit v1.2.3-freya


From aa40b8ca0d777c1b7ae87a2708f3ed1d748e93f0 Mon Sep 17 00:00:00 2001
From: Hazelnoot <acomputerdog@gmail.com>
Date: Fri, 13 Jun 2025 20:01:06 -0400
Subject: show signup reason in user admin screen

---
 locales/index.d.ts                                   |  4 ++++
 packages/backend/src/models/json-schema/user.ts      | 20 ++++++++++----------
 .../src/server/api/endpoints/admin/show-user.ts      |  4 ++++
 packages/frontend/src/components/SkApprovalUser.vue  |  2 +-
 packages/frontend/src/pages/admin-user.vue           |  5 +++++
 packages/misskey-js/src/autogen/types.ts             |  9 +++++----
 sharkey-locales/en-US.yml                            |  2 ++
 7 files changed, 31 insertions(+), 15 deletions(-)

(limited to 'packages/backend/src/models')

diff --git a/locales/index.d.ts b/locales/index.d.ts
index f9fcbdb238..a22a8e893e 100644
--- a/locales/index.d.ts
+++ b/locales/index.d.ts
@@ -13257,6 +13257,10 @@ export interface Locale extends ILocale {
      * ActivityPub user data in its raw form. These fields are public and accessible to other instances.
      */
     "rawApDescription": string;
+    /**
+     * Signup Reason
+     */
+    "signupReason": string;
 }
 declare const locales: {
     [lang: string]: Locale;
diff --git a/packages/backend/src/models/json-schema/user.ts b/packages/backend/src/models/json-schema/user.ts
index 2e5364f404..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,
@@ -278,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,
@@ -324,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,
diff --git a/packages/backend/src/server/api/endpoints/admin/show-user.ts b/packages/backend/src/server/api/endpoints/admin/show-user.ts
index 6a77fc177f..6f0081f1f7 100644
--- a/packages/backend/src/server/api/endpoints/admin/show-user.ts
+++ b/packages/backend/src/server/api/endpoints/admin/show-user.ts
@@ -221,6 +221,10 @@ export const meta = {
 					},
 				},
 			},
+			signupReason: {
+				type: 'string',
+				optional: false, nullable: true,
+			},
 		},
 	},
 } as const;
diff --git a/packages/frontend/src/components/SkApprovalUser.vue b/packages/frontend/src/components/SkApprovalUser.vue
index 310d044387..3f5bd345f2 100644
--- a/packages/frontend/src/components/SkApprovalUser.vue
+++ b/packages/frontend/src/components/SkApprovalUser.vue
@@ -19,7 +19,7 @@ SPDX-License-Identifier: AGPL-3.0-only
 				<div>{{ email }}</div>
 			</div>
 			<div>
-				<div :class="$style.label">Reason</div>
+				<div :class="$style.label">{{ i18n.ts.signupReason }}</div>
 				<div>{{ reason }}</div>
 			</div>
 		</div>
diff --git a/packages/frontend/src/pages/admin-user.vue b/packages/frontend/src/pages/admin-user.vue
index 840eff77dd..dc29ae2f80 100644
--- a/packages/frontend/src/pages/admin-user.vue
+++ b/packages/frontend/src/pages/admin-user.vue
@@ -130,6 +130,11 @@ SPDX-License-Identifier: AGPL-3.0-only
 					</div>
 				</FormSection>
 
+				<FormSection v-else-if="info.signupReason">
+					<template #label>{{ i18n.ts.signupReason }}</template>
+					{{ info.signupReason }}
+				</FormSection>
+
 				<FormSection v-if="!isSystem && user && iAmModerator">
 					<div class="_gaps">
 						<MkSwitch v-model="silenced" @update:modelValue="toggleSilence">{{ i18n.ts.silence }}</MkSwitch>
diff --git a/packages/misskey-js/src/autogen/types.ts b/packages/misskey-js/src/autogen/types.ts
index 6cb52fcbea..8debdec9c1 100644
--- a/packages/misskey-js/src/autogen/types.ts
+++ b/packages/misskey-js/src/autogen/types.ts
@@ -4246,6 +4246,10 @@ export type components = {
       /** Format: url */
       avatarUrl: string | null;
       avatarBlurhash: string | null;
+      /** @example Hi masters, I am Ai! */
+      description: string | null;
+      /** Format: date-time */
+      createdAt: string;
       avatarDecorations: {
           /** Format: id */
           id: string;
@@ -4304,8 +4308,6 @@ export type components = {
       movedTo: string | null;
       alsoKnownAs: string[] | null;
       /** Format: date-time */
-      createdAt: string;
-      /** Format: date-time */
       updatedAt: string | null;
       /** Format: date-time */
       lastFetchedAt: string | null;
@@ -4319,8 +4321,6 @@ export type components = {
       isSilenced: boolean;
       /** @example false */
       isSuspended: boolean;
-      /** @example Hi masters, I am Ai! */
-      description: string | null;
       location: string | null;
       /** @example 2018-03-12 */
       birthday: string | null;
@@ -11236,6 +11236,7 @@ export type operations = {
               remoteFollowing: number;
               remoteFollowers: number;
             };
+            signupReason: string | null;
           };
         };
       };
diff --git a/sharkey-locales/en-US.yml b/sharkey-locales/en-US.yml
index 5ea562d262..3f5c82f2dc 100644
--- a/sharkey-locales/en-US.yml
+++ b/sharkey-locales/en-US.yml
@@ -628,3 +628,5 @@ noteFooterLabel: "Note controls"
 rawUserDescription: "Packed user data in its raw form. Most of these fields are public and visible to all users."
 rawInfoDescription: "Extended user data in its raw form. These fields are private and can only be accessed by moderators."
 rawApDescription: "ActivityPub user data in its raw form. These fields are public and accessible to other instances."
+
+signupReason: "Signup Reason"
-- 
cgit v1.2.3-freya