ScadaBridge/docs/components/ConfigurationDatabase.md

Configuration Database

The Configuration Database component is the exclusive EF Core data-access layer for the central MS SQL configuration store. It owns the ScadaBridgeDbContext, every IEntityTypeConfiguration<T> Fluent mapping, all repository implementations, the IAuditService and IAuditCorrelationContext implementations, the AuditLogPartitionMaintenance service, and the EF Core migration history. No other component references EF Core or touches the configuration database directly.

Overview

The component lives in src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase/ and is central-only — site clusters never load it. Its responsibilities break down into four areas:

• DbContext + Fluent mappings — ScadaBridgeDbContext maps ~30 Commons POCO entity types to SQL Server using IEntityTypeConfiguration<T> classes in Configurations/, registered wholesale via modelBuilder.ApplyConfigurationsFromAssembly(...).
• Repository implementations — eleven scoped repositories implement the interfaces declared in Commons, covering every domain area from template authoring to audit log ingest.
• Config-change audit — AuditService implements IAuditService, staging an AuditLogEntry into the change tracker so it commits atomically with the entity change; AuditCorrelationContext threads a BundleImportId through AsyncLocal<T> so bundle-import audit rows are correlated without cross-contaminating concurrent import sessions.
• Partition maintenance — AuditLogPartitionMaintenance implements IPartitionMaintenance, rolling pf_AuditLog_Month forward by issuing ALTER PARTITION FUNCTION … SPLIT RANGE for each missing future monthly boundary.

The single DI entry point is ServiceCollectionExtensions.AddConfigurationDatabase(string connectionString).

Key Concepts

Persistence-ignorant Commons entities

POCO entity classes and repository interfaces are declared in Commons and are entirely free of EF Core attributes. All EF knowledge — column types, max-lengths, indexes, value converters, relationships — lives in the Configurations/ classes here. Consuming components depend on Commons types only; they never reference this project or EF Core directly.

Secret-column encryption

Three columns carry authentication secrets: SmtpConfiguration.Credentials, ExternalSystemDefinition.AuthConfiguration, and DatabaseConnectionDefinition.ConnectionString. Each uses EncryptedStringConverter, an EF ValueConverter<string?, string?> that wraps ASP.NET Data Protection. The protector is purpose-scoped to "ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.EncryptedColumn" and its key ring is persisted to the database itself (via IDataProtectionKeyContext), so both central nodes share one key ring and can read each other's writes.

ScadaBridgeDbContext accepts two constructors: the (DbContextOptions) single-argument form used by design-time EF tooling, and the (DbContextOptions, IDataProtectionProvider) form used at runtime. The runtime form encrypts; the design-time form substitutes a SchemaOnlyDataProtector that produces the same column schema but throws InvalidOperationException on any actual read or write, preventing silent encryption with a throwaway key. AddConfigurationDatabase always registers the runtime overload:

// ServiceCollectionExtensions.AddConfigurationDatabase (excerpt)
services.AddScoped(serviceProvider =>
{
    var options = serviceProvider.GetRequiredService<DbContextOptions<ScadaBridgeDbContext>>();
    var protectionProvider = serviceProvider.GetRequiredService<IDataProtectionProvider>();
    return new ScadaBridgeDbContext(options, protectionProvider);
});

services.AddDataProtection()
    .PersistKeysToDbContext<ScadaBridgeDbContext>();

A SecretAwareModelCacheKeyFactory folds HasSecretEncryptionProvider into the EF model cache key so a provider-bearing and a schema-only context never share a cached model.

Append-only AuditLog and DB-role enforcement

The central dbo.AuditLog table has two dedicated SQL Server roles:

Role	Grants
scadabridge_audit_writer	INSERT, SELECT on AuditLog only — no UPDATE, no DELETE
scadabridge_audit_purger	ALTER ON SCHEMA::dbo (required for SPLIT RANGE and partition switch-out)

Row-level DELETE on AuditLog is not granted even to the purge role; retention is always a partition switch, never a row delete.

Architecture

DbContext

ScadaBridgeDbContext exposes one DbSet<T> per mapped entity — templates, instances, sites, data connections, external systems, notifications, shared scripts, security mappings, deployment records, API methods, AuditLogEntry, AuditLogRow (the dbo.AuditLog persistence shape), SiteCall, and DataProtectionKey. OnModelCreating delegates all mapping to the Configurations/ assembly scan, then applies secret-column encryption and strips computed-column SQL for non-SQL-Server providers (so integration tests using SQLite can still call EnsureCreated).

Fluent API entity configurations

Each entity has its own IEntityTypeConfiguration<T> in Configurations/. Representative examples:

AuditLogEntityTypeConfiguration maps AuditLogRow to dbo.AuditLog. The table carries ten writable canonical columns plus five persisted computed columns derived from DetailsJson via JSON_VALUE … PERSISTED (Kind, Status, SourceSiteId, ExecutionId, ParentExecutionId) and one additional non-persisted computed column IngestedAtUtc (SWITCHOFFSET-based; SQL Server forbids PERSISTED on a non-deterministic expression). EF is configured with ValueGeneratedOnAddOrUpdate() and no write for the computed columns; the repository writes only the ten canonical columns and lets SQL Server derive the rest:

// AuditLogEntityTypeConfiguration (excerpt)
builder.Property(e => e.Kind)
    .HasConversion<string>()
    .HasMaxLength(32)
    .IsUnicode(false)
    .HasComputedColumnSql("JSON_VALUE(DetailsJson,'$.kind')", stored: true)
    .ValueGeneratedOnAddOrUpdate()
    .IsRequired();

builder.Property(e => e.ExecutionId)
    .HasComputedColumnSql(
        "CAST(JSON_VALUE(DetailsJson,'$.executionId') AS uniqueidentifier)", stored: true)
    .ValueGeneratedOnAddOrUpdate();

// Composite PK includes OccurredAtUtc for partition alignment
builder.HasKey(e => new { e.EventId, e.OccurredAtUtc });

builder.HasIndex(e => e.EventId).IsUnique()
    .HasDatabaseName("UX_AuditLog_EventId");

TemplateConfiguration (representative of the domain-area configs) sets up the self-referencing parent FK, folder FK, cascade-delete relationships to attributes/alarms/scripts/compositions/native alarm sources, and the filtered unique index that enforces name uniqueness only on non-derived (base) templates.

SiteCallEntityTypeConfiguration maps SiteCall to dbo.SiteCalls with a TrackedOperationId PK stored as varchar(36) (GUID in "D" format) so the column shape matches the wire format and the site SQLite store — one consistent format for operational debugging.

Repository implementations

All eleven repositories follow the same shape: they take ScadaBridgeDbContext by constructor injection, work with Commons POCO types, and never commit — callers invoke SaveChangesAsync() to commit the unit of work.

AuditLogRepository is the most specialized. Its InsertIfNotExistsAsync bypasses the change tracker and issues raw interpolated SQL because the computed columns must not appear in the INSERT column list:

// AuditLogRepository.InsertIfNotExistsAsync (excerpt)
await _context.Database.ExecuteSqlInterpolatedAsync(
    $@"IF NOT EXISTS (SELECT 1 FROM dbo.AuditLog WHERE EventId = {evt.EventId})
INSERT INTO dbo.AuditLog
    (EventId, OccurredAtUtc, Actor, Action, Outcome, Category, Target, SourceNode, CorrelationId, DetailsJson)
VALUES
    ({evt.EventId}, {occurred}, {actor}, {evt.Action}, {outcome}, {category}, {evt.Target}, {evt.SourceNode}, {evt.CorrelationId}, {evt.DetailsJson});",
    ct);

FormattableString interpolation parameterises every value so there is no injection surface. SQL error numbers 2601 and 2627 (unique-index violation) are swallowed as no-ops because the IF NOT EXISTS check has a race window; both the check-loser and the retrying telemetry path are semantically correct duplicates.

QueryAsync builds LINQ predicates over AuditLogRow using AsNoTracking(), translating filter dimensions (Channels, Kinds, Statuses, SourceSiteIds, SourceNodes, ExecutionId, ParentExecutionId, time range) to server-side SQL IN/equality predicates and using keyset pagination on (OccurredAtUtc DESC, EventId DESC).

GetExecutionTreeAsync walks the ParentExecutionId graph in two phases: a loop climbs to the root (bounded at 32 levels), then a recursive CTE descends the full tree and LEFT JOINs back to AuditLog so stub nodes (purged or row-less executions) still appear with RowCount = 0.

SwitchOutPartitionAsync executes a drop-and-rebuild dance — dropping UX_AuditLog_EventId, creating a byte-identical staging table (including the computed-column definitions), switching the target partition to staging, dropping staging, and rebuilding the unique index — all inside a single BEGIN TRY / BEGIN CATCH block that guarantees the index is present whether the switch succeeds or rolls back.

IAuditService — config-change audit

AuditService implements IAuditService, called by consuming components after each successful entity mutation. It constructs an AuditLogEntry with Timestamp = DateTimeOffset.UtcNow, serialises afterState to JSON tolerating reference cycles and capping depth at 32 to avoid unbounded payloads, stamps BundleImportId from the active IAuditCorrelationContext, and adds the entry to the change tracker only — the caller's SaveChangesAsync() commits the entry and the entity change atomically:

// AuditService.LogAsync (excerpt)
var entry = new AuditLogEntry(user, action, entityType, entityId, entityName)
{
    Timestamp = DateTimeOffset.UtcNow,
    AfterStateJson = afterState != null ? SerializeAfterState(afterState) : null,
    BundleImportId = _correlationContext.BundleImportId
};

await _context.AuditLogEntries.AddAsync(entry, cancellationToken);

AuditCorrelationContext backs BundleImportId with AsyncLocal<Guid?> so each logical call chain — each distinct bundle import invocation — carries its own value even when two imports share a DI scope. It is registered as scoped (to participate in the DI graph) but its in-memory state is per-call-context.

Partition maintenance

AuditLogPartitionMaintenance implements IPartitionMaintenance. On each tick (driven by the AuditLogPartitionMaintenanceService hosted service in the Audit Log component) it reads the current max boundary from sys.partition_range_values, then issues ALTER PARTITION SCHEME … NEXT USED followed by ALTER PARTITION FUNCTION … SPLIT RANGE for each missing month up to the lookahead horizon. The NEXT USED re-issue before every SPLIT is required because SQL Server consumes the flag after the first split. A SPLIT failure propagates (rather than being swallowed) so a failed month blocks subsequent months and the next tick retries from the same boundary — no partition holes.

Usage

Registration

The Host calls AddConfigurationDatabase once, passing the ScadaBridge:Database:ConfigurationDb connection string:

// Host composition root (excerpt)
services.AddConfigurationDatabase(
    configuration["ScadaBridge:Database:ConfigurationDb"]!);

This registers ScadaBridgeDbContext as scoped (with the runtime encryption overload), all eleven repository interfaces bound to their implementations, IAuditCorrelationContext → AuditCorrelationContext, IAuditService → AuditService, IInstanceLocator → InstanceLocator, IPartitionMaintenance → AuditLogPartitionMaintenance, and the Data Protection key ring persisted to the database.

The obsolete zero-argument overload throws InvalidOperationException at startup (marked error: true on the [Obsolete] attribute) so a misconfigured host fails fast with a clear message rather than silently producing an empty DI registration.

Consuming a repository

Consuming components resolve the Commons interface through DI and never reference this project:

// Example: TemplateEngineRepository usage pattern
public class SomeManagementHandler
{
    private readonly ITemplateEngineRepository _repo;
    private readonly IAuditService _audit;

    public async Task CreateTemplateAsync(Template template, string user, CancellationToken ct)
    {
        await _repo.AddTemplateAsync(template, ct);
        await _audit.LogAsync(user, "Create", "Template",
            template.Id.ToString(), template.Name, template, ct);
        await _repo.SaveChangesAsync(ct);   // single transaction
    }
}

Repository Add/Update/Delete calls only stage changes on the change tracker. SaveChangesAsync on the context (exposed via the repository or accessed directly) is the unit-of-work commit.

Migration management

MigrationHelper.ApplyOrValidateMigrationsAsync is called at startup after the ScadaBridgeDbContext is resolved. It first polls CanConnectAsync in a 2-second interval for up to 60 seconds (handling MSSQL container recovery lag), then:

• Development (isDevelopment = true): calls dbContext.Database.MigrateAsync() to auto-apply all pending migrations.
• Production (isDevelopment = false): calls GetPendingMigrationsAsync() and throws InvalidOperationException listing the pending migration names if any are outstanding. The host does not start until the schema is current.

Design-time tooling uses DesignTimeDbContextFactory, which reads the connection string from ScadaBridge:Database:ConfigurationDb in the Host's appsettings.json or from the SCADABRIDGE_DESIGNTIME_CONNECTIONSTRING environment variable. No hardcoded fallback exists — a missing connection string fails with an actionable message.

To generate production SQL scripts:

# All pending migrations as an idempotent script
dotnet ef migrations script --idempotent \
  --project src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase \
  --output migration.sql

# From a specific migration to another
dotnet ef migrations script FromMigration ToMigration \
  --project src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase \
  --output migration.sql

Configuration

The connection string is the only configuration this component reads directly. It is injected as a constructor argument to AddConfigurationDatabase and sourced from the Host options:

Key	Notes
ScadaBridge:Database:ConfigurationDb	SQL Server connection string. Required; startup fails without it.

The SCADABRIDGE_DESIGNTIME_CONNECTIONSTRING environment variable is an alternative source for dotnet ef tooling only.

Dependencies & Interactions

• Commons (#16) — all POCO entity classes (Templates, Instances, Sites, AuditLogEntry, SiteCall, …) and all repository interfaces (ITemplateEngineRepository, IDeploymentManagerRepository, ISecurityRepository, IInboundApiRepository, IExternalSystemRepository, INotificationRepository, INotificationOutboxRepository, ISiteCallAuditRepository, IAuditLogRepository, ICentralUiRepository, ISiteRepository) live there. Commons also declares IAuditService, IAuditCorrelationContext, IPartitionMaintenance, and IInstanceLocator — all implemented here.
• Audit Log (#23) — IAuditLogRepository (implemented by AuditLogRepository) is the sole central write path for dbo.AuditLog. AuditLogIngestActor, CentralAuditWriter, and SiteAuditReconciliationActor all resolve it from a fresh per-message DI scope; the Audit Log component hosts the AuditLogPartitionMaintenanceService and AuditLogPurgeActor that drive the IPartitionMaintenance implementation registered here.
• Template Engine (#1) — consumes ITemplateEngineRepository for all template, attribute, alarm, native alarm source, script, composition, instance, override, connection binding, and area operations.
• Deployment Manager (#2) — consumes IDeploymentManagerRepository for deployment records and configuration snapshots.
• Security & Auth (#10) — consumes ISecurityRepository for LDAP group mappings and site scoping rules.
• Inbound API (#14) — consumes IInboundApiRepository for API method definitions.
• External System Gateway (#7) — consumes IExternalSystemRepository for external system and database connection definitions.
• Notification Service (#8) — consumes INotificationRepository for notification lists, recipients, and SMTP configuration.
• Notification Outbox (#21) — consumes INotificationOutboxRepository for dbo.Notifications ingest, dispatcher polling, status transitions, KPI queries, and bulk purge of terminal rows.
• Site Call Audit (#22) — consumes ISiteCallAuditRepository for dbo.SiteCalls ingest, KPI queries, and bulk purge of terminal rows.
• Central UI (#9) — consumes ICentralUiRepository for read-oriented cross-domain queries and the configuration audit log viewer.
• Host (#15) — provides the connection string, calls AddConfigurationDatabase, and invokes MigrationHelper.ApplyOrValidateMigrationsAsync at startup.
• All central components that modify configuration state — call IAuditService.LogAsync() and then SaveChangesAsync() so audit entries commit atomically with entity changes.
• Design spec: Component-ConfigurationDatabase.md

Troubleshooting

Startup fails with "Database schema is out of date"

The host is running in production mode and GetPendingMigrationsAsync found unapplied migrations. Generate the idempotent SQL script (dotnet ef migrations script --idempotent) and apply it via SSMS before restarting the host.

Startup stalls waiting for the database

MigrationHelper polls CanConnectAsync every 2 seconds for up to 60 seconds. If the 60-second deadline elapses the host throws InvalidOperationException naming the elapsed time and attempt count. Common causes: SQL Server container still in recovery, wrong connection string, database not yet attached.

"Failed to decrypt an encrypted configuration column"

EncryptedStringConverter.Unprotect caught a CryptographicException. The Data Protection key ring is unavailable (keys deleted or the database was restored from a backup without the key rows) or the row was written by a different key ring. Restore the DataProtectionKeys table rows from a backup or re-provision the key ring and re-encrypt the affected column values.

AuditLog partition switch fails mid-operation

SwitchOutPartitionAsync wraps the drop-and-rebuild dance in BEGIN TRY / BEGIN CATCH. On failure the CATCH block drops the staging table if it exists and rebuilds UX_AuditLog_EventId if it was dropped before the failure. The original exception is re-thrown so the Audit Log purge actor logs it and retries on the next daily tick. Verify that the scadabridge_audit_purger role still holds ALTER ON SCHEMA::dbo if the operation fails with a permissions error.

Design-time dotnet ef tooling cannot find a connection string

Set ScadaBridge:Database:ConfigurationDb in the Host's appsettings.json (the factory looks for ../ZB.MOM.WW.ScadaBridge.Host relative to the project directory) or export SCADABRIDGE_DESIGNTIME_CONNECTIONSTRING.

Related Documentation

• Configuration Database design specification
• Audit Log
• Notification Outbox
• Site Call Audit
• Commons