Remove FK from work_item_type_user_preferences

Issue: Remove foreign key constraints from work_item_t... (#588587 - closed)

What does this MR do and why?

As part of implementing configurable work item types with Option 4 (single column approach), we need to remove the foreign key constraint from the work_item_type_user_preferences table.

This allows storing both system-defined type IDs (1-9) and custom type IDs (1000+) in the same work_item_type_id column.

Database changes

This MR removes the foreign key constraint from the work_item_type_user_preferences table using a post-deployment migration.

Application-level validation

To maintain data integrity after removing the foreign key constraint, this MR adds application-level validation that:

• Validates work_item_type_id references are valid when provided
• Uses WorkItems::TypesFramework::Provider.find_by_id() to check against both system-defined and legacy database types
• Works regardless of feature flag state, ensuring invalid IDs cannot be stored even during the transition period

Why this table is safe to drop the FK from

1. Small table - No performance concerns with validation
2. Nullable column - work_item_type_id is optional, allowing namespace-level preferences without a specific type
3. Existing defensive code - The model already has optional: true on the association
4. Provider-based lookup - The provider will handles both system-defined and custom type lookup - Currently it falls back to using DB lookup using the work_item_type_id table

References

• #580065 (closed)
• #588244 (closed)

Screenshots or screen recordings

Before	After

How to set up and validate locally

MR acceptance checklist

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