Merge remote-tracking branch 'origin/develop' into instance_rules

Signed-off-by: marcin mikołajczak <git@mkljczk.pl>

25 files changed, 807 insertions, 6 deletions

diff --git a/priv/repo/migrations/20180516144508_add_trigram_extension.exs b/priv/repo/migrations/20180516144508_add_trigram_extension.exs
index b14104cc4..21ead8758 100644
--- a/priv/repo/migrations/20180516144508_add_trigram_extension.exs
+++ b/priv/repo/migrations/20180516144508_add_trigram_extension.exs
@@ -7,13 +7,13 @@ defmodule Pleroma.Repo.Migrations.AddTrigramExtension do
   require Logger
 
   def up do
-    Logger.warn("ATTENTION ATTENTION ATTENTION\n")
+    Logger.warning("ATTENTION ATTENTION ATTENTION\n")
 
-    Logger.warn(
+    Logger.warning(
       "This will try to create the pg_trgm extension on your database. If your database user does NOT have the necessary rights, you will have to do it manually and re-run the migrations.\nYou can probably do this by running the following:\n"
     )
 
-    Logger.warn(
+    Logger.warning(
       "sudo -u postgres psql pleroma_dev -c \"create extension if not exists pg_trgm\"\n"
     )
 
diff --git a/priv/repo/migrations/20190710125158_add_following_address_from_source_data.exs b/priv/repo/migrations/20190710125158_add_following_address_from_source_data.exs
index 44a3d6d2d..3a1bf677b 100644
--- a/priv/repo/migrations/20190710125158_add_following_address_from_source_data.exs
+++ b/priv/repo/migrations/20190710125158_add_following_address_from_source_data.exs
@@ -26,7 +26,7 @@ defmodule Pleroma.Repo.Migrations.AddFollowingAddressFromSourceData do
         |> Pleroma.Repo.update()
 
       user ->
-        Logger.warn("User #{user.id} / #{user.nickname} does not seem to have source_data")
+        Logger.warning("User #{user.id} / #{user.nickname} does not seem to have source_data")
     end)
   end
 end
diff --git a/priv/repo/migrations/20191118084500_data_migration_populate_user_relationships.exs b/priv/repo/migrations/20191118084500_data_migration_populate_user_relationships.exs
index 571a75160..6fa671a79 100644
--- a/priv/repo/migrations/20191118084500_data_migration_populate_user_relationships.exs
+++ b/priv/repo/migrations/20191118084500_data_migration_populate_user_relationships.exs
@@ -63,7 +63,7 @@ defmodule Pleroma.Repo.Migrations.DataMigrationPopulateUserRelationships do
             ON CONFLICT (source_id, relationship_type, target_id) DO NOTHING
             """)
           else
-            _ -> Logger.warn("Unresolved #{field} reference: (#{source_uuid}, #{target_id})")
+            _ -> Logger.warning("Unresolved #{field} reference: (#{source_uuid}, #{target_id})")
           end
         end
       end
diff --git a/priv/repo/migrations/20200811143147_ap_id_not_null.exs b/priv/repo/migrations/20200811143147_ap_id_not_null.exs
index a160daef4..dbc226663 100644
--- a/priv/repo/migrations/20200811143147_ap_id_not_null.exs
+++ b/priv/repo/migrations/20200811143147_ap_id_not_null.exs
@@ -8,7 +8,7 @@ defmodule Pleroma.Repo.Migrations.ApIdNotNull do
   require Logger
 
   def up do
-    Logger.warn(
+    Logger.warning(
       "If this migration fails please open an issue at https://git.pleroma.social/pleroma/pleroma/-/issues/new \n"
     )
 
diff --git a/priv/repo/migrations/20220220135625_upload_filter_exiftool_to_exiftool_strip_location.exs b/priv/repo/migrations/20220220135625_upload_filter_exiftool_to_exiftool_strip_location.exs
new file mode 100644
index 000000000..0878b9699
--- /dev/null
+++ b/priv/repo/migrations/20220220135625_upload_filter_exiftool_to_exiftool_strip_location.exs
@@ -0,0 +1,37 @@
+defmodule Pleroma.Repo.Migrations.UploadFilterExiftoolToExiftoolStripLocation do
+  use Ecto.Migration
+
+  alias Pleroma.ConfigDB
+
+  def up,
+    do:
+      ConfigDB.get_by_params(%{group: :pleroma, key: Pleroma.Upload})
+      |> update_filtername(
+        Pleroma.Upload.Filter.Exiftool,
+        Pleroma.Upload.Filter.Exiftool.StripLocation
+      )
+
+  def down,
+    do:
+      ConfigDB.get_by_params(%{group: :pleroma, key: Pleroma.Upload})
+      |> update_filtername(
+        Pleroma.Upload.Filter.Exiftool.StripLocation,
+        Pleroma.Upload.Filter.Exiftool
+      )
+
+  defp update_filtername(%{value: value}, from_filtername, to_filtername) do
+    new_value =
+      value
+      |> Keyword.update(:filters, [], fn filters ->
+        filters
+        |> Enum.map(fn
+          ^from_filtername -> to_filtername
+          filter -> filter
+        end)
+      end)
+
+    ConfigDB.update_or_create(%{group: :pleroma, key: Pleroma.Upload, value: new_value})
+  end
+
+  defp update_filtername(_, _, _), do: nil
+end
diff --git a/priv/repo/migrations/20220308012601_create_announcements.exs b/priv/repo/migrations/20220308012601_create_announcements.exs
new file mode 100644
index 000000000..01c9ce041
--- /dev/null
+++ b/priv/repo/migrations/20220308012601_create_announcements.exs
@@ -0,0 +1,26 @@
+defmodule Pleroma.Repo.Migrations.CreateAnnouncements do
+  use Ecto.Migration
+
+  def change do
+    create_if_not_exists table(:announcements, primary_key: false) do
+      add(:id, :uuid, primary_key: true)
+      add(:data, :map)
+      add(:starts_at, :naive_datetime)
+      add(:ends_at, :naive_datetime)
+      add(:rendered, :map)
+
+      timestamps()
+    end
+
+    create_if_not_exists table(:announcement_read_relationships) do
+      add(:user_id, references(:users, type: :uuid, on_delete: :delete_all))
+      add(:announcement_id, references(:announcements, type: :uuid, on_delete: :delete_all))
+
+      timestamps(updated_at: false)
+    end
+
+    create_if_not_exists(
+      unique_index(:announcement_read_relationships, [:user_id, :announcement_id])
+    )
+  end
+end
diff --git a/priv/repo/migrations/20220506175506_add_index_hotspots.exs b/priv/repo/migrations/20220506175506_add_index_hotspots.exs
new file mode 100644
index 000000000..e1f59bbac
--- /dev/null
+++ b/priv/repo/migrations/20220506175506_add_index_hotspots.exs
@@ -0,0 +1,17 @@
+# Pleroma: A lightweight social networking server
+# Copyright © 2017-2022 Pleroma Authors <https://pleroma.social/>
+# SPDX-License-Identifier: AGPL-3.0-only
+
+defmodule Pleroma.Repo.Migrations.AddIndexHotspots do
+  use Ecto.Migration
+
+  def change do
+    # Stop inserts into activities from doing a full-table scan of users:
+    create_if_not_exists(index(:users, [:ap_id, "COALESCE(follower_address, '')"]))
+
+    # Change two indexes and a filter recheck into one index scan:
+    create_if_not_exists(index(:following_relationships, [:follower_id, :state]))
+
+    create_if_not_exists(index(:notifications, [:user_id, :seen]))
+  end
+end
diff --git a/priv/repo/migrations/20220509180452_change_thread_visibility_to_be_local_only_aware.exs b/priv/repo/migrations/20220509180452_change_thread_visibility_to_be_local_only_aware.exs
new file mode 100644
index 000000000..ea6ae6c5c
--- /dev/null
+++ b/priv/repo/migrations/20220509180452_change_thread_visibility_to_be_local_only_aware.exs
@@ -0,0 +1,153 @@
+defmodule Pleroma.Repo.Migrations.ChangeThreadVisibilityToBeLocalOnlyAware do
+  use Ecto.Migration
+
+  def up do
+    execute("DROP FUNCTION IF EXISTS thread_visibility(actor varchar, activity_id varchar)")
+    execute(update_thread_visibility())
+  end
+
+  def down do
+    execute(
+      "DROP FUNCTION IF EXISTS thread_visibility(actor varchar, activity_id varchar, local_public varchar)"
+    )
+
+    execute(restore_thread_visibility())
+  end
+
+  def update_thread_visibility do
+    """
+    CREATE OR REPLACE FUNCTION thread_visibility(actor varchar, activity_id varchar, local_public varchar default '') RETURNS boolean AS $$
+    DECLARE
+      public varchar := 'https://www.w3.org/ns/activitystreams#Public';
+      child objects%ROWTYPE;
+      activity activities%ROWTYPE;
+      author_fa varchar;
+      valid_recipients varchar[];
+      actor_user_following varchar[];
+    BEGIN
+      --- Fetch actor following
+      SELECT array_agg(following.follower_address) INTO actor_user_following FROM following_relationships
+      JOIN users ON users.id = following_relationships.follower_id
+      JOIN users AS following ON following.id = following_relationships.following_id
+      WHERE users.ap_id = actor;
+
+      --- Fetch our initial activity.
+      SELECT * INTO activity FROM activities WHERE activities.data->>'id' = activity_id;
+
+      LOOP
+        --- Ensure that we have an activity before continuing.
+        --- If we don't, the thread is not satisfiable.
+        IF activity IS NULL THEN
+          RETURN false;
+        END IF;
+
+        --- We only care about Create activities.
+        IF activity.data->>'type' != 'Create' THEN
+          RETURN true;
+        END IF;
+
+        --- Normalize the child object into child.
+        SELECT * INTO child FROM objects
+        INNER JOIN activities ON COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id'
+        WHERE COALESCE(activity.data->'object'->>'id', activity.data->>'object') = objects.data->>'id';
+
+        --- Fetch the author's AS2 following collection.
+        SELECT COALESCE(users.follower_address, '') INTO author_fa FROM users WHERE users.ap_id = activity.actor;
+
+        --- Prepare valid recipients array.
+        valid_recipients := ARRAY[actor, public];
+        --- If we specified local public, add it.
+        IF local_public <> '' THEN
+          valid_recipients := valid_recipients || local_public;
+        END IF;
+        IF ARRAY[author_fa] && actor_user_following THEN
+          valid_recipients := valid_recipients || author_fa;
+        END IF;
+
+        --- Check visibility.
+        IF NOT valid_recipients && activity.recipients THEN
+          --- activity not visible, break out of the loop
+          RETURN false;
+        END IF;
+
+        --- If there's a parent, load it and do this all over again.
+        IF (child.data->'inReplyTo' IS NOT NULL) AND (child.data->'inReplyTo' != 'null'::jsonb) THEN
+          SELECT * INTO activity FROM activities
+          INNER JOIN objects ON COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id'
+          WHERE child.data->>'inReplyTo' = objects.data->>'id';
+        ELSE
+          RETURN true;
+        END IF;
+      END LOOP;
+    END;
+    $$ LANGUAGE plpgsql IMMUTABLE;
+    """
+  end
+
+  # priv/repo/migrations/20191007073319_create_following_relationships.exs
+  def restore_thread_visibility do
+    """
+    CREATE OR REPLACE FUNCTION thread_visibility(actor varchar, activity_id varchar) RETURNS boolean AS $$
+    DECLARE
+      public varchar := 'https://www.w3.org/ns/activitystreams#Public';
+      child objects%ROWTYPE;
+      activity activities%ROWTYPE;
+      author_fa varchar;
+      valid_recipients varchar[];
+      actor_user_following varchar[];
+    BEGIN
+      --- Fetch actor following
+      SELECT array_agg(following.follower_address) INTO actor_user_following FROM following_relationships
+      JOIN users ON users.id = following_relationships.follower_id
+      JOIN users AS following ON following.id = following_relationships.following_id
+      WHERE users.ap_id = actor;
+
+      --- Fetch our initial activity.
+      SELECT * INTO activity FROM activities WHERE activities.data->>'id' = activity_id;
+
+      LOOP
+        --- Ensure that we have an activity before continuing.
+        --- If we don't, the thread is not satisfiable.
+        IF activity IS NULL THEN
+          RETURN false;
+        END IF;
+
+        --- We only care about Create activities.
+        IF activity.data->>'type' != 'Create' THEN
+          RETURN true;
+        END IF;
+
+        --- Normalize the child object into child.
+        SELECT * INTO child FROM objects
+        INNER JOIN activities ON COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id'
+        WHERE COALESCE(activity.data->'object'->>'id', activity.data->>'object') = objects.data->>'id';
+
+        --- Fetch the author's AS2 following collection.
+        SELECT COALESCE(users.follower_address, '') INTO author_fa FROM users WHERE users.ap_id = activity.actor;
+
+        --- Prepare valid recipients array.
+        valid_recipients := ARRAY[actor, public];
+        IF ARRAY[author_fa] && actor_user_following THEN
+          valid_recipients := valid_recipients || author_fa;
+        END IF;
+
+        --- Check visibility.
+        IF NOT valid_recipients && activity.recipients THEN
+          --- activity not visible, break out of the loop
+          RETURN false;
+        END IF;
+
+        --- If there's a parent, load it and do this all over again.
+        IF (child.data->'inReplyTo' IS NOT NULL) AND (child.data->'inReplyTo' != 'null'::jsonb) THEN
+          SELECT * INTO activity FROM activities
+          INNER JOIN objects ON COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id'
+          WHERE child.data->>'inReplyTo' = objects.data->>'id';
+        ELSE
+          RETURN true;
+        END IF;
+      END LOOP;
+    END;
+    $$ LANGUAGE plpgsql IMMUTABLE;
+    """
+  end
+end
diff --git a/priv/repo/migrations/20220527134341_add_quote_url_index_to_objects.exs b/priv/repo/migrations/20220527134341_add_quote_url_index_to_objects.exs
new file mode 100644
index 000000000..d77db34cd
--- /dev/null
+++ b/priv/repo/migrations/20220527134341_add_quote_url_index_to_objects.exs
@@ -0,0 +1,17 @@
+# Pleroma: A lightweight social networking server
+# Copyright © 2017-2022 Pleroma Authors <https://pleroma.social/>
+# SPDX-License-Identifier: AGPL-3.0-only
+
+defmodule Pleroma.Repo.Migrations.AddQuoteUrlIndexToObjects do
+  use Ecto.Migration
+  @disable_ddl_transaction true
+
+  def change do
+    create_if_not_exists(
+      index(:objects, ["(data->'quoteUrl')"],
+        name: :objects_quote_url,
+        concurrently: true
+      )
+    )
+  end
+end
diff --git a/priv/repo/migrations/20220602052233_change_report_notes_content_to_text.exs b/priv/repo/migrations/20220602052233_change_report_notes_content_to_text.exs
new file mode 100644
index 000000000..9343373fc
--- /dev/null
+++ b/priv/repo/migrations/20220602052233_change_report_notes_content_to_text.exs
@@ -0,0 +1,16 @@
+defmodule Pleroma.Repo.Migrations.ChangeReportNotesContentToText do
+  use Ecto.Migration
+
+  def up do
+    alter table(:report_notes) do
+      modify(:content, :text)
+    end
+  end
+
+  # 20191203043610_create_report_notes.exs
+  def down do
+    alter table(:report_notes) do
+      modify(:content, :string)
+    end
+  end
+end
diff --git a/priv/repo/migrations/20220605185734_add_update_to_notifications_enum.exs b/priv/repo/migrations/20220605185734_add_update_to_notifications_enum.exs
new file mode 100644
index 000000000..0656c885f
--- /dev/null
+++ b/priv/repo/migrations/20220605185734_add_update_to_notifications_enum.exs
@@ -0,0 +1,51 @@
+defmodule Pleroma.Repo.Migrations.AddUpdateToNotificationsEnum do
+  use Ecto.Migration
+
+  @disable_ddl_transaction true
+
+  def up do
+    """
+    alter type notification_type add value 'update'
+    """
+    |> execute()
+  end
+
+  # 20210717000000_add_poll_to_notifications_enum.exs
+  def down do
+    alter table(:notifications) do
+      modify(:type, :string)
+    end
+
+    """
+    delete from notifications where type = 'update'
+    """
+    |> execute()
+
+    """
+    drop type if exists notification_type
+    """
+    |> execute()
+
+    """
+    create type notification_type as enum (
+      'follow',
+      'follow_request',
+      'mention',
+      'move',
+      'pleroma:emoji_reaction',
+      'pleroma:chat_mention',
+      'reblog',
+      'favourite',
+      'pleroma:report',
+      'poll'
+    )
+    """
+    |> execute()
+
+    """
+    alter table notifications
+    alter column type type notification_type using (type::notification_type)
+    """
+    |> execute()
+  end
+end
diff --git a/priv/repo/migrations/20220616163503_add_expires_at_to_user_relationships.exs b/priv/repo/migrations/20220616163503_add_expires_at_to_user_relationships.exs
new file mode 100644
index 000000000..9e117e376
--- /dev/null
+++ b/priv/repo/migrations/20220616163503_add_expires_at_to_user_relationships.exs
@@ -0,0 +1,13 @@
+# Pleroma: A lightweight social networking server
+# Copyright © 2017-2022 Pleroma Authors <https://pleroma.social/>
+# SPDX-License-Identifier: AGPL-3.0-only
+
+defmodule Pleroma.Repo.Migrations.AddExpiresAtToUserRelationships do
+  use Ecto.Migration
+
+  def change do
+    alter table(:user_relationships) do
+      add_if_not_exists(:expires_at, :utc_datetime)
+    end
+  end
+end
diff --git a/priv/repo/migrations/20220711182322_add_associated_object_id_function.exs b/priv/repo/migrations/20220711182322_add_associated_object_id_function.exs
new file mode 100644
index 000000000..76348f31a
--- /dev/null
+++ b/priv/repo/migrations/20220711182322_add_associated_object_id_function.exs
@@ -0,0 +1,37 @@
+# Pleroma: A lightweight social networking server
+# Copyright © 2017-2022 Pleroma Authors <https://pleroma.social/>
+# SPDX-License-Identifier: AGPL-3.0-only
+
+defmodule Pleroma.Repo.Migrations.AddAssociatedObjectIdFunction do
+  use Ecto.Migration
+
+  def up do
+    statement = """
+    CREATE OR REPLACE FUNCTION associated_object_id(data jsonb) RETURNS varchar AS $$
+    DECLARE
+      object_data jsonb;
+    BEGIN
+      IF jsonb_typeof(data->'object') = 'array' THEN
+        object_data := data->'object'->0;
+      ELSE
+        object_data := data->'object';
+      END IF;
+
+      IF jsonb_typeof(object_data->'id') = 'string' THEN
+        RETURN object_data->>'id';
+      ELSIF jsonb_typeof(object_data) = 'string' THEN
+        RETURN object_data#>>'{}';
+      ELSE
+        RETURN NULL;
+      END IF;
+    END;
+    $$ LANGUAGE plpgsql IMMUTABLE;
+    """
+
+    execute(statement)
+  end
+
+  def down do
+    execute("DROP FUNCTION IF EXISTS associated_object_id(data jsonb)")
+  end
+end
diff --git a/priv/repo/migrations/20220711192750_switch_to_associated_object_id_index.exs b/priv/repo/migrations/20220711192750_switch_to_associated_object_id_index.exs
new file mode 100644
index 000000000..75c1cd40b
--- /dev/null
+++ b/priv/repo/migrations/20220711192750_switch_to_associated_object_id_index.exs
@@ -0,0 +1,37 @@
+# Pleroma: A lightweight social networking server
+# Copyright © 2017-2022 Pleroma Authors <https://pleroma.social/>
+# SPDX-License-Identifier: AGPL-3.0-only
+
+defmodule Pleroma.Repo.Migrations.SwitchToAssociatedObjectIdIndex do
+  use Ecto.Migration
+  @disable_ddl_transaction true
+  @disable_migration_lock true
+
+  def up do
+    drop_if_exists(
+      index(:activities, ["(coalesce(data->'object'->>'id', data->>'object'))"],
+        name: :activities_create_objects_index
+      )
+    )
+
+    create(
+      index(:activities, ["associated_object_id(data)"],
+        name: :activities_create_objects_index,
+        concurrently: true
+      )
+    )
+  end
+
+  def down do
+    drop_if_exists(
+      index(:activities, ["associated_object_id(data)"], name: :activities_create_objects_index)
+    )
+
+    create(
+      index(:activities, ["(coalesce(data->'object'->>'id', data->>'object'))"],
+        name: :activities_create_objects_index,
+        concurrently: true
+      )
+    )
+  end
+end
diff --git a/priv/repo/migrations/20220807125023_data_migration_delete_context_objects.exs b/priv/repo/migrations/20220807125023_data_migration_delete_context_objects.exs
new file mode 100644
index 000000000..84365dbe3
--- /dev/null
+++ b/priv/repo/migrations/20220807125023_data_migration_delete_context_objects.exs
@@ -0,0 +1,18 @@
+defmodule Pleroma.Repo.Migrations.DataMigrationDeleteContextObjects do
+  use Ecto.Migration
+
+  require Logger
+
+  def up do
+    dt = NaiveDateTime.utc_now()
+
+    execute(
+      "INSERT INTO data_migrations(name, inserted_at, updated_at) " <>
+        "VALUES ('delete_context_objects', '#{dt}', '#{dt}') ON CONFLICT DO NOTHING;"
+    )
+  end
+
+  def down do
+    execute("DELETE FROM data_migrations WHERE name = 'delete_context_objects';")
+  end
+end
diff --git a/priv/repo/migrations/20220821004840_change_thread_visibility_to_use_new_object_id_index.exs b/priv/repo/migrations/20220821004840_change_thread_visibility_to_use_new_object_id_index.exs
new file mode 100644
index 000000000..bb56843cb
--- /dev/null
+++ b/priv/repo/migrations/20220821004840_change_thread_visibility_to_use_new_object_id_index.exs
@@ -0,0 +1,156 @@
+# Pleroma: A lightweight social networking server
+# Copyright © 2017-2022 Pleroma Authors <https://pleroma.social/>
+# SPDX-License-Identifier: AGPL-3.0-only
+
+defmodule Pleroma.Repo.Migrations.ChangeThreadVisibilityToUseNewObjectIdIndex do
+  use Ecto.Migration
+
+  def up do
+    execute(update_thread_visibility())
+  end
+
+  def down do
+    execute(restore_thread_visibility())
+  end
+
+  def update_thread_visibility do
+    """
+    CREATE OR REPLACE FUNCTION thread_visibility(actor varchar, activity_id varchar, local_public varchar default '') RETURNS boolean AS $$
+    DECLARE
+      public varchar := 'https://www.w3.org/ns/activitystreams#Public';
+      child objects%ROWTYPE;
+      activity activities%ROWTYPE;
+      author_fa varchar;
+      valid_recipients varchar[];
+      actor_user_following varchar[];
+    BEGIN
+      --- Fetch actor following
+      SELECT array_agg(following.follower_address) INTO actor_user_following FROM following_relationships
+      JOIN users ON users.id = following_relationships.follower_id
+      JOIN users AS following ON following.id = following_relationships.following_id
+      WHERE users.ap_id = actor;
+
+      --- Fetch our initial activity.
+      SELECT * INTO activity FROM activities WHERE activities.data->>'id' = activity_id;
+
+      LOOP
+        --- Ensure that we have an activity before continuing.
+        --- If we don't, the thread is not satisfiable.
+        IF activity IS NULL THEN
+          RETURN false;
+        END IF;
+
+        --- We only care about Create activities.
+        IF activity.data->>'type' != 'Create' THEN
+          RETURN true;
+        END IF;
+
+        --- Normalize the child object into child.
+        SELECT * INTO child FROM objects
+        INNER JOIN activities ON associated_object_id(activities.data) = objects.data->>'id'
+        WHERE associated_object_id(activity.data) = objects.data->>'id';
+
+        --- Fetch the author's AS2 following collection.
+        SELECT COALESCE(users.follower_address, '') INTO author_fa FROM users WHERE users.ap_id = activity.actor;
+
+        --- Prepare valid recipients array.
+        valid_recipients := ARRAY[actor, public];
+        --- If we specified local public, add it.
+        IF local_public <> '' THEN
+          valid_recipients := valid_recipients || local_public;
+        END IF;
+        IF ARRAY[author_fa] && actor_user_following THEN
+          valid_recipients := valid_recipients || author_fa;
+        END IF;
+
+        --- Check visibility.
+        IF NOT valid_recipients && activity.recipients THEN
+          --- activity not visible, break out of the loop
+          RETURN false;
+        END IF;
+
+        --- If there's a parent, load it and do this all over again.
+        IF (child.data->'inReplyTo' IS NOT NULL) AND (child.data->'inReplyTo' != 'null'::jsonb) THEN
+          SELECT * INTO activity FROM activities
+          INNER JOIN objects ON associated_object_id(activities.data) = objects.data->>'id'
+          WHERE child.data->>'inReplyTo' = objects.data->>'id';
+        ELSE
+          RETURN true;
+        END IF;
+      END LOOP;
+    END;
+    $$ LANGUAGE plpgsql IMMUTABLE;
+    """
+  end
+
+  # priv/repo/migrations/20220509180452_change_thread_visibility_to_be_local_only_aware.exs
+  def restore_thread_visibility do
+    """
+    CREATE OR REPLACE FUNCTION thread_visibility(actor varchar, activity_id varchar, local_public varchar default '') RETURNS boolean AS $$
+    DECLARE
+      public varchar := 'https://www.w3.org/ns/activitystreams#Public';
+      child objects%ROWTYPE;
+      activity activities%ROWTYPE;
+      author_fa varchar;
+      valid_recipients varchar[];
+      actor_user_following varchar[];
+    BEGIN
+      --- Fetch actor following
+      SELECT array_agg(following.follower_address) INTO actor_user_following FROM following_relationships
+      JOIN users ON users.id = following_relationships.follower_id
+      JOIN users AS following ON following.id = following_relationships.following_id
+      WHERE users.ap_id = actor;
+
+      --- Fetch our initial activity.
+      SELECT * INTO activity FROM activities WHERE activities.data->>'id' = activity_id;
+
+      LOOP
+        --- Ensure that we have an activity before continuing.
+        --- If we don't, the thread is not satisfiable.
+        IF activity IS NULL THEN
+          RETURN false;
+        END IF;
+
+        --- We only care about Create activities.
+        IF activity.data->>'type' != 'Create' THEN
+          RETURN true;
+        END IF;
+
+        --- Normalize the child object into child.
+        SELECT * INTO child FROM objects
+        INNER JOIN activities ON COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id'
+        WHERE COALESCE(activity.data->'object'->>'id', activity.data->>'object') = objects.data->>'id';
+
+        --- Fetch the author's AS2 following collection.
+        SELECT COALESCE(users.follower_address, '') INTO author_fa FROM users WHERE users.ap_id = activity.actor;
+
+        --- Prepare valid recipients array.
+        valid_recipients := ARRAY[actor, public];
+        --- If we specified local public, add it.
+        IF local_public <> '' THEN
+          valid_recipients := valid_recipients || local_public;
+        END IF;
+        IF ARRAY[author_fa] && actor_user_following THEN
+          valid_recipients := valid_recipients || author_fa;
+        END IF;
+
+        --- Check visibility.
+        IF NOT valid_recipients && activity.recipients THEN
+          --- activity not visible, break out of the loop
+          RETURN false;
+        END IF;
+
+        --- If there's a parent, load it and do this all over again.
+        IF (child.data->'inReplyTo' IS NOT NULL) AND (child.data->'inReplyTo' != 'null'::jsonb) THEN
+          SELECT * INTO activity FROM activities
+          INNER JOIN objects ON COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id'
+          WHERE child.data->>'inReplyTo' = objects.data->>'id';
+        ELSE
+          RETURN true;
+        END IF;
+      END LOOP;
+    END;
+    $$ LANGUAGE plpgsql IMMUTABLE;
+    """
+  end
+end
diff --git a/priv/repo/migrations/20220905011454_generate_unset_user_keys.exs b/priv/repo/migrations/20220905011454_generate_unset_user_keys.exs
new file mode 100644
index 000000000..580c38841
--- /dev/null
+++ b/priv/repo/migrations/20220905011454_generate_unset_user_keys.exs
@@ -0,0 +1,36 @@
+# Pleroma: A lightweight social networking server
+# Copyright © 2017-2022 Pleroma Authors <https://pleroma.social/>
+# SPDX-License-Identifier: AGPL-3.0-only
+
+defmodule User do
+  use Ecto.Schema
+
+  schema "users" do
+    field(:keys, :string)
+    field(:local, :boolean, default: true)
+  end
+end
+
+defmodule Pleroma.Repo.Migrations.GenerateUnsetUserKeys do
+  use Ecto.Migration
+  import Ecto.Query
+  alias Pleroma.Keys
+  alias Pleroma.Repo
+
+  def change do
+    query =
+      from(u in User,
+        where: u.local == true,
+        where: is_nil(u.keys),
+        select: u
+      )
+
+    Repo.stream(query)
+    |> Enum.each(fn user ->
+      with {:ok, pem} <- Keys.generate_rsa_pem() do
+        Ecto.Changeset.cast(user, %{keys: pem}, [:keys])
+        |> Repo.update()
+      end
+    end)
+  end
+end
diff --git a/priv/repo/migrations/20221103014611_create_oban_peers.exs b/priv/repo/migrations/20221103014611_create_oban_peers.exs
new file mode 100644
index 000000000..cb522a056
--- /dev/null
+++ b/priv/repo/migrations/20221103014611_create_oban_peers.exs
@@ -0,0 +1,7 @@
+defmodule Pleroma.Repo.Migrations.CreateObanPeers do
+  use Ecto.Migration
+
+  def up, do: Oban.Migrations.up(version: 11)
+
+  def down, do: Oban.Migrations.down(version: 11)
+end
diff --git a/priv/repo/migrations/20221103014728_swap_primary_oban_indexes.exs b/priv/repo/migrations/20221103014728_swap_primary_oban_indexes.exs
new file mode 100644
index 000000000..54e2503de
--- /dev/null
+++ b/priv/repo/migrations/20221103014728_swap_primary_oban_indexes.exs
@@ -0,0 +1,26 @@
+defmodule Pleroma.Repo.Migrations.SwapPrimaryObanIndexes do
+  use Ecto.Migration
+
+  @disable_ddl_transaction true
+  @disable_migration_lock true
+
+  def change do
+    create_if_not_exists(
+      index(
+        :oban_jobs,
+        [:state, :queue, :priority, :scheduled_at, :id],
+        concurrently: true,
+        prefix: "public"
+      )
+    )
+
+    drop_if_exists(
+      index(
+        :oban_jobs,
+        [:queue, :state, :priority, :scheduled_at, :id],
+        concurrently: true,
+        prefix: "public"
+      )
+    )
+  end
+end
diff --git a/priv/repo/migrations/20221111164213_deprecate_quack.exs b/priv/repo/migrations/20221111164213_deprecate_quack.exs
new file mode 100644
index 000000000..d30fe8117
--- /dev/null
+++ b/priv/repo/migrations/20221111164213_deprecate_quack.exs
@@ -0,0 +1,24 @@
+# Pleroma: A lightweight social networking server
+# Copyright © 2017-2022 Pleroma Authors <https://pleroma.social/>
+# SPDX-License-Identifier: AGPL-3.0-only
+
+defmodule Pleroma.Repo.Migrations.DeprecateQuack do
+  use Ecto.Migration
+  alias Pleroma.ConfigDB
+
+  def up do
+    :quack
+    |> ConfigDB.get_all_by_group()
+    |> Enum.each(&ConfigDB.delete/1)
+
+    logger_config = ConfigDB.get_by_group_and_key(:logger, :backends)
+
+    if not is_nil(logger_config) do
+      %{value: backends} = logger_config
+      new_backends = backends -- [Quack.Logger]
+      {:ok, _} = ConfigDB.update_or_create(%{group: :logger, key: :backends, value: new_backends})
+    end
+  end
+
+  def down, do: :ok
+end
diff --git a/priv/repo/migrations/20221216052127_add_state_to_backups.exs b/priv/repo/migrations/20221216052127_add_state_to_backups.exs
new file mode 100644
index 000000000..73b30fc35
--- /dev/null
+++ b/priv/repo/migrations/20221216052127_add_state_to_backups.exs
@@ -0,0 +1,21 @@
+# Pleroma: A lightweight social networking server
+# Copyright © 2017-2022 Pleroma Authors <https://pleroma.social/>
+# SPDX-License-Identifier: AGPL-3.0-only
+
+defmodule Pleroma.Repo.Migrations.AddStateToBackups do
+  use Ecto.Migration
+
+  def up do
+    alter table(:backups) do
+      add(:state, :integer, default: 5)
+      add(:processed_number, :integer, default: 0)
+    end
+  end
+
+  def down do
+    alter table(:backups) do
+      remove(:state)
+      remove(:processed_number)
+    end
+  end
+end
diff --git a/priv/repo/migrations/20230306112859_instances_add_metadata.exs b/priv/repo/migrations/20230306112859_instances_add_metadata.exs
new file mode 100644
index 000000000..898f5220e
--- /dev/null
+++ b/priv/repo/migrations/20230306112859_instances_add_metadata.exs
@@ -0,0 +1,14 @@
+# Pleroma: A lightweight social networking server
+# Copyright © 2017-2023 Pleroma Authors <https://pleroma.social/>
+# SPDX-License-Identifier: AGPL-3.0-only
+
+defmodule Pleroma.Repo.Migrations.InstancesAddMetadata do
+  use Ecto.Migration
+
+  def change do
+    alter table(:instances) do
+      add(:metadata, :map)
+      add(:metadata_updated_at, :utc_datetime)
+    end
+  end
+end
diff --git a/priv/repo/migrations/20230422154018_drop_unused_indexes.exs b/priv/repo/migrations/20230422154018_drop_unused_indexes.exs
new file mode 100644
index 000000000..0d877a798
--- /dev/null
+++ b/priv/repo/migrations/20230422154018_drop_unused_indexes.exs
@@ -0,0 +1,73 @@
+defmodule Pleroma.Repo.Migrations.DropUnusedIndexes do
+  use Ecto.Migration
+
+  @disable_ddl_transaction true
+  @disable_migration_lock true
+
+  def up do
+    drop_if_exists(
+      index(:activities, ["(data->>'actor')", "inserted_at desc"], name: :activities_actor_index)
+    )
+
+    drop_if_exists(index(:activities, ["(data->'to')"], name: :activities_to_index))
+
+    drop_if_exists(index(:activities, ["(data->'cc')"], name: :activities_cc_index))
+
+    drop_if_exists(index(:activities, ["(split_part(actor, '/', 3))"], name: :activities_hosts))
+
+    drop_if_exists(
+      index(:activities, ["(data->'object'->>'inReplyTo')"], name: :activities_in_reply_to)
+    )
+
+    drop_if_exists(
+      index(:activities, ["((data #> '{\"object\",\"likes\"}'))"], name: :activities_likes)
+    )
+  end
+
+  def down do
+    create_if_not_exists(
+      index(:activities, ["(data->>'actor')", "inserted_at desc"],
+        name: :activities_actor_index,
+        concurrently: true
+      )
+    )
+
+    create_if_not_exists(
+      index(:activities, ["(data->'to')"],
+        name: :activities_to_index,
+        using: :gin,
+        concurrently: true
+      )
+    )
+
+    create_if_not_exists(
+      index(:activities, ["(data->'cc')"],
+        name: :activities_cc_index,
+        using: :gin,
+        concurrently: true
+      )
+    )
+
+    create_if_not_exists(
+      index(:activities, ["(split_part(actor, '/', 3))"],
+        name: :activities_hosts,
+        concurrently: true
+      )
+    )
+
+    create_if_not_exists(
+      index(:activities, ["(data->'object'->>'inReplyTo')"],
+        name: :activities_in_reply_to,
+        concurrently: true
+      )
+    )
+
+    create_if_not_exists(
+      index(:activities, ["((data #> '{\"object\",\"likes\"}'))"],
+        name: :activities_likes,
+        using: :gin,
+        concurrently: true
+      )
+    )
+  end
+end
diff --git a/priv/repo/migrations/20230504173400_remove_user_ap_enabled.exs b/priv/repo/migrations/20230504173400_remove_user_ap_enabled.exs
new file mode 100644
index 000000000..0aea41324
--- /dev/null
+++ b/priv/repo/migrations/20230504173400_remove_user_ap_enabled.exs
@@ -0,0 +1,13 @@
+# Pleroma: A lightweight social networking server
+# Copyright © 2017-2023 Pleroma Authors <https://pleroma.social/>
+# SPDX-License-Identifier: AGPL-3.0-only
+
+defmodule Pleroma.Repo.Migrations.RemoveUserApEnabled do
+  use Ecto.Migration
+
+  def change do
+    alter table(:users) do
+      remove(:ap_enabled, :boolean, default: false, null: false)
+    end
+  end
+end
diff --git a/priv/repo/migrations/20231107200724_consolidate_email_queues.exs b/priv/repo/migrations/20231107200724_consolidate_email_queues.exs
new file mode 100644
index 000000000..63f5af369
--- /dev/null
+++ b/priv/repo/migrations/20231107200724_consolidate_email_queues.exs
@@ -0,0 +1,9 @@
+defmodule Pleroma.Repo.Migrations.ConsolidateEmailQueues do
+  use Ecto.Migration
+
+  def change do
+    execute(
+      "UPDATE oban_jobs SET queue = 'mailer' WHERE queue in ('digest_emails', 'new_users_digest')"
+    )
+  end
+end