# Migration Rollout and Rollback Runbook

## Scope
This runbook covers rollout and rollback for correlation traceability migration:

- `apps/wallet_database/priv/repo/migrations/20260402000026_add_correlation_id_to_all_business_tables.exs`

It includes both clean-environment rollout and recovery steps for partially-applied local/staging databases.

## Preconditions
- Branch checked out with migration file present.
- DB credentials available in runtime config.
- Backup snapshot taken before migration (required for shared staging/prod).

## A. Clean DB Rollout (Preferred Path)

1. Verify migration status:

```bash
mix ecto.migrations --migrations-path apps/wallet_database/priv/repo/migrations
```

Expected before rollout:
- `down 20260402000026 add_correlation_id_to_all_business_tables`

2. Apply migration:

```bash
mix ecto.migrate --migrations-path apps/wallet_database/priv/repo/migrations
```

3. Verify migration is up:

```bash
mix ecto.migrations --migrations-path apps/wallet_database/priv/repo/migrations | rg 20260402000026
```

Expected after rollout:
- `up 20260402000026 add_correlation_id_to_all_business_tables`

## B. Partial-Apply Recovery (If Prior Attempt Failed Midway)

Symptoms:
- migration fails with `Duplicate column name 'correlation_id'`
- migration fails with `Duplicate key name '<table>_correlation_id_index'`

1. Inspect current state table-by-table:

```sql
SELECT TABLE_NAME, COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND COLUMN_NAME = 'correlation_id'
ORDER BY TABLE_NAME;

SELECT TABLE_NAME, INDEX_NAME
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND INDEX_NAME LIKE '%_correlation_id_index'
ORDER BY TABLE_NAME, INDEX_NAME;
```

2. Normalize to a clean baseline before rerun:
- Option A (safe for non-prod): manually drop existing partial indexes/columns on affected tables, then rerun migration.
- Option B (surgical): mark migration as applied only if all target tables already contain both column and index.

3. Rerun migration command:

```bash
mix ecto.migrate --migrations-path apps/wallet_database/priv/repo/migrations
```

## C. Rollback

Rollback one step:

```bash
mix ecto.rollback --migrations-path apps/wallet_database/priv/repo/migrations --step 1
```

Verify down status:

```bash
mix ecto.migrations --migrations-path apps/wallet_database/priv/repo/migrations | rg 20260402000026
```

Expected:
- `down 20260402000026 add_correlation_id_to_all_business_tables`

## D. Post-Migration Validation

1. Schema validation:

```bash
find apps/wallet_database/lib/wallet_database/schemas -name "*.ex" -exec grep -L "correlation_id" {} \;
```

Expected:
- no output

2. Migration registry validation:

```bash
mix run -e "import Ecto.Query; versions = WalletDatabase.Repo.all(from m in \"schema_migrations\", select: m.version); IO.inspect(Enum.member?(versions, 20260402000026))"
```

Expected:
- `true`

3. Spot-check query by correlation id (example table):

```sql
EXPLAIN SELECT * FROM loans WHERE correlation_id = 'corr_test_123';
```

Expected:
- plan uses `loans_correlation_id_index`

## F. Encrypted Field Backfill (P1-001)

After deploying schema updates that use deterministic encrypted types,
run a staged backfill to rewrite legacy plaintext values.

Dry run:

```bash
mix wallet_database.backfill_encrypted_fields --dry-run
```

Execute:

```bash
mix wallet_database.backfill_encrypted_fields
```

Expected output:
- `Scanned: <n>`
- `Updated: <n>`
- `Failed: 0`

Verification query pattern (example):

```sql
SELECT COUNT(*) AS plaintext_remaining
FROM users
WHERE email IS NOT NULL AND email NOT LIKE 'enc1:%';
```

Expected:
- `plaintext_remaining = 0` after completion.

## E. Safety Notes
- Do not run rollback on shared production without explicit approval.
- For production, prefer forward-fix over rollback if data writes have started after migration.
- Always capture incident notes when partial-apply recovery is required.