# Migration Agent Instructions

## Overview

Database migrations use `gormigrate`. Migrations auto-run on worker startup.

## File Naming

```
migrate/record_YYYYMMDDHHMM_description.go
```

Example: `record_202602031000_add_backup_protected.go`

## Migration Pattern

```go
package migrate

import (
    "github.com/pingcap/errors"
    "github.com/pingcap/log"
    "github.com/tidbcloud/dataflow-service/app/repo/entity"
    "go.uber.org/zap"
    "gorm.io/gorm"
)

var _ = AddDataflowDBMigrateRecord(
    "202602031000",  // YYYYMMDDHHMM timestamp
    func(db *gorm.DB) error {
        // Use AutoMigrate for simple schema changes
        if err := db.AutoMigrate(&entity.Backup{}); err != nil {
            errMsg := "auto migrate table 'backups' failed"
            log.Error(errMsg, zap.Error(err))
            return errors.Annotate(err, errMsg)
        }
        log.Info("migration 202602031000 completed: added 'protected' column")
        return nil
    },
)
```

## Common Operations

**Add column:**
```go
if err := db.AutoMigrate(&entity.MyTable{}); err != nil {
    return errors.Annotate(err, "add column")
}
```

**Raw SQL (for complex changes):**
```go
if err := db.Exec("ALTER TABLE my_table ADD COLUMN new_col VARCHAR(255)").Error; err != nil {
    return errors.Annotate(err, "add column")
}
```

**Data migration:**
```go
// Update existing rows - use batching for large tables
err := db.Model(&entity.MyTable{}).
    Where("new_field IS NULL").
    Update("new_field", "default_value").
    Error
```

**Create index:**
```go
if err := db.Migrator().CreateIndex(&entity.MyTable{}, "idx_name"); err != nil {
    return errors.Annotate(err, "create index")
}
```

## Key Rules

1. **No transactions** - `gormigrate` uses `UseTransaction: false`
2. **Idempotent where possible** - Safe to re-run
3. **Log success** - Always log completion message
4. **Wrap errors** - Use `errors.Annotate()`
5. **Test locally** - Run `make unit-test` before committing

## Entity Changes

When adding/modifying columns:

1. Update `app/repo/entity/entity.go` with new fields
2. Run `make generate-entity` to regenerate QuerySet
3. Create migration file with matching timestamp
4. Test migration on local database

## Manual Execution

```bash
./dist/dataflow migrate --config config.yaml
```