Schema Maintenance

BitzOrcas.SchemaMaintenance is a standalone operations executable. It does not start API Host DI; it collects compiled persistence metadata, constructs the SqlSugar SQL Server adapter, and performs drift check, SafeOnly script generation, migration apply, or Seed CSV length validation. It handles technical schema differences, not business-data migration or release approval.

Day-to-day deploys should run lock-safe --init-schema first (missing tables and nullable adds only). This tool and /host/schema own residual drift: widen, required add, existing-table indexes. The API Host loop operations-schema-drift-notify pushes the same full-catalog sniff to host-admin. A disposable local persist wipe uses AppHost BITZORCAS_ASPIRE_RESET_SCHEMA=true, not this tool. See Database initialization and migrations.

Five modes

Exactly one mode should be selected. Manual parsing lets a later mode overwrite an earlier one; runbooks must not rely on that implicit behavior.

Configuration and connection precedence

Mode, --force, --dry-run, and output are parsed from CLI only. Connection uses --connection first, then Configuration ConnectionStrings:Default, then SqlSugar:ConnectionString. Providers load appsettings, local, SCHEMA_ environment, and command line.

# Supply ConnectionStrings:Default through the environment provider.
export SCHEMA_ConnectionStrings__Default="$TARGET_SQLSERVER_CONNECTION"

# Read-only check; exit 2 means drift, not a crashed tool.
dotnet run --project src/Tooling/BitzOrcas.SchemaMaintenance -- \
  --check-schema

Source help mentions SCHEMA__ConnectionStrings__Default while provider prefix is SCHEMA_; verify the real environment-key contract in isolation before automation. A gitignored local settings file or explicit --connection (with shell-history caution) is more deterministic today.

Drift check

DeclaredMetadataCollector.CollectAll() supplies declared entities for comparison with target tables, columns, and indexes. Missing tables are blockers; column differences show declared/actual types and direction; missing indexes show name and columns.

# Preserve exit code and full report for migration review.
set +e
dotnet run --project src/Tooling/BitzOrcas.SchemaMaintenance -- \
  --check-schema > schema-drift.log 2>&1
rc=$?
set -e
test "$rc" -eq 0 -o "$rc" -eq 2

Code 0 means the current detector found no modeled difference. It does not prove every database object, trigger, permission, or data-quality rule. Code 2 is the expected “drift exists, not migrated” business result.

Generate a SafeOnly script

--generate-migration always uses MigrationSafetyLevel.SafeOnly. Without --output, SQL goes to stdout; with a path, File.WriteAllTextAsync replaces that file.

# Write a fresh evidence artifact and review it before database change control.
mkdir -p .verify-output/schema
dotnet run --project src/Tooling/BitzOrcas.SchemaMaintenance -- \
  --generate-migration \
  --output .verify-output/schema/safe-migration.sql

# Inspect statements and potentially disruptive operations.
sed -n '1,240p' .verify-output/schema/safe-migration.sql
rg -n 'DROP|ALTER COLUMN|NOT NULL|CREATE.*INDEX' \
  .verify-output/schema/safe-migration.sql

No safe script can mean clean schema or only confirm-required drift; read the drift report as well.

Apply safe migrations

--apply-safe-migrations generates and applies SafeOnly. Add --dry-run first to print category, description, and SQL without invoking the executor.

# Rehearse against a restored database on the production engine version.
dotnet run --project src/Tooling/BitzOrcas.SchemaMaintenance -- \
  --apply-safe-migrations --dry-run

# After approval, execute against the isolated target first.
dotnet run --project src/Tooling/BitzOrcas.SchemaMaintenance -- \
  --apply-safe-migrations

“Safe” is a generator classification, not zero downtime. Index creation and widening a large column can still lock, grow logs, and consume IO; production needs a window, monitoring, backup, and rollback plan.

Apply All and --force

--apply-all-migrations uses WithConfirm without --force and FullForce with it. There is no interactive per-statement confirmation; generator safety level and executor decide the final script.

# Always preview the FullForce candidate and retain it as evidence.
dotnet run --project src/Tooling/BitzOrcas.SchemaMaintenance -- \
  --apply-all-migrations --force --dry-run \
  > .verify-output/schema/full-force-preview.log

Never wire --apply-all-migrations --force directly into unattended production automation. Destructive shrink/drop work should use dedicated migrations, backfill, compatibility windows, and explicit rollback.

Offline Seed CSV validation

--validate-seed needs no connection string. It discovers seed assemblies, compares CSV values with declared metadata lengths, and reports files, rows, table/column, declared length, actual maximum, sample, and suggestion.

# Run after Seed Exporter candidates enter their owner Asset paths.
dotnet run --project src/Tooling/BitzOrcas.SchemaMaintenance -- \
  --validate-seed

The validator focuses on length. Passing does not prove business-key uniqueness, dependency completeness, PII/secret safety, or replay idempotency; SeedRunner and integration contracts own those facts.

Standalone versus Host

The tool manually constructs SqlSugarScope, fixes database type to SQL Server, and reads metadata from compiled assemblies. It does not reuse all API Host configuration, license, tenant middleware, or provider switch. Results apply to SqlSugar SQL Server schema maintenance, not a universal EF Core/Mongo migrator.

Build the same commit as the target release or declared metadata may represent another version. Record git rev-parse HEAD, SDK, target database/schema, and backup identity.

Exit codes

Apply failure also returns 99. Automation preserves full logs instead of interpreting every nonzero as drift.

Recommended production flow

1. Build Release tool from target deployment commit.
2. Check a restored production backup and retain report.
3. Generate SafeOnly script; DBA/owner reviews locks, capacity, compatibility.
4. Dry run, apply, and re-check on the restored copy.
5. Run application smoke, relevant ORM contracts, and data validation.
6. Create pre-production backup with RPO/RTO and rollback triggers.
7. Execute approved script/mode in a maintenance window with monitoring.
8. Re-check and archive commit, command, exit code, and report.

Review checklist

• One command selects one mode.
• Tool commit matches deployment version.
• Target is confirmed SqlSugar/SQL Server scope.
• Both check report and script are retained.
• --output cannot overwrite an unbacked approval artifact.
• FullForce is not an unattended production step.
• Large index/column operations have lock, capacity, rollback analysis.
• Seed validation is not overstated as complete seed proof.
• Post-apply check and application smoke pass.

See also

• Database migrations
• Seed Exporter
• Persistence
• Disaster recovery