Ops.Migration -- Schema and Data Changes as Typed Steps

"Run migration 47 before deploying the new pod." -- a Slack message, 3 AM, from someone who quit last month.

The Problem

Database migrations are the most dangerous part of any deployment. A schema change applied in the wrong order corrupts data. A data backfill that runs before its prerequisite column exists crashes with a 500-line stack trace. An index creation on a 50M row table locks the database for twelve minutes and nobody warned anyone.

The typical migration workflow:

Developer writes a SQL file: 047_add_payment_status.sql
Developer writes a second SQL file: 048_backfill_payment_status.sql
Developer tells the team in Slack: "run 47 before 48, and 48 takes about 5 minutes"
Someone runs them in production. Maybe in order. Maybe not.
Nobody verifies that the backfill actually worked. The PaymentStatus column has 12,000 NULL rows that nobody discovers until a customer complains.

What is missing:

Typed ordering. Migration 48 depends on migration 47. That dependency is a Slack message, not a compiler constraint.
Validation. After migration 48 runs, how do you verify 100% of rows have a non-NULL PaymentStatus? You write a manual SQL query. Or you do not.
Rollback. If migration 48 fails midway through 2.4 million rows, what is the rollback? "Restore from backup" is not a rollback plan.
Parallelism. Index creation on table A and index creation on table B can run in parallel. Schema changes to the same table cannot. That knowledge lives in someone's head.
Downtime awareness. Some migrations require downtime. The team needs to know before they start, not when the connection pool is exhausted.

Attribute Definitions

// =================================================================
// Ops.Migration.Lib -- Database & Data Migration DSL
// =================================================================

/// Mark a method as a migration step -- one atomic unit of database change.
/// The method body contains the migration logic (SQL or C# code).
[AttributeUsage(AttributeTargets.Method)]
public sealed class MigrationStepAttribute : Attribute
{
    public int Order { get; }
    public string Database { get; init; } = "default";
    public MigrationKind Kind { get; init; } = MigrationKind.Schema;
    public string EstimatedDuration { get; init; } = "";
    public string Description { get; init; } = "";

    public MigrationStepAttribute(int order) => Order = order;
}

public enum MigrationKind
{
    Schema,          // ALTER TABLE, CREATE TABLE, DROP COLUMN
    Data,            // INSERT, UPDATE, DELETE (data transforms)
    Index,           // CREATE INDEX, DROP INDEX (often slow, parallelizable)
    Seed,            // INSERT reference/lookup data
    ExeMigration     // custom C# migrator (complex transforms, file processing)
}

/// Mark a method as an executable migration -- runs custom C# code
/// for transforms too complex for SQL.
[AttributeUsage(AttributeTargets.Method)]
public sealed class ExeMigrationAttribute : Attribute
{
    public int Order { get; }
    public string App { get; init; } = "";
    public string Description { get; init; } = "";
    public string[] InputFiles { get; init; } = [];
    public int ExpectedRowCount { get; init; } = 0;
    public string Timeout { get; init; } = "00:30:00";

    public ExeMigrationAttribute(int order) => Order = order;
}

/// Declare a post-migration validation -- a query that must succeed
/// after the migration step completes.
[AttributeUsage(AttributeTargets.Method, AllowMultiple = true)]
public sealed class MigrationValidationAttribute : Attribute
{
    public string Query { get; }
    public string ExpectedResult { get; init; } = "";
    public MigrationValidationKind Kind { get; init; } = MigrationValidationKind.RowCount;

    public MigrationValidationAttribute(string query) => Query = query;
}

public enum MigrationValidationKind
{
    RowCount,         // SELECT COUNT(*) must match expected
    SchemaCheck,      // column/table existence
    DataIntegrity,    // foreign key consistency, no NULLs where unexpected
    IndexExists       // index presence check
}

/// Declare a dependency between migration steps.
/// The generator uses these to build execution waves.
[AttributeUsage(AttributeTargets.Method, AllowMultiple = true)]
public sealed class MigrationDependencyAttribute : Attribute
{
    public string DependsOnStep { get; }
    public ParallelismKind Parallelism { get; init; } = ParallelismKind.Sequential;

    public MigrationDependencyAttribute(string dependsOnStep) => DependsOnStep = dependsOnStep;
}

public enum ParallelismKind
{
    Sequential,    // must complete before this step starts
    Parallel,      // can run at the same time (different tables)
    Wave           // grouped into a wave -- all in the wave start together
}

/// Flag that a migration step requires downtime.
/// The generator aggregates these and reports total estimated downtime.
[AttributeUsage(AttributeTargets.Method)]
public sealed class DowntimeRequiredAttribute : Attribute
{
    public string EstimatedDuration { get; }
    public string Reason { get; init; } = "";

    public DowntimeRequiredAttribute(string estimatedDuration) => EstimatedDuration = estimatedDuration;
}

/// Link a migration step to its rollback.
/// The referenced method must also be a [MigrationStep].
[AttributeUsage(AttributeTargets.Method)]
public sealed class MigrationRollbackAttribute : Attribute
{
    public Type RollbackClass { get; }
    public string RollbackMethod { get; init; } = "";

    public MigrationRollbackAttribute(Type rollbackClass) => RollbackClass = rollbackClass;
}

Usage Example

OrdersDb v2.4: a schema change, an index, a data backfill of 2.4 million rows, and a rollback plan.

// -- OrdersDbV24Migration.cs ----------------------------------------

public sealed class OrdersDbV24Migration
{
    [MigrationStep(1,
        Database = "OrderDb",
        Kind = MigrationKind.Schema,
        EstimatedDuration = "00:00:30",
        Description = "Add PaymentStatus column to Orders table")]
    [MigrationValidation(
        "SELECT COUNT(*) FROM information_schema.columns " +
        "WHERE table_name = 'Orders' AND column_name = 'PaymentStatus'",
        ExpectedResult = "1",
        Kind = MigrationValidationKind.SchemaCheck)]
    [MigrationRollback(typeof(OrdersDbV24Rollback), RollbackMethod = "DropPaymentStatusColumn")]
    public void AddPaymentStatusColumn() { }

    [MigrationStep(2,
        Database = "OrderDb",
        Kind = MigrationKind.Index,
        EstimatedDuration = "00:02:00",
        Description = "Create index on PaymentStatus for query performance")]
    [MigrationDependency("AddPaymentStatusColumn", Parallelism = ParallelismKind.Sequential)]
    [MigrationValidation(
        "SELECT COUNT(*) FROM sys.indexes WHERE name = 'IX_Orders_PaymentStatus'",
        ExpectedResult = "1",
        Kind = MigrationValidationKind.IndexExists)]
    [MigrationRollback(typeof(OrdersDbV24Rollback), RollbackMethod = "DropPaymentStatusIndex")]
    public void CreatePaymentStatusIndex() { }

    [ExeMigration(3,
        App = "order-migrator",
        Description = "Backfill PaymentStatus from legacy payment_log table (2.4M rows)",
        ExpectedRowCount = 2_400_000,
        Timeout = "01:00:00")]
    [MigrationDependency("AddPaymentStatusColumn", Parallelism = ParallelismKind.Sequential)]
    [MigrationDependency("CreatePaymentStatusIndex", Parallelism = ParallelismKind.Sequential)]
    [MigrationValidation(
        "SELECT COUNT(*) FROM Orders WHERE PaymentStatus IS NULL",
        ExpectedResult = "0",
        Kind = MigrationValidationKind.DataIntegrity)]
    [MigrationRollback(typeof(OrdersDbV24Rollback), RollbackMethod = "ClearPaymentStatus")]
    [DowntimeRequired("00:15:00", Reason = "Exclusive lock on Orders table during bulk update")]
    public void BackfillPaymentStatus() { }
}

// -- OrdersDbV24Rollback.cs -----------------------------------------

public sealed class OrdersDbV24Rollback
{
    [MigrationStep(100,
        Database = "OrderDb",
        Kind = MigrationKind.Data,
        Description = "Set PaymentStatus to NULL for all rows")]
    public void ClearPaymentStatus() { }

    [MigrationStep(101,
        Database = "OrderDb",
        Kind = MigrationKind.Index,
        Description = "Drop PaymentStatus index")]
    [MigrationDependency("ClearPaymentStatus")]
    public void DropPaymentStatusIndex() { }

    [MigrationStep(102,
        Database = "OrderDb",
        Kind = MigrationKind.Schema,
        Description = "Drop PaymentStatus column")]
    [MigrationDependency("DropPaymentStatusIndex")]
    public void DropPaymentStatusColumn() { }
}

Three forward steps. Three rollback steps. Each with validation queries. Each with explicit dependencies. The entire migration is reviewable in a pull request.

Generated Artifacts

`MigrationPlan.g.cs` -- Ordered Execution Plan

The source generator reads all [MigrationStep] and [ExeMigration] attributes, resolves dependencies, computes wave groups, and emits a static execution plan.

// <auto-generated by Ops.Migration.Generators />
namespace Ops.Migration.Generated;

public static class MigrationPlan
{
    public static readonly string Database = "OrderDb";
    public static readonly string Version = "2.4";

    public static readonly IReadOnlyList<MigrationWave> Waves =
    [
        new(WaveNumber: 1,
            Steps:
            [
                new("AddPaymentStatusColumn",
                    Order: 1,
                    Kind: MigrationKind.Schema,
                    EstimatedDuration: TimeSpan.FromSeconds(30),
                    RequiresDowntime: false,
                    HasRollback: true,
                    Validations:
                    [
                        new("SchemaCheck: column 'PaymentStatus' exists", "1"),
                    ]),
            ]),

        new(WaveNumber: 2,
            Steps:
            [
                new("CreatePaymentStatusIndex",
                    Order: 2,
                    Kind: MigrationKind.Index,
                    EstimatedDuration: TimeSpan.FromMinutes(2),
                    RequiresDowntime: false,
                    HasRollback: true,
                    Validations:
                    [
                        new("IndexExists: IX_Orders_PaymentStatus", "1"),
                    ]),
            ]),

        new(WaveNumber: 3,
            Steps:
            [
                new("BackfillPaymentStatus",
                    Order: 3,
                    Kind: MigrationKind.ExeMigration,
                    EstimatedDuration: TimeSpan.FromHours(1),
                    RequiresDowntime: true,
                    DowntimeEstimate: TimeSpan.FromMinutes(15),
                    DowntimeReason: "Exclusive lock on Orders table during bulk update",
                    HasRollback: true,
                    ExpectedRowCount: 2_400_000,
                    Validations:
                    [
                        new("DataIntegrity: no NULL PaymentStatus", "0"),
                    ]),
            ]),
    ];

    public static readonly TimeSpan TotalEstimatedDuration =
        TimeSpan.FromSeconds(30) + TimeSpan.FromMinutes(2) + TimeSpan.FromHours(1);

    public static readonly TimeSpan TotalDowntimeEstimate =
        TimeSpan.FromMinutes(15);

    public static readonly IReadOnlyList<RollbackStep> RollbackPlan =
    [
        new("ClearPaymentStatus", Order: 100, Kind: MigrationKind.Data),
        new("DropPaymentStatusIndex", Order: 101, Kind: MigrationKind.Index),
        new("DropPaymentStatusColumn", Order: 102, Kind: MigrationKind.Schema),
    ];

    public static void ValidateAll(IDbConnection connection)
    {
        foreach (var wave in Waves)
        foreach (var step in wave.Steps)
        foreach (var validation in step.Validations)
        {
            var result = connection.ExecuteScalar<string>(validation.Query);
            if (result != validation.ExpectedResult)
                throw new MigrationValidationException(
                    $"Step '{step.Name}' validation failed: " +
                    $"expected '{validation.ExpectedResult}', got '{result}'");
        }
    }
}

public sealed record MigrationWave(int WaveNumber, IReadOnlyList<MigrationStepInfo> Steps);

public sealed record MigrationStepInfo(
    string Name, int Order, MigrationKind Kind,
    TimeSpan EstimatedDuration, bool RequiresDowntime,
    bool HasRollback,
    IReadOnlyList<ValidationInfo> Validations,
    TimeSpan? DowntimeEstimate = null,
    string? DowntimeReason = null,
    int? ExpectedRowCount = null);

public sealed record ValidationInfo(string Query, string ExpectedResult);
public sealed record RollbackStep(string Name, int Order, MigrationKind Kind);

`MigrationReport.g.md` -- Human-Readable Summary

The generator also emits a Markdown report that can be attached to pull requests or change management tickets:

# Migration Report: OrderDb v2.4

**Generated:** 2026-04-06T14:30:00Z
**Database:** OrderDb
**Total Steps:** 3 (forward) + 3 (rollback)
**Estimated Duration:** 1h 2m 30s
**Downtime Required:** Yes (15 minutes)

## Execution Plan

| Wave | Step | Kind | Duration | Downtime | Rollback |
|------|------|------|----------|----------|----------|
| 1 | AddPaymentStatusColumn | Schema | 30s | No | Yes |
| 2 | CreatePaymentStatusIndex | Index | 2m | No | Yes |
| 3 | BackfillPaymentStatus | ExeMigration | 1h | **15m** | Yes |

## Downtime Windows

- **Wave 3 -- BackfillPaymentStatus:** 15 minutes
  Reason: Exclusive lock on Orders table during bulk update

## Post-Migration Validations

1. Column 'PaymentStatus' exists in Orders table
2. Index 'IX_Orders_PaymentStatus' exists
3. Zero rows with NULL PaymentStatus

## Rollback Plan

| Order | Step | Kind |
|-------|------|------|
| 1 | ClearPaymentStatus | Data |
| 2 | DropPaymentStatusIndex | Index |
| 3 | DropPaymentStatusColumn | Schema |

**Rollback estimated duration:** ~5 minutes

Validation Script: `migration-validation.sql`

For teams that want to run validations independently of the C# runtime:

-- <auto-generated by Ops.Migration.Generators />
-- Migration Validation: OrderDb v2.4

-- Step 1: AddPaymentStatusColumn
SELECT CASE
    WHEN (SELECT COUNT(*) FROM information_schema.columns
          WHERE table_name = 'Orders' AND column_name = 'PaymentStatus') = 1
    THEN 'PASS: PaymentStatus column exists'
    ELSE 'FAIL: PaymentStatus column missing'
END AS Step1_Validation;

-- Step 2: CreatePaymentStatusIndex
SELECT CASE
    WHEN (SELECT COUNT(*) FROM sys.indexes
          WHERE name = 'IX_Orders_PaymentStatus') = 1
    THEN 'PASS: IX_Orders_PaymentStatus index exists'
    ELSE 'FAIL: IX_Orders_PaymentStatus index missing'
END AS Step2_Validation;

-- Step 3: BackfillPaymentStatus
SELECT CASE
    WHEN (SELECT COUNT(*) FROM Orders WHERE PaymentStatus IS NULL) = 0
    THEN 'PASS: No NULL PaymentStatus values'
    ELSE 'FAIL: ' + CAST((SELECT COUNT(*) FROM Orders WHERE PaymentStatus IS NULL) AS VARCHAR)
         + ' rows with NULL PaymentStatus'
END AS Step3_Validation;

Analyzer Diagnostics

OPS004: Data Migration Without Validation Query

[MigrationStep(5,
    Database = "OrderDb",
    Kind = MigrationKind.Data,
    Description = "Backfill legacy addresses")]
// No [MigrationValidation] on this method
public void BackfillAddresses() { }

// warning OPS004: Data migration 'BackfillAddresses' has no
//   [MigrationValidation]. Data migrations should have at least one
//   validation query to verify correctness after execution.
//   Add [MigrationValidation("SELECT COUNT(*) FROM ...", ExpectedResult = "0")]

Data migrations change existing rows. Without a validation query, there is no way to verify the backfill completed correctly. The analyzer enforces this as a warning (configurable to error via .editorconfig).

OPS005: Parallel Conflict on Same Table

[MigrationStep(10,
    Database = "OrderDb",
    Kind = MigrationKind.Schema,
    Description = "Add column A to Orders")]
public void AddColumnA() { }

[MigrationStep(11,
    Database = "OrderDb",
    Kind = MigrationKind.Schema,
    Description = "Add column B to Orders")]
[MigrationDependency("AddColumnA", Parallelism = ParallelismKind.Parallel)]
public void AddColumnB() { }

// error OPS005: Migration steps 'AddColumnA' and 'AddColumnB' are marked
//   as Parallel but both target database 'OrderDb' with Kind = Schema.
//   Two schema changes to the same database cannot run in parallel safely.
//   Change Parallelism to Sequential or Wave.

The analyzer parses the Database and Kind properties. Two Schema changes to the same database cannot be parallel -- they would compete for schema locks. Two Index changes on different tables can be parallel. The analyzer knows the rules.

OPS006: Migration Without Rollback

[MigrationStep(20,
    Database = "OrderDb",
    Kind = MigrationKind.Schema,
    Description = "Drop legacy column")]
// No [MigrationRollback] on this method
public void DropLegacyColumn() { }

// warning OPS006: Migration step 'DropLegacyColumn' has no
//   [MigrationRollback]. Schema changes should have a rollback path.
//   Add [MigrationRollback(typeof(...), RollbackMethod = "...")]
//   or suppress this warning if the change is intentionally irreversible.

Not every migration can be rolled back (a column drop is destructive). But the analyzer requires you to acknowledge that. Either provide a rollback or suppress the warning with a comment. The default is to warn.

Cross-DSL Integration

Migration to Deployment

The Deployment DSL declares gates:

[DeploymentGate("migration-47",
    Kind = GateKind.MigrationComplete,
    Target = "OrderDb:47")]

The Migration analyzer verifies that step 47 exists in the Migration DSL for database OrderDb. If it does not, the build fails:

error OPS020: DeploymentGate 'migration-47' references migration step 47
  on database 'OrderDb', but no [MigrationStep(47, Database = "OrderDb")]
  exists in the compilation.

The generated MigrationPlan.g.cs also exposes a IsComplete(int stepOrder) method that the deployment runtime can call to verify the gate.

Migration to Resilience

Every forward migration step that has a [MigrationRollback] is linked to the Resilience DSL's rollback infrastructure. The Resilience DSL's [RollbackPlan] can reference migration rollback steps:

[RollbackPlan(
    steps: ["ClearPaymentStatus", "DropPaymentStatusIndex", "DropPaymentStatusColumn"],
    AutomaticThreshold = "error_rate > 5%")]

The generated rollback plan in the Migration DSL and the rollback plan in the Resilience DSL are cross-validated. If a step name does not exist in either DSL, the analyzer reports it.

Migration to Observability

The Migration generator emits metrics for each step:

// <auto-generated by Ops.Migration.Generators />
// These metrics are discoverable by the Observability DSL

[Metric("migration_step_duration_seconds",
    MetricKind.Histogram,
    Unit = "seconds",
    Description = "Duration of each migration step execution")]
[Metric("migration_rows_affected",
    MetricKind.Counter,
    Unit = "rows",
    Description = "Number of rows affected by data migrations")]
[Metric("migration_validation_result",
    MetricKind.Gauge,
    Unit = "boolean",
    Description = "1 if validation passed, 0 if failed")]

The Observability DSL picks these up and wires them into the monitoring stack. A Grafana dashboard panel can show migration progress in real time.

The Compile-Time Safety Net

Without the Migration DSL:

Migration 48 runs before migration 47. The column does not exist. Production is down.
The backfill completes but 12,000 rows have NULL values. Nobody checks for two weeks.
A rollback is needed. Nobody remembers the steps. The backup is 6 hours old.
Two developers add migrations to the same table in the same sprint. They both succeed locally. In production, one takes a schema lock and the other deadlocks.

With the Migration DSL:

The compiler rejects parallel schema changes to the same database.
The compiler rejects data migrations without validation queries.
The compiler warns about missing rollback plans.
The generated execution plan shows waves, timing, and downtime requirements.
The validation scripts run automatically after each step.
The rollback plan is code, not a memory.

Migration ordering is not a Slack message. It is a DAG.

`[` or `Alt+S`	Focus sidebar navigation
`]` or `Alt+C`	Focus main content
`↑` `↓`	Navigate between sidebar items
`Enter`	Open page / toggle section
`Space`	Toggle section expand/collapse
`Escape`	Close overlay / sidebar

`Ctrl+K`	Open search
`?`	Show this help

`Ctrl+=` or `Ctrl+↑`	Increase font size
`Ctrl+−` or `Ctrl+↓`	Decrease font size
`f`	Open console font selector

`Ctrl+⇧+=` or `Ctrl+⇧+↑`	Browser zoom in
`Ctrl+⇧+−` or `Ctrl+⇧+↓`	Browser zoom out
`Ctrl+⇧+0`	Reset browser zoom

`Tab`	Focus a diagram or image
`Enter`	Open full size overlay
`+` `−`	Zoom in / out (in overlay)
`Escape`	Close overlay, return focus

Ops.Migration -- Schema and Data Changes as Typed Steps📋

The Problem📋

Attribute Definitions📋

Usage Example📋

Generated Artifacts📋

MigrationPlan.g.cs -- Ordered Execution Plan📋

MigrationReport.g.md -- Human-Readable Summary📋

Validation Script: migration-validation.sql📋

Analyzer Diagnostics📋

OPS004: Data Migration Without Validation Query📋

OPS005: Parallel Conflict on Same Table📋

OPS006: Migration Without Rollback📋

Cross-DSL Integration📋

Migration to Deployment📋

Migration to Resilience📋

Migration to Observability📋

The Compile-Time Safety Net📋