Supporting item sort order

Purpose

Investigate how to support adding a sort order field for item records and integrating with RTAC responses.

Requirements

1. Add a new order field

   1. Add the order field to the item schema to support sorting.

   2. The field should be:

      • Numeric.

      • Optional (nullable).

   3. Ensure the field is included in the API and can be set, updated, or queried when necessary.

2. Behavior for New Items

   1. Add logic to assign an order value to new items upon creation automatically:

   2. Increment the value from the maximum order of items within the same holdings record.

   3. Ensure new items are correctly appended to the bottom of the sort by the order field.

3. API Support for Updating Sort Order

   1. Extend the item API to allow libraries to:

      • Set or update the order field for individual items.

      • Perform bulk updates to configure the order field for multiple items at once.

      • No validation needed for deduplication

4. Edge-RTAC Integration

   1. Extend RTAC logic and schema to include the order field.

   2. Items in RTAC responses should be sorted by the order field.

5. Migration of existing records (one of)

   1. Migration required

      1. Existing records should be updated with values in the new field

      2. Values should be defined by items sorted by barcode, grouped by relation to the holdings record.

   2. Migration-Free Integration

      1. Avoid requiring an upfront migration of data for the order field.

Additional Considerations

1. Performance: Ensure that order field handling does not impact system performance, especially for large holdings records or high-traffic RTAC queries.

2. Backward Compatibility: Maintain backward-compatible behavior for systems and APIs that do not depend on the order field, including sorting by barcode.

3. Documentation and Usability: Properly document the:

   1. Purpose and usage of the order field.

   2. API behavior for setting and updating the field.

4. Future UI Enhancements: Ensure the backend design supports potential future UI features, such as manual reordering or drag-and-drop interfaces for item reordering by library staff.

Links

• Spike Jira: MODINVSTOR-1387: SPIKE: Investigate supporting item sort order via APIClosedPreview

• Feature Jiras:

Proposed Solution

Key Features of the Solution

1. Addition of the order Field to existing APIs

   • What: Introduce a new, optional order field in the item record schema.

   • Why: This field will store the numeric sort order for items, which can be manually configured by libraries.

   • Details:

     • The field will be nullable and optional (backward compatibility).

     • Update all interfaces that use the item schema in mod-inventory-storage:

       • item-storage

       • item-storage-dereferenced

       • inventory-hierarchy

       • inventory-view

       • inventory-view-instance-set

       • item-sync

       • item-sync-unsafe

     • Update all interfaces that use the item schema in mod-inventory:

       • inventory

       • inventory-move

       • inventory-update-ownership

   • Jira: TBD

   • Estimate: 2 SP

2. Create a new API to support the update of items' order.

   • What: Introduce a new API in mod-inventory-storage that will support partial update of items in batches.

   • Why: This API will allow updating fields for several items and will be useful for UI integration. Could be used to update only the order field.

   • Details:

     • Implement PATCH /item-storage/items endpoint

       • Request body schema:

         { "items": [ { "id": "uuid1", "_version": 1 "order": 1, "otherFields": null }, { "id": "uuid2", "_version": 3 "order": 10, "otherFields": null } ] }

       • Request body field details:

         • id - required UUID-string field for item identification

         • _version - required integer field for optimistic locking support

         • order - optional field

         • The request could also contain other item schema fields.

         • The logic of item update should be:

           • If the field exists (nullable or with a value), then update the field

           • If the field doesn’t exist at all, do not update the field

       • Request handling logic:

         • should fail the full request and rollback values in case of optimistic locking

         • Send the domain events of the successful update

   • Jira: TBD

   • Estimate: 3 SP

3. Existing item handling for the order field

   1. Solution 1

      • What: Implement a migration script that will set the order value for each existing item

      • Cons: Such migration could be an expensive and time-consuming operation

      • Pros: After migration, all items will have an assigned order that will make it easy to sort by it or assign new values

      • Details:

        • The script should sort items by barcode and assign order value by just incrementing

          WITH sorted_items AS ( SELECT id, ROW_NUMBER() OVER (ORDER BY jsonb->>'barcode' ASC) AS order_value FROM tenant_mod_inventory_storage.item ) UPDATE tenant_mod_inventory_storage.item SET jsonb = jsonb_set(jsonb, '{order}', to_jsonb(sorted_items.order_value)) FROM sorted_items WHERE item.id = sorted_items.id;

   2. Solution 2

      • What: Setting order value at runtime

      • Cons:

        • Degradation of item get requests

        • In case of a large number of items requested (due to CQL, it could be the whole dataset), there is a risk of failures

      • Pros: No migration needed - it will not affect time needed for the module upgrade.

      • Details:

        • When an item is requested, if there is no order set, then check its position in the list of items that relate to one holding record, sorted by barcode

        • Update items of the holding in the database with an order value

        • Respond to get request

   3. Solution 3

      • What: Migrate records after the module upgrade with a background job (in runtime or in off-hours)

      • Cons: The order value will be 0 for requested items until the migration is done

      • Pros: No impact on module upgrade time and item get requests

      • Details:

        • Make on the API level the order equal to 0 by default

        • Implement an async migration that will set the order for items based on barcode sorting.

      • Jira: TBD

      • Estimate: 5 SP

4. Handling new item creation

   1. Solution 1

      • What: Automatically assign the order value to new items created after the implementation of this feature.

      • Cons: Degradation of item creation requests

      • Pros: Ensure new items are correctly appended without requiring additional manual steps.

      • Details:

        • Use value from request if exist, if not then:

        • Fetch the current maximum order within a holdings record.

        • Increment the value by 1 for the newly created item.

        • If no order values exist, derive the order from the largest barcode.

      • Jira: TBD

      • Estimate: 5 SP

   2. Solution 2

      • What: Make an order field required in the item schema. Move the value assignment responsibility to the client.

      • Cons: The client may need additional steps to understand which value he wants to assign in case it should appear at the bottom of the list.

      • Pros: No degradation of item creation requests: the backend uses the value passed in the request.

      • Details:

        • Update the item schema to make the order field required

   3. Solution 3

      • What: Make an order field to have a default value of MAX_INTEGER in the item schema.

      • Cons: The newly created items will appear at the end of the item list, but there is no guarantee that in case of sequential creation of items for the same holding, the order of the created items will not be the same as the order of creation, because the order will be same for all of them.

      • Pros: No degradation of item creation requests: the backend uses the default value if nothing is passed in the request.

      • Details:

        • Update the item schema to make the order field required

5. RTAC integration

   • What: Include the order field in RTAC responses.

   • Details:

     • Update the mod-rtac response/request schemas to include the order field

       • rtac-batch API

     • Update the edge-rtac response schema to include the order field.

   • Jira: TBD

   • Estimate: 4 SP