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.
Changes
- Add
status
(enum status { open: 1, closed: 2 }
) andresolved_at
columns toabuse_reports
table - Add new scopes to
AbuseReport
model to support filtering of reports displayed in the abuse reports list..by_category
-
.open
and.closed
- 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. - Update
AbuseReportFinder
to implement filtering by status and category. - Add
Admin::AbuseReportEntity
andAdmin::AbuseReportSerializer
to support returningJSON
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:
- 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;
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;
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
Indexes added
- index_abuse_reports_on_status_and_id
- index_abuse_reports_on_status_category_and_id
Notes
- 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). - Other filters not in this MR that need to be supported by indexes are
by user_id
(index onuser_id
already exists) andby 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.
Edited by Eugie Limpin