Session 04 - Data Governance & Migration

EF Core Migrations Best Practices

Migration Workflow

Commands cơ bản

bash
# Tạo migration
dotnet ef migrations add AddOrderStatus --project src/Infrastructure --startup-project src/Api

# Review SQL trước khi apply
dotnet ef migrations script --idempotent --output migration.sql

# Apply migration
dotnet ef database update

# Rollback
dotnet ef database update PreviousMigrationName

# List migrations
dotnet ef migrations list

Production Migration Strategy

csharp
// KHÔNG dùng Database.Migrate() trong production startup
// Thay vào đó, dùng migration bundles hoặc SQL scripts

// Migration bundle (EF Core 6+)
// dotnet ef migrations bundle --self-contained -r linux-x64

// Hoặc generate idempotent SQL script cho DBA review
// dotnet ef migrations script --idempotent > deploy/migration.sql

Migration Best Practices

Zero-Downtime Schema Changes

Expand-Contract Pattern

Thay đổi schema không breaking bằng cách chia thành 3 phases:

Ví dụ: Rename Column CustomerName → FullName

csharp
// Phase 1: EXPAND - Add new column (Deploy #1)
public partial class AddFullNameColumn : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Add new column, nullable vì data chưa có
        migrationBuilder.AddColumn<string>(
            name: "FullName",
            table: "Customers",
            type: "nvarchar(200)",
            nullable: true);

        // Copy existing data
        migrationBuilder.Sql(
            "UPDATE Customers SET FullName = CustomerName WHERE FullName IS NULL");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(name: "FullName", table: "Customers");
    }
}

// Application code: Write to BOTH columns, read from new
public class Customer
{
    public string FullName { get; set; }       // New column
    public string? CustomerName { get; set; }  // Old column - still maintained
}

// Phase 2: MIGRATE - Backfill + verify (Deploy #2)
// - Batch update remaining rows
// - Verify data consistency
// - Application reads only from FullName

// Phase 3: CONTRACT - Remove old column (Deploy #3)
public partial class RemoveCustomerNameColumn : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Make new column NOT NULL after all data migrated
        migrationBuilder.AlterColumn<string>(
            name: "FullName",
            table: "Customers",
            type: "nvarchar(200)",
            nullable: false,
            defaultValue: "");

        // Drop old column
        migrationBuilder.DropColumn(name: "CustomerName", table: "Customers");
    }
}

Dangerous Operations - Cách Handle

Online Index Creation (SQL Server)

sql
-- Online index - không lock table
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
ON Orders (CustomerId)
WITH (ONLINE = ON, SORT_IN_TEMPDB = ON, MAXDOP = 4);

Data Classification

PII & Sensitive Data Categories

EF Core - Data Protection Integration

csharp
// Attribute-based classification
public class Customer
{
    public int Id { get; set; }

    [PersonalData]
    [ProtectedPersonalData]  // Auto-encrypt in store
    public required string Email { get; set; }

    [PersonalData]
    public required string Name { get; set; }

    // Not personal data - no special handling
    public required string PreferredLanguage { get; set; }
}

// Custom value converter for encryption
public class EncryptedStringConverter : ValueConverter<string, string>
{
    public EncryptedStringConverter(IDataProtector protector)
        : base(
            v => protector.Protect(v),
            v => protector.Unprotect(v))
    { }
}

// Register in DbContext
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    var protector = _dataProtectionProvider
        .CreateProtector("CustomerData.v1");

    modelBuilder.Entity<Customer>(entity =>
    {
        entity.Property(e => e.Email)
            .HasConversion(new EncryptedStringConverter(protector));
    });
}

Data Retention Policies

Implementation Strategy

csharp
// Retention policy configuration
public class RetentionPolicy
{
    public required string EntityType { get; init; }
    public TimeSpan RetentionPeriod { get; init; }
    public RetentionAction Action { get; init; }
}

public enum RetentionAction
{
    Delete,         // Hard delete
    Anonymize,      // Replace PII with anonymous data
    Archive         // Move to cold storage
}

// Background service cho data retention
public class DataRetentionService : BackgroundService
{
    private readonly IServiceScopeFactory _scopeFactory;
    private readonly ILogger<DataRetentionService> _logger;

    private readonly RetentionPolicy[] _policies = new[]
    {
        new RetentionPolicy
        {
            EntityType = "ApplicationLogs",
            RetentionPeriod = TimeSpan.FromDays(90),
            Action = RetentionAction.Delete
        },
        new RetentionPolicy
        {
            EntityType = "UserSessions",
            RetentionPeriod = TimeSpan.FromDays(30),
            Action = RetentionAction.Delete
        },
        new RetentionPolicy
        {
            EntityType = "InactiveAccounts",
            RetentionPeriod = TimeSpan.FromDays(730), // 2 years
            Action = RetentionAction.Anonymize
        },
        new RetentionPolicy
        {
            EntityType = "CompletedOrders",
            RetentionPeriod = TimeSpan.FromDays(2555), // 7 years (tax)
            Action = RetentionAction.Archive
        }
    };

    protected override async Task ExecuteAsync(CancellationToken ct)
    {
        while (!ct.IsCancellationRequested)
        {
            foreach (var policy in _policies)
            {
                await ApplyRetentionPolicyAsync(policy, ct);
            }

            // Run daily at 2 AM
            await Task.Delay(TimeSpan.FromHours(24), ct);
        }
    }

    private async Task ApplyRetentionPolicyAsync(RetentionPolicy policy, CancellationToken ct)
    {
        using var scope = _scopeFactory.CreateScope();
        var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();

        var cutoffDate = DateTimeOffset.UtcNow - policy.RetentionPeriod;

        _logger.LogInformation(
            "Applying retention policy: {Entity} older than {Date}, Action={Action}",
            policy.EntityType, cutoffDate, policy.Action);

        // Process in batches to avoid memory/lock issues
        const int batchSize = 1000;
        // ... batch processing logic
    }
}

GDPR Right to Deletion

Deletion Pipeline

Database Versioning Strategies

Approaches

DbUp Example (alternative to EF Migrations)

csharp
// Simple, script-based database versioning
var upgrader = DeployChanges.To
    .SqlDatabase(connectionString)
    .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
    .WithTransaction()    // Each script in a transaction
    .LogToConsole()
    .Build();

var result = upgrader.PerformUpgrade();

if (!result.Successful)
{
    Console.ForegroundColor = ConsoleColor.Red;
    Console.WriteLine(result.Error);
    return -1;
}

Script Naming Convention

Scripts/
  V001__Create_Customers_Table.sql
  V002__Create_Orders_Table.sql
  V003__Add_CustomerEmail_Index.sql
  V004__Add_OrderStatus_Column.sql

Trade-offs