Draft: POC for mappings approach for custom statuses

Status Mapping POC: Efficient Data Migration for Work Item Statuses

This POC implements a status mapping system that allows seamless data migration when statuses are removed from lifecycles or when work item types change lifecycles. The solution provides immediate user feedback while maintaining data integrity without requiring background migrations.

Screen recording that showcases deletion using the update lifecycle mutation with system defined lifecycle and removing a status and adding a mapping manually using the console:

Screen_Recording_2025-08-01_at_10.24.36

Problem Statement

When performing status-related data migrations (lifecycle assignments, status deletions), we need:

1. Immediate user feedback without waiting for background jobs
2. Ability to filter by old status names and still find relevant work items
3. Prevention of broken references when statuses are removed
4. Efficient querying that doesn't impact performance

Solution Architecture

1. Mapping Table Structure

Created work_item_custom_status_mappings table:

CREATE TABLE work_item_custom_status_mappings (
  id bigint PRIMARY KEY,
  namespace_id bigint NOT NULL,
  old_status_id bigint NOT NULL,
  new_status_id bigint NOT NULL,
  work_item_type_id bigint NOT NULL,
  created_at timestamp NOT NULL
);

Key design decisions:

1. Scoped to namespace level for efficient caching
2. Work item type specific for granular control (delete from lifecycle and change lifecycle for type)
3. Only handles custom-to-custom mappings (system-defined mappings remain separate)

2. Status Resolution Enhancement

Enhanced CurrentStatus#status method to resolve mappings at runtime:

def status
  namespace = work_item.namespace.root_ancestor
  
  if custom_status.present?
    mapped_status = namespace.resolve_work_items_status_mapping(
      custom_status.id, work_item.work_item_type_id
    )
    return mapped_status if mapped_status.present?
    return custom_status
  end

  # Similar logic for system-defined statuses... 
  # because it's possible current_status only has system defined status id
  # even if the namespace uses custom statuses.
end

3. Efficient Caching Strategy

Implemented request-scoped caching at the namespace level:

def resolve_work_items_status_mapping(old_status_id, work_item_type_id)
  cache_key = "work_items:status_mappings:#{id}"
  
  mappings = ::Gitlab::SafeRequestStore.fetch(cache_key) do
    # Single query loads all mappings for namespace
    # Indexed by [old_status_id, work_item_type_id] for O(1) lookup
  end
  
  mappings[[old_status_id, work_item_type_id]]&.new_status
end

Performance benefits:

1. Single database query per namespace per request
2. O(1) hash lookups for subsequent status resolutions
3. Automatic cleanup after request completion

4. Smart Filtering

Enhanced StatusFilter to include mapped statuses in search results:

def find_statuses_for_filtering
  requested_status = find_requested_status
  statuses = [requested_status]
  
  # Add statuses that map TO the requested status
  if requested_status.is_a?(Custom::Status)
    statuses.concat(find_statuses_mapping_to(requested_status))
  end
  
  statuses.uniq
end

User experience improvement:

1. Searching for "In Progress" finds work items with both "In Progress" and "Development" statuses
2. Backward compatibility maintained for existing filters
3. No performance degradation for non-mapped statuses

5. Chain Prevention

Implemented automatic chain prevention when creating mappings:

def update_existing_mappings_to_prevent_chains(old_status, new_status, work_item_type)
  # Find mappings where old_status is currently the target
  existing_mappings = Mapping.where(new_status_id: old_status.id)
  
  # Update them to point directly to the final target
  existing_mappings.update_all(new_status_id: new_status.id)
end

Example:

Before: A → B, then adding B → C
After:  A → C, B → C (no chains)

6. GraphQL Integration

Added status mapping support to lifecycle update mutation:

argument :status_mappings, [Types::WorkItems::StatusMappingInputType],
  required: false,
  description: 'Mappings for statuses being removed from the lifecycle.'

Input structure:

statusMappings: [
  {
    oldStatusId: "gid://gitlab/WorkItems::Statuses::Custom::Status/5"
    newStatusId: "gid://gitlab/WorkItems::Statuses::Custom::Status/2"
  }
]

Database Optimizations

Composite unique index on (namespace_id, old_status_id, work_item_type_id, new_status_id) which covers all queries.

Queries

Resolving the status on a specific item adds one additional query for the whole collection. Mappings are stored in a request cache per namespace:

WorkItems::Statuses::Custom::Mapping Load (1.1ms)  SELECT "work_item_custom_status_mappings".* FROM "work_item_custom_status_mappings" WHERE "work_item_custom_status_mappings"."namespace_id" = 31 /*application:web,correlation_id:01K1GES8ZE57B7PRKS21NE3KNV,endpoint_id:GraphqlController#execute,db_config_database:gitlabhq_development,db_config_name:main,line:/ee/app/models/ee/namespace.rb:742:in `block in resolve_work_items_status_mapping'*/
  ↳ ee/app/models/ee/namespace.rb:742:in `block in resolve_work_items_status_mapping'

Resolving the statuses in filtering adds one additional query to look up the mappings. Mappings are stored in a request cache per namespace:

WorkItems::Statuses::Custom::Mapping Load (1.1ms)  SELECT "work_item_custom_status_mappings"."old_status_id", "work_item_custom_status_mappings"."new_status_id" FROM "work_item_custom_status_mappings" WHERE "work_item_custom_status_mappings"."namespace_id" = 31 /*application:web,correlation_id:01K1GES8ZH97GTG21VSN9YTV8R,endpoint_id:GraphqlController#execute,db_config_database:gitlabhq_development,db_config_name:main,line:/ee/app/finders/work_items/status_filter.rb:43:in `find_statuses_mapping_to'*/
  ↳ ee/app/finders/work_items/status_filter.rb:43:in `find_statuses_mapping_to'

Testing Scenarios Covered

1. Single work item access: Status resolves correctly with mapping
2. Bulk work item loading: Efficient caching prevents N+1 queries
3. Filtering by old status name: Returns work items with mapped statuses
4. Filtering by new status name: Includes work items that were mapped to it
5. Chain prevention: Multiple mapping updates work correctly
6. Cross-namespace isolation: Mappings don't leak between namespaces

Future Considerations

Potential Enhancements

1. System-defined mapping unification: Consolidate with existing converted_from_system_defined_status_identifier
2. Cross-namespace filtering: Support filtering across multiple namespaces (allow passing namespace_ids)
3. Multiple status filtering: OR logic for filtering by multiple statuses simultaneously (allow passing list of statuses)

Migration Strategy

1. Current implementation maintains full backward compatibility
2. Can be deployed incrementally without disrupting existing functionality
3. Provides foundation for future background migration to clean up mappings

Contributes to Create POC for mapping (#556439 - closed)

References

1. Create POC for mapping (#556439 - closed)

Screenshots or screen recordings

How to set up and validate locally

Use a root group that doesn't have custom statuses yet. You can verify that by fetching the lifecycle. Should be the system defined one.

query getNamespaceLifecycles($fullPath: ID!) {
  namespace(fullPath: $fullPath) {
    id
    lifecycles {
      nodes {
        id
        name
        __typename
      }
      __typename
    }
    __typename
  }
}

Variables:

{
  "fullPath": "jashkenas"
}

For testing purposes you can create an issue with the "to do" status and one issue with "in progress".

Then update the lifecycle and remove the "in progress" status by not including it in the statuses argument. Now also provide a mapping from the "in progress" status to "to do" status. The request should convert the namespace to custom statuses and not attach the "in progress" status to the lifecycle and add a mapping.

Mutation:

mutation LifecycleUpdate($input: LifecycleUpdateInput!) {
  lifecycleUpdate(input: $input) {
    lifecycle {
      id
      name
      statuses {
        id
        name
        iconName
        color
        __typename
      }
      defaultOpenStatus {
        id
        name
        __typename
      }
      defaultClosedStatus {
        id
        name
        __typename
      }
      defaultDuplicateStatus {
        id
        name
        __typename
      }
      workItemTypes {
        id
        name
        iconName
        __typename
      }
      __typename
    }
    errors
    __typename
  }
}

Variables:

{
  "input": {
    "namespacePath": "jashkenas",
    "id": "gid://gitlab/WorkItems::Statuses::SystemDefined::Lifecycle/1",
    "statuses": [
      {
        "id": "gid://gitlab/WorkItems::Statuses::SystemDefined::Status/1",
        "name": "To do",
        "color": "#737278"
      },
      {
        "id": "gid://gitlab/WorkItems::Statuses::SystemDefined::Status/3",
        "name": "Done",
        "color": "#108548"
      },
      {
        "id": "gid://gitlab/WorkItems::Statuses::SystemDefined::Status/4",
        "name": "Won't do",
        "color": "#DD2B0E"
      },
      {
        "id": "gid://gitlab/WorkItems::Statuses::SystemDefined::Status/5",
        "name": "Duplicate",
        "color": "#DD2B0E"
      }
    ],
    "defaultOpenStatusIndex": 0,
    "defaultClosedStatusIndex": 1,
    "defaultDuplicateStatusIndex": 3,
    "statusMappings": [
      {
        "oldStatusId": "gid://gitlab/WorkItems::Statuses::SystemDefined::Status/2",
        "newStatusId": "gid://gitlab/WorkItems::Statuses::SystemDefined::Status/1"
      }
    ]
  }
}

Reload the list page afterwards and see the former "in progress" item now has the "to do" status. If you filter for "to do" it also resolves the mapped status nicely.

You can also use the same approach with custom statuses or use the console:

I created two statuses Doing and Doing to be deleted and added an issue with each status:

doing, doing_deleted = WorkItems::Statuses::Custom::Status.last(2)
doing_deleted.lifecycle_statuses.destroy_all # Now the status is not assigned any more to the lifecycle

# create mapping for work item types in lifecycle
doing.lifecycles.first.work_item_types.each do |wit|
  WorkItems::Statuses::Custom::Mapping.create!(
    namespace_id: doing.namespace_id, old_status_id: doing_deleted.id, new_status_id: doing.id, work_item_type: wit
  )
end 

Then reload the list page and you should see that the doing deleted issue now has the doing status.

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.