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`](../../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`](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.