Skip to content

artubss/SKILLS-CLAUDE-CODE / favorites/tpl-situacao-migracao-banco-dados

tpl-situacao-migracao-banco-dados

Verified

Template do pack (situacao/02-migracao-banco-dados.md). Orienta o agente em tarefas situacionais como debug, seguranca e refactor alinhado a esse contexto.

SITUATION: Database Migration

Skill gerado a partir do pack templates-claude-code. Arquivo de origem: situacao/02-migracao-banco-dados.md. Use como baseline e adapte ao projeto antes de mudancas grandes.

Conteudo do template

CONTEXT

Use this configuration when making schema changes (adding/removing columns, renaming tables, changing data types, adding constraints, migrating data between tables) on a production database. This applies whether using Postgres, MySQL, SQLite, MongoDB or any other database. The primary risk is data loss and downtime — this configuration eliminates both when followed correctly.

OBJECTIVES

  • Achieve schema changes with zero data loss
  • Maintain application availability during migration
  • Have a verified rollback path before executing forward migration
  • Validate data integrity before and after every migration step
  • Keep migration scripts idempotent and version-controlled

APPROACH RULES

  1. Expand-Contract Pattern (required for production). Never rename a column in one step. Expand first (add the new column), contract later (remove the old one) after all application code has been updated and deployed. Steps: ADD new column → backfill data → deploy app that writes to both → verify → remove old column.

  2. Every migration must have a rollback script. Write the down migration before running the up migration. If you cannot write a rollback (e.g., irreversible data transformations), document exactly why and get explicit sign-off.

  3. Backfill in batches, never in one statement. UPDATE large_table SET ... with millions of rows will lock the table. Use batch updates of 1,000–10,000 rows with a small sleep between batches.

  4. Test migration on staging with production data snapshot. Never run a migration for the first time on production. Always confirm timing and row counts on staging first.

  5. Validate before completing. After migration: check row counts match expected, verify NULL/NOT NULL constraints satisfied, run application smoke tests before announcing done.

  6. Version all migrations. Use sequential numbering (001, 002...) or timestamps. Never edit an already-applied migration file — create a new one.

  7. Foreign key changes need special handling. To add a FK constraint: add the column nullable first → backfill → verify all values are valid → add constraint → make NOT NULL if needed. Never add FK constraint and NOT NULL in one step.

ROUTING TABLE

If you encounterThen
Column renameUse Expand-Contract: add new column, backfill, update app code, verify, drop old column (3 separate deployments)
Table renameCreate a view with the old name pointing to new table; update all app references; remove view in final step
Adding NOT NULL columnAdd as nullable → backfill all rows → add NOT NULL constraint → add DEFAULT if needed
Removing a columnRemove from application code first (deploy) → wait one release cycle → drop column from schema
Large table (1M+ rows)Use pt-online-schema-change (MySQL) or pg_repack (Postgres). Never ALTER TABLE directly
Data type change (e.g., int → bigint)Add new column with new type → backfill → switch app → drop old
Adding unique constraintCheck for duplicates first with a query. If duplicates exist, resolve them before adding constraint
Dropping a tableRename to _archived_tablename first, keep for 30 days, then drop
Migration takes > 5 seconds on stagingReconsider approach — find a batched or online solution
Schema diff between environmentsAlways diff staging vs production before running migration. Use pg_dump --schema-only

DO NOT

  • DO NOT run ALTER TABLE on large tables during business hours without an online schema change tool
  • DO NOT write a migration that depends on application code that hasn't been deployed yet
  • DO NOT deploy new application code that depends on a migration that hasn't run yet
  • DO NOT truncate or drop tables without confirmed backups
  • DO NOT mix schema changes and data changes in the same migration script
  • DO NOT use CASCADE on DROP without listing exactly what will be affected
  • DO NOT edit a migration file that has already been applied to any environment
  • DO NOT assume rollback is possible after dropping a column — verify backup strategy first

OUTPUT FORMAT

For each migration, produce:

Migration Plan Document covering:

Migration: [description]
Ticket: [link]
Author: [name]
Date: [date]

RISK LEVEL: Low / Medium / High / Critical

FORWARD MIGRATION:
1. Step-by-step procedures
2. Expected duration on staging
3. Row counts before/after

ROLLBACK PLAN:
1. Step-by-step rollback
2. Decision point: when to rollback
3. Estimated rollback time

VALIDATION QUERIES:
-- Before migration
SELECT COUNT(*) FROM affected_table;

-- After migration
SELECT COUNT(*) FROM affected_table WHERE new_column IS NOT NULL;

DEPLOYMENT ORDER:
[ ] 1. Run migration script
[ ] 2. Verify validation queries
[ ] 3. Deploy application version X.Y.Z
[ ] 4. Smoke test endpoints
[ ] 5. Monitor error rate for 15 minutes

QUALITY GATES

  • Rollback script written and tested on staging before forward migration runs
  • Migration tested on a snapshot of production data (not just seed data)
  • Batch sizes specified for any UPDATE/INSERT affecting > 10,000 rows
  • All FK constraints validated with a query before being added
  • Zero downtime verified: no exclusive table locks for more than 100ms
  • Migration script is idempotent (can be run twice without error)
  • Validation queries defined and results documented
  • Database backup confirmed before production migration
  • Timing logged: how long did migration take on staging?
  • Application smoke tests pass after migration in staging
finishing-a-development-branchobra/superpowersUse when implementation is complete, all tests pass, and you need to decide how to integrate the workStars: 322kUpdated Claude CodeCodexVerifiedInstall
systematic-debuggingobra/superpowersUse when encountering any bug, test failure, or unexpected behavior, before proposing fixesStars: 322kUpdated Claude CodeCodexVerifiedInstall
test-driven-developmentobra/superpowersUse when implementing any feature or bugfix, before writing implementation codeStars: 322kUpdated Claude CodeCodexVerifiedInstall
skill-creatoranthropics/skillsCreate new skills, modify and improve existing skills, and measure skill performance. Use when users want to create a skill from scratch, edit, or optimize an existing skill, run evals to test a skill, benchmark skill performance with variance analysis, or optimize a skill's description for better triggering accuracy.Stars: 198kUpdated Claude CodeCodexVerifiedInstall