# Migration Plan and Backfill/Rollback Runbook
## Phase 3 — Persistence and Migration Foundation

**Document version:** 1.0
**Date:** 2026-03-30
**Status:** Ready for review

---

## 1. Overview

Phase 3 introduces 8 database migrations that add the multi-wallet schema layer without modifying
or dropping existing columns. All changes are **additive and nullable-first**, meaning production
traffic continues uninterrupted while backfill jobs run asynchronously.

### Migration execution order

| # | Migration file | Type | Reversible |
|---|---|---|---|
| 1 | `20260330150000_create_wallet_product_types` | CREATE table + seeds | ✅ Yes |
| 2 | `20260330160000_create_wallet_products` | CREATE table | ✅ Yes |
| 3 | `20260330170000_create_sub_wallets` | CREATE table | ✅ Yes |
| 4 | `20260330180000_create_wallet_currency_configs` | CREATE table | ✅ Yes |
| 5 | `20260330190000_add_wallet_product_id_to_accounts` | ALTER (add columns) | ✅ Yes |
| 6 | `20260330200000_add_sub_wallet_id_to_ledger` | ALTER (add columns) | ✅ Yes |
| 7 | `20260330210000_add_sub_wallet_refs_to_transfers` | ALTER (add columns) | ✅ Yes |
| 8 | `20260330220000_create_sub_wallet_transactions` | CREATE table | ✅ Yes |

---

## 2. Pre-migration checklist

```
[ ] Take a full DB snapshot (RDS snapshot or `mysqldump --single-transaction`)
[ ] Verify current migration state: `mix ecto.migrations --repo WalletDatabase.Repo`
[ ] Check active connections < 80% of connection pool limit
[ ] Confirm maintenance window posted to internal status page (10 min window)
[ ] SRE on-call paged and acknowledged
[ ] Rollback branch ready: `git checkout -b rollback/phase-3-migrations`
```

---

## 3. Migration execution procedure

### Step 1 — Run in staging first

```bash
MIX_ENV=staging mix ecto.migrate --repo WalletDatabase.Repo
```

Verify:
- All 8 migrations show `[up]` status
- `mix ecto.migrations --repo WalletDatabase.Repo` shows no pending
- Run smoke tests: `mix test apps/wallet_database/ --only smoke`

### Step 2 — Production run

```bash
# From wallet_app root
MIX_ENV=prod mix ecto.migrate --repo WalletDatabase.Repo
```

Expected output (all 8 in order):
```
20:15:00.001 [info] == Running WalletDatabase.Repo.Migrations.CreateWalletProductTypes
20:15:00.020 [info] == Migrated in 0.019s
20:15:00.022 [info] == Running WalletDatabase.Repo.Migrations.CreateWalletProducts
...
20:15:00.200 [info] == Migrated 8 migrations in 0.2s
```

### Step 3 — Post-migration verification

```bash
# Confirm 8 new/altered tables
mix ecto.migrations --repo WalletDatabase.Repo | grep " up "

# Row counts (all should be 0 except wallet_product_types which has 4 seed rows)
mysql -u $DB_USER -p $DB_NAME -e "
  SELECT 'wallet_product_types' tbl, COUNT(*) n FROM wallet_product_types
  UNION SELECT 'wallet_products', COUNT(*) FROM wallet_products
  UNION SELECT 'sub_wallets', COUNT(*) FROM sub_wallets
  UNION SELECT 'wallet_currency_configs', COUNT(*) FROM wallet_currency_configs
  UNION SELECT 'sub_wallet_transactions', COUNT(*) FROM sub_wallet_transactions;
"
# Expected: wallet_product_types=4, all others=0

# Confirm nullable columns added to existing tables
mysql -u $DB_USER -p $DB_NAME -e "
  SHOW COLUMNS FROM accounts LIKE 'wallet_product_id';
  SHOW COLUMNS FROM ledger_entries LIKE 'sub_wallet_id';
  SHOW COLUMNS FROM ledger_balances LIKE 'sub_wallet_id';
  SHOW COLUMNS FROM wallet_transfers LIKE 'from_sub_wallet_id';
"
```

---

## 4. Backfill plan

Backfill runs **after** migrations are applied and **before** Phase 4 domain logic goes live.
All backfill jobs are idempotent and safe to re-run.

### Backfill B01 — Accounts → WalletProduct

**Goal:** For every existing `Account`, create a corresponding `WalletProduct` record and link
`accounts.wallet_product_id` back to it.

**Strategy:**
1. Query all accounts in batches of 500
2. For each account, create a `WalletProduct` with:
   - `wallet_product_id`: `TypedId.generate("wp")`
   - `user_id`: `account.user_id`
   - `account_id`: `account.account_id`
   - `product_type_id`: `"wpt_personal"` (default for existing accounts)
   - `label`: `"My Wallet"` (default)
   - `primary_currency`: `account.currency`
   - `status`: mirrors account status (active→active, frozen→frozen, closed→closed)
3. Update `accounts.wallet_product_id = wallet_product.wallet_product_id`
4. Create a matching default `SubWallet`:
   - `sub_wallet_id`: `TypedId.generate("sw")`
   - `wallet_product_id`: wallet_product.wallet_product_id
   - `owner_customer_id`: account.user_id
   - `originating_wallet_product_id`: wallet_product.wallet_product_id
   - `sub_type`: `"default"`
   - `label`: `"Default"`
   - `currency`: account.currency
   - `is_default`: true
   - `status`: mirrors account status
5. Create a `CurrencyConfig`:
   - `classification`: `"primary"`
   - `currency_code`: account.currency
   - `currency_type`: `"fiat"`

**Script:** `apps/wallet_database/patch_user.py` (see file; runs against live DB with dry-run flag)

**Estimated duration:** ~2 min for 10k accounts (batched inserts)

**Idempotency guard:** Check `wallet_product_id IS NOT NULL` before inserting — skip if already backfilled.

### Backfill B02 — LedgerBalance → sub_wallet_id

**Goal:** Link existing `ledger_balances` rows to the default sub_wallet created in B01.

**Strategy:**
1. Join: `ledger_balances.account_id` → `accounts.account_id` → `accounts.wallet_product_id`
2. Lookup default sub_wallet for each wallet_product
3. `UPDATE ledger_balances SET sub_wallet_id = ? WHERE account_id = ? AND sub_wallet_id IS NULL`

**Estimated duration:** ~1 min for 10k balance rows (single UPDATE per account)

### Backfill B03 — LedgerEntry → sub_wallet_id (optional / P1)

Backfilling historical entries is a P1 concern. It is safe to leave `sub_wallet_id NULL` on
pre-migration entries since the ledger posting pipeline will populate it going forward.

**Deferral rationale:** LedgerEntry is an immutable audit table. Updating historical rows violates
the immutability principle. Instead, Phase 5 will expose two views:
- `sub_wallet_balance()` — counts only entries with non-null sub_wallet_id
- `legacy_account_balance()` — counts all entries for an account_id (backward compat)

### Backfill B04 — wallet_transfers → from/to sub_wallet_id (optional / P1)

Same rationale as B03. Historical transfers remain account-level; new transfers will carry
sub_wallet references. Backward compat wrapper resolves sub_wallet from account in queries.

---

## 5. Rollback procedure

All migrations have `down/0` implementations. Run in **reverse order**:

```bash
# Rollback one at a time (safe — inspect after each step)
MIX_ENV=prod mix ecto.rollback --repo WalletDatabase.Repo --step 1

# Or rollback all Phase 3 migrations at once
MIX_ENV=prod mix ecto.rollback --repo WalletDatabase.Repo --step 8
```

### Rollback safety table

| Migration | `down` action | Data loss risk |
|---|---|---|
| `create_wallet_product_types` | DROP TABLE | ✅ Seed data only — re-seeded on re-run |
| `create_wallet_products` | DROP TABLE | ⚠️ Loses backfill B01 data — acceptable pre-go-live |
| `create_sub_wallets` | DROP TABLE | ⚠️ Same as above |
| `create_wallet_currency_configs` | DROP TABLE | ⚠️ Same as above |
| `add_wallet_product_id_to_accounts` | DROP COLUMN | ⚠️ Loses backfill link — re-run B01 if re-migrating |
| `add_sub_wallet_id_to_ledger` | DROP COLUMN | ✅ Nullable — no data in new entries yet |
| `add_sub_wallet_refs_to_transfers` | DROP COLUMN | ✅ Nullable — no data yet |
| `create_sub_wallet_transactions` | DROP TABLE | ✅ New table — empty until Phase 5 |

**Decision rule:** If rollback is executed before Phase 4 code is deployed, there is zero user impact.
After Phase 4 is deployed and backfill B01 is complete, a rollback requires a coordinated
feature flag disable + data export before proceeding.

---

## 6. Go/no-go criteria

### Go criteria (must all pass)
- [ ] All 8 migrations show `[up]` in staging and production
- [ ] `wallet_product_types` has exactly 4 seed rows
- [ ] No increase in error rate on `/api/v1/*` endpoints (±5% tolerance)
- [ ] `accounts` table schema has `wallet_product_id`, `close_reason`, `closed_by` columns
- [ ] `ledger_entries` table schema has `sub_wallet_id` column
- [ ] `ledger_balances` table schema has `sub_wallet_id` column
- [ ] `wallet_transfers` table schema has `from_sub_wallet_id`, `to_sub_wallet_id` columns
- [ ] Backfill B01 completed with 0 errors (or dry-run reviewed)
- [ ] Backfill B02 completed with 0 errors

### No-go triggers (auto-rollback)
- Any migration fails with error (Ecto returns `{:error, ...}`)
- Existing table row counts change unexpectedly
- API p99 latency increases >20%
- Any balance discrepancy detected in smoke tests

---

## 7. Monitoring during migration

```bash
# Watch migration table in real-time
watch -n1 'mysql -u $DB_USER -p $DB_NAME -e "SELECT version, inserted_at FROM schema_migrations ORDER BY version DESC LIMIT 10;"'

# Watch for lock waits
mysql -u $DB_USER -p $DB_NAME -e "SHOW PROCESSLIST;" | grep -i "lock\|wait"
```

Since all `ALTER TABLE` changes add **nullable columns**, MySQL performs these as
metadata-only operations (no table rebuild) on InnoDB with MySQL 5.7+. Lock duration is
sub-millisecond.

---

## 8. Post-migration phase gate

Before proceeding to Phase 4 (domain implementation), confirm:

```
[ ] All 8 migrations applied and verified
[ ] Backfill B01 complete (accounts → wallet_products → sub_wallets linkage)
[ ] Backfill B02 complete (ledger_balances → sub_wallet_id linkage)
[ ] Schema dump committed: git diff HEAD -- priv/repo/structure.sql
[ ] Phase 3 tracker updated
[ ] SRE sign-off recorded in #wallet-deployments Slack channel
```