A schema-migration prompt should receive the current schema, target schema, active application versions, write paths, data volume, and deployment order. Its output is a reviewable sequence with compatibility checks, not a single attractive ALTER statement. For a field replacement, add the new field first, move readers and writers under a measured compatibility window, backfill existing rows, then remove the old field only after old clients are gone. Record whether a step is reversible; a destructive drop cannot be undone by rolling back application code. Require a dry run and data-integrity checks owned by the database team.
Make this comfortable
Schema migration prompts: check old and new clients together
Operational case
A parcel system wants to replace a combined delivery_zone field with region_code and route_code. API version 6 still reads delivery_zone while version 7 writes the two new fields. A proposed migration that drops delivery_zone immediately would break version 6 during a rolling deployment. The assistant's plan keeps the old field, introduces the new pair, compares 4,700 migrated rows with the original value, and waits for version 6 traffic to reach zero before contract. If 23 records cannot be parsed, it stops the drop and produces a review list instead of filling guessed codes.
Parcel schema: delivery_zone -> region_code + route_code.
Expand: add new nullable fields; keep old reader path.
Migrate: parse 4,700 rows; 23 unresolved -> review queue.
Verify: round-trip equality and version-6 traffic count.
Contract: drop old field only after unresolved=0 and old traffic=0.Performance and operating cost
A backfill of R rows is O(R) work and may create locks or replication lag; a prompt cannot estimate those costs accurately without database measurements. Dual writes add code and temporary storage, but they preserve compatibility across a rolling release. Keep the model's plan separate from the migration runner and use a staged rehearsal with representative data. Measure unresolved rows, query errors by client version, backfill duration, and rollback feasibility before approving the next phase. Review the generated SQL for dialect and transaction semantics rather than trusting syntactic plausibility.
Common Mistakes
- Do not drop the old column while an active client still reads it.
- Do not silently guess values for unparseable rows.
- Do not promise rollback can recreate deleted production data.
Connected lessons
- Prompt engineering applications
- Prompt Engineering
- Coding prompts: name the files, behavior, and proof
- Prompt release artifacts: version the whole decision path
- Python SQLite migration: commit schema and version as one unit
- Diff-scoped code review: make every finding reproducible
- Generated tests: verify the oracle before trusting coverage
- Incident triage prompts: build a timestamped evidence ledger
- Dependency upgrade prompts: trace behavior, not only versions
- Project: review a checkout release from diff to incident
- Prompt engineering for software delivery
Continue with: Architecture decisions: compare real alternatives and consequences.
Continue with: API documentation prompts: bind prose to a versioned contract.
Continue with: Backfill prompts: pin source, target, and snapshot.
prompt engineering
software delivery