Sliding Sync: Reset `forgotten` status when membership changes (like rejoining a room) (#17835)

Reset `sliding_sync_membership_snapshots` -> `forgotten` status when
membership changes (like rejoining a room).

Fix https://github.com/element-hq/synapse/issues/17781

### What was the problem before?

Previously, if someone used `/forget` on one of their rooms, it would
update `sliding_sync_membership_snapshots` as expected but when someone
rejoined the room (or had any membership change), the upsert didn't
overwrite and reset the `forgotten` status so it remained `forgotten`
and invisible down the Sliding Sync endpoint.

2 files changed, 23 insertions, 0 deletions

diff --git a/synapse/storage/schema/__init__.py b/synapse/storage/schema/__init__.py

index f171f4568a..934e1ccced 100644
--- a/synapse/storage/schema/__init__.py
+++ b/synapse/storage/schema/__init__.py
@@ -153,6 +153,8 @@ Changes in SCHEMA_VERSION = 87
 Changes in SCHEMA_VERSION = 88
     - MSC4140: Add `delayed_events` table that keeps track of events that are to
       be posted in response to a resettable timeout or an on-demand action.
+    - Add background update to fix data integrity issue in the
+      `sliding_sync_membership_snapshots` -> `forgotten` column
 """
 
 
diff --git a/synapse/storage/schema/main/delta/88/02_fix_sliding_sync_membership_snapshots_forgotten_column.sql b/synapse/storage/schema/main/delta/88/02_fix_sliding_sync_membership_snapshots_forgotten_column.sql
new file mode 100644

index 0000000000..4de46af2fc
--- /dev/null
+++ b/synapse/storage/schema/main/delta/88/02_fix_sliding_sync_membership_snapshots_forgotten_column.sql
@@ -0,0 +1,21 @@
+--
+-- This file is licensed under the Affero General Public License (AGPL) version 3.
+--
+-- Copyright (C) 2024 New Vector, Ltd
+--
+-- This program is free software: you can redistribute it and/or modify
+-- it under the terms of the GNU Affero General Public License as
+-- published by the Free Software Foundation, either version 3 of the
+-- License, or (at your option) any later version.
+--
+-- See the GNU Affero General Public License for more details:
+-- <https://www.gnu.org/licenses/agpl-3.0.html>.
+
+-- Add a background update to update the `sliding_sync_membership_snapshots` ->
+-- `forgotten` column to be in sync with the `room_memberships` table.
+--
+-- For any room that someone has forgotten and subsequently re-joined or had any new
+-- membership on, we need to go and update the column to match the `room_memberships`
+-- table as it has fallen out of sync.
+INSERT INTO background_updates (ordering, update_name, progress_json) VALUES
+  (8802, 'sliding_sync_membership_snapshots_fix_forgotten_column_bg_update', '{}');