# Database Migrations

This project uses the **Expand and Contract** pattern for database migrations to support zero-downtime deployments.

## Overview

The expand and contract pattern splits database changes into two phases:

- **Pre-deploy (Expand)**: Additive, non-breaking changes that are backwards compatible with the current API version
- **Post-deploy (Contract)**: Breaking changes that require the new API version to be deployed first

This allows the database and API to be updated independently while maintaining compatibility during the deployment window.

## Migration Folders

- `pre-deploy/` - Migrations that run **before** the API is deployed
- `post-deploy/` - Migrations that run **after** the API is deployed

Note: Root folder migrations (not in pre-deploy or post-deploy) are legacy migrations created before the expand-and-contract pattern was introduced. These run during `migration:run:init` only.

## Developer Workflow

### 1. Make Changes to Database Entities

Modify the TypeORM entity files in `src/**/*.entity.ts` as needed.

### 2. Generate Migrations

Run the migration generator:

```bash
npm run migration:generate
```

This creates the **same autogenerated migration in both** `pre-deploy/` and `post-deploy/` folders with a timestamp prefix.

### 3. Analyze and Adjust Migrations

**This is the critical step.** You MUST analyze the generated migrations and determine:

- Which changes are safe to run before the API deployment (pre-deploy)
- Which changes require the new API to be running first (post-deploy)
- Whether manual adjustments are needed for zero-downtime compatibility

#### Example Scenarios

**Adding a new field (Pre-deploy only)**

When adding a new nullable column or a column with a default value:

```typescript
// pre-deploy/migration.ts
public async up(queryRunner: QueryRunner): Promise<void> {
  await queryRunner.query(`ALTER TABLE "workspace" ADD "description" varchar NULL`);
}
```

```typescript
// post-deploy/migration.ts
// DELETE the generated migration - no post-deploy changes needed
```

The new column can be added before the API deployment since the old API will simply ignore it.

---

**Dropping a field (Post-deploy only)**

When removing a column:

```typescript
// pre-deploy/migration.ts
// DELETE the generated migration - no pre-deploy changes needed
```

```typescript
// post-deploy/migration.ts
public async up(queryRunner: QueryRunner): Promise<void> {
  await queryRunner.query(`ALTER TABLE "workspace" DROP COLUMN "legacy_field"`);
}
```

The column must only be dropped after the new API is deployed, since the old API may still be reading from it.

---

**Renaming a field (Expand then Contract)**

Renaming requires both phases to maintain zero-downtime. Use a **database trigger** to keep columns synchronized automatically:

```typescript
// pre-deploy/migration.ts (Expand)
public async up(queryRunner: QueryRunner): Promise<void> {
  // Add new column
  await queryRunner.query(`ALTER TABLE "workspace" ADD "display_name" varchar NULL`);
  // Copy existing data
  await queryRunner.query(`UPDATE "workspace" SET "display_name" = "name"`);
  // Create trigger to keep columns in sync during transition
  await queryRunner.query(`
    CREATE OR REPLACE FUNCTION sync_workspace_name()
    RETURNS TRIGGER AS $$
    BEGIN
      IF NEW.display_name IS NOT NULL THEN
        NEW.name := NEW.display_name;
      ELSIF NEW.name IS NOT NULL THEN
        NEW.display_name := NEW.name;
      END IF;
      RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;
  `);
  await queryRunner.query(`
    CREATE TRIGGER workspace_name_sync
    BEFORE INSERT OR UPDATE ON "workspace"
    FOR EACH ROW EXECUTE FUNCTION sync_workspace_name();
  `);
}
```

```typescript
// post-deploy/migration.ts (Contract)
public async up(queryRunner: QueryRunner): Promise<void> {
  // Remove trigger and old column
  await queryRunner.query(`DROP TRIGGER workspace_name_sync ON "workspace"`);
  await queryRunner.query(`DROP FUNCTION sync_workspace_name()`);
  await queryRunner.query(`ALTER TABLE "workspace" DROP COLUMN "name"`);
}
```

**How the trigger works:**

The trigger intercepts every INSERT and UPDATE on the table and automatically copies the value between columns:

| API Version | Writes to | Trigger copies to | Result |
|-------------|-----------|-------------------|--------|
| Old API | `name` | `display_name` | Both columns have the value |
| New API | `display_name` | `name` | Both columns have the value |

**Deployment timeline:**

1. **Pre-deploy migration runs** → Trigger is active, both columns exist
2. **Rolling deployment begins** → Mix of old and new API instances, trigger keeps data in sync
3. **Rolling deployment completes** → All instances are new API
4. **Post-deploy migration runs** → Trigger and old column are removed

**New API code changes:**

The new API should read from and write to `display_name` only. The trigger handles backward compatibility with old API instances—no dual-write logic needed in application code.

## Migration Scripts

### `npm run migration:run:init`

Runs **all migrations** from both pre-deploy and post-deploy folders. Use this for:

- Initial database setup
- Development environments
- Fresh database instances

### `npm run migration:run:pre-deploy`

Runs only migrations in the `pre-deploy/` folder. Use this:

- **Before** deploying a new API version
- As part of your CI/CD pipeline, before the rolling update begins

### `npm run migration:run:post-deploy`

Runs only migrations in the `post-deploy/` folder. Use this:

- **After** the new API version is fully deployed
- As part of your CI/CD pipeline, after the rolling update completes

## Reverting Migrations

```bash
npm run migration:revert
```

This reverts the **last executed migration** from either folder (based on the combined migration history in the database).

**Important behaviors:**

- Reverts one migration at a time - run multiple times to revert multiple migrations
- Uses the combined data-source that sees all migrations
- The revert order follows the execution timestamp, not the folder structure
- Always test revert scripts in development before relying on them in production

**Recommendation:** After reverting, you may need to also revert the corresponding entity changes and regenerate migrations to keep everything in sync.
