Skip to content
Snippets Groups Projects

Store abuse report labels separate from project/group labels

Merged Eugie Limpin requested to merge el-separate-abuse-report-labels-db-table into master

Context

This MR implements a part of https://gitlab.com/gitlab-org/gitlab/-/issues/430957+ and is MR no. 3 of 3:

  1. Move base label behavior to a concern
  2. Create AbuseReportLabelType GraphQL type
  3. Store abuse report labels separate from project/group labels - :point_left_tone2: YOU ARE HERE

Gist of https://gitlab.com/gitlab-org/gitlab/-/issues/430957+

Abuse report labels are currently implemented as Labels and stored in labels DB table. Since these labels are for abuse reports they do not have a project_id or a group_id and should be under gitlab_main_clusterwide schema (labels is under gitlab_main_cell).

The goal is to introduce a separate model and database table for abuse report labels so that both labels and abuse report labels features are compatible with Cells 1.0.

What does this MR do and why?

This MR introduces AntiAbuse::Reports::Label and AntiAbuse::Reports::LabelLink models backed by abuse_report_labels and abuse_report_label_links DB tables. It also updates the label_links and labels association of AbuseReport to use these new models as follows:

Before
erDiagram
    AbuseReport ||--o{ LabelLink : has_many
    Label ||--o{ LabelLink : has_many
    Label {
        bigint group_id
        bigint project_id
    }
After
erDiagram
    AbuseReport ||--o{ "AntiAbuse::Reports::LabelLink": has_many
    "AntiAbuse::Reports::Label" ||--o{ "AntiAbuse::Reports::LabelLink" : has_many

Finally, Admin::AbuseReportLabel < Label model is removed and all references to it are updated to use the new AntiAbuse::Reports::Label model so abuse report labels records go to abuse_report_labels table.

Questions

  1. Do we need to migrate any abuse report label data from labels to abuse_report_labels?

    No. The feature is currently not actively used by T&S and is behind a disabled feature flag. There is one abuse report record created in labels table in production that was created for testing and is planned to be deleted through a change request.

  2. What is the anticipated growth for the new table over the next 3 months, 6 months, 1 year? What assumptions are these based on?

    Abuse report labels will be used by T&S team (instance admins) in Gitlab.com. There is no limit but with normal use we expect at most a few hundred (overestimate) records in this table.

  3. How many reads and writes per hour would you expect this table to have in 3 months, 6 months, 1 year? Under what circumstances are rows updated? What assumptions are these based on?

    We expect labels to be created in bulk or a few at a time and updated rarely (maybe update description, title, color). Reads will happen only when visiting the abuse reports list and abuse report show page.

  4. Based on the anticipated data volume and access patterns, does the new table pose an availability risk to GitLab.com or self-managed instances?

    No.

  5. Does the proposed design scale to support the needs of GitLab.com and self-managed customers?

    Yes.

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Screenshots or screen recordings

Screen_Recording_2024-10-17_at_11.41.24_AM

How to set up and validate locally

  1. In rails console, enable the feature flag
    > Feature.enable(:abuse_report_labels)
  2. Login with an admin user (root)
  3. Go to abuse reports page (http://localhost:3000/admin/abuse_reports) and click on any abuse report
  4. Validate that creating, searching, adding, and removing labels to the abuse report works (see screencast above).
Edited by Eugie Limpin

Merge request reports

Loading
Loading

Activity

Filter activity
  • Approvals
  • Assignees & reviewers
  • Comments (from bots)
  • Comments (from users)
  • Commits & branches
  • Edits
  • Labels
  • Lock status
  • Mentions
  • Merge request status
  • Tracking
  • Ghost User
  • Reviewer roulette

    Category Reviewer Maintainer
    backend @minahilnichols profile link current availability (UTC-4, 12 hours behind author) @aakriti.gupta profile link current availability (UTC+2, 6 hours behind author)
    database @ck3g profile link current availability (UTC+2, 6 hours behind author) @OmarQunsulGitlab profile link current availability (UTC+2, 6 hours behind author)
    frontend @marina.mosti profile link current availability (UTC+2, 6 hours behind author) @pburdette profile link current availability (UTC-4, 12 hours behind author)
    groupauthorization Reviewer review is optional for groupauthorization @mokhax profile link current availability (UTC-6, 14 hours behind author)

    Please refer to documentation page for guidance on how you can benefit from the Reviewer Roulette, or use the GitLab Review Workload Dashboard to find other available reviewers.

    If needed, you can retry the :repeat: danger-review job that generated this comment.

    Generated by :no_entry_sign: Danger

  • Bundle size analysis [beta]

    This compares changes in bundle size for entry points between the commits 6d4139cb and 5553b136

    :sparkles: Special assets

    Entrypoint / Name Size before Size after Diff Diff in percent
    average 4.38 MB 4.38 MB - 0.0 %
    mainChunk 3.31 MB 3.31 MB - 0.0 %

    Note: We do not have exact data for 6d4139cb. So we have used data from: 081fd457.
    The target commit was too new, so we used the latest commit from master we have info on.
    It might help to rerun the bundle-size-review job
    This might mean that you have a few false positives in this report. If something unrelated to your code changes is reported, you can check this comparison in order to see if they caused this change.

    Please look at the full report for more details


    Read more about how this report works.

    Generated by :no_entry_sign: Danger

  • Eugie Limpin added 1 commit

    added 1 commit

    Compare with previous version

  • Eugie Limpin added 4 commits

    added 4 commits

    • e58abf2e - Move base label code to a concern
    • 72da7ad2 - Store abuse report labels separate from project/group labels
    • 66e5019e - Create LabelInterface to share common fields for Label types
    • ce723a12 - Rename Labels::ActiveRecord to LabelConcern

    Compare with previous version

  • Ghost User
  • Ghost User
  • Ghost User
  • Eugie Limpin added 3 commits

    added 3 commits

    • 00c129e1 - Store abuse report labels separate from project/group labels
    • 93da1207 - Create LabelInterface to share common fields for Label types
    • 5553b136 - Rename Labels::ActiveRecord to LabelConcern

    Compare with previous version

  • Eugie Limpin changed title from Draft: Abuse report labels to Draft: Store abuse report labels separate from project/group labels

    changed title from Draft: Abuse report labels to Draft: Store abuse report labels separate from project/group labels

  • Eugie Limpin mentioned in merge request !168474 (merged)

    mentioned in merge request !168474 (merged)

  • Eugie Limpin mentioned in merge request !168473 (merged)

    mentioned in merge request !168473 (merged)

  • Eugie Limpin changed milestone to %17.5

    changed milestone to %17.5

  • Eugie Limpin changed the description

    changed the description

  • Eugie Limpin changed the description

    changed the description

  • Eugie Limpin changed the description

    changed the description

  • Database migrations (on the main database)

    Migrations included in this change have been executed on gitlab.com data for testing purposes. For details, please see the migration testing pipeline (limited access).

    Migration Type Total runtime Result DB size change
    20241004035619 - CreateAbuseReportLabels Regular 5.5 s :white_check_mark: +24.00 KiB
    20241004070554 - CreateAbuseReportLabelLinks Regular 4.7 s :white_check_mark: +40.00 KiB
    20241007091845 - AddIndexToAbuseReportLabelsTitleColumn Regular 5.1 s :white_check_mark: +16.00 KiB
    Runtime Histogram for all migrations
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 4
    0.1 seconds - 1 second 8
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20241004035619 - CreateAbuseReportLabels

    • Type: Regular
    • Duration: 5.5 s
    • Database size change: +24.00 KiB
    Calls Total Time Max Time Mean Time Rows Query
    1 56.2 ms 56.2 ms 56.2 ms 0
    CREATE TABLE "abuse_report_labels" ("id" bigserial primary key, "created_at" timestamptz NOT NULL, "updated_at" timestamptz NOT NULL, "cached_markdown_version" integer, "title" text NOT NULL, "color" text, "description" text, "description_html" text, CONSTRAINT check_e264245e2a CHECK (char_length("title") <= 255), CONSTRAINT check_c7a15f74dc CHECK (char_length("color") <= 7), CONSTRAINT check_034642a23f CHECK (char_length("description") <= 500), CONSTRAINT check_7957e7e95f CHECK (char_length("description_html") <= 1000))
    1 0.0 ms 0.0 ms 0.0 ms 1
    SELECT "feature_gates"."key", "feature_gates"."value"  FROM "feature_gates"  WHERE "feature_gates"."feature_key" = $1
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for CreateAbuseReportLabels
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 1
    0.1 seconds - 1 second 3
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20241004070554 - CreateAbuseReportLabelLinks

    • Type: Regular
    • Duration: 4.7 s
    • Database size change: +40.00 KiB
    Calls Total Time Max Time Mean Time Rows Query
    1 12.2 ms 12.2 ms 12.2 ms 0
    CREATE TABLE "abuse_report_label_links" ("id" bigserial primary key, "abuse_report_id" bigint, "abuse_report_label_id" bigint, "created_at" timestamptz NOT NULL, "updated_at" timestamptz NOT NULL, CONSTRAINT "fk_rails_e15ea8b6bc" FOREIGN KEY ("abuse_report_id") REFERENCES "abuse_reports" ("id") ON DELETE CASCADE, CONSTRAINT "fk_rails_e1a10f7c4e" FOREIGN KEY ("abuse_report_label_id") REFERENCES "abuse_report_labels" ("id") ON DELETE CASCADE)
    1 2.0 ms 2.0 ms 2.0 ms 0
    CREATE INDEX "index_abuse_report_label_links_on_abuse_report_label_id" ON "abuse_report_label_links" ("abuse_report_label_id")
    1 1.9 ms 1.9 ms 1.9 ms 0
    CREATE INDEX "index_abuse_report_label_links_on_abuse_report_id" ON "abuse_report_label_links" ("abuse_report_id")
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for CreateAbuseReportLabelLinks
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 1
    0.1 seconds - 1 second 4
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20241007091845 - AddIndexToAbuseReportLabelsTitleColumn

    • Type: Regular
    • Duration: 5.1 s
    • Database size change: +16.00 KiB
    Calls Total Time Max Time Mean Time Rows Query
    1 5.6 ms 5.6 ms 5.6 ms 0
    CREATE INDEX CONCURRENTLY "index_abuse_report_labels_on_title_trigram" ON "abuse_report_labels" USING gin ("title" gin_trgm_ops)
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for AddIndexToAbuseReportLabelsTitleColumn
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 2
    0.1 seconds - 1 second 1
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Other information

    No other migrations pending on GitLab.com

    Clone details
    Clone ID Clone Created At Clone Data Timestamp Expected Removal Time
    database-testing-3770159-15610910-main 2024-10-08T04:54:36Z 2024-10-08T04:09:55Z 2024-10-08 17:04:19 +0000
    database-testing-3770159-15610910-ci 2024-10-08T04:54:36Z 2024-10-08T04:45:05Z 2024-10-08 17:04:19 +0000

    Job artifacts

    Database migrations (on the ci database)

    Migrations included in this change have been executed on gitlab.com data for testing purposes. For details, please see the migration testing pipeline (limited access).

    Migration Type Total runtime Result DB size change
    20241004035619 - CreateAbuseReportLabels Regular 7.5 s :white_check_mark: +48.00 KiB
    20241004070554 - CreateAbuseReportLabelLinks Regular 7.1 s :white_check_mark: +40.00 KiB
    20241007091845 - AddIndexToAbuseReportLabelsTitleColumn Regular 7.4 s :white_check_mark: +16.00 KiB
    Runtime Histogram for all migrations
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 3
    0.1 seconds - 1 second 14
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20241004035619 - CreateAbuseReportLabels

    • Type: Regular
    • Duration: 7.5 s
    • Database size change: +48.00 KiB
    Calls Total Time Max Time Mean Time Rows Query
    1 16.7 ms 16.7 ms 16.7 ms 0
    CREATE TABLE "abuse_report_labels" ("id" bigserial primary key, "created_at" timestamptz NOT NULL, "updated_at" timestamptz NOT NULL, "cached_markdown_version" integer, "title" text NOT NULL, "color" text, "description" text, "description_html" text, CONSTRAINT check_e264245e2a CHECK (char_length("title") <= 255), CONSTRAINT check_c7a15f74dc CHECK (char_length("color") <= 7), CONSTRAINT check_034642a23f CHECK (char_length("description") <= 500), CONSTRAINT check_7957e7e95f CHECK (char_length("description_html") <= 1000))
    1 0.5 ms 0.5 ms 0.5 ms 0
    CREATE TRIGGER gitlab_schema_write_trigger_for_abuse_report_labels BEFORE INSERT OR
    UPDATE OR DELETE OR TRUNCATE ON abuse_report_labels FOR EACH STATEMENT EXECUTE FUNCTION gitlab_schema_prevent_write()
    1 0.1 ms 0.1 ms 0.1 ms 1
    SELECT COUNT(*) from information_schema.triggers
    WHERE event_object_table = $1 AND trigger_name = $2
    1 0.1 ms 0.1 ms 0.1 ms 1
    SELECT table_name
    FROM information_schema.tables
    WHERE table_name = $1 AND table_schema = current_schema()
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for CreateAbuseReportLabels
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 1
    0.1 seconds - 1 second 5
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20241004070554 - CreateAbuseReportLabelLinks

    • Type: Regular
    • Duration: 7.1 s
    • Database size change: +40.00 KiB
    Calls Total Time Max Time Mean Time Rows Query
    1 16.5 ms 16.5 ms 16.5 ms 0
    CREATE TABLE "abuse_report_label_links" ("id" bigserial primary key, "abuse_report_id" bigint, "abuse_report_label_id" bigint, "created_at" timestamptz NOT NULL, "updated_at" timestamptz NOT NULL, CONSTRAINT "fk_rails_e15ea8b6bc" FOREIGN KEY ("abuse_report_id") REFERENCES "abuse_reports" ("id") ON DELETE CASCADE, CONSTRAINT "fk_rails_e1a10f7c4e" FOREIGN KEY ("abuse_report_label_id") REFERENCES "abuse_report_labels" ("id") ON DELETE CASCADE)
    1 1.9 ms 1.9 ms 1.9 ms 0
    CREATE INDEX "index_abuse_report_label_links_on_abuse_report_label_id" ON "abuse_report_label_links" ("abuse_report_label_id")
    1 1.6 ms 1.6 ms 1.6 ms 0
    CREATE INDEX "index_abuse_report_label_links_on_abuse_report_id" ON "abuse_report_label_links" ("abuse_report_id")
    1 0.3 ms 0.3 ms 0.3 ms 0
    CREATE TRIGGER gitlab_schema_write_trigger_for_abuse_report_label_links BEFORE INSERT OR
    UPDATE OR DELETE OR TRUNCATE ON abuse_report_label_links FOR EACH STATEMENT EXECUTE FUNCTION gitlab_schema_prevent_write()
    1 0.0 ms 0.0 ms 0.0 ms 1
    SELECT table_name
    FROM information_schema.tables
    WHERE table_name = $1 AND table_schema = current_schema()
    1 0.0 ms 0.0 ms 0.0 ms 1
    SELECT COUNT(*) from information_schema.triggers
    WHERE event_object_table = $1 AND trigger_name = $2
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for CreateAbuseReportLabelLinks
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 1
    0.1 seconds - 1 second 7
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20241007091845 - AddIndexToAbuseReportLabelsTitleColumn

    • Type: Regular
    • Duration: 7.4 s
    • Database size change: +16.00 KiB
    Calls Total Time Max Time Mean Time Rows Query
    1 6.6 ms 6.6 ms 6.6 ms 0
    CREATE INDEX CONCURRENTLY "index_abuse_report_labels_on_title_trigram" ON "abuse_report_labels" USING gin ("title" gin_trgm_ops)
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for AddIndexToAbuseReportLabelsTitleColumn
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 1
    0.1 seconds - 1 second 2
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Other information

    No other migrations pending on GitLab.com

    Clone details
    Clone ID Clone Created At Clone Data Timestamp Expected Removal Time
    database-testing-3770159-15610910-main 2024-10-08T04:54:36Z 2024-10-08T04:09:55Z 2024-10-08 17:04:19 +0000
    database-testing-3770159-15610910-ci 2024-10-08T04:54:36Z 2024-10-08T04:45:05Z 2024-10-08 17:04:19 +0000

    Job artifacts


    Brought to you by gitlab-org/database-team/gitlab-com-database-testing. Epic

    Edited by ****
  • Eugie Limpin added 2130 commits

    added 2130 commits

    • 5553b136...ee77d283 - 2128 commits from branch master
    • 9453d986 - Store abuse report labels separate from project/group labels
    • 733aed6a - Update DB Dictionary to add introduced_by_url values

    Compare with previous version

  • Eugie Limpin added 1 commit

    added 1 commit

    • b402386b - Store abuse report labels separate from project/group labels

    Compare with previous version

  • Eugie Limpin added 1 commit

    added 1 commit

    • 50fef099 - Store abuse report labels separate from project/group labels

    Compare with previous version

  • Eugie Limpin changed milestone to %17.6

    changed milestone to %17.6

  • Eugie Limpin marked this merge request as ready

    marked this merge request as ready

  • removed frontend label

  • Eugie Limpin changed the description

    changed the description

  • Eugie Limpin mentioned in merge request !169106 (merged)

    mentioned in merge request !169106 (merged)

  • Eugie Limpin added 2 commits

    added 2 commits

    • be920683 - Add AntiAbuse::Reports::LabelEntity to serialize abuse report labels
    • 4d0fe097 - Store abuse report labels separate from project/group labels

    Compare with previous version

  • A deleted user added frontend label

    added frontend label

  • Eugie Limpin added 693 commits

    added 693 commits

    Compare with previous version

  • Eugie Limpin added 1 commit

    added 1 commit

    • 1d7e800e - Update specs for AbuseReport model

    Compare with previous version

  • Eugie Limpin changed the description

    changed the description

  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Please register or sign in to reply
    Loading