From the free book Delta: Closing the Specification Gap (Dhuri, 2026). The PDF is the canonical edition — figures, tables, and code formatting are simplified online.
The three rules that prevent the migration incidents you have read about in other teams’ post-mortems
“A migration without a tested rollback is a migration with a production incident built in. AI models generate irreversible migrations by default. One constraint clause prevents this.” — The DBA who has been burned by a migration with ’throw new NotSupportedException()’ in the Down() method has a visceral understanding of Rule 2. Everyone else has a theoretical understanding. Rule 2 is for everyone. In This Chapter
A development team asked AI to generate a migration to ’clean up the old address columns after adding the new shipping address structure.’ The AI generated a migration that added the new columns and dropped the old ones in a single operation. The Down() method body: ’throw new NotSupportedException(”This migration cannot be reversed.”).’ The developer, running late for a demo, merged it without reading the Down() method. The migration ran in production on a Friday evening. The old address columns — still referenced by four reporting queries and a compliance audit export — were gone. No rollback was possible. Manual data recovery took four days. Seventeen compliance reports required reprocessing. A retrospective action item was filed: ’Always read the Down() method.’ This isn’t a sufficient action item. The correct action item is: ’Never allow an AI-generated migration with a non-functional Down() method to merge.’
THE COST OF THIS MISTAKE Incident: Irreversible AI-generated migration dropped columns still ,→ referenced by reporting queries Total cost: 4 days manual data recovery + 17 compliance reports ,→ reprocessed + Friday-to-Tuesday incident Time to resolve: 4 days recovery, 2 weeks reporting remediation Prevention: "Down() must fully reverse Up(). NotSupportedException ,→ in Down() is a BLOCKING review finding. Always."
Rule 1: ADDITIVE BY DEFAULT. Default is to add, never drop. Any destructive operation (DROP COLUMN, DROP TABLE) requires explicit instruction in the task, not inferred from ’clean up’ language. Rule 2: ROLLBACK REQUIRED. Every migration must include a Down() method (EF Core) or rollback SQL script (Flyway) that fully reverses the Up() migration. ’NotSupportedException’ in Down() is a blocking code review finding. Rule 3: SYNTHETIC DATA ONLY. Never include actual production table names, actual column names containing PII, or actual schema details that could identify sensitive data structures in prompts.
BEFORE — Tier 1 (What most developers type) "Write an EF Core migration to add shipping address fields to the ,→ Order entity" AFTER — Tier 3 (Specification Frame) "<role>Senior C# engineer. .NET 9, EF Core 9, PostgreSQL. Additive ,→ migrations, tested rollback.</role><change>Add ShippingAddress ,→ (OwnsOne value object) to Order: AddressLine1 (required, max ,→ 200), City (required, max 100), CountryCode (required, CHAR(2) ,→ ISO 3166), PostalCode (max 20). Nullable initially — existing ,→ rows will be null.</change><task>Produce: updated Order entity ,→ + ShippingAddress record, IEntityTypeConfiguration<Order>, EF ,→ Core migration with Up() AND Down().</task><constraints>OwnsOne ,→ in same table as Order, column prefix ShippingAddress_. Down()
,→
MUST fully reverse Up(). No existing columns dropped.
,→ CheckConstraint on CountryCode regex. Index on CountryCode.</ ,→ constraints>" Result: Tier 1: adds columns, no IEntityTypeConfiguration, no ,→ CheckConstraint, Down() throws NotSupportedException. Tier 3: ,→ OwnsOne configuration, CheckConstraint, non-blocking index, ,→ complete reversible Down(). C# — Entity Configuration (Compilable) // File: src/Infrastructure/Persistence/Orders/OrderConfiguration.cs using Microsoft.EntityFrameworkCore; using Microsoft.EntityFrameworkCore.Metadata.Builders; using YourCompany.Domain.Orders; namespace YourCompany.Infrastructure.Persistence.Orders; public sealed class OrderConfiguration : IEntityTypeConfiguration< ,→ Order> { public void Configure(EntityTypeBuilder<Order> builder) { builder.HasKey(o => o.Id); builder.OwnsOne(o => o.ShippingAddress, sa => { sa.Property(x => x.AddressLine1) .HasColumnName("ShippingAddress_AddressLine1") .HasMaxLength(200).IsRequired(false); sa.Property(x => x.CountryCode) .HasColumnName("ShippingAddress_CountryCode") .HasMaxLength(2).IsFixedLength().IsRequired(false); sa.HasIndex(x => x.CountryCode) .HasDatabaseName("IX_Orders_ShippingAddress_CountryCode ,→ "); sa.ToTable(t => t.HasCheckConstraint( "CK_Orders_CountryCode", "\"ShippingAddress_CountryCode\" IS NULL OR" + " \"ShippingAddress_CountryCode\" ~ '^[A-Z]{2}$'")); }); } }
18.2 Flyway Migration with Rollback (Java) SQL — V4__Add_shipping_address.sql -- Additive. Nullable initially — backfill in a separate job before ,→ adds NOT NULL. ALTER TABLE orders ADD COLUMN IF NOT EXISTS shipping_address_line1 VARCHAR(200), ADD COLUMN IF NOT EXISTS shipping_city
VARCHAR(100),
CHAR(2),
VARCHAR(20);
-- CONCURRENTLY: non-blocking on PostgreSQL CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_shipping_country ON orders (shipping_country_code); ALTER TABLE orders ADD CONSTRAINT chk_orders_country_code CHECK (shipping_country_code IS NULL OR shipping_country_code ~ '^[A-Z]{2}$'); SQL — V4__rollback.sql (Manual Rollback Script) -- Run manually if V4 migration must be reversed. -- Keep this file alongside V4 migration in version control. ALTER TABLE orders DROP CONSTRAINT IF EXISTS chk_orders_country_code; DROP INDEX CONCURRENTLY IF EXISTS idx_orders_shipping_country; ALTER TABLE orders DROP COLUMN IF EXISTS shipping_address_line1, DROP COLUMN IF EXISTS shipping_city, DROP COLUMN IF EXISTS shipping_country_code, DROP COLUMN IF EXISTS shipping_postal_code; -- Also: DELETE FROM flyway_schema_history WHERE version = '4';
18.3 N+1 Detection and @EntityGraph Fix (Java) PATTERN — The Patient Dashboard That Queried 4,000 Times Per Page Load PATTERN-6140 A clinical dashboard displayed 100 recent patients with their current medications. The service loaded the patient list in one query, then loaded medications for each patient in a separate query: 1 + 100 = 101 queries per page load. With 40 clinicians logged in during morning rounds: 4,040 database queries per second from a single page. Database CPU hit 100%. The service timed out. Morning rounds were disrupted for 35 minutes. The AI had generated FetchType.LAZY (the statistical default for @OneToMany) because that is the most common JPA example in training data. The fix: one @EntityGraph annotation loading medications in the original query. 101 queries became 1. The constraint that would have prevented the original N+1: ’Do NOT use FetchType.EAGER. Use explicit @EntityGraph in the specific repository method that requires joined loading.’
THE COST OF THIS MISTAKE Incident: N+1 query: 101 queries per page load × 40 concurrent ,→ clinicians = 4,040 queries/second Total cost: 35-minute morning rounds disruption for 40 clinicians — ,→ operational and patient care impact Time to resolve: 35 minutes disruption, 2 hours diagnosis, 30 minutes ,→ fix + deploy
Prevention: "Do NOT use FetchType.EAGER. Explicit @EntityGraph in ,→ specific repository methods only."
Java — @EntityGraph Fix (Compilable) // File: src/main/java/com/yourcompany/patients/PatientRepository. ,→ java package com.yourcompany.patients; import com.yourcompany.patients.domain.Patient; import org.springframework.data.jpa.repository.EntityGraph; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.Query; import org.springframework.data.repository.query.Param; import java.util.List; import java.util.UUID; public interface PatientRepository extends JpaRepository<Patient, ,→ UUID> { // BEFORE: triggers N+1 when calling patient.getMedications() in ,→ loop List<Patient> findByClinicId(UUID clinicId); // AFTER: single JOIN query — 101 queries → 1 query @EntityGraph(attributePaths = {"medications", "allergies"}) @Query("SELECT p FROM Patient p WHERE p.clinicId = :clinicId") List<Patient> findByClinicIdWithMedications( @Param("clinicId") UUID clinicId); }
ANTI-PATTERN — Asking AI to ’Optimize This Query’ Without EXPLAIN Output AVOID: ”Optimize this JPA query for better performance.” USE: ”Here is the EXPLAIN ANALYZE output: [paste]. Slow operations: [paste]. Total time: [X]ms at [N] rows. Propose: (1) missing indexes with CREATE INDEX CONCURRENTLY, (2) N+1 risks, (3) query rewrite if beneficial. Show before/after execution plan estimate.” Why: Without the EXPLAIN output, the model guesses at the bottleneck. With it, the model can identify the specific operation that is slow and propose a targeted fix. EXPLAIN first, optimize second.
Run EXPLAIN ANALYZE on your three slowest database queries in production. Paste the output into the query optimization prompt. What indexes does it recommend? What N+1 risks does it identify? Implement the top recommendation this sprint.
expensive migration errors: additive by default, rollback required (Down() must work), synthetic data only. + EF Core OwnsOne requires IEntityTypeConfiguration<T>. The model generates field-level properties without it. Always include the configuration constraint. + Flyway migrations must include CREATE INDEX CONCURRENTLY for PostgreSQL — blocking index creation causes production downtime. The constraint prevents the default. + N+1 queries are the most common AI-generated ORM performance error. The fix is @EntityGraph. The constraint that prevents the original mistake: ’Do NOT use FetchType.EAGER. Explicit @EntityGraph only.’ + Every migration must have a tested rollback path. ’Tested’ means: applied Down() in a dev environment and verified the schema is restored. Not ’we think it should work.’
UP NEXT — Chapter 19: DevOps, CI/CD, and Infrastructure Prompting
Chapter 19 covers the domain that produces the highest ratio of expensive mistakes to lines of code: infrastructure. The seven antipatterns in Section 19.1 appear in AI-generated Dockerfiles at a rate of approximately four per five files. Without the constraints, they are the defaults.