Database Initialization and Migrations

BitzOrcas converges schema on three lanes. Do not fold them into one InitTables during deploy:

Empty databases still use compile-time metadata CodeFirst. Size-of-data or breaking changes on existing databases must be applied from scripts/database/migrations/ first. --init-schema will not widen columns.

Critical path diagram

Follow the main path to identify responsibility handoffs, then use the prose to inspect failure branches and evidence.

Command semantics

systemd deploy modes (deploy.sh): see deployment methods — BITZORCAS_DEPLOY_DB_MODE=schema-only|platform-seed|full-seed.

AppHost persist rebuild

dotnet run --project src/Hosts/BitzOrcas.AppHost -- --reset-schema does not reset the database. Arguments after -- stay on the Aspire AppHost process and never reach schema-initializer. To wipe a fast / persist library, stop the running AppHost and start once with the explicit switch:

# Drop business, audit-bucket, and CAP tables, then rebuild and reseed demo accounts.
BITZORCAS_ASPIRE_RESET_SCHEMA=true \
BITZORCAS_ASPIRE_SEED_DEMO=true \
dotnet run --project src/Hosts/BitzOrcas.AppHost --launch-profile fast

# Empty tables only: omit SEED_DEMO.
BITZORCAS_ASPIRE_RESET_SCHEMA=true \
dotnet run --project src/Hosts/BitzOrcas.AppHost --launch-profile fast

schema-initializer prints a destructive warning, then runs --reset-schema --force. Turn BITZORCAS_ASPIRE_RESET_SCHEMA off before the next daily start. Production / Staging AppHost exits immediately. Do not use this switch to widen columns; review /host/schema or scripts/database/migrations/ instead.

To rotate demo passwords while keeping business data, use BITZORCAS_ASPIRE_RESET_DEMO_PASSWORDS, not RESET_SCHEMA.

Recommended wrappers:

scripts/database/init-schema.sh
scripts/database/seed-demo.sh

The target database must already exist and the account needs table/index permissions.

Initialization order

1. Create business tables and indexes from the compile-time registry.
2. Create current audit time-bucket tables.
3. Idempotently create Outbox tables through CAP IStorageInitializer.
4. Run idempotent CSV seeds in ISeedStep.Order.

During normal Host start, CAP still creates the Outbox tables automatically. A one-shot schema command does not start hosted services, so --init-schema initializes CAP Outbox whether or not --no-seed is set (schema-only deploys stay ready). Future audit shards are created when their time bucket receives its first write.

On SQL Server, --init-schema probes the catalog and then follows the lock-safe plan:

Upgrading an existing database

Back up
  → inventory scripts/database/migrations (breaking / backfill / widen)
  → DBA applies unapplied files in name order
  → deploy.sh runs --init-schema --no-seed (missing tables + nullable adds only)
  → start API / JobHost
  → if Host-Admin gets “schema drift” inbox mail, open /host/schema
  → verify, then shift traffic

deploy.sh does not run scripts/database/migrations/. That folder is a DBA checklist.

Example 1: new table plus a nullable column

A release adds SysWidget and nullable SysUser.Region. Production:

BITZORCAS_DEPLOY_DB_MODE=schema-only \
  sudo /www/scripts/deploy.sh /tmp/app.zip Production

--init-schema --no-seed creates the new table (with its indexes) and runs:

SET DEADLOCK_PRIORITY LOW;
SET LOCK_TIMEOUT 5000;
ALTER TABLE [dbo].[SysUser] ADD [Region] NVARCHAR(256) NULL;

This is metadata-only on SQL Server. If the schema lock is not granted in 5 seconds, the command fails closed.

Example 2: widen SysModule.RequiredPermission from 100 to 2000

--init-schema will not alter the column. Seeds longer than 100 characters fail with SQL 2628. Do this instead:

1. After deploy, open /host/schema and preview the WidenLength review script.
2. Or run this idempotent statement on the tiny catalog table:

-- Tiny catalog table: re-run only when the column is still narrower than declared.
IF COL_LENGTH('dbo.SysModule', 'RequiredPermission') IS NOT NULL
   AND COL_LENGTH('dbo.SysModule', 'RequiredPermission') < 4000
BEGIN
    -- Permission codes exceed 100 characters; init-schema will not widen this column.
    ALTER TABLE dbo.SysModule
        ALTER COLUMN RequiredPermission nvarchar(2000) NULL;
END;

3. Then run --seed-only if seeds still need to land.

Example 3: missing index on a large existing table

--init-schema only records residual drift. The /host/schema review script on SQL Server looks like:

CREATE INDEX [IX_Orders_Region] ON [sales].[Orders]([Region])
    WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

Enterprise/Developer can build online. Standard edition should drop the WITH clause and use a maintenance window. Scripts abort when estimated rows exceed 100,000.

Never drop every declared index and recreate it. To replace one index: create a new name ONLINE, verify, then drop the old one.

Host-Admin notification after publish

The real sniff runs on API Host (SchemaDriftNotifyHostedService, Catalog name operations-schema-drift-notify) every 15 minutes by default. It uses the same full entity catalog and host-admin recipient port as /host/schema.

JobHost still registers the matching Quartz adapter so the Catalog/descriptor/executor triple stays complete. JobHost does not have the full model set or Authorization recipient port, so the notifier fail-softs instead of reporting a false-clean subset. Do not widen JobHost CompositionInclude just to make this job sniff.

• Clean catalog: stay silent.
• Residual drift: aggregated inbox to TenantId=0 host-admin members, link /host/schema.
• Fail-soft; API and JobHost stay healthy.
• Disable with Operations:SchemaDriftNotify:Enabled=false or BackgroundJobs:operations-schema-drift-notify:Enabled=false.
• You can also open /host/schema or run:

dotnet run --project src/Tooling/BitzOrcas.SchemaMaintenance -- --check-schema

Seed data

CSV seeds use Storageable upsert and are repeatable, but removing a CSV row does not automatically delete existing database data. Deletion and archival require explicit migration or operational action.

Exit codes

See also

• Demo data
• Persistence
• Upgrading

Current automation boundary

scripts/database/migrations/*.sql is a filename-ordered manual migration set. There is no general ledger or runner that records applied file, checksum, operator, and timestamp per environment. The controlled release process must retain that evidence; directory presence does not prove target execution.

The 22 current scripts mainly protect unified aggregates, owner rows, audit time types, and platform persistence. Related Architecture tests prove selected files remain, not that a database applied them.

Safe expand and contract

1. Expand with nullable columns, new tables, and compatible indexes.
2. Deploy code where old and new replicas share the expanded schema.
3. Backfill by tenant and stable key with resumable progress.
4. Switch reads and observe query latency plus data differences.
5. Contract only after the observation window.

-- ① A migration checks object state so a retry does not destroy data.
IF COL_LENGTH('dbo.SandboxNote', 'NormalizedName') IS NULL
BEGIN
    ALTER TABLE dbo.SandboxNote ADD NormalizedName nvarchar(200) NULL;
END;

-- ② Backfill and NOT NULL contraction belong to separate release batches.

This illustrates an expand shape and is not a currently pending repository script. A real migration documents provider, schema, locking, forward-fix/recovery, and data verification.

Before-and-after checks

• backup passes VERIFYONLY and a recent real restore drill exists;
• unapplied files have name, checksum, approval, and target environment;
• long transactions, locks, index space, and replication/AG impact are assessed;
• mixed API/JobHost versions work on the expand schema;
• backfill is tenant-scoped, resumable, and auditable;
• after traffic, reconcile counts, invariants, outbox, jobs, and key queries;
• contract waits until the observation period closes.

Planned gap

The platform needs a checksummed migration ledger, controlled runner, dry-run/plan, concurrency lock, and release report. Until delivered, deployment automation and DBAs own these controls; the manual must not imply the framework does.