Foreign key constraint violation when deleting projects with AI catalog item consumers

Problem

When deleting a project that has AI catalog item consumers configured, a foreign key constraint violation occurs:

ERROR:  update or delete on table "ai_catalog_items" violates foreign key constraint "fk_bba1649fa5" on table "ai_catalog_item_consumers" (PG::ForeignKeyViolation)
DETAIL:  Key (id)=(78) is still referenced from table "ai_catalog_item_consumers".

Root Cause

The issue occurs during project deletion when:

  1. Project deletion cascades to delete ai_catalog_item_consumers records via the project_id foreign key (which has ON DELETE CASCADE)
  2. The ai_catalog_item_consumers table has a foreign key constraint on ai_catalog_item_id with ON DELETE RESTRICT
  3. This constraint prevents deletion of the ai_catalog_item if any consumers still reference it
  4. The deletion order causes a conflict where the constraint is violated before the cascade completes

Related Issues

  • MR !196764 (merged): Added ai_catalog_item_consumers table with ON DELETE RESTRICT constraint
  • Issue #554105 (closed): Soft-deletion of catalog items if a project/group is using it

Solution

Add dependent: :delete_all to the Project model's relationship with ai_catalog_item_consumers to ensure consumers are explicitly deleted before any cascade operations:

# In ee/app/models/ee/project.rb

has_many :configured_ai_catalog_items, 
  class_name: '::Ai::Catalog::ItemConsumer', 
  inverse_of: :project,
  dependent: :delete_all

This approach:

  • Ensures explicit deletion of consumers before project deletion completes
  • Maintains the application-level soft-delete logic for catalog items
  • Prevents the foreign key constraint violation
  • Aligns with the existing soft-delete strategy implemented in issue #554105 (closed)

Alternative Approach

Change the foreign key constraint from ON DELETE RESTRICT to ON DELETE CASCADE in the migration. However, this is less preferred as it bypasses the application-level deletion logic that handles soft-deletion of catalog items.

Edited by 🤖 GitLab Bot 🤖