docs(localdb): Phase 2 gate + final Phase 1 task record

Task 14. A GATE document, not an implementation plan - the plan explicitly wants
Phase 2 planned against post-Phase-1 reality, which means after this record
exists rather than from the original design alone.

Records what Phase 1 actually established (ordering constraints, where
registration lives and why, the fail-closed interceptor, the silent meter
allowlist), what must be ported before anything is deleted (both bespoke
replicator test files, treated as specifications, plus the accepted N5
bounded-duplicate race), and why Phase 2 is a harder risk class than Phase 1:
it REPLACES a working mechanism rather than adding one where none existed,
sf_messages has external side effects, config tables drive deployment, there is
no dual-mechanism period, and the migration is real this time rather than the
usual no-op.

The most important line is an open question, not an answer: choosing
MaxOplogRows / MaxOplogAge / snapshot-resync thresholds for sf_messages write
rates needs a Phase-1 rig SOAK that has not been run. The live gate proved
correctness, not steady-state behaviour over time. Phase 2 should not be planned
without it.

tasks.json finalized: all 15 tasks completed, every deviation from the plan
recorded with its reason.

Claude-Session: https://claude.ai/code/session_01BL2Vu1ESDQ9SCN4gVKkdts

CLAUDE.md

@@ -120,6 +120,12 @@ Other peers in the `scadaproj` family (see `scadaproj/CLAUDE.md` for details): `
 - DCL write failures returned synchronously to calling script.
 - Tag path resolution retried periodically for devices still booting.
 - Static attribute writes persisted to local SQLite (survive restart/failover, reset on redeployment).
+- **Consolidated site database (LocalDb Phase 1, 2026-07-19).** `OperationTracking` and `site_events` now live in ONE `ZB.MOM.WW.LocalDb`-managed SQLite file, configured by the **required** `LocalDb:Path` (`/app/data/site-localdb.db` on the rig; validated with `ValidateOnStart`, so a site config missing it fails to boot). Both are `RegisterReplicated` tables. Consequences worth knowing:
+  - `site_events.id` changed from autoincrement INTEGER to an application-minted **GUID**. Last-writer-wins keys on the primary key, so two nodes independently minting `id=1,2,3…` would destroy each other's events rather than merge them. The event-log read path uses a composite `(timestamp, id)` keyset cursor with an **opaque string** continuation token; `EventLogEntry.Id` and both `ContinuationToken`s are `string`/`string?` on the site↔central Akka DTOs.
+  - `ScadaBridge:OperationTracking:ConnectionString` and `ScadaBridge:SiteEventLog:DatabasePath` are **migration-only** — nothing reads them but `SiteLocalDbLegacyMigrator`, which copies a pre-Phase-1 file in once (deterministic `mig-{NodeName}-{legacyId}` event ids, `INSERT OR IGNORE`, runs AFTER `RegisterReplicated` so migrated rows replicate) and renames it `.migrated`. Delete the keys once a node has migrated.
+  - This incidentally fixes a data-loss bug: both legacy databases defaulted to CWD-relative paths **outside** the mounted volume and were discarded on every container recreate.
+  - **Replication is default-OFF and opt-in** via `LocalDb:Replication:PeerAddress` + a matching `ApiKey` on both nodes. `LocalDbSyncAuthInterceptor` is **fail-closed**: no configured key means no sync stream is accepted at all, so a key typo does not degrade to unauthenticated replication — the pair simply stops converging. The sync endpoint shares the existing site gRPC h2c listener (8083); no new port. Rig posture: **site-a replicated, site-b/site-c deliberately not**, so both states are proven side-by-side. Status surfaces on the site health report as `LocalDbReplicationConnected` / `LocalDbOplogBacklog` (both nullable — null means "no data", NOT "disconnected with an empty backlog") and as `localdb_*` Prometheus series. Note `ZbTelemetryOptions.Meters` is an **allowlist** (`SiteServiceRegistration.ObservedMeters`); an unlisted meter exports nothing, silently.
+  - Design: **scadaproj** `docs/plans/2026-07-19-scadabridge-localdb-design.md` (that doc lives in the umbrella repo, not here); Phase 1 plan + Phase 2 gate are here under `docs/plans/`. Phase 2 (config tables + `sf_messages`, deleting `SiteReplicationActor` + StoreAndForward `ReplicationService`) is NOT started — see `docs/plans/2026-07-19-localdb-phase2-gate.md`.
 - All timestamps are UTC throughout the system.
 - Inter-cluster communication uses two transports: ClusterClient for command/control (deployments, lifecycle, subscribe/unsubscribe handshake, snapshots) and gRPC server-streaming for real-time data (attribute values, alarm states). Both CentralCommunicationActor and SiteCommunicationActor registered with receptionist. Central creates one ClusterClient per site using NodeA/NodeB as contact points. Sites configure multiple central contact points for failover. Addresses cached in CentralCommunicationActor, refreshed periodically (60s) and on admin changes. Heartbeats serve health monitoring only.
 - gRPC streaming channel: SiteStreamGrpcServer on each site node (Kestrel HTTP/2, port 8083); central creates per-site SiteStreamGrpcClient via SiteStreamGrpcClientFactory. Site entity has GrpcNodeAAddress/GrpcNodeBAddress fields. Proto: sitestream.proto with SiteStreamService, SiteStreamEvent (oneof: AttributeValueUpdate, AlarmStateUpdate). DebugStreamEvent message removed (no longer flows through ClusterClient).

Directory.Packages.props

@@ -15,10 +15,28 @@
     <PackageVersion Include="bunit" Version="2.0.33-preview" />
     <PackageVersion Include="coverlet.collector" Version="6.0.4" />
     <PackageVersion Include="FluentAssertions" Version="8.3.0" />
-    <PackageVersion Include="Google.Protobuf" Version="3.29.3" />
+    <!--
-    <PackageVersion Include="Grpc.AspNetCore" Version="2.71.0" />
+      gRPC stack raised 2.71.0 -> 2.76.0 (Protobuf 3.29.3 -> 3.34.1) on 2026-07-19.
-    <PackageVersion Include="Grpc.Net.Client" Version="2.71.0" />
+
-    <PackageVersion Include="Grpc.Tools" Version="2.71.0" />
+      Forced by ZB.MOM.WW.LocalDb.Replication 0.1.0, whose nuspec floors Grpc.AspNetCore,
+      Grpc.Net.Client, Grpc.Core.Api and Grpc.Tools at 2.76.0 and Google.Protobuf at 3.34.1.
+      Below that floor restore fails NU1605 (package downgrade), so this is not optional for
+      LocalDb adoption.
+
+      The SQLitePCLRaw note below deferred exactly this bump as belonging in "their own
+      reviewed commit" rather than smuggled under a security fix - so it IS its own commit.
+      Direct consumer surface is narrow: Grpc.AspNetCore only in Host, Grpc.Net.Client only
+      in Communication. Grpc.Core.Api is pinned explicitly here to keep the whole family on
+      one version rather than letting it float in transitively.
+
+      Not bumped here: Microsoft.Data.SqlClient and Newtonsoft.Json, also named in that note.
+      Nothing forces them, and they are a data-access change with its own risk profile.
+    -->
+    <PackageVersion Include="Google.Protobuf" Version="3.34.1" />
+    <PackageVersion Include="Grpc.AspNetCore" Version="2.76.0" />
+    <PackageVersion Include="Grpc.Net.Client" Version="2.76.0" />
+    <PackageVersion Include="Grpc.Core.Api" Version="2.76.0" />
+    <PackageVersion Include="Grpc.Tools" Version="2.76.0" />
     <PackageVersion Include="MailKit" Version="4.16.0" />
     <PackageVersion Include="Microsoft.AspNetCore.Authentication.JwtBearer" Version="10.0.7" />
     <PackageVersion Include="Microsoft.AspNetCore.Authorization" Version="10.0.7" />
@@ -86,10 +104,13 @@
     <PackageVersion Include="ZB.MOM.WW.Auth.AspNetCore" Version="0.1.5" />
     <PackageVersion Include="ZB.MOM.WW.Audit" Version="0.1.0" />
     <PackageVersion Include="ZB.MOM.WW.Theme" Version="0.3.1" />
-    <PackageVersion Include="ZB.MOM.WW.Secrets" Version="0.2.1" />
+    <PackageVersion Include="ZB.MOM.WW.Secrets" Version="0.2.3" />
-    <PackageVersion Include="ZB.MOM.WW.Secrets.Abstractions" Version="0.2.1" />
+    <PackageVersion Include="ZB.MOM.WW.Secrets.Abstractions" Version="0.2.3" />
-    <PackageVersion Include="ZB.MOM.WW.Secrets.Ui" Version="0.2.1" />
+    <PackageVersion Include="ZB.MOM.WW.Secrets.Ui" Version="0.2.3" />
-    <PackageVersion Include="ZB.MOM.WW.Secrets.Replicator.SqlServer" Version="0.2.1" />
+    <PackageVersion Include="ZB.MOM.WW.Secrets.Replicator.SqlServer" Version="0.2.3" />
+    <PackageVersion Include="ZB.MOM.WW.LocalDb" Version="0.1.0" />
+    <PackageVersion Include="ZB.MOM.WW.LocalDb.Replication" Version="0.1.0" />
+    <PackageVersion Include="ZB.MOM.WW.LocalDb.Contracts" Version="0.1.0" />
   </ItemGroup>
 
   <!--
@@ -124,4 +145,19 @@
     <PackageVersion Include="SQLitePCLRaw.lib.e_sqlite3" Version="2.1.12" />
   </ItemGroup>
 
+  <!--
+    GHSA-pgww-w46g-26qg (NU1902, moderate) on AngleSharp, reached only transitively via bunit
+    in ZB.MOM.WW.ScadaBridge.CentralUI.Tests. With TreatWarningsAsErrors it made the WHOLE
+    SOLUTION BUILD RED, so no suite could run and every "0 warnings / green" gate was
+    unverifiable.
+
+    Resolved with a scoped NuGetAuditSuppress in that ONE test project (see its csproj for the
+    full rationale) — deliberately NOT a version pin here. A pin was tried first and reverted:
+    AngleSharp >= 1.5.0 is the only patched line, and it changes IHtmlCollection<T>'s indexer,
+    which makes every bunit build throw MissingMethodException at runtime (33 render tests
+    failed). Bumping bunit does not help either — even 2.7.2 resolves AngleSharp 1.4.0, which
+    is still vulnerable. There is no working patched combination upstream today.
+  -->
+
+
 </Project>

docker-env2/site-x-node-a/appsettings.Site.json

@@ -56,5 +56,13 @@
     "Logging": {
       "MinimumLevel": "Information"
     }
+  },
+  // Consolidated site database (LocalDb Phase 1): OperationTracking + site_events.
+  // On the mounted /app/data volume so it survives container recreate - unlike the
+  // legacy site-tracking.db / site_events.db, which defaulted to CWD-relative paths
+  // outside the volume and were lost on every recreate.
+  // Replication is opt-in and configured separately; absent = local-only.
+  "LocalDb": {
+    "Path": "/app/data/site-localdb.db"
   }
 }

docker-env2/site-x-node-b/appsettings.Site.json

@@ -56,5 +56,13 @@
     "Logging": {
       "MinimumLevel": "Information"
     }
+  },
+  // Consolidated site database (LocalDb Phase 1): OperationTracking + site_events.
+  // On the mounted /app/data volume so it survives container recreate - unlike the
+  // legacy site-tracking.db / site_events.db, which defaulted to CWD-relative paths
+  // outside the volume and were lost on every recreate.
+  // Replication is opt-in and configured separately; absent = local-only.
+  "LocalDb": {
+    "Path": "/app/data/site-localdb.db"
   }
 }

docker/site-a-node-a/appsettings.Site.json

@@ -57,5 +57,30 @@
     "Logging": {
       "MinimumLevel": "Information"
     }
+  },
+  // Consolidated site database (LocalDb Phase 1): OperationTracking + site_events.
+  // On the mounted /app/data volume so it survives container recreate - unlike the
+  // legacy site-tracking.db / site_events.db, which defaulted to CWD-relative paths
+  // outside the volume and were lost on every recreate.
+  // Replication is opt-in and configured separately; absent = local-only.
+  "LocalDb": {
+    "Path": "/app/data/site-localdb.db",
+    // Site-a is the rig's REPLICATED pair; site-b and site-c deliberately stay
+    // unreplicated so the default-OFF posture is proven side-by-side on one rig.
+    //
+    // This node is the initiator: it dials the peer. Replication is still
+    // bidirectional - the passive node's writes flow back over the same stream -
+    // so only one side needs PeerAddress. Port 8083 is the existing site gRPC
+    // listener (h2c); the sync endpoint shares it, no new port.
+    //
+    // The ApiKey must be IDENTICAL on both nodes: the host's
+    // LocalDbSyncAuthInterceptor is fail-closed, so a mismatch (or a missing key)
+    // rejects every sync stream. A plain dev key here matches the rig's existing
+    // posture; PRODUCTION uses a "${secret:...}" reference resolved by the
+    // pre-host secret expander.
+    "Replication": {
+      "PeerAddress": "http://scadabridge-site-a-b:8083",
+      "ApiKey": "dev-site-a-localdb-sync-key"
+    }
   }
 }

docker/site-a-node-b/appsettings.Site.json

@@ -57,5 +57,23 @@
     "Logging": {
       "MinimumLevel": "Information"
     }
+  },
+  // Consolidated site database (LocalDb Phase 1): OperationTracking + site_events.
+  // On the mounted /app/data volume so it survives container recreate - unlike the
+  // legacy site-tracking.db / site_events.db, which defaulted to CWD-relative paths
+  // outside the volume and were lost on every recreate.
+  // Replication is opt-in and configured separately; absent = local-only.
+  "LocalDb": {
+    "Path": "/app/data/site-localdb.db",
+    // The PASSIVE half of site-a's replicated pair: no PeerAddress, so this node's
+    // sync initiator starts and idles while node-a dials in. Its own writes still
+    // reach node-a - the stream is bidirectional.
+    //
+    // The key MUST match node-a's exactly. LocalDbSyncAuthInterceptor is
+    // fail-closed, so a typo here does not degrade to unauthenticated replication;
+    // it rejects every stream and the pair silently stops converging.
+    "Replication": {
+      "ApiKey": "dev-site-a-localdb-sync-key"
+    }
   }
 }

docker/site-b-node-a/appsettings.Site.json

@@ -57,5 +57,13 @@
     "Logging": {
       "MinimumLevel": "Information"
     }
+  },
+  // Consolidated site database (LocalDb Phase 1): OperationTracking + site_events.
+  // On the mounted /app/data volume so it survives container recreate - unlike the
+  // legacy site-tracking.db / site_events.db, which defaulted to CWD-relative paths
+  // outside the volume and were lost on every recreate.
+  // Replication is opt-in and configured separately; absent = local-only.
+  "LocalDb": {
+    "Path": "/app/data/site-localdb.db"
   }
 }

docker/site-b-node-b/appsettings.Site.json

@@ -57,5 +57,13 @@
     "Logging": {
       "MinimumLevel": "Information"
     }
+  },
+  // Consolidated site database (LocalDb Phase 1): OperationTracking + site_events.
+  // On the mounted /app/data volume so it survives container recreate - unlike the
+  // legacy site-tracking.db / site_events.db, which defaulted to CWD-relative paths
+  // outside the volume and were lost on every recreate.
+  // Replication is opt-in and configured separately; absent = local-only.
+  "LocalDb": {
+    "Path": "/app/data/site-localdb.db"
   }
 }

docker/site-c-node-a/appsettings.Site.json

@@ -57,5 +57,13 @@
     "Logging": {
       "MinimumLevel": "Information"
     }
+  },
+  // Consolidated site database (LocalDb Phase 1): OperationTracking + site_events.
+  // On the mounted /app/data volume so it survives container recreate - unlike the
+  // legacy site-tracking.db / site_events.db, which defaulted to CWD-relative paths
+  // outside the volume and were lost on every recreate.
+  // Replication is opt-in and configured separately; absent = local-only.
+  "LocalDb": {
+    "Path": "/app/data/site-localdb.db"
   }
 }

docker/site-c-node-b/appsettings.Site.json

@@ -57,5 +57,13 @@
     "Logging": {
       "MinimumLevel": "Information"
     }
+  },
+  // Consolidated site database (LocalDb Phase 1): OperationTracking + site_events.
+  // On the mounted /app/data volume so it survives container recreate - unlike the
+  // legacy site-tracking.db / site_events.db, which defaulted to CWD-relative paths
+  // outside the volume and were lost on every recreate.
+  // Replication is opt-in and configured separately; absent = local-only.
+  "LocalDb": {
+    "Path": "/app/data/site-localdb.db"
   }
 }

docs/plans/2026-07-19-localdb-adoption-phase1.md

@@ -0,0 +1,439 @@
+# ScadaBridge LocalDb Adoption — Phase 1 Implementation Plan
+
+> **For Claude:** REQUIRED SUB-SKILL: Use superpowers-extended-cc:executing-plans to implement this plan task-by-task.
+
+**Goal:** Site node pairs stop losing operation-tracking and site-event state on failover: both stores move into one `ZB.MOM.WW.LocalDb`-managed database with optional (default-OFF) 2-node replication, live-proven on the docker rig.
+
+**Architecture:** One consolidated LocalDb file per site node (`LocalDb:Path` → `/app/data/site-localdb.db`). `OperationTracking` + `site_events` become `RegisterReplicated` tables. Stores keep their public interfaces and their own connection-management style, but obtain connections from `ILocalDb.CreateConnection()` (the lib registers the `zb_hlc_next()` UDF per-connection — a foreign `SqliteConnection` writing to a registered table FAILS, so every write path must go through the lib). Replication (initiator = node-a dialing node-b:8083, bearer-key-gated passive endpoint) is config-gated and default OFF. Design doc: `~/Desktop/scadaproj/docs/plans/2026-07-19-scadabridge-localdb-design.md`.
+
+**Tech Stack:** .NET 10, `ZB.MOM.WW.LocalDb` 0.1.0 + `.Replication` + `.Contracts` (Gitea feed), Microsoft.Data.Sqlite, gRPC on the existing site Kestrel (8083), xunit.
+
+**Execution configuration (saved for later execution):**
+- Resume with `/superpowers-extended-cc:executing-plans docs/plans/2026-07-19-localdb-adoption-phase1.md` — the co-located `.tasks.json` carries the dependency graph.
+- **Model: Opus for every implement subagent** (`model: "opus"` on Agent dispatch). Review chain per task classification (high-risk tasks get serial spec + code review, small tasks a single Sonnet/Haiku code-reviewer pass).
+- **Dispatch tasks in waves — every task in a wave runs concurrently** (file sets are disjoint; verified below). Parallel implementers editing one repo MUST use worktree isolation (`Agent isolation: "worktree"`) — concurrent git in a shared worktree races destructively.
+
+| Wave | Tasks (concurrent) |
+|---|---|
+| 1 | Task 1 (packages) |
+| 2 | Task 2 (schema helpers) |
+| 3 | Task 3 (composition root) |
+| 4 | Task 4 (tracking store) ∥ Task 5a (event writer) ∥ Task 7 (replication wiring) |
+| 5 | Task 5b (event read path) ∥ Task 6 (migrator) ∥ Task 8 (auth interceptor) ∥ Task 9 (health signal) |
+| 6 | Task 10 (convergence test) ∥ Task 11 (rig config) |
+| 7 | Task 12 (live gate — serial, on the real rig) |
+| 8 | Task 13 (docs) ∥ Task 14 (Phase 2 planning) |
+
+**Hard constraints learned from the code (do not rediscover):**
+- `AddZbLocalDb(config, onReady)` is one-DB-per-process, first-registration-wins. Table DDL must run inside `onReady` **before** `RegisterReplicated`; rows inserted before registration are never captured/snapshotted.
+- `site_events.id` must change from `INTEGER AUTOINCREMENT` to TEXT (GUID) — two nodes minting the same autoincrement id silently overwrite each other under LWW.
+- The lib's passive sync endpoint does **not** verify the `ApiKey`; ScadaBridge must gate `/localdb_sync.v1.LocalDbSync/` itself (server interceptor, fixed-time compare).
+- `MapZbLocalDbSync()` returns `IEndpointRouteBuilder` (not the gRPC convention builder), so auth cannot be attached via the return value — hence the interceptor.
+- SQLitePCLRaw must stay pinned at 2.1.12 (family-wide advisory; ScadaBridge already pins it — do not remove the pin).
+
+---
+
+### Task 1: Add LocalDb packages to the build
+
+**Classification:** small
+**Estimated implement time:** ~3 min
+**Parallelizable with:** none (everything depends on it)
+
+**Files:**
+- Modify: `Directory.Packages.props` (package versions block, near the `Microsoft.Data.Sqlite` entry at line ~33)
+- Modify: `NuGet.config` (packageSourceMapping, dohertj2-gitea block)
+- Modify: `src/ZB.MOM.WW.ScadaBridge.Host/ZB.MOM.WW.ScadaBridge.Host.csproj`
+- Modify: `src/ZB.MOM.WW.ScadaBridge.SiteRuntime/ZB.MOM.WW.ScadaBridge.SiteRuntime.csproj`
+- Modify: `src/ZB.MOM.WW.ScadaBridge.SiteEventLogging/ZB.MOM.WW.ScadaBridge.SiteEventLogging.csproj`
+
+**Step 1:** Add central package versions:
+
+```xml
+<PackageVersion Include="ZB.MOM.WW.LocalDb" Version="0.1.0" />
+<PackageVersion Include="ZB.MOM.WW.LocalDb.Replication" Version="0.1.0" />
+<PackageVersion Include="ZB.MOM.WW.LocalDb.Contracts" Version="0.1.0" />
+```
+
+**Step 2:** Add feed mapping patterns to the `dohertj2-gitea` packageSource block in `NuGet.config`:
+
+```xml
+<package pattern="ZB.MOM.WW.LocalDb" />
+<package pattern="ZB.MOM.WW.LocalDb.*" />
+```
+
+**Step 3:** Add `<PackageReference Include="ZB.MOM.WW.LocalDb" />` to SiteRuntime + SiteEventLogging csproj; add `ZB.MOM.WW.LocalDb` **and** `ZB.MOM.WW.LocalDb.Replication` to Host csproj.
+
+**Step 4:** Run `dotnet build ZB.MOM.WW.ScadaBridge.slnx` — expect success, 0 warnings (TreatWarningsAsErrors). Verify restore came from the Gitea feed, not a 404.
+
+**Step 5:** Commit: `git commit -m "feat(localdb): reference ZB.MOM.WW.LocalDb 0.1.0 packages"`
+
+---
+
+### Task 2: Shared schema helpers (tracking + events, GUID PK)
+
+**Classification:** standard
+**Estimated implement time:** ~5 min
+**Parallelizable with:** none
+
+**Files:**
+- Create: `src/ZB.MOM.WW.ScadaBridge.SiteRuntime/Tracking/OperationTrackingSchema.cs`
+- Create: `src/ZB.MOM.WW.ScadaBridge.SiteEventLogging/SiteEventLogSchema.cs`
+- Test: `tests/ZB.MOM.WW.ScadaBridge.SiteRuntime.Tests/Tracking/OperationTrackingSchemaTests.cs`
+- Test: `tests/ZB.MOM.WW.ScadaBridge.SiteEventLogging.Tests/SiteEventLogSchemaTests.cs`
+
+The DDL must be callable from the Host's `AddZbLocalDb` onReady (which owns table creation), from the stores' existing idempotent init, and from tests. Extract each table's DDL into a static `Apply(SqliteConnection)` helper in its owning project.
+
+**Step 1: Write failing tests** — each test opens an in-memory `SqliteConnection`, calls `Apply` twice (idempotency), and asserts via `PRAGMA table_info` that (a) the table exists, (b) `site_events.id` is `TEXT` and `pk == 1` with **no** autoincrement, (c) `OperationTracking` columns match the current store schema including `SourceNode`.
+
+**Step 2:** Run the two test files — expect FAIL (types don't exist).
+
+**Step 3: Implement.** `OperationTrackingSchema.Apply` = verbatim move of the DDL + `AddColumnIfMissing("SourceNode", …)` logic from `OperationTrackingStore.InitializeSchema` (`OperationTrackingStore.cs:74-116`). `SiteEventLogSchema.Apply` = the DDL from `SiteEventLogger.cs:146-160` **changed to**:
+
+```sql
+CREATE TABLE IF NOT EXISTS site_events (
+    id TEXT NOT NULL PRIMARY KEY,
+    timestamp TEXT NOT NULL,
+    event_type TEXT NOT NULL,
+    severity TEXT NOT NULL,
+    instance_id TEXT,
+    source TEXT NOT NULL,
+    message TEXT NOT NULL,
+    details TEXT
+);
+-- same four indexes as today
+```
+
+Keep both helpers free of any LocalDb dependency (plain `Microsoft.Data.Sqlite`) so the owning projects don't need new references for this task alone. Rewire `OperationTrackingStore.InitializeSchema` and `SiteEventLogger.InitializeSchema` to delegate to the helpers (behavior-preserving for tracking; the events PK change lands here and is completed by Task 5's writer change).
+
+**Step 4:** Run: `dotnet test tests/ZB.MOM.WW.ScadaBridge.SiteRuntime.Tests` and `…SiteEventLogging.Tests` — new tests PASS. Pre-existing SiteEventLogging tests that insert rows will now fail on the missing id — fix them in this task by supplying GUID ids where the test writes rows directly (the production writer changes in Task 5; if a production-code insert breaks compilation of tests, coordinate: this task may leave SiteEventLogging.Tests red ONLY for writer-path tests fixed in Task 5 — prefer landing Tasks 2+5 in one PR-sized push).
+
+**Step 5:** Commit.
+
+---
+
+### Task 3: Register LocalDb in the site composition root
+
+**Classification:** high-risk (composition-root wiring — the family's known failure class)
+**Estimated implement time:** ~5 min
+**Parallelizable with:** none
+
+**Files:**
+- Create: `src/ZB.MOM.WW.ScadaBridge.Host/SiteLocalDbSetup.cs`
+- Modify: `src/ZB.MOM.WW.ScadaBridge.Host/SiteServiceRegistration.cs` (insert after `AddSiteRuntime`, ~line 51)
+- Modify: `docker/site-a-node-a/appsettings.Site.json` (+ the other five site-node appsettings): add `"LocalDb": { "Path": "/app/data/site-localdb.db" }`
+- Test: `tests/ZB.MOM.WW.ScadaBridge.Host.Tests/SiteLocalDbWiringTests.cs`
+
+**Step 1: Write the failing DI-graph test.** Follow the existing pattern from the ScadaBridge#22 fix (`Host.Tests` has WebApplicationFactory-style DI tests over the real registration): build a ServiceCollection via `SiteServiceRegistration.Configure` with an in-memory config supplying `LocalDb:Path` = temp file, resolve `ILocalDb`, and assert `ReplicatedTables` contains `OperationTracking` and `site_events`. This is the test that would have caught the Secrets inert-replicator bug — it builds the real graph.
+
+**Step 2:** Run it — FAIL (no registration).
+
+**Step 3: Implement `SiteLocalDbSetup`:**
+
+```csharp
+using Microsoft.Data.Sqlite;
+using ZB.MOM.WW.LocalDb;
+using ZB.MOM.WW.ScadaBridge.SiteEventLogging;
+using ZB.MOM.WW.ScadaBridge.SiteRuntime.Tracking;
+
+namespace ZB.MOM.WW.ScadaBridge.Host;
+
+/// <summary>
+/// onReady for the consolidated site LocalDb: creates the replicated tables and
+/// opts them into capture. Runs before any store resolves ILocalDb; DDL must
+/// precede RegisterReplicated because pre-registration rows are never captured.
+/// </summary>
+public static class SiteLocalDbSetup
+{
+    public static void OnReady(ILocalDb db)
+    {
+        using (var conn = db.CreateConnection())
+        {
+            OperationTrackingSchema.Apply(conn);
+            SiteEventLogSchema.Apply(conn);
+        }
+        db.RegisterReplicated("OperationTracking");
+        db.RegisterReplicated("site_events");
+    }
+}
+```
+
+In `SiteServiceRegistration.Configure`, after `AddSiteRuntime` (line ~51):
+
+```csharp
+// Consolidated site LocalDb — OperationTracking + site_events live here as
+// replicated tables (design: scadaproj docs/plans/2026-07-19-scadabridge-localdb-design.md).
+// Replication itself is registered in Program.cs (needs the WebApplication gRPC host);
+// storage is unconditional — with no peer configured this is just a local SQLite DB.
+services.AddZbLocalDb(config, SiteLocalDbSetup.OnReady);
+```
+
+**Step 4:** Run the wiring test — PASS. Run `dotnet build` — 0 warnings.
+
+**Step 5:** Commit.
+
+---
+
+### Task 4: Rewire OperationTrackingStore onto ILocalDb connections
+
+**Classification:** standard
+**Estimated implement time:** ~4 min
+**Parallelizable with:** Task 5, Task 7
+
+**Files:**
+- Modify: `src/ZB.MOM.WW.ScadaBridge.SiteRuntime/Tracking/OperationTrackingStore.cs`
+- Modify: `src/ZB.MOM.WW.ScadaBridge.SiteRuntime/ServiceCollectionExtensions.cs:74` (the `IOperationTrackingStore` registration; `AddSiteRuntime`'s connection-string parameter becomes vestigial for this store — leave the parameter alone, it still feeds the site config DB)
+- Test: `tests/ZB.MOM.WW.ScadaBridge.SiteRuntime.Tests/Tracking/` (existing store tests)
+- Test: `tests/ZB.MOM.WW.ScadaBridge.Host.Tests/CompositionRootTests.cs:433` (`Site_Resolves_IOperationTrackingStore` — it overrides the connection string to a temp path because the store opens eagerly in its ctor; that override changes shape once the store takes `ILocalDb`)
+
+**Step 1:** Constructor gains `ILocalDb localDb`; `_writeConnection = localDb.CreateConnection()` replaces `new SqliteConnection(_connectionString)` + `Open()` (verified in the lib, `SqliteLocalDb.cs:83-98`: CreateConnection returns an **open** connection with per-connection pragmas — `synchronous`, `busy_timeout`, `foreign_keys=ON` — and the `zb_hlc_next` UDF registered; do NOT call `Open()` on it again). Reader paths that open ad-hoc connections from `_connectionString` switch to `localDb.CreateConnection()` too (reads don't fire triggers, but one connection source is one less config knob). `OperationTrackingOptions.ConnectionString` becomes unused by the store — keep the options class but drop the store's dependency; leave a validator relaxation only if the DI-graph test demands it.
+
+**Step 2:** Update existing store tests to construct via a real `ILocalDb` over a temp file (the lib package is test-referenceable) instead of a raw connection string. Schema now comes from `OperationTrackingSchema.Apply` in setup.
+
+**Step 3:** `dotnet test tests/ZB.MOM.WW.ScadaBridge.SiteRuntime.Tests` — PASS.
+
+**Step 4:** Commit.
+
+---
+
+### Task 5a: Rewire SiteEventLogger onto ILocalDb + mint GUID ids
+
+**Classification:** standard
+**Estimated implement time:** ~4 min
+**Parallelizable with:** Task 4, Task 7
+
+> **Amended 2026-07-19 (execution review).** The original Task 5 said "any `ORDER BY id`
+> becomes `ORDER BY timestamp`" and listed 3 files. Code recon found the integer `id` is
+> load-bearing in three separate places beyond the writer — a keyset cursor, the
+> storage-cap purge, and `long`-typed DTOs that cross the site↔central Akka boundary.
+> Task 5 is therefore split: **5a = the writer** (this task, self-contained), **5b = the
+> read/purge/contract change** (high-risk, its own task). Do not fold 5b back into 5a.
+
+**Files:**
+- Modify: `src/ZB.MOM.WW.ScadaBridge.SiteEventLogging/SiteEventLogger.cs`
+- Modify: `src/ZB.MOM.WW.ScadaBridge.SiteEventLogging/ServiceCollectionExtensions.cs` (AddSiteEventLogging registration)
+- Test: `tests/ZB.MOM.WW.ScadaBridge.SiteEventLogging.Tests/` (writer-path tests only)
+
+**Step 1:** `SiteEventLogger` constructor gains `ILocalDb localDb`; its single owned `_connection` (`SiteEventLogger.cs:34,62-65`) becomes `localDb.CreateConnection()` — already open, pragma-configured, HLC-UDF-registered; do **not** call `Open()` again. Delete the `connectionStringOverride` seam (`:50`) rather than preserving it: a raw connection lacks `zb_hlc_next()`, so any test writing through the override against a registered table now **fails closed**. Switch the test fixture to a real `ILocalDb` over a temp file.
+
+**Step 2:** Preserve the `internal WithConnection` seam (`:104`, `:123`) — `EventLogQueryService` and `EventLogPurgeService` both depend on it deliberately (their ctors take the concrete `SiteEventLogger`, not the interface). Its body changes connection source only; the signature and locking semantics stay identical, so 5b's consumers keep compiling.
+
+**Step 3:** The INSERT (`:237-240`) gains an `id` column bound to `Guid.NewGuid().ToString("N")`, minted per event in the writer loop. `ISiteEventLogger.LogEventAsync`'s signature is unchanged — all 18 fire-and-forget call sites are untouched.
+
+**Step 4:** Fix writer-path tests from Task 2. Run `dotnet test tests/ZB.MOM.WW.ScadaBridge.SiteEventLogging.Tests` — writer tests PASS. Query/purge tests may be red here; 5b closes them. Note that in the commit message.
+
+**Step 5:** Commit.
+
+---
+
+### Task 5b: Migrate the event-log read path off integer ids
+
+**Classification:** high-risk (cross-cluster data contract + silent-data-loss paging bug)
+**Estimated implement time:** ~5 min
+**Parallelizable with:** Task 6, Task 8, Task 9
+**Blocked by:** Task 5a
+
+The GUID PK breaks three integer-id assumptions. All three must move together — shipping any one alone is a live bug.
+
+**Files:**
+- Modify: `src/ZB.MOM.WW.ScadaBridge.SiteEventLogging/EventLogQueryService.cs` (cursor `:68-71`, `ORDER BY id ASC` `:141`, `GetInt64(0)` `:153`, token mint `:181`)
+- Modify: `src/ZB.MOM.WW.ScadaBridge.SiteEventLogging/EventLogPurgeService.cs` (storage-cap delete `:162-166`)
+- Modify: `src/ZB.MOM.WW.ScadaBridge.Commons/Messages/RemoteQuery/EventLogQueryResponse.cs` (`EventLogEntry.Id` `long`→`string`; `ContinuationToken` `long?`→`string?`)
+- Modify: `src/ZB.MOM.WW.ScadaBridge.Commons/Messages/RemoteQuery/EventLogQueryRequest.cs` (`ContinuationToken` `long?`→`string?`)
+- Modify: `src/ZB.MOM.WW.ScadaBridge.ManagementService/ManagementActor.cs:3127` (passes `null` today — verify it still compiles)
+- Modify: `src/ZB.MOM.WW.ScadaBridge.CentralUI/Components/Pages/Monitoring/EventLogs.razor:247,268,271` (`_continuationToken` type)
+- Test: `tests/ZB.MOM.WW.ScadaBridge.SiteEventLogging.Tests/` (query + purge)
+
+**Step 1: Write the failing tests first.**
+- Paging: insert N events with known timestamps, page through with `PageSize` < N, assert **every** row is returned exactly once across pages and in chronological order. This test fails loudly against a naive `id > $afterId` GUID cursor — that is the point.
+- Ordering: rows come back oldest-first regardless of GUID values.
+- Purge: with the storage cap tripped, the **oldest** rows are deleted, not arbitrary ones.
+
+**Step 2:** Run — FAIL.
+
+**Step 3: Implement.**
+- **Cursor** → composite keyset on `(timestamp, id)`, since `timestamp` alone is not unique:
+  `WHERE (timestamp > $afterTs) OR (timestamp = $afterTs AND id > $afterId)`, `ORDER BY timestamp ASC, id ASC`. The continuation token encodes both — use `"{timestamp}|{id}"` as the `string?` token; parse defensively and treat a malformed token as "start from the beginning" rather than throwing.
+- **Purge** (`:162-166`) → `ORDER BY timestamp ASC` instead of `ORDER BY id ASC`. (The retention purge at `:128` already filters on `timestamp` and is unaffected.)
+- **DTOs** → `Id` and both `ContinuationToken`s become `string`/`string?`; reader uses `GetString(0)`.
+
+**Step 4: Wire-compat check.** These DTOs cross the site↔central Akka boundary (`SiteCommunicationActor.cs:193` → `CommunicationService.cs:304` → ManagementActor / CentralUI). Both sides ship in the same deployable and the rig redeploys as a unit, so no rolling-upgrade shim is needed — **but state that explicitly in the commit message**, and confirm no serializer manifest/binding pins these types by name+type (grep the Akka serialization setup). If a binding exists, stop and surface it.
+
+**Step 5:** `dotnet test tests/ZB.MOM.WW.ScadaBridge.SiteEventLogging.Tests` + full solution build (0 warnings) — PASS. Commit.
+
+---
+
+### Task 6: One-time legacy data migrator
+
+**Classification:** high-risk (data migration)
+**Estimated implement time:** ~5 min
+**Parallelizable with:** Task 8, Task 9 (needs Tasks 4-5 done)
+
+**Files:**
+- Create: `src/ZB.MOM.WW.ScadaBridge.Host/SiteLocalDbLegacyMigrator.cs`
+- Modify: `src/ZB.MOM.WW.ScadaBridge.Host/SiteLocalDbSetup.cs` (call migrator at the end of OnReady)
+- Test: `tests/ZB.MOM.WW.ScadaBridge.Host.Tests/SiteLocalDbLegacyMigratorTests.cs`
+
+Semantics (write the tests first, one per bullet):
+- Legacy files located from the OLD config keys (`ScadaBridge:OperationTracking:ConnectionString`, `ScadaBridge:SiteEventLog:DatabasePath`); missing file ⇒ no-op.
+- **Both keys are unset in every docker appsettings today**, so they fall back to the code defaults `"Data Source=site-tracking.db"` (`OperationTrackingOptions.cs:14`) and `"site_events.db"` (`SiteEventLogOptions.cs:10`) — **CWD-relative**, i.e. `/app/site-tracking.db` and `/app/site_events.db`, NOT `/app/data/`. Resolve relative paths against the process CWD; do not assume the data volume. (Consequence worth knowing: both DBs live outside the mounted volume today and are already lost on every container recreate — so on the rig the migrator will usually find nothing, and that is a legitimate no-op, not a failure. Phase 1 incidentally fixes that data-loss bug by consolidating into `/app/data/site-localdb.db`.)
+- Runs **after** `RegisterReplicated` so migrated rows enter the oplog and replicate.
+- Tracking rows copy with `INSERT OR IGNORE` (same TEXT PK ⇒ idempotent).
+- Legacy `site_events` rows get **deterministic** ids — `"mig-{NodeName}-{legacyId}"` — NOT fresh GUIDs, so a crashed-then-rerun migration cannot duplicate events (`INSERT OR IGNORE` on the deterministic key). NodeName from `ScadaBridge:Node:NodeName`.
+- The whole copy runs in one `ILocalDb.BeginTransactionAsync` transaction; the legacy file is renamed to `<name>.migrated` only after commit. Migration failure throws ⇒ host fails to boot (no half-state; legacy files untouched on rollback).
+- Second boot (file already `.migrated`) ⇒ no-op.
+
+Run the migrator tests + full Host.Tests; commit.
+
+---
+
+### Task 7: Replication registration + sync endpoint (default OFF)
+
+**Classification:** high-risk (wiring + endpoint exposure)
+**Estimated implement time:** ~4 min
+**Parallelizable with:** Task 4, Task 5 (needs Task 3 only; touches Program.cs, disjoint from the store files)
+
+**Files:**
+- Modify: `src/ZB.MOM.WW.ScadaBridge.Host/Program.cs` site block (`:515-536`)
+- Test: `tests/ZB.MOM.WW.ScadaBridge.Host.Tests/SiteLocalDbWiringTests.cs` (extend)
+
+**Step 1:** In the site block: `builder.Services.AddZbLocalDbReplication(builder.Configuration);` next to `AddGrpc()` (line ~516), and `app.MapZbLocalDbSync();` next to the existing `MapGrpcService<SiteStreamGrpcServer>()` (line ~536). Kestrel already serves h2c HTTP/2 on 8083 — no listener changes.
+
+**Step 2:** Extend the DI-graph test: with no `LocalDb:Replication:PeerAddress`, the host builds and the initiator idles (resolve `ISyncStatus`, assert not connected, no throw). This is the "replication is default-OFF and inert-but-not-broken" pin.
+
+**Step 3:** Build + Host.Tests green; commit.
+
+---
+
+### Task 8: Bearer auth interceptor for the sync endpoint
+
+**Classification:** high-risk (security)
+**Estimated implement time:** ~5 min
+**Parallelizable with:** Task 6, Task 9
+
+**Files:**
+- Create: `src/ZB.MOM.WW.ScadaBridge.Host/LocalDbSyncAuthInterceptor.cs`
+- Modify: `src/ZB.MOM.WW.ScadaBridge.Host/Program.cs` (site `AddGrpc` gains `options.Interceptors.Add<LocalDbSyncAuthInterceptor>()`)
+- Test: `tests/ZB.MOM.WW.ScadaBridge.Host.Tests/LocalDbSyncAuthInterceptorTests.cs`
+
+The lib's passive service does not verify keys (documented). Fail-closed server interceptor, scoped by method path:
+
+```csharp
+/// <summary>
+/// Gates the LocalDb passive sync endpoint. The library deliberately leaves
+/// inbound auth to the host. Scoped to /localdb_sync.v1.LocalDbSync/ so the
+/// existing SiteStream service is untouched. Fail-closed: no configured key
+/// means NO sync stream is accepted, authenticated or not.
+/// </summary>
+public sealed class LocalDbSyncAuthInterceptor : Interceptor
+{
+    private const string ServicePrefix = "/localdb_sync.v1.LocalDbSync/";
+    // read via IOptions<ReplicationOptions> — verified: AddZbLocalDbReplication binds
+    // section "LocalDb:Replication" with ValidateOnStart; ApiKey is string? (null =
+    // no key configured). Same key on both nodes; ${secret:} refs resolve through
+    // the existing pre-host expander.
+    // compare with CryptographicOperations.FixedTimeEquals over UTF8 bytes;
+    // non-sync methods pass through untouched.
+}
+```
+
+Tests (TDD, write first): non-sync path passes with no key; sync path with no configured key → `PermissionDenied`; wrong bearer → `PermissionDenied`; correct bearer → passes through. Use the interceptor directly with a fake `ServerCallContext` (Grpc.Core.Testing) — no live channel needed. Commit.
+
+---
+
+### Task 9: Surface ISyncStatus as site health signal
+
+**Classification:** small
+**Estimated implement time:** ~3 min
+**Parallelizable with:** Task 6, Task 8
+
+**Files:**
+- Modify: `src/ZB.MOM.WW.ScadaBridge.Host/SiteServiceRegistration.cs` (health wiring — follow the `AddSiteEventLogHealthMetricsBridge` precedent at :76)
+- Test: extend `SiteLocalDbWiringTests`
+
+Bridge `ISyncStatus` (`Connected`, `OplogBacklog` — **nullable**, null must surface as unknown, never as 0) into the site health report the same way `FailedWriteCount` is bridged. Metrics need no work — the `ZB.MOM.WW.LocalDb.Replication` meter flows through the existing `AddZbTelemetry` Prometheus export. Test: bridge registered + resolvable. Commit.
+
+---
+
+### Task 10: Two-node in-process convergence test
+
+**Classification:** high-risk
+**Estimated implement time:** ~5 min
+**Parallelizable with:** Task 11
+**Blocked by:** Task 6, Task 8, **Task 5b** (scenario 3 reads events back through the query path)
+
+**Files:**
+- Create: `tests/ZB.MOM.WW.ScadaBridge.IntegrationTests/LocalDbSitePairConvergenceTests.cs`
+
+Model on the LocalDb lib's own integration suite (`~/Desktop/scadaproj/ZB.MOM.WW.LocalDb/tests/` — two real SQLite DBs over a real gRPC pipe): two hosts running the ScadaBridge site schema (`SiteLocalDbSetup.OnReady`), replication enabled between them (loopback ports, shared ApiKey through the real interceptor). Scenarios:
+1. Tracking row written on A → readable on B ≤ a few flush intervals.
+2. Same op updated on both nodes → both converge to one winner (LWW).
+3. Events logged on both nodes → union visible on both (GUID ids, no collisions).
+4. B offline while A accumulates → B rejoins → convergence.
+
+Offline, no docker. Run: `dotnet test tests/ZB.MOM.WW.ScadaBridge.IntegrationTests --filter "FullyQualifiedName~LocalDbSitePairConvergence"` — PASS. Commit.
+
+---
+
+### Task 11: Docker rig enablement (site-a pair)
+
+**Classification:** small
+**Estimated implement time:** ~4 min
+**Parallelizable with:** Task 10
+
+**Files:**
+- Modify: `docker/site-a-node-a/appsettings.Site.json` — add `"Replication": { "PeerAddress": "http://<site-a-node-b container name>:8083", "ApiKey": "<dev key>" }` under `LocalDb` (take exact container hostname from `docker/docker-compose.yml`)
+- Modify: `docker/site-a-node-b/appsettings.Site.json` — `"Replication": { "ApiKey": "<same dev key>" }` (passive; key for inbound verification)
+- Site-b/site-c pairs stay unreplicated (flag-off posture proven side-by-side)
+
+Plain dev key in rig config is acceptable (matches the rig's existing posture); note in the compose README that production uses a `${secret:}` ref. Commit.
+
+---
+
+### Task 12: Live gate on the docker rig
+
+**Classification:** standard (verification, no code)
+**Estimated implement time:** ~10 min wall (mostly waiting on the rig)
+**Parallelizable with:** none
+
+Per `docker/README.md` + memory gotchas (restart central-a+b together; Central needs ApiKeyPepper):
+1. `bash docker/deploy.sh` — rebuild + redeploy the 8-node cluster.
+2. Generate tracking + event activity (existing rig flows / CLI).
+3. On both site-a nodes: `sqlite3 /app/data/site-localdb.db "SELECT count(*) FROM site_events"` etc. — counts converge; spot-check row equality.
+4. `bash docker/failover-drill.sh` — after takeover, the new active node serves complete tracking/event history from before the flip.
+5. Check `/metrics` on 8084 for `localdb_*` instruments; check the dead-letter table is empty.
+6. Verify site-b (replication off) boots and behaves identically to before — the default-OFF pin, live.
+
+Evidence (commands + output) goes in the PR/commit message. Any failure here loops back to the offending task — do not mark this plan done with a red gate.
+
+---
+
+### Task 13: Documentation truth pass
+
+**Classification:** trivial
+**Estimated implement time:** ~4 min
+**Parallelizable with:** Task 14
+
+- ScadaBridge `CLAUDE.md`: consolidated-DB note, config keys, replication flag posture, deleted config keys (old tracking/eventlog paths) marked migration-only.
+- scadaproj `CLAUDE.md` LocalDb component row: "no app adoption yet" → ScadaBridge Phase 1 adopted (state exactly what is and is not live, per the component-status-honesty convention).
+- `SiteEventLogger` XML doc: remove the now-false "Not replicated to standby. On failover, the new active node starts a fresh log."
+
+Commit; push per repo convention.
+
+---
+
+### Task 14: Phase 2 gate — plan the bespoke-replicator migration
+
+**Classification:** standard (planning only)
+**Estimated implement time:** planning session
+
+**Blocked by:** Task 12 live gate PASS.
+
+Phase 2 (config tables + `sf_messages` into the consolidated DB; **delete** `SiteReplicationActor` + StoreAndForward `ReplicationService`) gets its own implementation plan written against post-Phase-1 reality. Inputs: the design doc §1 Phase 2, the bespoke replicator's test intents (port before deletion), the N5 bounded-duplicate acceptance, and any oplog-cap/churn observations from the Phase-1 rig soak. Do not start Phase 2 work without that plan.
+
+---
+
+## Execution notes
+
+- Family conventions bind: TreatWarningsAsErrors, red-first tests, container-built DI-graph tests for all wiring, `dotnet test` offline-green.
+- Lib assumptions **verified against source 2026-07-19** (`~/Desktop/scadaproj/ZB.MOM.WW.LocalDb/`): `CreateConnection()` returns an open, pragma-configured, UDF-registered connection (`SqliteLocalDb.cs:83-98`); `ReplicationOptions` binds from `LocalDb:Replication` with `ValidateOnStart`, `ApiKey` nullable, and a passive node (empty `PeerAddress`) starts its `SyncBackgroundService` and immediately idles (`ReplicationServiceCollectionExtensions.cs`).
+- If any task needs files beyond its `Files:` block, that is a plan defect — surface it rather than silently expanding scope.

docs/plans/2026-07-19-localdb-adoption-phase1.md.tasks.json

@@ -0,0 +1,195 @@
+{
+  "planPath": "docs/plans/2026-07-19-localdb-adoption-phase1.md",
+  "execution": {
+    "mode": "parallel-waves",
+    "implementerModel": "opus",
+    "isolation": "worktree",
+    "branch": "feat/localdb-phase1",
+    "note": "Dispatch every unblocked task concurrently (waves in the plan header). Parallel implementers MUST use worktree isolation - concurrent git in one worktree races destructively."
+  },
+  "amendments": [
+    {
+      "date": "2026-07-19",
+      "by": "execution review",
+      "change": "Task 5 split into 5a (id 5, writer) and 5b (id 15, read path). Code recon found the integer site_events.id load-bearing in the keyset cursor (EventLogQueryService:70,141,153), the storage-cap purge (EventLogPurgeService:164), and long-typed DTOs crossing the site<->central Akka boundary (EventLogEntry.Id, both ContinuationTokens) - none of which were in the original Task 5 file set. 5b is high-risk. Task 10 gains a blockedBy on 15."
+    },
+    {
+      "date": "2026-07-19",
+      "by": "execution",
+      "change": "Task 2 in isolation left the production writer broken (site_events.id NOT NULL, nothing minting it) - 40 tests red. Per the plan's own 'one PR-sized push' guidance, 5a's GUID minting and all of 5b landed together with Task 2 in commit 727fa48c. Task 5a remains OPEN for its other half: the ILocalDb.CreateConnection() swap, which still needs Task 3."
+    }
+  ],
+  "prerequisites": [
+    {
+      "commit": "4715f2f9",
+      "what": "gRPC 2.71.0 -> 2.76.0 + Protobuf 3.34.1. Forced by LocalDb.Replication 0.1.0's nuspec floor; restore fails NU1605 below it. Directory.Packages.props had deferred this bump to 'its own reviewed commit' - honored."
+    },
+    {
+      "commit": "f056b67e",
+      "what": "AngleSharp GHSA-pgww-w46g-26qg scoped suppression in CentralUI.Tests (supersedes the wrong pin in ecf6b628). Pre-existing: the solution build was RED on clean main, making every task's verification gate meaningless. No patched-and-working version exists upstream - 1.5.x breaks bunit at runtime, and bunit 2.7.2 still resolves vulnerable 1.4.0."
+    }
+  ],
+  "tasks": [
+    {
+      "id": 1,
+      "subject": "Task 1: Add LocalDb packages to the build",
+      "status": "completed",
+      "commit": "7370b331"
+    },
+    {
+      "id": 2,
+      "subject": "Task 2: Shared schema helpers (tracking + events, GUID PK)",
+      "status": "completed",
+      "blockedBy": [
+        1
+      ],
+      "commit": "727fa48c"
+    },
+    {
+      "id": 3,
+      "subject": "Task 3: Register LocalDb in the site composition root",
+      "status": "completed",
+      "blockedBy": [
+        2
+      ],
+      "commit": "2977e6c4",
+      "note": "LocalDb:Path added to 10 configs (8 site nodes incl. docker-env2 site-x, dev template, wonder prod sample) - it is REQUIRED via ValidateOnStart, so any site config missing it fails startup."
+    },
+    {
+      "id": 4,
+      "subject": "Task 4: Rewire OperationTrackingStore onto ILocalDb connections",
+      "status": "completed",
+      "blockedBy": [
+        3
+      ],
+      "commit": "0b5e9b44",
+      "note": "Ctor takes ILocalDb, not IOptions<OperationTrackingOptions>; both ad-hoc reader paths switched too and their OpenAsync calls removed (CreateConnection returns an OPEN connection). Three DI fixtures needed LocalDb:Path added because resolving IOperationTrackingStore now forces ILocalDb construction."
+    },
+    {
+      "id": 5,
+      "subject": "Task 5a: Rewire SiteEventLogger onto ILocalDb + mint GUID ids",
+      "status": "completed",
+      "blockedBy": [
+        3
+      ],
+      "commit": "727fa48c + 0d8a80aa",
+      "note": "GUID minting landed early with Task 2 (727fa48c) to avoid committing a writer that violated site_events.id NOT NULL. The ILocalDb.CreateConnection() swap is 0d8a80aa. connectionStringOverride DELETED, not preserved: site_events is replicated, so a raw connection lacks zb_hlc_next() and fails closed."
+    },
+    {
+      "id": 15,
+      "subject": "Task 5b: Migrate the event-log read path off integer ids",
+      "status": "completed",
+      "blockedBy": [
+        5
+      ],
+      "commit": "727fa48c"
+    },
+    {
+      "id": 6,
+      "subject": "Task 6: One-time legacy data migrator",
+      "status": "completed",
+      "blockedBy": [
+        4,
+        5
+      ],
+      "commit": "98b94771",
+      "note": "Runs AFTER RegisterReplicated (pinned by an oplog assertion). Deterministic mig-{NodeName}-{legacyId} event ids, not fresh GUIDs, so a crash between commit and rename cannot duplicate. DEVIATION: sync transaction on a CreateConnection() connection instead of BeginTransactionAsync - OnReady is a synchronous Action<ILocalDb>."
+    },
+    {
+      "id": 7,
+      "subject": "Task 7: Replication registration + sync endpoint (default OFF)",
+      "status": "completed",
+      "blockedBy": [
+        3
+      ],
+      "commit": "a560e9ea",
+      "note": "DEVIATION: AddZbLocalDbReplication moved from Program.cs (where the plan put it) into SiteServiceRegistration, because the composition-root tests build that graph and not Program.cs - in Program.cs a missing registration would still show green. MapZbLocalDbSync stays in Program.cs (needs the WebApplication). Endpoint is UNAUTHENTICATED until Task 8; no config in this repo sets a peer."
+    },
+    {
+      "id": 8,
+      "subject": "Task 8: Bearer auth interceptor for the sync endpoint",
+      "status": "completed",
+      "blockedBy": [
+        7
+      ],
+      "commit": "59c69519",
+      "note": "Fail-closed: no configured ApiKey rejects ALL sync streams. All four handler shapes gated, not just unary (the real RPC is a duplex stream). DEVIATION: Grpc.Core.Testing does not exist on grpc-dotnet; hand-rolled FakeServerCallContext instead of adding the retired native package."
+    },
+    {
+      "id": 9,
+      "subject": "Task 9: Surface ISyncStatus as site health signal",
+      "status": "completed",
+      "blockedBy": [
+        7
+      ],
+      "commit": "59c69519",
+      "note": "Additive init properties on SiteHealthReport so the Akka DTO ctor is untouched. Both fields nullable: null = no data, false/0 = a real reading (which is the healthy default-OFF state). Collector stores one tuple, read once, so a torn read cannot pair fresh Connected with stale backlog."
+    },
+    {
+      "id": 10,
+      "subject": "Task 10: Two-node in-process convergence test",
+      "status": "completed",
+      "blockedBy": [
+        6,
+        8,
+        15
+      ],
+      "commit": "3c395d79",
+      "note": "5 scenarios over real loopback gRPC + the real interceptor, initialized via SiteLocalDbSetup.OnReady. Verified NON-vacuous: mismatching the two ApiKeys turns all 5 red with 2m30s of timeouts."
+    },
+    {
+      "id": 11,
+      "subject": "Task 11: Docker rig enablement (site-a pair)",
+      "status": "completed",
+      "blockedBy": [
+        8
+      ],
+      "commit": "(with Task 11 commit)",
+      "note": "site-a replicated, site-b/site-c deliberately not, so default-OFF is proven side-by-side on one rig."
+    },
+    {
+      "id": 12,
+      "subject": "Task 12: Live gate on the docker rig",
+      "status": "completed",
+      "blockedBy": [
+        9,
+        10,
+        11
+      ],
+      "commit": "95c108f1",
+      "note": "PASS. Found one REAL defect: ZbTelemetryOptions.Meters is a silent allowlist, so localdb_* was absent from /metrics - the plan's 'flows through automatically' claim was wrong. Fixed + pinned. Evidence: identical row_version/HLC/originating node_id across the pair, 0 dead letters, oplog drained, site failover + rejoin catch-up, localdb_* scraped, site-b inert. NOTE: docker/failover-drill.sh targets CENTRAL nodes and does NOT exercise the site-pair claim; the site flip was run directly."
+    },
+    {
+      "id": 13,
+      "subject": "Task 13: Documentation truth pass",
+      "status": "completed",
+      "blockedBy": [
+        12
+      ],
+      "commit": "(docs commit)",
+      "note": "Both CLAUDE.md files + the false 'starts a fresh log' XML doc + the two now-migration-only option properties."
+    },
+    {
+      "id": 14,
+      "subject": "Task 14: Phase 2 gate - plan the bespoke-replicator migration",
+      "status": "completed",
+      "blockedBy": [
+        12
+      ],
+      "commit": "(gate commit)",
+      "note": "Gate document only, per the plan - Phase 2 gets its own implementation plan. docs/plans/2026-07-19-localdb-phase2-gate.md. KEY FINDING: the rig SOAK that Phase 2's oplog-cap tuning needs has NOT been run; the live gate proved correctness, not steady-state behaviour."
+    }
+  ],
+  "knownFlakes": [
+    {
+      "test": "SiteRuntime.Tests InstanceActorChildAttributeRaceTests.ChildActors_AreSeededFromAnIsolatedCopy_NotTheLiveAttributesDictionary",
+      "note": "Intermittent ActorNotFoundException under full-suite load; passes in isolation and in most full runs. Pre-existing, unrelated to LocalDb. Worth its own issue."
+    },
+    {
+      "test": "AuditLog.Tests ParentExecutionIdCorrelationTests.InboundRoutedRun_AllRoutedRows_CarryInboundExecutionId_AsParentExecutionId",
+      "note": "Cold-MSSQL-fixture timing: ~91s and AwaitAssert-times-out on a cold fixture, ~1s and passes warm. Baselined against a stashed tree to confirm it is not a LocalDb regression."
+    }
+  ],
+  "lastUpdated": "2026-07-19",
+  "phase1Status": "COMPLETE - all 15 tasks done, live gate PASS. Branch feat/localdb-phase1 NOT merged/pushed."
+}

docs/plans/2026-07-19-localdb-phase2-gate.md

@@ -0,0 +1,101 @@
+# LocalDb Phase 2 — Gate Document
+
+> **Status: NOT STARTED.** This is the gate, not the plan. Phase 2 work must not begin
+> until an implementation plan is written against the facts below and the open questions
+> in §5 are answered.
+
+**Phase 1 live gate: PASS** (2026-07-19, docker rig). See the Task 12 commit for evidence.
+
+**Phase 2 goal (from the design doc, scadaproj `docs/plans/2026-07-19-scadabridge-localdb-design.md` §1):**
+move `scadabridge.db`'s config tables and StoreAndForward's `sf_messages` into the same
+consolidated LocalDb file, then **delete** `SiteReplicationActor` and StoreAndForward's
+`ReplicationService` outright. CDC triggers replace hand-shipped ops; the library's
+snapshot resync replaces the bespoke chunked anti-entropy. The design explicitly chose
+**replace + delete, no dual-mechanism period** — recoverable from git.
+
+---
+
+## 1. What Phase 1 actually established
+
+Facts a Phase 2 plan can rely on, all verified rather than assumed:
+
+- One `ILocalDb` per site process at `LocalDb:Path`, **required** (`ValidateOnStart`).
+  A site config missing it fails to boot.
+- `SiteLocalDbSetup.OnReady` is the single place tables are created and registered.
+  Ordering is load-bearing: **DDL → `RegisterReplicated` → writes**, then the legacy
+  migrator. Rows written before registration are invisible to the peer forever, silently.
+- Replication is registered in `SiteServiceRegistration` (not `Program.cs`) so the
+  composition-root tests cover it; only `MapZbLocalDbSync` lives in `Program.cs`.
+- Inbound auth is `LocalDbSyncAuthInterceptor`, fail-closed, scoped to
+  `/localdb_sync.v1.LocalDbSync/`, sharing the existing 8083 h2c listener.
+- Default-OFF is real and proven side-by-side: rig site-a replicates, site-b/site-c do not.
+- `ZbTelemetryOptions.Meters` is a **silent allowlist** (`SiteServiceRegistration.ObservedMeters`).
+- Bidirectional sync works with only one node configured as initiator.
+
+## 2. What Phase 2 must port before deleting anything
+
+The bespoke replicator's tests encode behaviour that the design's CDC replacement must
+still satisfy. **Port the test intents first, delete second** — the plan should treat the
+existing tests as the specification, not as code to be removed alongside the implementation.
+
+- `tests/ZB.MOM.WW.ScadaBridge.SiteRuntime.Tests/Actors/SiteReplicationActorTests.cs`
+- `tests/ZB.MOM.WW.ScadaBridge.StoreAndForward.Tests/ReplicationServiceTests.cs`
+
+Implementation under deletion:
+
+- `src/ZB.MOM.WW.ScadaBridge.SiteRuntime/Actors/SiteReplicationActor.cs`
+- `src/ZB.MOM.WW.ScadaBridge.StoreAndForward/ReplicationService.cs`
+
+Known accepted behaviour to carry forward, not silently drop: the **N5 bounded-duplicate
+race** the bespoke replicator documents and accepts. A Phase 2 plan must state whether CDC
+inherits the same bound, a tighter one, or a different failure shape.
+
+## 3. Substantially harder than Phase 1
+
+Phase 1 moved two tables that **nothing replicated before**, so the worst case was "no
+worse than today". Phase 2 replaces a working mechanism, which makes it a different risk
+class:
+
+- **`sf_messages` is an outbound buffer with side effects.** Phase 1's tables are
+  read-mostly records; a store-and-forward row that resurrects or double-delivers sends
+  real traffic to an external system. Park/requeue/remove becoming ordinary captured
+  row updates/deletes needs its own analysis of what LWW does to an in-flight send.
+- **Config tables drive deployment.** A converged-but-wrong config row is a deployed
+  instance behaving incorrectly, not a missing history entry.
+- **No dual-mechanism period.** The chosen posture means the cutover is the test. That
+  raises the bar on the offline convergence suite and the live gate considerably.
+- **Migration is not a no-op this time.** Phase 1's migrator usually finds nothing,
+  because the legacy files sat outside the volume. `scadabridge.db` and
+  `store-and-forward.db` are real, populated, on the volume, and actively replicated
+  while the migration runs.
+
+## 4. Required inputs before writing the plan
+
+1. The design doc §1 Phase 2 and §2 schema/migration sections, re-read against
+   post-Phase-1 reality (several Phase 1 assumptions changed during execution — see the
+   `amendments` array in `2026-07-19-localdb-adoption-phase1.md.tasks.json`).
+2. The two test files in §2, read as specifications.
+3. **Rig soak observations that do not exist yet** — see §5.
+
+## 5. Open questions — answer these first
+
+- **Oplog growth and churn under real config/S&F write rates.** Phase 1's tables are
+  low-volume; `sf_messages` is not. `MaxOplogRows` / `MaxOplogAge` / snapshot-resync
+  thresholds cannot be chosen from first principles. *This needs a Phase-1 rig soak that
+  has not been run.* The live gate proved correctness, not steady-state behaviour over
+  time.
+- **LWW semantics for in-flight store-and-forward sends.** What does last-writer-wins do
+  when one node parks a message the other is mid-delivery on?
+- **Migration-under-load.** How does the one-time copy of an actively-replicating
+  `scadabridge.db` interact with the bespoke replicator still running during cutover?
+- **Rollback story.** With no dual-mechanism period, what is the recovery path if the
+  cutover fails in production — beyond "revert the commit"?
+- **Does the consolidated file stay appropriate?** One-DB-per-process was chosen partly
+  because Phase 1's tables were small. Adding config + S&F changes the size and write
+  profile of that single file.
+
+## 6. Not in scope (unchanged from the design)
+
+`auditlog.db` (diverges per node by design — central pulls the union; replicating it would
+double-forward), the secrets store (has its own answer in `Secrets.Replicator.SqlServer`
+hub mode), and central nodes (SQL-Server-first, no LocalDb use case).

nuget.config

@@ -27,6 +27,8 @@
       <package pattern="ZB.MOM.WW.Theme" />
       <package pattern="ZB.MOM.WW.Secrets" />
       <package pattern="ZB.MOM.WW.Secrets.*" />
+      <package pattern="ZB.MOM.WW.LocalDb" />
+      <package pattern="ZB.MOM.WW.LocalDb.*" />
     </packageSource>
   </packageSourceMapping>
   <!--

src/ZB.MOM.WW.ScadaBridge.CentralUI/Components/Pages/Monitoring/EventLogs.razor

@@ -178,7 +178,10 @@
 
     private List<EventLogEntry>? _entries;
     private bool _hasMore;
-    private long? _continuationToken;
+    // Opaque keyset cursor from the previous response — passed back verbatim, never
+    // parsed. It became a string in LocalDb Phase 1 when site_events ids turned into
+    // GUIDs and the cursor had to carry the timestamp too.
+    private string? _continuationToken;
     private bool _searching;
     private string? _errorMessage;
     private ToastNotification _toast = default!;

src/ZB.MOM.WW.ScadaBridge.Commons/Messages/Health/SiteHealthReport.cs

@@ -73,6 +73,37 @@ public record SiteHealthReport(
     /// indicates a stuck/blocked script holding a dedicated thread.
     /// </summary>
     public double? ScriptOldestBusyAgeSeconds { get; init; }
+
+    // LocalDb 2-node replication of the consolidated site database (Phase 1).
+    // Additive init properties for the same reason as the scheduler gauges above:
+    // the positional constructor stays untouched. Refreshed on the site by
+    // LocalDbReplicationStatusReporter.
+
+    /// <summary>
+    /// Whether a replication sync session is currently running with the peer site node,
+    /// or <see langword="null"/> when replication is not wired on this node.
+    /// </summary>
+    /// <remarks>
+    /// Nullable on purpose, and <see langword="false"/> is NOT the same as null.
+    /// Replication ships default-OFF, so a site node with no peer configured reports
+    /// <see langword="false"/> — that is a healthy, expected state, not an outage. Only
+    /// a node that was configured with a peer and is reporting <see langword="false"/>
+    /// is degraded, and distinguishing those two cases is the operator's job, not this
+    /// field's.
+    /// </remarks>
+    public bool? LocalDbReplicationConnected { get; init; }
+
+    /// <summary>
+    /// Unacked replication oplog entries waiting to reach the peer, or
+    /// <see langword="null"/> when unknown.
+    /// </summary>
+    /// <remarks>
+    /// <b>Null means unknown, never zero.</b> The underlying provider returns null when
+    /// no backlog source is wired or the poll failed, and that must survive to the
+    /// operator: a failed read rendered as "0 backlog" would report a broken replication
+    /// pair as perfectly healthy. Collapsing null to 0 anywhere on this path is a bug.
+    /// </remarks>
+    public long? LocalDbOplogBacklog { get; init; }
 }
 
 /// <summary>

src/ZB.MOM.WW.ScadaBridge.Commons/Messages/RemoteQuery/EventLogQueryRequest.cs

@@ -3,13 +3,19 @@ namespace ZB.MOM.WW.ScadaBridge.Commons.Messages.RemoteQuery;
 /// <summary>
 /// Request to query site event logs from central.
 /// Supports filtering by event type, severity, instance, time range, and keyword search.
-/// Uses keyset pagination via continuation token (last event ID).
+/// Uses keyset pagination via an opaque continuation token.
 /// </summary>
 /// <param name="InstanceId">
 /// Instance filter matched against the site event log's <c>instance_id</c> column,
 /// which stores the instance <b>UniqueName</b> (InstanceActor.LogLifecycleEvent passes
 /// <c>_instanceUniqueName</c>; EventLogQueryService matches <c>instance_id = $instanceId</c>).
 /// </param>
+/// <param name="ContinuationToken">
+/// Opaque cursor from the previous response's <c>ContinuationToken</c>, or
+/// <see langword="null"/> to start from the oldest matching event. Treat as opaque —
+/// it encodes timestamp and id together and its format is not part of the contract.
+/// An unparseable token is treated as "start from the beginning" rather than an error.
+/// </param>
 public record EventLogQueryRequest(
     string CorrelationId,
     string SiteId,
@@ -19,6 +25,6 @@ public record EventLogQueryRequest(
     string? Severity,
     string? InstanceId,
     string? KeywordFilter,
-    long? ContinuationToken,
+    string? ContinuationToken,
     int PageSize,
     DateTimeOffset Timestamp);

src/ZB.MOM.WW.ScadaBridge.Commons/Messages/RemoteQuery/EventLogQueryResponse.cs

@@ -3,8 +3,18 @@ namespace ZB.MOM.WW.ScadaBridge.Commons.Messages.RemoteQuery;
 /// <summary>
 /// A single event log entry returned from a site query.
 /// </summary>
+/// <param name="Id">
+/// The event's primary key: a GUID string minted by the recording site node.
+/// <para>
+/// This was a <c>long</c> autoincrement id until LocalDb Phase 1. Site pairs now
+/// replicate <c>site_events</c> with last-writer-wins on this key, so a
+/// server-minted integer would have both nodes issuing the same ids for unrelated
+/// events and silently overwriting each other on sync. Consumers must treat it as
+/// an opaque identifier — it carries no ordering.
+/// </para>
+/// </param>
 public record EventLogEntry(
-    long Id,
+    string Id,
     DateTimeOffset Timestamp,
     string EventType,
     string Severity,
@@ -15,13 +25,18 @@ public record EventLogEntry(
 
 /// <summary>
 /// Response containing paginated event log entries from a site.
-/// Uses keyset pagination: ContinuationToken is the last event ID in the result set.
 /// </summary>
+/// <param name="ContinuationToken">
+/// Opaque keyset-pagination cursor: pass it back verbatim on the next request to
+/// continue after the last returned row, or <see langword="null"/> to start from the
+/// beginning. Encodes <c>timestamp</c> and <c>id</c> together, because GUID ids do not
+/// sort chronologically and timestamps alone are not unique. Do not parse it.
+/// </param>
 public record EventLogQueryResponse(
     string CorrelationId,
     string SiteId,
     IReadOnlyList<EventLogEntry> Entries,
-    long? ContinuationToken,
+    string? ContinuationToken,
     bool HasMore,
     bool Success,
     string? ErrorMessage,

src/ZB.MOM.WW.ScadaBridge.HealthMonitoring/ISiteHealthCollector.cs

@@ -141,6 +141,25 @@ public interface ISiteHealthCollector
         // SiteHealthCollector overrides this with the Interlocked.Exchange store.
     }
 
+    /// <summary>
+    /// Replace the latest LocalDb replication status (peer-session connectivity and
+    /// unacked oplog backlog) used by the next <see cref="CollectReport"/> call.
+    /// Refreshed periodically by the <c>LocalDbReplicationStatusReporter</c> hosted
+    /// service. Point-in-time: values are NOT reset on <see cref="CollectReport"/>.
+    /// </summary>
+    /// <param name="connected">
+    /// Whether a sync session is currently running. <see langword="false"/> is the
+    /// normal state on a node with no peer configured — replication ships default-OFF.
+    /// </param>
+    /// <param name="oplogBacklog">
+    /// Unacked oplog entries, or <see langword="null"/> when unknown. Pass null through
+    /// unchanged: a failed poll rendered as 0 would report a broken pair as healthy.
+    /// </param>
+    void SetLocalDbReplicationStatus(bool connected, long? oplogBacklog)
+    {
+        // Default no-op so test fakes do not need to be updated.
+    }
+
     /// <summary>
     /// Replace the latest script-execution-scheduler gauges (queue depth, busy
     /// thread count, and age in seconds of the oldest in-flight script) used by

src/ZB.MOM.WW.ScadaBridge.HealthMonitoring/LocalDbReplicationStatusReporter.cs

@@ -0,0 +1,130 @@
+using Microsoft.Extensions.Hosting;
+using Microsoft.Extensions.Logging;
+
+namespace ZB.MOM.WW.ScadaBridge.HealthMonitoring;
+
+/// <summary>
+/// Site-side hosted service that periodically reads LocalDb replication status and pushes
+/// it into <see cref="ISiteHealthCollector"/>, so the next
+/// <see cref="ISiteHealthCollector.CollectReport"/> emits fresh
+/// <c>LocalDbReplicationConnected</c> / <c>LocalDbOplogBacklog</c> fields.
+/// </summary>
+/// <remarks>
+/// <para>
+/// <b>Why delegates and not <c>ISyncStatus</c> directly.</b> Same reasoning as
+/// <see cref="SiteEventLogFailureCountReporter"/>: HealthMonitoring does not take a
+/// reference on the replication library. The Host site wiring captures the two reads as
+/// lambdas at registration time; this service only moves numbers.
+/// </para>
+/// <para>
+/// <b>Null backlog is preserved, never coerced to zero.</b> <c>ISyncStatus.OplogBacklog</c>
+/// is nullable precisely so a failed poll reads as "unknown" — rendering it as 0 would
+/// report a replication pair that cannot read its own oplog as perfectly healthy.
+/// </para>
+/// <para>
+/// <b>Cadence.</b> 30 s, matching <see cref="SiteEventLogFailureCountReporter"/> and
+/// <c>SiteAuditBacklogReporter</c>. Any exception during a probe is logged and swallowed;
+/// the next tick retries.
+/// </para>
+/// </remarks>
+public sealed class LocalDbReplicationStatusReporter : IHostedService, IDisposable
+{
+    /// <summary>Default poll cadence, matching the other site health bridges.</summary>
+    internal static readonly TimeSpan DefaultRefreshInterval = TimeSpan.FromSeconds(30);
+
+    private readonly Func<bool> _connectedProvider;
+    private readonly Func<long?> _oplogBacklogProvider;
+    private readonly ISiteHealthCollector _collector;
+    private readonly ILogger<LocalDbReplicationStatusReporter> _logger;
+    private readonly TimeSpan _refreshInterval;
+    private CancellationTokenSource? _cts;
+    private Task? _loop;
+
+    /// <summary>Initializes a new instance of <see cref="LocalDbReplicationStatusReporter"/>.</summary>
+    /// <param name="connectedProvider">Reads whether a peer sync session is currently running.</param>
+    /// <param name="oplogBacklogProvider">Reads the unacked oplog backlog, or null when unknown.</param>
+    /// <param name="collector">The site health collector receiving the snapshot.</param>
+    /// <param name="logger">Logger instance.</param>
+    /// <param name="refreshInterval">Poll interval override; defaults to 30 s.</param>
+    public LocalDbReplicationStatusReporter(
+        Func<bool> connectedProvider,
+        Func<long?> oplogBacklogProvider,
+        ISiteHealthCollector collector,
+        ILogger<LocalDbReplicationStatusReporter> logger,
+        TimeSpan? refreshInterval = null)
+    {
+        _connectedProvider = connectedProvider ?? throw new ArgumentNullException(nameof(connectedProvider));
+        _oplogBacklogProvider = oplogBacklogProvider ?? throw new ArgumentNullException(nameof(oplogBacklogProvider));
+        _collector = collector ?? throw new ArgumentNullException(nameof(collector));
+        _logger = logger ?? throw new ArgumentNullException(nameof(logger));
+        _refreshInterval = refreshInterval ?? DefaultRefreshInterval;
+    }
+
+    /// <summary>Starts the polling loop, probing once immediately before entering the timed cycle.</summary>
+    /// <param name="ct">Cancellation token signalling host shutdown.</param>
+    /// <returns>A task that represents the asynchronous operation.</returns>
+    public Task StartAsync(CancellationToken ct)
+    {
+        var cts = CancellationTokenSource.CreateLinkedTokenSource(ct);
+        _cts = cts;
+        _loop = RunAsync(cts.Token);
+        return Task.CompletedTask;
+    }
+
+    /// <summary>Stops the polling loop.</summary>
+    /// <param name="ct">Cancellation token for the stop operation.</param>
+    /// <returns>A task that represents the asynchronous operation.</returns>
+    public async Task StopAsync(CancellationToken ct)
+    {
+        if (_cts is null) return;
+
+        await _cts.CancelAsync().ConfigureAwait(false);
+        if (_loop is not null)
+        {
+            // Await the loop so the reporter is quiescent before the host disposes the
+            // collector out from under it.
+            try { await _loop.ConfigureAwait(false); }
+            catch (OperationCanceledException) { /* expected on shutdown */ }
+        }
+    }
+
+    private async Task RunAsync(CancellationToken ct)
+    {
+        while (!ct.IsCancellationRequested)
+        {
+            Probe();
+
+            try
+            {
+                await Task.Delay(_refreshInterval, ct).ConfigureAwait(false);
+            }
+            catch (OperationCanceledException)
+            {
+                return;
+            }
+        }
+    }
+
+    /// <summary>
+    /// Reads both providers and pushes one snapshot onto the collector; never throws.
+    /// Public so a test can drive a single deterministic probe instead of starting the
+    /// service and waiting out the 30 s cadence.
+    /// </summary>
+    public void Probe()
+    {
+        try
+        {
+            _collector.SetLocalDbReplicationStatus(
+                _connectedProvider(), _oplogBacklogProvider());
+        }
+        catch (Exception ex)
+        {
+            // Health reporting must never take the site node down. The previous snapshot
+            // stays on the collector and the next tick retries.
+            _logger.LogWarning(ex, "Failed to read LocalDb replication status for the site health report.");
+        }
+    }
+
+    /// <inheritdoc />
+    public void Dispose() => _cts?.Dispose();
+}

src/ZB.MOM.WW.ScadaBridge.HealthMonitoring/ServiceCollectionExtensions.cs

@@ -21,6 +21,12 @@ public static class ServiceCollectionExtensions
     /// </summary>
     private sealed class SiteEventLogHealthMetricsBridgeMarker { }
 
+    /// <summary>
+    /// Sentinel marker for <see cref="AddLocalDbReplicationHealthBridge"/>'s idempotency
+    /// guard — same rationale as <see cref="SiteEventLogHealthMetricsBridgeMarker"/>.
+    /// </summary>
+    private sealed class LocalDbReplicationHealthBridgeMarker { }
+
     /// <summary>
     /// Register site-side health monitoring services (metric collection + periodic reporting).
     /// Call this on site nodes only. For central, call AddCentralHealthAggregation() instead.
@@ -153,6 +159,46 @@ public static class ServiceCollectionExtensions
         return services;
     }
 
+    /// <summary>
+    /// Bridge LocalDb replication status (peer connectivity + unacked oplog backlog) onto
+    /// the site health report. Must be called AFTER <c>AddSiteHealthMonitoring</c>
+    /// (registers <see cref="ISiteHealthCollector"/>) and after the replication engine is
+    /// registered. Idempotent via a marker sentinel.
+    /// </summary>
+    /// <remarks>
+    /// Delegates rather than an <c>ISyncStatus</c> parameter keep HealthMonitoring free of
+    /// a reference on the replication library, matching
+    /// <see cref="AddSiteEventLogHealthMetricsBridge"/>. Pass the backlog through as
+    /// nullable — coercing null to 0 would report an unreadable oplog as a healthy one.
+    /// </remarks>
+    /// <param name="services">The service collection to register into.</param>
+    /// <param name="connectedProvider">Given the root provider, returns a reader for "a sync session is running".</param>
+    /// <param name="oplogBacklogProvider">Given the root provider, returns a reader for the unacked backlog (null = unknown).</param>
+    /// <returns>The same <see cref="IServiceCollection"/> for chaining.</returns>
+    public static IServiceCollection AddLocalDbReplicationHealthBridge(
+        this IServiceCollection services,
+        Func<IServiceProvider, Func<bool>> connectedProvider,
+        Func<IServiceProvider, Func<long?>> oplogBacklogProvider)
+    {
+        ArgumentNullException.ThrowIfNull(services);
+        ArgumentNullException.ThrowIfNull(connectedProvider);
+        ArgumentNullException.ThrowIfNull(oplogBacklogProvider);
+
+        if (services.Any(d => d.ServiceType == typeof(LocalDbReplicationHealthBridgeMarker)))
+        {
+            return services;
+        }
+
+        services.AddSingleton<LocalDbReplicationHealthBridgeMarker>();
+        services.AddHostedService(sp => new LocalDbReplicationStatusReporter(
+            connectedProvider(sp),
+            oplogBacklogProvider(sp),
+            sp.GetRequiredService<ISiteHealthCollector>(),
+            sp.GetRequiredService<ILogger<LocalDbReplicationStatusReporter>>()));
+
+        return services;
+    }
+
     /// <summary>
     /// Register the <see cref="HealthMonitoringOptionsValidator"/>
     /// so a misconfigured <c>ScadaBridge:HealthMonitoring</c> section (zero/negative

src/ZB.MOM.WW.ScadaBridge.HealthMonitoring/SiteHealthCollector.cs

@@ -18,6 +18,10 @@ public class SiteHealthCollector : ISiteHealthCollector
     private int _auditRedactionFailures;
     private volatile SiteAuditBacklogSnapshot? _siteAuditBacklog;
     private long _siteEventLogWriteFailures;
+    // One volatile tuple rather than two independent fields: a torn read that paired a
+    // fresh Connected with a stale backlog would be indistinguishable from a real state.
+    // Null = the reporter has not yet run (or replication is not wired).
+    private volatile Tuple<bool, long?>? _localDbReplicationStatus;
     private readonly ConcurrentDictionary<string, ConnectionHealth> _connectionStatuses = new();
     private readonly ConcurrentDictionary<string, TagResolutionStatus> _tagResolutionCounts = new();
     private readonly ConcurrentDictionary<string, string> _connectionEndpoints = new();
@@ -94,6 +98,12 @@ public class SiteHealthCollector : ISiteHealthCollector
         Interlocked.Exchange(ref _siteEventLogWriteFailures, count);
     }
 
+    /// <inheritdoc />
+    public void SetLocalDbReplicationStatus(bool connected, long? oplogBacklog)
+    {
+        _localDbReplicationStatus = Tuple.Create(connected, oplogBacklog);
+    }
+
     /// <inheritdoc />
     public void UpdateConnectionHealth(string connectionName, ConnectionHealth health)
     {
@@ -219,6 +229,9 @@ public class SiteHealthCollector : ISiteHealthCollector
         var deadLetters = Interlocked.Exchange(ref _deadLetterCount, 0);
         var siteAuditWriteFailures = Interlocked.Exchange(ref _siteAuditWriteFailures, 0);
         var auditRedactionFailures = Interlocked.Exchange(ref _auditRedactionFailures, 0);
+        // Single read of the volatile tuple — two reads could straddle a reporter tick and
+        // pair a fresh Connected with a stale backlog.
+        var localDbReplication = _localDbReplicationStatus;
 
         // Snapshot current connection and tag resolution state
         var connectionStatuses = new Dictionary<string, ConnectionHealth>(_connectionStatuses);
@@ -259,7 +272,12 @@ public class SiteHealthCollector : ISiteHealthCollector
         {
             ScriptQueueDepth = Interlocked.CompareExchange(ref _scriptQueueDepth, 0, 0),
             ScriptBusyThreads = Interlocked.CompareExchange(ref _scriptBusyThreads, 0, 0),
-            ScriptOldestBusyAgeSeconds = ReadScriptOldestBusyAgeSeconds()
+            ScriptOldestBusyAgeSeconds = ReadScriptOldestBusyAgeSeconds(),
+            // Both fields come from the ONE snapshot read above. Null (the reporter has
+            // not run) leaves both report fields null — "no data", not "disconnected
+            // with an empty backlog".
+            LocalDbReplicationConnected = localDbReplication?.Item1,
+            LocalDbOplogBacklog = localDbReplication?.Item2
         };
     }
 }

src/ZB.MOM.WW.ScadaBridge.Host/LocalDbSyncAuthInterceptor.cs

@@ -0,0 +1,158 @@
+using System.Security.Cryptography;
+using System.Text;
+using Grpc.Core;
+using Grpc.Core.Interceptors;
+using Microsoft.Extensions.Logging;
+using Microsoft.Extensions.Options;
+using ZB.MOM.WW.LocalDb.Replication;
+
+namespace ZB.MOM.WW.ScadaBridge.Host;
+
+/// <summary>
+/// Gates the LocalDb passive sync endpoint. The replication library deliberately leaves
+/// inbound authentication to the host — its <c>LocalDbSyncService</c> verifies nothing —
+/// so without this interceptor anything that can reach the site node's gRPC port could
+/// stream arbitrary rows into the consolidated site database.
+/// </summary>
+/// <remarks>
+/// <para>
+/// <b>Scoped by method path.</b> Only calls under
+/// <c>/localdb_sync.v1.LocalDbSync/</c> are gated; every other method — notably the
+/// existing <c>SiteStream</c> service sharing this listener — passes through untouched.
+/// </para>
+/// <para>
+/// <b>Fail-closed.</b> With no <c>LocalDb:Replication:ApiKey</c> configured, NO sync
+/// stream is accepted, authenticated or not. That is the deliberate choice: the
+/// alternative — treating "no key" as "no auth required" — would silently expose the
+/// endpoint on exactly the default configuration every site node ships with. An operator
+/// enabling replication must set the same key on both nodes, which is already required
+/// for the initiator to dial out (<c>SyncBackgroundService</c> sends
+/// <c>Authorization: Bearer &lt;key&gt;</c>).
+/// </para>
+/// <para>
+/// Comparison is <see cref="CryptographicOperations.FixedTimeEquals"/> over UTF-8 bytes,
+/// so a wrong key cannot be recovered byte-by-byte from response timing. Length differences
+/// are unavoidably observable and are not sensitive.
+/// </para>
+/// </remarks>
+public sealed class LocalDbSyncAuthInterceptor : Interceptor
+{
+    private const string ServicePrefix = "/localdb_sync.v1.LocalDbSync/";
+    private const string AuthorizationHeader = "authorization";
+    private const string BearerPrefix = "Bearer ";
+
+    private readonly IOptions<ReplicationOptions> _options;
+    private readonly ILogger<LocalDbSyncAuthInterceptor> _logger;
+
+    /// <summary>Creates the interceptor.</summary>
+    /// <param name="options">Replication options; <c>ApiKey</c> is the expected bearer token.</param>
+    /// <param name="logger">Logger for denial diagnostics.</param>
+    public LocalDbSyncAuthInterceptor(
+        IOptions<ReplicationOptions> options,
+        ILogger<LocalDbSyncAuthInterceptor> logger)
+    {
+        ArgumentNullException.ThrowIfNull(options);
+        ArgumentNullException.ThrowIfNull(logger);
+
+        _options = options;
+        _logger = logger;
+    }
+
+    /// <inheritdoc />
+    public override Task<TResponse> UnaryServerHandler<TRequest, TResponse>(
+        TRequest request,
+        ServerCallContext context,
+        UnaryServerMethod<TRequest, TResponse> continuation)
+    {
+        Authorize(context);
+        return continuation(request, context);
+    }
+
+    /// <inheritdoc />
+    public override Task DuplexStreamingServerHandler<TRequest, TResponse>(
+        IAsyncStreamReader<TRequest> requestStream,
+        IServerStreamWriter<TResponse> responseStream,
+        ServerCallContext context,
+        DuplexStreamingServerMethod<TRequest, TResponse> continuation)
+    {
+        Authorize(context);
+        return continuation(requestStream, responseStream, context);
+    }
+
+    /// <inheritdoc />
+    public override Task<TResponse> ClientStreamingServerHandler<TRequest, TResponse>(
+        IAsyncStreamReader<TRequest> requestStream,
+        ServerCallContext context,
+        ClientStreamingServerMethod<TRequest, TResponse> continuation)
+    {
+        Authorize(context);
+        return continuation(requestStream, context);
+    }
+
+    /// <inheritdoc />
+    public override Task ServerStreamingServerHandler<TRequest, TResponse>(
+        TRequest request,
+        IServerStreamWriter<TResponse> responseStream,
+        ServerCallContext context,
+        ServerStreamingServerMethod<TRequest, TResponse> continuation)
+    {
+        Authorize(context);
+        return continuation(request, responseStream, context);
+    }
+
+    /// <summary>
+    /// Throws <see cref="RpcException"/> with <see cref="StatusCode.PermissionDenied"/> if
+    /// this is a sync call that does not carry the configured bearer token. Non-sync calls
+    /// return immediately.
+    /// </summary>
+    private void Authorize(ServerCallContext context)
+    {
+        if (!context.Method.StartsWith(ServicePrefix, StringComparison.Ordinal))
+            return;
+
+        var expected = _options.Value.ApiKey;
+        if (string.IsNullOrEmpty(expected))
+        {
+            _logger.LogWarning(
+                "Rejected a LocalDb sync call to {Method}: no LocalDb:Replication:ApiKey is configured, " +
+                "so the passive sync endpoint is closed. Configure the same key on both nodes of the pair.",
+                context.Method);
+            throw new RpcException(new Status(
+                StatusCode.PermissionDenied,
+                "LocalDb sync is not accepting connections: no API key is configured on this node."));
+        }
+
+        var presented = ExtractBearerToken(context.RequestHeaders);
+        if (presented is null || !FixedTimeEquals(presented, expected))
+        {
+            _logger.LogWarning(
+                "Rejected a LocalDb sync call to {Method}: {Reason}.",
+                context.Method,
+                presented is null ? "no bearer token presented" : "bearer token did not match");
+            throw new RpcException(new Status(
+                StatusCode.PermissionDenied,
+                "LocalDb sync authentication failed."));
+        }
+    }
+
+    private static string? ExtractBearerToken(Metadata headers)
+    {
+        // gRPC lowercases header keys on the wire; compare case-insensitively anyway so a
+        // hand-built Metadata in a test behaves the same as a real request.
+        foreach (var entry in headers)
+        {
+            if (!string.Equals(entry.Key, AuthorizationHeader, StringComparison.OrdinalIgnoreCase))
+                continue;
+
+            var value = entry.Value;
+            if (value is not null && value.StartsWith(BearerPrefix, StringComparison.OrdinalIgnoreCase))
+                return value[BearerPrefix.Length..];
+        }
+
+        return null;
+    }
+
+    private static bool FixedTimeEquals(string presented, string expected)
+        => CryptographicOperations.FixedTimeEquals(
+            Encoding.UTF8.GetBytes(presented), Encoding.UTF8.GetBytes(expected));
+}

src/ZB.MOM.WW.ScadaBridge.Host/Program.cs

@@ -7,6 +7,7 @@ using ZB.MOM.WW.Auth.AspNetCore;
 using ZB.MOM.WW.Health;
 using ZB.MOM.WW.Health.Akka;
 using ZB.MOM.WW.Health.EntityFrameworkCore;
+using ZB.MOM.WW.LocalDb.Replication;
 using ZB.MOM.WW.ScadaBridge.AuditLog;
 using ZB.MOM.WW.ScadaBridge.CentralUI;
 using ZB.MOM.WW.ScadaBridge.ClusterInfrastructure;
@@ -192,6 +193,14 @@ try
         // builds identities with RoleClaimType = ZbClaimTypes.Role, so an Administrator is
         // authorized for both manage + reveal. Done Host-side to keep Secrets.Ui out of Security.
         builder.Services.Configure<AuthorizationOptions>(o => o.AddSecretsAuthorization());
+        // Secrets.Ui components audit through the SHARED ZB.MOM.WW.Audit.IAuditWriter seam,
+        // which is deliberately distinct from the repo's own Commons IAuditWriter — bridge it
+        // to the central direct-write path (ICentralAuditWriter → dbo.AuditLog), NOT the site
+        // SQLite chain, which is never resolved on central and has no forwarder here
+        // (ScadaBridge#22; component activation 500'd without this registration).
+        builder.Services.AddSingleton<ZB.MOM.WW.Audit.IAuditWriter>(
+            sp => new ZB.MOM.WW.ScadaBridge.Host.Services.CentralSharedSeamAuditWriter(
+                sp.GetRequiredService<ZB.MOM.WW.ScadaBridge.Commons.Interfaces.Services.ICentralAuditWriter>()));
         builder.Services.AddInboundAPI();
 
         // Inbound-API auth re-arch: the shared ZB.MOM.WW.Auth.ApiKeys verifier +
@@ -505,10 +514,15 @@ try
         });
 
         // gRPC server registration
-        builder.Services.AddGrpc();
+        // The interceptor gates ONLY /localdb_sync.v1.LocalDbSync/ — SiteStream calls on
+        // this same pipeline pass through untouched. It is fail-closed: with no
+        // LocalDb:Replication:ApiKey configured, no sync stream is accepted at all.
+        builder.Services.AddGrpc(options =>
+            options.Interceptors.Add<LocalDbSyncAuthInterceptor>());
         builder.Services.AddSingleton<ZB.MOM.WW.ScadaBridge.Communication.Grpc.SiteStreamGrpcServer>();
 
-        // Existing site service registrations
+        // Existing site service registrations (this is also where LocalDb and its
+        // replication engine are registered — see SiteServiceRegistration)
         SiteServiceRegistration.Configure(builder.Services, builder.Configuration);
 
         var app = builder.Build();
@@ -527,6 +541,13 @@ try
         // Map gRPC service — resolves the singleton SiteStreamGrpcServer from DI
         app.MapGrpcService<ZB.MOM.WW.ScadaBridge.Communication.Grpc.SiteStreamGrpcServer>();
 
+        // The passive half of LocalDb replication: the peer node dials THIS endpoint.
+        // It shares the Kestrel h2c listener the site gRPC server already uses, so no
+        // listener or port changes are needed. Mapping it is harmless with no peer
+        // configured — nothing dials it, and LocalDbSyncAuthInterceptor (registered on
+        // AddGrpc above) rejects anything that tries until an ApiKey is configured.
+        app.MapZbLocalDbSync();
+
         // Site-shutdown ordering. ApplicationStopping
         // fires BEFORE IHostedService.StopAsync runs, so the gRPC server
         // refuses new streams (Unavailable) and cancels every active stream

src/ZB.MOM.WW.ScadaBridge.Host/Services/CentralSharedSeamAuditWriter.cs

@@ -0,0 +1,30 @@
+using ZB.MOM.WW.Audit;
+using ZB.MOM.WW.ScadaBridge.Commons.Interfaces.Services;
+
+namespace ZB.MOM.WW.ScadaBridge.Host.Services;
+
+/// <summary>
+/// Central-only bridge from the shared-library audit seam
+/// (<see cref="ZB.MOM.WW.Audit.IAuditWriter"/>) to the central direct-write path
+/// (<see cref="ICentralAuditWriter"/> → dbo.AuditLog). Shared-lib UI components —
+/// today the Secrets.Ui components mounted at /admin/secrets — inject the shared
+/// seam, which is deliberately distinct from the repo's own
+/// <see cref="Commons.Interfaces.Services.IAuditWriter"/> (ScadaBridge#22).
+/// </summary>
+/// <remarks>
+/// The target is <see cref="ICentralAuditWriter"/> and NOT the repo's own site
+/// writer chain: the site SQLite chain is registered on central but by design
+/// never resolved there, and events written to it would dead-end in a local file
+/// that no forwarding sidecar drains on a central node. The best-effort contract
+/// (audit failures never abort the user-facing action) is inherited from
+/// <see cref="AuditLog.Central.CentralAuditWriter"/>, which swallows and logs —
+/// so this forward needs no try/catch of its own.
+/// </remarks>
+/// <param name="central">The central direct-write audit path to forward into.</param>
+public sealed class CentralSharedSeamAuditWriter(ICentralAuditWriter central)
+    : ZB.MOM.WW.Audit.IAuditWriter
+{
+    /// <inheritdoc />
+    public Task WriteAsync(AuditEvent auditEvent, CancellationToken cancellationToken = default)
+        => central.WriteAsync(auditEvent, cancellationToken);
+}

src/ZB.MOM.WW.ScadaBridge.Host/SiteLocalDbLegacyMigrator.cs

@@ -0,0 +1,264 @@
+using Microsoft.Data.Sqlite;
+using Microsoft.Extensions.Configuration;
+using ZB.MOM.WW.LocalDb;
+
+namespace ZB.MOM.WW.ScadaBridge.Host;
+
+/// <summary>
+/// One-time copy of the pre-Phase-1 site databases (<c>site-tracking.db</c> and
+/// <c>site_events.db</c>) into the consolidated <c>ZB.MOM.WW.LocalDb</c> database.
+/// </summary>
+/// <remarks>
+/// <para>
+/// <b>Runs after <c>RegisterReplicated</c>, deliberately.</b> Capture is trigger-based, so
+/// rows inserted before registration would never enter the oplog and would never reach the
+/// peer. Migrating after registration means the migrated history replicates like any other
+/// write — which is the entire point of Phase 1.
+/// </para>
+/// <para>
+/// <b>Idempotent twice over.</b> Tracking rows carry the same TEXT primary key they always
+/// had, so <c>INSERT OR IGNORE</c> is naturally idempotent. Legacy events had autoincrement
+/// integer ids, which the consolidated schema replaced with GUIDs; they are given
+/// <i>deterministic</i> ids of the form <c>mig-{NodeName}-{legacyId}</c> rather than fresh
+/// GUIDs, so a migration that crashes and re-runs cannot duplicate events. The NodeName
+/// prefix keeps the two nodes of a pair from colliding on their independent legacy id
+/// sequences.
+/// </para>
+/// <para>
+/// <b>All-or-nothing.</b> The copy runs in one transaction and the legacy file is renamed
+/// to <c>&lt;name&gt;.migrated</c> only after commit. A failure throws out of
+/// <c>OnReady</c>, which fails host startup with the legacy files untouched — no
+/// half-migrated state to reason about. A second boot sees the renamed file and no-ops.
+/// </para>
+/// <para>
+/// <b>Finding nothing is the expected case on the docker rig.</b> Neither legacy config key
+/// is set in any rig appsettings, so both fall back to CWD-relative code defaults
+/// (<c>/app/site-tracking.db</c>, <c>/app/site_events.db</c>) that sit OUTSIDE the mounted
+/// data volume — meaning they were already being discarded on every container recreate.
+/// Phase 1 incidentally fixes that data-loss bug by consolidating into
+/// <c>/app/data/site-localdb.db</c>. A no-op here is a legitimate result, not a failure.
+/// </para>
+/// </remarks>
+public static class SiteLocalDbLegacyMigrator
+{
+    private const string MigratedSuffix = ".migrated";
+
+    /// <summary>Default legacy tracking connection string (<c>OperationTrackingOptions.ConnectionString</c>).</summary>
+    private const string DefaultTrackingConnectionString = "Data Source=site-tracking.db";
+
+    /// <summary>Default legacy event-log path (<c>SiteEventLogOptions.DatabasePath</c>).</summary>
+    private const string DefaultEventLogPath = "site_events.db";
+
+    /// <summary>
+    /// Copies any legacy site databases into <paramref name="db"/>, then renames them.
+    /// </summary>
+    /// <param name="db">The consolidated site database, with both tables already registered.</param>
+    /// <param name="config">Configuration supplying the legacy paths and the node name.</param>
+    public static void Migrate(ILocalDb db, IConfiguration config)
+    {
+        ArgumentNullException.ThrowIfNull(db);
+        ArgumentNullException.ThrowIfNull(config);
+
+        var nodeName = config["ScadaBridge:Node:NodeName"] ?? "unknown-node";
+
+        MigrateTracking(db, ResolveTrackingPath(config));
+        MigrateEvents(db, ResolveEventLogPath(config), nodeName);
+    }
+
+    /// <summary>
+    /// Resolves the legacy tracking database path from the OLD connection-string key,
+    /// falling back to the code default. Relative paths resolve against the process working
+    /// directory — NOT the data volume — because that is where the old code actually put them.
+    /// </summary>
+    internal static string ResolveTrackingPath(IConfiguration config)
+    {
+        var connectionString =
+            config["ScadaBridge:OperationTracking:ConnectionString"] ?? DefaultTrackingConnectionString;
+
+        string dataSource;
+        try
+        {
+            dataSource = new SqliteConnectionStringBuilder(connectionString).DataSource;
+        }
+        catch (ArgumentException)
+        {
+            // An unparseable legacy connection string means there is nothing to migrate
+            // from. Do not fail the host over a stale key we are about to stop reading.
+            return string.Empty;
+        }
+
+        // In-memory legacy databases (test/dev configurations) have nothing durable to
+        // migrate, and Path.GetFullPath on them would produce nonsense.
+        if (string.IsNullOrWhiteSpace(dataSource) ||
+            dataSource.Equals(":memory:", StringComparison.OrdinalIgnoreCase) ||
+            dataSource.StartsWith("file:", StringComparison.OrdinalIgnoreCase))
+        {
+            return string.Empty;
+        }
+
+        return Path.GetFullPath(dataSource);
+    }
+
+    /// <summary>Resolves the legacy event-log path from the OLD key, falling back to the code default.</summary>
+    internal static string ResolveEventLogPath(IConfiguration config)
+    {
+        var path = config["ScadaBridge:SiteEventLog:DatabasePath"] ?? DefaultEventLogPath;
+
+        if (string.IsNullOrWhiteSpace(path) ||
+            path.Equals(":memory:", StringComparison.OrdinalIgnoreCase))
+        {
+            return string.Empty;
+        }
+
+        return Path.GetFullPath(path);
+    }
+
+    private static void MigrateTracking(ILocalDb db, string legacyPath)
+    {
+        if (!ShouldMigrate(legacyPath)) return;
+
+        var rows = ReadAll(
+            legacyPath,
+            """
+            SELECT TrackedOperationId, Kind, TargetSummary, Status,
+                   RetryCount, LastError, HttpStatus,
+                   CreatedAtUtc, UpdatedAtUtc, TerminalAtUtc,
+                   SourceInstanceId, SourceScript, SourceNode
+            FROM   OperationTracking;
+            """,
+            expectedColumns: 13);
+
+        // A legacy file with no OperationTracking table at all reads as "nothing to do";
+        // it still gets renamed so the probe does not repeat on every boot.
+        if (rows is not null)
+        {
+            using var connection = db.CreateConnection();
+            using var transaction = connection.BeginTransaction();
+
+            foreach (var row in rows)
+            {
+                using var cmd = connection.CreateCommand();
+                cmd.Transaction = transaction;
+                cmd.CommandText = """
+                    INSERT OR IGNORE INTO OperationTracking (
+                        TrackedOperationId, Kind, TargetSummary, Status,
+                        RetryCount, LastError, HttpStatus,
+                        CreatedAtUtc, UpdatedAtUtc, TerminalAtUtc,
+                        SourceInstanceId, SourceScript, SourceNode
+                    ) VALUES (
+                        $p0, $p1, $p2, $p3, $p4, $p5, $p6,
+                        $p7, $p8, $p9, $p10, $p11, $p12
+                    );
+                    """;
+                Bind(cmd, row);
+                cmd.ExecuteNonQuery();
+            }
+
+            transaction.Commit();
+        }
+
+        MarkMigrated(legacyPath);
+    }
+
+    private static void MigrateEvents(ILocalDb db, string legacyPath, string nodeName)
+    {
+        if (!ShouldMigrate(legacyPath)) return;
+
+        var rows = ReadAll(
+            legacyPath,
+            """
+            SELECT id, timestamp, event_type, severity, instance_id, source, message, details
+            FROM   site_events;
+            """,
+            expectedColumns: 8);
+
+        if (rows is not null)
+        {
+            using var connection = db.CreateConnection();
+            using var transaction = connection.BeginTransaction();
+
+            foreach (var row in rows)
+            {
+                using var cmd = connection.CreateCommand();
+                cmd.Transaction = transaction;
+                cmd.CommandText = """
+                    INSERT OR IGNORE INTO site_events (
+                        id, timestamp, event_type, severity, instance_id, source, message, details
+                    ) VALUES (
+                        $id, $p1, $p2, $p3, $p4, $p5, $p6, $p7
+                    );
+                    """;
+
+                // Deterministic id, NOT a fresh GUID: a crashed-then-rerun migration must
+                // not duplicate events, and INSERT OR IGNORE can only dedupe on a stable key.
+                cmd.Parameters.AddWithValue("$id", $"mig-{nodeName}-{row[0] ?? "null"}");
+                for (var i = 1; i < row.Length; i++)
+                    cmd.Parameters.AddWithValue($"$p{i}", row[i] ?? (object)DBNull.Value);
+
+                cmd.ExecuteNonQuery();
+            }
+
+            transaction.Commit();
+        }
+
+        MarkMigrated(legacyPath);
+    }
+
+    /// <summary>True when there is a legacy file present that has not already been migrated.</summary>
+    private static bool ShouldMigrate(string legacyPath)
+        => !string.IsNullOrEmpty(legacyPath)
+           && File.Exists(legacyPath)
+           && !File.Exists(legacyPath + MigratedSuffix);
+
+    /// <summary>
+    /// Reads every row of <paramref name="sql"/> from the legacy file, or null when the
+    /// table does not exist (an old file predating the table is not an error).
+    /// </summary>
+    private static List<object?[]>? ReadAll(string legacyPath, string sql, int expectedColumns)
+    {
+        using var connection = new SqliteConnection($"Data Source={legacyPath};Mode=ReadOnly");
+        connection.Open();
+
+        using var cmd = connection.CreateCommand();
+        cmd.CommandText = sql;
+
+        SqliteDataReader reader;
+        try
+        {
+            reader = cmd.ExecuteReader();
+        }
+        catch (SqliteException)
+        {
+            // "no such table" / "no such column" — a legacy file from before this table
+            // existed, or a shape we do not recognise. Nothing to copy.
+            return null;
+        }
+
+        var rows = new List<object?[]>();
+        using (reader)
+        {
+            while (reader.Read())
+            {
+                var row = new object?[expectedColumns];
+                for (var i = 0; i < expectedColumns; i++)
+                    row[i] = reader.IsDBNull(i) ? null : reader.GetValue(i);
+                rows.Add(row);
+            }
+        }
+
+        return rows;
+    }
+
+    private static void Bind(SqliteCommand cmd, object?[] row)
+    {
+        for (var i = 0; i < row.Length; i++)
+            cmd.Parameters.AddWithValue($"$p{i}", row[i] ?? (object)DBNull.Value);
+    }
+
+    /// <summary>
+    /// Renames the legacy file so a later boot skips it. Done only after the copy has
+    /// committed; the file is kept rather than deleted so an operator can still inspect it.
+    /// </summary>
+    private static void MarkMigrated(string legacyPath)
+        => File.Move(legacyPath, legacyPath + MigratedSuffix);
+}

src/ZB.MOM.WW.ScadaBridge.Host/SiteLocalDbSetup.cs

@@ -0,0 +1,59 @@
+using Microsoft.Extensions.Configuration;
+using ZB.MOM.WW.LocalDb;
+using ZB.MOM.WW.ScadaBridge.SiteEventLogging;
+using ZB.MOM.WW.ScadaBridge.SiteRuntime.Tracking;
+
+namespace ZB.MOM.WW.ScadaBridge.Host;
+
+/// <summary>
+/// Initializes the consolidated site database — the single <c>ZB.MOM.WW.LocalDb</c>-managed
+/// file that holds the site node's replicated state.
+/// </summary>
+/// <remarks>
+/// <para>
+/// <b>Ordering is load-bearing.</b> Table DDL must run BEFORE
+/// <c>RegisterReplicated</c>, and every row that should replicate must be written
+/// AFTER it. Capture is trigger-based change-data-capture: the library neither
+/// captures nor snapshots rows that already existed when the triggers were installed,
+/// so a row written before registration is invisible to the peer forever — silently,
+/// with no error anywhere.
+/// </para>
+/// <para>
+/// Runs inside the <c>AddZbLocalDb</c> onReady callback, once, before any caller
+/// receives the <see cref="ILocalDb"/> singleton. onReady is a synchronous
+/// <c>Action&lt;ILocalDb&gt;</c>, hence the direct <c>CreateConnection()</c> rather than
+/// blocking on the async execute API. A throw here propagates out of the first
+/// <c>GetRequiredService&lt;ILocalDb&gt;()</c> and fails host startup, which is the
+/// intent: a site node that cannot establish its schema must not come up half-working.
+/// </para>
+/// </remarks>
+public static class SiteLocalDbSetup
+{
+    /// <summary>
+    /// Creates the site node's replicated tables, opts them into change capture, and
+    /// migrates any pre-Phase-1 databases in.
+    /// </summary>
+    /// <param name="db">The LocalDb instance being initialized.</param>
+    /// <param name="config">Configuration, for the legacy migrator's old path keys and node name.</param>
+    public static void OnReady(ILocalDb db, IConfiguration config)
+    {
+        ArgumentNullException.ThrowIfNull(db);
+        ArgumentNullException.ThrowIfNull(config);
+
+        using (var connection = db.CreateConnection())
+        {
+            OperationTrackingSchema.Apply(connection);
+            SiteEventLogSchema.Apply(connection);
+        }
+
+        // Both tables qualify: each has an explicit primary key (RegisterReplicated
+        // rejects tables without one) and no BLOB columns (which json_object cannot
+        // capture). Registration is idempotent and installs the capture triggers.
+        db.RegisterReplicated("OperationTracking");
+        db.RegisterReplicated("site_events");
+
+        // AFTER registration, so migrated rows enter the oplog and reach the peer like
+        // any other write. Before it, they would be invisible to replication forever.
+        SiteLocalDbLegacyMigrator.Migrate(db, config);
+    }
+}

src/ZB.MOM.WW.ScadaBridge.Host/SiteServiceRegistration.cs

@@ -14,6 +14,8 @@ using ZB.MOM.WW.ScadaBridge.NotificationService;
 using ZB.MOM.WW.ScadaBridge.SiteEventLogging;
 using ZB.MOM.WW.ScadaBridge.SiteRuntime;
 using ZB.MOM.WW.ScadaBridge.StoreAndForward;
+using ZB.MOM.WW.LocalDb;
+using ZB.MOM.WW.LocalDb.Replication;
 using ZB.MOM.WW.Secrets.DependencyInjection;
 using ZB.MOM.WW.Telemetry;
 
@@ -25,6 +27,24 @@ namespace ZB.MOM.WW.ScadaBridge.Host;
 /// </summary>
 public static class SiteServiceRegistration
 {
+    /// <summary>
+    /// Every <see cref="System.Diagnostics.Metrics.Meter"/> OpenTelemetry is told to observe.
+    /// </summary>
+    /// <remarks>
+    /// <c>ZbTelemetryOptions.Meters</c> is an ALLOWLIST, and an unlisted meter's instruments
+    /// are simply never observed — no error, no warning, no missing-metric signal anywhere.
+    /// The LocalDb replication meter was silently absent from the rig's <c>/metrics</c>
+    /// scrape until the Phase 1 live gate went looking for it (the plan had assumed it
+    /// flowed through automatically). Hoisted to a named constant so the list is
+    /// assertable; adding a meter anywhere in the product means adding it here.
+    /// </remarks>
+    public static readonly string[] ObservedMeters =
+    [
+        ScadaBridgeTelemetry.MeterName,
+        // Emitted only on site nodes; harmless on central, which never records against it.
+        LocalDbMetrics.MeterName,
+    ];
+
     /// <summary>Registers all DI services required for the site role.</summary>
     /// <param name="services">The service collection to register into.</param>
     /// <param name="config">Application configuration for options binding.</param>
@@ -49,6 +69,28 @@ public static class SiteServiceRegistration
         // and site-local repository implementations (IExternalSystemRepository, INotificationRepository)
         var siteDbPath = config["ScadaBridge:Database:SiteDbPath"] ?? "site.db";
         services.AddSiteRuntime($"Data Source={siteDbPath}");
+
+        // Consolidated site database (LocalDb Phase 1). Holds OperationTracking and
+        // site_events as replicated tables so the pair stops losing them on failover.
+        //
+        // Storage is registered UNCONDITIONALLY and needs no flag: with no peer
+        // configured, LocalDb is simply a local SQLite file and the replication
+        // initiator idles.
+        //
+        // Design: scadaproj docs/plans/2026-07-19-scadabridge-localdb-design.md
+        services.AddZbLocalDb(config, db => SiteLocalDbSetup.OnReady(db, config));
+
+        // The replication engine, likewise unconditional but INERT by default: with no
+        // LocalDb:Replication:PeerAddress the initiating SyncBackgroundService starts and
+        // immediately idles, and nothing dials the passive endpoint. A site pair
+        // replicates only once an operator sets a peer on both nodes.
+        //
+        // Registered HERE rather than in Program.cs (where the plan first put it) so the
+        // composition-root tests, which build this graph and not Program.cs, actually
+        // cover it. The endpoint half — MapZbLocalDbSync — has to stay in Program.cs
+        // because it needs the WebApplication.
+        services.AddZbLocalDbReplication(config);
+
         services.AddDataConnectionLayer();
         // Local SQLite store by default; a local store replicating against a shared SQL-Server hub
         // only when Secrets:Replication:Enabled is true AND a hub connection string is present.
@@ -76,6 +118,16 @@ public static class SiteServiceRegistration
         services.AddSiteEventLogHealthMetricsBridge(
             sp => () => sp.GetRequiredService<ISiteEventLogger>().FailedWriteCount);
 
+        // LocalDb replication — bridge ISyncStatus onto the site health report. Registered
+        // unconditionally alongside the engine: on a node with no peer this reports
+        // Connected=false with a real 0 backlog, which is the healthy default-OFF state.
+        // OplogBacklog is passed through NULLABLE on purpose — null means the poll failed
+        // or no provider is wired, and flattening it to 0 would render a replication pair
+        // that cannot read its own oplog as perfectly healthy.
+        services.AddLocalDbReplicationHealthBridge(
+            sp => () => sp.GetRequiredService<ISyncStatus>().Connected,
+            sp => () => sp.GetRequiredService<ISyncStatus>().OplogBacklog);
+
         // Audit Log — site-side hot-path writer + telemetry collaborators.
         // The SiteAuditTelemetryActor itself is registered by AkkaHostedService
         // in the site-role block; this call wires every DI dependency it (and
@@ -228,7 +280,7 @@ public static class SiteServiceRegistration
             o.ServiceName = "scadabridge";
             o.SiteId = config["ScadaBridge:Node:SiteId"] ?? "central";
             o.NodeRole = config["ScadaBridge:Node:Role"];
-            o.Meters = [ScadaBridgeTelemetry.MeterName];
+            o.Meters = ObservedMeters;
             if (Enum.TryParse<ZbExporter>(config["ScadaBridge:Telemetry:Exporter"], ignoreCase: true, out var exporter))
                 o.Exporter = exporter;
             var otlp = config["ScadaBridge:Telemetry:OtlpEndpoint"];

src/ZB.MOM.WW.ScadaBridge.Host/ZB.MOM.WW.ScadaBridge.Host.csproj

@@ -41,6 +41,8 @@
     <PackageReference Include="ZB.MOM.WW.Secrets.Abstractions" />
     <PackageReference Include="ZB.MOM.WW.Secrets.Ui" />
     <PackageReference Include="ZB.MOM.WW.Secrets.Replicator.SqlServer" />
+    <PackageReference Include="ZB.MOM.WW.LocalDb" />
+    <PackageReference Include="ZB.MOM.WW.LocalDb.Replication" />
   </ItemGroup>
 
   <ItemGroup>

src/ZB.MOM.WW.ScadaBridge.Host/appsettings.Site.json

@@ -58,5 +58,11 @@
     "Logging": {
       "MinimumLevel": "Information"
     }
+  },
+  // Consolidated site database (LocalDb Phase 1): OperationTracking + site_events.
+  // LocalDb:Path is REQUIRED and validated on start - a site node with no value here
+  // fails to boot, so every site config must set it.
+  "LocalDb": {
+    "Path": "./data/site-localdb.db"
   }
 }

src/ZB.MOM.WW.ScadaBridge.SiteEventLogging/EventLogPurgeService.cs

@@ -159,9 +159,16 @@ public class EventLogPurgeService : BackgroundService
             var deleted = _eventLogger.WithConnection(connection =>
             {
                 using var cmd = connection.CreateCommand();
+                // Order by timestamp, NOT by id. The id was a monotonic autoincrement
+                // when this was written, so "lowest id" meant "oldest"; since LocalDb
+                // Phase 1 it is a GUID and ordering by it would delete an ARBITRARY
+                // batch of events rather than the oldest ones. id remains in the ORDER
+                // BY only as a tie-break so the batch is deterministic.
                 cmd.CommandText = $"""
                     DELETE FROM site_events WHERE id IN (
-                        SELECT id FROM site_events ORDER BY id ASC LIMIT {CapPurgeBatchSize}
+                        SELECT id FROM site_events
+                        ORDER BY timestamp ASC, id ASC
+                        LIMIT {CapPurgeBatchSize}
                     )
                     """;
                 var rows = cmd.ExecuteNonQuery();

src/ZB.MOM.WW.ScadaBridge.SiteEventLogging/EventLogQueryService.cs

@@ -48,6 +48,52 @@ public class EventLogQueryService : IEventLogQueryService
             .Replace("_", "\\_");
     }
 
+    /// <summary>
+    /// Separator between the timestamp and id halves of a continuation token. Chosen
+    /// because it cannot occur in an ISO 8601 "o" timestamp or in a GUID "N" string,
+    /// so the split is unambiguous.
+    /// </summary>
+    private const char TokenSeparator = '|';
+
+    /// <summary>
+    /// Builds the opaque keyset cursor for the last row of a page. The token carries
+    /// the exact stored timestamp string (not a re-formatted <see cref="DateTimeOffset"/>)
+    /// so it compares byte-for-byte against the column under SQLite's BINARY collation.
+    /// </summary>
+    private static string FormatContinuationToken(EventLogEntry last) =>
+        string.Concat(
+            last.Timestamp.ToUniversalTime().ToString("o", CultureInfo.InvariantCulture),
+            TokenSeparator,
+            last.Id);
+
+    /// <summary>
+    /// Parses a continuation token back into its timestamp and id halves.
+    /// Returns <see langword="false"/> for null, empty, or malformed tokens — including
+    /// a legacy numeric token from a client that predates the GUID id change — in which
+    /// case the caller starts from the beginning instead of failing the query.
+    /// </summary>
+    private static bool TryParseContinuationToken(
+        string? token, out string timestamp, out string id)
+    {
+        timestamp = "";
+        id = "";
+
+        if (string.IsNullOrWhiteSpace(token))
+        {
+            return false;
+        }
+
+        var separator = token.IndexOf(TokenSeparator);
+        if (separator <= 0 || separator == token.Length - 1)
+        {
+            return false;
+        }
+
+        timestamp = token[..separator];
+        id = token[(separator + 1)..];
+        return true;
+    }
+
     /// <inheritdoc />
     public EventLogQueryResponse ExecuteQuery(EventLogQueryRequest request)
     {
@@ -64,11 +110,23 @@ public class EventLogQueryService : IEventLogQueryService
             var whereClauses = new List<string>();
             var parameters = new List<SqliteParameter>();
 
-            // Keyset pagination: only return events with id > continuation token
+            // Keyset pagination on the composite (timestamp, id) key.
-            if (request.ContinuationToken.HasValue)
+            //
+            // This used to be a plain "id > $afterId" against a monotonic autoincrement
+            // id. Since LocalDb Phase 1 the id is a GUID, which does not sort
+            // chronologically — a naive "id > x" would return an arbitrary subset and
+            // SILENTLY DROP ROWS from the page-through. Timestamps alone are not unique
+            // (two events can share a tick), so the tie is broken on id to guarantee a
+            // total order and exactly-once paging.
+            //
+            // A malformed token is ignored rather than throwing: an old long-typed token
+            // from a client mid-upgrade degrades to "start from the beginning", which is
+            // a visible repeat rather than silent data loss.
+            if (TryParseContinuationToken(request.ContinuationToken, out var afterTs, out var afterId))
             {
-                whereClauses.Add("id > $afterId");
+                whereClauses.Add("(timestamp > $afterTs OR (timestamp = $afterTs AND id > $afterId))");
-                parameters.Add(new SqliteParameter("$afterId", request.ContinuationToken.Value));
+                parameters.Add(new SqliteParameter("$afterTs", afterTs));
+                parameters.Add(new SqliteParameter("$afterId", afterId));
             }
 
             if (request.From.HasValue)
@@ -138,7 +196,7 @@ public class EventLogQueryService : IEventLogQueryService
                     SELECT id, timestamp, event_type, severity, instance_id, source, message, details
                     FROM site_events
                     {whereClause}
-                    ORDER BY id ASC
+                    ORDER BY timestamp ASC, id ASC
                     LIMIT $limit
                     """;
                 cmd.Parameters.AddWithValue("$limit", pageSize + 1);
@@ -150,7 +208,7 @@ public class EventLogQueryService : IEventLogQueryService
                 while (reader.Read())
                 {
                     rows.Add(new EventLogEntry(
-                        Id: reader.GetInt64(0),
+                        Id: reader.GetString(0),
                         // Parse with explicit invariant culture and round-trip style.
                         // Stored values are ISO 8601 "o" UTC
                         // (see SiteEventLogger.LogEventAsync), and the recorder's
@@ -178,7 +236,9 @@ public class EventLogQueryService : IEventLogQueryService
                 entries.RemoveAt(entries.Count - 1);
             }
 
-            var continuationToken = entries.Count > 0 ? entries[^1].Id : (long?)null;
+            var continuationToken = entries.Count > 0
+                ? FormatContinuationToken(entries[^1])
+                : null;
 
             return new EventLogQueryResponse(
                 CorrelationId: request.CorrelationId,

src/ZB.MOM.WW.ScadaBridge.SiteEventLogging/SiteEventLogOptions.cs

@@ -6,7 +6,14 @@ public class SiteEventLogOptions
     public int RetentionDays { get; set; } = 30;
     /// <summary>Maximum SQLite database size in megabytes before old entries are purged; default 1024 MB.</summary>
     public int MaxStorageMb { get; set; } = 1024;
-    /// <summary>File path for the site event log SQLite database.</summary>
+    /// <summary>
+    /// MIGRATION-ONLY as of LocalDb Phase 1. <see cref="SiteEventLogger"/> no longer reads
+    /// this: <c>site_events</c> lives in the consolidated site database at
+    /// <c>LocalDb:Path</c>. The only remaining consumer is
+    /// <c>SiteLocalDbLegacyMigrator</c>, which uses it to locate a pre-Phase-1
+    /// <c>site_events.db</c> and copy it in once. Safe to delete from a config once that
+    /// node's migration has run.
+    /// </summary>
     public string DatabasePath { get; set; } = "site_events.db";
     /// <summary>Maximum number of rows returned per paginated query; default 500.</summary>
     public int QueryPageSize { get; set; } = 500;

src/ZB.MOM.WW.ScadaBridge.SiteEventLogging/SiteEventLogSchema.cs

@@ -0,0 +1,71 @@
+using Microsoft.Data.Sqlite;
+
+namespace ZB.MOM.WW.ScadaBridge.SiteEventLogging;
+
+/// <summary>
+/// DDL for the <c>site_events</c> table, extracted from <see cref="SiteEventLogger"/> so
+/// it can be applied by whoever owns the database file.
+/// <para>
+/// Under LocalDb Phase 1 that owner is the Host's <c>AddZbLocalDb</c> onReady callback:
+/// the table must exist before <c>ILocalDb.RegisterReplicated</c> installs its capture
+/// triggers, and rows written before registration are never captured or snapshotted.
+/// </para>
+/// <para>
+/// <b>Schema change.</b> The primary key is a TEXT GUID minted by the writer, replacing
+/// the historical <c>INTEGER PRIMARY KEY AUTOINCREMENT</c>. This is required, not
+/// cosmetic: replication resolves conflicts by last-writer-wins on the primary key, so
+/// two site nodes each minting autoincrement id 1 for unrelated events would be treated
+/// as one row and silently overwrite each other. GUID keys make the event log a pure
+/// union across the pair. Consumers that relied on the id being monotonic — the keyset
+/// paging cursor and the storage-cap purge — order by <c>timestamp</c> instead.
+/// </para>
+/// <para>
+/// Deliberately depends only on <c>Microsoft.Data.Sqlite</c>, not on the LocalDb
+/// library — the Host needs to apply this DDL to a LocalDb-managed connection, but
+/// nothing about the schema itself is LocalDb-specific.
+/// </para>
+/// </summary>
+public static class SiteEventLogSchema
+{
+    /// <summary>
+    /// Creates the <c>site_events</c> table and its indexes when absent. Idempotent —
+    /// safe to run on every startup.
+    /// </summary>
+    /// <param name="connection">An open connection to the database that should hold the table.</param>
+    public static void Apply(SqliteConnection connection)
+    {
+        ArgumentNullException.ThrowIfNull(connection);
+
+        // auto_vacuum must be set before any table is created for it to take effect
+        // on a fresh database. With INCREMENTAL mode, PRAGMA incremental_vacuum can
+        // later reclaim free pages so the storage-cap purge can shrink the file.
+        using (var pragmaCmd = connection.CreateCommand())
+        {
+            pragmaCmd.CommandText = "PRAGMA auto_vacuum = INCREMENTAL";
+            pragmaCmd.ExecuteNonQuery();
+        }
+
+        using var cmd = connection.CreateCommand();
+        cmd.CommandText = """
+            CREATE TABLE IF NOT EXISTS site_events (
+                id TEXT NOT NULL PRIMARY KEY,
+                timestamp TEXT NOT NULL,
+                event_type TEXT NOT NULL,
+                severity TEXT NOT NULL,
+                instance_id TEXT,
+                source TEXT NOT NULL,
+                message TEXT NOT NULL,
+                details TEXT
+            );
+            CREATE INDEX IF NOT EXISTS idx_events_timestamp ON site_events(timestamp);
+            CREATE INDEX IF NOT EXISTS idx_events_type ON site_events(event_type);
+            CREATE INDEX IF NOT EXISTS idx_events_instance ON site_events(instance_id);
+            CREATE INDEX IF NOT EXISTS idx_events_severity ON site_events(severity);
+            """;
+        // The query service also supports keyword search via leading-wildcard
+        // LIKE on message/source. A leading-wildcard LIKE cannot use a B-tree
+        // index, so that path intentionally full-scans; severity/event_type/
+        // instance_id/timestamp filters above are all covered.
+        cmd.ExecuteNonQuery();
+    }
+}

src/ZB.MOM.WW.ScadaBridge.SiteEventLogging/SiteEventLogger.cs

@@ -2,13 +2,16 @@ using System.Threading.Channels;
 using Microsoft.Data.Sqlite;
 using Microsoft.Extensions.Logging;
 using Microsoft.Extensions.Options;
+using ZB.MOM.WW.LocalDb;
 
 namespace ZB.MOM.WW.ScadaBridge.SiteEventLogging;
 
 /// <summary>
-/// Records operational events to a local SQLite database.
+/// Records operational events into the consolidated site database (LocalDb Phase 1).
-/// Only the active node generates events. Not replicated to standby.
+/// Only the active node generates events, but <c>site_events</c> is a replicated table:
-/// On failover, the new active node starts a fresh log.
+/// when the pair has a peer configured, the standby holds the same history and a failover
+/// no longer starts a fresh log. With no peer configured the database is simply local —
+/// replication is opt-in via <c>LocalDb:Replication:PeerAddress</c>.
 /// </summary>
 /// <remarks>
 /// <para>
@@ -40,29 +43,36 @@ public class SiteEventLogger : ISiteEventLogger, IDisposable
     private bool _disposed;
 
     /// <summary>
-    /// Initializes the event logger, opens the SQLite connection, and starts the background writer loop.
+    /// Initializes the event logger over the consolidated site database and starts the
+    /// background writer loop.
     /// </summary>
-    /// <param name="options">Site event log configuration (database path, retention settings).</param>
+    /// <param name="options">Site event log configuration (retention settings, queue capacity).</param>
     /// <param name="logger">Logger for write-failure diagnostics.</param>
-    /// <param name="connectionStringOverride">Optional connection string override; uses the configured path when null.</param>
+    /// <param name="localDb">
+    /// The consolidated site database. The connection it hands out is already open and
+    /// carries the <c>zb_hlc_next()</c> UDF that <c>site_events</c>' capture triggers
+    /// call. This logger owns the connection for its lifetime and disposes it.
+    /// </param>
+    /// <remarks>
+    /// The former <c>connectionStringOverride</c> seam is deliberately gone rather than
+    /// preserved. <c>site_events</c> is a replicated table, so a raw connection lacking
+    /// the UDF would fail closed on every insert — an override would only let a caller
+    /// build a logger that cannot write. <c>SiteEventLogOptions.DatabasePath</c> is
+    /// likewise no longer read: the location is <c>LocalDb:Path</c>.
+    /// </remarks>
     public SiteEventLogger(
         IOptions<SiteEventLogOptions> options,
         ILogger<SiteEventLogger> logger,
-        string? connectionStringOverride = null)
+        ILocalDb localDb)
     {
+        ArgumentNullException.ThrowIfNull(options);
+        ArgumentNullException.ThrowIfNull(localDb);
+
         _logger = logger;
 
-        // Cache=Shared is a cross-connection optimisation
+        // Already open — CreateConnection returns a live, pragma-configured,
-        // that lets multiple SqliteConnections share an in-process page cache.
+        // UDF-registered connection. Calling Open() on it again would throw.
-        // This logger owns exactly one SqliteConnection and serialises all
+        _connection = localDb.CreateConnection();
-        // access through _writeLock, so the mode is dormant — at best dead
-        // configuration, at worst a small future foot-gun for any second
-        // connection opened to the same file. A test path that genuinely
-        // needs Cache=Shared can still inject it via connectionStringOverride.
-        var connectionString = connectionStringOverride
-            ?? $"Data Source={options.Value.DatabasePath}";
-        _connection = new SqliteConnection(connectionString);
-        _connection.Open();
 
         InitializeSchema();
 
@@ -130,40 +140,11 @@ public class SiteEventLogger : ISiteEventLogger, IDisposable
         }
     }
 
-    private void InitializeSchema()
+    // Schema lives in SiteEventLogSchema so the Host's AddZbLocalDb onReady callback
-    {
+    // can create this table in the consolidated site database before RegisterReplicated
-        // auto_vacuum must be set before any table is created for it to take effect
+    // installs its capture triggers. Applying it here too keeps a directly-constructed
-        // on a fresh database. With INCREMENTAL mode, PRAGMA incremental_vacuum can
+    // logger (tests, tooling) self-sufficient; the DDL is idempotent.
-        // later reclaim free pages so the storage-cap purge can shrink the file.
+    private void InitializeSchema() => SiteEventLogSchema.Apply(_connection);
-        using (var pragmaCmd = _connection.CreateCommand())
-        {
-            pragmaCmd.CommandText = "PRAGMA auto_vacuum = INCREMENTAL";
-            pragmaCmd.ExecuteNonQuery();
-        }
-
-        using var cmd = _connection.CreateCommand();
-        cmd.CommandText = """
-            CREATE TABLE IF NOT EXISTS site_events (
-                id INTEGER PRIMARY KEY AUTOINCREMENT,
-                timestamp TEXT NOT NULL,
-                event_type TEXT NOT NULL,
-                severity TEXT NOT NULL,
-                instance_id TEXT,
-                source TEXT NOT NULL,
-                message TEXT NOT NULL,
-                details TEXT
-            );
-            CREATE INDEX IF NOT EXISTS idx_events_timestamp ON site_events(timestamp);
-            CREATE INDEX IF NOT EXISTS idx_events_type ON site_events(event_type);
-            CREATE INDEX IF NOT EXISTS idx_events_instance ON site_events(instance_id);
-            CREATE INDEX IF NOT EXISTS idx_events_severity ON site_events(severity);
-            """;
-        // The query service also supports keyword search via leading-wildcard
-        // LIKE on message/source. A leading-wildcard LIKE cannot use a B-tree
-        // index, so that path intentionally full-scans; severity/event_type/
-        // instance_id/timestamp filters above are all covered.
-        cmd.ExecuteNonQuery();
-    }
 
     /// <summary>
     /// Closed set of allowed severities. Case-sensitive to
@@ -198,7 +179,13 @@ public class SiteEventLogger : ISiteEventLogger, IDisposable
                 nameof(severity));
         }
 
+        // The id is minted here, by the application, rather than by SQLite. Under
+        // LocalDb replication the site pair converges by last-writer-wins on the
+        // primary key, so a server-minted AUTOINCREMENT would have both nodes
+        // independently issuing id 1, 2, 3... for unrelated events and silently
+        // overwriting each other on sync. A GUID makes the event log a pure union.
         var pending = new PendingEvent(
+            Guid.NewGuid().ToString("N"),
             DateTimeOffset.UtcNow.ToString("o"),
             eventType,
             severity,
@@ -235,9 +222,10 @@ public class SiteEventLogger : ISiteEventLogger, IDisposable
                 {
                     using var cmd = connection.CreateCommand();
                     cmd.CommandText = """
-                        INSERT INTO site_events (timestamp, event_type, severity, instance_id, source, message, details)
+                        INSERT INTO site_events (id, timestamp, event_type, severity, instance_id, source, message, details)
-                        VALUES ($timestamp, $event_type, $severity, $instance_id, $source, $message, $details)
+                        VALUES ($id, $timestamp, $event_type, $severity, $instance_id, $source, $message, $details)
                         """;
+                    cmd.Parameters.AddWithValue("$id", pending.Id);
                     cmd.Parameters.AddWithValue("$timestamp", pending.Timestamp);
                     cmd.Parameters.AddWithValue("$event_type", pending.EventType);
                     cmd.Parameters.AddWithValue("$severity", pending.Severity);
@@ -311,6 +299,7 @@ public class SiteEventLogger : ISiteEventLogger, IDisposable
 
     /// <summary>An event awaiting persistence by the background writer.</summary>
     private sealed record PendingEvent(
+        string Id,
         string Timestamp,
         string EventType,
         string Severity,

src/ZB.MOM.WW.ScadaBridge.SiteEventLogging/ZB.MOM.WW.ScadaBridge.SiteEventLogging.csproj

@@ -18,6 +18,7 @@
     <PackageReference Include="Microsoft.Extensions.Logging.Abstractions" />
     <PackageReference Include="Microsoft.Extensions.Options" />
     <PackageReference Include="ZB.MOM.WW.Configuration" />
+    <PackageReference Include="ZB.MOM.WW.LocalDb" />
   </ItemGroup>
 
   <ItemGroup>

src/ZB.MOM.WW.ScadaBridge.SiteRuntime/ServiceCollectionExtensions.cs

@@ -70,7 +70,11 @@ public static class ServiceCollectionExtensions
         // reconciliation, Tracking.Status audit-only). Central roots never call
         // AddSiteRuntime, so this stays absent on central — matching the SITE-ONLY
         // contract. OperationTrackingOptions is bound + validated by the Host's
-        // SiteServiceRegistration site-options block.
+        // SiteServiceRegistration site-options block — but it no longer supplies the
+        // store's database: the store takes ILocalDb (the consolidated site database
+        // at LocalDb:Path), so OperationTrackingOptions.ConnectionString is now only
+        // read by the site config DB. Resolving this store therefore forces ILocalDb
+        // construction, which is what runs SiteLocalDbSetup.OnReady.
         services.AddSingleton<Commons.Interfaces.IOperationTrackingStore, Tracking.OperationTrackingStore>();
 
         // Site-local repository implementations backed by SQLite

src/ZB.MOM.WW.ScadaBridge.SiteRuntime/Tracking/OperationTrackingOptions.cs

@@ -7,9 +7,12 @@ namespace ZB.MOM.WW.ScadaBridge.SiteRuntime.Tracking;
 public class OperationTrackingOptions
 {
     /// <summary>
-    /// Full ADO.NET connection string for the SQLite database (e.g.
+    /// MIGRATION-ONLY as of LocalDb Phase 1. <see cref="OperationTrackingStore"/> no longer
-    /// <c>"Data Source=site-tracking.db"</c>). Tests use the
+    /// reads this: the tracking table lives in the consolidated site database at
-    /// <c>Mode=Memory;Cache=Shared</c> form to keep the database in-memory.
+    /// <c>LocalDb:Path</c>. The only remaining consumer is
+    /// <c>SiteLocalDbLegacyMigrator</c>, which uses it to locate a pre-Phase-1
+    /// <c>site-tracking.db</c> and copy it in once. Safe to delete from a config once that
+    /// node's migration has run.
     /// </summary>
     public string ConnectionString { get; set; } = "Data Source=site-tracking.db";
 

src/ZB.MOM.WW.ScadaBridge.SiteRuntime/Tracking/OperationTrackingSchema.cs

@@ -0,0 +1,105 @@
+using Microsoft.Data.Sqlite;
+
+namespace ZB.MOM.WW.ScadaBridge.SiteRuntime.Tracking;
+
+/// <summary>
+/// DDL for the <c>OperationTracking</c> table, extracted from
+/// <see cref="OperationTrackingStore"/> so it can be applied by whoever owns the
+/// database file.
+/// <para>
+/// Under LocalDb Phase 1 that owner is the Host's <c>AddZbLocalDb</c> onReady callback:
+/// the table must exist before <c>ILocalDb.RegisterReplicated</c> installs its capture
+/// triggers, and rows written before registration are never captured or snapshotted.
+/// The store still calls this during its own initialization, so a store constructed
+/// directly (tests, tooling) remains self-sufficient.
+/// </para>
+/// <para>
+/// Deliberately depends only on <c>Microsoft.Data.Sqlite</c>, not on the LocalDb
+/// library — the Host needs to apply this DDL to a LocalDb-managed connection, but
+/// nothing about the schema itself is LocalDb-specific.
+/// </para>
+/// </summary>
+public static class OperationTrackingSchema
+{
+    /// <summary>
+    /// Creates the <c>OperationTracking</c> table and its indexes when absent, and
+    /// additively upgrades a table created by an older build. Idempotent — safe to run
+    /// on every startup.
+    /// </summary>
+    /// <param name="connection">An open connection to the database that should hold the table.</param>
+    public static void Apply(SqliteConnection connection)
+    {
+        ArgumentNullException.ThrowIfNull(connection);
+
+        using (var cmd = connection.CreateCommand())
+        {
+            // TrackedOperationId is a TEXT GUID, which is also what lets this table
+            // replicate as-is: RegisterReplicated requires an explicit primary key and
+            // rejects BLOB columns, and there are none here.
+            cmd.CommandText = """
+                CREATE TABLE IF NOT EXISTS OperationTracking (
+                    TrackedOperationId TEXT    NOT NULL PRIMARY KEY,
+                    Kind               TEXT    NOT NULL,
+                    TargetSummary      TEXT    NULL,
+                    Status             TEXT    NOT NULL,
+                    RetryCount         INTEGER NOT NULL DEFAULT 0,
+                    LastError          TEXT    NULL,
+                    HttpStatus         INTEGER NULL,
+                    CreatedAtUtc       TEXT    NOT NULL,
+                    UpdatedAtUtc       TEXT    NOT NULL,
+                    TerminalAtUtc      TEXT    NULL,
+                    SourceInstanceId   TEXT    NULL,
+                    SourceScript       TEXT    NULL,
+                    SourceNode         TEXT    NULL
+                );
+                CREATE INDEX IF NOT EXISTS IX_OperationTracking_Status_Updated
+                    ON OperationTracking (Status, UpdatedAtUtc);
+                CREATE INDEX IF NOT EXISTS IX_OperationTracking_UpdatedAt
+                    ON OperationTracking (UpdatedAtUtc);
+                """;
+            cmd.ExecuteNonQuery();
+        }
+
+        // SourceNode stamping: additively add the SourceNode column.
+        // CREATE TABLE IF NOT EXISTS above does NOT add columns to an
+        // OperationTracking table that already exists from a pre-SourceNode
+        // build, so a tracking.db created by an older build needs the column
+        // ALTER-ed in. The file is durable across restart/failover by design
+        // (retention window default 7 days), so without this step every
+        // RecordEnqueueAsync on an upgraded deployment would bind $sourceNode
+        // against a missing column and the write would fail.
+        // SQLite has no "ADD COLUMN IF NOT EXISTS"; the column presence is
+        // probed first and the ALTER skipped when already there. The column is
+        // nullable with no default, so any row written before this migration
+        // reads back SourceNode = null (back-compat).
+        AddColumnIfMissing(connection, "SourceNode", "TEXT NULL");
+    }
+
+    /// <summary>
+    /// Additively adds a column to <c>OperationTracking</c> only when it is not
+    /// already present. SQLite lacks <c>ADD COLUMN IF NOT EXISTS</c>, so the
+    /// schema is probed via <c>PRAGMA table_info</c> first. Mirrors the
+    /// <c>SqliteAuditWriter.AddColumnIfMissing</c> precedent.
+    /// </summary>
+    private static void AddColumnIfMissing(
+        SqliteConnection connection, string columnName, string columnDefinition)
+    {
+        using (var probe = connection.CreateCommand())
+        {
+            probe.CommandText =
+                "SELECT COUNT(*) FROM pragma_table_info('OperationTracking') WHERE name = $name";
+            probe.Parameters.AddWithValue("$name", columnName);
+            if (Convert.ToInt32(probe.ExecuteScalar()) > 0)
+            {
+                return;
+            }
+        }
+
+        using var alter = connection.CreateCommand();
+        // Column name + definition are caller-controlled constants, never user
+        // input — safe to interpolate (parameters are not permitted in DDL).
+        alter.CommandText =
+            $"ALTER TABLE OperationTracking ADD COLUMN {columnName} {columnDefinition}";
+        alter.ExecuteNonQuery();
+    }
+}

src/ZB.MOM.WW.ScadaBridge.SiteRuntime/Tracking/OperationTrackingStore.cs

@@ -1,7 +1,7 @@
 using System.Globalization;
 using Microsoft.Data.Sqlite;
 using Microsoft.Extensions.Logging;
-using Microsoft.Extensions.Options;
+using ZB.MOM.WW.LocalDb;
 using ZB.MOM.WW.ScadaBridge.Commons.Interfaces;
 using ZB.MOM.WW.ScadaBridge.Commons.Types;
 
@@ -39,7 +39,7 @@ public class OperationTrackingStore : IOperationTrackingStore, IAsyncDisposable,
     // _writeGate. Readers do NOT share this connection or gate; see GetStatusAsync.
     private readonly SqliteConnection _writeConnection;
     private readonly SemaphoreSlim _writeGate = new(1, 1);
-    private readonly string _connectionString;
+    private readonly ILocalDb _localDb;
     private readonly ILogger<OperationTrackingStore> _logger;
 
     // Dispose-once state shared by the sync Dispose and async
@@ -51,94 +51,44 @@ public class OperationTrackingStore : IOperationTrackingStore, IAsyncDisposable,
     private int _disposeState;
 
     /// <summary>
-    /// Initializes the tracking store, opens the SQLite connection, and applies the schema.
+    /// Initializes the tracking store over the consolidated site database and applies the schema.
     /// </summary>
-    /// <param name="options">Tracking store configuration (connection string, retention window).</param>
+    /// <param name="localDb">
+    /// The consolidated site database. Every connection it hands out is already open and carries
+    /// the per-connection pragmas plus the <c>zb_hlc_next()</c> UDF that
+    /// <c>OperationTracking</c>'s capture triggers call — which is exactly why the store no longer
+    /// opens its own <see cref="SqliteConnection"/> from a connection string. A raw connection
+    /// would lack the UDF and every write to the replicated table would fail closed.
+    /// </param>
     /// <param name="logger">Logger for diagnostics.</param>
+    /// <remarks>
+    /// <see cref="OperationTrackingOptions.ConnectionString"/> no longer feeds this store — the
+    /// database location is <c>LocalDb:Path</c>. The option remains bound for the purge/retention
+    /// settings that share the class.
+    /// </remarks>
     public OperationTrackingStore(
-        IOptions<OperationTrackingOptions> options,
+        ILocalDb localDb,
         ILogger<OperationTrackingStore> logger)
     {
-        ArgumentNullException.ThrowIfNull(options);
+        ArgumentNullException.ThrowIfNull(localDb);
         ArgumentNullException.ThrowIfNull(logger);
 
         _logger = logger;
-        _connectionString = options.Value.ConnectionString;
+        _localDb = localDb;
-        _writeConnection = new SqliteConnection(_connectionString);
+        // Already open — CreateConnection returns a live, pragma-configured,
-        _writeConnection.Open();
+        // UDF-registered connection. Calling Open() on it again would throw.
+        _writeConnection = localDb.CreateConnection();
         InitializeSchema();
     }
 
-    private void InitializeSchema()
+    // Schema lives in OperationTrackingSchema so the Host's AddZbLocalDb onReady
-    {
+    // callback can create this table in the consolidated site database before
-        using var cmd = _writeConnection.CreateCommand();
+    // RegisterReplicated installs its capture triggers. In the host this call is
-        cmd.CommandText = """
+    // therefore always a no-op — onReady runs while ILocalDb is being constructed,
-            CREATE TABLE IF NOT EXISTS OperationTracking (
+    // which is strictly before this constructor can receive it. It stays because it
-                TrackedOperationId TEXT    NOT NULL PRIMARY KEY,
+    // keeps a directly-constructed store (tests, tooling) self-sufficient, and the
-                Kind               TEXT    NOT NULL,
+    // DDL is idempotent.
-                TargetSummary      TEXT    NULL,
+    private void InitializeSchema() => OperationTrackingSchema.Apply(_writeConnection);
-                Status             TEXT    NOT NULL,
-                RetryCount         INTEGER NOT NULL DEFAULT 0,
-                LastError          TEXT    NULL,
-                HttpStatus         INTEGER NULL,
-                CreatedAtUtc       TEXT    NOT NULL,
-                UpdatedAtUtc       TEXT    NOT NULL,
-                TerminalAtUtc      TEXT    NULL,
-                SourceInstanceId   TEXT    NULL,
-                SourceScript       TEXT    NULL,
-                SourceNode         TEXT    NULL
-            );
-            CREATE INDEX IF NOT EXISTS IX_OperationTracking_Status_Updated
-                ON OperationTracking (Status, UpdatedAtUtc);
-            CREATE INDEX IF NOT EXISTS IX_OperationTracking_UpdatedAt
-                ON OperationTracking (UpdatedAtUtc);
-            """;
-        cmd.ExecuteNonQuery();
-
-        // SourceNode stamping: additively add the SourceNode column.
-        // CREATE TABLE IF NOT EXISTS above does NOT add columns to an
-        // OperationTracking table that already exists from a pre-SourceNode
-        // build, so a tracking.db created by an older build needs the column
-        // ALTER-ed in. The file is durable across restart/failover by design
-        // (retention window default 7 days), so without this step every
-        // RecordEnqueueAsync on an upgraded deployment would bind $sourceNode
-        // against a missing column and the write would fail.
-        // SQLite has no "ADD COLUMN IF NOT EXISTS"; the column presence is
-        // probed first and the ALTER skipped when already there. The column is
-        // nullable with no default, so any row written before this migration
-        // reads back SourceNode = null (back-compat).
-        //
-        // NOTE: This is the FIRST idempotent column-upgrade in
-        // OperationTrackingStore — prior schema changes pre-dated any
-        // production rollout and relied solely on CREATE TABLE IF NOT EXISTS.
-        // The helper mirrors the SqliteAuditWriter precedent.
-        AddColumnIfMissing("SourceNode", "TEXT NULL");
-    }
-
-    /// <summary>
-    /// Additively adds a column to <c>OperationTracking</c> only when it is not
-    /// already present. SQLite lacks <c>ADD COLUMN IF NOT EXISTS</c>, so the
-    /// schema is probed via <c>PRAGMA table_info</c> first. Idempotent — safe
-    /// to run on every <see cref="InitializeSchema"/>. Mirrors the
-    /// <c>SqliteAuditWriter.AddColumnIfMissing</c> precedent.
-    /// </summary>
-    private void AddColumnIfMissing(string columnName, string columnDefinition)
-    {
-        using var probe = _writeConnection.CreateCommand();
-        probe.CommandText = "SELECT COUNT(*) FROM pragma_table_info('OperationTracking') WHERE name = $name";
-        probe.Parameters.AddWithValue("$name", columnName);
-        var exists = Convert.ToInt32(probe.ExecuteScalar()) > 0;
-        if (exists)
-        {
-            return;
-        }
-
-        using var alter = _writeConnection.CreateCommand();
-        // Column name + definition are caller-controlled constants, never user
-        // input — safe to interpolate (parameters are not permitted in DDL).
-        alter.CommandText = $"ALTER TABLE OperationTracking ADD COLUMN {columnName} {columnDefinition}";
-        alter.ExecuteNonQuery();
-    }
 
     /// <inheritdoc/>
     public async Task RecordEnqueueAsync(
@@ -289,14 +239,12 @@ public class OperationTrackingStore : IOperationTrackingStore, IAsyncDisposable,
     {
         ObjectDisposedException.ThrowIf(Volatile.Read(ref _disposeState) != 0, this);
 
-        // Reads open a fresh, ungated SqliteConnection so a
+        // Reads open a fresh, ungated connection so a long-running write doesn't
-        // long-running write doesn't block status queries. The connection
+        // block status queries. It comes from the same ILocalDb as the writer;
-        // string is shared with the writer; SQLite handles cross-connection
+        // SQLite handles cross-connection isolation natively (readers see a WAL
-        // isolation natively (a reader sees a consistent snapshot via the
+        // snapshot). Mirrors the SiteStorageService precedent. Already open — do
-        // shared cache lock for in-memory DBs, or a WAL snapshot for file DBs).
+        // not call OpenAsync on it.
-        // Mirrors the SiteStorageService precedent.
+        await using var readConnection = _localDb.CreateConnection();
-        await using var readConnection = new SqliteConnection(_connectionString);
-        await readConnection.OpenAsync(ct).ConfigureAwait(false);
 
         await using var cmd = readConnection.CreateCommand();
         cmd.CommandText = """
@@ -376,8 +324,8 @@ public class OperationTrackingStore : IOperationTrackingStore, IAsyncDisposable,
         // the standalone IX_OperationTracking_UpdatedAt index — UpdatedAtUtc is
         // the cursor. (The composite (Status, UpdatedAtUtc) index cannot satisfy a
         // status-less UpdatedAtUtc range scan; this dedicated index does.)
-        await using var readConnection = new SqliteConnection(_connectionString);
+        // Already open — do not call OpenAsync on it.
-        await readConnection.OpenAsync(ct).ConfigureAwait(false);
+        await using var readConnection = _localDb.CreateConnection();
 
         await using var cmd = readConnection.CreateCommand();
 

src/ZB.MOM.WW.ScadaBridge.SiteRuntime/ZB.MOM.WW.ScadaBridge.SiteRuntime.csproj

@@ -23,6 +23,7 @@
     <PackageReference Include="Microsoft.Extensions.Logging.Abstractions" />
     <PackageReference Include="Microsoft.Extensions.Options" />
     <PackageReference Include="ZB.MOM.WW.Configuration" />
+    <PackageReference Include="ZB.MOM.WW.LocalDb" />
   </ItemGroup>
 
   <ItemGroup>

tests/ZB.MOM.WW.ScadaBridge.AuditLog.Tests/Integration/Infrastructure/CombinedTelemetryHarness.cs

@@ -1,8 +1,10 @@
 using Akka.Actor;
 using Microsoft.EntityFrameworkCore;
+using Microsoft.Extensions.Configuration;
 using Microsoft.Extensions.DependencyInjection;
 using Microsoft.Extensions.Logging.Abstractions;
 using Microsoft.Extensions.Options;
+using ZB.MOM.WW.LocalDb;
 using ZB.MOM.WW.ScadaBridge.AuditLog.Central;
 using ZB.MOM.WW.ScadaBridge.AuditLog.Site;
 using ZB.MOM.WW.ScadaBridge.AuditLog.Site.Telemetry;
@@ -58,6 +60,8 @@ public sealed class CombinedTelemetryHarness : IAsyncDisposable
     public IServiceProvider ServiceProvider { get; }
 
     private readonly MsSqlMigrationFixture _fixture;
+    private readonly string _trackingDbPath;
+    private readonly ServiceProvider _trackingLocalDbProvider;
     private bool _disposed;
 
     public CombinedTelemetryHarness(
@@ -88,13 +92,23 @@ public sealed class CombinedTelemetryHarness : IAsyncDisposable
             SqliteWriter, Ring, new NoOpAuditWriteFailureCounter(),
             NullLogger<FallbackAuditWriter>.Instance);
 
-        TrackingStore = new OperationTrackingStore(
+        // The tracking store now runs on the consolidated ILocalDb rather than its own
-            Options.Create(new OperationTrackingOptions
+        // connection string, so it needs a real database FILE — LocalDbOptions.Path is
+        // a filesystem path and there is no in-memory mode. Unique per harness, torn
+        // down in DisposeAsync alongside its WAL sidecars.
+        _trackingDbPath = Path.Combine(
+            Path.GetTempPath(), $"tracking-g-{Guid.NewGuid():N}.db");
+        _trackingLocalDbProvider = new ServiceCollection()
+            .AddZbLocalDb(new ConfigurationBuilder()
+                .AddInMemoryCollection(new Dictionary<string, string?>
                 {
-                // Same shared-in-memory pattern as the audit writer.
+                    ["LocalDb:Path"] = _trackingDbPath,
-                ConnectionString =
+                })
-                    $"Data Source=file:tracking-g-{Guid.NewGuid():N}?mode=memory&cache=shared",
+                .Build())
-            }),
+            .BuildServiceProvider();
+
+        TrackingStore = new OperationTrackingStore(
+            _trackingLocalDbProvider.GetRequiredService<ILocalDb>(),
             NullLogger<OperationTrackingStore>.Instance);
 
         // Central wiring: real repositories backed by the MSSQL fixture's DB.
@@ -165,6 +179,15 @@ public sealed class CombinedTelemetryHarness : IAsyncDisposable
         _disposed = true;
         await SqliteWriter.DisposeAsync().ConfigureAwait(false);
         await TrackingStore.DisposeAsync().ConfigureAwait(false);
+
+        // Dispose the ILocalDb before deleting the file — it holds an open master
+        // connection anchoring the WAL.
+        await _trackingLocalDbProvider.DisposeAsync().ConfigureAwait(false);
+        foreach (var suffix in new[] { "", "-wal", "-shm" })
+        {
+            try { File.Delete(_trackingDbPath + suffix); } catch { /* best effort */ }
+        }
+
         if (ServiceProvider is IAsyncDisposable asyncSp)
         {
             await asyncSp.DisposeAsync().ConfigureAwait(false);

tests/ZB.MOM.WW.ScadaBridge.CentralUI.Tests/ZB.MOM.WW.ScadaBridge.CentralUI.Tests.csproj

@@ -40,4 +40,28 @@
     <ProjectReference Include="../../src/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.csproj" />
   </ItemGroup>
 
+  <!--
+    GHSA-pgww-w46g-26qg (NU1902, moderate) on AngleSharp, reached ONLY transitively via
+    bunit. Suppressed here, scoped to this one test project, because there is verifiably
+    no version combination that resolves it:
+
+      - AngleSharp < 1.5.0 is vulnerable (1.4.0 is what even the newest bunit resolves).
+      - AngleSharp >= 1.5.0 changed IHtmlCollection<T>'s indexer, so every bunit build
+        throws MissingMethodException at runtime - 33 CentralUI render tests fail.
+      - Bumping bunit does not help: checked up to 2.7.2, still AngleSharp 1.4.0.
+
+    So the choice is a suppressed advisory or an unbuildable/failing test suite. Scoped
+    suppression wins because the reach is genuinely nil: AngleSharp is an HTML parser used
+    by bunit to assert on rendered markup in unit tests. It ships in no production project
+    and processes no untrusted input - the "documents" it parses are our own components'
+    output. Revisit when bunit ships against AngleSharp 1.5+.
+
+    NOT the same call as GHSA-2m69-gcr7-jv3q (SQLitePCLRaw): that suppression was removed
+    in 2026-07 precisely because it was masking a vulnerability in PRODUCTION code paths.
+    This one is test-only, and a patched-but-working version does not exist upstream.
+  -->
+  <ItemGroup>
+    <NuGetAuditSuppress Include="https://github.com/advisories/GHSA-pgww-w46g-26qg" />
+  </ItemGroup>
+
 </Project>

tests/ZB.MOM.WW.ScadaBridge.Host.Tests/ActorPathTests.cs

@@ -163,10 +163,12 @@ public class SiteActorPathTests : IAsyncLifetime
     private IHost? _host;
     private ActorSystem? _actorSystem;
     private string _tempDbPath = null!;
+    private string _tempLocalDbPath = null!;
 
     public async Task InitializeAsync()
     {
         _tempDbPath = Path.Combine(Path.GetTempPath(), $"scadabridge_actor_test_{Guid.NewGuid()}.db");
+        _tempLocalDbPath = Path.Combine(Path.GetTempPath(), $"scadabridge_actor_localdb_{Guid.NewGuid()}.db");
 
         var builder = Microsoft.Extensions.Hosting.Host.CreateDefaultBuilder();
         builder.ConfigureAppConfiguration(config =>
@@ -183,6 +185,10 @@ public class SiteActorPathTests : IAsyncLifetime
                 ["ScadaBridge:Cluster:SeedNodes:1"] = "akka.tcp://scadabridge@localhost:25520",
                 ["ScadaBridge:Cluster:MinNrOfMembers"] = "1",
                 ["ScadaBridge:Database:SiteDbPath"] = _tempDbPath,
+                // Required: LocalDbOptions.Path is validated with ValidateOnStart, and this
+                // fixture actually starts the host — a site node with no configured
+                // consolidated-database path fails fast rather than booting half-working.
+                ["LocalDb:Path"] = _tempLocalDbPath,
                 // Configure a dummy central contact point to trigger ClusterClient creation
                 ["ScadaBridge:Communication:CentralContactPoints:0"] = "akka.tcp://scadabridge@localhost:25510",
             });
@@ -207,6 +213,7 @@ public class SiteActorPathTests : IAsyncLifetime
             _host.Dispose();
         }
         try { File.Delete(_tempDbPath); } catch { /* best effort */ }
+        try { File.Delete(_tempLocalDbPath); } catch { /* best effort */ }
     }
 
     [Fact]

tests/ZB.MOM.WW.ScadaBridge.Host.Tests/AkkaHostedServiceAuditWiringTests.cs

@@ -290,10 +290,12 @@ public class SiteAuditWiringTests : IDisposable
 {
     private readonly WebApplication _host;
     private readonly string _tempDbPath;
+    private readonly string _tempLocalDbPath;
 
     public SiteAuditWiringTests()
     {
         _tempDbPath = Path.Combine(Path.GetTempPath(), $"scadabridge_audit_wiring_{Guid.NewGuid()}.db");
+        _tempLocalDbPath = Path.Combine(Path.GetTempPath(), $"scadabridge_audit_wiring_localdb_{Guid.NewGuid()}.db");
 
         var builder = WebApplication.CreateBuilder();
         builder.Configuration.Sources.Clear();
@@ -312,6 +314,11 @@ public class SiteAuditWiringTests : IDisposable
             // resolved; point it at an in-memory connection so the test doesn't
             // pollute the working directory.
             ["AuditLog:SiteWriter:DatabasePath"] = ":memory:",
+            // The cached-call telemetry forwarder pulls in IOperationTrackingStore,
+            // which now resolves ILocalDb — so the consolidated site database must be
+            // configured or the whole graph fails to resolve. There is no in-memory
+            // mode: LocalDbOptions.Path is a filesystem path.
+            ["LocalDb:Path"] = _tempLocalDbPath,
         });
 
         builder.Services.AddGrpc();
@@ -326,6 +333,7 @@ public class SiteAuditWiringTests : IDisposable
     {
         (_host as IDisposable)?.Dispose();
         try { File.Delete(_tempDbPath); } catch { /* best effort */ }
+        try { File.Delete(_tempLocalDbPath); } catch { /* best effort */ }
     }
 
     [Fact]

tests/ZB.MOM.WW.ScadaBridge.Host.Tests/CompositionRootTests.cs

@@ -18,6 +18,7 @@ using ZB.MOM.WW.ScadaBridge.HealthMonitoring;
 using ZB.MOM.WW.ScadaBridge.Host;
 using ZB.MOM.WW.ScadaBridge.Host.Actors;
 using ZB.MOM.WW.ScadaBridge.Host.Health;
+using ZB.MOM.WW.ScadaBridge.Host.Services;
 using ZB.MOM.WW.ScadaBridge.InboundAPI;
 using ZB.MOM.WW.ScadaBridge.ManagementService;
 using ZB.MOM.WW.ScadaBridge.NotificationService;
@@ -333,6 +334,43 @@ public class CentralCompositionRootTests : IDisposable
         Assert.NotNull(gate);
         Assert.IsType<ActiveNodeGate>(gate);
     }
+
+    // --- ScadaBridge#22 regression ---
+
+    /// <summary>
+    /// ScadaBridge#22 regression: the Secrets.Ui components mounted at /admin/secrets inject
+    /// <see cref="ZB.MOM.WW.Audit.IAuditWriter"/> — the SHARED-lib seam, not the repo's own
+    /// <see cref="IAuditWriter"/> (deliberately distinct per its doc comment). The secrets
+    /// adoption mounted the UI without bridging that seam, so component activation threw and
+    /// the page 500'd on every render; the login wall hid it. This resolves the full
+    /// injection set of every Secrets.Ui component (SecretsList / SecretEditor /
+    /// RevealButton / ConfirmDeleteModal) out of the REAL central composition root, because
+    /// the gap is invisible until the graph is actually built — the same defect class as the
+    /// four caught in the secrets library itself.
+    /// </summary>
+    [Theory]
+    [InlineData(typeof(ZB.MOM.WW.Secrets.Abstractions.ISecretStore))]
+    [InlineData(typeof(ZB.MOM.WW.Secrets.Abstractions.ISecretCipher))]
+    [InlineData(typeof(ZB.MOM.WW.Audit.IAuditWriter))]
+    public void Central_Resolves_SecretsUiComponentDependencies(Type serviceType)
+    {
+        var service = _factory.Services.GetService(serviceType);
+        Assert.NotNull(service);
+    }
+
+    /// <summary>
+    /// The bridge must route secrets-UI audit events into the CENTRAL direct-write path
+    /// (<see cref="ICentralAuditWriter"/> → dbo.AuditLog), not the site SQLite chain — the
+    /// site writer chain is registered on central but by design never resolved there, and
+    /// events written to it would dead-end in a local file no forwarder ever drains.
+    /// </summary>
+    [Fact]
+    public void Central_SharedAuditSeam_IsTheCentralDirectWriteBridge()
+    {
+        var writer = _factory.Services.GetService<ZB.MOM.WW.Audit.IAuditWriter>();
+        Assert.NotNull(writer);
+        Assert.IsType<CentralSharedSeamAuditWriter>(writer);
+    }
 }
 
 /// <summary>
@@ -345,11 +383,13 @@ public class SiteCompositionRootTests : IDisposable
     private readonly WebApplication _host;
     private readonly string _tempDbPath;
     private readonly string _tempTrackingDbPath;
+    private readonly string _tempLocalDbPath;
 
     public SiteCompositionRootTests()
     {
         _tempDbPath = Path.Combine(Path.GetTempPath(), $"scadabridge_test_{Guid.NewGuid()}.db");
         _tempTrackingDbPath = Path.Combine(Path.GetTempPath(), $"scadabridge_track_{Guid.NewGuid()}.db");
+        _tempLocalDbPath = Path.Combine(Path.GetTempPath(), $"scadabridge_localdb_{Guid.NewGuid()}.db");
 
         var builder = WebApplication.CreateBuilder();
         builder.Configuration.Sources.Clear();
@@ -362,10 +402,13 @@ public class SiteCompositionRootTests : IDisposable
             ["ScadaBridge:Node:RemotingPort"] = "0",
             ["ScadaBridge:Node:GrpcPort"] = "0",
             ["ScadaBridge:Database:SiteDbPath"] = _tempDbPath,
-            // Point the site-local operation-tracking SQLite store (the ctor opens/creates
+            // Retained for the site config DB; it no longer points the tracking store
-            // the file eagerly) at a temp path so resolving IOperationTrackingStore in the
+            // anywhere — that store now opens the consolidated database below.
-            // composition-root test does not litter site-tracking.db in the test cwd.
             ["ScadaBridge:OperationTracking:ConnectionString"] = $"Data Source={_tempTrackingDbPath}",
+            // The consolidated site database. IOperationTrackingStore resolves ILocalDb
+            // and creates the file eagerly in its ctor, so this must be a temp path or
+            // the composition-root test litters the working directory.
+            ["LocalDb:Path"] = _tempLocalDbPath,
             // ClusterOptions requires at least one seed node (ClusterOptionsValidator).
             ["ScadaBridge:Cluster:SeedNodes:0"] = "akka.tcp://scadabridge@localhost:2551",
             ["ScadaBridge:Cluster:SeedNodes:1"] = "akka.tcp://scadabridge@localhost:2552",
@@ -389,6 +432,7 @@ public class SiteCompositionRootTests : IDisposable
         (_host as IDisposable)?.Dispose();
         try { File.Delete(_tempDbPath); } catch { /* best effort */ }
         try { File.Delete(_tempTrackingDbPath); } catch { /* best effort */ }
+        try { File.Delete(_tempLocalDbPath); } catch { /* best effort */ }
     }
 
     [Fact]

tests/ZB.MOM.WW.ScadaBridge.Host.Tests/LocalDbSyncAuthInterceptorTests.cs

@@ -0,0 +1,171 @@
+using Grpc.Core;
+using Microsoft.Extensions.Logging.Abstractions;
+using Microsoft.Extensions.Options;
+using ZB.MOM.WW.LocalDb.Replication;
+using ZB.MOM.WW.ScadaBridge.Host;
+
+namespace ZB.MOM.WW.ScadaBridge.Host.Tests;
+
+/// <summary>
+/// LocalDb Phase 1 (Task 8) — the passive sync endpoint's inbound gate.
+/// </summary>
+/// <remarks>
+/// The replication library's <c>LocalDbSyncService</c> verifies nothing; inbound auth is
+/// explicitly the host's job. Without this interceptor, anything able to reach a site
+/// node's gRPC port could stream arbitrary rows straight into the consolidated site
+/// database — including into <c>OperationTracking</c>, which central reconciles from.
+/// </remarks>
+public class LocalDbSyncAuthInterceptorTests
+{
+    private const string SyncMethod = "/localdb_sync.v1.LocalDbSync/Sync";
+    private const string SiteStreamMethod = "/scadabridge.SiteStream/Connect";
+
+    private static LocalDbSyncAuthInterceptor CreateInterceptor(string? apiKey)
+        => new(
+            Options.Create(new ReplicationOptions { ApiKey = apiKey }),
+            NullLogger<LocalDbSyncAuthInterceptor>.Instance);
+
+    private static ServerCallContext CreateContext(string method, string? authorizationHeader)
+    {
+        var headers = new Metadata();
+        if (authorizationHeader is not null)
+            headers.Add("authorization", authorizationHeader);
+
+        return new FakeServerCallContext(method, headers);
+    }
+
+    /// <summary>
+    /// Minimal <see cref="ServerCallContext"/> carrying just a method name and request
+    /// headers — the only two things the interceptor reads.
+    /// </summary>
+    /// <remarks>
+    /// Hand-rolled rather than using <c>Grpc.Core.Testing.TestServerCallContext</c>: that
+    /// type ships in the retired native <c>Grpc.Core</c> package and does not exist on the
+    /// grpc-dotnet stack this solution runs on. Pulling in the dead package to get one
+    /// test helper would be a worse trade than these few overrides.
+    /// </remarks>
+    private sealed class FakeServerCallContext(string method, Metadata requestHeaders)
+        : ServerCallContext
+    {
+        protected override string MethodCore => method;
+        protected override string HostCore => "localhost";
+        protected override string PeerCore => "ipv4:127.0.0.1:12345";
+        protected override DateTime DeadlineCore => DateTime.UtcNow.AddMinutes(1);
+        protected override Metadata RequestHeadersCore => requestHeaders;
+        protected override CancellationToken CancellationTokenCore => CancellationToken.None;
+        protected override Metadata ResponseTrailersCore { get; } = [];
+        protected override Status StatusCore { get; set; }
+        protected override WriteOptions? WriteOptionsCore { get; set; }
+        protected override AuthContext AuthContextCore { get; } =
+            new(null, new Dictionary<string, List<AuthProperty>>());
+
+        protected override ContextPropagationToken CreatePropagationTokenCore(
+            ContextPropagationOptions? options)
+            => throw new NotSupportedException();
+
+        protected override Task WriteResponseHeadersAsyncCore(Metadata responseHeaders)
+            => Task.CompletedTask;
+    }
+
+    /// <summary>Invokes the interceptor's unary path with a trivial continuation.</summary>
+    private static Task<string> Invoke(
+        LocalDbSyncAuthInterceptor interceptor, ServerCallContext context)
+        => interceptor.UnaryServerHandler<string, string>(
+            "request", context, (_, _) => Task.FromResult("ok"));
+
+    [Fact]
+    public async Task NonSyncMethod_PassesThrough_EvenWithNoKeyConfigured()
+    {
+        // The interceptor is registered on the shared site AddGrpc pipeline, so it sees
+        // every call. It must be scoped strictly to the sync service — gating SiteStream
+        // would break site↔central communication outright.
+        var interceptor = CreateInterceptor(apiKey: null);
+        var context = CreateContext(SiteStreamMethod, authorizationHeader: null);
+
+        Assert.Equal("ok", await Invoke(interceptor, context));
+    }
+
+    [Fact]
+    public async Task SyncMethod_WithNoKeyConfigured_IsDenied_EvenWithABearerToken()
+    {
+        // Fail-closed. "No key configured" is the DEFAULT every site node ships with, so
+        // treating it as "no auth required" would silently expose the endpoint on exactly
+        // the configuration that is most common. Presenting a token must not help.
+        var interceptor = CreateInterceptor(apiKey: null);
+        var context = CreateContext(SyncMethod, "Bearer anything-at-all");
+
+        var ex = await Assert.ThrowsAsync<RpcException>(() => Invoke(interceptor, context));
+        Assert.Equal(StatusCode.PermissionDenied, ex.StatusCode);
+    }
+
+    [Fact]
+    public async Task SyncMethod_WithNoBearerToken_IsDenied()
+    {
+        var interceptor = CreateInterceptor("the-shared-key");
+        var context = CreateContext(SyncMethod, authorizationHeader: null);
+
+        var ex = await Assert.ThrowsAsync<RpcException>(() => Invoke(interceptor, context));
+        Assert.Equal(StatusCode.PermissionDenied, ex.StatusCode);
+    }
+
+    [Fact]
+    public async Task SyncMethod_WithWrongBearerToken_IsDenied()
+    {
+        var interceptor = CreateInterceptor("the-shared-key");
+        var context = CreateContext(SyncMethod, "Bearer the-wrong-key");
+
+        var ex = await Assert.ThrowsAsync<RpcException>(() => Invoke(interceptor, context));
+        Assert.Equal(StatusCode.PermissionDenied, ex.StatusCode);
+    }
+
+    [Fact]
+    public async Task SyncMethod_WithCorrectBearerToken_PassesThrough()
+    {
+        var interceptor = CreateInterceptor("the-shared-key");
+        var context = CreateContext(SyncMethod, "Bearer the-shared-key");
+
+        Assert.Equal("ok", await Invoke(interceptor, context));
+    }
+
+    [Fact]
+    public async Task SyncMethod_WithCorrectKey_ButNoBearerScheme_IsDenied()
+    {
+        // A raw key with no "Bearer " prefix is not what SyncBackgroundService sends, and
+        // accepting it would widen the accepted credential shape for no reason.
+        var interceptor = CreateInterceptor("the-shared-key");
+        var context = CreateContext(SyncMethod, "the-shared-key");
+
+        var ex = await Assert.ThrowsAsync<RpcException>(() => Invoke(interceptor, context));
+        Assert.Equal(StatusCode.PermissionDenied, ex.StatusCode);
+    }
+
+    [Fact]
+    public async Task SyncMethod_TokenComparison_IsNotAPrefixMatch()
+    {
+        // A prefix/StartsWith comparison would accept a truncated key and make the secret
+        // recoverable one character at a time.
+        var interceptor = CreateInterceptor("the-shared-key");
+        var context = CreateContext(SyncMethod, "Bearer the-shared-ke");
+
+        var ex = await Assert.ThrowsAsync<RpcException>(() => Invoke(interceptor, context));
+        Assert.Equal(StatusCode.PermissionDenied, ex.StatusCode);
+    }
+
+    [Fact]
+    public async Task DuplexStreaming_IsGated_BecauseThatIsHowSyncActuallyRuns()
+    {
+        // The sync RPC is a bidirectional stream. Gating only the unary path would leave
+        // the real endpoint wide open while every unary test still passed.
+        var interceptor = CreateInterceptor("the-shared-key");
+        var context = CreateContext(SyncMethod, "Bearer the-wrong-key");
+
+        var ex = await Assert.ThrowsAsync<RpcException>(() =>
+            interceptor.DuplexStreamingServerHandler<string, string>(
+                requestStream: null!,
+                responseStream: null!,
+                context,
+                (_, _, _) => Task.CompletedTask));
+
+        Assert.Equal(StatusCode.PermissionDenied, ex.StatusCode);
+    }
+}

tests/ZB.MOM.WW.ScadaBridge.Host.Tests/SiteLocalDbLegacyMigratorTests.cs

@@ -0,0 +1,327 @@
+using Microsoft.Data.Sqlite;
+using Microsoft.Extensions.Configuration;
+using Microsoft.Extensions.DependencyInjection;
+using ZB.MOM.WW.LocalDb;
+using ZB.MOM.WW.ScadaBridge.Host;
+
+namespace ZB.MOM.WW.ScadaBridge.Host.Tests;
+
+/// <summary>
+/// LocalDb Phase 1 (Task 6) — the one-time copy of the pre-Phase-1 site databases into
+/// the consolidated one.
+/// </summary>
+/// <remarks>
+/// This is a data migration that runs during host startup, so its failure modes are
+/// expensive: a duplicate-on-rerun bug silently doubles a site's event history, and a
+/// half-committed copy leaves an operator with no clean state to recover to. Every bullet
+/// of the plan's semantics gets its own test.
+/// </remarks>
+public class SiteLocalDbLegacyMigratorTests : IDisposable
+{
+    private readonly string _root;
+    private readonly List<ServiceProvider> _providers = [];
+
+    public SiteLocalDbLegacyMigratorTests()
+    {
+        _root = Path.Combine(Path.GetTempPath(), $"localdb-migrator-{Guid.NewGuid():N}");
+        Directory.CreateDirectory(_root);
+    }
+
+    public void Dispose()
+    {
+        foreach (var provider in _providers)
+        {
+            try { provider.Dispose(); } catch { /* best effort */ }
+        }
+
+        try { Directory.Delete(_root, recursive: true); } catch { /* best effort */ }
+        GC.SuppressFinalize(this);
+    }
+
+    private string Path_(string name) => System.IO.Path.Combine(_root, name);
+
+    /// <summary>A consolidated database with both tables created and registered, as the host has it.</summary>
+    private ILocalDb CreateConsolidated(IConfiguration config)
+    {
+        var provider = new ServiceCollection()
+            .AddZbLocalDb(config, db =>
+            {
+                using var connection = db.CreateConnection();
+                ZB.MOM.WW.ScadaBridge.SiteRuntime.Tracking.OperationTrackingSchema.Apply(connection);
+                ZB.MOM.WW.ScadaBridge.SiteEventLogging.SiteEventLogSchema.Apply(connection);
+                db.RegisterReplicated("OperationTracking");
+                db.RegisterReplicated("site_events");
+            })
+            .BuildServiceProvider();
+        _providers.Add(provider);
+
+        return provider.GetRequiredService<ILocalDb>();
+    }
+
+    private IConfiguration Config(
+        string? trackingPath = null, string? eventsPath = null, string nodeName = "node-a")
+    {
+        var values = new Dictionary<string, string?>
+        {
+            ["LocalDb:Path"] = Path_("consolidated.db"),
+            ["ScadaBridge:Node:NodeName"] = nodeName,
+        };
+
+        if (trackingPath is not null)
+            values["ScadaBridge:OperationTracking:ConnectionString"] = $"Data Source={trackingPath}";
+        if (eventsPath is not null)
+            values["ScadaBridge:SiteEventLog:DatabasePath"] = eventsPath;
+
+        return new ConfigurationBuilder().AddInMemoryCollection(values).Build();
+    }
+
+    private static void SeedLegacyTracking(string path, params string[] ids)
+    {
+        using var connection = new SqliteConnection($"Data Source={path}");
+        connection.Open();
+        ZB.MOM.WW.ScadaBridge.SiteRuntime.Tracking.OperationTrackingSchema.Apply(connection);
+
+        foreach (var id in ids)
+        {
+            using var cmd = connection.CreateCommand();
+            cmd.CommandText = """
+                INSERT INTO OperationTracking (
+                    TrackedOperationId, Kind, TargetSummary, Status, RetryCount,
+                    CreatedAtUtc, UpdatedAtUtc)
+                VALUES ($id, 'ApiCallCached', 'ERP.GetOrder', 'Submitted', 0, $now, $now);
+                """;
+            cmd.Parameters.AddWithValue("$id", id);
+            cmd.Parameters.AddWithValue("$now", DateTime.UtcNow.ToString("o"));
+            cmd.ExecuteNonQuery();
+        }
+    }
+
+    /// <summary>Seeds the LEGACY event schema: an autoincrement integer id, not a GUID.</summary>
+    private static void SeedLegacyEvents(string path, int count)
+    {
+        using var connection = new SqliteConnection($"Data Source={path}");
+        connection.Open();
+
+        using (var ddl = connection.CreateCommand())
+        {
+            ddl.CommandText = """
+                CREATE TABLE IF NOT EXISTS site_events (
+                    id INTEGER PRIMARY KEY AUTOINCREMENT,
+                    timestamp TEXT NOT NULL, event_type TEXT NOT NULL, severity TEXT NOT NULL,
+                    instance_id TEXT, source TEXT NOT NULL, message TEXT NOT NULL, details TEXT
+                );
+                """;
+            ddl.ExecuteNonQuery();
+        }
+
+        for (var i = 0; i < count; i++)
+        {
+            using var cmd = connection.CreateCommand();
+            cmd.CommandText = """
+                INSERT INTO site_events (timestamp, event_type, severity, source, message)
+                VALUES ($ts, 'script', 'Info', 'src', $msg);
+                """;
+            cmd.Parameters.AddWithValue("$ts", DateTimeOffset.UtcNow.AddMinutes(-i).ToString("o"));
+            cmd.Parameters.AddWithValue("$msg", $"legacy message {i}");
+            cmd.ExecuteNonQuery();
+        }
+    }
+
+    private static long CountRows(ILocalDb db, string table)
+    {
+        using var connection = db.CreateConnection();
+        using var cmd = connection.CreateCommand();
+        cmd.CommandText = $"SELECT COUNT(*) FROM {table}";
+        return (long)cmd.ExecuteScalar()!;
+    }
+
+    private static List<string> SelectIds(ILocalDb db, string table, string idColumn)
+    {
+        using var connection = db.CreateConnection();
+        using var cmd = connection.CreateCommand();
+        cmd.CommandText = $"SELECT {idColumn} FROM {table} ORDER BY {idColumn}";
+        using var reader = cmd.ExecuteReader();
+
+        var ids = new List<string>();
+        while (reader.Read()) ids.Add(reader.GetString(0));
+        return ids;
+    }
+
+    [Fact]
+    public void MissingLegacyFiles_AreANoOp()
+    {
+        // The expected case on the docker rig: neither legacy key is set anywhere, and
+        // the CWD-relative defaults point outside the data volume, so there is usually
+        // nothing there at all. That must be a clean no-op, not a startup failure.
+        var config = Config(Path_("absent-tracking.db"), Path_("absent-events.db"));
+        var db = CreateConsolidated(config);
+
+        SiteLocalDbLegacyMigrator.Migrate(db, config);
+
+        Assert.Equal(0, CountRows(db, "OperationTracking"));
+        Assert.Equal(0, CountRows(db, "site_events"));
+    }
+
+    [Fact]
+    public void LegacyTrackingRows_AreCopiedAndTheFileIsRenamed()
+    {
+        var trackingPath = Path_("site-tracking.db");
+        SeedLegacyTracking(trackingPath, "op-1", "op-2", "op-3");
+        var config = Config(trackingPath, Path_("absent-events.db"));
+        var db = CreateConsolidated(config);
+
+        SiteLocalDbLegacyMigrator.Migrate(db, config);
+
+        Assert.Equal(3, CountRows(db, "OperationTracking"));
+        Assert.Equal(["op-1", "op-2", "op-3"], SelectIds(db, "OperationTracking", "TrackedOperationId"));
+
+        // Renamed, not deleted — the operator can still inspect the original.
+        Assert.False(File.Exists(trackingPath));
+        Assert.True(File.Exists(trackingPath + ".migrated"));
+    }
+
+    [Fact]
+    public void LegacyEvents_GetDeterministicIds_NotFreshGuids()
+    {
+        // The whole reason a rerun cannot duplicate. Fresh GUIDs would make
+        // INSERT OR IGNORE useless and double the history on every retry.
+        var eventsPath = Path_("site_events.db");
+        SeedLegacyEvents(eventsPath, count: 3);
+        var config = Config(Path_("absent-tracking.db"), eventsPath, nodeName: "node-b");
+        var db = CreateConsolidated(config);
+
+        SiteLocalDbLegacyMigrator.Migrate(db, config);
+
+        Assert.Equal(
+            ["mig-node-b-1", "mig-node-b-2", "mig-node-b-3"],
+            SelectIds(db, "site_events", "id"));
+    }
+
+    [Fact]
+    public void SecondBoot_IsANoOp_BecauseTheFileIsAlreadyMarkedMigrated()
+    {
+        var trackingPath = Path_("site-tracking.db");
+        var eventsPath = Path_("site_events.db");
+        SeedLegacyTracking(trackingPath, "op-1", "op-2");
+        SeedLegacyEvents(eventsPath, count: 2);
+        var config = Config(trackingPath, eventsPath);
+        var db = CreateConsolidated(config);
+
+        SiteLocalDbLegacyMigrator.Migrate(db, config);
+        SiteLocalDbLegacyMigrator.Migrate(db, config);
+
+        Assert.Equal(2, CountRows(db, "OperationTracking"));
+        Assert.Equal(2, CountRows(db, "site_events"));
+    }
+
+    [Fact]
+    public void RerunAgainstAnUnrenamedFile_DoesNotDuplicate()
+    {
+        // Simulates the crash window: the copy committed but the rename did not. On the
+        // next boot the migrator sees the legacy file again and re-copies it. Both tables
+        // must absorb that with no duplicates — tracking via its natural TEXT key, events
+        // via the deterministic mig- id.
+        var trackingPath = Path_("site-tracking.db");
+        var eventsPath = Path_("site_events.db");
+        SeedLegacyTracking(trackingPath, "op-1", "op-2");
+        SeedLegacyEvents(eventsPath, count: 2);
+        var config = Config(trackingPath, eventsPath);
+        var db = CreateConsolidated(config);
+
+        SiteLocalDbLegacyMigrator.Migrate(db, config);
+
+        // Undo the rename, exactly as a crash between commit and rename would leave it.
+        File.Move(trackingPath + ".migrated", trackingPath);
+        File.Move(eventsPath + ".migrated", eventsPath);
+
+        SiteLocalDbLegacyMigrator.Migrate(db, config);
+
+        Assert.Equal(2, CountRows(db, "OperationTracking"));
+        Assert.Equal(2, CountRows(db, "site_events"));
+    }
+
+    [Fact]
+    public void MigratedRows_EnterTheOplog_SoTheyActuallyReplicate()
+    {
+        // The reason the migrator runs AFTER RegisterReplicated. Capture is trigger-based:
+        // migrate before registration and the rows are invisible to the peer forever, with
+        // no error anywhere. Asserting on the oplog is the only way to catch that ordering
+        // being reversed — every other test here would still pass.
+        var trackingPath = Path_("site-tracking.db");
+        SeedLegacyTracking(trackingPath, "op-1", "op-2");
+        var config = Config(trackingPath, Path_("absent-events.db"));
+        var db = CreateConsolidated(config);
+
+        SiteLocalDbLegacyMigrator.Migrate(db, config);
+
+        using var connection = db.CreateConnection();
+        using var cmd = connection.CreateCommand();
+        cmd.CommandText =
+            "SELECT COUNT(*) FROM __localdb_oplog WHERE table_name = 'OperationTracking'";
+        Assert.Equal(2L, (long)cmd.ExecuteScalar()!);
+    }
+
+    [Fact]
+    public void LegacyFileWithoutTheExpectedTable_IsTreatedAsEmpty_NotAFailure()
+    {
+        // A file predating the table (or an unrecognised shape) must not fail host boot.
+        var trackingPath = Path_("site-tracking.db");
+        using (var connection = new SqliteConnection($"Data Source={trackingPath}"))
+        {
+            connection.Open();
+            using var ddl = connection.CreateCommand();
+            ddl.CommandText = "CREATE TABLE something_else (x INTEGER);";
+            ddl.ExecuteNonQuery();
+        }
+
+        var config = Config(trackingPath, Path_("absent-events.db"));
+        var db = CreateConsolidated(config);
+
+        SiteLocalDbLegacyMigrator.Migrate(db, config);
+
+        Assert.Equal(0, CountRows(db, "OperationTracking"));
+        Assert.True(File.Exists(trackingPath + ".migrated"));
+    }
+
+    [Fact]
+    public void InMemoryLegacyConnectionStrings_AreSkipped()
+    {
+        // Test/dev configurations point the legacy stores at in-memory databases. There is
+        // nothing durable to migrate, and Path.GetFullPath on ":memory:" would be nonsense.
+        var config = new ConfigurationBuilder().AddInMemoryCollection(new Dictionary<string, string?>
+        {
+            ["LocalDb:Path"] = Path_("consolidated.db"),
+            ["ScadaBridge:Node:NodeName"] = "node-a",
+            ["ScadaBridge:OperationTracking:ConnectionString"] =
+                "Data Source=file:legacy?mode=memory&cache=shared",
+            ["ScadaBridge:SiteEventLog:DatabasePath"] = ":memory:",
+        }).Build();
+
+        Assert.Equal(string.Empty, SiteLocalDbLegacyMigrator.ResolveTrackingPath(config));
+        Assert.Equal(string.Empty, SiteLocalDbLegacyMigrator.ResolveEventLogPath(config));
+
+        var db = CreateConsolidated(config);
+        SiteLocalDbLegacyMigrator.Migrate(db, config);
+
+        Assert.Equal(0, CountRows(db, "site_events"));
+    }
+
+    [Fact]
+    public void UnsetLegacyKeys_FallBackToTheCwdRelativeCodeDefaults()
+    {
+        // Neither key is set in any docker appsettings, so the OLD code used these
+        // CWD-relative defaults. Resolving them anywhere else (e.g. against the data
+        // volume) would look for the legacy data in a directory it was never written to.
+        var config = new ConfigurationBuilder().AddInMemoryCollection(new Dictionary<string, string?>
+        {
+            ["LocalDb:Path"] = Path_("consolidated.db"),
+        }).Build();
+
+        Assert.Equal(
+            System.IO.Path.GetFullPath("site-tracking.db"),
+            SiteLocalDbLegacyMigrator.ResolveTrackingPath(config));
+        Assert.Equal(
+            System.IO.Path.GetFullPath("site_events.db"),
+            SiteLocalDbLegacyMigrator.ResolveEventLogPath(config));
+    }
+}

tests/ZB.MOM.WW.ScadaBridge.Host.Tests/SiteLocalDbWiringTests.cs

@@ -0,0 +1,228 @@
+using Microsoft.AspNetCore.Builder;
+using Microsoft.Extensions.Configuration;
+using Microsoft.Extensions.DependencyInjection;
+using Microsoft.Extensions.Hosting;
+using Microsoft.Extensions.Logging.Abstractions;
+using Microsoft.Extensions.Options;
+using ZB.MOM.WW.ScadaBridge.HealthMonitoring;
+using ZB.MOM.WW.Telemetry;
+using ZB.MOM.WW.LocalDb;
+using ZB.MOM.WW.LocalDb.Replication;
+using ZB.MOM.WW.ScadaBridge.Host;
+using ZB.MOM.WW.ScadaBridge.Host.Actors;
+
+namespace ZB.MOM.WW.ScadaBridge.Host.Tests;
+
+/// <summary>
+/// LocalDb Phase 1 (Task 3) — locks the consolidated site database into the REAL site
+/// composition root.
+/// <para>
+/// These tests build the actual container from
+/// <see cref="SiteServiceRegistration.Configure"/> rather than asserting on a
+/// hand-assembled <c>ServiceCollection</c>. That is deliberate and load-bearing: this
+/// family has now shipped three wiring defects that only a container-built graph would
+/// have caught — the Secrets Akka replicator that registered into a no-op sink (0.2.0),
+/// the singleton cycle that deadlocked a real host (0.2.2), and Secrets.Ui's missing
+/// <c>IAuditWriter</c> that 500'd only behind a login wall (ScadaBridge#22). A test that
+/// merely checks "AddZbLocalDb was called" would have passed in every one of those cases.
+/// </para>
+/// </summary>
+public class SiteLocalDbWiringTests : IDisposable
+{
+    private readonly WebApplication _host;
+    private readonly string _tempDbPath;
+    private readonly string _tempSiteDbPath;
+    private readonly string _tempTrackingDbPath;
+
+    public SiteLocalDbWiringTests()
+    {
+        var stamp = Guid.NewGuid();
+        _tempDbPath = Path.Combine(Path.GetTempPath(), $"scadabridge_localdb_{stamp}.db");
+        _tempSiteDbPath = Path.Combine(Path.GetTempPath(), $"scadabridge_site_{stamp}.db");
+        _tempTrackingDbPath = Path.Combine(Path.GetTempPath(), $"scadabridge_track_{stamp}.db");
+
+        var builder = WebApplication.CreateBuilder();
+        builder.Configuration.Sources.Clear();
+        builder.Configuration.AddInMemoryCollection(new Dictionary<string, string?>
+        {
+            ["ScadaBridge:Node:Role"] = "Site",
+            ["ScadaBridge:Node:NodeName"] = "node-a",
+            ["ScadaBridge:Node:NodeHostname"] = "test-site",
+            ["ScadaBridge:Node:SiteId"] = "TestSite",
+            ["ScadaBridge:Node:RemotingPort"] = "0",
+            ["ScadaBridge:Node:GrpcPort"] = "0",
+            ["ScadaBridge:Database:SiteDbPath"] = _tempSiteDbPath,
+            ["ScadaBridge:OperationTracking:ConnectionString"] = $"Data Source={_tempTrackingDbPath}",
+            ["ScadaBridge:Cluster:SeedNodes:0"] = "akka.tcp://scadabridge@localhost:2551",
+            ["ScadaBridge:Cluster:SeedNodes:1"] = "akka.tcp://scadabridge@localhost:2552",
+
+            // The consolidated site database. No Replication section at all — this
+            // fixture is also the default-OFF pin: storage must work standalone.
+            ["LocalDb:Path"] = _tempDbPath,
+        });
+
+        builder.Services.AddGrpc();
+        builder.Services.AddSingleton<ZB.MOM.WW.ScadaBridge.Communication.Grpc.SiteStreamGrpcServer>();
+
+        SiteServiceRegistration.Configure(builder.Services, builder.Configuration);
+
+        AkkaHostedServiceRemover.RemoveAkkaHostedServiceOnly(builder.Services);
+
+        _host = builder.Build();
+    }
+
+    public void Dispose()
+    {
+        (_host as IDisposable)?.Dispose();
+        foreach (var path in new[] { _tempDbPath, _tempSiteDbPath, _tempTrackingDbPath })
+        {
+            try { File.Delete(path); } catch { /* best effort */ }
+        }
+
+        GC.SuppressFinalize(this);
+    }
+
+    [Fact]
+    public void Site_Resolves_ILocalDb()
+    {
+        Assert.NotNull(_host.Services.GetService<ILocalDb>());
+    }
+
+    [Fact]
+    public void Site_LocalDb_RegistersBothReplicatedTables()
+    {
+        // The whole point of Phase 1. If onReady silently failed to register these,
+        // storage would still work and every other test would pass — the pair would
+        // just never replicate anything. Assert on the library's own view of what is
+        // registered, not on our belief that we called it.
+        var db = _host.Services.GetRequiredService<ILocalDb>();
+
+        Assert.Contains("OperationTracking", db.ReplicatedTables.Keys);
+        Assert.Contains("site_events", db.ReplicatedTables.Keys);
+    }
+
+    [Fact]
+    public void Site_LocalDb_ReplicatedTablesHaveExplicitPrimaryKeys()
+    {
+        // RegisterReplicated refuses a table with no explicit PK, so reaching this
+        // assertion at all proves the DDL ran before registration. The PK names are
+        // pinned because they are what last-writer-wins conflict resolution keys on.
+        var db = _host.Services.GetRequiredService<ILocalDb>();
+
+        Assert.Equal(
+            ["TrackedOperationId"], db.ReplicatedTables["OperationTracking"].PkColumns);
+        Assert.Equal(["id"], db.ReplicatedTables["site_events"].PkColumns);
+    }
+
+    [Fact]
+    public void Site_LocalDb_IsASingleton()
+    {
+        // AddZbLocalDb is one-DB-per-process and first-registration-wins. Two
+        // instances would mean two SQLite handles and two trigger installations
+        // against the same file.
+        var first = _host.Services.GetRequiredService<ILocalDb>();
+        var second = _host.Services.GetRequiredService<ILocalDb>();
+
+        Assert.Same(first, second);
+    }
+
+    [Fact]
+    public void Site_Replication_IsRegistered_AndInertWithNoPeer()
+    {
+        // Task 7's default-OFF pin. This fixture configures NO
+        // LocalDb:Replication:PeerAddress, which is the shipping default, so the
+        // engine must resolve cleanly and sit idle rather than throw, retry, or
+        // report a connection. "Default-off" here means inert, NOT unregistered —
+        // the services are always in the graph.
+        var status = _host.Services.GetService<ISyncStatus>();
+
+        Assert.NotNull(status);
+        Assert.False(status!.Connected);
+        Assert.Null(status.PeerNodeId);
+        Assert.Null(status.LastSyncUtc);
+    }
+
+    [Fact]
+    public void Site_Replication_OplogBacklog_IsZero_NotNull_OnAHealthyIdleNode()
+    {
+        // OplogBacklog is deliberately nullable: null means "unknown" (no provider
+        // wired, or the poll failed) and must never be reported as a healthy 0. On a
+        // node whose local database is present and readable, a real 0 proves the
+        // backlog provider is actually wired to the oplog — a null here would mean
+        // Task 9's health signal is about to publish "unknown" forever.
+        var status = _host.Services.GetRequiredService<ISyncStatus>();
+
+        Assert.Equal(0L, status.OplogBacklog);
+    }
+
+    [Fact]
+    public void Site_Replication_HealthBridge_IsRegisteredAsAHostedService()
+    {
+        // Task 9. Registered via a factory lambda, so ImplementationType is null and the
+        // only honest assertion is on the resolved instance.
+        var hosted = _host.Services.GetServices<IHostedService>();
+
+        Assert.Contains(hosted, h => h is LocalDbReplicationStatusReporter);
+    }
+
+    [Fact]
+    public void Site_Replication_HealthBridge_PublishesStatusOntoTheHealthReport()
+    {
+        // End-to-end through the REAL collector: probe once, then collect. This is what
+        // catches a bridge that is registered but wired to the wrong provider — the
+        // registration test above would pass either way.
+        var reporter = _host.Services.GetServices<IHostedService>()
+            .OfType<LocalDbReplicationStatusReporter>()
+            .Single();
+        var collector = _host.Services.GetRequiredService<ISiteHealthCollector>();
+
+        reporter.Probe();
+        var report = collector.CollectReport("TestSite");
+
+        // No peer configured: not connected, and a REAL 0 backlog rather than null.
+        Assert.False(report.LocalDbReplicationConnected);
+        Assert.Equal(0L, report.LocalDbOplogBacklog);
+    }
+
+    [Fact]
+    public void Site_HealthReport_LocalDbFields_AreNull_BeforeTheReporterHasRun()
+    {
+        // Null means "no data yet", and must be distinguishable from a real
+        // "disconnected, zero backlog". A collector that defaulted these to false/0 would
+        // report an unwired node as a healthy connected one.
+        var collector = new SiteHealthCollector();
+
+        var report = collector.CollectReport("TestSite");
+
+        Assert.Null(report.LocalDbReplicationConnected);
+        Assert.Null(report.LocalDbOplogBacklog);
+    }
+
+    [Fact]
+    public void Site_Telemetry_Allowlists_TheLocalDbReplicationMeter()
+    {
+        // ZbTelemetryOptions.Meters is an ALLOWLIST: an unlisted Meter's instruments are
+        // never observed, with no error and no missing-metric warning anywhere. The
+        // replication meter was silently absent from the rig's /metrics scrape until the
+        // live gate looked for it, so pin the allowlist rather than trusting the next
+        // reader to remember.
+        //
+        // This asserts on the allowlist constant, not on OTel's observed-meter set:
+        // AddZbTelemetry applies its options to a local instance and never registers
+        // IOptions, so there is nothing resolvable to interrogate. The end-to-end proof
+        // that localdb_* actually reaches /metrics is the Task 12 live gate; this test
+        // exists to stop the entry being dropped again.
+        Assert.Contains(LocalDbMetrics.MeterName, SiteServiceRegistration.ObservedMeters);
+    }
+
+    [Fact]
+    public void Site_LocalDb_CreatesTheConfiguredFile()
+    {
+        // Resolving is what triggers onReady; the file should exist afterwards at the
+        // configured path and not somewhere CWD-relative.
+        _ = _host.Services.GetRequiredService<ILocalDb>();
+
+        Assert.True(File.Exists(_tempDbPath),
+            $"Expected the consolidated site database at '{_tempDbPath}'.");
+    }
+}

tests/ZB.MOM.WW.ScadaBridge.IntegrationTests/LocalDbSitePairConvergenceTests.cs

@@ -0,0 +1,348 @@
+using System.Net;
+using Microsoft.AspNetCore.Builder;
+using Microsoft.AspNetCore.Hosting;
+using Microsoft.AspNetCore.Hosting.Server;
+using Microsoft.AspNetCore.Hosting.Server.Features;
+using Microsoft.AspNetCore.Server.Kestrel.Core;
+using Microsoft.Extensions.Configuration;
+using Microsoft.Extensions.DependencyInjection;
+using Microsoft.Extensions.Hosting;
+using ZB.MOM.WW.LocalDb;
+using ZB.MOM.WW.LocalDb.Replication;
+using ZB.MOM.WW.ScadaBridge.Host;
+
+namespace ZB.MOM.WW.ScadaBridge.IntegrationTests;
+
+/// <summary>
+/// Serializes the site-pair convergence tests against each other: each one stands up a real
+/// Kestrel listener plus two SQLite files, and running them concurrently under CI
+/// contention is a flakiness risk.
+/// </summary>
+[CollectionDefinition("LocalDbSitePairConvergence")]
+public sealed class LocalDbSitePairConvergenceCollection;
+
+/// <summary>
+/// Two ScadaBridge site nodes replicating the consolidated site database over a REAL
+/// loopback gRPC transport, through the REAL fail-closed auth interceptor.
+/// </summary>
+/// <remarks>
+/// <para>
+/// This is the test that answers the question Phase 1 exists to answer: does a site node
+/// pair actually stop losing operation-tracking and site-event state? Everything upstream
+/// of it — schema helpers, DI wiring, the interceptor — can be individually green while the
+/// pair still fails to converge.
+/// </para>
+/// <para>
+/// It uses <see cref="SiteLocalDbSetup.OnReady"/>, not a hand-written schema, so the tables,
+/// their primary keys, and the registration ORDER under test are the ones the host actually
+/// runs. A separate schema here would prove only that the test agrees with itself.
+/// </para>
+/// <para>
+/// Offline: no docker, no external services. Loopback Kestrel with h2c.
+/// </para>
+/// </remarks>
+[Collection("LocalDbSitePairConvergence")]
+public sealed class LocalDbSitePairConvergenceTests : IAsyncLifetime
+{
+    private const string SharedApiKey = "site-pair-convergence-key";
+    private static readonly TimeSpan ConvergeTimeout = TimeSpan.FromSeconds(30);
+
+    private readonly string _pathA = Path.Combine(Path.GetTempPath(), $"sitepairA-{Guid.NewGuid():N}.db");
+    private readonly string _pathB = Path.Combine(Path.GetTempPath(), $"sitepairB-{Guid.NewGuid():N}.db");
+
+    // The databases are owned by the fixture, in their own providers, and registered into the
+    // hosts as pre-constructed instances. MS.DI does not dispose instances it did not create,
+    // so tearing a host down (the offline-peer scenario) leaves the databases intact and
+    // writable — which is exactly what lets node A accumulate writes while B is down.
+    private ServiceProvider _dbProviderA = null!;
+    private ServiceProvider _dbProviderB = null!;
+
+    private IHost? _serverHost;      // node B — passive
+    private IHost? _initiatorHost;   // node A — dials the peer
+
+    static LocalDbSitePairConvergenceTests() =>
+        // Grpc.Net.Client dials the loopback server over HTTP/2 cleartext.
+        AppContext.SetSwitch("System.Net.Http.SocketsHttpHandler.Http2UnencryptedSupport", true);
+
+    private ILocalDb A => _dbProviderA.GetRequiredService<ILocalDb>();
+    private ILocalDb B => _dbProviderB.GetRequiredService<ILocalDb>();
+
+    public async Task InitializeAsync()
+    {
+        _dbProviderA = BuildDatabaseProvider(_pathA, "node-a");
+        _dbProviderB = BuildDatabaseProvider(_pathB, "node-b");
+
+        // Force construction (and therefore OnReady) before anything replicates.
+        _ = A;
+        _ = B;
+
+        await StartPassiveAsync();
+        await StartInitiatorAsync();
+    }
+
+    public async Task DisposeAsync()
+    {
+        await StopHostAsync(_initiatorHost);
+        await StopHostAsync(_serverHost);
+        await _dbProviderA.DisposeAsync();
+        await _dbProviderB.DisposeAsync();
+
+        Microsoft.Data.Sqlite.SqliteConnection.ClearAllPools();
+        foreach (var path in new[] { _pathA, _pathB })
+        {
+            foreach (var suffix in new[] { "", "-wal", "-shm" })
+            {
+                try { File.Delete(path + suffix); } catch { /* best effort */ }
+            }
+        }
+    }
+
+    // ---- fixture internals ------------------------------------------------------------
+
+    /// <summary>
+    /// A provider owning one consolidated site database, initialized through the host's own
+    /// <see cref="SiteLocalDbSetup.OnReady"/> — same schema, same registration order.
+    /// </summary>
+    private static ServiceProvider BuildDatabaseProvider(string path, string nodeName)
+    {
+        var config = new ConfigurationBuilder()
+            .AddInMemoryCollection(new Dictionary<string, string?>
+            {
+                ["LocalDb:Path"] = path,
+                ["ScadaBridge:Node:NodeName"] = nodeName,
+                // Point the legacy migrator at paths that do not exist, so it no-ops rather
+                // than picking up stray files from the test working directory.
+                ["ScadaBridge:OperationTracking:ConnectionString"] =
+                    $"Data Source={Path.Combine(Path.GetTempPath(), $"absent-{Guid.NewGuid():N}.db")}",
+                ["ScadaBridge:SiteEventLog:DatabasePath"] =
+                    Path.Combine(Path.GetTempPath(), $"absent-{Guid.NewGuid():N}.db"),
+            })
+            .Build();
+
+        return new ServiceCollection()
+            .AddZbLocalDb(config, db => SiteLocalDbSetup.OnReady(db, config))
+            .BuildServiceProvider();
+    }
+
+    private static IConfiguration ReplicationConfig(string? peerAddress)
+    {
+        var values = new Dictionary<string, string?>
+        {
+            // Tight flush + bounded reconnect backoff so convergence is observable well
+            // inside the poll deadline. The 60 s production default would let the doubling
+            // backoff overrun it after a peer outage.
+            ["LocalDb:Replication:FlushInterval"] = "00:00:00.050",
+            ["LocalDb:Replication:ReconnectBackoffMax"] = "00:00:02",
+            // Both nodes share one key — the interceptor is fail-closed, so a mismatch here
+            // turns every scenario below red (verified by deliberately breaking it).
+            ["LocalDb:Replication:ApiKey"] = SharedApiKey,
+        };
+
+        if (peerAddress is not null)
+            values["LocalDb:Replication:PeerAddress"] = peerAddress;
+
+        return new ConfigurationBuilder().AddInMemoryCollection(values).Build();
+    }
+
+    private async Task StartPassiveAsync()
+    {
+        var config = ReplicationConfig(peerAddress: null);
+
+        _serverHost = await new HostBuilder()
+            .ConfigureWebHost(web =>
+            {
+                web.UseKestrel(o =>
+                    o.Listen(IPAddress.Loopback, 0, listen => listen.Protocols = HttpProtocols.Http2));
+                web.ConfigureServices(services =>
+                {
+                    services.AddLogging();
+                    services.AddRouting();
+                    // The REAL interceptor, not a stand-in. If it rejected legitimate peer
+                    // traffic, every scenario below would fail — which is the point.
+                    services.AddGrpc(o => o.Interceptors.Add<LocalDbSyncAuthInterceptor>());
+                    services.AddSingleton(B);
+                    services.AddZbLocalDbReplication(config);
+                });
+                web.Configure(app =>
+                {
+                    app.UseRouting();
+                    app.UseEndpoints(e => e.MapZbLocalDbSync());
+                });
+            })
+            .StartAsync();
+    }
+
+    private async Task StartInitiatorAsync()
+    {
+        var config = ReplicationConfig(PassiveAddress());
+
+        _initiatorHost = await new HostBuilder()
+            .ConfigureServices(services =>
+            {
+                services.AddLogging();
+                services.AddSingleton(A);
+                services.AddZbLocalDbReplication(config);
+            })
+            .StartAsync();
+    }
+
+    private string PassiveAddress()
+        => _serverHost!.Services.GetRequiredService<IServer>()
+            .Features.Get<IServerAddressesFeature>()!.Addresses.Single();
+
+    private static async Task StopHostAsync(IHost? host)
+    {
+        if (host is null) return;
+        try { await host.StopAsync(TimeSpan.FromSeconds(5)); } catch { /* teardown */ }
+        host.Dispose();
+    }
+
+    // ---- data helpers -----------------------------------------------------------------
+
+    private static Task WriteTrackingAsync(ILocalDb db, string id, string status, string target)
+        => db.ExecuteAsync(
+            """
+            INSERT INTO OperationTracking (
+                TrackedOperationId, Kind, TargetSummary, Status, RetryCount,
+                CreatedAtUtc, UpdatedAtUtc)
+            VALUES (@id, 'ApiCallCached', @target, @status, 0, @now, @now)
+            ON CONFLICT(TrackedOperationId) DO UPDATE SET
+                Status = excluded.Status,
+                TargetSummary = excluded.TargetSummary,
+                UpdatedAtUtc = excluded.UpdatedAtUtc;
+            """,
+            new { id, status, target, now = DateTime.UtcNow.ToString("o") });
+
+    private static Task WriteEventAsync(ILocalDb db, string id, string message)
+        => db.ExecuteAsync(
+            """
+            INSERT INTO site_events (id, timestamp, event_type, severity, source, message)
+            VALUES (@id, @ts, 'script', 'Info', 'convergence-test', @message);
+            """,
+            new { id, ts = DateTimeOffset.UtcNow.ToString("o"), message });
+
+    private static async Task<string?> ReadTrackingStatusAsync(ILocalDb db, string id)
+    {
+        var rows = await db.QueryAsync(
+            "SELECT Status FROM OperationTracking WHERE TrackedOperationId = @id",
+            static r => r.GetString(0), new { id });
+        return rows.Count == 0 ? null : rows[0];
+    }
+
+    private static Task<IReadOnlyList<string>> ReadEventIdsAsync(ILocalDb db)
+        => db.QueryAsync("SELECT id FROM site_events ORDER BY id", static r => r.GetString(0));
+
+    /// <summary>Polls <paramref name="condition"/> until true or the deadline passes.</summary>
+    private static async Task WaitUntilAsync(Func<Task<bool>> condition, string because)
+    {
+        var deadline = DateTime.UtcNow + ConvergeTimeout;
+        while (DateTime.UtcNow < deadline)
+        {
+            if (await condition()) return;
+            await Task.Delay(50);
+        }
+
+        Assert.Fail($"Timed out after {ConvergeTimeout.TotalSeconds:0}s waiting for: {because}");
+    }
+
+    // ---- scenarios --------------------------------------------------------------------
+
+    [Fact]
+    public async Task TrackingRow_WrittenOnA_BecomesReadableOnB()
+    {
+        await WriteTrackingAsync(A, "op-a-1", "Submitted", "ERP.GetOrder");
+
+        await WaitUntilAsync(
+            async () => await ReadTrackingStatusAsync(B, "op-a-1") == "Submitted",
+            "the tracking row written on node A to appear on node B");
+    }
+
+    [Fact]
+    public async Task TrackingRow_WrittenOnB_BecomesReadableOnA()
+    {
+        // Replication is bidirectional even though only A dials: proving the passive node's
+        // writes flow back is what makes a failover in EITHER direction safe.
+        await WriteTrackingAsync(B, "op-b-1", "Submitted", "ERP.GetOrder");
+
+        await WaitUntilAsync(
+            async () => await ReadTrackingStatusAsync(A, "op-b-1") == "Submitted",
+            "the tracking row written on node B to appear on node A");
+    }
+
+    [Fact]
+    public async Task SameOperation_UpdatedOnBothNodes_ConvergesToOneWinner()
+    {
+        // Last-writer-wins on the primary key. The specific winner is not asserted — that is
+        // the HLC's business — but the two nodes MUST agree, and must agree on a value one of
+        // them actually wrote rather than a merge of both.
+        await WriteTrackingAsync(A, "op-conflict", "Submitted", "ERP.GetOrder");
+        await WaitUntilAsync(
+            async () => await ReadTrackingStatusAsync(B, "op-conflict") is not null,
+            "the conflicting row to exist on both nodes before it is updated");
+
+        await WriteTrackingAsync(A, "op-conflict", "Delivered", "ERP.GetOrder");
+        await WriteTrackingAsync(B, "op-conflict", "Parked", "ERP.GetOrder");
+
+        await WaitUntilAsync(
+            async () =>
+            {
+                var a = await ReadTrackingStatusAsync(A, "op-conflict");
+                var b = await ReadTrackingStatusAsync(B, "op-conflict");
+                return a is not null && a == b;
+            },
+            "both nodes to converge on one status for the contended operation");
+
+        var winner = await ReadTrackingStatusAsync(A, "op-conflict");
+        Assert.Contains(winner, new[] { "Delivered", "Parked" });
+    }
+
+    [Fact]
+    public async Task EventsLoggedOnBothNodes_ConvergeToTheUnion_WithNoIdCollisions()
+    {
+        // The reason site_events moved to GUID ids. Under the old autoincrement scheme both
+        // nodes would independently mint id=1, id=2, ... and last-writer-wins on the primary
+        // key would silently DESTROY one node's events instead of merging them. The union
+        // count is the assertion that catches that.
+        var idsFromA = Enumerable.Range(0, 5).Select(_ => Guid.NewGuid().ToString("N")).ToList();
+        var idsFromB = Enumerable.Range(0, 5).Select(_ => Guid.NewGuid().ToString("N")).ToList();
+
+        foreach (var id in idsFromA) await WriteEventAsync(A, id, "from A");
+        foreach (var id in idsFromB) await WriteEventAsync(B, id, "from B");
+
+        var expected = idsFromA.Concat(idsFromB).OrderBy(x => x, StringComparer.Ordinal).ToList();
+
+        await WaitUntilAsync(
+            async () => (await ReadEventIdsAsync(A)).SequenceEqual(expected)
+                        && (await ReadEventIdsAsync(B)).SequenceEqual(expected),
+            "both nodes to hold the union of all 10 events");
+    }
+
+    [Fact]
+    public async Task PeerOffline_ThenRejoins_CatchesUpOnEverythingItMissed()
+    {
+        // The failover case that motivates Phase 1: one node is down while the other keeps
+        // working, and nothing written during the outage may be lost.
+        await StopHostAsync(_serverHost);
+        _serverHost = null;
+
+        var idsDuringOutage = Enumerable.Range(0, 5).Select(_ => Guid.NewGuid().ToString("N")).ToList();
+        foreach (var id in idsDuringOutage) await WriteEventAsync(A, id, "written while B was down");
+        await WriteTrackingAsync(A, "op-during-outage", "Delivered", "ERP.GetOrder");
+
+        // Node B's database survived the host teardown (pre-constructed instance), so this is
+        // a genuine rejoin rather than a fresh node. It comes back on a NEW loopback port;
+        // the initiator's channel factory re-reads the peer address on each reconnect.
+        await StartPassiveAsync();
+        await StopHostAsync(_initiatorHost);
+        await StartInitiatorAsync();
+
+        await WaitUntilAsync(
+            async () =>
+            {
+                var events = await ReadEventIdsAsync(B);
+                return idsDuringOutage.All(events.Contains)
+                       && await ReadTrackingStatusAsync(B, "op-during-outage") == "Delivered";
+            },
+            "node B to catch up on everything written while it was offline");
+    }
+}

tests/ZB.MOM.WW.ScadaBridge.SiteEventLogging.Tests/EventLogCoverageTests.cs

@@ -11,20 +11,27 @@ namespace ZB.MOM.WW.ScadaBridge.SiteEventLogging.Tests;
 public class EventLogCoverageTests : IDisposable
 {
     private readonly string _dbPath;
+    private readonly TestLocalDb _localDb;
 
     public EventLogCoverageTests()
     {
         _dbPath = Path.Combine(Path.GetTempPath(), $"test_coverage_{Guid.NewGuid()}.db");
+        _localDb = TestLocalDb.Create(_dbPath);
     }
 
     public void Dispose()
     {
-        if (File.Exists(_dbPath)) File.Delete(_dbPath);
+        _localDb.Dispose();
+        TestLocalDb.DeleteFiles(_dbPath);
+        GC.SuppressFinalize(this);
     }
 
+    // Each call gets its own connection from the one shared local database, so the
+    // loggers these tests dispose mid-test do not tear down the database itself.
     private SiteEventLogger NewLogger() => new(
         Options.Create(new SiteEventLogOptions { DatabasePath = _dbPath }),
-        NullLogger<SiteEventLogger>.Instance);
+        NullLogger<SiteEventLogger>.Instance,
+        _localDb.Db);
 
     private static EventLogQueryRequest MakeRequest() => new(
         CorrelationId: "corr-err",

tests/ZB.MOM.WW.ScadaBridge.SiteEventLogging.Tests/EventLogPurgeServiceTests.cs

@@ -6,6 +6,7 @@ namespace ZB.MOM.WW.ScadaBridge.SiteEventLogging.Tests;
 public class EventLogPurgeServiceTests : IDisposable
 {
     private readonly SiteEventLogger _eventLogger;
+    private readonly TestLocalDb _localDb;
     private readonly string _dbPath;
     private readonly SiteEventLogOptions _options;
 
@@ -27,15 +28,19 @@ public class EventLogPurgeServiceTests : IDisposable
             RetentionDays = 30,
             MaxStorageMb = 1024
         };
+        _localDb = TestLocalDb.Create(_dbPath);
         _eventLogger = new SiteEventLogger(
             Options.Create(_options),
-            NullLogger<SiteEventLogger>.Instance);
+            NullLogger<SiteEventLogger>.Instance,
+            _localDb.Db);
     }
 
     public void Dispose()
     {
         _eventLogger.Dispose();
-        if (File.Exists(_dbPath)) File.Delete(_dbPath);
+        _localDb.Dispose();
+        TestLocalDb.DeleteFiles(_dbPath);
+        GC.SuppressFinalize(this);
     }
 
     private EventLogPurgeService CreatePurgeService(
@@ -56,9 +61,10 @@ public class EventLogPurgeServiceTests : IDisposable
         {
             using var cmd = connection.CreateCommand();
             cmd.CommandText = """
-                INSERT INTO site_events (timestamp, event_type, severity, source, message)
+                INSERT INTO site_events (id, timestamp, event_type, severity, source, message)
-                VALUES ($ts, 'script', 'Info', 'Test', 'Test message')
+                VALUES ($id, $ts, 'script', 'Info', 'Test', 'Test message')
                 """;
+            cmd.Parameters.AddWithValue("$id", Guid.NewGuid().ToString("N"));
             cmd.Parameters.AddWithValue("$ts", timestamp.ToString("o"));
             cmd.ExecuteNonQuery();
         });
@@ -135,6 +141,29 @@ public class EventLogPurgeServiceTests : IDisposable
         Assert.True(size > 0);
     }
 
+    /// <summary>
+    /// Base instant for bulk-seeded events: one day ago, evaluated once so a run is
+    /// self-consistent.
+    /// <para>
+    /// Must stay INSIDE the retention window (<c>RetentionDays</c>, default 30).
+    /// <see cref="EventLogPurgeService.RunPurge"/> applies the retention purge before
+    /// the storage-cap purge, so a fixed calendar date in the past would be deleted
+    /// wholesale as stale and the storage-cap assertions would silently test an empty
+    /// table rather than cap-trimming behaviour.
+    /// </para>
+    /// </summary>
+    private static readonly DateTimeOffset BulkSeedStart =
+        DateTimeOffset.UtcNow.AddDays(-1);
+
+    /// <summary>
+    /// Timestamp of the i-th bulk-seeded event. Each row is one second newer than the
+    /// last, so "oldest" is unambiguous — the storage-cap purge orders by timestamp,
+    /// and previously every bulk row shared the same <c>UtcNow</c>, which left the
+    /// oldest-first guarantee untestable.
+    /// </summary>
+    private static DateTimeOffset BulkSeedTimestamp(int index) =>
+        BulkSeedStart.AddSeconds(index);
+
     private void InsertBulkEvents(int count)
     {
         // Each event carries a sizeable details payload so the database grows
@@ -146,24 +175,30 @@ public class EventLogPurgeServiceTests : IDisposable
             {
                 using var cmd = connection.CreateCommand();
                 cmd.CommandText = """
-                    INSERT INTO site_events (timestamp, event_type, severity, source, message, details)
+                    INSERT INTO site_events (id, timestamp, event_type, severity, source, message, details)
-                    VALUES ($ts, 'script', 'Info', 'Test', 'Bulk event', $details)
+                    VALUES ($id, $ts, 'script', 'Info', 'Test', 'Bulk event', $details)
                     """;
-                cmd.Parameters.AddWithValue("$ts", DateTimeOffset.UtcNow.ToString("o"));
+                cmd.Parameters.AddWithValue("$id", Guid.NewGuid().ToString("N"));
+                cmd.Parameters.AddWithValue("$ts", BulkSeedTimestamp(i).ToString("o"));
                 cmd.Parameters.AddWithValue("$details", details);
                 cmd.ExecuteNonQuery();
             }
         });
     }
 
-    private long MinEventId()
+    /// <summary>
+    /// Oldest surviving event timestamp. Replaces the former <c>MIN(id)</c> probe:
+    /// ids are GUIDs since LocalDb Phase 1 and carry no ordering, so age is measured
+    /// on the timestamp column the purge itself orders by.
+    /// </summary>
+    private string MinEventTimestamp()
     {
         return _eventLogger.WithConnection(connection =>
         {
             using var cmd = connection.CreateCommand();
-            cmd.CommandText = "SELECT MIN(id) FROM site_events";
+            cmd.CommandText = "SELECT MIN(timestamp) FROM site_events";
             var result = cmd.ExecuteScalar();
-            return result is long l ? l : 0;
+            return result as string ?? "";
         });
     }
 
@@ -204,9 +239,15 @@ public class EventLogPurgeServiceTests : IDisposable
     [Fact]
     public void PurgeByStorageCap_RemovesOldestEventsFirst()
     {
-        // Regression test for SiteEventLogging-002: only the oldest events
+        // Regression test for SiteEventLogging-002: only the oldest events should be
-        // (lowest ids) should be removed when trimming to the cap.
+        // removed when trimming to the cap.
-        InsertBulkEvents(3000);
+        //
+        // "Oldest" used to mean "lowest autoincrement id". Since LocalDb Phase 1 the id
+        // is a GUID with no ordering, so both the purge and this test key on timestamp.
+        // Ordering by id here would delete an arbitrary batch, which is precisely the
+        // regression this test now guards.
+        const int eventCount = 3000;
+        InsertBulkEvents(eventCount);
 
         var purge = CreatePurgeService();
         var totalSize = purge.GetDatabaseSizeBytes();
@@ -218,20 +259,23 @@ public class EventLogPurgeServiceTests : IDisposable
             MaxStorageMb = (int)Math.Max(1, (totalSize / 2) / (1024 * 1024))
         };
 
-        var minIdBefore = MinEventId();
+        var oldestBefore = MinEventTimestamp();
         var cappedPurge = CreatePurgeService(capOptions);
         cappedPurge.RunPurge();
-        var minIdAfter = MinEventId();
+        var oldestAfter = MinEventTimestamp();
 
-        // The surviving rows must be the newest ones — minimum id has advanced.
+        // The surviving rows must be the newest ones — the oldest timestamp advanced.
-        Assert.True(minIdAfter > minIdBefore,
+        Assert.True(
-            "Oldest events (lowest ids) must be purged first.");
+            string.CompareOrdinal(oldestAfter, oldestBefore) > 0,
+            $"Oldest events must be purged first; oldest went from '{oldestBefore}' to '{oldestAfter}'.");
 
-        // The newest event (highest id) must still be present.
+        // The newest event must still be present.
+        var newestTimestamp = BulkSeedTimestamp(eventCount - 1).ToString("o");
         var newestPresent = _eventLogger.WithConnection(connection =>
         {
             using var cmd = connection.CreateCommand();
-            cmd.CommandText = "SELECT COUNT(*) FROM site_events WHERE id = 3000";
+            cmd.CommandText = "SELECT COUNT(*) FROM site_events WHERE timestamp = $ts";
+            cmd.Parameters.AddWithValue("$ts", newestTimestamp);
             return (long)cmd.ExecuteScalar()!;
         });
         Assert.Equal(1L, newestPresent);

tests/ZB.MOM.WW.ScadaBridge.SiteEventLogging.Tests/EventLogQueryServiceTests.cs

@@ -8,6 +8,7 @@ public class EventLogQueryServiceTests : IDisposable
 {
     private readonly SiteEventLogger _eventLogger;
     private readonly EventLogQueryService _queryService;
+    private readonly TestLocalDb _localDb;
     private readonly string _dbPath;
 
     public EventLogQueryServiceTests()
@@ -18,7 +19,8 @@ public class EventLogQueryServiceTests : IDisposable
             DatabasePath = _dbPath,
             QueryPageSize = 500
         });
-        _eventLogger = new SiteEventLogger(options, NullLogger<SiteEventLogger>.Instance);
+        _localDb = TestLocalDb.Create(_dbPath);
+        _eventLogger = new SiteEventLogger(options, NullLogger<SiteEventLogger>.Instance, _localDb.Db);
         _queryService = new EventLogQueryService(
             _eventLogger,
             options,
@@ -28,7 +30,9 @@ public class EventLogQueryServiceTests : IDisposable
     public void Dispose()
     {
         _eventLogger.Dispose();
-        if (File.Exists(_dbPath)) File.Delete(_dbPath);
+        _localDb.Dispose();
+        TestLocalDb.DeleteFiles(_dbPath);
+        GC.SuppressFinalize(this);
     }
 
     private async Task SeedEvents()
@@ -45,7 +49,7 @@ public class EventLogQueryServiceTests : IDisposable
         string? severity = null,
         string? instanceId = null,
         string? keyword = null,
-        long? continuationToken = null,
+        string? continuationToken = null,
         int pageSize = 500,
         DateTimeOffset? from = null,
         DateTimeOffset? to = null) =>
@@ -281,7 +285,7 @@ public class EventLogQueryServiceTests : IDisposable
         Assert.Single(response.Entries);
 
         var entry = response.Entries[0];
-        Assert.True(entry.Id > 0);
+        Assert.False(string.IsNullOrWhiteSpace(entry.Id));
         Assert.Equal("script", entry.EventType);
         Assert.Equal("Error", entry.Severity);
         Assert.Equal("inst-1", entry.InstanceId);
@@ -340,9 +344,10 @@ public class EventLogQueryServiceTests : IDisposable
         {
             using var cmd = connection.CreateCommand();
             cmd.CommandText = """
-                INSERT INTO site_events (timestamp, event_type, severity, instance_id, source, message)
+                INSERT INTO site_events (id, timestamp, event_type, severity, instance_id, source, message)
-                VALUES ($ts, $et, $sev, $iid, $src, $msg)
+                VALUES ($id, $ts, $et, $sev, $iid, $src, $msg)
                 """;
+            cmd.Parameters.AddWithValue("$id", Guid.NewGuid().ToString("N"));
             cmd.Parameters.AddWithValue("$ts", timestamp.ToString("o"));
             cmd.Parameters.AddWithValue("$et", eventType);
             cmd.Parameters.AddWithValue("$sev", severity);
@@ -366,7 +371,8 @@ public class EventLogQueryServiceTests : IDisposable
             QueryPageSize = 500,
             MaxQueryPageSize = 3,
         });
-        using var logger = new SiteEventLogger(options, NullLogger<SiteEventLogger>.Instance);
+        using var localDb = TestLocalDb.Create(dbPath);
+        using var logger = new SiteEventLogger(options, NullLogger<SiteEventLogger>.Instance, localDb.Db);
         var query = new EventLogQueryService(logger, options, NullLogger<EventLogQueryService>.Instance);
 
         try
@@ -390,7 +396,8 @@ public class EventLogQueryServiceTests : IDisposable
         finally
         {
             logger.Dispose();
-            if (File.Exists(dbPath)) File.Delete(dbPath);
+            localDb.Dispose();
+            TestLocalDb.DeleteFiles(dbPath);
         }
     }
 }

tests/ZB.MOM.WW.ScadaBridge.SiteEventLogging.Tests/SchemaIndexTests.cs

@@ -13,12 +13,14 @@ public class SchemaIndexTests : IDisposable
     private readonly SiteEventLogger _logger;
     private readonly SqliteConnection _verifyConnection;
     private readonly string _dbPath;
+    private readonly TestLocalDb _localDb;
 
     public SchemaIndexTests()
     {
         _dbPath = Path.Combine(Path.GetTempPath(), $"test_index_{Guid.NewGuid()}.db");
         var options = Options.Create(new SiteEventLogOptions { DatabasePath = _dbPath });
-        _logger = new SiteEventLogger(options, NullLogger<SiteEventLogger>.Instance);
+        _localDb = TestLocalDb.Create(_dbPath);
+        _logger = new SiteEventLogger(options, NullLogger<SiteEventLogger>.Instance, _localDb.Db);
 
         _verifyConnection = new SqliteConnection($"Data Source={_dbPath}");
         _verifyConnection.Open();
@@ -28,7 +30,8 @@ public class SchemaIndexTests : IDisposable
     {
         _verifyConnection.Dispose();
         _logger.Dispose();
-        if (File.Exists(_dbPath)) File.Delete(_dbPath);
+        _localDb.Dispose();
+        TestLocalDb.DeleteFiles(_dbPath);
     }
 
     [Fact]

tests/ZB.MOM.WW.ScadaBridge.SiteEventLogging.Tests/ServiceWiringTests.cs

@@ -1,6 +1,8 @@
+using Microsoft.Extensions.Configuration;
 using Microsoft.Extensions.DependencyInjection;
 using Microsoft.Extensions.Logging;
 using Microsoft.Extensions.Options;
+using ZB.MOM.WW.LocalDb;
 
 namespace ZB.MOM.WW.ScadaBridge.SiteEventLogging.Tests;
 
@@ -23,7 +25,7 @@ public class ServiceWiringTests : IDisposable
     public void Dispose()
     {
         _provider?.Dispose();
-        if (File.Exists(_dbPath)) File.Delete(_dbPath);
+        TestLocalDb.DeleteFiles(_dbPath);
     }
 
     [Fact]
@@ -32,6 +34,15 @@ public class ServiceWiringTests : IDisposable
         var services = new ServiceCollection();
         services.AddLogging();
         services.Configure<SiteEventLogOptions>(o => o.DatabasePath = _dbPath);
+        // SiteEventLogger now takes ILocalDb, so the consolidated site database has to
+        // be in the graph for AddSiteEventLogging to resolve at all. In the host this
+        // comes from SiteServiceRegistration's AddZbLocalDb.
+        services.AddZbLocalDb(new ConfigurationBuilder()
+            .AddInMemoryCollection(new Dictionary<string, string?>
+            {
+                ["LocalDb:Path"] = _dbPath,
+            })
+            .Build());
         services.AddSiteEventLogging();
 
         _provider = services.BuildServiceProvider();
@@ -57,8 +68,9 @@ public class ServiceWiringTests : IDisposable
         // concrete SiteEventLogger. Constructing them directly must compile and work.
         var options = Options.Create(new SiteEventLogOptions { DatabasePath = _dbPath });
         using var loggerFactory = LoggerFactory.Create(_ => { });
+        using var localDb = TestLocalDb.Create(_dbPath);
         using var recorder = new SiteEventLogger(
-            options, loggerFactory.CreateLogger<SiteEventLogger>());
+            options, loggerFactory.CreateLogger<SiteEventLogger>(), localDb.Db);
 
         var purge = new EventLogPurgeService(
             recorder, options, loggerFactory.CreateLogger<EventLogPurgeService>());

tests/ZB.MOM.WW.ScadaBridge.SiteEventLogging.Tests/SiteEventLogSchemaTests.cs

@@ -0,0 +1,133 @@
+using Microsoft.Data.Sqlite;
+
+namespace ZB.MOM.WW.ScadaBridge.SiteEventLogging.Tests;
+
+/// <summary>
+/// LocalDb Phase 1 (Task 2) — the <c>site_events</c> DDL is extracted out of
+/// <see cref="SiteEventLogger"/> so the Host's <c>AddZbLocalDb</c> onReady callback can
+/// create it in the consolidated site database.
+/// <para>
+/// The extraction also carries the one deliberate schema CHANGE of Phase 1: the primary
+/// key moves from <c>INTEGER PRIMARY KEY AUTOINCREMENT</c> to a TEXT GUID. Under the
+/// library's last-writer-wins replication, two site nodes each minting autoincrement id 1
+/// would be treated as the same row and silently overwrite one another. GUID keys make
+/// the event log a pure union across the pair instead.
+/// </para>
+/// </summary>
+public class SiteEventLogSchemaTests
+{
+    private static SqliteConnection OpenConnection()
+    {
+        var conn = new SqliteConnection("Data Source=:memory:");
+        conn.Open();
+        return conn;
+    }
+
+    private static List<(string Name, string Type, bool NotNull, bool Pk)> TableInfo(
+        SqliteConnection conn, string table)
+    {
+        using var cmd = conn.CreateCommand();
+        cmd.CommandText = $"PRAGMA table_info('{table}')";
+        using var reader = cmd.ExecuteReader();
+        var cols = new List<(string, string, bool, bool)>();
+        while (reader.Read())
+        {
+            cols.Add((reader.GetString(1), reader.GetString(2), reader.GetInt32(3) == 1,
+                reader.GetInt32(5) > 0));
+        }
+
+        return cols;
+    }
+
+    [Fact]
+    public void Apply_CreatesTableWithExpectedColumns()
+    {
+        using var conn = OpenConnection();
+
+        SiteEventLogSchema.Apply(conn);
+
+        Assert.Equal(
+            ["id", "timestamp", "event_type", "severity", "instance_id", "source", "message", "details"],
+            TableInfo(conn, "site_events").Select(c => c.Name));
+    }
+
+    [Fact]
+    public void Apply_IdIsTextPrimaryKey_NotAutoincrement()
+    {
+        using var conn = OpenConnection();
+
+        SiteEventLogSchema.Apply(conn);
+
+        var pk = Assert.Single(TableInfo(conn, "site_events"), c => c.Pk);
+        Assert.Equal("id", pk.Name);
+        Assert.Equal("TEXT", pk.Type);
+
+        // AUTOINCREMENT creates the sqlite_sequence bookkeeping table. Its absence
+        // is the load-bearing assertion: ids must be application-minted GUIDs, not
+        // server-minted integers that collide across the replicating pair.
+        using var cmd = conn.CreateCommand();
+        cmd.CommandText =
+            "SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' AND name = 'sqlite_sequence'";
+        Assert.Equal(0L, Convert.ToInt64(cmd.ExecuteScalar()));
+    }
+
+    [Fact]
+    public void Apply_RejectsIntegerIdInserts()
+    {
+        using var conn = OpenConnection();
+
+        SiteEventLogSchema.Apply(conn);
+
+        // A TEXT PK with no AUTOINCREMENT means an INSERT omitting id is an error,
+        // which is what forces the writer to mint one (Task 5a).
+        using var cmd = conn.CreateCommand();
+        cmd.CommandText = """
+            INSERT INTO site_events (timestamp, event_type, severity, source, message)
+            VALUES ('2026-07-19T00:00:00Z', 'Test', 'Info', 'test', 'no id supplied')
+            """;
+        Assert.Throws<SqliteException>(() => cmd.ExecuteNonQuery());
+    }
+
+    [Fact]
+    public void Apply_HasNoBlobColumns()
+    {
+        using var conn = OpenConnection();
+
+        SiteEventLogSchema.Apply(conn);
+
+        Assert.DoesNotContain(
+            TableInfo(conn, "site_events"),
+            c => c.Type.Contains("BLOB", StringComparison.OrdinalIgnoreCase));
+    }
+
+    [Fact]
+    public void Apply_CreatesExpectedIndexes()
+    {
+        using var conn = OpenConnection();
+
+        SiteEventLogSchema.Apply(conn);
+
+        using var cmd = conn.CreateCommand();
+        cmd.CommandText =
+            "SELECT name FROM sqlite_master WHERE type = 'index' AND tbl_name = 'site_events' " +
+            "AND name NOT LIKE 'sqlite_%' ORDER BY name";
+        using var reader = cmd.ExecuteReader();
+        var indexes = new List<string>();
+        while (reader.Read()) indexes.Add(reader.GetString(0));
+
+        Assert.Equal(
+            ["idx_events_instance", "idx_events_severity", "idx_events_timestamp", "idx_events_type"],
+            indexes);
+    }
+
+    [Fact]
+    public void Apply_IsIdempotent()
+    {
+        using var conn = OpenConnection();
+
+        SiteEventLogSchema.Apply(conn);
+        SiteEventLogSchema.Apply(conn);
+
+        Assert.Equal(8, TableInfo(conn, "site_events").Count);
+    }
+}

tests/ZB.MOM.WW.ScadaBridge.SiteEventLogging.Tests/SiteEventLoggerAsyncTests.cs

@@ -13,18 +13,21 @@ public class SiteEventLoggerAsyncTests : IDisposable
 {
     private readonly SiteEventLogger _logger;
     private readonly string _dbPath;
+    private readonly TestLocalDb _localDb;
 
     public SiteEventLoggerAsyncTests()
     {
         _dbPath = Path.Combine(Path.GetTempPath(), $"test_async_{Guid.NewGuid()}.db");
         var options = Options.Create(new SiteEventLogOptions { DatabasePath = _dbPath });
-        _logger = new SiteEventLogger(options, NullLogger<SiteEventLogger>.Instance);
+        _localDb = TestLocalDb.Create(_dbPath);
+        _logger = new SiteEventLogger(options, NullLogger<SiteEventLogger>.Instance, _localDb.Db);
     }
 
     public void Dispose()
     {
         _logger.Dispose();
-        if (File.Exists(_dbPath)) File.Delete(_dbPath);
+        _localDb.Dispose();
+        TestLocalDb.DeleteFiles(_dbPath);
     }
 
     [Fact]
@@ -91,11 +94,10 @@ public class SiteEventLoggerAsyncTests : IDisposable
         // Health Monitoring can surface a logging outage.
         using var failingConnection = new SqliteConnection("Data Source=:memory:");
         failingConnection.Open();
-        var options = Options.Create(new SiteEventLogOptions
+        var failDbPath = Path.Combine(Path.GetTempPath(), $"test_fail_{Guid.NewGuid()}.db");
-        {
+        var options = Options.Create(new SiteEventLogOptions { DatabasePath = failDbPath });
-            DatabasePath = Path.Combine(Path.GetTempPath(), $"test_fail_{Guid.NewGuid()}.db")
+        using var failLocalDb = TestLocalDb.Create(failDbPath);
-        });
+        var logger = new SiteEventLogger(options, NullLogger<SiteEventLogger>.Instance, failLocalDb.Db);
-        var logger = new SiteEventLogger(options, NullLogger<SiteEventLogger>.Instance);
 
         // Drop the table so every write fails with "no such table".
         logger.WithConnection(connection =>
@@ -112,5 +114,7 @@ public class SiteEventLoggerAsyncTests : IDisposable
             "FailedWriteCount must increment when an event write fails.");
 
         logger.Dispose();
+        failLocalDb.Dispose();
+        TestLocalDb.DeleteFiles(failDbPath);
     }
 }

tests/ZB.MOM.WW.ScadaBridge.SiteEventLogging.Tests/SiteEventLoggerTests.cs

@@ -9,12 +9,14 @@ public class SiteEventLoggerTests : IDisposable
     private readonly SiteEventLogger _logger;
     private readonly SqliteConnection _verifyConnection;
     private readonly string _dbPath;
+    private readonly TestLocalDb _localDb;
 
     public SiteEventLoggerTests()
     {
         _dbPath = Path.Combine(Path.GetTempPath(), $"test_events_{Guid.NewGuid()}.db");
         var options = Options.Create(new SiteEventLogOptions { DatabasePath = _dbPath });
-        _logger = new SiteEventLogger(options, NullLogger<SiteEventLogger>.Instance);
+        _localDb = TestLocalDb.Create(_dbPath);
+        _logger = new SiteEventLogger(options, NullLogger<SiteEventLogger>.Instance, _localDb.Db);
 
         // Separate connection for verification queries
         _verifyConnection = new SqliteConnection($"Data Source={_dbPath}");
@@ -25,7 +27,8 @@ public class SiteEventLoggerTests : IDisposable
     {
         _verifyConnection.Dispose();
         _logger.Dispose();
-        if (File.Exists(_dbPath)) File.Delete(_dbPath);
+        _localDb.Dispose();
+        TestLocalDb.DeleteFiles(_dbPath);
     }
 
     [Fact]
@@ -110,20 +113,30 @@ public class SiteEventLoggerTests : IDisposable
     }
 
     [Fact]
-    public async Task LogEventAsync_MultipleEvents_AutoIncrementIds()
+    public async Task LogEventAsync_MultipleEvents_GetDistinctApplicationMintedIds()
     {
+        // Replaces LogEventAsync_MultipleEvents_AutoIncrementIds. Ids are no longer
+        // server-minted autoincrement integers: under LocalDb replication the site pair
+        // converges by last-writer-wins on the primary key, so both nodes issuing 1, 2,
+        // 3... for unrelated events would silently overwrite each other on sync.
+        //
+        // The invariant is therefore uniqueness, NOT monotonicity — ordering is carried
+        // by the timestamp column, which is what the query and purge paths sort on.
         await _logger.LogEventAsync("script", "Info", null, "S1", "First");
         await _logger.LogEventAsync("script", "Info", null, "S2", "Second");
         await _logger.LogEventAsync("script", "Info", null, "S3", "Third");
 
         using var cmd = _verifyConnection.CreateCommand();
-        cmd.CommandText = "SELECT id FROM site_events ORDER BY id";
+        cmd.CommandText = "SELECT id FROM site_events ORDER BY timestamp";
         using var reader = cmd.ExecuteReader();
-        var ids = new List<long>();
+        var ids = new List<string>();
-        while (reader.Read()) ids.Add(reader.GetInt64(0));
+        while (reader.Read()) ids.Add(reader.GetString(0));
 
         Assert.Equal(3, ids.Count);
-        Assert.True(ids[0] < ids[1] && ids[1] < ids[2]);
+        Assert.All(ids, id => Assert.False(string.IsNullOrWhiteSpace(id)));
+        Assert.Equal(3, ids.Distinct(StringComparer.Ordinal).Count());
+        Assert.All(ids, id => Assert.True(Guid.TryParseExact(id, "N", out _),
+            $"Event id '{id}' is not a GUID in 'N' format."));
     }
 
     [Fact]

tests/ZB.MOM.WW.ScadaBridge.SiteEventLogging.Tests/TestLocalDb.cs

@@ -0,0 +1,70 @@
+using Microsoft.Extensions.Configuration;
+using Microsoft.Extensions.DependencyInjection;
+using ZB.MOM.WW.LocalDb;
+
+namespace ZB.MOM.WW.ScadaBridge.SiteEventLogging.Tests;
+
+/// <summary>
+/// A real <see cref="ILocalDb"/> over a temp file, for tests that construct a
+/// <see cref="SiteEventLogger"/> directly.
+/// </summary>
+/// <remarks>
+/// <para>
+/// <see cref="SiteEventLogger"/> takes <see cref="ILocalDb"/> rather than a connection
+/// string, and there is no in-memory mode — <c>LocalDbOptions.Path</c> is a filesystem
+/// path. A real one is used rather than a stub on purpose: connections handed out by
+/// <c>ILocalDb</c> carry the <c>zb_hlc_next()</c> UDF that <c>site_events</c>' capture
+/// triggers call, so a stub returning a bare <c>SqliteConnection</c> would pass these
+/// tests while failing closed the moment the table is registered for replication.
+/// </para>
+/// <para>
+/// No <c>onReady</c> callback and no <c>RegisterReplicated</c>: the logger's own
+/// <c>InitializeSchema</c> creates the table, which is what keeps a directly-constructed
+/// logger self-sufficient. Registration is the host's job
+/// (<c>SiteLocalDbSetup.OnReady</c>).
+/// </para>
+/// </remarks>
+public sealed class TestLocalDb : IDisposable
+{
+    private readonly ServiceProvider _provider;
+
+    private TestLocalDb(ServiceProvider provider, ILocalDb db)
+    {
+        _provider = provider;
+        Db = db;
+    }
+
+    /// <summary>The live local database.</summary>
+    public ILocalDb Db { get; }
+
+    /// <summary>Opens a local database at <paramref name="databasePath"/>.</summary>
+    public static TestLocalDb Create(string databasePath)
+    {
+        var configuration = new ConfigurationBuilder()
+            .AddInMemoryCollection(new Dictionary<string, string?>
+            {
+                ["LocalDb:Path"] = databasePath,
+            })
+            .Build();
+
+        var provider = new ServiceCollection()
+            .AddZbLocalDb(configuration)
+            .BuildServiceProvider();
+
+        return new TestLocalDb(provider, provider.GetRequiredService<ILocalDb>());
+    }
+
+    /// <summary>
+    /// Deletes <paramref name="databasePath"/> and its WAL sidecars. Call only after the
+    /// owning <see cref="TestLocalDb"/> is disposed — the master connection anchors the WAL.
+    /// </summary>
+    public static void DeleteFiles(string databasePath)
+    {
+        foreach (var suffix in new[] { "", "-wal", "-shm" })
+        {
+            try { File.Delete(databasePath + suffix); } catch { /* best effort */ }
+        }
+    }
+
+    public void Dispose() => _provider.Dispose();
+}

tests/ZB.MOM.WW.ScadaBridge.SiteRuntime.Tests/Tracking/OperationTrackingSchemaTests.cs

@@ -0,0 +1,147 @@
+using Microsoft.Data.Sqlite;
+using ZB.MOM.WW.ScadaBridge.SiteRuntime.Tracking;
+
+namespace ZB.MOM.WW.ScadaBridge.SiteRuntime.Tests.Tracking;
+
+/// <summary>
+/// LocalDb Phase 1 (Task 2) — the <c>OperationTracking</c> DDL is extracted out of
+/// <see cref="OperationTrackingStore"/> so it can also be applied from the Host's
+/// <c>AddZbLocalDb</c> onReady callback, which owns table creation for the consolidated
+/// site database. These tests pin the extracted schema against the shape the store has
+/// always created: the move must be behaviour-preserving.
+/// </summary>
+public class OperationTrackingSchemaTests
+{
+    private static SqliteConnection OpenConnection()
+    {
+        var conn = new SqliteConnection("Data Source=:memory:");
+        conn.Open();
+        return conn;
+    }
+
+    private static List<(string Name, string Type, bool NotNull, bool Pk)> TableInfo(
+        SqliteConnection conn, string table)
+    {
+        using var cmd = conn.CreateCommand();
+        cmd.CommandText = $"PRAGMA table_info('{table}')";
+        using var reader = cmd.ExecuteReader();
+        var cols = new List<(string, string, bool, bool)>();
+        while (reader.Read())
+        {
+            cols.Add((reader.GetString(1), reader.GetString(2), reader.GetInt32(3) == 1,
+                reader.GetInt32(5) > 0));
+        }
+
+        return cols;
+    }
+
+    [Fact]
+    public void Apply_CreatesTableWithExpectedColumns()
+    {
+        using var conn = OpenConnection();
+
+        OperationTrackingSchema.Apply(conn);
+
+        var cols = TableInfo(conn, "OperationTracking");
+        Assert.Equal(
+            [
+                "TrackedOperationId", "Kind", "TargetSummary", "Status", "RetryCount",
+                "LastError", "HttpStatus", "CreatedAtUtc", "UpdatedAtUtc", "TerminalAtUtc",
+                "SourceInstanceId", "SourceScript", "SourceNode"
+            ],
+            cols.Select(c => c.Name));
+    }
+
+    [Fact]
+    public void Apply_UsesTextPrimaryKey()
+    {
+        using var conn = OpenConnection();
+
+        OperationTrackingSchema.Apply(conn);
+
+        // RegisterReplicated requires an explicit primary key and rejects BLOB
+        // columns; a TEXT PK is what makes this table replicate as-is.
+        var pk = Assert.Single(TableInfo(conn, "OperationTracking"), c => c.Pk);
+        Assert.Equal("TrackedOperationId", pk.Name);
+        Assert.Equal("TEXT", pk.Type);
+    }
+
+    [Fact]
+    public void Apply_HasNoBlobColumns()
+    {
+        using var conn = OpenConnection();
+
+        OperationTrackingSchema.Apply(conn);
+
+        // ILocalDb.RegisterReplicated throws on any column whose declared type
+        // contains BLOB (json_object cannot capture them).
+        Assert.DoesNotContain(
+            TableInfo(conn, "OperationTracking"),
+            c => c.Type.Contains("BLOB", StringComparison.OrdinalIgnoreCase));
+    }
+
+    [Fact]
+    public void Apply_CreatesExpectedIndexes()
+    {
+        using var conn = OpenConnection();
+
+        OperationTrackingSchema.Apply(conn);
+
+        using var cmd = conn.CreateCommand();
+        cmd.CommandText =
+            "SELECT name FROM sqlite_master WHERE type = 'index' AND tbl_name = 'OperationTracking' " +
+            "AND name NOT LIKE 'sqlite_%' ORDER BY name";
+        using var reader = cmd.ExecuteReader();
+        var indexes = new List<string>();
+        while (reader.Read()) indexes.Add(reader.GetString(0));
+
+        Assert.Equal(
+            ["IX_OperationTracking_Status_Updated", "IX_OperationTracking_UpdatedAt"],
+            indexes);
+    }
+
+    [Fact]
+    public void Apply_IsIdempotent()
+    {
+        using var conn = OpenConnection();
+
+        OperationTrackingSchema.Apply(conn);
+        OperationTrackingSchema.Apply(conn);
+
+        Assert.Equal(13, TableInfo(conn, "OperationTracking").Count);
+    }
+
+    [Fact]
+    public void Apply_AddsSourceNodeToLegacyTableMissingIt()
+    {
+        using var conn = OpenConnection();
+
+        // A tracking DB created by a pre-SourceNode build. CREATE TABLE IF NOT
+        // EXISTS will not add the column, so Apply must ALTER it in — the
+        // additive-migration behaviour the store has today must survive extraction.
+        using (var legacy = conn.CreateCommand())
+        {
+            legacy.CommandText = """
+                CREATE TABLE OperationTracking (
+                    TrackedOperationId TEXT    NOT NULL PRIMARY KEY,
+                    Kind               TEXT    NOT NULL,
+                    TargetSummary      TEXT    NULL,
+                    Status             TEXT    NOT NULL,
+                    RetryCount         INTEGER NOT NULL DEFAULT 0,
+                    LastError          TEXT    NULL,
+                    HttpStatus         INTEGER NULL,
+                    CreatedAtUtc       TEXT    NOT NULL,
+                    UpdatedAtUtc       TEXT    NOT NULL,
+                    TerminalAtUtc      TEXT    NULL,
+                    SourceInstanceId   TEXT    NULL,
+                    SourceScript       TEXT    NULL
+                );
+                """;
+            legacy.ExecuteNonQuery();
+        }
+
+        OperationTrackingSchema.Apply(conn);
+
+        Assert.Contains(TableInfo(conn, "OperationTracking"), c => c.Name == "SourceNode");
+    }
+}

tests/ZB.MOM.WW.ScadaBridge.SiteRuntime.Tests/Tracking/OperationTrackingStoreTests.cs

@@ -1,6 +1,8 @@
 using Microsoft.Data.Sqlite;
+using Microsoft.Extensions.Configuration;
+using Microsoft.Extensions.DependencyInjection;
 using Microsoft.Extensions.Logging.Abstractions;
-using Microsoft.Extensions.Options;
+using ZB.MOM.WW.LocalDb;
 using ZB.MOM.WW.ScadaBridge.Commons.Types;
 using ZB.MOM.WW.ScadaBridge.Commons.Types.Enums;
 using ZB.MOM.WW.ScadaBridge.SiteRuntime.Tracking;
@@ -9,30 +11,93 @@ namespace ZB.MOM.WW.ScadaBridge.SiteRuntime.Tests.Tracking;
 
 /// <summary>
 /// Audit Log #23 (M3 Bundle A — Task A2) — schema + behaviour tests for the
-/// site-local <see cref="OperationTrackingStore"/>. Each test uses a unique
+/// site-local <see cref="OperationTrackingStore"/>.
-/// shared-cache in-memory SQLite database so the store and the verifier share
-/// the same store without touching disk.
 /// </summary>
-public class OperationTrackingStoreTests
+/// <remarks>
+/// The store now takes <see cref="ILocalDb"/> rather than a connection string, so each
+/// test gets a real <c>ILocalDb</c> over a unique temp FILE. The previous
+/// <c>mode=memory&amp;cache=shared</c> fixture is gone and cannot come back:
+/// <c>LocalDbOptions.Path</c> is a filesystem path, and — more importantly — a raw
+/// connection to a shared-cache in-memory database would not carry the
+/// <c>zb_hlc_next()</c> UDF the capture triggers call, so testing through one would no
+/// longer resemble how the store runs in the host.
+/// <para>
+/// Verifier connections are plain <see cref="SqliteConnection"/>s over the same file.
+/// That is deliberate and safe: they only read, or write columns on an unregistered
+/// table (this fixture never calls <c>RegisterReplicated</c>), so no trigger fires and
+/// the missing UDF is never reached.
+/// </para>
+/// </remarks>
+public class OperationTrackingStoreTests : IDisposable
 {
-    private static (OperationTrackingStore store, string dataSource) CreateStore(
+    // Every ILocalDb and temp file created by this fixture, torn down together. The
-        string testName)
+    // ILocalDb holds an open master connection anchoring the WAL, so it must be
+    // disposed before the file can be deleted.
+    private readonly List<ServiceProvider> _providers = [];
+    private readonly List<string> _tempFiles = [];
+
+    public void Dispose()
     {
-        var dataSource = $"file:{testName}-{Guid.NewGuid():N}?mode=memory&cache=shared";
+        foreach (var provider in _providers)
-        var connectionString = $"Data Source={dataSource};Cache=Shared";
-        var options = new OperationTrackingOptions
         {
-            ConnectionString = connectionString,
+            try { provider.Dispose(); } catch { /* best effort */ }
-        };
-        var store = new OperationTrackingStore(
-            Options.Create(options),
-            NullLogger<OperationTrackingStore>.Instance);
-        return (store, dataSource);
         }
 
-    private static SqliteConnection OpenVerifierConnection(string dataSource)
+        foreach (var path in _tempFiles)
         {
-        var connection = new SqliteConnection($"Data Source={dataSource};Cache=Shared");
+            foreach (var suffix in new[] { "", "-wal", "-shm" })
+            {
+                try { File.Delete(path + suffix); } catch { /* best effort */ }
+            }
+        }
+
+        GC.SuppressFinalize(this);
+    }
+
+    /// <summary>Allocates a unique temp database path, registered for cleanup.</summary>
+    private string NewDatabasePath(string testName)
+    {
+        var path = Path.Combine(
+            Path.GetTempPath(), $"optracking-{testName}-{Guid.NewGuid():N}.db");
+        _tempFiles.Add(path);
+        return path;
+    }
+
+    /// <summary>
+    /// Builds a real <see cref="ILocalDb"/> over <paramref name="databasePath"/>. No
+    /// <c>onReady</c> callback and no <c>RegisterReplicated</c> — the store's own
+    /// <c>InitializeSchema</c> creates the table, which is what keeps a
+    /// directly-constructed store self-sufficient.
+    /// </summary>
+    private ILocalDb CreateLocalDb(string databasePath)
+    {
+        var configuration = new ConfigurationBuilder()
+            .AddInMemoryCollection(new Dictionary<string, string?>
+            {
+                ["LocalDb:Path"] = databasePath,
+            })
+            .Build();
+
+        var provider = new ServiceCollection()
+            .AddZbLocalDb(configuration)
+            .BuildServiceProvider();
+        _providers.Add(provider);
+
+        return provider.GetRequiredService<ILocalDb>();
+    }
+
+    private (OperationTrackingStore store, string dataSource) CreateStore(string testName)
+    {
+        var databasePath = NewDatabasePath(testName);
+        var store = new OperationTrackingStore(
+            CreateLocalDb(databasePath),
+            NullLogger<OperationTrackingStore>.Instance);
+        return (store, databasePath);
+    }
+
+    private static SqliteConnection OpenVerifierConnection(string databasePath)
+    {
+        var connection = new SqliteConnection($"Data Source={databasePath}");
         connection.Open();
         return connection;
     }
@@ -111,14 +176,19 @@ public class OperationTrackingStoreTests
             ON OperationTracking (Status, UpdatedAtUtc);
         """;
 
-    private static SqliteConnection SeedPreSourceNodeSchemaDatabase(string dataSource)
+    /// <summary>
+    /// Writes the pre-SourceNode schema into <paramref name="databasePath"/> and closes
+    /// the connection again. Unlike the old shared-cache in-memory fixture — where the
+    /// seeding connection had to stay open or the database evaporated — a file database
+    /// persists on its own, so nothing is held across the store's construction.
+    /// </summary>
+    private static void SeedPreSourceNodeSchemaDatabase(string databasePath)
     {
-        var connection = new SqliteConnection($"Data Source={dataSource};Cache=Shared");
+        using var connection = new SqliteConnection($"Data Source={databasePath}");
         connection.Open();
         using var cmd = connection.CreateCommand();
         cmd.CommandText = OldPreSourceNodeSchema;
         cmd.ExecuteNonQuery();
-        return connection;
     }
 
     private static bool ColumnExists(SqliteConnection connection, string columnName)
@@ -129,36 +199,36 @@ public class OperationTrackingStoreTests
         return Convert.ToInt32(cmd.ExecuteScalar()) > 0;
     }
 
-    private static OperationTrackingStore CreateStoreOver(string dataSource)
+    private OperationTrackingStore CreateStoreOver(string databasePath)
-    {
+        => new(CreateLocalDb(databasePath), NullLogger<OperationTrackingStore>.Instance);
-        var connectionString = $"Data Source={dataSource};Cache=Shared";
-        var options = new OperationTrackingOptions { ConnectionString = connectionString };
-        return new OperationTrackingStore(
-            Options.Create(options),
-            NullLogger<OperationTrackingStore>.Instance);
-    }
 
     [Fact]
     public async Task Initialize_adds_SourceNode_to_pre_existing_schema()
     {
-        var dataSource = $"file:{nameof(Initialize_adds_SourceNode_to_pre_existing_schema)}-{Guid.NewGuid():N}?mode=memory&cache=shared";
+        var databasePath = NewDatabasePath(nameof(Initialize_adds_SourceNode_to_pre_existing_schema));
 
-        // A pre-SourceNode deployment: tracking.db already exists with the
+        // A pre-SourceNode deployment: the tracking database already exists with
-        // 12-column schema and NO SourceNode column.
+        // the 12-column schema and NO SourceNode column.
-        using var seedConnection = SeedPreSourceNodeSchemaDatabase(dataSource);
+        SeedPreSourceNodeSchemaDatabase(databasePath);
-        Assert.True(ColumnExists(seedConnection, "SourceInstanceId"));
+        using (var seeded = OpenVerifierConnection(databasePath))
-        Assert.True(ColumnExists(seedConnection, "SourceScript"));
+        {
-        Assert.False(ColumnExists(seedConnection, "SourceNode"));
+            Assert.True(ColumnExists(seeded, "SourceInstanceId"));
+            Assert.True(ColumnExists(seeded, "SourceScript"));
+            Assert.False(ColumnExists(seeded, "SourceNode"));
+        }
 
         // Upgrade: a post-branch OperationTrackingStore opens the same database.
         // Its InitializeSchema must ALTER the missing SourceNode column in —
         // the CREATE TABLE IF NOT EXISTS alone is a no-op against the existing
         // table.
-        await using (var store = CreateStoreOver(dataSource))
+        await using (var store = CreateStoreOver(databasePath))
+        {
+            using (var verifier = OpenVerifierConnection(databasePath))
             {
                 Assert.True(
-                ColumnExists(seedConnection, "SourceNode"),
+                    ColumnExists(verifier, "SourceNode"),
                     "OperationTrackingStore must ALTER the SourceNode column into a pre-existing OperationTracking table.");
+            }
 
             // A RecordEnqueueAsync binding $sourceNode must now succeed; without
             // the ALTER it would fail with "no such column: SourceNode".
@@ -178,9 +248,10 @@ public class OperationTrackingStoreTests
 
         // Idempotency: a second store over the now-upgraded DB must not error
         // (the probe sees SourceNode already present and skips the ALTER).
-        await using (var storeAgain = CreateStoreOver(dataSource))
+        await using (var storeAgain = CreateStoreOver(databasePath))
         {
-            Assert.True(ColumnExists(seedConnection, "SourceNode"));
+            using var verifier = OpenVerifierConnection(databasePath);
+            Assert.True(ColumnExists(verifier, "SourceNode"));
         }
     }