# Data & Audit Backbone Fixes Implementation Plan

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

**Goal:** Defuse the scale-dependent operational time bombs in the central data/audit layer (missing site SQLite retention purge, partition-switch timeout self-lock, unindexed KPI scans, table-wide execution-tree scans, hardcoded-zero backlog KPI) and close the audit-integrity/consistency gaps (append-only role drift, frozen SiteCalls progress fields, missing operator-Retry audit rows, reconciliation dead-ends, NodeA-only reconciliation) identified in `archreview/04-data-audit-backbone.md`.

**Architecture:** All fixes stay inside the existing component boundaries: site-side purge lives in the AuditLog component's site half (SqliteAuditWriter + a new hosted retention job), central maintenance hardening lives in AuditLogRepository/AuditLogPurgeActor, index/DDL changes ride EF Core migrations in ConfigurationDatabase (with idempotent SQL scripts for production), and contract changes (keyset cursor, RequestedBy, SiteEntry fallback endpoint) are strictly additive per the repo's message-evolution rule. Design docs are updated in the same task as the code they describe (repo rule: doc + code + tests travel together).

**Tech Stack:** C#/.NET 8, Akka.NET (actors/TestKit), EF Core + SQL Server (central), Microsoft.Data.Sqlite (site), xUnit. Build: `dotnet build ZB.MOM.WW.ScadaBridge.slnx`. Test per-project: `dotnet test tests/<project> --filter <name>`. MSSQL-backed integration tests need `cd infra && docker compose up -d`. **EF migrations gotcha (repo-documented):** always build first and run `dotnet ef migrations add <Name>` WITHOUT `--no-build`, or it scaffolds an empty migration off the stale DLL.

## Findings Coverage

| # | Report finding (severity) | Task(s) |
|---|---------------------------|---------|
| S1 | Site SQLite 7-day retention purge missing (High) | 1, 2, 3 |
| S2 | `SwitchOutPartitionAsync` no CommandTimeout / log-only failure (High) | 4, 5 |
| S3 | Append-only DB-role enforcement DDL-only; purger grants insufficient (High) | 6, 7 |
| S4 | SiteCalls upsert freezes RetryCount/LastError at rank (Medium) | 11 |
| S5 | No EF `EnableRetryOnFailure` (Medium) | 12 |
| S6 | Operator Retry on parked notification emits no audit row (+SiteCalls relay identity) (Medium) | 13, 14 |
| S7 | Reconciliation edge cases dead-ended (cursor pin; permanent abandonment) (Medium) | 15, 16, 17 |
| S8 | NotificationOutboxRepository dual SQLite/T-SQL dialect — provider parity (Medium) | 24 (documented convention decision: MSSQL-only SQL + MSSQL fixtures is the norm for new repositories; existing dual-dialect kept as a documented legacy exception — rewriting the shipped repository + its whole test suite is not worth the churn) |
| S9 | Purge actors' first tick waits a full interval (Low) | 22 |
| S10 | `SqliteAuditWriter.Dispose` sync-over-async (Low) | Won't-fix — report itself notes the thread-pool-hop mitigation is correct and documented; flagged-only |
| P1 | SiteCalls KPI predicates full-scan — no `TerminalAtUtc` index (High) | 8 |
| P2 | `GetExecutionTreeAsync` scans entire AuditLog (High) | 9 |
| P3 | AuditLog clustered key leads with random GUID (Medium) | Deferred — report says "worth a deliberate benchmark before the table gets big"; changing it is a full-table rebuild. Task 24 records it as a tracked design decision in Component-ConfigurationDatabase.md so it is no longer untracked |
| P4 | KpiSample volume + unbatched purge (Medium) | 19 (batched purge); per-node sampling cadence recorded as tracked follow-up in 24 |
| P5 | Catch-all `(@p IS NULL OR col=@p)` predicates in SiteCalls query (Medium) | 21 |
| P6 | Outbox offset paging + double query + LIKE; KPI 5-7 round trips; unbounded oldest materialization (Medium) | 20 (KPI single query + bounded oldest); offset→keyset conversion Deferred — it changes the page/TotalCount UI contract for a table whose live-queue portion stays small under Task 19/existing purge; recorded in 24 |
| P7 | Non-persisted `IngestedAtUtc` computed column re-parse (Low) | 23 (predicate-ban comment guard) |
| P8 | `MarkForwardedAsync`/`MarkReconciledAsync` per-id IN lists near SQLite param limit (Low) | 23 |
| C1 | Design docs drifted (enum lists, Teams, per-channel overrides, SiteCalls schema) (Medium) | 24 |
| C2 | `AuditLogRow` entity lives in ConfigurationDatabase not Commons (Medium) | 24 (document the deviation — the rationale in the report is accepted: contract type is external `ZB.MOM.WW.Audit.AuditEvent`, row is a persistence-only projection) |
| C3 | Mixed timestamp CLR types across sibling tables (Low) | 24 (convention note: new tables use `DateTime`+Utc converter) |
| C4 | Uncharted KPI metric names are string literals (Low) | 23 |
| U1 | `backlogTotal` KPI history permanently zero | 10 |
| U2 | Site SQLite retention purge | 1, 2, 3 |
| U3 | Hash-chain tamper evidence / Parquet archival | No action — explicitly deferred to v1.x per Component-AuditLog.md; report confirms "consistent with plan; no drift" |
| U4 | SecuredWrite audit rows leave `SourceNode` NULL; doc under-promises per-channel keys | SourceNode fix Deferred — already a logged follow-up in Component-AuditLog.md:154-157 and the SecuredWrite emission path belongs to plan 07's domain. Doc under-promise fixed in 24 |
| U5 | Reconciliation cursor keyset upgrade untracked | 15, 16 (SiteCalls keyset implemented); AuditLog-pull keyset recorded as tracked follow-up in Component-AuditLog.md (24) |
| U6 | Dispatcher throughput ceiling (sequential delivery) | 25 |
| U7 | Test-coverage gaps mirror findings | Covered inside tasks 1, 4, 10 (the report's three named gaps each get a test) |
| X1 | AuditLog reconciliation dials site NodeA only (flagged in report 08, owned here) | 18 |

---

### Task 1: Site SQLite retention purge — `PurgeExpiredAsync` on the writer

**Classification:** standard
**Estimated implement time:** ~5 min
**Parallelizable with:** 4, 6, 8, 11, 12, 19, 20, 21
**Files:**
- Modify: `src/ZB.MOM.WW.ScadaBridge.Commons/Interfaces/Services/ISiteAuditQueue.cs` (add method)
- Modify: `src/ZB.MOM.WW.ScadaBridge.AuditLog/Site/SqliteAuditWriter.cs` (implement; the `auto_vacuum = INCREMENTAL` pragma at ~line 149 already anticipates this)
- Test: `tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests/Site/SqliteAuditWriterRetentionTests.cs` (create)

1. Write the failing test (pattern-match the fixture setup from `SqliteAuditWriterBacklogStatsTests.cs`):

```csharp
public class SqliteAuditWriterRetentionTests
{
    [Fact]
    public async Task PurgeExpired_DeletesForwardedAndReconciled_OlderThanCutoff()
    {
        await using var writer = CreateWriter(); // same in-memory/temp-file helper the sibling tests use
        var oldForwarded = await WriteEventAsync(writer, occurredAtUtc: DateTime.UtcNow.AddDays(-10));
        var oldReconciled = await WriteEventAsync(writer, occurredAtUtc: DateTime.UtcNow.AddDays(-10));
        var oldPending = await WriteEventAsync(writer, occurredAtUtc: DateTime.UtcNow.AddDays(-10));
        var fresh = await WriteEventAsync(writer, occurredAtUtc: DateTime.UtcNow);
        await writer.MarkForwardedAsync(new[] { oldForwarded, oldReconciled, fresh });
        await writer.MarkReconciledAsync(new[] { oldReconciled });

        var purged = await writer.PurgeExpiredAsync(DateTime.UtcNow.AddDays(-7));

        Assert.Equal(2, purged); // oldForwarded + oldReconciled
        // Hard ForwardState invariant: the old *Pending* row survives on age alone.
        var pending = await writer.ReadPendingAsync(10);
        Assert.Contains(pending, e => e.EventId == oldPending);
        // Fresh forwarded row inside the window survives.
        var stats = await writer.GetBacklogStatsAsync();
        Assert.Equal(1, stats.PendingCount);
    }

    [Fact]
    public async Task PurgeExpired_IsIdempotent()
    {
        await using var writer = CreateWriter();
        var id = await WriteEventAsync(writer, occurredAtUtc: DateTime.UtcNow.AddDays(-10));
        await writer.MarkForwardedAsync(new[] { id });
        Assert.Equal(1, await writer.PurgeExpiredAsync(DateTime.UtcNow.AddDays(-7)));
        Assert.Equal(0, await writer.PurgeExpiredAsync(DateTime.UtcNow.AddDays(-7)));
    }
}
```

2. Run: `dotnet test tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests --filter SqliteAuditWriterRetentionTests` — expect FAIL (method does not exist).
3. Add to `ISiteAuditQueue`: `Task<int> PurgeExpiredAsync(DateTime olderThanUtc, CancellationToken ct = default);` with XML doc stating the hard invariant (`Pending` rows are never purged on age alone — only `Forwarded`/`Reconciled`). Implement in `SqliteAuditWriter` under `_writeLock` (mirror `MarkForwardedAsync`'s lock + `ObjectDisposedException.ThrowIf` shape), one transaction:

```sql
CREATE TEMP TABLE IF NOT EXISTS purge_ids AS
    SELECT EventId FROM audit_forward_state
    WHERE ForwardState IN ('Forwarded','Reconciled') AND OccurredAtUtc < $cutoff;
DELETE FROM audit_forward_state WHERE EventId IN (SELECT EventId FROM purge_ids);
DELETE FROM audit_event WHERE EventId IN (SELECT EventId FROM purge_ids);
DROP TABLE purge_ids;
```

Delete the sidecar first (it is the FK child of `audit_event`). `$cutoff` uses the same round-trip `"o"` timestamp format the writer already uses. Return `changes()` from the `audit_event` DELETE. After COMMIT, run `PRAGMA incremental_vacuum;` (the pragma the ctor set up — reference the existing comment at `SqliteAuditWriter.cs:145` and update it: the purge now exists).
4. Run the filter again — expect PASS. Also run the whole site suite: `dotnet test tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests --filter "FullyQualifiedName~Site"` — expect PASS.
5. Commit: `git add src/ZB.MOM.WW.ScadaBridge.Commons/Interfaces/Services/ISiteAuditQueue.cs src/ZB.MOM.WW.ScadaBridge.AuditLog/Site/SqliteAuditWriter.cs tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests/Site/SqliteAuditWriterRetentionTests.cs && git commit -m "feat(audit-log): add site SQLite PurgeExpiredAsync honoring the ForwardState invariant"`

### Task 2: Site retention options

**Classification:** small
**Estimated implement time:** ~3 min
**Parallelizable with:** 1, 4, 6, 8, 11, 12, 19, 20, 21
**Files:**
- Create: `src/ZB.MOM.WW.ScadaBridge.AuditLog/Site/SiteAuditRetentionOptions.cs`
- Test: `tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests/Site/SiteAuditRetentionOptionsTests.cs` (create)

1. Failing test:

```csharp
public class SiteAuditRetentionOptionsTests
{
    [Theory]
    [InlineData(0, 1)]   // clamp floor
    [InlineData(7, 7)]   // default passthrough
    [InlineData(365, 90)] // clamp ceiling (spec: min 1, max 90)
    public void ResolvedRetentionDays_Clamps(int configured, int expected)
        => Assert.Equal(expected, new SiteAuditRetentionOptions { RetentionDays = configured }.ResolvedRetentionDays);

    [Fact]
    public void Defaults_Are_SevenDays_DailyPurge_FiveMinuteInitialDelay()
    {
        var o = new SiteAuditRetentionOptions();
        Assert.Equal(7, o.ResolvedRetentionDays);
        Assert.Equal(TimeSpan.FromHours(24), o.ResolvedPurgeInterval);
        Assert.Equal(TimeSpan.FromMinutes(5), o.InitialDelay);
    }
}
```

2. `dotnet test tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests --filter SiteAuditRetentionOptionsTests` — FAIL.
3. Implement the options class following the `SiteCallAuditOptions.ResolvedReconciliationInterval` clamp pattern: `RetentionDays` (default 7; `ResolvedRetentionDays` clamps to `[1, 90]`), `PurgeInterval` (default 24h; `ResolvedPurgeInterval` clamps to min 1 minute — Akka zero-interval spin footgun), `InitialDelay` (default 5 min — short so a daily-restarting node still purges). Config section: `ScadaBridge:AuditLog:SiteRetention`.
4. Run filter — PASS.
5. Commit: `git add src/ZB.MOM.WW.ScadaBridge.AuditLog/Site/SiteAuditRetentionOptions.cs tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests/Site/SiteAuditRetentionOptionsTests.cs && git commit -m "feat(audit-log): SiteAuditRetentionOptions (7-day default, clamped 1-90)"`

### Task 3: Site retention hosted job + registration + design doc

**Classification:** standard
**Estimated implement time:** ~5 min
**Parallelizable with:** 4, 6, 8, 11, 12, 19, 20, 21
**Files:**
- Create: `src/ZB.MOM.WW.ScadaBridge.AuditLog/Site/SiteAuditRetentionService.cs`
- Modify: `src/ZB.MOM.WW.ScadaBridge.AuditLog/ServiceCollectionExtensions.cs` (site registration path, near the `ISiteAuditQueue` registration at ~line 111)
- Modify: `docs/requirements/Component-AuditLog.md` (~lines 412-414, the "Sites: daily site job" bullet)
- Test: `tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests/Site/SiteAuditRetentionServiceTests.cs` (create)

1. Failing test (fake `ISiteAuditQueue`, short intervals):

```csharp
public class SiteAuditRetentionServiceTests
{
    [Fact]
    public async Task Tick_Purges_With_RetentionCutoff()
    {
        var queue = new RecordingSiteAuditQueue(); // stub: records PurgeExpiredAsync calls
        var options = Options.Create(new SiteAuditRetentionOptions
            { RetentionDays = 7, PurgeInterval = TimeSpan.FromMilliseconds(50), InitialDelay = TimeSpan.Zero });
        using var svc = new SiteAuditRetentionService(queue, options, NullLogger<SiteAuditRetentionService>.Instance);
        await svc.StartAsync(CancellationToken.None);
        await WaitUntilAsync(() => queue.PurgeCalls.Count >= 1, TimeSpan.FromSeconds(5));
        await svc.StopAsync(CancellationToken.None);
        var cutoff = queue.PurgeCalls[0];
        Assert.InRange(cutoff, DateTime.UtcNow.AddDays(-7).AddMinutes(-1), DateTime.UtcNow.AddDays(-7).AddMinutes(1));
    }

    [Fact]
    public async Task Tick_SwallowsExceptions_AndKeepsTicking()
    {
        var queue = new RecordingSiteAuditQueue { ThrowOnFirstCall = true };
        // ... start, wait for >= 2 calls, assert no unobserved exception
    }
}
```

2. `dotnet test tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests --filter SiteAuditRetentionServiceTests` — FAIL.
3. Implement `SiteAuditRetentionService : IHostedService, IDisposable` mirroring `SiteAuditBacklogReporter.cs` (same file layout: `System.Threading.Timer`, `InitialDelay` as due time, `ResolvedPurgeInterval` as period, per-tick try/catch that logs and continues, purge count logged at Information). Register in the site path of `ServiceCollectionExtensions`: bind `SiteAuditRetentionOptions` from `ScadaBridge:AuditLog:SiteRetention` and `services.AddHostedService<SiteAuditRetentionService>();`.
4. Run filter — PASS. Then `dotnet build ZB.MOM.WW.ScadaBridge.slnx` — expect clean.
5. Update `Component-AuditLog.md` ~412-414: replace the aspirational bullet with the shipped mechanism — `SiteAuditRetentionService` hosted job, config keys (`ScadaBridge:AuditLog:SiteRetention:RetentionDays|PurgeInterval`), the `PurgeExpiredAsync` invariant, and the post-purge `incremental_vacuum`.
6. Commit: `git add -A src/ZB.MOM.WW.ScadaBridge.AuditLog tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests/Site docs/requirements/Component-AuditLog.md && git commit -m "feat(audit-log): daily site SQLite retention purge job (closes unbounded site DB growth)"`

### Task 4: Maintenance command timeout on partition switch + per-channel purge

**Classification:** high-risk
**Estimated implement time:** ~5 min
**Parallelizable with:** 1, 2, 3, 8 (different files), 11, 20
**Files:**
- Modify: `src/ZB.MOM.WW.ScadaBridge.AuditLog/Central/AuditLogPurgeOptions.cs`
- Modify: `src/ZB.MOM.WW.ScadaBridge.Commons/Interfaces/Repositories/IAuditLogRepository.cs` (additive optional parameter)
- Modify: `src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase/Repositories/AuditLogRepository.cs` (`SwitchOutPartitionAsync` ~257-371, `PurgeChannelOlderThanAsync` ~374-445)
- Modify: `src/ZB.MOM.WW.ScadaBridge.AuditLog/Central/AuditLogPurgeActor.cs` (pass the timeout)
- Test: `tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests/Central/AuditLogPurgeActorTests.cs` (extend), `tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests/Integration/PartitionPurgeTests.cs` (extend, MSSQL)

1. Failing unit test in `AuditLogPurgeActorTests` (these tests already drive the actor against a mocked `IAuditLogRepository`): assert the actor calls `SwitchOutPartitionAsync(boundary, commandTimeout)` with the options value:

```csharp
[Fact]
public async Task PurgeTick_Passes_MaintenanceCommandTimeout_To_Repository()
{
    // options: MaintenanceCommandTimeoutMinutes = 45
    // mock repo: capture the TimeSpan? argument
    // drive one tick; Assert.Equal(TimeSpan.FromMinutes(45), captured);
}
```

Plus an options test: `MaintenanceCommandTimeoutMinutes` default 30, clamped to min 1 (`ResolvedMaintenanceCommandTimeout`).
2. `dotnet test tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests --filter AuditLogPurgeActorTests` — FAIL (no such parameter).
3. Implementation:
   - `AuditLogPurgeOptions`: add `public int MaintenanceCommandTimeoutMinutes { get; set; } = 30;` + `ResolvedMaintenanceCommandTimeout` clamp (min 1 minute), XML-doc'd against the 30s-default-timeout failure mode from the review.
   - `IAuditLogRepository`: `Task<long> SwitchOutPartitionAsync(DateTime monthBoundary, TimeSpan? commandTimeout = null, CancellationToken ct = default);` and `PurgeChannelOlderThanAsync(..., TimeSpan? commandTimeout = null, ...)` — additive defaults, existing callers compile unchanged.
   - `AuditLogRepository.SwitchOutPartitionAsync`: when `commandTimeout` is non-null, set `sampleCmd.CommandTimeout = (int)commandTimeout.Value.TotalSeconds;` and wrap the `ExecuteSqlRawAsync` with `_context.Database.SetCommandTimeout(commandTimeout)` (the context is scoped per purge tick — see `AuditLogPurgeActor.OnTickAsync`'s `CreateAsyncScope` — so no restore is needed, but restore the previous value in a `finally` anyway for hygiene). Same `cmd.CommandTimeout` treatment on the per-channel `DELETE TOP` command.
   - `AuditLogPurgeActor.OnTickAsync`: pass `_purgeOptions.ResolvedMaintenanceCommandTimeout` to both repository calls.
4. Run the actor filter — PASS. Extend `PartitionPurgeTests` (MSSQL; `cd infra && docker compose up -d` first) with one case that calls `SwitchOutPartitionAsync(boundary, TimeSpan.FromMinutes(30))` and asserts success — proving the timeout path executes against real SQL Server: `dotnet test tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests --filter PartitionPurgeTests`.
5. Commit: `git add -A src/ZB.MOM.WW.ScadaBridge.AuditLog src/ZB.MOM.WW.ScadaBridge.Commons src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests && git commit -m "fix(audit-log): explicit long CommandTimeout on partition-switch and per-channel purge maintenance paths"`

### Task 5: Purge-failure health event (not just a log line)

**Classification:** standard
**Estimated implement time:** ~4 min
**Parallelizable with:** 8, 11, 19, 20, 21
**Files:**
- Create: `src/ZB.MOM.WW.ScadaBridge.AuditLog/Central/AuditLogPurgeFailedEvent.cs`
- Modify: `src/ZB.MOM.WW.ScadaBridge.AuditLog/Central/AuditLogPurgeActor.cs` (catch block ~198-206 and the boundary-enumeration catch)
- Modify: `src/ZB.MOM.WW.ScadaBridge.AuditLog/Central/AuditCentralHealthSnapshot.cs` + `IAuditCentralHealthSnapshot.cs` (add `PurgeFailures` counter, mirroring the existing counter members)
- Modify: `docs/requirements/Component-AuditLog.md` (retention section: purge failure is now a health metric)
- Test: `tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests/Central/AuditLogPurgeActorTests.cs` (extend)

1. Failing test: mock repository whose `SwitchOutPartitionAsync` throws; subscribe a TestProbe to `AuditLogPurgeFailedEvent` on the EventStream (same pattern the existing tests use for `AuditLogPurgedEvent`); drive a tick; expect one `AuditLogPurgeFailedEvent(boundary, errorMessage, elapsedMs)` and assert the health snapshot's `PurgeFailures` incremented.
2. `dotnet test tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests --filter AuditLogPurgeActorTests` — FAIL.
3. Implement: `public sealed record AuditLogPurgeFailedEvent(DateTime MonthBoundary, string Error, long ElapsedMilliseconds);` — publish it in the per-boundary catch (alongside the existing `LogError`) using the already-captured `eventStream`; increment the new snapshot counter (inject `IAuditCentralHealthSnapshot` the same way sibling actors take their counters — follow the existing ctor DI shape). The success event (`AuditLogPurgedEvent`) already exists; this is its symmetric failure twin.
4. Run filter — PASS.
5. Doc: add to Component-AuditLog.md retention section: "Purge failure publishes `AuditLogPurgeFailedEvent` and increments the `PurgeFailures` central health counter — a silently failing retention job is a health-surface condition, not just a log line."
6. Commit: `git add -A src/ZB.MOM.WW.ScadaBridge.AuditLog tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests/Central docs/requirements/Component-AuditLog.md && git commit -m "feat(audit-log): surface partition-purge failure as health event + counter"`

### Task 6: Fix purger role grants (migration)

**Classification:** high-risk
**Estimated implement time:** ~4 min
**Parallelizable with:** 1, 2, 3, 11, 20 (NOT with Task 8 — both add migrations, serialize on the model snapshot)
**Files:**
- Create: `src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase/Migrations/<timestamp>_FixAuditPurgerRoleGrants.cs` (scaffolded)
- Test: `tests/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.Tests/Migrations/` (add `FixAuditPurgerRoleGrantsTests.cs`), plus re-run `AuditLogAppendOnlyGuardTests`

1. Failing test first — a migration-content guard in the style of the existing migration tests: assert the migration source contains `GRANT CREATE TABLE TO scadabridge_audit_purger` and `GRANT DELETE ON dbo.AuditLog TO scadabridge_audit_purger`. Run `dotnet test tests/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.Tests --filter FixAuditPurgerRoleGrants` — FAIL (file absent).
2. Scaffold (repo gotcha — build first, NO `--no-build`):

```bash
dotnet build ZB.MOM.WW.ScadaBridge.slnx
cd src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase
dotnet ef migrations add FixAuditPurgerRoleGrants
```

Verify the scaffold is model-empty (no schema change) and add the SQL by hand in `Up`:

```csharp
migrationBuilder.Sql(@"
IF DATABASE_PRINCIPAL_ID('scadabridge_audit_purger') IS NULL
    EXEC sp_executesql N'CREATE ROLE scadabridge_audit_purger';
-- The switch-out dance CREATEs a staging table; ALTER ON SCHEMA::dbo alone does not
-- confer the database-level CREATE TABLE permission (review finding S3).
GRANT CREATE TABLE TO scadabridge_audit_purger;
-- The per-channel retention override (PurgeChannelOlderThanAsync) is a bounded row
-- DELETE on the maintenance path; a segregated purger principal needs this grant.
GRANT DELETE ON dbo.AuditLog TO scadabridge_audit_purger;"); // AUDIT-PURGE-ALLOWED: role grant for the maintenance principal
```

`Down`: `REVOKE` both grants (guarded on principal existence).
3. Run the new test — PASS. Then run `dotnet test tests/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.Tests --filter AuditLogAppendOnlyGuardTests` — if the CI grep guard trips on the `GRANT DELETE ON dbo.AuditLog` text, extend the guard's allowlist to permit `GRANT`/`DENY`/`REVOKE` statements (they are permission DDL, not data mutation) — keep the guard strict for `DELETE FROM`/`UPDATE `.
4. Production script step (repo convention — manual SQL for production): `dotnet ef migrations script AddPendingDeployment FixAuditPurgerRoleGrants --idempotent --output ../../docs/plans/sql/FixAuditPurgerRoleGrants.sql` (adjust the from-migration to the actual latest; review the output).
5. MSSQL sanity: with infra up, `dotnet test tests/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.Tests --filter Migrations` — expect PASS.
6. Commit: `git add -A src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase tests/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.Tests docs/plans/sql && git commit -m "fix(config-db): grant CREATE TABLE + scoped DELETE to scadabridge_audit_purger so a segregated maintenance principal can actually run the purge"`

### Task 7: Honest append-only enforcement story (docs)

**Classification:** small
**Estimated implement time:** ~4 min
**Parallelizable with:** everything except 24 (same doc files)
**Files:**
- Modify: `docs/requirements/Component-AuditLog.md` (~417-424, "Security & Tamper-Evidence")
- Modify: `docs/requirements/Component-ConfigurationDatabase.md` (~399-402, AuditLog Table Purge section)

1. Rewrite Component-AuditLog.md §Security "Append-only enforcement" to state reality (review finding S3): the application runs with **one** connection principal for both writer and maintenance paths, so in the default deployment append-only is enforced by (a) the CI grep guard (`AuditLogAppendOnlyGuardTests`) with its single marked DELETE exemption, and (b) code review. The `scadabridge_audit_writer` / `scadabridge_audit_purger` roles are **optional DBA hardening**: a deployment that wants database-level enforcement must provision two logins (runtime → writer role; a maintenance job/second connection → purger role) — and note the purger role now carries `CREATE TABLE` + scoped `DELETE` (Task 6) so it can genuinely execute the switch-out and per-channel purge. Remove the false claim "row-level DELETE is not granted even to purge" (stale since `PerChannelRetentionDays` shipped).
2. In Component-ConfigurationDatabase.md fix the same passage's stale "single global value in v1, no per-channel overrides" claim while in the file (coordinates with Task 24 — do this bullet here since it is in the same paragraph; Task 24 skips it if already fixed).
3. Verify no other doc contradicts: `grep -rn "audit_writer\|audit_purger" docs/`.
4. Commit: `git add docs/requirements/Component-AuditLog.md docs/requirements/Component-ConfigurationDatabase.md && git commit -m "docs(audit-log): document append-only enforcement honestly (CI guard default; DB roles are optional DBA hardening)"`

### Task 8: SiteCalls filtered non-terminal index (migration)

**Classification:** high-risk
**Estimated implement time:** ~4 min
**Parallelizable with:** 1, 2, 3, 5, 11 (NOT with Task 6 — migration serialization)
**Files:**
- Modify: `src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase/Configurations/SiteCallEntityTypeConfiguration.cs` (~66-88; also fix the now-stale "No index — … per-site, not per-node" SourceNode comment)
- Create: `src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase/Migrations/<timestamp>_AddSiteCallsNonTerminalIndex.cs` (scaffolded)
- Test: `tests/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.Tests/SchemaConfigurationTests.cs` (extend)

1. Failing model test (SchemaConfigurationTests already asserts index shapes):

```csharp
[Fact]
public void SiteCalls_Has_Filtered_NonTerminal_Index()
{
    var entity = Model.FindEntityType(typeof(SiteCall))!;
    var index = entity.GetIndexes().Single(i => i.GetDatabaseName() == "IX_SiteCalls_NonTerminal");
    Assert.Equal(new[] { nameof(SiteCall.CreatedAtUtc) }, index.Properties.Select(p => p.Name));
    Assert.Equal("[TerminalAtUtc] IS NULL", index.GetFilter());
    Assert.Equal(new[] { "SourceSite", "SourceNode", "Status" }, index.GetIncludeProperties());
}
```

2. `dotnet test tests/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.Tests --filter SiteCalls_Has_Filtered_NonTerminal_Index` — FAIL.
3. Add to `SiteCallEntityTypeConfiguration.Configure`:

```csharp
// NonTerminal backs every "live queue" KPI predicate (TerminalAtUtc IS NULL):
// buffered/stuck/oldest counts run every 60 s from the KPI recorder plus every
// 10 s Health-dashboard poll. Filtered: the non-terminal population is the live
// queue, not the 365-day archive — each count becomes a small seek instead of a
// clustered scan (arch-review 04, P1).
builder.HasIndex(s => s.CreatedAtUtc)
    .HasDatabaseName("IX_SiteCalls_NonTerminal")
    .HasFilter("[TerminalAtUtc] IS NULL")
    .IncludeProperties(nameof(SiteCall.SourceSite), nameof(SiteCall.SourceNode), nameof(SiteCall.Status));
```

4. Scaffold the migration (build first, NO `--no-build`): `dotnet build ZB.MOM.WW.ScadaBridge.slnx && cd src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase && dotnet ef migrations add AddSiteCallsNonTerminalIndex` — verify the scaffold contains the `CreateIndex` with filter (if it is empty, the build/`--no-build` gotcha bit — delete and redo).
5. Run the model test — PASS. With infra up, run the MSSQL-backed repo suite: `dotnet test tests/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.Tests --filter SiteCallAuditRepositoryTests` — PASS.
6. Production script: `dotnet ef migrations script FixAuditPurgerRoleGrants AddSiteCallsNonTerminalIndex --idempotent --output ../../docs/plans/sql/AddSiteCallsNonTerminalIndex.sql`.
7. Commit: `git add -A src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase tests/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.Tests docs/plans/sql && git commit -m "perf(site-call-audit): filtered IX_SiteCalls_NonTerminal index — KPI predicates seek the live queue instead of scanning 365 days"`

### Task 9: Bound the execution-tree edge scan with a root time window

**Classification:** standard
**Estimated implement time:** ~5 min
**Parallelizable with:** 1, 2, 3, 11, 13, 18, 19
**Files:**
- Modify: `src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase/Repositories/AuditLogRepository.cs` (`GetExecutionTreeAsync`, ~632-790)
- Modify: `docs/requirements/Component-AuditLog.md` (audit-tree section: document the 7-day traversal bound)
- Test: `tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests/Integration/ExecutionIdCorrelationTests.cs` or the existing execution-tree test file (extend; MSSQL)

1. Failing test (add to the existing MSSQL execution-tree suite — locate with `grep -rln "GetExecutionTreeAsync" tests/`):

```csharp
[Fact]
public async Task ExecutionTree_Bounds_EdgeScan_To_Root_Window()
{
    // Root execution at T0, child at T0+5min → both returned.
    // Unrelated execution pair at T0+30 days sharing no ancestry → NOT returned (was never returned;
    // this asserts correctness is preserved), AND a genuine descendant stamped at T0+30 days
    // (beyond the 7-day traversal bound) is documented-excluded: assert it is absent.
}
```

2. Run: `dotnet test tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests --filter ExecutionTree_Bounds_EdgeScan` — FAIL (descendant beyond bound is currently returned).
3. Implementation in `GetExecutionTreeAsync`, between Phase 1 and Phase 2:
   - New constants: `private static readonly TimeSpan ExecutionTreeWindowSlack = TimeSpan.FromHours(1);` and `private static readonly TimeSpan ExecutionTreeMaxSpan = TimeSpan.FromDays(7);` with a comment: execution trees span minutes, not years; the window enables partition elimination on the Edges anchor (arch-review 04, P2).
   - Query `SELECT MIN(OccurredAtUtc) FROM dbo.AuditLog WHERE ExecutionId = @root;` (a seek on `IX_AuditLog_Execution`).
   - If non-NULL, parameterize the Edges CTE anchor: `WHERE ExecutionId IS NOT NULL AND OccurredAtUtc >= @winStart AND OccurredAtUtc < @winEnd` with `@winStart = rootFirst - slack`, `@winEnd = rootFirst + maxSpan`. If NULL (row-less stub root), keep the unbounded form (current behavior) — correctness over speed for the degenerate case.
4. Run the execution-tree filter plus the full existing suite: `dotnet test tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests --filter "FullyQualifiedName~ExecutionTree|FullyQualifiedName~ExecutionId"` — PASS.
5. Doc: Component-AuditLog.md audit-tree section — "tree traversal is bounded to a 7-day window anchored at the root's first event (configurable constant); descendants beyond the window are excluded by design."
6. Commit: `git add src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase/Repositories/AuditLogRepository.cs docs/requirements/Component-AuditLog.md tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests && git commit -m "perf(audit-log): bound GetExecutionTreeAsync edge scan to a root-anchored time window (partition elimination)"`

### Task 10: Fix the hardcoded-zero backlogTotal KPI history

**Classification:** standard
**Estimated implement time:** ~5 min
**Parallelizable with:** 1, 2, 3, 6, 8, 11, 19, 21
**Files:**
- Create: `src/ZB.MOM.WW.ScadaBridge.Commons/Interfaces/Kpi/IAuditBacklogProvider.cs`
- Create: `src/ZB.MOM.WW.ScadaBridge.HealthMonitoring/Kpi/CentralHealthAuditBacklogProvider.cs`
- Modify: `src/ZB.MOM.WW.ScadaBridge.HealthMonitoring/ServiceCollectionExtensions.cs` (central registration; locate the file — `grep -n AddSingleton src/ZB.MOM.WW.ScadaBridge.HealthMonitoring/ServiceCollectionExtensions.cs`)
- Modify: `src/ZB.MOM.WW.ScadaBridge.AuditLog/Kpi/AuditLogKpiSampleSource.cs`
- Test: `tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests/Kpi/AuditLogKpiSampleSourceTests.cs` (extend/create), `tests/ZB.MOM.WW.ScadaBridge.HealthMonitoring.Tests` (provider test)

1. Failing test:

```csharp
[Fact]
public async Task Collect_Uses_BacklogProvider_When_Present()
{
    var repo = new FakeAuditLogRepository(new AuditLogKpiSnapshot(10, 1, BacklogTotal: 0, DateTime.UtcNow));
    var source = new AuditLogKpiSampleSource(repo, new FakeBacklogProvider(42));
    var samples = await source.CollectAsync(DateTime.UtcNow);
    Assert.Equal(42, samples.Single(s => s.Metric == KpiMetrics.AuditLog.BacklogTotal).Value);
}

[Fact]
public async Task Collect_FallsBack_To_Snapshot_When_Provider_Absent()
{
    // ctor with provider: null → BacklogTotal sample = snapshot value (0) — unchanged behavior
}
```

2. `dotnet test tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests --filter AuditLogKpiSampleSource` — FAIL.
3. Implementation:
   - Commons: `public interface IAuditBacklogProvider { long GetPendingBacklogTotal(); }` (XML doc: sums latest per-site `SiteAuditBacklog.PendingCount` health reports; best-effort — sites not yet reporting contribute zero).
   - HealthMonitoring: `CentralHealthAuditBacklogProvider(ICentralHealthAggregator)` — port the exact summation from `CentralUI/Services/AuditLogQueryService.GetKpiSnapshotAsync` (~lines 145-155: `state.LatestReport?.SiteAuditBacklog?.PendingCount`, `> 0` guard). Register `services.AddSingleton<IAuditBacklogProvider, CentralHealthAuditBacklogProvider>();` in the central HealthMonitoring registration.
   - `AuditLogKpiSampleSource`: ctor gains `IAuditBacklogProvider? backlogProvider = null` (MS.DI resolves the default when unregistered — sites/tests unaffected); backlog sample value becomes `_backlogProvider?.GetPendingBacklogTotal() ?? snapshot.BacklogTotal`.
   - Optionally refactor `AuditLogQueryService` to consume the provider (skip if it drags UI DI churn — the live tile already works; the *history* was the broken surface).
4. Run filter — PASS. Build the solution.
5. Commit: `git add -A src/ZB.MOM.WW.ScadaBridge.Commons src/ZB.MOM.WW.ScadaBridge.HealthMonitoring src/ZB.MOM.WW.ScadaBridge.AuditLog tests && git commit -m "fix(kpi-history): backlogTotal trend records the real site-backlog aggregate instead of a hardwired zero"`

### Task 11: SiteCalls same-rank freshness upsert (unfreeze RetryCount/LastError)

**Classification:** high-risk
**Estimated implement time:** ~5 min
**Parallelizable with:** 1, 2, 3, 4, 8, 9, 10
**Files:**
- Modify: `src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase/Repositories/SiteCallAuditRepository.cs` (rank comment ~27-40; UPDATE predicate ~117-139)
- Modify: `docs/requirements/Component-SiteCallAudit.md` (~76-78 upsert contract wording)
- Test: `tests/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.Tests/Repositories/SiteCallAuditRepositoryTests.cs` (extend; MSSQL — infra up)

1. Failing tests:

```csharp
[Fact]
public async Task Upsert_SameRank_NewerUpdatedAt_RefreshesProgressFields()
{
    var id = NewId();
    await Repo.UpsertAsync(Call(id, "Attempted", retryCount: 1, lastError: "timeout #1", updatedAt: T0));
    await Repo.UpsertAsync(Call(id, "Attempted", retryCount: 5, lastError: "timeout #5", updatedAt: T0.AddMinutes(5)));
    var row = await Repo.GetAsync(id);
    Assert.Equal(5, row!.RetryCount);
    Assert.Equal("timeout #5", row.LastError);
}

[Fact]
public async Task Upsert_SameRank_OlderUpdatedAt_IsNoOp() { /* retry 5 then out-of-order retry 2 with older stamp → stays 5 */ }

[Fact]
public async Task Upsert_LowerRank_IsStillNoOp() { /* Delivered then Attempted (newer stamp) → stays Delivered */ }
```

2. `dotnet test tests/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.Tests --filter SiteCallAuditRepositoryTests` — FAIL (same-rank is a no-op today).
3. Change the UPDATE guard from `incomingRank > CASE...` to:

```sql
AND ( {incomingRank} > (CASE Status ... END)
   OR ({incomingRank} = (CASE Status ... END) AND UpdatedAtUtc < {siteCall.UpdatedAtUtc}) )
```

(repeat the CASE expression — both branches parameterize identically). Update the class comment: "monotonic on rank; within equal rank, newest `UpdatedAtUtc` wins (still idempotent — equal stamps are a no-op — and still regression-proof)". This unfreezes `RetryCount`/`LastError`/`HttpStatus` during the Attempted phase and lets a genuinely-newer terminal correction land.
4. Run filter — PASS (all pre-existing monotonic tests must stay green).
5. Doc: Component-SiteCallAudit.md upsert paragraph — replace "first-write-wins at each rank" semantics with "insert-if-not-exists, then upsert on newer status rank, with a newest-`UpdatedAtUtc` tiebreaker within equal rank so retrying calls show live RetryCount/LastError".
6. Commit: `git add src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase/Repositories/SiteCallAuditRepository.cs docs/requirements/Component-SiteCallAudit.md tests/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.Tests && git commit -m "fix(site-call-audit): same-rank freshness tiebreaker — retrying calls no longer freeze RetryCount/LastError at first write"`

### Task 12: EF Core connection resiliency (`EnableRetryOnFailure`)

**Classification:** high-risk
**Estimated implement time:** ~5 min
**Parallelizable with:** 1, 2, 3, 8, 11 (do NOT run concurrently with any task executing MSSQL integration tests — behavior-wide change)
**Files:**
- Modify: `src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase/ServiceCollectionExtensions.cs` (~29-34)
- Modify: `src/ZB.MOM.WW.ScadaBridge.AuditLog/Central/AuditLogIngestActor.cs` (`OnCachedTelemetryAsync` dual-write transaction ~262-311)
- Test: `tests/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.Tests/ServiceCollectionExtensionsTests.cs` (extend), `tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests/Integration/CombinedTelemetryIdempotencyTests.cs` (must stay green)

1. First inventory every user-initiated transaction against `ScadaBridgeDbContext`: `grep -rn "BeginTransaction" src/ --include="*.cs"` — expect `AuditLogIngestActor.OnCachedTelemetryAsync`; treat any others found identically.
2. Failing test in `ServiceCollectionExtensionsTests`:

```csharp
[Fact]
public void AddConfigurationDatabase_Configures_RetryingExecutionStrategy()
{
    var services = new ServiceCollection();
    services.AddConfigurationDatabase("Server=unused;Database=unused;");
    using var sp = services.BuildServiceProvider();
    var options = sp.GetRequiredService<DbContextOptions<ScadaBridgeDbContext>>();
    var sqlExt = options.Extensions.OfType<SqlServerOptionsExtension>().Single();
    Assert.NotNull(sqlExt.ExecutionStrategyFactory); // retry strategy configured
}
```

3. Run: `dotnet test tests/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.Tests --filter Configures_RetryingExecutionStrategy` — FAIL.
4. Implementation:
   - `options.UseSqlServer(connectionString, sql => sql.EnableRetryOnFailure(maxRetryCount: 5, maxRetryDelay: TimeSpan.FromSeconds(30), errorNumbersToAdd: null))` — comment: covers every read-side path (KPI asks, outbox queries, `Notify.Status`, execution trees) that previously surfaced transient faults raw (arch-review 04, S5).
   - `OnCachedTelemetryAsync`: wrap the per-entry `BeginTransactionAsync` block in the execution strategy — `var strategy = context.Database.CreateExecutionStrategy(); await strategy.ExecuteAsync(async () => { await using var tx = await context.Database.BeginTransactionAsync(); ... await tx.CommitAsync(); });` — the block is already idempotent (insert-if-not-exists + monotonic upsert), so a retried unit is safe; say so in the comment.
   - Note in `SwitchOutPartitionAsync`'s comment: the raw batch carries its own server-side BEGIN TRAN/CATCH and is IF-EXISTS-guarded idempotent, so strategy-level replay of the whole batch is safe.
5. Run: the new filter → PASS; then with infra up, `dotnet test tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests --filter "CombinedTelemetry"` → PASS (proves the dual-write still commits atomically under the strategy).
6. Commit: `git add src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase/ServiceCollectionExtensions.cs src/ZB.MOM.WW.ScadaBridge.AuditLog/Central/AuditLogIngestActor.cs tests && git commit -m "fix(config-db): EnableRetryOnFailure fleet-wide + execution-strategy wrap of the combined-telemetry dual-write"`

### Task 13: Audit row for operator Retry on parked notifications

**Classification:** standard
**Estimated implement time:** ~5 min
**Parallelizable with:** 8, 9, 11, 18, 19, 21
**Files:**
- Modify: `src/ZB.MOM.WW.ScadaBridge.Commons/Messages/Notification/NotificationOutboxQueries.cs` (`RetryNotificationRequest` ~54, `DiscardNotificationRequest` ~69 — additive `string? RequestedBy = null`)
- Modify: `src/ZB.MOM.WW.ScadaBridge.NotificationOutbox/NotificationOutboxActor.cs` (`RetryAsync` ~951-977; `BuildNotifyDeliverEvent` ~675-714 gains `string? actorOverride = null`; `EmitTerminalAuditAsync` passes it for discard)
- Modify: callers that send these requests — locate with `grep -rln "RetryNotificationRequest(" src/` (expect the Central UI outbox service and/or ManagementActor) and pass the authenticated username
- Modify: `docs/requirements/Component-NotificationOutbox.md` (~128)
- Test: `tests/ZB.MOM.WW.ScadaBridge.NotificationOutbox.Tests/NotificationOutboxActorRetryEmissionTests.cs` (create; clone the audit-writer capture rig from `NotificationOutboxActorTerminalEmissionTests.cs`)

1. Failing test:

```csharp
[Fact]
public async Task Retry_OnParked_Emits_Submitted_NotifyDeliver_AuditRow_With_Operator()
{
    // seed a Parked notification; send RetryNotificationRequest(corr, id, RequestedBy: "jdoe")
    // expect RetryNotificationResponse Success
    // assert captured audit writer received exactly one event:
    //   Kind NotifyDeliver, Status Submitted, Actor "jdoe", CorrelationId == NotificationId
}

[Fact]
public async Task Retry_AuditFailure_DoesNotFail_TheRetry() { /* throwing audit writer → response still Success (audit is best-effort) */ }
```

2. `dotnet test tests/ZB.MOM.WW.ScadaBridge.NotificationOutbox.Tests --filter RetryEmission` — FAIL.
3. Implementation:
   - Add `string? RequestedBy = null` to both request records (additive-only contract evolution — existing senders compile unchanged).
   - `BuildNotifyDeliverEvent(..., string? actorOverride = null)`: `actor: actorOverride ?? SystemActor`.
   - In `RetryAsync`, after `repository.UpdateAsync(notification)`: emit `BuildNotifyDeliverEvent(notification, now, AuditStatus.Submitted, errorMessage: null, actorOverride: request.RequestedBy)` via `_auditWriter.WriteAsync` inside the same try/catch-and-log shape as `EmitTerminalAuditAsync` (audit failure never aborts the retry). `Submitted` = "operator re-queued it" — the forensic gap the review names (`Parked → Attempted → Delivered` with no record of who un-parked).
   - `DiscardAsync`: pass `request.RequestedBy` through to the terminal row's actor.
   - Plumb the username at the senders (the UI/management layer knows the authenticated user; pass `null` where no identity exists).
4. Run filter + `dotnet test tests/ZB.MOM.WW.ScadaBridge.NotificationOutbox.Tests` — PASS.
5. Doc: Component-NotificationOutbox.md ~128 — "…each transition emits the corresponding audit row: operator **Retry** emits a `Notification`-channel `NotifyDeliver` row with status `Submitted` and the operator as `Actor`; Discard emits the `Terminal` row (operator as `Actor`)."
6. Commit: `git add -A src/ZB.MOM.WW.ScadaBridge.Commons src/ZB.MOM.WW.ScadaBridge.NotificationOutbox src/ZB.MOM.WW.ScadaBridge.CentralUI src/ZB.MOM.WW.ScadaBridge.ManagementService docs/requirements/Component-NotificationOutbox.md tests/ZB.MOM.WW.ScadaBridge.NotificationOutbox.Tests && git commit -m "feat(notification-outbox): operator Retry/Discard emit audit rows with operator identity"`

### Task 14: Operator identity on the SiteCalls Retry/Discard relay

**Classification:** standard
**Estimated implement time:** ~5 min
**Parallelizable with:** 8, 9, 10, 19, 20
**Files:**
- Modify: the relay request records — locate with `grep -rn "record RetryParkedOperation\|record DiscardParkedOperation" src/ZB.MOM.WW.ScadaBridge.Commons` (additive `string? RequestedBy = null`)
- Modify: `src/ZB.MOM.WW.ScadaBridge.SiteCallAudit/SiteCallAuditActor.cs` (relay handlers — see `SiteCallRelayTests.cs` for the handler names)
- Modify: `docs/requirements/Component-SiteCallAudit.md` (relay section)
- Test: `tests/ZB.MOM.WW.ScadaBridge.SiteCallAudit.Tests/SiteCallRelayTests.cs` (extend)

1. Failing test: relay a Retry for a parked row with `RequestedBy: "jdoe"`; assert the injected central audit writer (same `ICentralAuditWriter` seam `NotificationOutboxActor` uses — confirm with `grep -rn "ICentralAuditWriter" src/ZB.MOM.WW.ScadaBridge.NotificationOutbox src/ZB.MOM.WW.ScadaBridge.Commons`) received one event: `Kind = CachedResolve`, `Status = Submitted`, `Actor = "jdoe"`, `CorrelationId = TrackedOperationId`, channel taken from the stored `SiteCalls` row's `Channel`. Run `dotnet test tests/ZB.MOM.WW.ScadaBridge.SiteCallAudit.Tests --filter SiteCallRelayTests` — FAIL.
2. Implementation: inject `ICentralAuditWriter` into `SiteCallAuditActor` (optional ctor param defaulting to null → no-op, so existing tests construct unchanged); in the Retry/Discard relay handlers, after the site relay succeeds, fetch the row (`ISiteCallAuditRepository.GetAsync`) for its `Channel`, build the event via `ScadaBridgeAuditEventFactory.Create` (mirror `BuildNotifyDeliverEvent`'s shape), write best-effort (try/catch + log — the relay outcome is authoritative, audit never aborts it). Site-side telemetry still records the state change itself; this row adds *who asked*.
3. Run filter — PASS.
4. Doc: Component-SiteCallAudit.md relay section — the relay now emits a central direct-write audit row carrying operator identity; sites remain the source of truth for the state change.
5. Commit: `git add -A src/ZB.MOM.WW.ScadaBridge.Commons src/ZB.MOM.WW.ScadaBridge.SiteCallAudit docs/requirements/Component-SiteCallAudit.md tests/ZB.MOM.WW.ScadaBridge.SiteCallAudit.Tests && git commit -m "feat(site-call-audit): relay Retry/Discard emit operator-identity audit rows"`

### Task 15: Composite keyset in the SiteCalls reconciliation pull contract (site side)

**Classification:** high-risk
**Estimated implement time:** ~5 min
**Parallelizable with:** 1, 2, 3, 8, 13
**Files:**
- Modify: `src/ZB.MOM.WW.ScadaBridge.Communication/Protos/sitestream.proto` (additive `string after_id` on the SiteCalls pull request message — locate exact message with `grep -n "PullSiteCalls" src/ZB.MOM.WW.ScadaBridge.Communication/Protos/sitestream.proto`)
- Modify: the Commons pull-request message (`grep -rn "record CachedCallReconcileRequest\|PullSiteCallsRequest" src/ZB.MOM.WW.ScadaBridge.Commons`) — additive `string? AfterId = null`
- Modify: the site-side pull handler + tracking-store query (`grep -rn "ReadSince\|PullSiteCalls" src/ZB.MOM.WW.ScadaBridge.StoreAndForward src/ZB.MOM.WW.ScadaBridge.Communication --include="*.cs"`) — order by `(UpdatedAtUtc, TrackedOperationId)`, filter `UpdatedAtUtc > since OR (UpdatedAtUtc = since AND TrackedOperationId > afterId)` (when `afterId` present; absent → legacy `>= since` behavior)
- Test: site-side handler/store tests (same project as the handler; locate via the greps above)

1. Failing test on the site store/handler: seed `batchSize + 2` rows sharing one `UpdatedAtUtc`; pull with `since = thatTimestamp, afterId = <id of last row of page 1>`; assert page 2 returns the remaining rows (today it returns the same page). Run the owning test project with `--filter <new test name>` — FAIL.
2. Implement the additive proto field (field number = next free; additive-only rule), regenerate/compile, map it through the DTO, and apply the keyset predicate + deterministic `(UpdatedAtUtc, TrackedOperationId)` ordering in the store query. Absent `AfterId` preserves the exact legacy contract (old central against new site: unchanged).
3. Run the owning project's full suite — PASS.
4. Commit: `git add -A src/ZB.MOM.WW.ScadaBridge.Communication src/ZB.MOM.WW.ScadaBridge.Commons src/ZB.MOM.WW.ScadaBridge.StoreAndForward tests && git commit -m "feat(site-call-audit): additive (UpdatedAtUtc, TrackedOperationId) keyset in the reconciliation pull contract"`

### Task 16: Central keyset cursor — un-pin the SiteCalls reconciliation loop

**Classification:** high-risk
**Estimated implement time:** ~5 min
**Parallelizable with:** 5, 8, 9, 10 (requires Task 15)
**Files:**
- Modify: `src/ZB.MOM.WW.ScadaBridge.SiteCallAudit/SiteCallAuditActor.cs` (reconciliation loop ~600-670: cursor becomes `(DateTime, string?)`; pin branch ~636-654)
- Create: `src/ZB.MOM.WW.ScadaBridge.SiteCallAudit/SiteCallReconciliationPinnedChanged.cs` (EventStream record, mirroring `SiteAuditTelemetryStalledChanged`)
- Modify: `docs/requirements/Component-SiteCallAudit.md` (reconciliation cursor paragraph)
- Test: `tests/ZB.MOM.WW.ScadaBridge.SiteCallAudit.Tests/SiteCallAuditReconciliationTests.cs` (extend)

1. Failing test: fake pull client honoring the keyset; seed `2 × batchSize` rows sharing one `UpdatedAtUtc`; drive one tick; assert **all** rows were upserted (today the tail never reconciles). Second test: a fake *legacy* client that ignores `AfterId` (returns the same page) → assert the actor publishes `SiteCallReconciliationPinnedChanged(siteId, pinned: true)` on the EventStream instead of silently logging. `dotnet test tests/ZB.MOM.WW.ScadaBridge.SiteCallAudit.Tests --filter Reconciliation` — FAIL.
2. Implement: per-site cursor `(DateTime since, string? afterId)`; after each page set `afterId` to the max `(UpdatedAtUtc, TrackedOperationId)` row's id; pass it on the next pull. Keep the no-progress detection as the legacy-site fallback, but publish the new event (and clear it with `pinned: false` when progress resumes) — the dead-end becomes a health-observable condition.
3. Run filter — PASS; run the full SiteCallAudit.Tests suite — PASS.
4. Doc: Component-SiteCallAudit.md — cursor is now a composite `(UpdatedAtUtc, TrackedOperationId)` keyset (additive contract field; legacy sites fall back to the timestamp cursor and surface `SiteCallReconciliationPinnedChanged` when pinned). Also add one line to Component-AuditLog.md's reconciliation section: the analogous keyset for the `PullAuditEvents` cursor is a tracked follow-up (same shape; lower urgency because the audit cursor already re-pulls idempotently).
5. Commit: `git add -A src/ZB.MOM.WW.ScadaBridge.SiteCallAudit docs/requirements tests/ZB.MOM.WW.ScadaBridge.SiteCallAudit.Tests && git commit -m "fix(site-call-audit): composite keyset cursor eliminates the single-timestamp reconciliation pin; pinned state now a published event"`

### Task 17: Durable record for permanently-abandoned reconciliation rows

**Classification:** standard
**Estimated implement time:** ~5 min
**Parallelizable with:** 8, 11, 19, 20, 21
**Files:**
- Modify: `src/ZB.MOM.WW.ScadaBridge.Commons/Types/Enums/AuditKind.cs` (additive `ReconciliationAbandoned` value)
- Modify: `src/ZB.MOM.WW.ScadaBridge.AuditLog/Central/SiteAuditReconciliationActor.cs` (abandonment branch ~283-293)
- Modify: `docs/requirements/Component-AuditLog.md` (kind vocabulary + reconciliation section)
- Test: `tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests/Central/SiteAuditReconciliationActorTests.cs` (extend)

1. Failing test: repository stub whose `InsertIfNotExistsAsync` throws for one specific EventId across `MaxPermanentInsertAttempts` ticks but accepts everything else; after the abandonment tick, assert a **synthetic** audit event was inserted (new EventId; `Kind = ReconciliationAbandoned`; `DetailsJson`/Extra carrying the abandoned `EventId`, the site id, and the final error) and the cursor advanced. `dotnet test tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests --filter SiteAuditReconciliationActorTests` — FAIL.
2. Implement in the `attempts >= MaxPermanentInsertAttempts` branch: alongside the existing `LogCritical`, build a minimal synthetic event (fresh `Guid.NewGuid()` EventId, `OccurredAtUtc = now`, Actor = system, Action `"Audit.ReconciliationAbandoned"`, Category preserved from the lost event where parseable, Extra = `{ abandonedEventId, sourceSiteId, error }` — use the same factory/projection helpers the ingest path uses) and `InsertIfNotExistsAsync` it in its own try/catch (the synthetic row is small and well-formed, so the row-specific failure that killed the original won't recur; if it does, log and continue — never block the cursor twice). The permanent loss is now queryable in the audit log itself, not only in a rotating log file.
3. Run filter — PASS.
4. Doc: add `ReconciliationAbandoned` to the kind vocabulary in Component-AuditLog.md (Task 24 updates the Commons enum listing — coordinate: this task adds the value, 24 fixes the counts).
5. Commit: `git add -A src/ZB.MOM.WW.ScadaBridge.Commons src/ZB.MOM.WW.ScadaBridge.AuditLog docs/requirements/Component-AuditLog.md tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests && git commit -m "feat(audit-log): permanently-abandoned reconciliation rows leave a durable ReconciliationAbandoned audit record"`

### Task 18: Reconciliation NodeB failover (owned from report 08)

**Classification:** standard
**Estimated implement time:** ~5 min
**Parallelizable with:** 9, 11, 13, 19, 20, 21
**Files:**
- Modify: `src/ZB.MOM.WW.ScadaBridge.AuditLog/Central/ISiteEnumerator.cs` (`SiteEntry` record ~37: additive `string? FallbackGrpcEndpoint = null`; fix the stale "NodeA-only first cut" doc comment)
- Modify: `src/ZB.MOM.WW.ScadaBridge.AuditLog/Central/SiteEnumerator.cs` (~60-75: populate from `GrpcNodeBAddress`; include sites with blank NodeA but valid NodeB, primary = NodeA else NodeB)
- Modify: `src/ZB.MOM.WW.ScadaBridge.AuditLog/Central/GrpcPullAuditEventsClient.cs` + `GrpcPullSiteCallsClient.cs` (retry-once-on-fallback)
- Modify: `docs/requirements/Component-AuditLog.md` (reconciliation endpoint-resolution paragraph)
- Test: `tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests/Central/SiteEnumeratorTests.cs`, `GrpcPullAuditEventsClientTests.cs`, `GrpcPullSiteCallsClientTests.cs` (extend)

1. Failing tests: (a) `SiteEnumeratorTests` — site with both addresses yields `SiteEntry(id, nodeA, FallbackGrpcEndpoint: nodeB)`; site with only NodeB is no longer skipped; (b) `GrpcPullAuditEventsClientTests` — fake invoker throws `Unavailable` for the primary endpoint and succeeds for the fallback → the pull returns the fallback's events (today: collapses to empty). `dotnet test tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests --filter "SiteEnumeratorTests|GrpcPull"` — FAIL.
2. Implement: in both pull clients' transport-fault catch (the `Unavailable`/`DeadlineExceeded`/`Cancelled`/`HttpRequestException` set already enumerated there), if `FallbackGrpcEndpoint` is non-blank and differs from the primary, invoke once against it before collapsing to empty; log the failover at Information. The invoker's per-endpoint channel cache already handles the second endpoint.
3. Run filter + full Central test folder — PASS.
4. Doc: Component-AuditLog.md — reconciliation dials NodeA first, fails over to NodeB per call; during a NodeA outage the loss-recovery safety net stays available.
5. Commit: `git add -A src/ZB.MOM.WW.ScadaBridge.AuditLog docs/requirements/Component-AuditLog.md tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests && git commit -m "fix(audit-log): reconciliation pulls fail over to site NodeB when NodeA is unreachable"`

### Task 19: Batch the KpiSample purge

**Classification:** standard
**Estimated implement time:** ~4 min
**Parallelizable with:** 1, 2, 3, 8, 9, 13, 18
**Files:**
- Modify: `src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase/Repositories/KpiHistoryRepository.cs` (`PurgeOlderThanAsync` ~65-71)
- Modify: `src/ZB.MOM.WW.ScadaBridge.Commons/Interfaces/Repositories/IKpiHistoryRepository.cs` (doc update; signature unchanged)
- Test: `tests/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.Tests/Repositories/KpiHistoryRepositoryTests.cs` (extend)

1. Failing test: seed samples spread across 5 days; purge with a 2-day-old cutoff; assert the correct rows are gone and the return value equals the total deleted — then assert via an EF command interceptor (or SQL capture, matching how sibling tests inspect SQL) that **more than one** DELETE statement was issued for a multi-window purge. Run `dotnet test tests/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.Tests --filter KpiHistoryRepositoryTests` — FAIL (single `ExecuteDeleteAsync` today).
2. Implement provider-portable time-slice batching (avoids `DELETE TOP` dialect divergence — the convention Task 24 documents):

```csharp
public async Task<int> PurgeOlderThanAsync(DateTime before, CancellationToken cancellationToken = default)
{
    // Time-sliced batches: each DELETE covers at most one hour of samples, capping the
    // lock/log footprint per statement (arch-review 04, P4 — steady state is ~1 day of
    // rows/day; after an outage the catch-up would otherwise be one giant transaction).
    var total = 0;
    var floor = await _context.KpiSamples.Where(s => s.CapturedAtUtc < before)
        .MinAsync(s => (DateTime?)s.CapturedAtUtc, cancellationToken);
    while (floor is not null && floor < before)
    {
        var ceiling = floor.Value.AddHours(1) < before ? floor.Value.AddHours(1) : before;
        total += await _context.KpiSamples
            .Where(s => s.CapturedAtUtc < ceiling)
            .ExecuteDeleteAsync(cancellationToken);
        floor = await _context.KpiSamples.Where(s => s.CapturedAtUtc < before)
            .MinAsync(s => (DateTime?)s.CapturedAtUtc, cancellationToken);
    }
    return total;
}
```

3. Run filter — PASS; run `dotnet test tests/ZB.MOM.WW.ScadaBridge.KpiHistory.Tests` (recorder actor contract unchanged) — PASS.
4. Commit: `git add src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase/Repositories/KpiHistoryRepository.cs src/ZB.MOM.WW.ScadaBridge.Commons/Interfaces/Repositories/IKpiHistoryRepository.cs tests/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.Tests && git commit -m "perf(kpi-history): time-sliced batched purge replaces the single daily mega-DELETE"`

### Task 20: NotificationOutbox KPI — single-query aggregation, bounded oldest

**Classification:** standard
**Estimated implement time:** ~5 min
**Parallelizable with:** 1, 2, 3, 8, 9, 14, 18, 19
**Files:**
- Modify: `src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase/Repositories/NotificationOutboxRepository.cs` (`ComputeKpisAsync` ~239-283, `ComputePerSiteKpisAsync` ~286-334, `ComputePerNodeKpisAsync` ~337-391)
- Test: `tests/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.Tests/NotificationOutboxRepositoryPerSiteKpiTests.cs`, `...PerNodeKpiTests.cs`, and the global KPI tests (all must stay green — this is a pure refactor of query shape)

1. Add one new failing test capturing the *bounded-oldest* behavior: seed 3 non-terminal rows; assert the oldest-pending computation issues no query that materializes more than 1 row (assert via command interceptor / captured SQL: the oldest query is `ORDER BY … OFFSET 0 ROWS FETCH NEXT 1` or `TOP(1)`, not a full non-terminal SELECT). Run `dotnet test tests/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.Tests --filter NotificationOutboxRepository` — FAIL (per-site/per-node paths materialize every non-terminal row today, ~lines 312-318, 368-375).
2. Refactor each snapshot to:
   - **Counts:** one conditional-aggregation query per scope — `GroupBy(n => 1)` (global) / `GroupBy(n => n.SourceSiteId)` / `GroupBy(n => n.SourceNode)` with `g.Count(n => <predicate>)` per metric, replacing the 5-7 sequential round trips.
   - **Oldest pending:** global — `Where(nonTerminal).OrderBy(n => n.CreatedAt).Select(n => (DateTimeOffset?)n.CreatedAt).FirstOrDefaultAsync()`; per-site/per-node — try server-side `GroupBy(...).Select(g => new { g.Key, Oldest = g.Min(n => n.CreatedAt) })` first; if the `DateTimeOffset` converter blocks translation (the review's noted awkwardness), fall back to projecting only `(Key, CreatedAt)` pairs of non-terminal rows (narrow projection, no entity materialization) and reduce in memory — either way the full-entity materialization is gone.
3. Run the full NotificationOutboxRepository test set + `dotnet test tests/ZB.MOM.WW.ScadaBridge.NotificationOutbox.Tests --filter Kpi` — PASS (identical snapshot values).
4. Commit: `git add src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase/Repositories/NotificationOutboxRepository.cs tests/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.Tests && git commit -m "perf(notification-outbox): single-query KPI aggregation; oldest-pending no longer materializes the live queue"`

### Task 21: `OPTION (RECOMPILE)` on the SiteCalls catch-all query

**Classification:** small
**Estimated implement time:** ~2 min
**Parallelizable with:** 1, 2, 3, 8, 9, 13, 18, 19, 20 (NOT 11 — same file)
**Files:**
- Modify: `src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase/Repositories/SiteCallAuditRepository.cs` (`QueryAsync` SQL ~186-202)
- Test: `tests/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.Tests/Repositories/SiteCallAuditRepositoryTests.cs` (existing query tests must pass)

1. Append `OPTION (RECOMPILE)` after the `ORDER BY` line of the `FormattableString` (with a comment: every filter is the `(@p IS NULL OR col=@p)` optional-parameter shape; RECOMPILE lets the optimizer prune dead predicates per invocation and pick `IX_SiteCalls_Status_Updated`/`IX_SiteCalls_NonTerminal` — arch-review 04, P5; per-invocation compile cost is fine at UI-page cadence).
2. With infra up: `dotnet test tests/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.Tests --filter SiteCallAuditRepositoryTests` — PASS.
3. Commit: `git add src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase/Repositories/SiteCallAuditRepository.cs && git commit -m "perf(site-call-audit): OPTION (RECOMPILE) on the optional-parameter query page"`

### Task 22: Short first-tick delay on the three purge timers

**Classification:** small
**Estimated implement time:** ~3 min
**Parallelizable with:** 1, 2, 6, 8, 19, 20, 21 (NOT 4/5 — AuditLogPurgeActor; NOT 16 — SiteCallAuditActor)
**Files:**
- Modify: `src/ZB.MOM.WW.ScadaBridge.AuditLog/Central/AuditLogPurgeActor.cs` (`PreStart` ~95-100)
- Modify: `src/ZB.MOM.WW.ScadaBridge.SiteCallAudit/SiteCallAuditActor.cs` (`StartPurgeTimer` ~374-387)
- Modify: `src/ZB.MOM.WW.ScadaBridge.KpiHistory/KpiHistoryRecorderActor.cs` (purge timer ~124-128)
- Test: extend each actor's existing test file with one fast-first-tick case

1. Failing test (one per actor, using each suite's existing scheduler/interval-override rig): configure a long interval (24h) and assert a purge tick arrives within the short initial delay window rather than never.
2. Implement uniformly: `var initialDelay = interval < ShortFirstTick ? interval : ShortFirstTick;` with `private static readonly TimeSpan ShortFirstTick = TimeSpan.FromMinutes(5);` and a shared comment: purges are idempotent; `initialDelay = interval` meant a node recycled daily would never purge (arch-review 04, S9). Taking `min(interval, 5min)` keeps millisecond test cadences unchanged.
3. Run: `dotnet test tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests --filter AuditLogPurgeActorTests && dotnet test tests/ZB.MOM.WW.ScadaBridge.SiteCallAudit.Tests --filter Purge && dotnet test tests/ZB.MOM.WW.ScadaBridge.KpiHistory.Tests` — PASS.
4. Commit: `git add -A src/ZB.MOM.WW.ScadaBridge.AuditLog src/ZB.MOM.WW.ScadaBridge.SiteCallAudit src/ZB.MOM.WW.ScadaBridge.KpiHistory tests && git commit -m "fix(purge): short first tick on all three purge timers so daily-recycled nodes still purge"`

### Task 23: Low-severity cleanup batch (param chunking, KPI catalog, computed-column guard)

**Classification:** small
**Estimated implement time:** ~5 min
**Parallelizable with:** 4, 5, 8, 9, 11, 20
**Files:**
- Modify: `src/ZB.MOM.WW.ScadaBridge.AuditLog/Site/SqliteAuditWriter.cs` (`MarkForwardedAsync` ~644-664, `MarkReconciledAsync` ~735-753)
- Modify: `src/ZB.MOM.WW.ScadaBridge.Commons/Types/Kpi/KpiMetrics.cs` (locate: `grep -rn "class KpiMetrics" src/ZB.MOM.WW.ScadaBridge.Commons`)
- Modify: `src/ZB.MOM.WW.ScadaBridge.NotificationOutbox/Kpi/NotificationOutboxKpiSampleSource.cs` (~40-43)
- Modify: `src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase/Configurations/AuditLogEntityTypeConfiguration.cs` (~158-164)
- Test: `tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests/Site/SqliteAuditWriterWriteTests.cs` (extend), `tests/ZB.MOM.WW.ScadaBridge.NotificationOutbox.Tests/Kpi` (extend)

1. **P8 — parameter chunking:** failing test — write 1200 events, `MarkForwardedAsync(all 1200 ids)`, assert all flip to Forwarded (today: one statement with 1200+ parameters risks/violates SQLite's 999 limit as batch sizes grow). Implement: chunk `eventIds` into slices of 500 inside the existing `_writeLock` (one transaction around all chunks so the batch stays atomic); same in `MarkReconciledAsync`. Run `dotnet test tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests --filter SqliteAuditWriter` — PASS.
2. **C4 — KPI metric catalog:** add `KpiMetrics.NotificationOutbox.StuckCount = "stuckCount"` and `OldestPendingAgeSeconds = "oldestPendingAgeSeconds"` (EXACT existing string values — persisted, renames forbidden); replace the inline literals in `NotificationOutboxKpiSampleSource` with the constants; add a test asserting the constant values equal the historical strings.
3. **P7 — computed-column guard:** extend the `IngestedAtUtc` comment in `AuditLogEntityTypeConfiguration`: "NEVER use IngestedAtUtc in a WHERE predicate — non-persisted, it forces a full-table `JSON_VALUE` parse; read-side projection only (arch-review 04, P7)."
4. Run both test filters + build — PASS.
5. Commit: `git add -A src/ZB.MOM.WW.ScadaBridge.AuditLog src/ZB.MOM.WW.ScadaBridge.Commons src/ZB.MOM.WW.ScadaBridge.NotificationOutbox src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase tests && git commit -m "chore(data-layer): low-severity cleanups — SQLite param chunking, KPI metric catalog, computed-column predicate guard"`

### Task 24: Design-doc reconciliation pass

**Classification:** small
**Estimated implement time:** ~5 min
**Parallelizable with:** all code tasks except 7 (same files); run AFTER 17 (AuditKind gains a value)
**Files:**
- Modify: `docs/requirements/Component-Commons.md` (~46-47, ~89)
- Modify: `docs/requirements/Component-ConfigurationDatabase.md` (~58; timestamp-convention + clustered-key + repository-dialect notes)
- Modify: `docs/requirements/Component-SiteCallAudit.md` (~44-51 schema section)
- Modify: `docs/requirements/Component-AuditLog.md` (~396-399 per-channel config channel list)
- Modify: `docs/requirements/Component-NotificationOutbox.md` (~23 "dedicated blocking-I/O dispatcher" phrasing — align with Task 25 if it has landed, else describe the actual thread-pool off-actor delivery)

Concrete edits (each verifiable against the code cited in the review):
1. Component-Commons.md ~47: `AuditKind` list → the current enum (`grep -A20 "enum AuditKind" src/ZB.MOM.WW.ScadaBridge.Commons/Types/Enums/AuditKind.cs`): the original 10 + `SecuredWriteSubmitted/Approved/Rejected/Executed` (4) + `ReconciliationAbandoned` (Task 17). ~46: `AuditChannel` → 5 values incl. `SecuredWrite`.
2. Component-Commons.md ~89: `NotificationType` → "`Email`, `Sms` (shipped 2026-06-19; the Teams plan was evaluated and dropped)".
3. Component-ConfigurationDatabase.md ~58: NotificationList Type discriminator → "`Email` / `Sms`".
4. Component-ConfigurationDatabase.md ~399: "single global value in v1, no per-channel overrides" → per-channel `AuditLog:PerChannelRetentionDays` overrides exist (skip if Task 7 already fixed it).
5. Component-SiteCallAudit.md ~44-51: rewrite the schema table to the shipped columns (`Channel`/`Target`/`AuditStatus`-derived `Status` strings, `SourceNode`, no provenance columns) — align with `SiteCallEntityTypeConfiguration.cs` and Component-ConfigurationDatabase.md:64 (the current source of truth per the review).
6. Component-AuditLog.md ~396-399: per-channel retention config enumerates all five channels including `SecuredWrite` (the validator already accepts it — doc under-promised).
7. Component-Commons.md / Component-ConfigurationDatabase.md: document the `AuditLogRow` deviation (persistence-only projection of the external `ZB.MOM.WW.Audit.AuditEvent` contract — deliberately not a Commons POCO) and two conventions: **new tables use UTC `DateTime` + the Utc converter** (the `DateTimeOffset` choice on `Notification` is the documented exception that forced in-memory Min workarounds), and **new repositories write MSSQL-only SQL with MSSQL-backed test fixtures** (the NotificationOutboxRepository dual SQLite/T-SQL dialect is a documented legacy exception — finding S8). Record two tracked follow-ups: the AuditLog clustered-key `(OccurredAtUtc, EventId)` benchmark question (P3) and the per-node KPI sampling cadence / outbox keyset-paging deferrals (P4/P6).
8. Verify: `grep -rn "Teams" docs/requirements/ | grep -iv "dropped\|evaluated"` returns nothing load-bearing; `grep -n "no per-channel" docs/requirements/Component-ConfigurationDatabase.md` returns nothing.
9. Commit: `git add docs/requirements && git commit -m "docs: reconcile design docs with shipped code (enums, Sms, per-channel retention, SiteCalls schema, conventions)"`

### Task 25: Bounded parallel notification delivery

**Classification:** high-risk
**Estimated implement time:** ~5 min
**Parallelizable with:** 8, 9, 11, 18, 19 (NOT 13 — same actor file; do 13 first)
**Files:**
- Modify: `src/ZB.MOM.WW.ScadaBridge.NotificationOutbox/NotificationOutboxOptions.cs` (add `MaxParallelDeliveries`)
- Modify: `src/ZB.MOM.WW.ScadaBridge.NotificationOutbox/NotificationOutboxActor.cs` (`RunDispatchPass` ~321-347)
- Modify: `docs/requirements/Component-NotificationOutbox.md` (~23 dispatcher paragraph)
- Test: `tests/ZB.MOM.WW.ScadaBridge.NotificationOutbox.Tests/NotificationOutboxActorDispatchTests.cs` (extend)

1. Failing test: adapter stub whose `Deliver` blocks on a gate and records concurrent-invocation high-water mark; enqueue 8 pending notifications; with `MaxParallelDeliveries = 4` assert high-water ≥ 2 and all 8 reach terminal status; with `MaxParallelDeliveries = 1` assert high-water == 1 (existing sequential behavior preserved). `dotnet test tests/ZB.MOM.WW.ScadaBridge.NotificationOutbox.Tests --filter Dispatch` — FAIL.
2. Implement: `MaxParallelDeliveries` (default **4**, clamp min 1 — the review's alarm-storm scenario: sequential 5 s SMTP attempts cap throughput at ~0.2/s regardless of batch size). In `RunDispatchPass`, replace the sequential foreach with `SemaphoreSlim(maxParallel)` + `Task.WhenAll`, giving **each notification its own DI scope/repository** (rows are claimed per-notification, so no shared-context concurrency; the existing in-flight pass guard is untouched). Audit emission stays per-notification inside its task.
3. Run the full outbox suite: `dotnet test tests/ZB.MOM.WW.ScadaBridge.NotificationOutbox.Tests` — PASS (attempt/terminal emission tests prove per-row semantics survived).
4. Doc: Component-NotificationOutbox.md dispatcher section — delivery runs off the actor thread on the thread pool with bounded per-sweep parallelism (`MaxParallelDeliveries`, default 4); drop the inaccurate "dedicated blocking-I/O dispatcher" phrasing (doc drift the review flagged).
5. Commit: `git add -A src/ZB.MOM.WW.ScadaBridge.NotificationOutbox docs/requirements/Component-NotificationOutbox.md tests/ZB.MOM.WW.ScadaBridge.NotificationOutbox.Tests && git commit -m "perf(notification-outbox): bounded parallel delivery within a dispatch sweep (default 4)"`

---

## Dependencies on other plans

- **Plan 02 (Communication/S&F)** owns the S&F retry-sweep batching — Task 15's site-side tracking-store query touches adjacent code in the StoreAndForward project; coordinate file ownership if both run concurrently.
- **Plan 07 (UI/Management/Security)** owns SecuredWrite emission paths — the `SourceNode`-NULL follow-up (U4) stays with them; Task 24 only fixes the doc under-promise.
- **Plan 08 (Conventions/Tests)** flagged the reconciliation NodeB gap; it is implemented here (Task 18) — plan 08 should not duplicate it.
- Tasks 13/14 plumb `RequestedBy` through UI/ManagementActor senders — if plan 07 refactors those senders simultaneously, land this plan's additive message change first (defaults keep old senders compiling).
- Migration serialization is **intra-plan only** (Tasks 6 → 8); no other plan currently adds ConfigurationDatabase migrations, but if one does, coordinate snapshot ordering.

## Execution order

**P0 (the operational time bombs — do first, in this order):** 1 → 2 → 3 (site purge chain), 4 → 5 (partition-switch hardening), 6 → 8 (migrations, serialized), 9, 10.
**P1:** 11, 12, 13, 15 → 16, 17, 18.
**P2 (hardening/cleanup):** 14, 19, 20, 21, 22, 23, 25, then 7 and 24 (docs last so they describe what actually landed).
Intra-plan critical path: **1 → 2 → 3** and **15 → 16**; everything else is broadly parallelizable per each task's "Parallelizable with" contract. Finish with a full `dotnet build ZB.MOM.WW.ScadaBridge.slnx && dotnet test ZB.MOM.WW.ScadaBridge.slnx` (infra compose up for the MSSQL suites) before declaring the plan done.
