Create POC for mapping

When performing data migrations for status changes (lifecycle assignments, status deletions, label migrations), we need a solution that provides immediate user feedback while safely migrating data in the background.

We also want to explore if this mapping solution can function as a permanent or more permanent solution until an in-product BBM framework is built.

Statuses and work item types (WITs) are attached to a lifecycle.

Use cases

We need mapping logic in two cases:

1. A status gets deleted from a lifecycle
2. A WIT is attached to a new lifecycle and the old lifecycle has statuses which aren't attached to the new lifecycle.

Delete status

When we delete a status from a lifecycle we need a mapping for items that have the lifecycle from the deleted status to a replacement.

New lifecycle

When we change the lifecycle of a work item type we might have situations where a status exists in the old lifecycle but isn't part of the new lifecycle. In those cases we need to provide a replacement.

This time the status replacement is scoped to the work item type and not the lifecycle.

Proposal

Create a new mapping table that always works on the work item type level.

This way we don't need the lifecycle abstraction and can focus on resolving status for work item types instead. Also the mapping only applies to custom statuses because we copy system-defined statuses to custom ones on the first change to statuses.

-- Example structure
CREATE TABLE work_item_custom_status_mappings (
  id bigint PRIMARY KEY,
  namespace_id bigint NOT NULL, -- sharding key
  old_status_id bigint NOT NULL,
  new_status_id bigint NOT NULL,
  work_item_type_id bigint NOT NULL,
  created_at timestamp NOT NULL
);

We could resolve this in the CurrentStatus#status method. Because it works on the namespace level we could memoize the mapping on this level for faster queries.

For filtering we can potentially directly resolve the effective status using case statements.

Chaining

What happens when the replacement of a deleted status gets deleted?

To circumvent the complexity of building replacement chains, we should check whether a given status is a replacement for any status after adding a mapping. We'd select all mappings where the new status equals the to be deleted status and set it to the replacement of the to be deleted record:

status 1 -- deleted
status 2 -- replacement
status 3

Mappings:
old 1, new 2

---

Now we want to delete status 2 and replace with status 3:

Add new mapping: old 2, new 3

Search for mappings where new = 2 and set new = 3 (replacement)

Mappings:
old 1, new 3
old 2, new 3