Migration Tool:

---
description: Database migration guildeline
globs: **/*.sql, **/entities/**/*.java
---

## Migration Tool:

The project uses Flyway for database migrations, configured in Spring Boot.

## Migration Files Location:

* Migrations are stored in backend/src/main/resources/db/migration/
* Files follow the naming convention: V{version}__{description}.sql
    * Example: V200000176__rename_onboarded_at_in_memory_tracker_to_assimilated_at.sql

## Version Numbering:

* Versions use a numerical format

The project uses two main version patterns:
V10000xxx for baseline migrations
V200000xxx for subsequent migrations

New migration need to be using a greater number than the current latest version number.

### Migration Process:

* Migrations run automatically when the application starts (non-test environments)
* For unit test, DB migration is included in `pnpm backend:test`

## Migration File Structure:

SQL files contain the actual migration commands
Each file should contain a single, atomic change
Changes can include:
Creating new tables
Altering existing tables
Renaming columns
Adding/removing constraints
Dropping tables
Baseline:

There's a baseline migration file (V10000063__baseline.sql) that contains the initial schema

As noted in V200000101__db_migration_placeholder.sql, previous migrations were collapsed into this baseline file for brevity

## Best Practices:

* Each migration should be reversible when possible
* Migrations are version controlled and should never be modified once committed
* New changes should always be added as new migration files
* Clear, descriptive names should be used for migration files to indicate their purpose