# Migration Guide: Change Tracking Plugin v1 to v2

This guide explains how to migrate existing data when upgrading the `@cap-js/change-tracking` plugin from v1 to v2.

> [!NOTE]
> This guide applies exclusively to migrations from `@cap-js/change-tracking` v1 to v2. A separate migration guide for upgrading from the Java change-tracking plugin to v2 will be provided later.

## Overview

In version 1, change-tracking data was split across two tables and tracked at the application layer:

- **`sap_changelog_ChangeLog`**: one row per change event (who changed what and when)
- **`sap_changelog_Changes`**: one row per changed attribute (the actual field-level diffs)

In v2, the `ChangeLog` table is removed. The schema of `Changes` has been redesigned to store all data directly within a single table.

> [!WARNING]
> During the migration from v1 to v2, information about which service a change originated from is lost. Consider the implications for your application before proceeding with the migration.

## Step-by-Step Guide

### Step 1: Upgrade to the latest v1 plugin version

Ensure your database is deployed with version `1.2.2` of `@cap-js/change-tracking`. You can verify your installed version by running:

```bash
npm ls @cap-js/change-tracking
```

This step is required because version `1.2.2` introduces two new columns to the `Changes` table that are necessary for the data migration.

### Step 2: Copy data from the ChangeLog table to the Changes table

Run the following SQL command to copy the `createdAt` and `createdBy` columns from `sap.changelog.ChangeLog` to `sap.changelog.Changes` for all existing records:

```sql
MERGE INTO SAP_CHANGELOG_CHANGES AS c
  USING SAP_CHANGELOG_CHANGELOG AS cl
	ON c.changeLog_ID = cl.ID
	WHEN MATCHED THEN UPDATE SET
	    c.createdAt = cl.createdAt,
	    c.createdBy = cl.createdBy;
```

> [!NOTE]
> This manual step is only required for single-tenant applications. For multi-tenant applications, this data migration is handled automatically.

### Step 3: Update the plugin dependency to version 2

Update your `@cap-js/change-tracking` dependency to the target v2 version:

```bash
npm i @cap-js/change-tracking@2
```

### Step 4: Generate the migration table

Run the following command to generate the `sap.changelog.Changes.hdbmigrationtable` artifact under `db/src/` and update `db/undeploy.json`:

```bash
cds add change-tracking-migration
```

This command automatically:

- Creates `db/src/sap.changelog.Changes.hdbmigrationtable` containing the v1-to-v2 migration SQL
- Adds the deprecated v1 `.hdbtable` entries for `Changes` and `ChangeLog` to `db/undeploy.json`

> [!NOTE]
> The default migration table configuration supports composite keys of up to 5 parts. For entities with more than 5 primary keys, the corresponding `entityKey` will be set to `NULL` during migration. If your data model exceeds this limit, adjust the generated migration table SQL before deploying.

> [!NOTE]
> Earlier releases of `@cap-js/change-tracking` v1 occasionally generated invalid change records where the `entityKey` contained `null` or `undefined` values. While the migration process safely ignores these corrupt entries, it does not automatically delete them. We recommend manually reviewing and purging these invalid records to keep your database clean.

### Step 5: Deploy the schema updates

Deploy the updated schema to your database using CDS tools:

```bash
cds deploy --to hana # just deploy database schema
cds up # redeploy entire application
```

### Step 6: Cleanup deployment artifacts

To prevent HDI from dropping your migrated tables, you must clean up the temporary migration configurations.

1. **Remove** the old `.hdbtable` entries for `Changes` and `ChangeLog` in the `db/undeploy.json`.
2. Add the migration table entry to the file:

```json
[
  ...,
  "src/sap.changelog.Changes.hdbmigrationtable"
]
```

3. Delete the `sap.changelog.Changes.hdbmigrationtable` file from your `db/src` directory.

> [!IMPORTANT]
> Failing to remove the v1 `.hdbtable` entries from `undeploy.json` will cause the database to undeploy the tables during your next deployment, resulting in permanent data loss.

### Step 7: Generate missing hierarchy information

Version 2 tracks changes on child entities (compositions) differently. In v1, changes to a child entity were assigned to the parent entity. In v2, the change record is assigned directly to the child (the entity where the change occurred). Additionally, a parent change record is generated to signal that a nested change took place, linking them via a new `parent` association to support hierarchical expansion.

Run the `SAP_CHANGELOG_RESTORE_BACKLINKS` database procedure to backfill parent change records for historical child entity changes:

```sql
CALL "SAP_CHANGELOG_RESTORE_BACKLINKS"();
```

This procedure is idempotent and it only creates backlink parent records where they are missing and only updates child records that do not yet reference a `parent_ID`.

The restore procedure is generated by default. You can disable its generation:

```json
{
  "cds": {
    "requires": {
      "change-tracking": {
        "procedureForRestoringBacklinks": false
      }
    }
  }
}
```

> [!NOTE]
> This procedure also remains available for native v2 environments to repair or regenerate broken backlink hierarchies if needed.