dohertj2/lmxopcua
1
0
Fork 0
Code Issues 14 Pull Requests Actions Packages Projects Releases Wiki Activity

historian(sqlserver): Part 11 modified values (ReadModified + audit of HistoryUpdate) #668

New Issue
Closed
opened 2026-10-09 15:26:04 -04:00 by dohertj2 · 1 comment
dohertj2 commented 2026-10-09 15:26:04 -04:00
Owner
Copy Link
Copy Source

OPC UA HistoryUpdate on the SQL Server backend overwrites or deletes rows in place. Part 11 "modified values" are not kept: there is no audit history of replaced or deleted values, and ReadModified (HistoryRead with IsReadModified=true) is unsupported.

Scope: a hist.SampleModified table recording the prior value, the user/principal, the update type and the time. HistoryUpdate writes to it in the same transaction, and ReadModified serves it.

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

OPC UA HistoryUpdate on the SQL Server backend overwrites or deletes rows in place. Part 11 "modified values" are not kept: there is no audit history of replaced or deleted values, and `ReadModified` (HistoryRead with `IsReadModified=true`) is unsupported. **Scope:** a `hist.SampleModified` table recording the prior value, the user/principal, the update type and the time. HistoryUpdate writes to it in the same transaction, and `ReadModified` serves it. Claude-Session: https://claude.ai/code/session_01Ef2ozxS7QPanGT8uoATbTe
dohertj2 commented 2026-10-11 10:17:58 -04:00
Author
Owner
Copy Link
Copy Source

Implemented in 062086f9, review fix-ups 57deaeba (both merged to master).

  • SqlServer: schema v2 (002_sample_modified.sql, RequiredVersion = 2; v1 upgraded in place under the bootstrap applock). Every HistoryUpdate writes its trail row via OUTPUT … INTO hist.SampleModified in the same statement as the change — a failed update leaves no trail row (atomicity test forces the trail insert to fail). Retention (#667) purges SampleModified and never writes trail rows.
  • QuestDB: append-only {prefix}sample_modified (same TTL), written before the change; a crash between the two leaves an orphan audit row (documented; a non-crash failure now logs a Warning naming tag + range).
  • HistoryUpdateContext(Principal, ApiKeyId) threaded into the updater — the recorded user is the session's API-key principal (or anonymous); only the public key id, never secret material.
  • IsReadModified=true served by ReadModifiedAsync → HistoryModifiedData + ModificationInfo per value, with continuation points (cursor (SourceTime, ModifiedId), bound to node + read kind), reverse and open-ended reads. Authorized by the existing HistoryRead gate. Gateway/Null ⇒ BadHistoryOperationUnsupported.
  • Client.CLI: historyread --modified.

⚠️ Upgrade note: with AutoCreateSchema=false, run 002_sample_modified.sql (SqlServer) / create sample_modified (QuestDB) before upgrading, or every historian call fails. Upgrade both pair nodes together (a not-yet-upgraded peer writes HistoryUpdates without trail rows).

Live gate (docker-dev, gate 3) — PASS on SqlServer and QuestDB: fresh schema v2 + in-place v1→v2 upgrade (seeded v1 sample survived); insert/replace/delete-at-time as an API key then --modified returned exactly the 4 expected rows with correct priors and the key's principal; 18-entry paging forward and reverse at cap 5 (5/5/5/3, ties in modification order, no dup/skip) on both centrals; ACL on: a key without HistoryRead and anonymous get BadUserAccessDenied; Gateway ⇒ BadHistoryOperationUnsupported; plain raw read shows the replaced value.

Follow-ups filed (QuestDB TTL vs far-future timestamps, v2-required-for-all-traffic posture, cross-node QuestDB ordering/priors, IsDeleteModified).

Implemented in `062086f9`, review fix-ups `57deaeba` (both merged to master). - **SqlServer:** schema v2 (`002_sample_modified.sql`, `RequiredVersion = 2`; v1 upgraded in place under the bootstrap applock). Every HistoryUpdate writes its trail row via `OUTPUT … INTO hist.SampleModified` **in the same statement** as the change — a failed update leaves no trail row (atomicity test forces the trail insert to fail). Retention (#667) purges `SampleModified` and never writes trail rows. - **QuestDB:** append-only `{prefix}sample_modified` (same TTL), written before the change; a crash between the two leaves an orphan audit row (documented; a non-crash failure now logs a Warning naming tag + range). - `HistoryUpdateContext(Principal, ApiKeyId)` threaded into the updater — the recorded user is the session's API-key principal (or `anonymous`); only the public key id, never secret material. - `IsReadModified=true` served by `ReadModifiedAsync` → `HistoryModifiedData` + `ModificationInfo` per value, with continuation points (cursor `(SourceTime, ModifiedId)`, bound to node + read kind), reverse and open-ended reads. Authorized by the existing HistoryRead gate. Gateway/Null ⇒ `BadHistoryOperationUnsupported`. - Client.CLI: `historyread --modified`. ⚠️ **Upgrade note:** with `AutoCreateSchema=false`, run `002_sample_modified.sql` (SqlServer) / create `sample_modified` (QuestDB) **before** upgrading, or every historian call fails. Upgrade both pair nodes together (a not-yet-upgraded peer writes HistoryUpdates without trail rows). **Live gate (docker-dev, gate 3) — PASS on SqlServer and QuestDB:** fresh schema v2 + in-place v1→v2 upgrade (seeded v1 sample survived); insert/replace/delete-at-time as an API key then `--modified` returned exactly the 4 expected rows with correct priors and the key's principal; 18-entry paging forward and reverse at cap 5 (5/5/5/3, ties in modification order, no dup/skip) on both centrals; ACL on: a key without HistoryRead and anonymous get `BadUserAccessDenied`; Gateway ⇒ `BadHistoryOperationUnsupported`; plain raw read shows the replaced value. Follow-ups filed (QuestDB TTL vs far-future timestamps, v2-required-for-all-traffic posture, cross-node QuestDB ordering/priors, IsDeleteModified).
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
auto-managed
queue management
 cross-driver
queue management
 driver/abcip
queue management
 driver/ablegacy
queue management
 driver/focas
queue management
 driver/mqtt
MQTT / Sparkplug B broker-subscribe driver
 driver/opcuaclient
queue management
 driver/s7
queue management
 driver/twincat
queue management
 phase/1
queue management
 phase/2
queue management
 phase/3
queue management
 phase/4
queue management
 phase/5
queue management
 phase/6
queue management
 queue/blocked
queue management
 queue/done
queue management
 queue/failed
queue management
 queue/in-progress
queue management
 queue/queued
queue management
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
dohertj2 (dohertj2)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: dohertj2/lmxopcua#668
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?