---
name: database-schema-refactoring-tool
description: "Plans a schema refactor you can execute in stages: what to change and why, the migration sequence that keeps the application running, the risks per step, and a rollback for each. Use when an existing production schema needs a staged refactor plan for normalising, partitioning, renaming, splitting, or merging tables without breaking the application. Do not use for writing a single migration script, designing a schema for a new system, or tuning individual queries. Related topics: coding, database."
---

# Database Schema Refactoring Tool

Plans a schema refactor you can execute in stages: what to change and why, the migration sequence that keeps the application running, the risks per step, and a rollback for each.

## When to use

Use this skill when an existing production schema needs a staged refactor plan for normalising, partitioning, renaming, splitting, or merging tables without breaking the application. Do not use it for writing a single migration script, designing a schema for a new system, or tuning individual queries.

## Workflow

1. Ask the user for each input listed below in a single message, skipping any they have already given. Where options are listed, offer them, but accept other answers too.
2. State the goal, affected tables, and the single biggest risk in an overview the team can agree on first.
3. Produce the change mapping table (current object, new object, change type, reason) with every row tied to a stated concern.
4. Sequence the migration as expand and contract steps, each with its statement or script, the lock it takes, and the precondition for the next step.
5. Assess each step's risk with likelihood, impact, and the signal that it is going wrong.
6. Write a rollback action per step and mark the point after which rollback means restoring from backup.

## Inputs

- **Current DBMS** (choose one: MySQL, PostgreSQL, Oracle, SQL Server, SQLite)
- **Current schema** (longer free text)
- **Refactor goal** (choose one: Normalize, Denormalize, Add audit columns, Partition tables, Improve naming, Split a table, Merge tables)
- **Critical tables** (longer free text)
- **Performance concerns** (choose any: Query speed, Index bloat, Storage cost, Concurrency, Migration downtime)
- **Target schema version** (free text, e.g. "3.0")
- **Preserve all data** (choose one: Yes, No)
- **Applications that read or write these tables** (longer free text)

## Output

A Markdown report with sections Overview, Recommended Changes (mapping table), Migration Steps (ordered, with preconditions and locks), Risk Assessment (table per step), and Rollback Actions (bullet list per step).

## Prompt template

Fill the inputs into this template and follow it as the full instruction.

### Database Schema Refactoring Tool

Plan a refactor of this live schema.

- Current DBMS: {{Current DBMS}}
- Current schema: {{Current schema}}
- Refactor goal: {{Refactor goal}}
- Critical tables: {{Critical tables}}
- Performance concerns: {{Performance concerns}}
- Target schema version: {{Target schema version}}
- Preserve all data: {{Preserve all data}}
- Applications that read or write these tables: {{Applications that read or write these tables}}

Begin with an overview that states the goal, the tables affected, and the single biggest risk. List the recommended changes as a mapping table with current object, new object, change type, and reason tied to a stated concern. Sequence the migration using expand and contract so the application never breaks: add the new structures, dual-write or backfill, switch readers, switch writers, verify counts, then drop the old structures, with each step as a runnable statement or a named script and the condition that must hold before the next step. For critical tables prefer online operations and batched backfills; name the lock each step takes. Give a risk assessment per step with likelihood, impact, and the signal that shows it is happening. Finish with rollback actions per step, honest about the point after which rollback means restoring from backup.

## Pitfalls

- Renaming a column in one step while the application still reads the old name, which takes the service down until the deploy lands.
- Backfilling a critical table in a single UPDATE, which holds a lock for the whole run and blocks writers.
- Promising a rollback after the old structures are dropped; past that point only a backup restores data.
