DB Migration Strategy

Purpose

This document defines how we design and apply Entity Framework Core (EF Core) database migrations in a zero-downtime environment. Unlike previous approaches, migrations must now be safe to run while older application versions are still in production.

Key Principles

1. Backward Compatibility First — every database change must be compatible with both:
   • the current (old) application version, and
   • the new application version.
2. No Breaking Changes in a Single Release — avoid changes that require all application instances to be updated simultaneously.
3. Expand → Migrate → Contract Pattern — all schema changes must follow this pattern:
   • Expand: add new structures without removing old ones.
   • Migrate: update application code to use new structures.
   • Contract: remove old structures in a later release.

Migration Strategy

Phase 1: Expand (Safe Changes Only)

Allowed operations:

• Add new tables
• Add nullable columns
• Add columns with default values
• Add indexes
• Add new foreign keys (nullable first)

Avoid:

• Dropping columns/tables
• Renaming columns (use add + copy instead)
• Dropping indexes before a replacement is in place
• Making nullable columns non-nullable immediately

Example:

// Add new column (nullable)
migrationBuilder.AddColumn<string>(
    name: "NewColumn",
    table: "Orders",
    nullable: true);

Phase 2: Application Update

Deploy a new version of the application that:

• writes to both old and new schema (dual write), and
• reads from the old schema OR supports fallback logic.

Example strategy: write to both OldColumn and NewColumn; read from NewColumn if available, otherwise fall back.

Phase 3: Data Migration

Backfill data safely. Options:

• Background job
• Script executed separately
• Incremental migration

Example:

UPDATE Orders
SET NewColumn = OldColumn
WHERE NewColumn IS NULL;

Important:

• Must be idempotent.
• Must not lock large tables for long periods.

Phase 4: Switch Read Path

Update the application to:

• read only from the new schema, and
• stop depending on the old schema.

Phase 5: Contract (Cleanup)

Only after all instances run the new version:

• remove old columns, and
• remove fallback logic.

Example:

migrationBuilder.DropColumn(
    name: "OldColumn",
    table: "Orders");

Handling Common Changes

Marking Fields for Deletion (Required Process)

Before removing any column from the database, the corresponding property in code must be explicitly marked as deprecated using a custom attribute.

Attribute definition — developers must use a shared attribute, e.g.:

[MarkedForDeletion("Reason", "TargetRemovalVersion")]

Rules:

• Properties marked with [MarkedForDeletion] must be private (or at most protected if required by EF Core).
• Classes marked with [MarkedForDeletion] must be internal.

Analyzer enforcement — a Roslyn analyzer enforces the following:

• Any usage of a marked property or class results in a compile-time error.
• Usage is not allowed even within the same class.
• Allowed only in:
  • EF Core model configuration (expression trees), and
  • migration or data backfill code (optional exception).

This guarantees that the field is truly no longer used and that removal is safe in a later release.

Removing a Column (Step-by-Step)

Columns must never be removed in a single release.

1. Stop using the column — update application logic to no longer read/write the column, make sure the column is used in neither the frontend nor the backend, and introduce replacement fields if needed.

2. Mark for deletion (code-only release):

   [MarkedForDeletion("Replaced by NewColumn", "2026-Q3")]
   private string OldColumn;
   

   Ensure there are no remaining usages (enforced by the analyzer) and the application runs without the field.

3. Deploy and observe — run the system in production and verify no runtime dependency exists.

4. Remove from database — create a migration to drop the column and remove the property from code.

Adding a Column with Computed / Derived Values

When introducing a column whose value is derived from existing data, a multi-release approach is required.

1. Expand (add column as nullable):

   migrationBuilder.AddColumn<int>(
       name: "NewColumn",
       table: "Orders",
       nullable: true);
   

   The column must be nullable; no existing data is modified yet.

2. Application update — the new application version writes values for all NEW records; existing records remain NULL.

3. Data backfill (separate release) — create a migration or background process to compute values for existing data:

   UPDATE Orders
   SET NewColumn = /* computation based on existing columns */
   WHERE NewColumn IS NULL;
   

   Requirements: must be idempotent, must run safely on large datasets, avoid long locks.

4. Enforce NOT NULL constraint — after ALL data is populated, make the property non-nullable and remove null checks from code. Only safe once no NULL values remain.

5. Finalize usage — the application fully relies on the new column; old data dependencies are removed if applicable.

Renaming a Column

Do not use rename directly. Instead:

1. Add the new column.
2. Copy the data.
3. Update the app.
4. Remove the old column later.

Changing a Column Type

1. Add a new column with the new type.
2. Backfill the data.
3. Update the app.
4. Remove the old column.

Making a Column Non-Nullable

1. Ensure all rows have values.
2. Add default handling in the app.
3. Apply a migration to make it NOT NULL.

Dropping a Column

Only after no application version uses it anymore.

Deployment Strategy

A typical safe multi-step rollout:

1. Deploy migration (expand).
2. Deploy application (dual write).
3. Run data migration.
4. Deploy application (new read path).
5. Deploy cleanup migration.

EF Core Specific Guidelines

Migration Immutability (Critical Rule)

Once a migration has been merged into main, it must not be:

• edited,
• deleted, or
• reordered.

This applies especially after it has been applied to any shared environment.

Exception — only allowed if the migration is broken and cannot be applied, AND it has NOT been applied to any shared or production environment.

Why this matters — changing or removing migrations can lead to:

• schema drift between environments,
• failed deployments,
• inconsistent migration history, and
• runtime errors due to mismatched schemas.

Instead of modifying existing migrations, always create a new migration to fix or adjust behavior.

Migration Anti-Patterns (Do NOT Do This)

Dropping a column directly

migrationBuilder.DropColumn("OldColumn", "Orders");

Why it breaks: older application versions may still read/write the column, causing immediate runtime failures during rolling deployments.

Renaming columns using EF rename

migrationBuilder.RenameColumn(...);

Why it breaks: the old application version still expects the old name, resulting in missing-column errors.

Making a nullable column non-nullable immediately

migrationBuilder.AlterColumn<string>(nullable: false);

Why it breaks: existing rows may contain NULL values; the migration fails or the application crashes on insert/update.

Removing or editing existing migrations

Why it breaks: environments may already have applied the old version; the migration history becomes inconsistent.

Writing data only to the new column without dual write after renaming a column

Why it breaks: the old application version still reads the old column, leading to inconsistent or missing data.

Large blocking updates in one transaction

UPDATE LargeTable SET ...

Why it breaks: long-running locks can block production traffic and cause timeouts and outages.

Pull Request Checklist

Every PR containing migrations must include the following checklist. In this repo the bot posts it as a comment on any PR that touches Migrations/ or a *ModelSnapshot.cs, gated by the Database migration checklist check (see Development Workflow).

General

• Migration follows Expand → Migrate → Contract pattern
• Migration is backward compatible with the currently released version of SPARK
• No columns / tables are dropped or modified in a way that the currently released version of SPARK cannot handle
• Columns and tables are only dropped if they have been marked for deletion in an already released version of SPARK

Schema Changes

• New columns are nullable or have safe defaults — this is not relevant if the column is introduced in the Hub and not used by SPARK. As the Hub processing is stopped during the release, it can be assured that no new data is added during the migration. If you are not sure, use the two-step release version.
• No columns/tables are dropped prematurely, only after being attributed with MarkedForDeletion in a previous release
• No direct renames (use add + copy instead)

Data Safety

• Data migrations are idempotent (can be applied multiple times without changing the outcome)
• Large updates are batched or safe for production
• No long-running locks expected

Marked for Deletion

• Fields to be removed are marked with [MarkedForDeletion]
• No remaining usages (verified by analyzer)

EF Core Specific

• Auto-generated migration reviewed manually
• No unintended data loss operations
• Existing migrations were NOT modified or deleted

Breaking Changes

• If tables/columns are dropped, a new PBI was created and linked for the next sprint
• If changes require multiple releases, this is planned for the next release cycle (release first release, merge PRs for second release, second release)

Summary

Zero-downtime migrations require discipline:

• Never break existing behavior.
• Introduce changes gradually.
• Remove old structures only after it is safe.

Following this approach ensures continuous availability and safe deployments.