lmxopcua/docs/plans/2026-07-15-sql-poll-driver-design.md

# SQL poll driver (`Sql`) — executable implementation design

> **Status:** design, build-ready. Author: driver design sweep, 2026-07-15.
> **Supersedes/derives from research:** [`docs/research/drivers/sql-poll.md`](../research/drivers/sql-poll.md).
> **Grounding read:** `CLAUDE.md` (Equipment-kind driver + `TagConfig.FullName`);
> `src/Drivers/ZB.MOM.WW.OtOpcUa.Driver.Modbus/` (+ `.Contracts`) as the poll-driver template;
> `src/Core/ZB.MOM.WW.OtOpcUa.Core.Abstractions/` capability seams
> (`IDriver`, `ITagDiscovery`, `IReadable`, `ISubscribable`, `IHostConnectivityProbe`,
> `PollGroupEngine`, `EquipmentTagRefResolver`, `DataValueSnapshot`, `DriverAttributeInfo`);
> [`docs/plans/2026-05-28-driver-browsers-design.md`](2026-05-28-driver-browsers-design.md)
> (bespoke `IBrowseSession` house style);
> [`docs/plans/2026-07-15-universal-discovery-browser-design.md`](2026-07-15-universal-discovery-browser-design.md)
> (universal browser — see §4.0 reconciliation).

The driver type string is **`Sql`** (short, consistent with `Modbus`/`S7`/`Galaxy`). This doc
uses `Sql` throughout; the research report's working name `SqlPoll` maps 1:1.

---

## 0. TL;DR — decisions locked for the build

| Question | Decision |
|---|---|
| **Provider ship-first** | **SQL Server** via `Microsoft.Data.SqlClient` (already at `6.1.1` in `Directory.Packages.props:53`) behind an ADO.NET `DbProviderFactory` + a `SqlProvider` enum + an `ISqlDialect` seam. **Zero new NuGet deps for P1.** Postgres (`Npgsql`) + ODBC P2; MySQL/Oracle later. |
| **Browse** | **Bespoke schema-walk `IBrowseSession`** in a `Driver.Sql.Browser` project (`INFORMATION_SCHEMA`, dialect-abstracted). The **universal Discover-backed browser does NOT fit** — see §4.0. `ITagDiscovery.SupportsOnlineDiscovery` stays **`false`**. |
| **Write in v1** | **NO — read-only v1.** `IWritable` deferred to P4 as an opt-in, triple-gated, parameterized-UPSERT mode (design in §3.4). |
| **Capabilities v1** | `IDriver`, `ITagDiscovery` (authored replay only), `IReadable`, `ISubscribable` (poll via `PollGroupEngine`), `IHostConnectivityProbe`. |
| **Top 2 risks** | (1) **SQL injection** — values are always bound `DbParameter`s; identifiers only from `INFORMATION_SCHEMA`-validated + dialect-quoted names, never string-concatenated tag input. (2) **Secrets** — connection strings resolved from env/secret store via `connectionStringRef`, never committed/logged (mirror `ServerHistorian__ApiKey`). |
| **Effort** | Small–Medium. P1 ≈ Modbus minus wire-codec plus the dialect seam + result-set slicing; most infra (`PollGroupEngine`, `EquipmentTagRefResolver<TDef>`, health state machine, factory shape) is reused. |

---

## 1. Motivation + scope

Many plants expose process/production data only as **SQL rows** — MES/ERP staging tables, LIMS,
custom app databases, historian summary tables. Today OtOpcUa has no way to surface those under the
unified Equipment address space; an operator has to stand up a bespoke bridge. The `Sql` driver
closes that gap: it polls arbitrary SQL tables/views on an interval and publishes selected
columns/rows as ordinary OPC UA variable nodes — the same authoring flow (equipment page → Tags tab
→ address picker) as Modbus or S7.

**Scope v1: read-only.** Writing back into MES/ERP staging is operationally risky (a stray
`UPDATE`/`INSERT` can corrupt a batch record, double-post a transaction, or fire a trigger with side
effects). Read-only removes an entire risk class from the first ship. An opt-in write mode is
designed in §3.4 for a later phase but is **not** built in v1.

**Out of scope (v1):** `IWritable`, `IAlarmSource`, `IHistoryProvider` (alarms/history aren't
expressible from a plain poll query). SQL-poll data can still be *historized* by the server via the
standard `TagConfig.isHistorized` flag — that's the server historian path, not a driver capability.

Full research + verdicts: [`docs/research/drivers/sql-poll.md`](../research/drivers/sql-poll.md).

---

## 2. Project layout

Three new projects, mirroring the Modbus split (runtime / contracts / browser), plus test projects.
All `net10.0`, `Nullable`/`ImplicitUsings` enabled, `TreatWarningsAsErrors`, matching the repo
`.csproj` conventions.

| Path | Role |
|---|---|
| `src/Drivers/ZB.MOM.WW.OtOpcUa.Driver.Sql.Contracts/` | Options DTOs, `SqlProvider` enum, tag-model records, `SqlEquipmentTagParser`. **Zero transport deps** (refs only `Core.Abstractions`) so AdminUI + browser can reference it without dragging `Microsoft.Data.SqlClient`. |
| `src/Drivers/ZB.MOM.WW.OtOpcUa.Driver.Sql/` | `SqlDriver` (runtime capabilities), `ISqlDialect` + `SqlServerDialect`, `SqlPollReader`, factory extensions. **Owns the `Microsoft.Data.SqlClient` package ref.** |
| `src/Drivers/ZB.MOM.WW.OtOpcUa.Driver.Sql.Browser/` | `SqlDriverBrowser : IDriverBrowser`, `SqlBrowseSession : IBrowseSession` (schema walk). Refs `Commons` + `Sql.Contracts` + **`Driver.Sql`** (for `ISqlDialect`/`SqlServerDialect` — the dialect's catalog SQL *is* the browse engine, so it must be shared, not duplicated; `Microsoft.Data.SqlClient` comes transitively). This runtime-project ref is a deliberate deviation from `OpcUaClient.Browser` (which refs only its Contracts and owns the OPC UA client packages itself): the browser still opens its own transient connection. The resulting **transitive `Microsoft.Data.SqlClient` reference on AdminUI is reviewed and accepted** — AdminUI already uses SqlClient for ConfigDb, so this adds no new package to its closure. Recorded here deliberately so nobody "fixes" the deviation later without this context. |
| `tests/Drivers/ZB.MOM.WW.OtOpcUa.Driver.Sql.Tests/` | Unit suite (SQLite fixture, §9). |
| `tests/Drivers/ZB.MOM.WW.OtOpcUa.Driver.Sql.IntegrationTests/` | Env-gated integration suite (central SQL Server, §9). |
| `tests/Drivers/ZB.MOM.WW.OtOpcUa.Driver.Sql.Browser.Tests/` | Browser unit tests (SQLite `PRAGMA` dialect). |

Add all six to `ZB.MOM.WW.OtOpcUa.slnx`.

### 2.1 `Driver.Sql.csproj` (sketch)

```xml
<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>net10.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
    <TreatWarningsAsErrors>true</TreatWarningsAsErrors>
    <GenerateDocumentationFile>true</GenerateDocumentationFile>
    <NoWarn>$(NoWarn);CS1591</NoWarn>
    <RootNamespace>ZB.MOM.WW.OtOpcUa.Driver.Sql</RootNamespace>
  </PropertyGroup>
  <ItemGroup>
    <ProjectReference Include="..\..\Core\ZB.MOM.WW.OtOpcUa.Core.Abstractions\ZB.MOM.WW.OtOpcUa.Core.Abstractions.csproj"/>
    <ProjectReference Include="..\..\Core\ZB.MOM.WW.OtOpcUa.Core\ZB.MOM.WW.OtOpcUa.Core.csproj"/>
    <ProjectReference Include="..\ZB.MOM.WW.OtOpcUa.Driver.Sql.Contracts\ZB.MOM.WW.OtOpcUa.Driver.Sql.Contracts.csproj"/>
  </ItemGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.Data.SqlClient" />        <!-- version from Directory.Packages.props -->
    <PackageReference Include="Microsoft.Extensions.Logging.Abstractions" />
  </ItemGroup>
  <ItemGroup>
    <InternalsVisibleTo Include="ZB.MOM.WW.OtOpcUa.Driver.Sql.Tests"/>
  </ItemGroup>
</Project>
```

The capability seams + `PollGroupEngine` + `EquipmentTagRefResolver` all live in
`Core.Abstractions`, but the `Core.csproj` reference is **also** needed (Modbus carries it too):
the factory extensions (§7) take `DriverFactoryRegistry`, which lives in
`Core/Hosting/DriverFactoryRegistry.cs` (`ZB.MOM.WW.OtOpcUa.Core.Hosting`). The
SQLite unit-test project pulls `Microsoft.Data.Sqlite` (already `10.0.7` in the graph) — no product
dependency on SQLite.

### 2.2 Provider strategy — `DbProviderFactory` behind `ISqlDialect`

Use ADO.NET's provider-agnostic base types (`System.Data.Common`: `DbConnection`, `DbCommand`,
`DbParameter`, `DbDataReader`) obtained through a `DbProviderFactory`. A small `SqlProvider` enum
(not open-ended invariant strings) maps to a dialect that owns the two things the base types **can't**
abstract: identifier quoting and the metadata-catalog SQL.

```csharp
// Driver.Sql.Contracts
public enum SqlProvider { SqlServer, Postgres, MySql, Odbc, Oracle }

// Driver.Sql (public — the .Browser project consumes it; only implementations touch provider packages)
public interface ISqlDialect
{
    SqlProvider Provider { get; }
    DbProviderFactory Factory { get; }               // SqlClientFactory.Instance (P1)
    string QuoteIdentifier(string ident);            // [x] / "x" / `x`  (rejects/escapes embedded quote chars)
    string LivenessSql { get; }                      // "SELECT 1"  (Oracle: "SELECT 1 FROM DUAL")
    string ListSchemasSql { get; }                   // browse Root
    string ListTablesSql { get; }                    // browse Expand(schema)  — parameterized by @schema
    string ListColumnsSql { get; }                   // browse Expand(table)/Attributes — parameterized by @schema,@table
    DriverDataType MapColumnType(string sqlDataType); // INFORMATION_SCHEMA DATA_TYPE → DriverDataType
}
```

**Ship-first: `SqlServerDialect` only** (`Microsoft.Data.SqlClient`, `SqlClientFactory.Instance`,
`[bracket]` quoting, `INFORMATION_SCHEMA` catalog). P2 adds `PostgresDialect` (`Npgsql`) + a SQLite
dialect for the browser tests (`PRAGMA table_info`, `sqlite_schema`) + `OdbcDialect`. `Npgsql` /
`System.Data.Odbc` / `Oracle.ManagedDataAccess.Core` are each a **new** `PackageVersion` gated behind
their phase — the P1 SQL-Server slice adds none. In .NET there is no `machine.config` provider
registry: factories come from each provider's static `Instance` singleton
(`SqlClientFactory.Instance`, `NpgsqlFactory.Instance`, `OdbcFactory.Instance`).

---

## 3. Capability mapping

`SqlDriver : IDriver, ITagDiscovery, IReadable, ISubscribable, IHostConnectivityProbe`.

### 3.1 `InitializeAsync` — open + validate

1. Deserialize `SqlDriverConfigDto` from `driverConfigJson` (via `SqlDriverFactoryExtensions`,
   mirroring `ModbusDriverFactoryExtensions.CreateInstance`), using a shared `JsonSerializerOptions`
   with **`JsonStringEnumConverter`** (§6 — the enum-serialization trap) and
   `UnmappedMemberHandling.Skip`.
2. **Resolve the connection string** from `connectionStringRef` against the process config/env
   (§8.2) — never from the committed JSON.
3. Select `ISqlDialect` from `options.Provider` (P1: only `SqlServer` is constructible; others throw
   a clean "provider not available in this build").
4. **Validate**: open one `DbConnection` from `dialect.Factory`, run `dialect.LivenessSql` under
   `CommandTimeout` + a linked CTS (§8.3), dispose. Success → `DriverState.Healthy`; failure →
   `DriverState.Faulted` with `LastError` (same state machine as `ModbusDriver.InitializeAsync`).
5. Build the `PollGroupEngine` (reader delegate = §3.2) with `minInterval = 100ms`, an `onError`
   sink routing to the health surface, and a `backoffCap` (Modbus/S7 precedent, 05/STAB-8).

**Pooling:** rely on ADO.NET built-in pooling (keyed by exact connection-string text). Do **not**
hold a long-lived connection — **open-use-dispose per poll pass** (`await using var conn = …`) so the
pool manages lifetime and a dropped/restarted server recovers transparently. Expose `Max Pool Size`
etc. only through the connection string.

**`ReinitializeAsync`** = teardown + init (Modbus pattern); no live sockets to preserve.

**`GetMemoryFootprint`** ≈ the cached parsed-tag map + last-value dictionaries (small). No symbol
tables to flush; `FlushOptionalCachesAsync` is a no-op returning `Task.CompletedTask`.

### 3.2 `IReadable` / `ISubscribable` — one query per group, not per tag

The efficiency story is **batching by query-group**. A naive "one SELECT per tag" hammers the DB;
instead the driver coalesces tags that share a source query, issues **one round-trip per group per
poll**, and slices the result back to per-tag snapshots — structurally identical to
`ModbusDriver.ReadCoalescedAsync` (group → single PDU → slice back).

```csharp
public async Task<IReadOnlyList<DataValueSnapshot>> ReadAsync(
    IReadOnlyList<string> fullReferences, CancellationToken ct)
{
    // 1. Resolve each ref to a SqlTagDefinition via EquipmentTagRefResolver<SqlTagDefinition>.
    // 2. Group by GroupKey (model-dependent, §3.6).
    // 3. Per group: open connection, build ONE parameterized command, ExecuteReaderAsync(ct),
    //    index rows, map each tag's cell → DataValueSnapshot.
    // 4. Reassemble snapshots in INPUT order (PollGroupEngine contract: N in → N out).
    //    Per-tag failure = a Bad-coded snapshot, NOT an exception (IReadable contract).
    //    Whole call throws only if the driver itself is unreachable (→ engine backoff).
}
```

`ISubscribable.SubscribeAsync` delegates to `PollGroupEngine.Subscribe(refs, publishingInterval)`
with `ReadAsync` as the reader delegate — the engine owns the loop, the 100 ms interval floor,
capped-exponential failure backoff, and change-diffing; `OnDataChange` is raised from the engine's
`onChange`. `UnsubscribeAsync` → `engine.Unsubscribe(handle)`.

**Poll interval** is driver-level (`defaultPollInterval`, default 5 s) with optional per-tag/per-group
override; the OPC UA subscription's requested publishing interval is clamped up to the group's floor
by `PollGroupEngine`.

**Per-call deadline (mandatory — R2-01 frozen-peer lesson).** Every `DbCommand` sets
`CommandTimeout` (= `commandTimeout` option, seconds) **and** runs `ExecuteReaderAsync(linkedCt)`
where the linked CTS is bounded by `operationTimeout` (wall-clock). A frozen DB (partition, lock
wait, slow query) surfaces `BadTimeout` per group and lets the poll loop back off — it must **not**
wedge the poll thread. Both are required: the linked token gives real client-side cancellation;
`CommandTimeout` is the server-side backstop.

### 3.3 `ITagDiscovery` — authored replay only (NOT schema enumeration)

`DiscoverAsync` materializes exactly the authored tags — the `tags` array in options **plus** the
equipment tags contributed at deploy time (whose reference is their raw `TagConfig` JSON). It does
**not** enumerate the DB schema: schema enumeration is a browse-time concern (§4). Each authored tag
becomes an `IAddressSpaceBuilder.Variable(browseName, displayName, DriverAttributeInfo)` with
`FullName` = the tag reference string, `DriverDataType` from the tag's declared/inferred type,
`SecurityClass = ViewOnly` (read-only v1). `RediscoverPolicy => Once`.
**`SupportsOnlineDiscovery => false`** (default member — see §4.0).

### 3.4 `IWritable` — verdict: NOT in v1

**v1 does not implement `IWritable`.** Rationale in §1. The P4 opt-in design: a tag may carry a
`write` block describing a **parameterized** statement (never string-concatenated):

```jsonc
"write": {
  "mode": "Update",                                    // Update | Insert | Upsert | StoredProc
  "sql": "UPDATE dbo.Setpoints SET value=@value, ts=@ts WHERE tag_name=@key",
  "keyParam": "@key", "keyValue": "Line1.Speed", "valueParam": "@value"
}
```

`WriteAsync` binds the OPC UA write value to `@value` (typed via the tag's declared type) and executes
the tag's parameterized command. **Triple-gated:** (a) the node's `WriteOperate` role (standard
`NodeWriteRouter`), (b) a driver-level `allowWrites` master switch (default `false`), (c)
`TagConfig.writable`. Not auto-retried unless the tag is `WriteIdempotent`
(`DriverAttributeInfo.WriteIdempotent`): an `UPDATE … WHERE key=` set-value is idempotent; an
`INSERT` / counter `UPDATE x=x+1` is not. Because a failed inbound device write reverts the node to
its prior value (write-outcome self-correction, master `1d797c1c`), SQL writes — unlike Galaxy's
fire-and-forget — *can* surface a genuine failure, which is the honest behaviour.

### 3.5 `IHostConnectivityProbe`

One logical host (the DB server host:port from the connection string). `GetHostStatuses()` returns a
single `HostConnectivityStatus(server, HostState.Running|Stopped|Faulted, lastChangedUtc)` derived
from the last poll/liveness outcome; `OnHostStatusChanged` fires on Running↔Stopped transitions. This
lets the Core scope Bad-quality fan-out to the driver's subtree on a DB outage (same pattern the
Galaxy driver uses for Platform/AppEngine). Optional but cheap — recommended for v1.

### 3.6 Tag→value mapping models

Three models; **(a) and (b) ship in P1**, (c) is P3.

**(a) Key-value / EAV table** *(primary).* A `(tagname, value, timestamp)` table; a tag names a
`table`, `keyColumn`+`keyValue`, `valueColumn`, optional `timestampColumn`.
**GroupKey = `(table, keyColumn, valueColumn, timestampColumn)`.** All such tags fold into one
`SELECT <keyColumn>, <valueColumn>, <timestampColumn> FROM <table> WHERE <keyColumn> IN (@k0,@k1,…)`
per poll; index rows by key, slice back. Values in the `IN` list are **bound parameters**, one per
key — never interpolated.

**Query contract — one row per key (v1).** The `WHERE <keyColumn> IN (…)` poll assumes the authored
source yields **exactly one row per key**. Point the tag at a current-values table, or — for
append-only staging/history tables that hold many rows per key — at a **view or query the operator
writes** that reduces to latest-per-key (`GROUP BY key` + `MAX(timestamp)`, or
`ROW_NUMBER() OVER (PARTITION BY <keyColumn> ORDER BY <timestampColumn> DESC) = 1`); that
operator-authored latest-per-key view is the **supported workaround** for append-only sources. If a
poll nonetheless returns multiple rows for a key, the driver deterministically takes the **last**
occurrence in reader order and emits a **rate-limited warning** log naming the offending key — the
value stays usable, but the config is telling you the source violates the contract.

**(b) Wide row** *(cheap).* One "latest status" row, many columns → many tags. A tag names a `table`,
a `columnName`, and a `rowSelector` (`{whereColumn, whereValue}` or `{topByTimestamp: <col>}`).
**GroupKey = `(table, rowSelector)`.** All wide-row tags on the same row fold into one
`SELECT <col_a>, <col_b>, … FROM <table> WHERE <whereColumn>=@w` (or `ORDER BY <col> DESC` +
`TOP 1`/`LIMIT 1` via dialect); map each selected column to its tag.

**(c) Named parameterized SELECT** *(escape hatch, P3).* Named query defs at driver level
(`queries: { "q1": { sql, params } }`); a tag references a query by name + a projection (`rowKey`
selecting the row, `column` selecting the value). **GroupKey = the named query.** Covers joins/views/
computed projections. The SQL is authored by a `ConfigEditor`-privileged operator and stored
server-side — it is *config*, not runtime user input — but it still binds only parameters.

**Group execution shape (recommended):** for each distinct GroupKey the driver holds a compiled
`SqlQueryPlan { GroupKey, SqlText, ParameterBinder, RowIndexer, IReadOnlyList<SqlTagDefinition> members }`
built lazily and cached; a poll pass executes each plan's command once. Cap concurrent group
execution (per-driver semaphore, Modbus/S7 precedent) so a driver spanning many tables never opens an
unbounded number of pooled connections.

### 3.7 Type mapping (SQL → OPC UA) + timestamps

Map the column's provider metadata (`DbColumn.DataType` from `DbDataReader.GetColumnSchema()`, or the
`INFORMATION_SCHEMA` `DATA_TYPE`) to `DriverDataType` (`Core.Abstractions/DriverDataType.cs`), which
the address-space layer maps to OPC UA built-ins.

| SQL type (family) | CLR (`DbDataReader`) | `DriverDataType` |
|---|---|---|
| `bit` / `boolean` | `bool` | `Boolean` |
| `tinyint` / `smallint` | `byte`/`short` | `Int16` |
| `int` / `integer` | `int` | `Int32` |
| `bigint` | `long` | `Int64` |
| `real` / `float(24)` | `float` | `Float32` |
| `float` / `double` / `decimal` / `numeric` / `money` | `double`/`decimal`→`double` | `Float64` |
| `char` / `varchar` / `nvarchar` / `text` | `string` | `String` |
| `date` / `datetime` / `datetime2` / `timestamptz` | `DateTime`/`DateTimeOffset` | `DateTime` |
| `uniqueidentifier` / `uuid` | `Guid` | `String` |

`decimal`/`numeric` collapse to `Float64` (v1) with a documented precision-loss caveat. A tag may
**override** the inferred type explicitly (`"type": "Int32"`) — the operator sometimes knows better
than column metadata (e.g. a `varchar` column holding a number); the driver then coerces the read
cell to the declared type. `NULL` → `DataValueSnapshot { Value = null }` with `Good`/`Uncertain` per
a `nullIsBad` option (default: treat as `Uncertain`, not `Bad`). A key **entirely absent** from the
result set (row deleted) is distinct from a NULL cell: that tag's snapshot goes **`BadNoData`** —
`nullIsBad` governs only a present row whose value cell is NULL.

**Source timestamp:** if a tag declares `timestampColumn`, that column's value becomes
`DataValueSnapshot.SourceTimestampUtc`; otherwise the poll-read wall-clock is used for both source and
server timestamps (the Modbus behaviour). A source timestamp is what makes SQL-poll data honest about
staleness — strongly recommend authoring one wherever the staging table has it.

---

## 4. Bespoke schema browser (`Driver.Sql.Browser`)

### 4.0 Reconciliation with the universal Discover-backed browser — **honest statement**

The [universal browser design](2026-07-15-universal-discovery-browser-design.md) proposes one generic
`DiscoveryDriverBrowser` that runs a driver's `ITagDiscovery.DiscoverAsync` against a capturing
builder and re-presents the streamed tree. **That mechanism does not fit the `Sql` driver, and here is
exactly why:**

> The universal browser only ever shows what `DiscoverAsync` streams. For `Sql`, `DiscoverAsync` is
> **authored/config-driven** (§3.3) — it replays the *already-authored* tags, it does **not** enumerate
> the database schema. A universal Discover-backed browser would therefore replay the tags the operator
> already typed in, which is useless for *discovering* new ones. Schema enumeration
> (databases/schemas → tables/views → columns) is a **browse-time** concern that lives in
> `INFORMATION_SCHEMA`, not in the runtime read path.

The universal design's own capability gate encodes this distinction (`SupportsOnlineDiscovery`:
"does `DiscoverAsync` enumerate from the backend, or merely replay pre-declared tags?"). `Sql` is in
the **replay** bucket alongside Modbus/S7. So:

- **`Sql` leaves `ITagDiscovery.SupportsOnlineDiscovery = false`** (the default member) — the universal
  browser's `CanBrowse` returns false for `Sql`, and it never offers the universal fallback.
- **`Sql` ships a bespoke `IBrowseSession`** (this section) that walks the *live schema catalog*,
  which is the thing an operator actually wants to browse. A registered bespoke `IDriverBrowser` for
  `DriverType="Sql"` takes precedence over the universal fallback by construction
  (`BrowserSessionService` resolves bespoke-first).

This is the same reason OpcUaClient/Galaxy keep bespoke browsers: the browse tree is a *different
shape* from the discovered tag set. For `Sql` it's even starker — the discovered set is a strict
subset (only what's authored), while the schema catalog is the full browsable universe.

### 4.1 The schema walk

Relational DBs are self-describing. The picker walks the catalog three levels deep, each level a
dialect-abstracted catalog query:

- **`RootAsync`** → schemas (Folder nodes). SQL Server: `SELECT DISTINCT TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES` (optionally databases first for multi-DB servers). SQLite (test dialect): the single main DB.
- **`ExpandAsync(schemaNodeId)`** → tables + views in that schema (Folder nodes): `… TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=@schema`.
- **`ExpandAsync(tableNodeId)`** → columns (Leaf nodes): `… COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@schema AND TABLE_NAME=@table`.
- **`AttributesAsync(columnNodeId)`** → column data type + nullability side-panel (mirrors Galaxy's two-stage attribute pick): `AttributeInfo(Name: column, DriverDataType: dialect.MapColumnType(dataType).ToString(), IsArray: false, SecurityClass: "ViewOnly")`.

`@schema`/`@table` are **bound parameters** in the catalog queries — even here, no interpolation.

### 4.2 Portability caveat — the catalog query is dialect-specific

`INFORMATION_SCHEMA` is the SQL-92 standard catalog, supported by **SQL Server, PostgreSQL, and
MySQL/MariaDB** — so the queries above are portable across those three. But **two backends don't
implement it:**

- **Oracle** uses `ALL_TABLES` / `ALL_TAB_COLUMNS` (`USER_`/`DBA_` variants).
- **SQLite** uses `sqlite_schema`/`sqlite_master` + `PRAGMA table_info(<table>)`.

So the catalog SQL **belongs on `ISqlDialect`** (`ListSchemasSql`/`ListTablesSql`/`ListColumnsSql` +
`MapColumnType`), not hardcoded. P1 `SqlServerDialect` uses `INFORMATION_SCHEMA`; the SQLite test
dialect uses `PRAGMA`; Postgres/MySQL reuse `INFORMATION_SCHEMA`; Oracle (later) uses
`ALL_TAB_COLUMNS`. (Alternative for the *column* level only: `DbDataReader.GetColumnSchema()` on a
`SELECT * … WHERE 1=0` `SchemaOnly` command — but the table list still needs catalog SQL, so the
dialect method stays regardless.)

### 4.3 Picked-column → `TagConfig`

On column pick the browser emits `BrowseNode.NodeId` encoding `schema.table|column`; the picker body
composes the `TagConfig` blob (§5.2/5.3): DisplayName defaults to `table.column`, the inferred
`DriverDataType` from `AttributesAsync` prefills `type`, and the operator chooses the model (key-value
vs wide-row) + fills the key/row selector. `SecurityClass = ViewOnly` in read-only v1.

### 4.4 Project + registration (mirrors `OpcUaClient.Browser`)

| Path | Purpose |
|---|---|
| `Driver.Sql.Browser/SqlDriverBrowser.cs` | `IDriverBrowser`, `DriverType => "Sql"`; `OpenAsync(configJson, ct)` deserializes options and resolves the connection string from the `connectionStringRef` in the form JSON via a **direct env-var read** (`Sql__ConnectionStrings__<ref>`) **in the AdminUI process** — the same resolution the driver performs at Initialize (§8.2). If the ref doesn't resolve, the open **fails with an actionable message naming the exact missing env var** (e.g. `Connection string ref 'MesStaging' not found — set env var Sql__ConnectionStrings__MesStaging on this admin node`). The picker form **additionally accepts a pasted literal connection string** for ad-hoc browse — used for that browse session only, **never persisted** into the driver config (same trust boundary as `TestDriverConnect` + the OpcUaClient browser — transient, never cached). Opens a `DbConnection`, returns a `SqlBrowseSession`. |
| `Driver.Sql.Browser/SqlBrowseSession.cs` | `IBrowseSession`; `RootAsync`/`ExpandAsync`/`AttributesAsync` over the dialect catalog queries; a `SemaphoreSlim _gate` (one ADO.NET connection is not concurrent). Per-call work is bounded by the AdminUI's existing 20 s per-call timeout. |
| AdminUI DI (`EndpointRouteBuilderExtensions.cs`, beside `:49-50`) | `services.AddSingleton<IDriverBrowser, SqlDriverBrowser>();` |
| `AdminUI/Uns/TagEditors/TagConfigEditorMap.cs` | `["Sql"] = typeof(Components.Shared.Uns.TagEditors.SqlTagConfigEditor)` (§6). |
| A new `SqlAddressPickerBody.razor` picker body | Browse tree (reuse `DriverBrowseTree.razor`) + attribute side-panel + model/selector fields; gated by the existing `DriverOperator` policy; manual entry retained. |

The browser reuses the AdminUI `BrowseSessionRegistry` + reaper + 2-min idle TTL + per-call timeout
unchanged. **The universal browser DI line is NOT added for `Sql`** — its `CanBrowse` is false anyway,
and the bespoke registration wins.

**Deployment requirement — env parity across split nodes.** Because the browser resolves
`connectionStringRef` in the **AdminUI process**, on split-role deployments the **admin nodes must
carry the same `Sql__ConnectionStrings__<ref>` env vars as the driver nodes** for every ref the
operator wants browseable (the same split-role lesson as driver probes on admin nodes). A ref present
only on driver nodes still *polls* fine at runtime but cannot be *browsed*; when that happens the
browser-open failure names the exact missing env var so the operator knows precisely what to add to
the admin node's environment — or falls back to pasting a literal connection string into the picker
for a one-off session-only browse.

---

## 5. TagConfig + driver-config JSON

### 5.1 Driver config (the `DriverConfig` blob)

```jsonc
{
  "provider": "SqlServer",
  // Connection string is resolved at Initialize from an env/secret ref — NOT stored here.
  "connectionStringRef": "MesStaging",             // => env Sql__ConnectionStrings__MesStaging
  "defaultPollInterval": "00:00:05",
  "operationTimeout": "00:00:15",                  // per-query wall-clock deadline (→ BadTimeout); MUST be > commandTimeout (§8.3)
  "commandTimeout": "00:00:10",                    // server-side CommandTimeout backstop (seconds granularity)
  "maxConcurrentGroups": 4,                        // cap on concurrent group queries (pool guard)
  "nullIsBad": false,                              // NULL cell → Uncertain (default) vs Bad
  "allowWrites": false,                            // master write kill-switch (v1: always false)
  "probe": { "enabled": true, "interval": "00:00:10" },

  // Optional pre-declared tag table (analogous to Modbus "Tags"); equipment tags may also
  // contribute their address as raw TagConfig JSON at deploy time.
  "tags": [ /* see 5.2 / 5.3 */ ],

  // Optional named queries for the arbitrary-SELECT model (P3).
  "queries": {
    "activeBatch": {
      "sql": "SELECT line, speed, temp, ts FROM dbo.LineStatus WHERE active=@active",
      "params": { "@active": true }
    }
  }
}
```

### 5.2 Per-tag `TagConfig` — key-value model

```jsonc
{
  "driver": "Sql",
  "model": "KeyValue",
  "table": "dbo.TagValues",
  "keyColumn": "tag_name",
  "keyValue": "Line1.Speed",
  "valueColumn": "num_value",
  "timestampColumn": "sample_ts",
  "type": "Float64",                 // optional explicit override of inferred type
  "writable": false
}
```

GroupKey for batching = `("dbo.TagValues", "tag_name", "num_value", "sample_ts")`; all such tags fold
into one `… WHERE tag_name IN (@k0,@k1,…)` per poll. The `table` must yield one row per `keyValue` —
for append-only sources, author a latest-per-key view and point `table` at it (§3.6 query contract).

### 5.3 Per-tag `TagConfig` — wide-row model

```jsonc
{
  "driver": "Sql",
  "model": "WideRow",
  "table": "dbo.LatestStatus",
  "columnName": "oven_temp",
  "rowSelector": { "whereColumn": "station_id", "whereValue": 7 },
  // or "rowSelector": { "topByTimestamp": "sample_ts" }   // newest row
  "type": "Float64",
  "writable": false
}
```

GroupKey = `("dbo.LatestStatus", rowSelector)`; every wide-row tag on the same row folds into one
`SELECT oven_temp, <other cols>, … FROM dbo.LatestStatus WHERE station_id=@id`.

### 5.4 Per-tag `TagConfig` — named-query model (P3)

```jsonc
{
  "driver": "Sql",
  "model": "Query",
  "query": "activeBatch",
  "rowKey": { "column": "line", "value": "Line1" },   // which row this tag reads
  "column": "speed",                                   // which projected column is the value
  "timestampColumn": "ts"
}
```

### 5.5 Equipment-tag parser

`SqlEquipmentTagParser.TryParse(reference, out SqlTagDefinition def)` (in `.Contracts`) mirrors
`ModbusEquipmentTagParser`: recognizes the blob by a leading `{` + a `"driver":"Sql"` (or a `"model"`
discriminator), reads the model-specific fields, uses **`TagConfigJson.TryReadEnumStrict`** for the
`model`/`type` enums (a present-but-invalid enum rejects the tag → `BadNodeIdUnknown`, per R2-11), and
maps to a transient `SqlTagDefinition` whose `Name == reference` so the value the driver publishes
keys the forward router correctly. `EquipmentTagRefResolver<SqlTagDefinition>` bridges the two
authoring models (an authored `tags`-table entry by name **or** an equipment tag whose reference is
its raw `TagConfig` JSON, parsed once and cached) — same as Modbus.

---

## 6. Typed editor + validator + the enum-serialization trap

Add `["Sql"] = typeof(…SqlTagConfigEditor)` to `TagConfigEditorMap` and a matching entry to
`TagConfigValidator`. The validation layer also enforces the driver-level timeout-ordering rule
(§8.3): `operationTimeout` must be strictly greater than `commandTimeout`, rejected (or warned on)
at authoring time. The editor is a thin Razor shell over a pure `SqlTagConfigModel`
(`FromJson`/`ToJson`/`Validate`, preserves unknown keys) — copy the Modbus template under
`Components/Shared/Uns/TagEditors/` + `Uns/TagEditors/`. The model exposes: `Model` (KeyValue/WideRow/
Query), `Table`, `KeyColumn`/`KeyValue`/`ValueColumn`/`TimestampColumn` (key-value), `ColumnName` +
`RowSelector` (wide-row), `Query`/`RowKey`/`Column` (named), and `Type` (`DriverDataType?` override).
Without a typed editor `Sql` would fall to the generic raw-JSON textarea (like Galaxy today) — the
typed editor is worth building because the model discriminator + selector shape is error-prone by hand.

**The `JsonStringEnumConverter` enum-serialization trap (MEMORY: systemic).** Every AdminUI driver
page that serializes an enum **numerically** while the factory DTO is **string-typed** produces a
config that faults the driver at parse time (proven e2e for S7 + Modbus). **All** enum fields here —
`provider` (`SqlProvider`), `model`, `type` (`DriverDataType`) — MUST round-trip as **strings** on
**both** sides:

- The AdminUI editor/model `ToJson` and the driver-config page serializer attach
  `JsonStringEnumConverter` (mirror the OpcUaClient page).
- The factory (`SqlDriverFactoryExtensions`) and `SqlEquipmentTagParser` deserialize with the same
  string converter (and `TryReadEnumStrict` for tag enums).
- The `TestDriverConnect` probe uses the same options.

Add a unit test asserting `provider`/`model`/`type` serialize as `"SqlServer"`/`"KeyValue"`/`"Float64"`
(quoted strings), not `0`/`1`/`8`.

---

## 7. Factory + registration

`SqlDriverFactoryExtensions` mirrors `ModbusDriverFactoryExtensions`:

```csharp
public static class SqlDriverFactoryExtensions
{
    public const string DriverTypeName = "Sql";

    public static void Register(DriverFactoryRegistry registry, ILoggerFactory? loggerFactory = null)
    {
        ArgumentNullException.ThrowIfNull(registry);
        registry.Register(DriverTypeName, (id, json) => CreateInstance(id, json, loggerFactory));
    }

    public static SqlDriver CreateInstance(string driverInstanceId, string driverConfigJson, ILoggerFactory? loggerFactory)
    { /* deserialize DTO (string enum converter), validate connectionStringRef present, build options+dialect */ }
}
```

Wire-up (one line each):

- **Factory:** add `Driver.Sql.SqlDriverFactoryExtensions.Register(registry, loggerFactory);` to
  `src/Server/ZB.MOM.WW.OtOpcUa.Host/Drivers/DriverFactoryBootstrap.cs` `Register(...)` (beside the
  eight existing driver `Register` lines).
- **Tier:** declared on the factory registration itself — the optional `tier` parameter of
  `DriverFactoryRegistry.Register(driverType, factory, tier)`
  (`Core/Hosting/DriverFactoryRegistry.cs`), which the resilience pipeline reads via
  `DriverFactoryRegistry.GetTier`. The default `DriverTier.A` (managed SDK, in-process) is correct
  for `Sql` — no explicit argument needed, same as every existing driver. (Do **not** hunt for a
  `DriverTypeMetadata`/`DriverTypeRegistry` registration site: that registry in `Core.Abstractions`
  currently has no production population site — no existing driver registers metadata there.)
- **Probe:** if a lightweight `SqlProbe : IDriverProbe` is added for the AdminUI "Test Connect"
  button, register it in `AddOtOpcUaDriverProbes` via `TryAddEnumerable` (a bare `SELECT 1` liveness
  check — recommended, cheap, and admin-node registration matters per the split-role gotcha).
- **Browser:** the `AddSingleton<IDriverBrowser, SqlDriverBrowser>()` line (§4.4).

`ShouldStub()` in `DriverInstanceActor` is platform/role-driven and needs no `Sql`-specific change —
SQL Server client works cross-platform, so `Sql` runs on macOS dev too (unlike Galaxy).

---

## 8. Resilience / security / timeout

### 8.1 SQL injection (critical, risk #1)

- **Every value is a bound `DbParameter`.** The `IN (@k0,@k1,…)` key list, `WHERE col=@w` selectors,
  named-query `params`, and (P4) write values are all parameters. **Zero** runtime tag input is ever
  concatenated into SQL text.
- **Identifiers** (schema/table/column) that must appear as SQL text come **only** from
  `INFORMATION_SCHEMA`-validated names or a browse-derived allow-list, and are emitted through
  `dialect.QuoteIdentifier` (which escapes/rejects embedded quote characters). A table/column string
  in a `TagConfig` is validated against the live catalog (or an allow-list) before it's ever quoted
  into text; an unknown identifier rejects the tag (→ `BadNodeIdUnknown`) rather than executing.
- **Treat any code path that builds SQL by string-concatenating a tag field as a defect** — enforce
  with a review checklist item + a unit test that feeds a malicious `keyValue`/`table`
  (`'; DROP TABLE …`) and asserts it either binds harmlessly (value) or is rejected (identifier),
  never executes.
- The named-query model stores operator-authored SQL as *config* (`ConfigEditor`-gated), and even
  there only parameters bind runtime values.

### 8.2 Secrets (critical, risk #2)

Mirror `ServerHistorian:ApiKey` (env `ServerHistorian__ApiKey`, appsettings carries only a reminder
comment) and the env-overridable ConfigDb connection string:

- `DriverConfig` JSON stores a **`connectionStringRef`** (a logical name), **not** the connection
  string. The driver resolves it at `InitializeAsync` by reading the environment variable directly
  (`Sql__ConnectionStrings__<ref>` — the flat env-var spelling of the config key), so the secret
  lives in the process environment / secret store — never in the config DB row or any committed
  file. Direct env read (not `IConfiguration`) is deliberate: the factory registry materializes
  drivers via a static `(id, json)` closure (Modbus pattern) with no `IConfiguration` in reach, so
  this keeps the factory shape unchanged.
- If an inline `connectionString` is ever permitted (dev convenience only), the AdminUI **redacts** it
  in display/logging and flags it dev-only.
- **Never log the resolved connection string.** Log only provider + server host + database name.
- Prefer integrated/managed auth where the estate supports it (`Integrated Security=true` / Azure AD /
  Kerberos) so no password transits config at all.
- The `.Browser` path resolves `connectionStringRef` via the **same direct env read, in the AdminUI
  process** (§4.4) — which is why split-role deployments need **env parity**: admin nodes carry the
  same `Sql__ConnectionStrings__<ref>` vars as driver nodes for any browseable ref, and an unresolved
  ref fails the browse open with a message naming the exact missing env var. A pasted ad-hoc literal
  connection string travels only over the authenticated Blazor circuit (same boundary as every other
  driver browser + `TestDriverConnect`), is used for that browse session only, and is never persisted
  into the driver config or logged. Either way the browser builds one transient connection and
  releases it — **no `_lastConfigJson` cached field anywhere.**

### 8.3 Timeout / frozen-peer (high, risk #3)

Per §3.2: `CommandTimeout` (server-side backstop) **and** `ExecuteReaderAsync(linkedCt)` bounded by
`operationTimeout` (client-side, real cancellation) on **every** command — the SQL analogue of the
R2-01 S7 blackhole finding. A frozen DB surfaces `BadTimeout` per group + degrades the driver + backs
off the poll loop; it must never wedge a poll thread. Asserted by the blackhole live-gate (§9).

**Timeout-ordering rule (authoring validation).** `operationTimeout` **must be strictly greater than**
`commandTimeout` — the defaults (15 s > 10 s) encode this deliberately. The driver-page /
`TagConfigValidator`-level check **rejects** (or at minimum warns loudly on)
`operationTimeout <= commandTimeout`: inverting the order means the client-side abort always fires
first and **masks the server-side backstop** — every server-side timeout would surface as a generic
client cancellation instead of the `CommandTimeout` failure it actually is, hiding the diagnostic
that distinguishes "slow query/lock wait" from "driver gave up".

### 8.4 Connection pooling / exhaustion (medium)

Open-use-dispose per poll (`await using` on both `DbConnection` and `DbDataReader` — never leak a
reader). Cap concurrent group execution via `maxConcurrentGroups` (a `SemaphoreSlim`, Modbus/S7
precedent) so a driver spanning many tables never opens an unbounded number of pooled connections.
Pool sizing is expressed only through the connection string (`Max Pool Size`).

### 8.5 Provider portability (medium)

`INFORMATION_SCHEMA` covers SQL Server/Postgres/MySQL but not Oracle/SQLite; parameter markers and
identifier quoting differ. **All of it lives behind `ISqlDialect`** — no dialect assumption leaks into
the driver core (the reader, grouping, and poll engine are provider-agnostic).

### 8.6 Type / precision (low-medium)

`decimal`→`Float64` precision loss; `NULL` semantics (`nullIsBad`); `datetime` vs `datetimeoffset`
timezone handling (normalize to UTC in `SourceTimestampUtc`). Documented + per-tag `type` override.

---

## 9. Test fixtures

- **SQLite unit fixture (primary).** `Microsoft.Data.Sqlite` (already `10.0.7` in-graph, no
  container). Seed a `(tag_name, num_value, sample_ts)` key-value table and a wide-row `LatestStatus`
  table in-process. Covers read/subscribe mapping, type mapping, group-batch slicing, the poll-engine
  wiring, `nullIsBad`, and the browser over `PRAGMA table_info` (SQLite dialect). Fast, offline,
  CI-safe on macOS. Requires a `SqliteDialect` (test-scoped is fine, or ship it as the P2 SQLite
  browser dialect). Note: SQLite's dynamic typing means the dialect's `MapColumnType` maps declared
  affinity, not runtime type — keep the seed columns explicitly typed.
- **Central SQL Server integration fixture.** `10.100.0.35,14330` (always-on per `CLAUDE.md`). A
  seeded `SqlPollFixture` database with the same two sample tables validates the real
  `Microsoft.Data.SqlClient` path, `INFORMATION_SCHEMA` browse, `CommandTimeout`/cancellation, and
  pooling. **Env-gated** (`SQL_TEST_ENDPOINT` / a full `Sql__ConnectionStrings__Fixture`) so it skips
  cleanly offline, like the other `*.IntegrationTests`. Deploy the seed via the docker rig; label the
  stack `project=lmxopcua`.
- **Blackhole / timeout live-gate (most important integration test).** Mirror R2-01 S7: `docker pause`
  a SQL Server container mid-poll and assert the read surfaces `BadTimeout`, the driver degrades, and
  the poll loop backs off — **not** a wedged thread. This gate runs against a **dedicated disposable
  `mssql` container** (its own compose stack under `tests/.../Docker/`, `project=lmxopcua` label) —
  **never** pause the shared central SQL Server on `,14330`, which also hosts `ConfigDb` for the whole
  rig. This is the single highest-value integration test given the frozen-peer risk class.
- **Injection regression test.** Feed a malicious `keyValue`/`table` and assert bind-harmless (value)
  or reject (identifier), never execute (§8.1).
- **Postgres/ODBC fixtures** land with P2/P3 (a `postgres:16` container for Npgsql; an ODBC DSN
  against the same SQL Server).

---

## 10. Phasing + effort

1. **P1 — SQL Server read-only, key-value + wide-row.** `.Contracts` (options DTO, `SqlProvider` enum,
   tag models, `SqlEquipmentTagParser`); `SqlDriver` (`IDriver`/`ITagDiscovery`/`IReadable`/
   `ISubscribable` via `PollGroupEngine` + `IHostConnectivityProbe`); `SqlServerDialect`; grouping +
   result-set slicing; factory + type-metadata + probe registration; `SqlTagConfigEditor` + validator
   (+ the enum-serialization test); SQLite unit fixture; env-gated SQL-Server integration fixture +
   blackhole live-gate + injection test. **No new NuGet deps.**
2. **P2 — Bespoke schema browser.** `Driver.Sql.Browser` (`SqlDriverBrowser`/`SqlBrowseSession` over
   `INFORMATION_SCHEMA` + a SQLite `PRAGMA` dialect for tests), `SqlAddressPickerBody.razor` + tree +
   attribute side-panel, DI wire-up, column→`TagConfig` commit. Near-mechanical clone of
   `OpcUaClient.Browser`. **Small.**
3. **P3 — Named-query model + PostgreSQL + ODBC.** `PostgresDialect`/`OdbcDialect`; add `Npgsql` +
   `System.Data.Odbc` `PackageVersion`s; named-query grouping; Postgres container fixture.
4. **P4 (optional) — Write mode.** `IWritable` with per-tag parameterized UPSERT/StoredProc,
   triple-gated (`WriteOperate` + `allowWrites` + `TagConfig.writable`), `WriteIdempotent` retry
   policy (write-failure revert already provided by the runtime).
5. **P5 (demand-driven) — MySQL/MariaDB (`MySqlConnector`) + native Oracle (`ALL_TAB_COLUMNS`
   dialect).**

**Effort:** P1 **Medium** (Modbus minus wire-codec, plus dialect seam + slicing; poll engine,
resolver, health machine, factory shape all reused). P2 **Small**. The provider/dialect long tail
(P3+) is where portability effort lives — keeping it behind `ISqlDialect` from day one is what makes
the P1 SQL-Server slice cheap and the rest additive.

---

## Sources / grounding

- Research report: [`docs/research/drivers/sql-poll.md`](../research/drivers/sql-poll.md).
- In-repo seams: `Core.Abstractions/{IDriver,ITagDiscovery,IReadable,ISubscribable,IHostConnectivityProbe,PollGroupEngine,EquipmentTagRefResolver,DataValueSnapshot,DriverAttributeInfo,DriverDataType,DriverHealth,TagConfigJson}.cs`.
- Templates: `Driver.Modbus/{ModbusDriver,ModbusDriverFactoryExtensions}.cs`, `Driver.Modbus.Contracts/ModbusEquipmentTagParser.cs`, `Driver.OpcUaClient.Browser/*`.
- Wire-up sites: `Host/Drivers/DriverFactoryBootstrap.cs` (`Register` + `AddOtOpcUaDriverProbes`), `AdminUI/EndpointRouteBuilderExtensions.cs:49-50` (browser DI), `AdminUI/Uns/TagEditors/{TagConfigEditorMap,TagConfigValidator}.cs`.
- Packages: `Directory.Packages.props:53-54` (`Microsoft.Data.SqlClient 6.1.1`, `Microsoft.Data.Sqlite 10.0.7`).
- Browse house style: [`docs/plans/2026-05-28-driver-browsers-design.md`](2026-05-28-driver-browsers-design.md); universal browser reconciliation: [`docs/plans/2026-07-15-universal-discovery-browser-design.md`](2026-07-15-universal-discovery-browser-design.md).
```