Remove the chat_names -> ci_pipeline_chat_data foreign key

Within !73146 (merged), we added loose foreign key definitions for the chat_names table.

In this issue, we're going to attempt to remove the foreign key constraint on staging and later on production via a change request.

The following foreign key will be removed:

TABLE "ci_pipeline_chat_data" CONSTRAINT "fk_rails_f300456b63" FOREIGN KEY (chat_name_id) REFERENCES chat_names(id) ON DELETE CASCADE

The ci_pipeline_chat_data is quite small, so in case of a rollback, we can easily re-introduce the foreign key.

Verification steps

• Pipeline passes without the foreign key
  • Pipeline: !73645 (closed)
  • FK missing errors (can be ignored)
• Check if application code safely degrades in case of inconsistent data (add notes below)
  • Referencing the already removed chat_name association returns nil

    chat_name = FactoryBot.create(:chat_name, user: User.first, integration: Integration.first)
    pipeline = Ci::pipeline.first
    pipeline_chat_data = pipeline.create_chat_data!(response_url: 'http://example.com', chat_name_id: chat_name.id)
    pipeline.destroy
    
    pipeline_chat_data.reload.chat_name
      ChatName Load (1.1ms)  SELECT "chat_names".* FROM "chat_names" WHERE "chat_names"."id" = 1 LIMIT 1
    => nil

  • The "main" entrypoint to chat_name through chat_data already handles nils.
  • Errors could be raised here and here
• Change request issue is created
• FK is removed on staging
• FK is removed on production
• Verify the cleanup process using our exposed Prometheus metrics.

Rollback steps

These steps will restore the original foreign key.

Add a NOT VALID foreign key:

ALTER TABLE ci_pipeline_chat_data 
ADD CONSTRAINT fk_rails_f300456b63 
FOREIGN KEY (chat_name_id) 
REFERENCES chat_names (id) 
ON DELETE CASCADE 
NOT VALID;

Verify consistency:

SELECT 1 FROM ci_pipeline_chat_data
WHERE
NOT EXISTS (SELECT 1 FROM chat_names WHERE chat_names.id=ci_pipeline_chat_data.chat_name_id)
LIMIT 1

If the query returns 1, then we need to manually remove the orphaned rows:

DELETE FROM ci_pipeline_chat_data
WHERE
NOT EXISTS (SELECT 1 FROM chat_names WHERE chat_names.id=ci_pipeline_chat_data.chat_name_id)

Validate the foreign key:

ALTER TABLE ci_pipeline_chat_data VALIDATE CONSTRAINT fk_rails_f300456b63