Backend: Abuse reports list filter by status and category

What does this MR do and why?

This MR adds backend code needed to implement abuse reports list filter by status and category as part of https://gitlab.com/gitlab-org/modelops/anti-abuse/team-tasks/-/issues/157.

UI design of list

Changes

1. Add status (enum status { open: 1, closed: 2 }) and resolved_at columns to abuse_reports table
2. Add new scopes to AbuseReport model to support filtering of reports displayed in the abuse reports list.
   1. .by_category
   2. .open and .closed
3. Rename .by_user scope to .by_user_id. This is done to accommodate the planned addition of .by_user scope that takes a username. .by_user_id will be replaced by .by_user eventually.
4. Update AbuseReportFinder to implement filtering by status and category.
5. Add Admin::AbuseReportEntity and Admin::AbuseReportSerializer to support returning JSON representation of abuse reports that will be consumed by Vue applications in the frontend

Database changes

db:migrate db:rollback output

AddStatusAndResolvedAtToAbuseReports

up

main: == 20230216040505 AddStatusAndResolvedAtToAbuseReports: migrating =============
main: -- add_column(:abuse_reports, :status, :integer, {:limit=>2, :default=>1, :null=>false})
main:    -> 0.0057s
main: -- add_column(:abuse_reports, :resolved_at, :datetime_with_timezone, {:default=>nil, :null=>true})
main:    -> 0.0016s
main: == 20230216040505 AddStatusAndResolvedAtToAbuseReports: migrated (0.0167s) ====

down

main: == 20230216040505 AddStatusAndResolvedAtToAbuseReports: reverting =============
main: -- remove_column(:abuse_reports, :resolved_at, :datetime_with_timezone, {:default=>nil, :null=>true})
main:    -> 0.0042s
main: -- remove_column(:abuse_reports, :status, :integer, {:limit=>2, :default=>1, :null=>false})
main:    -> 0.0013s
main: == 20230216040505 AddStatusAndResolvedAtToAbuseReports: reverted (0.0185s) ====

AddStatusAndIdIndexToAbuseReports

up

main: == 20230220035034 AddStatusAndIdIndexToAbuseReports: migrating ================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.2170s
main: -- index_exists?(:abuse_reports, [:status, :id], {:name=>"index_abuse_reports_on_status_and_id", :algorithm=>:concurrently})
main:    -> 0.0048s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0009s
main: -- add_index(:abuse_reports, [:status, :id], {:name=>"index_abuse_reports_on_status_and_id", :algorithm=>:concurrently})
main:    -> 0.0050s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20230220035034 AddStatusAndIdIndexToAbuseReports: migrated (0.2525s) =======

down

main: == 20230220035034 AddStatusAndIdIndexToAbuseReports: reverting ================
main: -- transaction_open?()
main:    -> 0.0002s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.2028s
main: -- indexes(:abuse_reports)
main:    -> 0.0077s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0009s
main: -- remove_index(:abuse_reports, {:algorithm=>:concurrently, :name=>"index_abuse_reports_on_status_and_id"})
main:    -> 0.0038s
main: -- execute("RESET statement_timeout")
main:    -> 0.0008s
main: == 20230220035034 AddStatusAndIdIndexToAbuseReports: reverted (0.2472s) =======

AddStatusCategoryAndIdIndexToAbuseReports

up

main: == 20230216071312 AddStatusCategoryAndIdIndexToAbuseReports: migrating ========
main: -- transaction_open?()
main:    -> 0.0003s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.2565s
main: -- index_exists?(:abuse_reports, [:status, :category, :id], {:name=>"index_abuse_reports_on_status_and_category_id", :algorithm=>:concurrently})
main:    -> 0.0061s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0008s
main: -- add_index(:abuse_reports, [:status, :category, :id], {:name=>"index_abuse_reports_on_status_and_category_id", :algorithm=>:concurrently})
main:    -> 0.0079s
main: -- execute("RESET statement_timeout")
main:    -> 0.0010s
main: == 20230216071312 AddStatusCategoryAndIdIndexToAbuseReports: migrated (0.3055s)

down

main: == 20230216071312 AddStatusCategoryAndIdIndexToAbuseReports: reverting ========
main: -- transaction_open?()
main:    -> 0.0001s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.1554s
main: -- indexes(:abuse_reports)
main:    -> 0.0048s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0008s
main: -- remove_index(:abuse_reports, {:algorithm=>:concurrently, :name=>"index_abuse_reports_on_status_and_category_id"})
main:    -> 0.0042s
main: -- execute("RESET statement_timeout")
main:    -> 0.0008s
main: == 20230216071312 AddStatusCategoryAndIdIndexToAbuseReports: reverted (0.1864s)

Queries

Notes:

1. The abuse reports list will always be filtered by status (open by default) hence the inclusion of "abuse_reports"."status" = 1 in all the queries.

AbuseReport.open

Raw SQL

SELECT
    "abuse_reports".*
FROM
    "abuse_reports"
WHERE
    "abuse_reports"."status" = 1
ORDER BY
    "abuse_reports"."id" DESC
LIMIT 20 OFFSET 30000;

Query plan

https://console.postgres.ai/shared/e8ee3160-0cde-45fc-aacf-339487cf3a55

AbuseReport.closed

Raw SQL

SELECT
    "abuse_reports".*
FROM
    "abuse_reports"
WHERE
    "abuse_reports"."status" = 2
ORDER BY
    "abuse_reports"."id" DESC
LIMIT 20 OFFSET 30000;

Query plan

https://console.postgres.ai/shared/d5996d7e-fa5d-4ab7-9d2d-0b001aefe33b

AbuseReport.by_category

Raw SQL

SELECT
    "abuse_reports".*
FROM
    "abuse_reports"
WHERE
    "abuse_reports"."status" = 1
    AND "abuse_reports"."category" = 3
ORDER BY
    "abuse_reports"."id" DESC
LIMIT 20 OFFSET 30000

Query plan

https://console.postgres.ai/shared/918db229-71c9-4c53-8d32-bf20e4c66e77

Indexes added

1. index_abuse_reports_on_status_and_id
2. index_abuse_reports_on_status_category_and_id

Notes

1. No index is added for category. The multi-column index above can be reused when filtering by category due to the fact that the abuse reports list will always be filtered by status (open by default).
2. Other filters not in this MR that need to be supported by indexes are by user_id (index on user_id already exists) and by reporter_id. Queries for these will have separate indexes to support them.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.