Add models for system defined status

Feature context: Customizable statuses for work items

This MR is part of the customizable statuses for work items initiative for Premium and Ultimate tier namespaces. We'll provide hard-coded system-defined statuses (e.g., "In Progress", "Done") organized into categories that determine state transitions (open/closed). Statuses are managed through lifecycles, which define sets of available statuses for specific work item types. First iteration focuses on system-defined statuses for tasks, with custom status support planned for future releases.

Expand for detailed feature context with links to concept, POC and designs

Core Concepts

• Status: Defines a work item's current stage (e.g., "In Progress", "Done")
• Status Category: Groups statuses and determines their effect on work item state (and icon)
• Lifecycle: Collection of statuses available to specific work item types
• System maintains default statuses for key transitions (open, close, duplicate)

Implementation Scope

• Premium and Ultimate only feature
• We provide system defined statuses which are implemented in code instead of database tables
• When customers decide to customize statuses, we'll copy system defined statuses to root namespace custom statuses
• Customers can change custom statuses
• Status changes trigger automatic state transitions based on category (open, closed)
• Root namespaces use either system-defined or custom statuses exclusively

First Iteration 🎯

• Implementing system defined statuses
• Status widget visible only on tasks
• Status visible in list and detail views
• Focus on core status management functionality
• Initial custom status groundwork

Second iteration

• Full custom status and lifecycle support
• Root namespace management interface
• Status support across all work item types
• Status on boards

Related Items

• Longer post about core concepts and approach
• Proof of concept with system defined and custom status
• Design: List/detail/board
• Design: Administration

What does this MR do and why?

Solves Add current status and system defined status mo... (#517334 - closed)

Contributes to BE: Create tables to support Status widget (#498393 - closed)

This MR introduces several new models and database changes to support system-defined statuses for work items. Here's a summary of the main changes:

1. Database Changes:
   • Created a new table work_item_current_statuses to persist the link between work items and their statuses (system-defined or custom).
   • We don't want to make issues table wider, instead use a separate join table.
   • Has namespace_id sharding key for cells compatibility
   • Added necessary indexes and foreign key constraints.
   • Also added custom_status_id already here because we can better order columns now
2. New Models:
   • WorkItems::Statuses::SystemDefined::Lifecycle: (From POC) Represents a lifecycle that defines sets of available statuses for specific work item types. It's a fixed items model which uses the modules/concerns added in this MR
   • WorkItems::Statuses::SystemDefined::Status: (From POC) Represents a system-defined status with attributes like name, color, category, and lifecycle association. It's a fixed items model which uses the modules/concerns added in this MR
   • WorkItems::Statuses::CurrentStatus: (From POC) Links work items to their current status (system-defined or custom in the future). Uses a fixed items model association which was implemented in this MR.

References

Please include cross links to any resources that are relevant to this MR. This will give reviewers and future readers helpful context to give an efficient review of the changes introduced.

• BE: Create tables to support Status widget (#498393 - closed)
• Add current status and system defined status mo... (#517334 - closed)
• Longer post about core concepts and approach
• Proof of concept with system defined and custom status

MR acceptance checklist

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

Screenshots or screen recordings

⛔ backend only change

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

With these changes you should be able to manage a system defined status on a work item.

1. Run the migration bundle exec rails db:migrate
2. Open the rails console and hack a bit on work items

# Fetch any work item. Ensure type is one of issue or task for now
wi = WorkItem.last

raise 'not of type issue or task' unless [:issue, :task].include?(wi.work_item_type.base_type.to_sym)

# Get the base work item tyoe as we'll use this later on to figure out
# which statuses will be available for the WIT
base_type = wi.work_item_type.base_type.to_sym
# Get the lifecycle of the base type
lifecycle = WorkItems::Statuses::SystemDefined::Lifecycle.of_work_item_base_type(base_type)
# You can either use lifecylcle.statuses.first (or any other index) or
# find by name case insensitively (we'll use this for the quick action later)
status = lifecycle.find_available_status_by_name("in progress")

current_status = WorkItems::Statuses::CurrentStatus.find_or_initialize_by(work_item: wi)
current_status.status = status
# Join object will set namespace and changed_to_status_at automatically
current_status.save!

wi.reset
# Should be our system defined status object
wi.current_status.status