dohertj2/ScadaBridge
1
0
Fork 0
Code Issues 2 Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
ScadaBridge/docs/plans/2026-09-02-inbound-api-key-shared-store.md
T

42 KiB
Raw Permalink Blame History

Inbound API Key Shared SQL Server Store — Implementation Plan

For Claude: REQUIRED SUB-SKILL: Use superpowers-extended-cc:executing-plans to implement this plan task-by-task.

Goal: Give the shared ZB.MOM.WW.Auth.ApiKeys library a SQL Server provider, point both ScadaBridge central nodes at one zbauth schema in the configuration database, import each node's existing SQLite keys once, prove a central failover no longer 401s inbound callers on the docker rig, and retire the rig's file-copy workaround.

Architecture: Two repos, strictly ordered. First ~/Desktop/scadaproj/ZB.MOM.WW.Auth (0.2.1 → 0.3.0): an IApiKeyStoreMigrator abstraction, SQLite implementing it, and a new package ZB.MOM.WW.Auth.ApiKeys.SqlServer whose AddZbApiKeyAuthSqlServer registers SQL Server stores before AddZbApiKeyAuth so the library's TryAdd calls leave them in place (the Secrets 0.5.0 mechanism). Then ScadaBridge pins 0.3.0, adds ApiKeyStore:Provider, composes the SQL provider on central, runs a one-shot SQLite import at boot, and flips the docker rig. Design: docs/plans/2026-09-02-inbound-api-key-shared-store-design.md.

Tech Stack: .NET 10, Microsoft.Data.SqlClient 6.0.2 (family pin), xUnit + Skip.IfNot live gating, Gitea NuGet feed (build/push.sh), docker rig (docker/, MSSQL from infra/).

Facts the implementer must not re-derive (verified 2026-09-02):

  • Library layout: src/ZB.MOM.WW.Auth.Abstractions/ApiKeys/ApiKeyContracts.cs holds every contract (ApiKeyOptions, IApiKeyStore, IApiKeyAdminStore, IApiKeyAuditStore, records). SQLite lives in src/ZB.MOM.WW.Auth.ApiKeys/Sqlite/ (SqliteAuthSchema v3, SqliteAuthStoreMigrator, three stores, ScopeSerializer public, SqliteValueParsing internal). DI: DependencyInjection/ApiKeyServiceCollectionExtensions.cs + ApiKeyMigrationHostedService (takes SqliteAuthStoreMigrator concretely). Admin/ApiKeyAdminCommands ctor takes SqliteAuthStoreMigrator concretely — MxAccessGateway, HistorianGateway and ScadaBridge all construct it that way, so that ctor must survive.
  • Versioning is lockstep from Directory.Build.props (<Version>0.2.1</Version>); packages are packed to artifacts/ by build/pack.sh and pushed by build/push.sh (GITEA_NUGET_SOURCE, GITEA_NUGET_KEY). README.md carries the changelog and still says "current release is 0.1.4" — stale, fix it.
  • Secrets' SQL Server pattern to copy: ~/Desktop/scadaproj/ZB.MOM.WW.Secrets/src/ZB.MOM.WW.Secrets.Replicator.SqlServer/ — SecretsSqlServerConnectionFactory, SqlServerSecretsSchema (CREATE SCHEMA via EXEC, IF NOT EXISTS tables), SqlServerSecretsStoreMigrator (retry on 1205/2714/1913/2627, MaxMigrationRetries = 5), and the DI extension's "register before AddZbSecrets" comment. Live tests: tests/ZB.MOM.WW.Secrets.Replicator.SqlServer.Tests/Live/LiveSqlServerFixture.cs gated on SECRETS_SQLSERVER_CONNSTR.
  • HMAC-SHA256 hashes are 32 bytes (ApiKeySecretHasher.Hash). Scopes are serialised by ScopeSerializer.Serialize/Deserialize — reuse it, do not invent a second format.
  • ScadaBridge touchpoints: src/ZB.MOM.WW.ScadaBridge.Host/Program.cs:427-470 (store defaults + AddZbApiKeyAuth + ApiKeyAdminCommands composition), Directory.Packages.props:101-104 (four 0.2.1 pins), tests/ZB.MOM.WW.ScadaBridge.Security.Tests/LibraryInboundApiKeyAdminTests.cs, tests/ZB.MOM.WW.ScadaBridge.InboundAPI.Tests/EndpointExtensionsTests.cs:753-758 and EndpointContentTypeTests.cs:157 (construct ApiKeyAdminCommands with the SQLite migrator — keep compiling via the retained ctor).
  • Rig: both centrals reach scadabridge-mssql with ScadaBridge__Database__ConfigurationDb (env in docker/docker-compose.yml, user scadabridge_app, which already created the zbsecrets schema in ZbSecretsHub on 2026-08-07, so it holds DDL rights). Local MSSQL for library tests: Server=localhost,1433;User Id=sa;Password=<the infra/ MSSQL_SA_PASSWORD>;TrustServerCertificate=true (infra/docker-compose.yml).
  • Register row 108 already records the defect; row 107 is the unrelated DelmiaRecv item. Do not add a new row — close 108.
  • The executing session's docker/sync-inbound-api-keys.sh and the preflight in docker/verify-delmia-notifier.sh exist because of this defect; Task 14 removes the script and the preflight's dependency on it.

Model assignment: Opus for the library core (schema, migrator race handling, stores, importer) and the ScadaBridge Host composition; Sonnet for the abstraction plumbing, tests that mirror existing ones, config/docs; Opus for the rig verification; Fable (this session) for the final cross-repo review and the publish decision.

Part A — ~/Desktop/scadaproj/ZB.MOM.WW.Auth (library 0.3.0)

All Part A commands run from ~/Desktop/scadaproj/ZB.MOM.WW.Auth. Commit in the scadaproj repo (it is a plain directory of that repo, no nested .git).

Task 1: IApiKeyStoreMigrator abstraction + SQLite implements it

Classification: small Estimated implement time: ~4 min Parallelizable with: none (everything in Part A builds on it) Model: sonnet

Files:

  • Modify: src/ZB.MOM.WW.Auth.Abstractions/ApiKeys/ApiKeyContracts.cs (append)
  • Modify: src/ZB.MOM.WW.Auth.ApiKeys/Sqlite/SqliteAuthStoreMigrator.cs:14
  • Modify: src/ZB.MOM.WW.Auth.ApiKeys/DependencyInjection/ApiKeyMigrationHostedService.cs:13-14
  • Modify: src/ZB.MOM.WW.Auth.ApiKeys/DependencyInjection/ApiKeyServiceCollectionExtensions.cs:64-68
  • Modify: src/ZB.MOM.WW.Auth.ApiKeys/Admin/ApiKeyAdminCommands.cs:36-73
  • Test: tests/ZB.MOM.WW.Auth.ApiKeys.Tests/ApiKeyServiceCollectionExtensionsTests.cs

Step 1: Write the failing test (append to ApiKeyServiceCollectionExtensionsTests.cs, following the file's existing container-building helper)

    [Fact]
    public void AddZbApiKeyAuth_RegistersSqliteMigratorAsIApiKeyStoreMigrator()
    {
        using ServiceProvider sp = BuildProvider(); // the file's existing helper that calls AddZbApiKeyAuth over a temp SqlitePath
        Assert.IsType<SqliteAuthStoreMigrator>(sp.GetRequiredService<IApiKeyStoreMigrator>());
        Assert.Same(sp.GetRequiredService<SqliteAuthStoreMigrator>(), sp.GetRequiredService<IApiKeyStoreMigrator>());
    }

    [Fact]
    public void AddZbApiKeyAuth_LeavesPreRegisteredMigratorInPlace()
    {
        var services = new ServiceCollection();
        var fake = new FakeMigrator();
        services.AddSingleton<IApiKeyStoreMigrator>(fake);
        services.AddZbApiKeyAuth(MinimalConfig(), "ApiKeys"); // the file's existing config helper
        using ServiceProvider sp = services.BuildServiceProvider();
        Assert.Same(fake, sp.GetRequiredService<IApiKeyStoreMigrator>());
    }

    private sealed class FakeMigrator : IApiKeyStoreMigrator
    {
        public Task MigrateAsync(CancellationToken ct) => Task.CompletedTask;
    }

Step 2: Run to verify it fails

Run: dotnet test tests/ZB.MOM.WW.Auth.ApiKeys.Tests --filter "FullyQualifiedName~ApiKeyServiceCollectionExtensionsTests" 2>&1 | tail -5 Expected: build error — IApiKeyStoreMigrator does not exist.

Step 3: Implement

ApiKeyContracts.cs (append):

/// <summary>
/// Creates or upgrades the API-key store schema to the version this build supports. Each store
/// provider (SQLite, SQL Server) supplies one; hosts run it at startup and before admin commands.
/// Implementations must be idempotent and safe to run concurrently from several nodes.
/// </summary>
public interface IApiKeyStoreMigrator
{
    /// <summary>Migrates the store schema.</summary>
    /// <param name="ct">A token to cancel the operation.</param>
    /// <returns>A task that completes when the schema is current.</returns>
    Task MigrateAsync(CancellationToken ct);
}

SqliteAuthStoreMigrator.cs: public sealed class SqliteAuthStoreMigrator(AuthSqliteConnectionFactory connectionFactory) : IApiKeyStoreMigrator (add using ZB.MOM.WW.Auth.Abstractions.ApiKeys;).

ApiKeyMigrationHostedService.cs: change the primary-ctor parameter type to IApiKeyStoreMigrator migrator.

ApiKeyServiceCollectionExtensions.cs — replace the migrator block:

        // Migrator: the SQLite implementation is the default; a provider package that registers
        // IApiKeyStoreMigrator (and the three stores) BEFORE this call displaces it — that is the
        // whole mechanism by which AddZbApiKeyAuthSqlServer swaps the backend.
        services.TryAddSingleton(sp =>
            new SqliteAuthStoreMigrator(sp.GetRequiredService<AuthSqliteConnectionFactory>()));
        services.TryAddSingleton<IApiKeyStoreMigrator>(sp => sp.GetRequiredService<SqliteAuthStoreMigrator>());

ApiKeyAdminCommands.cs: field becomes private readonly IApiKeyStoreMigrator _migrator;; add a new ctor taking IApiKeyStoreMigrator migrator with the same body; the existing ctor taking SqliteAuthStoreMigrator stays and calls this(options, adminStore, auditStore, pepperProvider, (IApiKeyStoreMigrator)migrator, clock). XML-doc both.

Step 4: Run all library tests

Run: dotnet test ZB.MOM.WW.Auth.slnx 2>&1 | tail -5 Expected: Passed!, 0 failed (the two new tests included).

Step 5: Commit (in ~/Desktop/scadaproj)

git add ZB.MOM.WW.Auth/src ZB.MOM.WW.Auth/tests
git commit -m "feat(auth.apikeys): IApiKeyStoreMigrator abstraction — SQLite implements it, DI registers it via TryAdd so a provider registered first wins; ApiKeyAdminCommands accepts the interface (old ctor kept)"

Task 2: New package skeleton ZB.MOM.WW.Auth.ApiKeys.SqlServer + options + connection factory

Classification: small Estimated implement time: ~5 min Parallelizable with: none Model: sonnet

Files:

  • Create: src/ZB.MOM.WW.Auth.ApiKeys.SqlServer/ZB.MOM.WW.Auth.ApiKeys.SqlServer.csproj
  • Create: src/ZB.MOM.WW.Auth.ApiKeys.SqlServer/SqlServerApiKeyOptions.cs
  • Create: src/ZB.MOM.WW.Auth.ApiKeys.SqlServer/AuthSqlServerConnectionFactory.cs
  • Create: tests/ZB.MOM.WW.Auth.ApiKeys.SqlServer.Tests/ZB.MOM.WW.Auth.ApiKeys.SqlServer.Tests.csproj
  • Create: tests/ZB.MOM.WW.Auth.ApiKeys.SqlServer.Tests/SqlServerApiKeyOptionsTests.cs
  • Modify: ZB.MOM.WW.Auth.slnx (add both projects), Directory.Packages.props (add Microsoft.Data.SqlClient 6.0.2, Xunit.SkippableFact if the Secrets tests use it — check ~/Desktop/scadaproj/ZB.MOM.WW.Secrets/Directory.Packages.props and copy the exact package/version)

Step 1: csproj — copy src/ZB.MOM.WW.Auth.ApiKeys/ZB.MOM.WW.Auth.ApiKeys.csproj, then: PackageId ZB.MOM.WW.Auth.ApiKeys.SqlServer; Description "SQL Server API-key store for the ZB.MOM.WW SCADA family — one shared row set for clustered hosts (central pairs)."; ProjectReference to ..\ZB.MOM.WW.Auth.ApiKeys\ZB.MOM.WW.Auth.ApiKeys.csproj (brings Abstractions, ScopeSerializer, hasher); PackageReference Include="Microsoft.Data.SqlClient"; drop Microsoft.Data.Sqlite from this csproj (it is transitive). Same InternalsVisibleTo pattern for the new test project.

Step 2: Failing tests (SqlServerApiKeyOptionsTests.cs)

public sealed class SqlServerApiKeyOptionsTests
{
    [Fact]
    public void Validate_RejectsMissingConnectionString() =>
        Assert.Throws<InvalidOperationException>(() => new SqlServerApiKeyOptions { ConnectionString = " " }.Validate());

    [Fact]
    public void Validate_RejectsUnsafeSchemaName() =>
        Assert.Throws<InvalidOperationException>(() => new SqlServerApiKeyOptions { ConnectionString = "Server=x", Schema = "zb;auth" }.Validate());

    [Fact]
    public void Schema_DefaultsToZbauth() =>
        Assert.Equal("zbauth", new SqlServerApiKeyOptions().Schema);
}

Step 3: Implement

namespace ZB.MOM.WW.Auth.ApiKeys.SqlServer;

/// <summary>Options for the shared SQL Server API-key store (section supplied by the host, e.g. <c>ScadaBridge:InboundApi:ApiKeyStore:SqlServer</c>).</summary>
public sealed class SqlServerApiKeyOptions
{
    /// <summary>Full SQL Server connection string. Required. Never a <c>${secret:}</c> reference on hosts whose secret store is itself SQL-backed.</summary>
    public string ConnectionString { get; set; } = string.Empty;

    /// <summary>Schema that owns the three tables. Letters, digits and underscore only (it is interpolated into DDL).</summary>
    public string Schema { get; set; } = "zbauth";

    /// <summary>Fails fast on a blank connection string or an unsafe schema identifier.</summary>
    /// <exception cref="InvalidOperationException">The options are not usable.</exception>
    public void Validate()
    {
        if (string.IsNullOrWhiteSpace(ConnectionString))
            throw new InvalidOperationException("SQL Server API-key store requires a ConnectionString.");
        if (string.IsNullOrEmpty(Schema) || !Schema.All(c => char.IsAsciiLetterOrDigit(c) || c == '_') || Schema.Length > 64)
            throw new InvalidOperationException($"SQL Server API-key store schema name '{Schema}' is not a safe identifier.");
    }
}

AuthSqlServerConnectionFactory(SqlServerApiKeyOptions options) mirrors SecretsSqlServerConnectionFactory: Validate() in the ctor, OpenConnectionAsync(ct) returning an opened SqlConnection, and a Schema property.

Step 4: Run dotnet test tests/ZB.MOM.WW.Auth.ApiKeys.SqlServer.Tests → 3 passed. dotnet build ZB.MOM.WW.Auth.slnx → succeeded.

Step 5: Commit

git add ZB.MOM.WW.Auth
git commit -m "feat(auth.apikeys.sqlserver): package skeleton, options (schema default zbauth, validated) and connection factory"

Task 3: Schema + migrator (concurrent-boot safe)

Classification: high-risk (DDL run concurrently by both central nodes at every boot) Estimated implement time: ~5 min Parallelizable with: none Model: opus

Files:

  • Create: src/ZB.MOM.WW.Auth.ApiKeys.SqlServer/SqlServerAuthSchema.cs
  • Create: src/ZB.MOM.WW.Auth.ApiKeys.SqlServer/SqlServerApiKeyStoreMigrator.cs
  • Create: tests/ZB.MOM.WW.Auth.ApiKeys.SqlServer.Tests/Live/LiveSqlServerFixture.cs (copy of Secrets' fixture, env var AUTH_SQLSERVER_CONNSTR, creates a throwaway database zbauth_test_<random> per run and drops it on dispose)
  • Create: tests/ZB.MOM.WW.Auth.ApiKeys.SqlServer.Tests/Live/SqlServerApiKeyStoreMigratorLiveTests.cs

Step 1: Failing live tests (all [SkippableFact] + Skip.IfNot(LiveSqlServer.IsEnabled, …) exactly as Secrets does)

  • Migrate_OnEmptyDatabase_CreatesSchemaTablesAndVersionRow — asserts sys.schemas has zbauth, the three tables exist, schema_version.version == SqlServerAuthSchema.CurrentVersion.
  • Migrate_Twice_IsIdempotent.
  • Migrate_Concurrently_FromEightTasks_AllSucceed — Task.WhenAll of 8 migrators on a virgin database; this is the case that bit Secrets live.
  • Migrate_WhenStoredVersionIsNewer_Throws — write version = 99, expect AuthStoreMigrationException (reuse the existing exception type from SqliteAuthStoreMigrator.cs).

Step 2: Run with AUTH_SQLSERVER_CONNSTR="Server=localhost,1433;User Id=sa;Password=<the infra/ MSSQL_SA_PASSWORD>;TrustServerCertificate=true" (start infra/ first: docker compose -f ~/Desktop/ScadaBridge/infra/docker-compose.yml up -d scadabridge-mssql). Expected: compile failure (types missing). Without the variable the tests must report Skipped, not failed.

Step 3: Implement

SqlServerAuthSchema.CurrentVersion = 1. DDL ({schema} interpolated from the validated option only):

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'{schema}') EXEC(N'CREATE SCHEMA [{schema}]');
IF OBJECT_ID(N'[{schema}].[schema_version]', N'U') IS NULL
CREATE TABLE [{schema}].[schema_version] (
    id           INT            NOT NULL CONSTRAINT PK_{schema}_schema_version PRIMARY KEY CHECK (id = 1),
    version      INT            NOT NULL,
    applied_utc  DATETIMEOFFSET(7) NOT NULL);
IF OBJECT_ID(N'[{schema}].[api_keys]', N'U') IS NULL
CREATE TABLE [{schema}].[api_keys] (
    key_id        NVARCHAR(64)      NOT NULL CONSTRAINT PK_{schema}_api_keys PRIMARY KEY,
    key_prefix    NVARCHAR(16)      NOT NULL,
    secret_hash   VARBINARY(64)     NOT NULL,
    display_name  NVARCHAR(256)     NOT NULL,
    scopes        NVARCHAR(MAX)     NOT NULL,
    constraints   NVARCHAR(MAX)     NULL,
    created_utc   DATETIMEOFFSET(7) NOT NULL,
    last_used_utc DATETIMEOFFSET(7) NULL,
    revoked_utc   DATETIMEOFFSET(7) NULL,
    expires_utc   DATETIMEOFFSET(7) NULL);
IF OBJECT_ID(N'[{schema}].[api_key_audit]', N'U') IS NULL
CREATE TABLE [{schema}].[api_key_audit] (
    audit_id       BIGINT IDENTITY(1,1) NOT NULL CONSTRAINT PK_{schema}_api_key_audit PRIMARY KEY,
    key_id         NVARCHAR(64)      NULL,
    event_type     NVARCHAR(64)      NOT NULL,
    remote_address NVARCHAR(64)      NULL,
    created_utc    DATETIMEOFFSET(7) NOT NULL,
    details        NVARCHAR(MAX)     NULL);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'ix_api_keys_revoked_utc' AND object_id = OBJECT_ID(N'[{schema}].[api_keys]'))
    CREATE INDEX ix_api_keys_revoked_utc ON [{schema}].[api_keys] (revoked_utc);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'ix_api_key_audit_key_id_created_utc' AND object_id = OBJECT_ID(N'[{schema}].[api_key_audit]'))
    CREATE INDEX ix_api_key_audit_key_id_created_utc ON [{schema}].[api_key_audit] (key_id, created_utc);
MERGE [{schema}].[schema_version] AS t USING (SELECT 1 AS id) AS s ON t.id = s.id
    WHEN NOT MATCHED THEN INSERT (id, version, applied_utc) VALUES (1, @version, SYSDATETIMEOFFSET());

SqlServerApiKeyStoreMigrator : IApiKeyStoreMigrator — port SqlServerSecretsStoreMigrator including its retry loop and the four error numbers verbatim, its "stored version newer than supported → throw" check, and its MigrateOnceAsync structure (Serializable transaction). Keep the class comment that explains why the retry exists (cite the 2026-08-07 central-pair first boot).

Also ship the same DDL as a standalone file for DBAs: docs/sql/zbauth-schema.sql in the library repo (plain zbauth, @version replaced by the literal), referenced from the README.

Step 4: Run the live tests with the env var → 4 passed; without → 4 skipped. dotnet test ZB.MOM.WW.Auth.slnx still green.

Step 5: Commit

git commit -am "feat(auth.apikeys.sqlserver): zbauth schema v1 + concurrent-boot-safe migrator (retry on 1205/2714/1913/2627, newer-version fail-closed); live tests gated on AUTH_SQLSERVER_CONNSTR"

Task 4: The three SQL Server stores (contract tests shared with SQLite)

Classification: high-risk (auth data path) Estimated implement time: ~5 min per store — split into 4a store, 4b admin store, 4c audit store if the executor's clock says so Parallelizable with: none Model: opus

Files:

  • Create: src/ZB.MOM.WW.Auth.ApiKeys.SqlServer/SqlServerApiKeyStore.cs
  • Create: src/ZB.MOM.WW.Auth.ApiKeys.SqlServer/SqlServerApiKeyAdminStore.cs
  • Create: src/ZB.MOM.WW.Auth.ApiKeys.SqlServer/SqlServerApiKeyAuditStore.cs
  • Create: tests/ZB.MOM.WW.Auth.ApiKeys.SqlServer.Tests/Live/SqlServerApiKeyStoreLiveTests.cs, SqlServerApiKeyAdminStoreLiveTests.cs, SqlServerApiKeyAuditStoreLiveTests.cs

Step 1: Failing tests — port every [Fact] in tests/ZB.MOM.WW.Auth.ApiKeys.Tests/SqliteApiKeyStoreTests.cs and SqliteApiKeyAdminStoreTests.cs one-for-one to [SkippableFact]s against the live fixture (same names, same assertions, SampleRecord/AssertRecordEqual helpers copied). Add for the audit store: Append_ThenListRecent_ReturnsNewestFirstWithinLimit. Add one SQL-specific case: FindByKeyId_RoundTripsDateTimeOffsetToTheTick (SQLite stores text; SQL Server must not truncate to milliseconds).

Step 2: Run → compile failure.

Step 3: Implement — parameterised SqlCommands, one connection per operation via the factory, ConfigureAwait(false), ScopeSerializer for scopes, DateTimeOffset bound as SqlDbType.DateTimeOffset. Semantics must match SQLite exactly — read each SQLite method before porting:

  • FindActiveByKeyIdAsync = revoked_utc IS NULL (expiry is the verifier's job, not the store's).
  • MarkUsedAsync = UPDATE … SET last_used_utc = @when WHERE key_id = @id (no row → no-op).
  • CreateAsync = plain INSERT (a duplicate key_id throws, as SQLite does).
  • RevokeAsync/SetEnabledAsync(false) set revoked_utc = @when only when currently NULL and return whether a row changed; SetEnabledAsync(true) clears it. RotateAsync updates secret_hash only (preserves expires_utc). DeleteAsync returns rows-affected > 0. ListAsync ordered by created_utc, key_id.
  • Audit ListRecentAsync(limit) = SELECT TOP (@limit) … ORDER BY audit_id DESC.

Step 4: Run live tests → all pass; SQLite suite unchanged.

Step 5: Commit — feat(auth.apikeys.sqlserver): IApiKeyStore/IApiKeyAdminStore/IApiKeyAuditStore over SQL Server, semantics pinned by the SQLite contract tests re-run live

Task 5: One-shot SQLite → SQL Server importer

Classification: standard Estimated implement time: ~5 min Parallelizable with: Task 6 Model: opus

Files:

  • Create: src/ZB.MOM.WW.Auth.ApiKeys.SqlServer/SqliteApiKeyStoreImporter.cs
  • Create: tests/ZB.MOM.WW.Auth.ApiKeys.SqlServer.Tests/Live/SqliteApiKeyStoreImporterLiveTests.cs

Step 1: Failing tests

  • Import_CopiesKeysAndAuditRows_AndReturnsCounts — seed a temp SQLite via SqliteAuthStoreMigrator + SqliteApiKeyAdminStore (2 keys, one revoked, one with expiry) + 3 audit rows; import; assert SQL Server ListAsync shows both with identical fields and audit count 3.
  • Import_Twice_IsIdempotent — second run returns Inserted = 0, Skipped = 2.
  • Import_UnionsWithExistingRows — SQL already has key C; SQLite has A and B; result has A, B, C and no row of C was modified (compare last_used_utc).
  • Import_MissingFile_ReturnsZeroWithoutThrowing.

Step 2: Run → compile failure.

Step 3: Implement

/// <summary>Result of a one-shot import.</summary>
public sealed record ApiKeyImportResult(int KeysInserted, int KeysSkipped, int AuditInserted, int AuditSkipped, bool SourceFound);

/// <summary>
/// Copies every key and audit row from a per-node SQLite store into the shared SQL Server store,
/// insert-where-not-exists (keys by key_id; audit rows by key_id + event_type + created_utc), so it is
/// idempotent and every node of a pair may run it: the result is the UNION of the nodes' files. A row
/// that already exists in SQL Server is never modified — a revoke recorded on one node but not the
/// other therefore survives only if the revoked copy is imported first; operators reconcile by listing.
/// </summary>
public sealed class SqliteApiKeyStoreImporter(AuthSqlServerConnectionFactory sql)
{
    public async Task<ApiKeyImportResult> ImportAsync(string sqlitePath, CancellationToken ct) { … }
}

Read the SQLite side with a raw SqliteConnection (Mode=ReadOnly) and the column list from SqliteApiKeyStore, parse timestamps exactly like SqliteValueParsing (make that class internal + InternalsVisibleTo the SqlServer project, or duplicate the two-line parser — prefer InternalsVisibleTo). Insert with IF NOT EXISTS (SELECT 1 … WHERE key_id = @id) INSERT … per row inside one transaction.

Step 4: Run → 4 passed live. Step 5: Commit — feat(auth.apikeys.sqlserver): SqliteApiKeyStoreImporter — idempotent insert-where-not-exists copy of keys + audit (union across a pair)

Task 6: DI extension AddZbApiKeyAuthSqlServer + README/changelog + version 0.3.0

Classification: standard Estimated implement time: ~5 min Parallelizable with: Task 5 Model: sonnet

Files:

  • Create: src/ZB.MOM.WW.Auth.ApiKeys.SqlServer/DependencyInjection/SqlServerApiKeyServiceCollectionExtensions.cs
  • Test: tests/ZB.MOM.WW.Auth.ApiKeys.SqlServer.Tests/SqlServerApiKeyServiceCollectionExtensionsTests.cs (no live DB needed — resolves types only)
  • Modify: Directory.Build.props (0.2.1 → 0.3.0), README.md (Packages table row, Consumer Matrix column, "current release" line, new ### 0.3.0 changelog entry above ### 0.1.4)

Step 1: Failing test

[Fact]
public void AddZbApiKeyAuthSqlServer_DisplacesSqliteStoresAndMigrator()
{
    var cfg = new ConfigurationBuilder().AddInMemoryCollection(new Dictionary<string, string?> {
        ["ApiKeys:TokenPrefix"] = "sbk", ["ApiKeys:PepperSecretName"] = "Pepper", ["Pepper"] = "0123456789abcdef",
        ["ApiKeys:SqlServer:ConnectionString"] = "Server=unused;TrustServerCertificate=true" }).Build();
    var services = new ServiceCollection();
    services.AddZbApiKeyAuthSqlServer(cfg, "ApiKeys", "ApiKeys:SqlServer");
    using var sp = services.BuildServiceProvider();
    Assert.IsType<SqlServerApiKeyStore>(sp.GetRequiredService<IApiKeyStore>());
    Assert.IsType<SqlServerApiKeyAdminStore>(sp.GetRequiredService<IApiKeyAdminStore>());
    Assert.IsType<SqlServerApiKeyAuditStore>(sp.GetRequiredService<IApiKeyAuditStore>());
    Assert.IsType<SqlServerApiKeyStoreMigrator>(sp.GetRequiredService<IApiKeyStoreMigrator>());
    Assert.NotNull(sp.GetRequiredService<SqliteApiKeyStoreImporter>());
    Assert.NotNull(sp.GetRequiredService<IApiKeyVerifier>());
}

Step 3: Implement

public static IServiceCollection AddZbApiKeyAuthSqlServer(
    this IServiceCollection services, IConfiguration config, string apiKeySectionPath, string sqlServerSectionPath)
{
    services.Configure<SqlServerApiKeyOptions>(config.GetSection(sqlServerSectionPath));
    services.TryAddSingleton(sp => new AuthSqlServerConnectionFactory(sp.GetRequiredService<IOptions<SqlServerApiKeyOptions>>().Value));
    // Registered BEFORE AddZbApiKeyAuth so its TryAdd calls find these and leave them alone.
    services.TryAddSingleton<IApiKeyStore>(sp => new SqlServerApiKeyStore(sp.GetRequiredService<AuthSqlServerConnectionFactory>()));
    services.TryAddSingleton<IApiKeyAdminStore>(sp => new SqlServerApiKeyAdminStore(sp.GetRequiredService<AuthSqlServerConnectionFactory>()));
    services.TryAddSingleton<IApiKeyAuditStore>(sp => new SqlServerApiKeyAuditStore(sp.GetRequiredService<AuthSqlServerConnectionFactory>()));
    services.TryAddSingleton<SqlServerApiKeyStoreMigrator>();
    services.TryAddSingleton<IApiKeyStoreMigrator>(sp => sp.GetRequiredService<SqlServerApiKeyStoreMigrator>());
    services.TryAddSingleton<SqliteApiKeyStoreImporter>();
    return services.AddZbApiKeyAuth(config, apiKeySectionPath);
}

Note AddZbApiKeyAuth still registers AuthSqliteConnectionFactory from ApiKeyOptions.SqlitePath — harmless (lazy, never opened) and it keeps the importer's source path in one option. Document that in the extension's XML comment.

README changelog entry ### 0.3.0 — SQL Server API-key store (ScadaBridge central pair): the abstraction, the new package, AddZbApiKeyAuthSqlServer, the importer, "SQLite remains default; ApiKeyAdminCommands old ctor retained; consumers on SQLite need no code change to move to 0.3.0", and the docs/sql/zbauth-schema.sql pointer. Fix "current release is 0.1.4" → 0.3.0.

Step 4: Run dotnet test ZB.MOM.WW.Auth.slnx green; bash build/pack.sh produces five 0.3.0 nupkgs in artifacts/.

Step 5: Commit — feat(auth)!: 0.3.0 — ZB.MOM.WW.Auth.ApiKeys.SqlServer package (AddZbApiKeyAuthSqlServer, importer), README/changelog (the ! is for the new package/version, not an API break).

Task 7: Publish 0.3.0 to the Gitea feed + umbrella index

Classification: trivial (but outward-facing — confirm with the user before pushing) Estimated implement time: ~3 min Parallelizable with: none Model: sonnet (the push itself is the user's call)

Files:

  • Modify: ~/Desktop/scadaproj/CLAUDE.md (Auth row in the shared-libs table: feed carries 0.3.0, new ApiKeys.SqlServer package, ScadaBridge is its first consumer)

Steps: export GITEA_NUGET_SOURCE=… GITEA_NUGET_KEY=…; bash build/push.sh (needs the user's token — ask, do not guess). Verify: dotnet nuget list source then curl -s "$GITEA_NUGET_SOURCE" | head -c 200 and dotnet package search ZB.MOM.WW.Auth.ApiKeys.SqlServer --source dohertj2-gitea from the ScadaBridge checkout shows 0.3.0. Commit the index change in scadaproj.

Part B — ScadaBridge (~/Desktop/ScadaBridge)

Task 8: Pin 0.3.0 and add ApiKeyStore:Provider options

Classification: small Estimated implement time: ~4 min Parallelizable with: none Model: sonnet

Files:

  • Modify: Directory.Packages.props:101-104 (four pins → 0.3.0, add <PackageVersion Include="ZB.MOM.WW.Auth.ApiKeys.SqlServer" Version="0.3.0" />)
  • Modify: src/ZB.MOM.WW.ScadaBridge.Host/ZB.MOM.WW.ScadaBridge.Host.csproj (add <PackageReference Include="ZB.MOM.WW.Auth.ApiKeys.SqlServer" />)
  • Create: src/ZB.MOM.WW.ScadaBridge.Host/InboundApiKeyStoreOptions.cs
  • Test: tests/ZB.MOM.WW.ScadaBridge.Host.Tests/InboundApiKeyStoreOptionsTests.cs

Step 1: Failing tests — Provider_DefaultsToSqlite, Provider_ParsesCaseInsensitively("sqlserver" → SqlServer), Provider_RejectsUnknownValue (validation error names the two legal values).

Step 3: Implement

namespace ZB.MOM.WW.ScadaBridge.Host;

/// <summary>Which backend holds inbound API keys. Bound from <c>ScadaBridge:InboundApi:ApiKeyStore</c> alongside the library's own <c>ApiKeyOptions</c>.</summary>
public enum InboundApiKeyStoreProvider { Sqlite, SqlServer }

/// <summary>Host-side selector for the inbound API key store. <c>Sqlite</c> is per-node (single-node installs); <c>SqlServer</c> shares one row set across a central pair (design 2026-09-02).</summary>
public sealed class InboundApiKeyStoreOptions
{
    public const string SectionPath = "ScadaBridge:InboundApi:ApiKeyStore";
    public InboundApiKeyStoreProvider Provider { get; set; } = InboundApiKeyStoreProvider.Sqlite;
    /// <summary>SQLite file to import once when <see cref="Provider"/> is SqlServer; defaults to the library's <c>SqlitePath</c>. Renamed <c>*.imported</c> afterwards.</summary>
    public string? ImportFromSqlitePath { get; set; }
}

Bind with ValidateOnStart (enum binding already rejects unknown names; add a Validate that produces the friendly message).

Step 4: dotnet build ZB.MOM.WW.ScadaBridge.slnx (restores 0.3.0 — Task 7 must have pushed) + the new tests green. Step 5: Commit — chore(host): pin ZB.MOM.WW.Auth 0.3.0 + ApiKeys.SqlServer; InboundApiKeyStoreOptions (Provider Sqlite|SqlServer)

Task 9: Compose the provider in Program.cs + import hosted service

Classification: high-risk (auth composition on central) Estimated implement time: ~5 min Parallelizable with: none Model: opus

Files:

  • Modify: src/ZB.MOM.WW.ScadaBridge.Host/Program.cs:427-470
  • Create: src/ZB.MOM.WW.ScadaBridge.Host/InboundApiKeyStoreImportService.cs
  • Test: tests/ZB.MOM.WW.ScadaBridge.Host.Tests/CompositionRootTests.cs (extend the existing central-composition test: with Provider=SqlServer and a dummy connection string, IApiKeyStore resolves to SqlServerApiKeyStore and IApiKeyStoreMigrator to the SQL migrator; with the default, both resolve to the SQLite types)
  • Test: tests/ZB.MOM.WW.ScadaBridge.Host.Tests/InboundApiKeyStoreImportServiceTests.cs (fake importer: file present → ImportAsync called once and file renamed .imported; file absent → not called; Provider=Sqlite → service inert)

Step 3: Implement (Program.cs, replacing lines 447-450 and the ApiKeyAdminCommands migrator argument):

        var storeOptions = builder.Configuration.GetSection(InboundApiKeyStoreOptions.SectionPath).Get<InboundApiKeyStoreOptions>() ?? new();
        if (storeOptions.Provider == InboundApiKeyStoreProvider.SqlServer)
        {
            // Shared store for a central pair (design 2026-09-02). The connection string defaults to the
            // configuration database — the pair already shares it — under its own `zbauth` schema.
            const string sqlSection = apiKeyStoreSection + ":SqlServer";
            if (string.IsNullOrWhiteSpace(builder.Configuration[$"{sqlSection}:ConnectionString"]))
                builder.Configuration.AddInMemoryCollection(new Dictionary<string, string?>
                    { [$"{sqlSection}:ConnectionString"] = builder.Configuration["ScadaBridge:Database:ConfigurationDb"] });
            builder.Services.AddZbApiKeyAuthSqlServer(builder.Configuration, apiKeyStoreSection, sqlSection);
            builder.Services.AddHostedService<InboundApiKeyStoreImportService>();
        }
        else
        {
            builder.Services.AddZbApiKeyAuth(builder.Configuration, apiKeyStoreSection);
        }
        …
            sp.GetRequiredService<ZB.MOM.WW.Auth.Abstractions.ApiKeys.IApiKeyStoreMigrator>(),   // was SqliteAuthStoreMigrator

InboundApiKeyStoreImportService : IHostedService — registered after AddZbApiKeyAuthSqlServer so it starts after the library's migration hosted service (hosted services start in registration order); resolves SqliteApiKeyStoreImporter, IOptions<InboundApiKeyStoreOptions>, IOptions<ApiKeyOptions> (for the default path), ILogger. StartAsync: path = ImportFromSqlitePath ?? ApiKeyOptions.SqlitePath; if File.Exists → ImportAsync, log Information with the counts, File.Move(path, path + ".imported") (also move -wal/-shm if present). Any exception → log Error and continue (keys already in SQL keep working; a failed import must not take the node down). Keep the ${secret:} caveat from the docker compose comment: the connection string must be a literal or an env override, never a secret reference resolved by the SQL-backed secrets store (circularity).

Step 4: dotnet test tests/ZB.MOM.WW.ScadaBridge.Host.Tests green; dotnet test ZB.MOM.WW.ScadaBridge.slnx green (the InboundAPI and Security tests still construct ApiKeyAdminCommands with the SQLite migrator — the retained ctor keeps them compiling; do not rewrite them).

Step 5: Commit — feat(host): inbound API key store provider — SqlServer shares one zbauth row set across the central pair; one-shot SQLite import at boot (file renamed .imported)

Task 10: StartupValidator warning for a clustered central on the per-node store

Classification: small Estimated implement time: ~4 min Parallelizable with: Task 11, Task 12 Model: sonnet

Files:

  • Modify: src/ZB.MOM.WW.ScadaBridge.Host/StartupValidator.cs (near the existing inbound-API pepper check at ~line 80)
  • Test: tests/ZB.MOM.WW.ScadaBridge.Host.Tests/StartupValidatorTests.cs

Rule: Central role AND ScadaBridge:Cluster:SeedNodes (whatever key the validator already reads for the self-first check) lists ≥ 2 nodes AND Provider == Sqlite → Warning "Inbound API keys use the per-node SQLite store on a {n}-node central cluster; a failover will reject every inbound client until keys are re-issued. Set ScadaBridge:InboundApi:ApiKeyStore:Provider=SqlServer (design 2026-09-02)." Not an error (availability-first, like auto-down). Tests: warns for 2 seeds + Sqlite; silent for 1 seed; silent for SqlServer.

Commit — feat(host): StartupValidator warns when a clustered central keeps per-node inbound API keys

Task 11: Docker rig on the shared store; retire the file-copy workaround

Classification: standard Estimated implement time: ~5 min (+ ~3 min rebuild) Parallelizable with: Task 10, Task 12 Model: opus

Files:

  • Modify: docker/docker-compose.yml (both central environment: blocks: ScadaBridge__InboundApi__ApiKeyStore__Provider: "SqlServer" with a comment pointing at the design; connection string deliberately NOT set — falls back to ScadaBridge__Database__ConfigurationDb)
  • Modify: docker-env2/docker-compose.yml (same for its two centrals — that rig is also a pair)
  • Delete: docker/sync-inbound-api-keys.sh
  • Modify: docker/verify-delmia-notifier.sh (header lines 9-13 + the preflight: keep the "both nodes list the key" check — it is now the proof, not a gate that needs a script — and reword the comment)
  • Modify: docker/README.md (remove the sync script line if listed; note the provider)

Steps:

  1. Edit compose + scripts. bash docker/deploy.sh (rebuilds the image with 0.3.0 and recreates the pair).
  2. Watch both central logs for the import: docker logs scadabridge-central-a 2>&1 | grep -i "api-key store import\|zbauth" — expect one node reporting KeysInserted=1 (the delmia-notifier key) and the other KeysInserted=0, KeysSkipped=1 (both files hold the same key after the earlier sync), and inbound-api-keys.sqlite.imported on both volumes (docker exec scadabridge-central-a ls /app/data).
  3. Confirm one row set: docker exec scadabridge-mssql /opt/mssql-tools18/bin/sqlcmd -C -S localhost -U sa -P 'ScadaBridge_Dev1#' -d ScadaBridgeConfig -Q "SELECT key_id, display_name, revoked_utc FROM zbauth.api_keys" (adapt the tools path to what the image has). Both scadabridge security api-key list via :9001 and :9002 show the same keys.
  4. Cluster-wide revoke proof: security api-key create --name revoke-probe --methods DelmiaRecipeDownload via :9001; curl the method through :9002 with it → 200; security api-key update --key-id <id> --enabled false via :9001; the same curl via :9002 → 401. Then delete it.
  5. Run export DELMIA_API_KEY=…; bash docker/verify-delmia-notifier.sh without any file copy → A, B, E, F YES; C, G NO. Record the takeover seconds.
  6. docker-env2: bash docker-env2/deploy.sh, confirm both of its centrals boot and one imports.

Commit — chore(docker): both rigs' centrals on the shared SQL Server inbound key store; delete sync-inbound-api-keys.sh (superseded); verify script proves both nodes see the key without copying

Task 12: Documentation + register + umbrella index

Classification: trivial (docs only, but wide) Estimated implement time: ~5 min Parallelizable with: Task 10, Task 11 Model: sonnet

Files:

  • Modify: docs/requirements/Component-InboundAPI.md §Storage (lines 23-24: keys live in the ZB.MOM.WW.Auth.ApiKeys store — SQLite per node by default, or the shared zbauth schema in the configuration database on a central pair via Provider=SqlServer; revoke/rotate/scope are cluster-wide only under SqlServer) and the Dependencies line 309.
  • Modify: docs/operations/inbound-api-key-reissue.md §2 config table (add Provider, SqlServer:ConnectionString default, ImportFromSqlitePath) + a new §"Central pairs" paragraph: stop both together, set Provider, start both, check the import log line on each, security api-key list from each node, then delete the .imported files after a quarantine.
  • Create: docs/operations/sql/zbauth-schema.sql (copy of the library's DDL for DBAs whose app login lacks DDL rights; the migrator then finds the version row and skips).
  • Modify: docs/deployment/topology-guide.md (central-pair section: the key store is shared under SqlServer; SQLite on a pair is a documented misconfiguration the validator warns about).
  • Modify: env_variables.md (rows for ScadaBridge__InboundApi__ApiKeyStore__Provider, …__SqlServer__ConnectionString, …__ImportFromSqlitePath).
  • Modify: CLAUDE.md → Security & Auth: one bullet — inbound key store per node by default; central pairs MUST run Provider=SqlServer (shared zbauth schema in the config DB, import once at boot, revoke cluster-wide); rig does; QA/prod appsettings are user-held (deployment record).
  • Modify: docs/plans/2026-07-08-deferred-work-register.md row 108 → resolved (library 0.3.0 + host provider + rig proof; QA/vd03 rollout pending as a deployment record).
  • Modify: docs/api-specs/Delmia-Recipe-Download-API.md §8 (the note about the sync script → superseded).
  • Modify: ../scadaproj/CLAUDE.md ScadaBridge entry (inbound keys on the shared store; Auth 0.3.0 consumer).

Check: grep -rn "sync-inbound-api-keys" docs docker CLAUDE.md → only historical mentions in the register/plan. Commit — docs: inbound API key store — shared SQL Server provider on central pairs (spec, runbook, topology, env inventory, CLAUDE.md, register row 108 closed)

Task 13: Deployment record template for QA (vq03/vq04) and prod (vd03)

Classification: trivial Estimated implement time: ~3 min Parallelizable with: none (after Task 12) Model: sonnet

Files:

  • Create: docs/deployment/deployment-records/2026-09-XX-zb-servers-inbound-key-store.md (a planned record with the exact steps and blanks for results — the user runs it; appsettings on those boxes are user-held)

Content: build/stage per the 2026-08-21 recipe; QA: stop both centrals together → add "ApiKeyStore": { "Provider": "SqlServer" } under ScadaBridge:InboundApi in both appsettings.Central.json → if scadabridge_app lacks DDL rights run docs/operations/sql/zbauth-schema.sql on the QA config DB first → start both → expect one KeysInserted=N line and one KeysSkipped line → security api-key list from 10.220.158.2:8085 and .3:8085 agree → sync-free failover check with a curl against DelmiaRecipeDownload from the non-active node. Prod vd03: single node, same change (KeysInserted=5 expected: mes, mes api key, delmia-mes-receivers, TestKey, JOE_TEST_KEY). Rollback: revert Provider, rename .imported back — no SQL rows need removing (the SQLite path ignores them).

Commit — docs(deployment): planned record for moving QA/prod central inbound key stores to SQL Server

Task 14: Final cross-repo review

Classification: standard Estimated implement time: ~5 min Parallelizable with: none Model: fable (this session)

Check: (1) scadaproj git log shows Tasks 1-7 committed and artifacts/ holds five 0.3.0 packages that match the feed; (2) ScadaBridge git diff main contains no connection string or token (grep -n "Password=\|sbk_[A-Za-z0-9]" <diff> → only the dev-only compose values that were already committed); (3) dotnet test ZB.MOM.WW.ScadaBridge.slnx green; (4) Task 11 step 4 (revoke visible on the peer) and step 5 (drill without copying) are recorded with numbers; (5) MxAccessGateway and HistorianGateway still build against 0.3.0 without edits (dotnet build each after bumping their pins in a scratch branch — do not commit there; report only); (6) the rig's sync-inbound-api-keys.sh is gone and nothing references it. Then ask the user about pushing both repos.

Out of scope

  • Site nodes, MxAccessGateway, HistorianGateway (SQLite stays their store).
  • Hard-failing a clustered central on Provider=Sqlite (warning only; revisit after QA is on SqlServer).
  • Encrypting secret_hash at rest; the pepper never enters SQL.
  • Fixing register row 107 (prod DelmiaRecv) — separate change.
Reference in New Issue View Git Blame Copy Permalink