From e00bba850fd4e0897451fef667fa89c94b777c01 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Jarka=20Ko=C5=A1anov=C3=A1?= Date: Wed, 1 Oct 2025 13:05:58 +0200 Subject: [PATCH] Add organization_id to spam_logs table Changelog: other --- db/docs/spam_logs.yml | 2 +- ...1105600_add_organization_id_to_spam_logs.rb | 9 +++++++++ ...d_index_for_organization_id_to_spam_logs.rb | 18 ++++++++++++++++++ ...05800_add_fk_for_organizations_spam_logs.rb | 16 ++++++++++++++++ db/schema_migrations/20251001105600 | 1 + db/schema_migrations/20251001105700 | 1 + db/schema_migrations/20251001105800 | 1 + db/structure.sql | 8 +++++++- spec/lib/gitlab/database/sharding_key_spec.rb | 1 + 9 files changed, 55 insertions(+), 2 deletions(-) create mode 100644 db/migrate/20251001105600_add_organization_id_to_spam_logs.rb create mode 100644 db/migrate/20251001105700_add_index_for_organization_id_to_spam_logs.rb create mode 100644 db/migrate/20251001105800_add_fk_for_organizations_spam_logs.rb create mode 100644 db/schema_migrations/20251001105600 create mode 100644 db/schema_migrations/20251001105700 create mode 100644 db/schema_migrations/20251001105800 diff --git a/db/docs/spam_logs.yml b/db/docs/spam_logs.yml index c04e4e3d8db031..3fff82ee218c75 100644 --- a/db/docs/spam_logs.yml +++ b/db/docs/spam_logs.yml @@ -7,6 +7,6 @@ feature_categories: description: Logs users flagged by the Akismet anti-spam integration. introduced_by_url: https://gitlab.com/gitlab-org/gitlab-foss/-/merge_requests/2266 milestone: '8.5' -gitlab_schema: gitlab_main_user +gitlab_schema: gitlab_main_org table_size: small sharding_key_issue_url: https://gitlab.com/gitlab-org/gitlab/-/issues/553470 diff --git a/db/migrate/20251001105600_add_organization_id_to_spam_logs.rb b/db/migrate/20251001105600_add_organization_id_to_spam_logs.rb new file mode 100644 index 00000000000000..875ee0b567572c --- /dev/null +++ b/db/migrate/20251001105600_add_organization_id_to_spam_logs.rb @@ -0,0 +1,9 @@ +# frozen_string_literal: true + +class AddOrganizationIdToSpamLogs < Gitlab::Database::Migration[2.3] + milestone '18.5' + + def change + add_column :spam_logs, :organization_id, :bigint + end +end diff --git a/db/migrate/20251001105700_add_index_for_organization_id_to_spam_logs.rb b/db/migrate/20251001105700_add_index_for_organization_id_to_spam_logs.rb new file mode 100644 index 00000000000000..ec7ddb18deecfc --- /dev/null +++ b/db/migrate/20251001105700_add_index_for_organization_id_to_spam_logs.rb @@ -0,0 +1,18 @@ +# frozen_string_literal: true + +class AddIndexForOrganizationIdToSpamLogs < Gitlab::Database::Migration[2.3] + milestone '18.5' + + disable_ddl_transaction! + + TABLE_NAME = :spam_logs + INDEX_NAME = 'index_spam_logs_on_organization_id' + + def up + add_concurrent_index TABLE_NAME, :organization_id, name: INDEX_NAME + end + + def down + remove_concurrent_index_by_name TABLE_NAME, INDEX_NAME + end +end diff --git a/db/migrate/20251001105800_add_fk_for_organizations_spam_logs.rb b/db/migrate/20251001105800_add_fk_for_organizations_spam_logs.rb new file mode 100644 index 00000000000000..277c62c160804d --- /dev/null +++ b/db/migrate/20251001105800_add_fk_for_organizations_spam_logs.rb @@ -0,0 +1,16 @@ +# frozen_string_literal: true + +class AddFkForOrganizationsSpamLogs < Gitlab::Database::Migration[2.3] + disable_ddl_transaction! + milestone '18.5' + + def up + add_concurrent_foreign_key :spam_logs, :organizations, column: :organization_id + end + + def down + with_lock_retries do + remove_foreign_key :spam_logs, :organizations, column: :organization_id + end + end +end diff --git a/db/schema_migrations/20251001105600 b/db/schema_migrations/20251001105600 new file mode 100644 index 00000000000000..61be5e3a94ef7a --- /dev/null +++ b/db/schema_migrations/20251001105600 @@ -0,0 +1 @@ +3a7ee21bb43ed285d435cec038232eb9579a4f593d759ad8f4d6916776dfc0b3 \ No newline at end of file diff --git a/db/schema_migrations/20251001105700 b/db/schema_migrations/20251001105700 new file mode 100644 index 00000000000000..14cd0d03b47cce --- /dev/null +++ b/db/schema_migrations/20251001105700 @@ -0,0 +1 @@ +76aa0982803c01dec59ada7dc45ea03c4ec61ed542ac1cffcee5f41f1e461598 \ No newline at end of file diff --git a/db/schema_migrations/20251001105800 b/db/schema_migrations/20251001105800 new file mode 100644 index 00000000000000..e0540d21102130 --- /dev/null +++ b/db/schema_migrations/20251001105800 @@ -0,0 +1 @@ +0ec2bbc6c7a75cfe04e68ffc9e5d2b6b6fbb831b20a20717974b62c800996f26 \ No newline at end of file diff --git a/db/structure.sql b/db/structure.sql index 666516d90ef217..a93efa4489d479 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -26284,7 +26284,8 @@ CREATE TABLE spam_logs ( updated_at timestamp without time zone NOT NULL, submitted_as_ham boolean DEFAULT false NOT NULL, recaptcha_verified boolean DEFAULT false NOT NULL, - target_id bigint + target_id bigint, + organization_id bigint ); CREATE SEQUENCE spam_logs_id_seq @@ -42332,6 +42333,8 @@ CREATE INDEX index_sop_schedules_on_user_id ON security_orchestration_policy_rul CREATE UNIQUE INDEX index_source_id_microsoft_access_tokens ON system_access_group_microsoft_graph_access_tokens USING btree (temp_source_id); +CREATE INDEX index_spam_logs_on_organization_id ON spam_logs USING btree (organization_id); + CREATE INDEX index_spam_logs_on_user_id ON spam_logs USING btree (user_id); CREATE INDEX index_sprints_iterations_cadence_id ON sprints USING btree (iterations_cadence_id); @@ -47783,6 +47786,9 @@ ALTER TABLE ONLY oauth_device_grants ALTER TABLE ONLY project_group_links ADD CONSTRAINT fk_30ec712bec FOREIGN KEY (member_role_id) REFERENCES member_roles(id) ON DELETE SET NULL; +ALTER TABLE ONLY spam_logs + ADD CONSTRAINT fk_30f09d75a5 FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE; + ALTER TABLE ONLY lists ADD CONSTRAINT fk_30f2a831f4 FOREIGN KEY (iteration_id) REFERENCES sprints(id) ON DELETE CASCADE; diff --git a/spec/lib/gitlab/database/sharding_key_spec.rb b/spec/lib/gitlab/database/sharding_key_spec.rb index e3702ee927a7e8..6c6ece2b58aa92 100644 --- a/spec/lib/gitlab/database/sharding_key_spec.rb +++ b/spec/lib/gitlab/database/sharding_key_spec.rb @@ -317,6 +317,7 @@ "abuse_report_labels" => "https://gitlab.com/gitlab-org/gitlab/-/issues/553427", "abuse_report_events" => "https://gitlab.com/gitlab-org/gitlab/-/issues/553429", "abuse_events" => "https://gitlab.com/gitlab-org/gitlab/-/issues/553427", + "spam_logs" => "https://gitlab.com/gitlab-org/gitlab/-/issues/553470", "abuse_report_assignees" => "https://gitlab.com/gitlab-org/gitlab/-/issues/553428", "labels" => "https://gitlab.com/gitlab-org/gitlab/-/issues/563889", "member_roles" => "https://gitlab.com/gitlab-org/gitlab/-/issues/567738", -- GitLab