Enterprise Database Migrations

SCRIPE employs a highly sophisticated, multi-provider database architecture. Rather than relying on a single monolith `DbContext` that behaves erratically across different SQL dialects, the platform utilizes strictly typed **Derived DbContexts**. This completely isolates `ModelSnapshot` files per database provider, allowing infinite scalability without EF Core migration collisions.

Derived DbContext Topology

At the core of a SCRIPE module lies an abstract base DbContext (e.g., `IdentityDbContext`). This base class contains all `DbSet` properties and business-level schema configurations. We then generate sealed derived classes for each supported provider: `SqlServerIdentityDbContext`, `OracleIdentityDbContext`, and `PostgreSqlIdentityDbContext`.

Runtime Provider Injection

Repositories exclusively reference the abstract base context, remaining completely agnostic to the underlying database engine. At initialization, the core infrastructure reads your `DatabaseProvider` flag from `appsettings.json` and dynamically registers the appropriate derived context via our proprietary `AddMultiProviderDatabase` extension.

// The AddMultiProviderDatabase extension method handles runtime resolution 
// based automatically on the appsettings.json "DatabaseProvider" flag.

services.AddMultiProviderDatabase<
    IdentityDbContext,                     // Base Abstract Context used by Repositories
    SqlServerIdentityDbContext,            // SQL Server Derived Context
    OracleIdentityDbContext,               // Oracle Derived Context
    PostgreSqlIdentityDbContext            // PostgreSQL Derived Context
>(configuration);

Generating Multi-Provider Migrations

The `scripe-cli` eliminates the tedious process of maintaining parallel migrations. With a single command, the CLI spawns child processes that generate distinct, dialect-perfect migrations for all supported providers simultaneously.

# All 3 providers at once (default)
$ scripe db add-migration Initial -m Identity

# Target a specific provider with -p
$ scripe db add-migration Initial -m Identity -p SqlServer

# Behind the scenes (all providers), the CLI executes:
# dotnet ef migrations add Initial -c SqlServerIdentityDbContext -o Migrations/SqlServer
# dotnet ef migrations add Initial -c OracleIdentityDbContext -o Migrations/Oracle
# dotnet ef migrations add Initial -c PostgreSqlIdentityDbContext -o Migrations/PostgreSql

Auto-Detecting Provider Updates

When applying migrations with `scripe db update`, the CLI automatically parses your backend `appsettings.json` to detect the currently active `DatabaseProvider`. It seamlessly executes the update against the correct database engine without requiring you to manually specify the provider flag. For edge cases, you can override this behavior using the `-p` parameter.

# Auto-detect provider from appsettings.json
$ scripe db update -m Identity

# Override with a specific provider
$ scripe db update -m Identity -p Oracle

# Output:
# [INFO] Auto-detected database provider: SqlServer from appsettings.json
# [INFO] > dotnet ef database update --project "..." --context SqlServerIdentityDbContext

Smart Force Removal

To rollback migrations safely and automatically across providers, use `scripe db remove-migration`. By parsing your active provider, the CLI safely reverts the migration logic and applies a standard removal check. Validated, it then aggressively cleans up inactive providers sequentially using aggressive force techniques, avoiding irrelevant connection timeouts and ensuring all branches are neatly reset simultaneously.

# Remove from all 3 providers (active first, then force for others)
$ scripe db remove-migration -m Identity

# Remove from a specific provider only (auto-applies --force if non-active)
$ scripe db remove-migration -m Identity -p SqlServer

# Output (all providers):
# [INFO] Auto-detected active provider: Oracle
# [INFO] Removing latest migration for active provider (Oracle)...
# [INFO] Force removing latest migration for SqlServer...
# [INFO] Force removing latest migration for PostgreSql...

Adding a New Database Engine

To introduce a new provider (e.g., SQLite for testing), follow the Clean Architecture extension pattern:

1. Create a new sealed derived context (e.g., `SqliteIdentityDbContext`).
2. Implement an `IDesignTimeDbContextFactory<T>` specifically for the new context.
3. Update `InfrastructureDI.cs` to include the new context in the provider registration array.
4. Execute `scripe db add-migration Initial -m Identity` to generate the initial snapshot.

public class SqliteIdentityDbContextFactory : BaseIdentityDbContextFactory<SqliteIdentityDbContext>
{
    protected override void ConfigureOptions(DbContextOptionsBuilder optionsBuilder, string connectionString)
    {
        optionsBuilder.UseSqlite(connectionString, o => o.MigrationsAssembly(typeof(SqliteIdentityDbContext).Assembly.GetName().Name));
    }
}