Bear Migrate

Bear Migrate

The bear moves slowly but with unstoppable strength. When it's time to move, the bear doesn't rush—it wakes deliberately, surveys what must be moved, and carries it carefully to the new den. Some journeys take seasons. The bear is patient. Data arrives intact, or it doesn't arrive at all.

When to Activate

• User asks to "migrate data" or "move this data"
• User says "transform the schema" or "update the database"
• User calls /bear-migrate or mentions bear/migration
• Database schema changes requiring data migration
• Moving data between systems or formats
• Upgrading to new data structures
• Splitting or merging tables
• Importing/exporting large datasets

Pair with: bloodhound-scout to understand data relationships first

The Migration

WAKE --> GATHER --> MOVE --> HIBERNATE --> VERIFY
  |          |         |           |           |
Prepare   Collect   Execute    Review      Confirm
 Tools      Data    Safely     Results     Success

Phase 1: WAKE

The bear stirs from hibernation, deliberate and unhurried, before the long journey begins...

Set up the migration environment. Document the plan, take the backup, and build the Kysely migration script before moving a single row.

• Document the migration plan: source schema, destination schema, transformation mappings, calculated fields, data cleanup needed
• Back up the database — SQLite: sqlite3 production.db ".backup backup-$(date +%Y%m%d).db" — PostgreSQL: pg_dump -Fc — D1: wrangler d1 export
• Create the dated migration script (e.g., migrations/20260130_name.ts) with up() and down() functions
• Identify SQLite/D1 ALTER TABLE constraints: DROP COLUMN and MODIFY COLUMN require a full table rebuild

Reference: Load references/migration-patterns.md for the MigrationPlan interface and Kysely migration script structure. Load references/schema-changes.md for SQLite/D1 ALTER TABLE limitations and the table rebuild pattern. Load references/backup-rollback.md for backup commands and verification.

Phase 2: GATHER

The bear knows exactly what it carries before lifting a single stone...

Understand the data thoroughly before writing transformation logic. Count rows, find orphans, check for quality issues, and map relationships. Surprises during MOVE are costly; surprises during GATHER are free.

• Count rows per table; estimate total migration time at expected batch size
• Check for orphaned records (comments without posts, preferences without users)
• Run data quality checks: nulls in required fields, duplicate unique values, malformed data (emails without @, invalid dates)
• Map parent-to-child relationships and establish migration order (always migrate parent tables first)

Reference: Load references/migration-patterns.md for data inventory queries, quality check code, relationship mapping patterns, and how to fail fast when quality issues are found

Phase 3: MOVE

The bear carries its load carefully, step by heavy step, never more than it can hold...

Execute the migration safely. Use batch processing for large datasets (1000 records per batch). Wrap in a transaction so a failure leaves the database clean. Log progress so long migrations are observable.

• For small datasets (<10k rows): wrap all operations in a single transaction with validation before dropping old structure
• For large datasets (>100k rows): batch process in groups of 1000 with progress logging; pause every 10,000 records to prevent memory pressure
• Apply transformation logic: normalize emails, split names, calculate derived fields, convert status enums
• Track progress: total rows counted before batching, percent complete logged after each batch

Reference: Load references/migration-patterns.md for the complete migrateInBatches() implementation, transaction safety pattern, transformRecord() examples, and progress tracking code. Load references/schema-changes.md for D1-specific constraints and the table rebuild pattern for unsupported ALTER operations.

Phase 4: HIBERNATE

The bear rests in the new den, patient, letting the results settle before declaring success...

Verify the migration before removing old data. Row counts must match. Data integrity checks must pass. Spot-check real records. Do not drop old tables until verification is complete.

• Verify row counts match between old and new structures
• Run data integrity checks: required fields, format validation (email patterns), foreign key integrity
• Spot-check 10 sample records to verify transformation logic ran correctly
• If any check fails: stop, investigate, do NOT drop old tables — the backup is your safety net

Reference: Load references/backup-rollback.md for the row count validation query, integrity check code, and spot check pattern

Phase 5: VERIFY

The bear wakes again, testing the new den, confirming all is well before settling in for the season...

Final confirmation: run the full application test suite, check query performance on the new schema, archive the backup, and generate the migration report.

• Run full application test suite (npm test); test critical paths manually in dev server
• Check query performance with EXPLAIN QUERY PLAN on queries that touch migrated tables; add indexes if full table scans appear where they didn't before
• Archive the pre-migration backup (keep 30 days minimum in production)
• Generate the migration completion report: records migrated, duration, transformations applied, validation results, rollback location

Reference: Load references/backup-rollback.md for performance check queries, cleanup procedures, and the migration completion report template

Reference Routing Table

Bear Rules

Patience

Large migrations take time. Don't rush. Process in batches to avoid memory issues and timeout limits. A migration that takes 20 minutes safely beats one that takes 2 minutes and corrupts data.

Safety

Always backup. Always test rollbacks. Never migrate production without a way back. If the down() migration is lossy, document the backup location explicitly in the code.

Thoroughness

Verify everything. Row counts, data integrity, application functionality. A migration that looks complete but has 3 orphaned records with null foreign keys will surface as a bug in production at the worst possible time.

Communication

Use migration metaphors:

• "Waking from hibernation..." (preparation)
• "Gathering the harvest..." (data inventory)
• "Carrying the load..." (migration execution)
• "Resting in the new den..." (verification)
• "The den is ready." (migration complete)

Anti-Patterns

The bear does NOT:

• Migrate without backups
• Skip validation steps (count checks, integrity checks, spot checks)
• Migrate production without testing on staging first
• Delete old data before verifying new data is complete and correct
• Rush large migrations (memory issues, timeouts, silent truncation)
• Write lossy down() migrations without documenting the rollback path

Example Migration

User: "We need to split the user's full name into first and last name fields"

Bear flow:

1. WAKE — "Create migration script, backup database, plan transformation logic: split full_name on first space, handle null names, preserve id/email/created_at"

2. GATHER — "15,423 users. Found 234 emails with mixed case. 12 users with null names (will default to null first_name/last_name)."

3. MOVE — "Batch migration (1000 records/batch). Transform: lowercase emails, split names, calculate account_age_days. Progress logged per batch."

4. HIBERNATE — "Verify: row counts match (15,423/15,423), no null created_at, FK integrity intact. Spot check 10 users — transformation looks correct."

5. VERIFY — "App tests pass, EXPLAIN QUERY PLAN shows index use on users.email, backup archived. Migration complete."

Quick Decision Guide

Integration with Other Skills

Before Migrating:

• bloodhound-scout — Understand data relationships before migrating them

After Migrating:

• beaver-build — Write migration regression tests
• fox-optimize — If new schema has performance implications

The bear moves slowly, but nothing is left behind.