fix: Remote Note Cleaning will delete notes embedded in a page (#16408)

* feat: preserve number of pages referencing the note

* chore: delete pages on account delete

* fix: notes on the pages are removed by CleanRemoteNotes

* test: add the simplest test for page embedded notes

* fix: section block is not considered

* fix: section block is not considered in migration

* chore: remove comments from columns

* revert unnecessary change

* add pageCount to webhook test

* fix type error on backend

1 files changed, 58 insertions, 0 deletions

diff --git a/packages/backend/migration/1755168347001-PageCountInNote.js b/packages/backend/migration/1755168347001-PageCountInNote.js
new file mode 100644
index 0000000000..9f1894ab2f
--- /dev/null
+++ b/packages/backend/migration/1755168347001-PageCountInNote.js
@@ -0,0 +1,58 @@
+/*
+ * SPDX-FileCopyrightText: syuilo and misskey-project
+ * SPDX-License-Identifier: AGPL-3.0-only
+ */
+
+export class PageCountInNote1755168347001 {
+    name = 'PageCountInNote1755168347001'
+
+    async up(queryRunner) {
+        await queryRunner.query(`ALTER TABLE "note" ADD "pageCount" smallint NOT NULL DEFAULT '0'`);
+
+        // Update existing notes
+        // block_list CTE collects all page blocks on the pages including child blocks in the section blocks.
+        // The clipped_notes CTE counts how many distinct pages each note block is referenced in.
+        // Finally, we update the note table with the count of pages for each referenced note.
+        await queryRunner.query(`
+            WITH RECURSIVE block_list AS (
+                (
+                    SELECT
+                        page.id as page_id,
+                        block as block
+                    FROM page
+                        CROSS JOIN LATERAL jsonb_array_elements(page.content) block
+                    WHERE block->>'type' = 'note' OR block->>'type' = 'section'
+                )
+                UNION ALL
+                (
+                    SELECT
+                        block_list.page_id,
+                        child_block AS block
+                    FROM LATERAL (
+                        SELECT page_id, block
+                            FROM block_list
+                            WHERE block_list.block->>'type' = 'section'
+                        ) block_list
+                        CROSS JOIN LATERAL jsonb_array_elements(block_list.block->'children') child_block
+                    WHERE child_block->>'type' = 'note' OR child_block->>'type' = 'section'
+                )
+            ),
+            clipped_notes AS (
+                SELECT
+                    (block->>'note') AS note_id,
+                    COUNT(distinct block_list.page_id) AS count
+                FROM block_list
+                WHERE block_list.block->>'type' = 'note'
+                GROUP BY block->>'note'
+            )
+            UPDATE note
+            SET "pageCount" = clipped_notes.count
+            FROM clipped_notes
+            WHERE note.id = clipped_notes.note_id;
+        `);
+    }
+
+    async down(queryRunner) {
+        await queryRunner.query(`ALTER TABLE "note" DROP COLUMN "pageCount"`);
+    }
+}