Change tracking plugin records ID=undefined for bulk operations with IN clause

[cryzzer]

Problem Summary:
The CAP change tracking plugin fails to properly identify individual entities when performing bulk UPDATE or INSERT operations using the IN clause. Instead of recording the actual entity IDs, it logs ID=undefined in the change tracking table, making it impossible to trace which specific entities were modified.

Issue Details:
Expected Behavior: When updating multiple entities with a bulk operation, each affected entity should be individually tracked with its actual ID in the change log.

Actual Behavior: The change tracking plugin cannot determine individual entity IDs from bulk operations and records ID=undefined for all affected entities.

Code Example:

export class ProductRepository {
  public async updateProductsStatus(
    productIDs: string[],
    status: string,
    approverUserId?: string
  ): Promise<void> {
    const { Product } = await ServiceHelper.getServiceEntities();

    // This bulk operation causes ID=undefined in change tracking
    await UPDATE(Product)
      .set({
        status_code: status,
        lastModifiedBy: approverUserId,
      })
      .where({
        ID: { IN: productIDs }, // Bulk update with IN clause
      });
  }

  // Single entity update - works correctly
  public async updateProductStatus(
    productID: string,
    status: string,
    approverUserId?: string
  ): Promise<void> {
    const { Product } = await ServiceHelper.getServiceEntities();

    await UPDATE(Product)
      .set({
        status_code: status,
        lastModifiedBy: approverUserId,
      })
      .where({ ID: productID }); // Single entity - works fine
  }
}

Change Log Output:

| ID                                    | KEYS          | ATTRIBUTE        | VALUECHANGEDFROM | VALUECHANGEDTO | ENTITYID             | ENTITY              | PARENTENTITYID | PARENTKEY | SERVICEENTITYPATH | MODIFICATION | VALUEDATATYPE | CHANGELOGID |
|---------------------------------------|---------------|------------------|------------------|----------------|----------------------|---------------------|----------------|-----------|-------------------|--------------|---------------|------------|
| 0f0a0b0c-1d2e-3f40-5a6b-7c8d9e0f1a2b | ID=undefined  | govStatus_ID     |                  | ACTIVE         | com.example.Product  | MyService.Product   |                |           | MyService.Product | create       | cds.String    | tx_10001   |
| 1a2b3c4d-5e6f-7081-92a3-b4c5d6e7f809 | ID=undefined  | lastApprover_ID  |                  | USER001        | com.example.Product  | MyService.Product   |                |           | MyService.Product | create       | cds.String    | tx_10002   |
| 2b3c4d5e-6f70-8192-a3b4-c5d6e7f8091a | ID=undefined  | govStatus_ID     |                  | ACTIVE         | com.example.Product  | MyService.Product   |                |           | MyService.Product | create       | cds.String    | tx_10003   |

Comparison with Working Single-Entity Operations:

| ID                                    | KEYS                                         | ATTRIBUTE    | VALUECHANGEDFROM | VALUECHANGEDTO | ENTITYID             | ENTITY              | PARENTENTITYID | PARENTKEY                           | SERVICEENTITYPATH | MODIFICATION | VALUEDATATYPE | CHANGELOGID |
|---------------------------------------|----------------------------------------------|--------------|------------------|----------------|----------------------|---------------------|----------------|-------------------------------------|-------------------|--------------|---------------|------------|
| d4e5f6g7-h8i9-0123-defg-4567890abcde | ID=550e8400-e29b-41d4-a716-446655440000      | status_code  | DRAFT            | ACTIVE         | com.example.Product  | MyService.Product   |                | 941fee5a-c752-40eb-a46a-b265a93fe9f7 | MyService.Product | update       | cds.String    | tx_12348   |

Impact:

• Audit Trail Broken: Unable to determine which specific entities were modified
• Compliance Issues: Change tracking requirements cannot be satisfied for bulk operations
• Debugging Difficulties: Troubleshooting data changes becomes nearly impossible
• Inconsistent Behavior: Single-entity operations work correctly while bulk operations fail

Environment:
CAP Version: "@sap/cds": "^8.3.1"
Change Tracking Plugin Version: "@cap-js/change-tracking": "^1.0.8"
Database: Tested with SQLite and SAP HANA

Reproduction Steps

1. Set up CAP application with change tracking plugin enabled
2. Create an entity with service exposure for change tracking
3. Perform bulk UPDATE using WHERE ID IN [array]
4. Check change tracking table entries
5. Observe ID=undefined in the logged changes

Minimal Example:
Entity Definition:

entity Products {
  key ID: UUID;
  name: String;
  status_code: String;
  lastModifiedBy: String;
}

Service Definition:

service MyService {
  entity Product as projection on db.Products;
}

Test Data:

const productIds = [
  '550e8400-e29b-41d4-a716-446655440000',
  '6ba7b810-9dad-11d1-80b4-00c04fd430c8',
  '6ba7b811-9dad-11d1-80b4-00c04fd430c9'
];

// This will cause ID=undefined in change tracking
await UPDATE(Product)
  .set({ status_code: 'ACTIVE' })
  .where({ ID: { IN: productIds } });

[cryzzer]

I believe the same issue applies to these approaches as well:

await INSERT.into(Products).entries(newProducts);

where newProducts is an array of Products

and this approach as well:

private entityName: String;

public async setStatusCode(id: string | string[], status: StatusCodes) {
    const entity = await ServiceEntityHelper.getEntity(this.entityName);
    await UPDATE(entity)
      .set({
        statusCode: status,
      })
      .where({ ID: id });
  }

However, when id is an array, as is the case in the function above, it does not function as intended.

[Sv7enNowitzki]

Hi @cryzzer ,

Currently, all expressions are unsupported. The plugin derived other logic based on req.diff, and the relevant content obtained in diff is in the form of original expression logic without conversion. Therefore, when the plugin try to get the ID, it is unavailable.

I personally think there are two directions:

1. Request CAP to enable the diff API to perform this conversion, so the plugin receives processed data.
2. Have the plugin itself handle this processing.

Since the topic owner is on vacation, we might need to wait for him to return for further feedback.

Best Regards,
Wenjun

[cryzzer]

Hi @Sv7enNowitzki,

Thanks for the response. I will be waiting for the topic owner to give his opinion as well and decide what solution will be implemented because this is a major blocker for our application.

[nkaputnik]

Hello @cryzzer
from a CAP perspective, it is highly improbable that the DIFF method will get an investment to support such a scenario.
So, the only solution would therefore be route 2. which is a high investment topic.
We cannot estimate any delivery for such a feature, yet alone a priorization.

I have assigned this issue other to Marten, who may be able to invest some additional investigation towards end of october.

Best,
Nick

[cryzzer]

Hello @nkaputnik & @schiwekM.

I am writing to inquire about the status of the issue at hand. Do you have any updates or additional information regarding the investigation? Additionally, could you please provide an estimated timeline for any potential developments?

[schiwekM]

Hi @cryzzer,

we are actively working on a solution via #216. By moving the tracking logic to the database every modification can then be properly tracked avoiding these kinds of bugs in the future, as there are a few similar issues where the current service level tracking does not cover specific kinds of modifications.

The current plan is to have a first beta version released before Christmas.

Hope that sounds good & BR,
Marten