For database migrations, we’re using Prisma’s built-in migration mechanism (npx prisma migrate). However, we’ve extended it to support not just migrating the database schema, but also the data itself too.

The new migration procedure will now always be executed:

• If the database is already up to date, all migrations are ignored.
• If there is no database, the migrations will initialize the first schema and apply all migrations to reflect the current schema.
• If there is an outdated database available, only the missing migrations are applied to reflect the current schema.

The migration works as follows:

1. Each database schema migration is applied in isolation in lexically order (in the same order as npx prisma migrate does). For this we move all migrations into a temp folder, move each migration stepwise back to its origin location, and execute npx prisma migrate. Already applied migrations (including from previous steps) will be omitted by Prisma and only the last one will be applied if necessary.
2. For each database schema migration, we check if 1.) the migration was applied (by checking the console output) and 2.) if the migration folder contains a data-migration.ts file. If yes, we execute this script before proceeding with the next database schema migration. This allows to write a type-safe migration script and also to integrate more complex business logic if required. E.g., if necessary we may parse serialized objects which are stored as JSON objects using the Type library of SelfLearn to implement the required migration logic. Further, we re-generate the Prisma Client for each data base migration to ensure correct data access.
3. Cleanup: We remove all temporary data and move all files and folder to their origin, if not already done during the previous steps.

The following steps are needed to implement a new migration (database schema & data):

1. npx prisma migrate reset --skip-seed -f (https://fig.io/manual/prisma/migrate/reset). This will reset (and wipe) the data base and apply all migrations. As a consequence, the database will be in the state of before the migration.
2. npx prisma migrate dev --create-only --name <name>. This creates a new database schema migration from the previous version to the current schema.
3. Review and modify the migration if required. For instance, if a column was renamed in the schema definition, Prisma will drop the column in the table and create a new column resulting in data loss. Fix this by replacing these commands by a single Alter Table statement. It's also possible that the schema is altered in a way that a consistent migration is not possible in one step (e.g., a foreign key is exchanged and dropped). In such situations, we need 2 separate migrations (first ones extend the schema and transfers it into a redundant state with multiple foreign references; second one drops deprecated foreign key). This principle is called Expand and Contract Pattern.
4. Develop a data migration, if required. For this you need:
   1. npx prisma generate to generate a Prisma Client for after the schema migration.
   2. Create a data-migration.ts file inside the migration folder of step 2.
   3. Implement the required data changes. The data changes must be automatically be executed when the script is executed, i.e., no export is needed, but the migration function must be called from toplevel of the file. Provide also a try catch block and end the process with an failure code via process.exit(1) to interrupt the whole migration script in case of errors.
      Since these data migration scripts may be implemented to reflect upcoming data changes, they are likely to cause type error checks in future development. It's OK to use // @ts-expect-error to suppress these warnings.
   4. Test the new data migration script by executing it via: npx ts-node --esm --skipProject <path/to/data-migration.ts
5. Test complete migration by clearing the local data base and run: npm run test:migrate