[BE] Store both system-defined and custom work item type IDs in work_item_type_id column

Overview

Store both system-defined work item types (IDs 1-9) and custom work item types (IDs ≥ 1000) in the existing work_item_type_id column across relevant tables, avoiding the need for separate columns and maintaining query performance.

Decision Summary

After extensive discussion with the Cells and Database teams, we've decided on Approach 4 with a sequence offset:

Implementation:

• Use the existing work_item_type_id column for both system-defined and custom types
• System-defined types: IDs 1-9 (immutable, defined in code)
• Custom types: IDs starting from 1000 (stored in work_item_custom_types table)
• Offset the work_item_custom_types sequence to start at 1000 using ALTER SEQUENCE
• Add a check constraint ensuring custom type IDs are ≥ 1000

Key Benefits:

• Single column for filtering - maintains existing index efficiency
• No new columns/indexes on the large issues table
• No backfill required
• Cells-comliant (sequence offset compatible with Topology Service)
• Clear separation between system-defined (1-9) and custom (1000+) types

Trade-offs:

• No FK constraint - we'll handle referential integrity via application logic and DB triggers
• Magic boundary at 1000 - requires thorough documentation

Implementation Details

1. Sequence Configuration: Alter work_item_custom_types_id_seq to start at 1000
2. Check Constraint: Add constraint ensuring work_item_custom_types.id >= 1000
3. Referential Integrity: Implement custom triggers to prevent deletion of types still referenced in issues
4. Type Resolution: Use WorkItems::TypesFramework::Provider to abstract type resolution in application layer

Affected Tables

All tables with work_item_type_id columns will follow this pattern:

• issues (primary table)
• work_item_custom_status_mappings
• work_item_type_custom_fields
• work_item_type_custom_lifecycles
• work_item_type_user_preferences
• work_item_widget_definitions