Operations Schema Drift and Migration Safety

The Operations schema path is “detect now → generate SQL → optionally execute.” It can support development and controlled operations, but it has no immutable migration plan, preview hash, execution lease, or all-or-nothing transaction. Do not treat it as a full migration platform.

1. End-to-end flow

Every preview and apply recollects metadata, redetects the database, and regenerates SQL. Code, database, or composition changes between calls can change the script.

2. Query and command surface

Preview accepts FullForce, but the response has no plan ID, hash, expiry, or approval binding.

3. Declared metadata source

Handlers call ModuleAssemblyRegistry.GetRegistrations() and flatten each registration’s MetadataByType.Values. Detection uses compile-time ORM metadata from the current composition root, not runtime entity-attribute scanning.

A module absent from the registry contributes no declared tables. Always interpret drift against the selected composition profile.

4. Safety levels

Safe is a generator category, not a zero-lock or zero-downtime guarantee. Adding an index or defaulted column can still lock a large table and grow its transaction log.

5. Actual dry-run semantics

// ① Each request reads current metadata and current database drift.
var report = await detector.DetectAsync(declaredEntities, cancellationToken);
var script = generator.GenerateScript(report, MigrationSafetyLevel.SafeOnly);

// ② DryRun stops execution but stores no immutable plan identifier.
if (request.DryRun)
    return new MigrationResult(
        [script.ToSqlText()],
        script.Statements.Select(x => x.TableName).Distinct().ToList(),
        [$"[Dry-run] {script.Count} statements not executed"],
        clock.UtcNow);

Dry-run places the whole SQL text in the first item of an ExecutedSqls-shaped result. The field name must not be interpreted as proof of execution.

6. Approval differs by environment

Safe apply validates only when approvalGate.IsApprovalRequired. The API Host returns true in Production and Staging.

FullForce always calls ValidateAsync, but the Host adapter returns true immediately outside required environments. The effective behavior is still Production/Staging enforcement, not the “all environments” claim in source comments.

In a required environment with no IOpsExtensionStore, any non-empty ticket is accepted without state or intent validation.

7. FullForce Reason is optional

ApplyAllSchemaMigration.Command.Reason is nullable. The handler performs no blank validation and writes (not provided) to audit.

If destructive changes require justification, non-empty and length validation must become part of the request invariant and approval intent.

8. Why FullForce calls ApplySafeAsync

ISchemaMigrationExecutor exposes only ApplySafeAsync(MigrationScript). The FullForce handler uses it too. The executor does not validate the script level; it executes whatever it receives. Approval and risk classification live entirely upstream.

FullForce is not silently downgraded, but the method name creates false confidence. A commercial contract should use a neutral ApplyAsync and explicitly validate the allowed level and evidence.

9. Per-statement best effort

The SqlSugar executor loops through statements:

• add-index statements parse an index name and skip an existing index;
• each SQL command executes separately;
• exceptions become warning logs and SkippedStatements text;
• later statements continue;
• the method returns a successful Result<MigrationResult>.

HTTP or Result success therefore means the loop completed, not that every statement succeeded.

10. Correct result handling

var result = await mediator.Send(
    new ApplySafeSchemaMigration.Command(ticket, DryRun: false),
    cancellationToken);

if (result.IsFailure)
    return ReleaseDecision.Block(result.Error.Code);

var migration = result.Value!;

// ① A skipped string can mean idempotence or an execution exception.
if (migration.SkippedStatements.Any(x => x.Contains("failed", StringComparison.OrdinalIgnoreCase)))
    return ReleaseDecision.Block("A migration statement failed");

// ② Save executed SQL and tables, not merely HTTP 200.
return await evidence.RecordAsync(migration, cancellationToken);

Current skipped reasons are localized text instead of structured status, making automation brittle.

11. Transaction and concurrency boundaries

The executor wraps neither the whole script nor statement groups in a transaction. There is no deployment-wide lease. Two instances can detect and execute the same drift concurrently.

Only add-index has a dedicated pre-check. Other idempotence depends on database errors, which are converted to skipped text. Partial success is current behavior and requires manual or subsequent-script recovery.

12. Audit boundary

Safe and FullForce write an ActivityRecord only after the executor returns success. Because statement exceptions are converted to success, audit may say IsSuccess=true while failed statements appear in skipped text.

Empty scripts and dry-runs are not audited. Approval rejection and pre-execution exceptions have no dedicated failure audit. The record stores counts, tables, reason, and ticket correlation, not full SQL or before/after diff despite stronger comments.

13. HTTP reachability boundary

The four schema requests have [GenerateEndpoint] only; the handwritten Operations group does not map them. The current generator does not recurse into nested Query/Command types.

GA requires a real Host endpoint inventory and HTTP contract tests, not a source grep for attributes.

14. Minimum production runbook

1. Freeze the application build and database snapshot.
2. Capture drift and the target-level preview.
3. Review lock, log, truncation, and rollback impact per SQL statement.
4. Save an SQL hash and obtain independent approval.
5. Enter a single-instance or leased maintenance window.
6. Redetect; require new approval if the hash changed.
7. Execute and inspect structured status per statement.
8. Redetect and explain every remaining drift.
9. Save before/after, approval, actor, and database identity.
10. Rehearse partial-success recovery.

Steps 4–8 are not automatically bound by the current implementation.

15. Key GA gaps

• immutable plan, database fingerprint, SQL hash, and expiry;
• preview/approval/execution binding;
• database or deployment lease;
• structured per-statement status and failure policy;
• optional transactional grouping by DDL capability;
• required reason, SoD, and fail-closed approval;
• rejection, failure, dry-run, and empty-plan audit;
• real HTTP, SQL Server concurrency, and fault contracts.

16. Test and inspection commands

dotnet test tests/BitzOrcas.Application.Tests \
  --filter 'FullyQualifiedName~SchemaMigration|FullyQualifiedName~SchemaDrift'

# Commercial safety primitives should currently have no substantive implementation hit.
rg -n "PlanHash|DatabaseFingerprint|DistributedLock|MigrationLease" \
  src/Platform/Operations src/Framework -g '*.cs' \
  --glob '!**/bin/**' --glob '!**/obj/**'

Operations overview · Backup and archive