diff --git a/db/docs/spam_logs.yml b/db/docs/spam_logs.yml index c04e4e3d8db0318b523b9d2a37dfeec599a8facb..3fff82ee218c75622270a1a642ab9053c8b5b3b4 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 0000000000000000000000000000000000000000..875ee0b567572c407ae89a5fbe2adb920e317927 --- /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 0000000000000000000000000000000000000000..ec7ddb18deecfcfc0f8a88acacd6c5ee7ae99fb2 --- /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 0000000000000000000000000000000000000000..277c62c160804de6b3fd55ceaf8cd7a578f17d34 --- /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 0000000000000000000000000000000000000000..61be5e3a94ef7a799214416893b22ee25fa76938 --- /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 0000000000000000000000000000000000000000..14cd0d03b47ccebcffe1bd24046ec828a680ea82 --- /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 0000000000000000000000000000000000000000..e0540d21102130062f635ca54212093b3df421bc --- /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 666516d90ef217690e2df715842ffb86f9571902..a93efa4489d4798979a1aa989a6a1f3c8c5b41e0 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 e3702ee927a7e8619e8559eb330d56fe675d6882..6c6ece2b58aa9290078ab5797695f7447013fee3 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",