Concepts — Pragmatic.Migrations Architecture

Deep dive into how declarative schema migrations work: from compile-time metadata to runtime execution.

---

Overview

Pragmatic.Migrations eliminates migration files entirely. Instead of an imperative sequence of “add this column, drop that table”, you declare the desired state via [Entity] attributes and the framework computes what needs to change.

┌──────────────────────┐     ┌──────────────────────────────────────────────────┐
│   Compile Time       │     │   Runtime                                        │
│                      │     │                                                  │
│   Source Generator   │     │   Introspect DB                                  │
│   reads [Entity]     │────▶│   ↓                                              │
│   ↓                  │     │   SchemaDiffEngine.ComputeDiff(desired, current) │
│   SchemaVersion      │     │   ↓                                              │
│   (desired schema)   │     │   ISqlMigrationGenerator.GenerateScript(diff)    │
│                      │     │   ↓                                              │
│                      │     │   MigrationRunner.MigrateAsync(context)          │
└──────────────────────┘     └──────────────────────────────────────────────────┘

---

1. Schema Metadata (Compile Time)

SchemaVersion

The Source Generator produces a SchemaVersion record for each database in your topology:

public sealed record SchemaVersion(
    string Hash,                          // FNV-1a content hash
    ImmutableArray<TableSchema> Tables,   // All tables
    string? DatabaseName,                 // e.g. "ShowcaseAppDatabase"
    string? ProviderName,                 // e.g. "PostgreSql"
    string? ConfigKey);                   // e.g. "ConnectionStrings:App"

Each TableSchema contains:

public sealed record TableSchema(
    string TableName,
    ImmutableArray<ColumnSchema> Columns,
    ImmutableArray<IndexSchema> Indexes,
    ImmutableArray<ForeignKeySchema> ForeignKeys);

And each column carries full type information:

public sealed record ColumnSchema(
    string ColumnName,
    string ClrType,
    string? ProviderType,    // e.g. "varchar(256)", "timestamptz"
    bool IsNullable,
    bool IsPrimaryKey,
    string? DefaultValue,
    int? MaxLength);

Content Hash

The Hash field is an FNV-1a hash computed over all tables, columns, indexes, and foreign keys. This enables a fast path: if the introspected schema produces the same hash, the diff engine is skipped entirely.

Current hash == Desired hash → MigrationResult.NoChanges (no diff needed)

---

2. Schema Introspection (Runtime)

ISchemaIntrospector

Each provider implements ISchemaIntrospector to read the current database schema:

Provider	Strategy	System Tables
PostgreSQL	information_schema + pg_catalog	information_schema.tables, information_schema.columns, pg_indexes
SQL Server	sys.* views	sys.tables, sys.columns, sys.indexes, sys.foreign_keys
SQLite	sqlite_master + PRAGMA	sqlite_master, PRAGMA table_info, PRAGMA index_list

The introspector returns a SchemaVersion representing the current state. Framework-internal tables (__PragmaticSchema, __EFMigrationsHistory) are excluded automatically.

Provider Auto-Detection

You never specify the provider manually. The SG reads which EF Core provider package is referenced and sets SchemaVersion.ProviderName accordingly. At runtime, MigrationProviderFactory resolves the correct introspector, SQL generator, and connection factory via keyed DI services.

---

3. Diff Engine

SchemaDiffEngine

The ISchemaDiffEngine compares two SchemaVersion records and produces a SchemaDiff:

public sealed record SchemaDiff(
    ImmutableArray<SchemaChange> Changes,
    bool HasBreakingChanges,
    SchemaVersion? DesiredSchema);

Change Types

The diff engine produces an ordered list of SchemaChange subtypes:

Change Type	Description	Breaking?
CreateTable	New table with all columns, indexes, FKs	No
DropTable	Table removed from desired schema	Yes
AddColumn	New column on existing table	No
DropColumn	Column removed from desired schema	Yes
RenameColumn	Column name changed	No
AlterColumnType	Column type changed (e.g., int to bigint)	Depends
AlterColumnNullability	NULL to NOT NULL or vice versa	NOT NULL = risky
AddIndex	New index	No
DropIndex	Index removed	No
AddForeignKey	New FK constraint	No
DropForeignKey	FK removed	No
AlterColumnDefault	Default value changed	No

Ordering

Changes are emitted in dependency order:

1. CreateTable (no FK references first)
2. AddColumn
3. AlterColumn*
4. AddIndex
5. AddForeignKey (after both tables exist)
6. DropForeignKey (before dropping tables)
7. DropIndex
8. DropColumn
9. DropTable

This ordering ensures referential integrity is maintained throughout the migration.

Breaking Change Detection

A change is marked IsBreaking = true when it may cause data loss:

• DropTable — entire table and data removed
• DropColumn — column data lost
• AlterColumnNullability with NewIsNullable = false — may fail if NULLs exist
• AlterColumnType — narrowing conversions may truncate data

Breaking changes are blocked by default. The runner returns a failed MigrationResult with suggestions. Use Force = true to override.

At host startup, a failed MigrationResult aborts startup (fail-fast): the host never runs on a stale or partial schema. Configure the runner with UsePragmaticMigrations(m => m.Force()) to opt into breaking changes, or m.DryRun() to log the plan without applying it.

---

4. SQL Generation

ISqlMigrationGenerator

Each provider implements idempotent SQL generation:

PostgreSQL — Uses DO $$ ... $$ blocks:

DO $$ BEGIN
  IF NOT EXISTS (SELECT FROM pg_tables WHERE tablename = 'Reservations') THEN
    CREATE TABLE "Reservations" (
      "Id" uuid NOT NULL PRIMARY KEY,
      "GuestName" text NOT NULL,
      "CheckIn" date NOT NULL
    );
  END IF;
END $$;

SQL Server — Uses IF NOT EXISTS (sys.*) checks:

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Reservations')
CREATE TABLE Reservations (
    Id uniqueidentifier NOT NULL PRIMARY KEY,
    GuestName nvarchar(max) NOT NULL,
    CheckIn date NOT NULL
);

SQLite — Uses CREATE IF NOT EXISTS (partial idempotency):

CREATE TABLE IF NOT EXISTS Reservations (
    Id TEXT NOT NULL PRIMARY KEY,
    GuestName TEXT NOT NULL,
    CheckIn TEXT NOT NULL
);

SQLite has limited ALTER TABLE support. For column type changes, the generator uses the table-rebuild strategy (CREATE new, INSERT SELECT, DROP old, RENAME).

Per-Change Scripts

The runner does not execute the full script as a single batch. Instead, GenerateChangeScript(change) produces the SQL for each individual SchemaChange. This enables:

• Precise error reporting (“failed at step 3/7”)
• Per-change progress tracking
• Context-aware suggestions based on the failed change type

---

5. Migration Runner

Pipeline

MigrationRunner.MigrateAsync(context) executes this pipeline:

Phase 0: Open connection + acquire advisory lock (MigrationLock)
Phase 1: Introspect current schema
         → Fast path: if hashes match, skip to Phase 7
Phase 2: Compute diff (SchemaDiffEngine)
Phase 3: Generate SQL + safety check (breaking changes)
         → Dry run: return result without executing
Phase 4: Execute each change in a transaction
         → On failure: rollback + build error suggestions
Phase 5: Record in __PragmaticSchema audit table
Phase 6: Run seed providers (IMigrationSeedProvider)
Phase 7: Run data migrations (IDataMigration) in a dedicated transaction

Data Migrations

IDataMigration is a named, versioned data transformation — backfilling a column, converting values, moving data between tables. Each runs exactly once per database, tracked by Name in the __PragmaticDataMigrations table, inside a dedicated transaction after the schema phase. Data migrations run even when the schema diff is empty (the fast path skips straight to Phase 7), so a data-only change still applies. Implementations must enrol their commands in the supplied DbTransaction so the change is atomic with the tracking record. If a data migration throws, its transaction is rolled back and the migration result is failed.

Transaction Safety

All schema changes are executed inside a single database transaction. If any change fails:

1. The transaction is rolled back
2. The MigrationResult includes FailedChangeIndex, FailedChangeSql, and context-aware Suggestions
3. The database is left in its original state

Advisory Locking

Before introspection begins, the runner acquires an advisory lock via MigrationLock. This prevents concurrent migrations within the same process (e.g., parallel host starts in tests). The lock is released after the transaction completes.

This is separate from leader election, which prevents concurrent migrations across processes/instances.

Progress Streaming

The runner reports progress via IMigrationProgressStream. Each event includes:

• Phase — “analyzing”, “applying”, “complete”, “error”
• Message — Human-readable description
• ProgressPercent — 0-100 during execution
• DatabaseName — Which database is being migrated
• IsError / ErrorDetail — On failure

This integrates with the Control Plane: the host reports migration progress via heartbeats, visible on the hub.

---

6. Leader Election

The Problem

When multiple instances of the same host start simultaneously (e.g., Kubernetes rolling deploy), only one should run migrations. Without coordination, you get race conditions: duplicate table creation, conflicting ALTER statements, or deadlocks.

DatabaseLeaderElection

The default strategy uses the database itself for coordination — no external infrastructure (Redis, Consul, etcd) required.

It creates a __PragmaticLock table with a single row:

Column	Type	Description
LockName	varchar(128)	Always 'migration'
HolderId	varchar(64)	Guid7-based host identifier
AcquiredAt	timestamptz	When the lock was acquired
ExpiresAt	timestamptz	Auto-expiry (default: 5 minutes)

The acquisition query is atomic:

UPDATE "__PragmaticLock"
SET "HolderId" = @holderId,
    "AcquiredAt" = NOW(),
    "ExpiresAt" = NOW() + MAKE_INTERVAL(mins => @timeoutMinutes)
WHERE "LockName" = 'migration'
  AND ("HolderId" IS NULL OR "HolderId" = @holderId OR "ExpiresAt" < NOW())

This handles three cases:

1. No holder (HolderId IS NULL) — first instance wins
2. Same holder (HolderId = @holderId) — re-entrant, same instance retries
3. Expired (ExpiresAt < NOW()) — previous leader crashed, new one takes over

FallbackLeaderElection

Wraps DatabaseLeaderElection with a safety net. If the inner election throws (e.g., database not reachable for the lock table), it logs a warning and proceeds as leader. This ensures a single-instance deployment never fails to start.

AlwaysLeaderElection

Used when no connection factory is available (e.g., test/mock scenarios). Always grants leadership.

Follower Behavior

Non-leader instances call WaitForLeaderCompletionAsync(), which polls every 2 seconds:

while (!ct.IsCancellationRequested)
{
    await Task.Delay(2000, ct);
    // Check if HolderId is NULL (leader released) or expired
    var result = await cmd.ExecuteScalarAsync(ct);
    if (result is null or DBNull)
        return; // Leader completed, proceed
}

---

7. Tenant Migration Orchestration

TenantMigrationOrchestrator

For multi-tenant deployments with database-per-tenant, the orchestrator iterates all active tenants with dedicated connection strings:

1. Get active tenants from ITenantStore
2. Filter to tenants with dedicated databases (non-null ConnectionString)
3. For each tenant:
   a. Mark tenant as Migrating
   b. Run MigrationRunner.MigrateAsync with tenant's connection string
   c. On success: mark Active
   d. On failure: mark Suspended
4. Report progress to IHostStatus and IControlPlane

The orchestrator integrates with:

• IHostStatus — Reports HostState.Migrating with per-tenant progress
• IControlPlane — Pushes status updates to the hub for cluster visibility
• ITenantStore — Updates tenant state (Active/Migrating/Suspended)

Each tenant migration uses the standard MigrationRunner pipeline, including leader election per tenant database.

---

8. Observability

Structured Logging

All log messages use [LoggerMessage] source-generated methods in MigrationDiagnostics:

Event	Level	Message
Migration started	Information	[{DatabaseName}] Starting migration ({ProviderName}) -- desired hash: {DesiredHash}
Schema up to date	Information	[{DatabaseName}] Schema up to date -- no changes needed (hash: {Hash})
Diff computed	Information	[{DatabaseName}] Diff computed: {ChangeCount} changes ({BreakingCount} breaking)
Applying change	Debug	[{DatabaseName}] Applying {StepIndex}/{TotalSteps}: {ChangeDescription}
Migration complete	Information	[{DatabaseName}] Migration complete: {ChangeCount} changes applied in {DurationMs}ms
Migration failed	Error	[{DatabaseName}] Migration failed at step {StepIndex}/{TotalSteps}: {ChangeDescription}
Breaking blocked	Warning	[{DatabaseName}] Blocked: {BreakingCount} breaking changes require Force=true

OpenTelemetry

ActivitySource: Pragmatic.Migrations

Spans are created for each migration run with tags:

• db.name — Database name
• db.provider — Provider name
• migrations.change_count — Number of changes
• migrations.breaking_count — Number of breaking changes
• migrations.duration_ms — Total duration

Metrics

Metric	Type	Description
pragmatic.migrations.executed	Counter	Total migrations executed
pragmatic.migrations.failed	Counter	Failed migrations
pragmatic.migrations.changes_applied	Counter	Schema changes applied
pragmatic.migrations.duration	Histogram	Migration duration (ms)

---

9. Comparison with EF Core Migrations

Aspect	EF Core Migrations	Pragmatic.Migrations
Approach	Imperative (migration files)	Declarative (desired state)
Migration files	Required, one per change	None
Design-time tools	dotnet ef required	Not needed
ModelSnapshot	Required for diffing	Not needed (SG-generated)
Idempotency	Optional (--idempotent flag)	Always idempotent
Multi-database	Manual configuration	Auto-detected from topology
Transaction safety	Provider-dependent	Always (single transaction)
Error reporting	Generic exception	Per-change with suggestions
Breaking detection	None	Built-in, blocks by default
Leader election	Not included	Built-in (database-based)
Tenant orchestration	Manual	Built-in via TenantMigrationOrchestrator
Audit trail	Not included	__PragmaticSchema table
Data migrations	Hand-written SQL in migration files	First-class IDataMigration — named, tracked, run-once
Schema snapshot	ModelSnapshot.cs (diff input, drifts)	Committed schema/*.json (review artifact, generated)

---

Architecture Diagram

Pragmatic.Migrations/
├── Schema/              # Data model for desired/current schema
│   ├── SchemaVersion    # Top-level: hash + tables + provider
│   ├── TableSchema      # Table name + columns + indexes + FKs
│   ├── ColumnSchema     # Column name, type, nullable, PK, default
│   ├── IndexSchema      # Index name, columns, unique flag
│   └── ForeignKeySchema # FK name, columns, referenced table
├── Diff/                # Comparison engine
│   ├── SchemaDiffEngine # Compares desired vs current
│   ├── SchemaDiff       # Result: ordered SchemaChange[]
│   └── Changes/         # 12 SchemaChange subtypes
├── Introspection/       # Database schema readers
│   ├── ISchemaIntrospector
│   ├── PostgreSqlSchemaIntrospector
│   ├── SqlServerSchemaIntrospector
│   └── SqliteSchemaIntrospector
├── Sql/                 # SQL script generators
│   ├── ISqlMigrationGenerator
│   ├── SqlGeneratorBase
│   ├── PostgreSqlMigrationGenerator
│   ├── SqlServerMigrationGenerator
│   └── SqliteMigrationGenerator
├── Runner/              # Execution engine
│   ├── IMigrationRunner
│   ├── MigrationRunner          # Main pipeline
│   ├── MigrationResult          # Success/failure + suggestions
│   ├── MigrationOptions         # DryRun, Force, Timeout, Filter
│   ├── MigrationContext         # Connection string + schema + options
│   ├── MigrationLock            # Advisory lock (intra-process)
│   ├── MigrationResilience      # Retry on transient connection failures
│   ├── DatabaseLeaderElection   # Cross-process via __PragmaticLock
│   ├── FallbackLeaderElection   # Safety wrapper
│   └── MigrationDiagnostics     # Logging + OTel + Metrics
├── Tenant/              # Multi-tenant orchestration
│   ├── ITenantMigrationOrchestrator
│   ├── TenantMigrationOrchestrator
│   ├── TenantMigrationOptions
│   └── TenantMigrationSummary
├── Configuration/       # DI and builder
│   ├── MigrationsBuilder
│   └── MigrationProviderFactory
└── Extensions/          # IPragmaticBuilder extensions
    └── PragmaticBuilderMigrationsExtensions