Skip to content

Database Migrations - Audit Trail Platform (ATP)

Schema evolution without disruption — ATP's database migrations enable safe schema changes with FluentMigrator and EF Core, ensuring zero-downtime deployments, backward compatibility, and rollback safety for multi-tenant audit data.

📋 Documentation Generation Plan

This document will be generated in 7 cycles. Current progress:

Cycle Topics Estimated Lines Status
Cycle 1 Migration Fundamentals & Strategy (1-2) ~2,500 ⏳ Not Started
Cycle 2 FluentMigrator Implementation (3-4) ~3,500 ⏳ Not Started
Cycle 3 Entity Framework Core Migrations (5-6) ~3,000 ⏳ Not Started
Cycle 4 Zero-Downtime Migration Patterns (7-8) ~3,500 ⏳ Not Started
Cycle 5 Multi-Tenant Migrations (9-10) ~3,000 ⏳ Not Started
Cycle 6 Testing, Rollback & Recovery (11-12) ~3,000 ⏳ Not Started
Cycle 7 CI/CD Integration & Compliance (13-14) ~2,500 ⏳ Not Started

Total Estimated Lines: ~21,000

Purpose & Scope

This document defines comprehensive database migration strategies for the Audit Trail Platform (ATP), establishing schema evolution patterns, migration tools (FluentMigrator, Entity Framework Core), zero-downtime deployment procedures, multi-tenant considerations, rollback strategies, and CI/CD integration to ensure safe, reliable, and auditable database schema changes across all ATP services and environments.

Key Migration Principles - Version-Controlled Migrations: All schema changes tracked in Git as migration files - Forward-Only: Migrations applied sequentially, never skipped (except rollback scenarios) - Idempotent: Migrations can be safely re-run without side effects - Tested: All migrations tested in dev/test before production - Zero-Downtime: Schema changes compatible with running application versions - Backward Compatible: New schema works with old application code during deployment - Audited: All migration executions logged for compliance - Rollback-Safe: Every migration has rollback procedure (Down method)

ATP Migration Tools - FluentMigrator: Primary migration tool for SQL Server, PostgreSQL, MySQL (ATP default) - Entity Framework Core: Alternative for .NET-centric services - MongoDB Migrations: CSharpMongoMigrations for document databases - Azure SQL: Cloud-native features (online index rebuild, resumable operations) - Migration Runner: In-process during application startup with retry logic

What this document covers

  • Establish migration fundamentals: What they are, why needed, versioning strategy, ATP approach
  • Define FluentMigrator usage: Migration classes, Up/Down methods, schema builder API, multi-database support
  • Specify Entity Framework Core migrations: Code-first approach, migration generation, database update
  • Document zero-downtime patterns: Expand-contract, backward compatibility, feature flags during migration
  • Detail multi-tenant migrations: Tenant-scoped schemas, parallel execution, tenant isolation
  • Describe data migrations: Seeding data, transforming data, large-scale data updates
  • Outline testing strategies: Migration testing, rollback testing, data integrity validation
  • Specify rollback procedures: Down migrations, point-in-time restore, partial rollback
  • Document CI/CD integration: Automated migration in pipelines, validation gates, production approval
  • Detail performance optimization: Large table migrations, index creation, partition management
  • Describe compliance and audit: Migration logs, approval workflows, change tracking
  • Outline troubleshooting: Common migration issues, debugging, recovery procedures
  • Specify migration monitoring: Execution metrics, failure tracking, duration monitoring
  • Document best practices: Do's and don'ts, anti-patterns, migration design guidelines

Out of scope (referenced elsewhere)

Readers & ownership

  • Backend Developers (owners): Migration authoring, testing, execution
  • Database Administrators: Schema design, performance optimization, production migrations
  • Platform Engineering: Migration automation, CI/CD integration, infrastructure
  • QA/Test Engineers: Migration testing, rollback validation, data integrity testing
  • Operations/SRE: Production migration execution, monitoring, incident response
  • Compliance/Audit: Migration audit trails, approval workflows, change tracking

Artifacts produced

  • Migration Files: FluentMigrator/EF Core migration classes in source control
  • Migration Catalog: All migrations with version, description, author, date
  • Migration Procedures: Runbooks for each migration type (schema, data, index)
  • Zero-Downtime Patterns: Expand-contract templates, feature flag examples
  • Testing Framework: Migration test suite, rollback tests, data integrity validators
  • Automation Scripts: CI/CD pipeline for automated migrations, validation gates
  • Rollback Procedures: Down migrations, restore procedures, partial rollback guides
  • Monitoring Dashboards: Migration execution metrics, failure tracking, duration trends
  • Compliance Logs: Migration audit trail, approval records, change documentation
  • Performance Guidelines: Large table migration strategies, index optimization
  • Troubleshooting Guide: Common issues, debugging procedures, recovery steps
  • Best Practices Catalog: Migration design patterns, anti-patterns to avoid

Acceptance (done when)

  • All migration tools (FluentMigrator, EF Core) are documented with complete usage guides
  • Migration workflow is documented from creation through production deployment
  • Zero-downtime patterns are specified with expand-contract examples
  • Multi-tenant considerations ensure tenant isolation during migrations
  • Testing framework validates migrations in dev/test before production
  • Rollback procedures are documented and tested for all migration types
  • CI/CD integration automates migration execution with validation gates
  • Monitoring and alerting track migration execution and failures
  • Compliance audit trail captures all migration events with approvals
  • Performance optimization handles large table migrations without blocking
  • Troubleshooting guide covers common migration issues and resolutions
  • Best practices documented with code examples and anti-patterns
  • Documentation complete with migration templates, runbooks, and cross-references

Detailed Cycle Plan

CYCLE 1: Migration Fundamentals & Strategy (~2,500 lines)

Topic 1: Database Migration Fundamentals

What will be covered:

  • What are Database Migrations?
  • Version-controlled schema changes
  • Sequential evolution of database structure
  • Code-based (not manual SQL scripts)
  • Applied automatically during deployment

  • Tracked in migration history table

  • Why Database Migrations?

  • Version Control: Schema changes in Git alongside code
  • Automation: No manual SQL execution in production
  • Repeatability: Same schema across all environments
  • Auditability: Complete history of schema evolution
  • Collaboration: Multiple developers, coordinated changes

  • Rollback: Revert schema changes if needed

  • Migration Tools Comparison

Tool Language ATP Usage Pros Cons
FluentMigrator C# fluent API ✅ Primary Multi-DB, expressive, versioned Code-heavy
EF Core Migrations C# code-first ⚠️ Alternative Integrated with EF SQL Server focused
Flyway SQL scripts ❌ Not used Simple, SQL-native Less type-safe
Liquibase XML/YAML ❌ Not used Database-agnostic XML complexity
DbUp C# embedded SQL ❌ Not used Simple Limited features
  • ATP Migration Strategy
  • Primary Tool: FluentMigrator (multi-database support, type-safe)
  • Fallback: Entity Framework Core migrations (for EF-centric services)
  • Execution: In-process during application startup (with retry)
  • Environments: Dev → Test → Staging → Production

  • Approval: Automated (dev/test), manual approval (staging/production)

  • Migration Versioning

  • Version Number: Sequential integers (1, 2, 3, ..., 100, 101, ...)
  • Timestamp-Based: Alternative (202410301030 = 2024-10-30 10:30)
  • ATP Convention: Timestamp-based for ordering across teams

  • Format: YYYYMMDDHHMMSS (20241030103000)

  • Migration History Tracking 

    CREATE TABLE [dbo].[VersionInfo] (
    [Version] BIGINT NOT NULL PRIMARY KEY,
    [AppliedOn] DATETIME2 NOT NULL,
    [Description] NVARCHAR(1024) NULL,
    [AppliedBy] NVARCHAR(256) NULL,
    [ExecutionTime] INT NULL, -- milliseconds
    [Success] BIT NOT NULL DEFAULT 1
);

  • Migration Lifecycle 

    1. Developer creates migration (Up and Down methods)
2. Migration committed to Git
3. CI pipeline validates migration (lint, build, test)
4. Deployment to dev environment (automatic)
5. Deployment to test environment (automatic after validation)
6. Deployment to staging (manual approval)
7. Deployment to production (dual approval + CAB)
8. Migration history recorded
9. Monitoring for issues
10. Rollback if needed (Down method or restore)

Code Examples: - Migration history table schema - Version number formats - Migration lifecycle workflow - Tool comparison code snippets

Diagrams: - Migration lifecycle flow - Version control integration - Migration history tracking - Tool comparison matrix

Deliverables: - Migration fundamentals guide - ATP migration strategy - Versioning conventions - Lifecycle documentation

Topic 2: Migration Design Patterns

What will be covered:

  • Schema Migration Patterns

1. Additive Changes (Safe, Backward Compatible) - Add new table - Add new column (nullable or with default) - Add new index - Add new constraint (non-blocking) - Expand column size (varchar(50) → varchar(100))

2. Destructive Changes (Breaking, Requires Careful Handling) - Drop table (requires data migration first) - Drop column (requires expand-contract) - Rename column (requires dual-write period) - Change column type (requires data transformation) - Add NOT NULL constraint (requires backfill)

3. Refactoring Patterns - Expand-Contract: Add new schema → dual-write → migrate data → remove old schema - Parallel Change: Run old and new schemas simultaneously - Strangler Fig: Gradually migrate from old to new schema

  • Data Migration Patterns
  • Backfill: Populate new column with data from existing columns
  • Transform: Change data format or structure
  • Split: Split column into multiple columns
  • Merge: Combine multiple columns into one

  • Lookup: Replace values with foreign keys

  • Index Migration Patterns

  • Online Index Creation: CREATE INDEX ONLINE (Azure SQL)
  • Resumable Index: CREATE INDEX RESUMABLE (large tables)
  • Index Rebuild: Reorganize fragmented indexes

  • Disable/Enable: Disable index during bulk operations

  • ATP Migration Categories

  • Schema Migrations: Table structure changes (70% of migrations)
  • Data Migrations: Data transformation and seeding (20%)
  • Index Migrations: Performance optimization (10%)
  • Compliance Migrations: GDPR, retention policy enforcement

Code Examples: - Additive migration examples - Destructive migration with expand-contract - Data transformation migration - Online index creation

Diagrams: - Expand-contract pattern timeline - Parallel change strategy - Data migration flow - Index creation strategies

Deliverables: - Migration pattern catalog - Design pattern templates - ATP categorization guide - Best practices per pattern

CYCLE 2: FluentMigrator Implementation (~3,500 lines)

Topic 3: FluentMigrator Basics

What will be covered:

  • FluentMigrator Overview
  • .NET library for database migrations
  • Fluent API for schema definition
  • Multi-database support (SQL Server, PostgreSQL, MySQL, Oracle)
  • Version tracking and ordering

  • Rollback support (Down methods)

  • Migration Class Structure 

    using FluentMigrator;

namespace ATP.Ingestion.Migrations
{
    [Migration(20241030103000, "Add EventStream table for hash chain storage")]
    public class AddEventStreamTable : Migration
    {
        public override void Up()
        {
            Create.Table("EventStreams")
                .WithColumn("StreamId").AsGuid().NotNullable().PrimaryKey()
                .WithColumn("TenantId").AsString(50).NotNullable().Indexed()
                .WithColumn("SubjectId").AsString(128).NotNullable()
                .WithColumn("StreamType").AsString(50).NotNullable()
                .WithColumn("Status").AsInt32().NotNullable() // Enum: Open, Sealed, Archived
                .WithColumn("FirstEventId").AsString(26).Nullable() // ULID
                .WithColumn("LastEventId").AsString(26).Nullable()
                .WithColumn("EventCount").AsInt32().NotNullable().WithDefaultValue(0)
                .WithColumn("HashChainHead").AsString(64).Nullable() // SHA-256 hex
                .WithColumn("MerkleRoot").AsString(64).Nullable()
                .WithColumn("CreatedAt").AsDateTime2().NotNullable()
                .WithColumn("SealedAt").AsDateTime2().Nullable()
                .WithColumn("ArchivedAt").AsDateTime2().Nullable();

            Create.Index("IX_EventStreams_TenantId_Status")
                .OnTable("EventStreams")
                .OnColumn("TenantId").Ascending()
                .OnColumn("Status").Ascending();
        }

        public override void Down()
        {
            Delete.Index("IX_EventStreams_TenantId_Status").OnTable("EventStreams");
            Delete.Table("EventStreams");
        }
    }
}

  • FluentMigrator Fluent API

  • Create: Tables, columns, indexes, constraints, schemas
  • Alter: Add/drop columns, change types, modify constraints
  • Delete: Tables, columns, indexes, constraints
  • Insert: Seed data

  • Execute: Custom SQL when needed

  • Column Types and Mappings 

    // String types
.AsString(50)              // VARCHAR(50)
.AsString(4000)            // VARCHAR(4000)
.AsString(Int32.MaxValue)  // VARCHAR(MAX) or TEXT
.AsFixedLengthString(10)   // CHAR(10)

// Numeric types
.AsInt32()                 // INT
.AsInt64()                 // BIGINT
.AsDecimal(18, 2)          // DECIMAL(18,2)
.AsFloat()                 // FLOAT
.AsDouble()                // DOUBLE

// Date/Time types
.AsDateTime()              // DATETIME
.AsDateTime2()             // DATETIME2 (SQL Server, higher precision)
.AsDate()                  // DATE
.AsTime()                  // TIME

// Binary types
.AsBinary(1024)            // VARBINARY(1024)
.AsBinary(Int32.MaxValue)  // VARBINARY(MAX)

// Other types
.AsBoolean()               // BIT (SQL Server) or BOOLEAN
.AsGuid()                  // UNIQUEIDENTIFIER (SQL Server) or UUID
.AsCustom("JSONB")         // PostgreSQL JSONB

  • Constraints and Indexes 

    // Primary Key
.PrimaryKey("PK_EventStreams")

// Foreign Key
Create.ForeignKey("FK_Events_EventStreams")
    .FromTable("Events").ForeignColumn("StreamId")
    .ToTable("EventStreams").PrimaryColumn("StreamId")
    .OnDelete(Rule.Cascade);

// Unique Constraint
.Unique("UQ_EventStreams_TenantId_SubjectId")

// Check Constraint
.WithColumn("Status").AsInt32()
    .WithConstraint("CHK_Status", "Status IN (0, 1, 2)")

// Default Value
.WithDefaultValue(0)
.WithDefaultValue(SystemMethods.CurrentDateTime)

// Index
Create.Index("IX_Events_TenantId_Timestamp")
    .OnTable("Events")
    .OnColumn("TenantId").Ascending()
    .OnColumn("Timestamp").Descending()
    .WithOptions().NonClustered();

  • Multi-Database Support

  • Same migration code for SQL Server, PostgreSQL, MySQL
  • Database-specific features via IfDatabase
  • Database-agnostic by default

Code Examples: - Complete FluentMigrator migration class - All column types with examples - Constraints and indexes - Multi-database conditional code

Diagrams: - FluentMigrator architecture - Migration execution flow - Column type mapping - Index creation strategies

Deliverables: - FluentMigrator usage guide - Migration templates - API reference - Multi-database guide

Topic 4: ATP Migration Organization

What will be covered:

  • Migration Project Structure 

    ATP.Ingestion.DatabaseModel.Migrations/
├── Migrations/
│   ├── 20241001_Initial.cs
│   ├── 20241015_AddEventStream.cs
│   ├── 20241030_AddHashChain.cs
│   └── 20241115_AddMerkleTree.cs
├── Seeds/
│   ├── DevSeedData.cs
│   ├── TestSeedData.cs
│   └── ProductionSeedData.cs
├── Scripts/
│   ├── migration-runner.ps1
│   └── rollback-helper.ps1
└── ATP.Ingestion.DatabaseModel.Migrations.csproj

  • Migration Naming Convention

  • Format: YYYYMMDDHHMMSS_DescriptiveName.cs
  • Example: 20241030103000_AddEventStreamTable.cs
  • Version: Timestamp as long integer (20241030103000)

  • Description: Verb + noun (AddEventStreamTable, AlterEventsIndex)

  • Migration Attributes 

    [Migration(20241030103000, 
    Description = "Add EventStream table for hash chain storage",
    TransactionBehavior = TransactionBehavior.Default,
    BreakingChange = false)]
public class AddEventStreamTable : Migration
{
    // Implementation
}

  • ATP Schema Naming

  • Schema per bounded context: Ingestion, Query, Policy, Export, Integrity
  • Table naming: PascalCase plural (EventStreams, AuditEvents, Classifications)
  • Column naming: PascalCase (TenantId, EventId, CreatedAt)
  • Index naming: IX_{Table}_{Columns} (IX_Events_TenantId_Timestamp)

  • Foreign key naming: FK_{FromTable}_{ToTable} (FK_Events_EventStreams)

  • Migration Registration 

    // Startup.cs
services.AddFluentMigratorCore()
    .ConfigureRunner(rb => rb
        .AddSqlServer() // or AddPostgreSql(), AddMySql()
        .WithGlobalConnectionString(connectionString)
        .ScanIn(typeof(AddEventStreamTable).Assembly)
            .For.Migrations())
    .AddLogging(lb => lb.AddFluentMigratorConsole());

// Run migrations on startup
app.RunMicroserviceFluentMigrations();

Code Examples: - Complete migration project structure - Naming convention examples - Migration attributes - Registration and execution code

Diagrams: - Project organization - Migration discovery process - Schema naming hierarchy - Execution pipeline

Deliverables: - Project structure template - Naming conventions guide - Registration procedures - ATP schema standards

CYCLE 3: Entity Framework Core Migrations (~3,000 lines)

Topic 5: EF Core Code-First Migrations

What will be covered:

  • EF Core Migrations Overview
  • Code-first approach (define entities, generate migrations)
  • DbContext with DbSet properties
  • Migration generation via CLI

  • Automatic schema synchronization

  • DbContext Definition 

    public class AtpDbContext : DbContext
{
    public DbSet<AuditEvent> AuditEvents { get; set; }
    public DbSet<EventStream> EventStreams { get; set; }
    public DbSet<Classification> Classifications { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Entity configurations
        modelBuilder.Entity<AuditEvent>(entity =>
        {
            entity.ToTable("AuditEvents", "Ingestion");
            entity.HasKey(e => e.EventId);
            entity.Property(e => e.EventId).HasMaxLength(26); // ULID
            entity.Property(e => e.TenantId).HasMaxLength(50).IsRequired();
            entity.HasIndex(e => new { e.TenantId, e.Timestamp });
        });
    }
}

  • Migration Generation 

    # Generate migration
dotnet ef migrations add AddEventStreamTable --project ATP.Ingestion.DatabaseModel

# Preview SQL
dotnet ef migrations script --from 0 --to AddEventStreamTable

# Apply to database
dotnet ef database update

# Rollback
dotnet ef database update PreviousMigration

  • Generated Migration File 

    public partial class AddEventStreamTable : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "EventStreams",
            schema: "Ingestion",
            columns: table => new
            {
                StreamId = table.Column<Guid>(nullable: false),
                TenantId = table.Column<string>(maxLength: 50, nullable: false),
                // ... other columns
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_EventStreams", x => x.StreamId);
            });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "EventStreams",
            schema: "Ingestion");
    }
}

Complete EF Core migration guide

Topic 6: EF Core vs FluentMigrator

What will be covered:

  • When to Use Each Tool
  • FluentMigrator: Multi-database, complex migrations, fine control
  • EF Core: Code-first, rapid development, EF-integrated services

  • ATP Recommendation: FluentMigrator for production services

  • Migration Tool Coexistence

  • Can use both in same project (different databases)
  • Separate migration history tables
  • Coordination required for shared schemas

Complete comparison and coexistence guide

CYCLE 4: Zero-Downtime Migration Patterns (~3,500 lines)

Topic 7: Expand-Contract Pattern

What will be covered:

  • Expand-Contract Overview
  • Three-phase pattern for breaking changes
  • Phase 1 (Expand): Add new schema alongside old
  • Phase 2 (Migrate): Dual-write to old and new, migrate data

  • Phase 3 (Contract): Remove old schema

  • Example: Renaming Column 

    Current: Column "EventData"
Target: Column "Payload"

Phase 1 - Expand (Migration 1):
- Add new column "Payload" (nullable)
- Deploy application v1.1 (writes to both EventData and Payload)

Phase 2 - Migrate (Background job):
- Copy EventData → Payload for all existing rows
- Verify all rows have Payload populated

Phase 3 - Contract (Migration 2, weeks later):
- Deploy application v1.2 (writes only to Payload, ignores EventData)
- Drop column "EventData"

  • Expand-Contract Timeline 

    Week 1: Migration 1 (Expand) - Add Payload column
Week 1: Deploy v1.1 - Dual-write to EventData and Payload
Week 2-3: Background job copies data
Week 4: Migration 2 (Contract) - Drop EventData column
Week 4: Deploy v1.2 - Use only Payload

  • Dual-Write Implementation 

    public class AuditEvent
{
    // Old column (deprecated)
    [Obsolete("Use Payload instead")]
    public string EventData { get; set; }

    // New column
    public string Payload { get; set; }

    // Ensure both are set during transition
    public void SetPayload(string value)
    {
        Payload = value;
        EventData = value; // Dual-write during transition
    }
}

  • Feature Flags for Migration Phases

  • Flag: UseNewPayloadColumn (default: false → true)
  • Phase 1: Flag false, dual-write
  • Phase 2: Flag true, read new column
  • Phase 3: Remove old column and flag

Code Examples: - Complete expand-contract migrations - Dual-write code - Data migration script - Feature flag integration

Diagrams: - Expand-contract timeline - Dual-write architecture - Migration phases - Feature flag usage

Deliverables: - Expand-contract template - Dual-write patterns - Migration orchestration - Timeline planning guide

Topic 8: Backward Compatibility Strategies

What will be covered:

  • Additive-Only Migrations
  • Add columns as nullable or with defaults
  • Add indexes online (non-blocking)

  • Add tables (no impact on existing)

  • Rolling Deployment Compatibility

  • New schema must work with old code (blue-green deployment)
  • Old schema must work with new code (rollback scenario)

  • Test both directions

  • View-Based Compatibility

  • Create view with old column names
  • Migrate to new tables behind view
  • Drop view after transition

Complete backward compatibility guide

CYCLE 5: Multi-Tenant Migrations (~3,000 lines)

Topic 9: Tenant-Scoped Migrations

What will be covered:

  • Multi-Tenant Database Strategies

1. Shared Database, Shared Schema (ATP Approach) - All tenants in same tables - TenantId column for isolation - Row-Level Security (RLS) for access control - Single migration affects all tenants

2. Shared Database, Schema Per Tenant - Schema per tenant (tenant_abc, tenant_xyz) - Migration runs for each schema - Parallel execution possible - Higher isolation but more complex

3. Database Per Tenant - Separate database instance per tenant - Migration runs for each database - Highest isolation but expensive - Not used in ATP (doesn't scale)

  • ATP Tenant Isolation During Migrations
  • Migrations affect all tenants simultaneously
  • Test with multi-tenant test data
  • Monitor per-tenant performance

  • Rollback affects all tenants

  • Tenant-Specific Data Migrations 

    [Migration(20241030110000, "Backfill classification for all tenants")]
public class BackfillClassification : Migration
{
    public override void Up()
    {
        // Process each tenant separately for monitoring
        Execute.Sql(@"
            UPDATE Events
            SET Classification = CASE
                WHEN Action LIKE '%Secret%' THEN 3  -- Secret
                WHEN Action LIKE '%Confidential%' THEN 2  -- Confidential
                ELSE 1  -- Internal
            END
            WHERE Classification IS NULL
              AND TenantId = @TenantId", 
            new { TenantId = "tenant-abc" });

        // Repeat for each tenant or use cursor
    }
}

Complete multi-tenant migration guide

Topic 10: Tenant Isolation and Safety

What will be covered:

  • Isolation During Migrations
  • Migrations cannot expose cross-tenant data
  • Validate TenantId constraints

  • Test multi-tenant scenarios

  • Tenant-Safe Data Migrations

  • Always include WHERE TenantId = ...
  • Validate no cross-tenant data leakage

  • Audit migration queries

  • Performance Per Tenant

  • Large migrations may impact specific tenants differently
  • Monitor per-tenant query latency
  • Stagger data migrations if needed

Complete tenant safety guide

CYCLE 6: Testing, Rollback & Recovery (~3,000 lines)

Topic 11: Migration Testing

What will be covered:

  • Migration Testing Strategies

1. Unit Tests (Migration Logic) 

[Fact]
public void Migration_AddEventStreamTable_CreatesTableSuccessfully()
{
    // Arrange
    var migration = new AddEventStreamTable();
    var context = new MigrationTestContext();

    // Act
    migration.Up();

    // Assert
    context.AssertTableExists("EventStreams");
    context.AssertColumnExists("EventStreams", "StreamId");
    context.AssertIndexExists("IX_EventStreams_TenantId_Status");
}

2. Integration Tests (Applied to Real Database) - Apply migration to test database - Verify schema changes - Verify data integrity - Test rollback (Down method)

3. Data Integrity Tests - Verify no data loss - Verify constraints enforced - Verify indexes improve performance - Verify multi-tenant isolation maintained

4. Rollback Tests - Apply migration - Apply rollback (Down) - Verify original state restored - Verify no data corruption

5. Performance Tests - Measure migration execution time - Monitor table locks - Validate online operations continue - Load test during migration

  • Test Database Setup
  • Docker container for ephemeral databases
  • Seed data for realistic testing
  • Multi-tenant test data
  • Automated cleanup

Code Examples: - Migration unit tests - Integration test suite - Rollback tests - Performance benchmarks

Diagrams: - Testing strategy pyramid - Test database lifecycle - Rollback validation flow

Deliverables: - Testing framework - Test templates - CI/CD test integration - Performance benchmarks

Topic 12: Rollback and Recovery

What will be covered:

  • Rollback Strategies

1. Down Migration (Code Rollback) - Execute Down() method - Reverses Up() changes - Safe for schema-only migrations - Risky for data migrations (data loss possible)

2. Point-in-Time Restore (Data Rollback) - Restore database from backup - Restore to timestamp before migration - Safe but requires downtime - Use for critical failures

3. Forward Fix (Preferred) - Create new migration to fix issue - Faster than rollback - No data loss - Requires quick diagnosis

  • Rollback Decision Matrix
Scenario Strategy Downtime Data Loss Risk
Schema bug (no data) Down migration None None
Data migration error Point-in-time restore Yes (minutes) Recent writes lost
Performance regression Forward fix or rollback None None
Critical data corruption Restore + replay events Yes (hours) Minimized
  • ATP Rollback Procedures
  • Always attempt forward fix first
  • Rollback as last resort
  • Require dual approval for production rollback
  • Complete incident report post-rollback

Code Examples: - Rollback execution code - Forward fix examples - Restore procedures - Incident templates

Diagrams: - Rollback decision tree - Restore workflow - Forward fix vs rollback - Recovery timeline

Deliverables: - Rollback runbooks - Decision matrix - Recovery procedures - Incident response guide

CYCLE 7: CI/CD Integration & Compliance (~2,500 lines)

Topic 13: CI/CD Pipeline Integration

What will be covered:

  • Migration in Azure Pipelines 

    stages:
  - stage: Migrate_Dev
    jobs:
      - job: RunMigrations
        steps:
          - task: DotNetCoreCLI@2
            inputs:
              command: 'run'
              projects: '**/ATP.*.Migrations.csproj'
              arguments: '--migrate --environment Dev'

          - task: PublishTestResults@2
            inputs:
              testResultsFormat: 'VSTest'
              testResultsFiles: '**/migration-tests.trx'

  - stage: Migrate_Production
    dependsOn: Migrate_Dev
    condition: and(succeeded(), eq(variables['Build.SourceBranch'], 'refs/heads/main'))
    jobs:
      - deployment: RunProductionMigrations
        environment: Production
        strategy:
          runOnce:
            deploy:
              steps:
                - task: AzureCLI@2
                  inputs:
                    scriptType: 'ps'
                    scriptLocation: 'inlineScript'
                    inlineScript: |
                      # Run migrations
                      dotnet run --migrate --environment Production

  • Migration Validation Gates

  • Lint migration code (coding standards)
  • Build migration project
  • Run migration tests
  • Validate backward compatibility
  • Security scan (SQL injection check)

  • Approve for production (manual gate)

  • Automated vs Manual Execution

  • Dev/Test: Automatic execution
  • Staging: Automatic with validation
  • Production: Manual approval required

Complete CI/CD integration guide

Topic 14: Compliance and Audit

What will be covered:

  • Migration Audit Trail
  • All migrations logged to Azure Monitor
  • Log includes: Version, timestamp, executor, duration, result

  • Immutable audit log (7-year retention)

  • Change Approval Workflow

  • Non-breaking migrations: Team lead approval
  • Breaking migrations: Architect + DBA approval

  • Production migrations: CAB approval

  • Compliance Evidence

  • Migration history for SOC 2 audits
  • Change management documentation
  • Approval records
  • Rollback procedures

Complete compliance documentation

Summary & Implementation Plan

Implementation Phases

Phase 1: Foundations (Cycle 1) - 2 weeks - Fundamentals and strategy

Phase 2: Tools (Cycles 2-3) - 5 weeks - FluentMigrator and EF Core

Phase 3: Advanced (Cycle 4) - 3 weeks - Zero-downtime patterns

Phase 4: Multi-Tenant (Cycle 5) - 3 weeks - Tenant-scoped migrations

Phase 5: Quality (Cycle 6) - 3 weeks - Testing and rollback

Phase 6: Operations (Cycle 7) - 2 weeks - CI/CD and compliance

Success Metrics

  • Zero-Downtime: 100% migrations without service interruption
  • Rollback Safety: 100% migrations have tested Down methods
  • Test Coverage: 100% migrations have integration tests
  • Automation: >90% migrations automated in CI/CD
  • Compliance: 100% migrations with audit trail
  • Performance: <5 minutes for typical schema migrations

Document Status: ✅ Plan Approved - Ready for Content Generation

Target Start Date: Q1 2026

Expected Completion: Q2 2026 (18 weeks)

Owner: Backend Engineering Team

Last Updated: 2024-10-30