scadaproj/docs/plans/2026-07-17-secrets-g7-sqlserver-store.md

G-7 (Option A) — Shared SQL-Server ISecretStore Implementation Plan

✅ EXECUTED 2026-07-18 — and the scope was widened on the user's instruction. This plan covered Option A only, with Option B (Akka) deferred. The user directed that both be built, packaged as ZB.MOM.WW.Secrets.Replicator.SqlServer and ZB.MOM.WW.Secrets.Replicator.AkkaDotNet — so the "Deferred phase-2" section below is also built, and the naming here (SqlServer/ inside the core package; ZB.MOM.WW.Secrets.Akka) is superseded by two standalone packages.

Other deltas from what was planned, all deliberate:

• Two SQL-Server topologies, not one. Shared-store (as planned) plus a local-store-with-hub mode, so the package's Replicator name is honest and partition tolerance is available without Akka.
• SecretLastWriterWins extracted to Abstractions. The plan flagged Task 2 high-risk because the LWW semantics "MUST match SQLite exactly"; a shared predicate makes that structural instead of a review obligation.
• ReplicatingSecretStore added. Discovered during the build that nothing ever called ISecretReplicator.PublishAsync — the seam was inert. A store decorator fixes every write path.
• ISecretReplicationHub added, so the hub sweep is testable offline rather than requiring a live SQL Server for every convergence case.
• Anti-entropy is bidirectional. Pull-only would strand a write made while a peer was down: the peer never learns the name exists, so it can never ask for it.

Verified: 164 pass / 1 skip / 0 warnings, including 15 live tests against a real SQL Server 2022 and a 9-test in-process 2-node Akka cluster. See components/secrets/GAPS.md §G-7.

For Claude: REQUIRED SUB-SKILL: Use superpowers-extended-cc:executing-plans (or subagent-driven-development) to implement this plan task-by-task.

Goal: Add a shared SQL-Server ISecretStore provider to ZB.MOM.WW.Secrets so the Akka-clustered apps (ScadaBridge, OtOpcUa) can back their secrets with one shared, ciphertext-only database — every node sees the same secrets, no replication code.

Architecture: Mirror the existing SqliteSecretStore + SqliteSecretsStoreMigrator in T-SQL behind the unchanged ISecretStore seam. Select the store in AddZbSecrets from a new SecretsOptions.Store enum (SQLite stays the default). The KEK stays per-node/out-of-DB, so the DB holds only ciphertext. See the design + fork rationale in 2026-07-17-secrets-g7-clustered-replication-design.md.

Tech Stack: .NET 10, Microsoft.Data.SqlClient, xUnit + Shouldly, env-gated live SQL tests (LocalDB or a mcr.microsoft.com/mssql/server container), matching the app live-test idiom.

Precondition: G-8 is merged — ISecretStore.ApplyRewrapAsync exists and the SQL-Server store must implement it. rewrap-all then runs once against the shared store.

---

Task 1: SQL-Server schema + migrator

Classification: standard Estimated implement time: ~5 min Parallelizable with: none (Task 2 depends on it)

Files:

• Create: src/ZB.MOM.WW.Secrets/SqlServer/SqlServerSecretsSchema.cs
• Create: src/ZB.MOM.WW.Secrets/SqlServer/SqlServerSecretsStoreMigrator.cs
• Create: src/ZB.MOM.WW.Secrets/SqlServer/SecretsSqlServerConnectionFactory.cs
• Test: tests/ZB.MOM.WW.Secrets.Tests/SqlServer/SqlServerSecretsStoreMigratorTests.cs (env-gated)

Notes:

• Mirror SqliteSecretsSchema / SqliteSecretsStoreMigrator semantics: schema_version single-row table + secret table, CurrentVersion = 1, idempotent IF NOT EXISTS DDL, refuse a newer on-disk version. Column set identical to SQLite; use varbinary(max) for the 6 crypto BLOBs, nvarchar for text, and store timestamps as ISO-8601 ("O") text so StoredSecret round-trips byte-identically to the SQLite path (avoids datetimeoffset precision drift in tests).
• Add Microsoft.Data.SqlClient to Directory.Packages.props + the core csproj.
• Gate live tests on an env var (e.g. SECRETS_SQLSERVER_CONNSTR); skip cleanly when unset.

Task 2: SqlServerSecretStore : ISecretStore

Classification: high-risk Estimated implement time: ~5 min Parallelizable with: none (depends on Task 1)

Files:

• Create: src/ZB.MOM.WW.Secrets/SqlServer/SqlServerSecretStore.cs
• Test: tests/ZB.MOM.WW.Secrets.Tests/SqlServer/SqlServerSecretStoreTests.cs (env-gated; port the SqliteSecretStoreTests cases 1:1, incl. the G-8 ApplyRewrap* cases)

Notes:

• Implement all 7 members: GetAsync, UpsertAsync (revision bump — MERGE or insert/ON CONFLICT-equivalent via UPDATE+INSERT under a transaction), DeleteAsync (tombstone + revision bump), ListAsync (metadata projection — never the crypto columns), GetManifestAsync, ApplyReplicatedAsync (LWW under IsolationLevel.Serializable, applied verbatim, no revision bump), ApplyRewrapAsync (UPDATE the 4 wrap columns + kek_id only; no revision/updated_utc change).
• Fully parameterized commands; same "created_utc/created_by preserved on overwrite" semantics as SQLite. This is high-risk because the LWW + rewrap semantics MUST match SQLite exactly (a cluster reconciles across both if a node ever migrates).

Task 3: DI store-selection wiring

Classification: standard Estimated implement time: ~4 min Parallelizable with: none (depends on Task 2)

Files:

• Modify: src/ZB.MOM.WW.Secrets/SecretsOptions.cs (add SecretsStoreKind Store = Sqlite default; add string? SqlServerConnectionString)
• Create: src/ZB.MOM.WW.Secrets/SecretsStoreKind.cs (enum Sqlite | SqlServer)
• Modify: src/ZB.MOM.WW.Secrets/DependencyInjection/SecretsServiceCollectionExtensions.cs (branch the ISecretStore + migrator registration on Store; keep SQLite the default so HistorianGateway/mxaccessgw are unaffected)
• Modify: src/ZB.MOM.WW.Secrets/DependencyInjection/SecretsMigrationHostedService.cs if it hard-refs the SQLite migrator type (abstract behind a shared migrator seam or a switch)
• Test: tests/ZB.MOM.WW.Secrets.Tests/DependencyInjection/AddZbSecretsTests.cs (assert the correct store type is resolved for each Store value)

Notes:

• Introduce a tiny ISecretsStoreMigrator seam (both migrators implement it) so the hosted service + CLI stay store-agnostic, OR switch on Store at registration. Prefer the seam — cleaner.
• The connection string should itself be deliverable via ${secret:} / a secret: ref (consumers already do this for other connstrings).

Task 4: Docs + adoption notes

Classification: small Estimated implement time: ~3 min Parallelizable with: Task 3

Files:

• Modify: README.md (Clustered deployments: document Secrets:Store = SqlServer + shared-KEK requirement + "run rewrap-all once against the shared store")
• Create: docs/operations/shared-sql-store.md (operator setup: provision the DB, grant, set the connstr via a secret ref, confirm the same KEK on every node)
• Modify: components/secrets/GAPS.md (G-7 → "Option A built"; note Option B deferred)

Notes: No app changes here — adoption in ScadaBridge/OtOpcUa is a follow-on (flip Secrets:Store, supply the connstr + shared KEK). Capture that as a checklist, not code.

---

Deferred phase-2 (NOT built here): Option B — ZB.MOM.WW.Secrets.Akka

Build only when an app declares it must resolve secrets while partitioned from the shared store. Shape (for when it's warranted):

• New package ZB.MOM.WW.Secrets.Akka (net10; refs Akka.Cluster + Abstractions).
• AkkaSecretReplicator : ISecretReplicator — PublishAsync broadcasts the encrypted StoredSecret to peers (a cluster-aware router / distributed pub-sub topic).
• An anti-entropy actor (cluster singleton or per-node) that periodically exchanges GetManifestAsync digests and pulls newer/missing rows via ApplyReplicatedAsync (LWW); tombstones propagate deletes; a retention window bounds tombstone GC.
• StoredSecret serialization (ciphertext only — never the KEK) + an ISecretActorAccessor wiring.
• Validation: a live 2-node ScadaBridge/OtOpcUa rig proving write-on-A → resolve-on-B, delete propagation, and partition-heal resync — a G-2-class live gate.
• KEK constraint identical to Option A: same master KEK on every node; rewrap-all per node.