artubss/SKILLS-CLAUDE-CODE / favorites/tpl-situacao-migracao-banco-dados
tpl-situacao-migracao-banco-dados
VerifiedTemplate do pack (situacao/02-migracao-banco-dados.md). Orienta o agente em tarefas situacionais como debug, seguranca e refactor alinhado a esse contexto.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 6.1 KB |
SKILL.md
View on GitHub---
name: tpl-situacao-migracao-banco-dados
description: Template do pack (situacao/02-migracao-banco-dados.md). Orienta o agente em tarefas situacionais como debug, seguranca e refactor alinhado a esse contexto.
metadata:
version: 1.0.0
source_template: situacao/02-migracao-banco-dados.md
generated_by: install_pack_templates_as_claude_skills
---
# 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 encounter | Then |
|-----------------|------|
| Column rename | Use Expand-Contract: add new column, backfill, update app code, verify, drop old column (3 separate deployments) |
| Table rename | Create a view with the old name pointing to new table; update all app references; remove view in final step |
| Adding NOT NULL column | Add as nullable → backfill all rows → add NOT NULL constraint → add DEFAULT if needed |
| Removing a column | Remove 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 constraint | Check for duplicates first with a query. If duplicates exist, resolve them before adding constraint |
| Dropping a table | Rename to `_archived_tablename` first, keep for 30 days, then drop |
| Migration takes > 5 seconds on staging | Reconsider approach — find a batched or online solution |
| Schema diff between environments | Always 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
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| finishing-a-development-branchobra/superpowersUse when implementation is complete, all tests pass, and you need to decide how to integrate the work | Stars: 323k | Updated | Claude CodeCodex | Verified | Install |
| systematic-debuggingobra/superpowersUse when encountering any bug, test failure, or unexpected behavior, before proposing fixes | Stars: 323k | Updated | Claude CodeCodex | Verified | Install |
| test-driven-developmentobra/superpowersUse when implementing any feature or bugfix, before writing implementation code | Stars: 323k | Updated | Claude CodeCodex | Verified | Install |
| 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: 198k | Updated | Claude CodeCodex | Verified | Install |