enhance(backend): refine system account (#15530)

* wip

* wip

* wip

* Update SystemAccountService.ts

* Update 1740121393164-system-accounts.js

* Update DeleteAccountService.ts

* wip

* wip

* wip

* wip

* Update 1740121393164-system-accounts.js

* Update RepositoryModule.ts

* wip

* wip

* wip

* Update ApRendererService.ts

* wip

* wip

* Update SystemAccountService.ts

* fix tests

* fix tests

* fix tests

* fix tests

* fix tests

* fix tests

* add print logs

* ログが長すぎて出てないかもしれない

* fix migration

* refactor

* fix fed-tests

* Update RelayService.ts

* merge

* Update user.test.ts

* chore: emit log

* fix: tweak sleep duration

* fix: exit 1

* fix: wait for misskey processes to become healthy

* fix: longer sleep for user deletion

* fix: make sleep longer again

* デッドロック解消の試み

https://github.com/misskey-dev/misskey/issues/15005

* Revert "デッドロック解消の試み"

This reverts commit 266141f66fb584371bbb56ef7eba04e14bcff94d.

* wip

* Update SystemAccountService.ts

---------

Co-authored-by: おさむのひと <46447427+samunohito@users.noreply.github.com>
Co-authored-by: zyoshoka <107108195+zyoshoka@users.noreply.github.com>

3 files changed, 78 insertions, 0 deletions

diff --git a/packages/backend/migration/1740121393164-system-accounts.js b/packages/backend/migration/1740121393164-system-accounts.js
new file mode 100644
index 0000000000..9490cb2b64
--- /dev/null
+++ b/packages/backend/migration/1740121393164-system-accounts.js
@@ -0,0 +1,37 @@
+/*
+ * SPDX-FileCopyrightText: syuilo and misskey-project
+ * SPDX-License-Identifier: AGPL-3.0-only
+ */
+
+export class SystemAccounts1740121393164 {
+    name = 'SystemAccounts1740121393164'
+
+    async up(queryRunner) {
+        await queryRunner.query(`CREATE TABLE "system_account" ("id" character varying(32) NOT NULL, "userId" character varying(32) NOT NULL, "type" character varying(256) NOT NULL, CONSTRAINT "PK_edb56f4aaf9ddd50ee556da97ba" PRIMARY KEY ("id"))`);
+        await queryRunner.query(`CREATE INDEX "IDX_41a3c87a37aea616ee459369e1" ON "system_account" ("userId") `);
+        await queryRunner.query(`CREATE UNIQUE INDEX "IDX_c362033aee0ea51011386a5a7e" ON "system_account" ("type") `);
+        await queryRunner.query(`ALTER TABLE "system_account" ADD CONSTRAINT "FK_41a3c87a37aea616ee459369e12" FOREIGN KEY ("userId") REFERENCES "user"("id") ON DELETE CASCADE ON UPDATE NO ACTION`);
+
+				const instanceActor = await queryRunner.query(`SELECT "id" FROM "user" WHERE "username" = 'instance.actor'`);
+				if (instanceActor.length > 0) {
+					await queryRunner.query(`INSERT INTO "system_account" ("id", "userId", "type") VALUES ('${instanceActor[0].id}', '${instanceActor[0].id}', 'actor')`);
+				}
+
+				const relayActor = await queryRunner.query(`SELECT "id" FROM "user" WHERE "username" = 'relay.actor'`);
+				if (relayActor.length > 0) {
+					await queryRunner.query(`INSERT INTO "system_account" ("id", "userId", "type") VALUES ('${relayActor[0].id}', '${relayActor[0].id}', 'relay')`);
+				}
+
+				const meta = await queryRunner.query(`SELECT "proxyAccountId" FROM "meta" ORDER BY "id" DESC LIMIT 1`);
+				if (!meta && meta.length >= 1 && meta[0].proxyAccountId) {
+					await queryRunner.query(`INSERT INTO "system_account" ("id", "userId", "type") VALUES ('${meta[0].proxyAccountId}', '${meta[0].proxyAccountId}', 'proxy')`);
+				}
+    }
+
+    async down(queryRunner) {
+        await queryRunner.query(`ALTER TABLE "system_account" DROP CONSTRAINT "FK_41a3c87a37aea616ee459369e12"`);
+        await queryRunner.query(`DROP INDEX "public"."IDX_c362033aee0ea51011386a5a7e"`);
+        await queryRunner.query(`DROP INDEX "public"."IDX_41a3c87a37aea616ee459369e1"`);
+        await queryRunner.query(`DROP TABLE "system_account"`);
+    }
+}
diff --git a/packages/backend/migration/1740129169650-system-accounts-2.js b/packages/backend/migration/1740129169650-system-accounts-2.js
new file mode 100644
index 0000000000..07270855bf
--- /dev/null
+++ b/packages/backend/migration/1740129169650-system-accounts-2.js
@@ -0,0 +1,18 @@
+/*
+ * SPDX-FileCopyrightText: syuilo and misskey-project
+ * SPDX-License-Identifier: AGPL-3.0-only
+ */
+
+export class SystemAccounts21740129169650 {
+    name = 'SystemAccounts21740129169650'
+
+    async up(queryRunner) {
+        await queryRunner.query(`ALTER TABLE "meta" DROP CONSTRAINT "FK_ab1bc0c1e209daa77b8e8d212ad"`);
+        await queryRunner.query(`ALTER TABLE "meta" DROP COLUMN "proxyAccountId"`);
+    }
+
+    async down(queryRunner) {
+        await queryRunner.query(`ALTER TABLE "meta" ADD "proxyAccountId" character varying(32)`);
+        await queryRunner.query(`ALTER TABLE "meta" ADD CONSTRAINT "FK_ab1bc0c1e209daa77b8e8d212ad" FOREIGN KEY ("proxyAccountId") REFERENCES "user"("id") ON DELETE SET NULL ON UPDATE NO ACTION`);
+    }
+}
diff --git a/packages/backend/migration/1740133121105-system-accounts-3.js b/packages/backend/migration/1740133121105-system-accounts-3.js
new file mode 100644
index 0000000000..02f9207cdc
--- /dev/null
+++ b/packages/backend/migration/1740133121105-system-accounts-3.js
@@ -0,0 +1,23 @@
+/*
+ * SPDX-FileCopyrightText: syuilo and misskey-project
+ * SPDX-License-Identifier: AGPL-3.0-only
+ */
+
+export class SystemAccounts31740133121105 {
+    name = 'SystemAccounts31740133121105'
+
+    async up(queryRunner) {
+        await queryRunner.query(`ALTER TABLE "meta" ADD "rootUserId" character varying(32)`);
+        await queryRunner.query(`ALTER TABLE "meta" ADD CONSTRAINT "FK_c80e4079d632f95eac06a9d28cc" FOREIGN KEY ("rootUserId") REFERENCES "user"("id") ON DELETE SET NULL ON UPDATE NO ACTION`);
+
+				const users = await queryRunner.query(`SELECT "id" FROM "user" WHERE "isRoot" = true LIMIT 1`);
+				if (users.length > 0) {
+					await queryRunner.query(`UPDATE "meta" SET "rootUserId" = $1`, [users[0].id]);
+				}
+    }
+
+    async down(queryRunner) {
+        await queryRunner.query(`ALTER TABLE "meta" DROP CONSTRAINT "FK_c80e4079d632f95eac06a9d28cc"`);
+        await queryRunner.query(`ALTER TABLE "meta" DROP COLUMN "rootUserId"`);
+    }
+}