dohertj2/ScadaBridge
1
0
Fork 0
Code Issues 5 Pull Requests Actions Packages Projects Releases Wiki Activity

deploy-gate: hash-chain enablement chains the whole backlog - owner decision 'no backlog chaining' (#107) is not implementable on main #139

New Issue
Closed
opened 2026-11-13 02:47:00 -05:00 by dohertj2 · 3 comments
dohertj2 commented 2026-11-13 02:47:00 -05:00
Owner
Copy Link
Copy Source

Found preparing the QA vq03/vq04 upgrade pack for #107 (2026-11-13).

Owner decision (#107, 2026-11-13): "No backlog chaining: only rows written after enablement are chained."

What main does today (3a59c3f6): AuditLog:HashChain:Enabled=true chains every unchained row, oldest month first. AuditLogHashChain picks the next row with SELECT TOP (1) OccurredAtUtc FROM dbo.AuditLog WHERE ChainSeq IS NULL ORDER BY OccurredAtUtc (ConfigurationDatabase/Maintenance/AuditLogHashChain.cs:102). AuditLogHashChainOptions only has Enabled, IntervalSeconds, BatchSize, MaxBatchesPerTick and HeadExport, so nothing limits the chain to rows written after enablement. The options' own remarks say "Enabling it on an existing database chains the historical backlog oldest month first".

So with today's binaries the decision can't be carried out. Flipping Enabled on DEV vd03 (owner: "now, after the SourceNode backfill") or on QA (after the #107 window) chains the whole backlog: QA has 3,441 rows from 2026-05-29 on, and DEV is similar. The chained rows' SourceNode is then frozen. That undoes the decision, and the decision can't be reversed afterwards without a restore.

Gate: don't set AuditLog:HashChain:Enabled on DEV or QA until one of these is done:

  • (a) Add a cutoff, for example AuditLog:HashChain:ChainFromUtc (absolute) or a "chain from first enablement" anchor stored in AuditLogChainHead. Rows with OccurredAtUtc before the cutoff are never chained. verify-chain and verify-heads must then treat pre-cutoff rows as out of scope instead of UnchainedRows, and the filtered IX_AuditLog_Unchained index has to stop carrying them for ever. Decide what happens to late site rows whose OccurredAtUtc is before the cutoff but that arrive after it (probably: chain them, because they were written after enablement; the key would then be IngestedAtUtc, not OccurredAtUtc).
  • (b) The owner revises the decision to accept backlog chaining.

The QA migration window itself is not blocked: the chain stays off in that window, as decided.

Pack: docs/deployment/deploy-prep/2026-11-13-qa-vq03-vq04/README.md §7.

Found preparing the QA vq03/vq04 upgrade pack for #107 (2026-11-13). **Owner decision (#107, 2026-11-13):** "No backlog chaining: only rows written after enablement are chained." **What main does today (`3a59c3f6`):** `AuditLog:HashChain:Enabled=true` chains *every* unchained row, oldest month first. `AuditLogHashChain` picks the next row with `SELECT TOP (1) OccurredAtUtc FROM dbo.AuditLog WHERE ChainSeq IS NULL ORDER BY OccurredAtUtc` (`ConfigurationDatabase/Maintenance/AuditLogHashChain.cs:102`). `AuditLogHashChainOptions` only has `Enabled`, `IntervalSeconds`, `BatchSize`, `MaxBatchesPerTick` and `HeadExport`, so nothing limits the chain to rows written after enablement. The options' own remarks say "Enabling it on an existing database chains the historical backlog oldest month first". So with today's binaries the decision can't be carried out. Flipping `Enabled` on DEV vd03 (owner: "now, after the SourceNode backfill") or on QA (after the #107 window) chains the whole backlog: QA has 3,441 rows from 2026-05-29 on, and DEV is similar. The chained rows' `SourceNode` is then frozen. That undoes the decision, and the decision can't be reversed afterwards without a restore. **Gate:** don't set `AuditLog:HashChain:Enabled` on DEV or QA until one of these is done: - (a) Add a cutoff, for example `AuditLog:HashChain:ChainFromUtc` (absolute) or a "chain from first enablement" anchor stored in `AuditLogChainHead`. Rows with `OccurredAtUtc` before the cutoff are never chained. `verify-chain` and `verify-heads` must then treat pre-cutoff rows as out of scope instead of `UnchainedRows`, and the filtered `IX_AuditLog_Unchained` index has to stop carrying them for ever. Decide what happens to late site rows whose `OccurredAtUtc` is before the cutoff but that arrive after it (probably: chain them, because they were written after enablement; the key would then be `IngestedAtUtc`, not `OccurredAtUtc`). - (b) The owner revises the decision to accept backlog chaining. The QA migration window itself is **not** blocked: the chain stays off in that window, as decided. Pack: `docs/deployment/deploy-prep/2026-11-13-qa-vq03-vq04/README.md` §7.
dohertj2 added the type:deploy-gatepriority:p2 labels 2026-11-13 02:47:01 -05:00
dohertj2 commented 2026-11-13 03:16:24 -05:00
Author
Owner
Copy Link
Copy Source

Fixed on main at 52da02a7 (docs follow-up 3badd1f9). No migration.

Design

  • Where the cutoff lives: in the database, not config. On the first chaining pass on a pristine database (no chain head and no chained row), the chainer writes one chain-start anchor row in dbo.AuditLogChainTombstone at (cutoff month, ChainSeq 0). PurgedAtUtc holds the cutoff, which is the DB's SYSUTCDATETIME(). RowHash = SHA-256("ScadaBridge.AuditLog.ChainCutoff.v1" || YYYY-MM || ticks), and EventId is the fixed value 5cadab1d-c4a1-4c0f-a000-000000000139. The row is written once, inside the chaining transaction under the exclusive sp_getapplock. Both centrals therefore read the same instant regardless of node clocks or config, and it survives restarts. A config timestamp could drift between the two nodes, so I didn't use one. Real links start at 1, so slot 0 is "the link before the first". The existing table and grants cover it: the maintenance login writes it through scadabridge_audit_purger's INSERT. This is pinned by a test.
  • Chaining: only rows with OccurredAtUtc >= cutoff are chained. The anchor hash is the genesis of the cutoff month (in place of 32 zero bytes), which binds the cutoff into that month's chain and into its exported head.
  • Setting: AuditLog:HashChain:ChainBacklog, default false, which is the owner decision. true restores chain-everything, but it is only read on the very first enablement. Once an anchor or any chained row exists, the decision is fixed. A legacy chain (heads, no anchor) keeps chaining everything as before. I kept the option because it is the only way to opt into full history coverage, and because it keeps legacy DBs and the existing tests on the old path.
  • Late rows: the cutoff is keyed on OccurredAtUtc (the partition key, indexed). IngestedAtUtc is a non-persisted JSON column that must not be filtered on. A site row dated before the cutoff that arrives after enablement stays out of scope. It is bounded by the site's store-and-forward backlog at that moment and is reported as such. The filtered IX_AuditLog_Unchained keeps the backlog rows until retention purges them. The chaining read seeks >= cutoff, so the cost is storage only.

Verifier and purges

  • verify-chain now splits unchained rows into UnchainedRows (in scope) and PreCutoffRows (backlog, never a break), plus ChainFromUtc. The CLI table prints Chain from and Pre-cutoff rows. Months before the cutoff are NotChained with their rows counted as pre-cutoff.
  • Partition switch-out deletes only ChainSeq > 0 tombstones, so the anchor survives. The head store tombstone count, head export and verify-heads ignore it, so there is no CountMismatch or MissingLink. Per-channel purge across the cutoff tombstones only the chained rows. Archive verify takes the anchor from its own tombstone file (or from the DB) and classifies pre-cutoff rows the same way.

Attack analysis

  • A chained row re-dated before the cutoff with its link kept is reported as the new break ChainedBeforeCutoff. That holds even if the attacker re-hashes every later link. With the link also cleared, it is MissingRow (or HeadMismatch at the tail). Moved into an earlier month, the source month reports MissingRow and the target month ChainedBeforeCutoff.
  • Moving the cutoff: editing PurgedAtUtc, filing the anchor in another month, or adding a second anchor gives the new CutoffAnchorInvalid, reported in every month. Re-hashing the anchor to a later cutoff gives ChainedBeforeCutoff. Deleting the anchor gives HashMismatch at ChainSeq 1.
  • Known limits (documented): pre-cutoff rows carry no tamper evidence, which is the decision. A backdated row forged before the cutoff looks like a genuine backlog row. A full rewrite of the anchor, the cutoff month's chain and its head is caught only by the off-box head export, as with any full-chain rewrite.

Evidence

  • New AuditLogHashChainCutoffTests (7, MSSQL): chain+verify with a cutoff (backlog untouched, Intact, pre-cutoff counts); tamper edit, delete and tail still caught; the eligibility attack in 3 variants; anchor tampering in 5 variants with a positive control; purge across the cutoff (per-channel, heads and export Matches, archive and switch-out, anchor kept, archive verifies, cutoff still honoured afterwards); both nodes agree (two concurrent chainers on the real DB clock give exactly one anchor inside the race window, and a later ChainBacklog=true node can't override it); ChainBacklog and legacy keep chaining everything; the maintenance login can write the anchor.
  • 6 new verifier unit tests, plus service, CLI and management-wire tests.
  • Mutation check: 9 of 9 killed. Mutations: chainer ignores cutoff (old behaviour), no ChainedBeforeCutoff check, verifier genesis ignores anchor, old unchained count, old switch-out SQL deletes anchor, old head-store count, anchor hash unchecked, anchor written on a non-pristine DB, chainer genesis ignores anchor.
  • Suites: AuditLog 483, ConfigurationDatabase 551 (MSSQL-backed run locally, 0 skipped), ManagementService 794, CLI 613, Host 724. All passed. Release build of ZB.MOM.WW.ScadaBridge.slnx: 0 warnings, 0 errors (also after rebasing on #138).

Deploy impact: no migration, so nothing joins the QA window's script. The enablement needs a build that contains 52da02a7. QA pack 3a59c3f6 and DEV vd03 424db155 would both still chain the backlog. The QA pack §7 enablement steps are updated (keep ChainBacklog unset, plus SQL checks for the anchor and for 0 pre-cutoff chained rows). DEV at migration 64 needs only a binary bump before Enabled=true. docs/requirements/Component-AuditLog.md has been updated. Closing.

Fixed on `main` at **`52da02a7`** (docs follow-up `3badd1f9`). **No migration.** **Design** - **Where the cutoff lives:** in the database, not config. On the first chaining pass on a pristine database (no chain head and no chained row), the chainer writes one **chain-start anchor** row in `dbo.AuditLogChainTombstone` at `(cutoff month, ChainSeq 0)`. `PurgedAtUtc` holds the cutoff, which is the DB's `SYSUTCDATETIME()`. `RowHash` = `SHA-256("ScadaBridge.AuditLog.ChainCutoff.v1" || YYYY-MM || ticks)`, and `EventId` is the fixed value `5cadab1d-c4a1-4c0f-a000-000000000139`. The row is written once, inside the chaining transaction under the exclusive `sp_getapplock`. Both centrals therefore read the same instant regardless of node clocks or config, and it survives restarts. A config timestamp could drift between the two nodes, so I didn't use one. Real links start at 1, so slot 0 is "the link before the first". The existing table and grants cover it: the maintenance login writes it through `scadabridge_audit_purger`'s INSERT. This is pinned by a test. - **Chaining:** only rows with `OccurredAtUtc >= cutoff` are chained. The anchor hash is the **genesis of the cutoff month** (in place of 32 zero bytes), which binds the cutoff into that month's chain and into its exported head. - **Setting:** `AuditLog:HashChain:ChainBacklog`, **default `false`**, which is the owner decision. `true` restores chain-everything, but it is only read on the very first enablement. Once an anchor or any chained row exists, the decision is fixed. A legacy chain (heads, no anchor) keeps chaining everything as before. I kept the option because it is the only way to opt into full history coverage, and because it keeps legacy DBs and the existing tests on the old path. - **Late rows:** the cutoff is keyed on `OccurredAtUtc` (the partition key, indexed). `IngestedAtUtc` is a non-persisted JSON column that must not be filtered on. A site row dated before the cutoff that arrives after enablement stays out of scope. It is bounded by the site's store-and-forward backlog at that moment and is reported as such. The filtered `IX_AuditLog_Unchained` keeps the backlog rows until retention purges them. The chaining read seeks `>= cutoff`, so the cost is storage only. **Verifier and purges** - `verify-chain` now splits unchained rows into `UnchainedRows` (in scope) and `PreCutoffRows` (backlog, never a break), plus `ChainFromUtc`. The CLI table prints `Chain from` and `Pre-cutoff rows`. Months before the cutoff are `NotChained` with their rows counted as pre-cutoff. - Partition switch-out deletes only `ChainSeq > 0` tombstones, so **the anchor survives**. The head store tombstone count, head export and `verify-heads` ignore it, so there is no `CountMismatch` or `MissingLink`. Per-channel purge across the cutoff tombstones only the chained rows. Archive verify takes the anchor from its own tombstone file (or from the DB) and classifies pre-cutoff rows the same way. **Attack analysis** - A chained row re-dated before the cutoff with its link kept is reported as the new break **`ChainedBeforeCutoff`**. That holds even if the attacker re-hashes every later link. With the link also cleared, it is `MissingRow` (or `HeadMismatch` at the tail). Moved into an earlier month, the source month reports `MissingRow` and the target month `ChainedBeforeCutoff`. - Moving the cutoff: editing `PurgedAtUtc`, filing the anchor in another month, or adding a second anchor gives the new **`CutoffAnchorInvalid`**, reported in every month. Re-hashing the anchor to a later cutoff gives `ChainedBeforeCutoff`. Deleting the anchor gives `HashMismatch` at ChainSeq 1. - **Known limits (documented):** pre-cutoff rows carry no tamper evidence, which is the decision. A backdated row forged *before* the cutoff looks like a genuine backlog row. A full rewrite of the anchor, the cutoff month's chain and its head is caught only by the off-box head export, as with any full-chain rewrite. **Evidence** - New `AuditLogHashChainCutoffTests` (7, MSSQL): chain+verify with a cutoff (backlog untouched, Intact, pre-cutoff counts); tamper edit, delete and tail still caught; the eligibility attack in 3 variants; anchor tampering in 5 variants with a positive control; purge across the cutoff (per-channel, heads and export Matches, archive and switch-out, anchor kept, archive verifies, cutoff still honoured afterwards); **both nodes agree** (two concurrent chainers on the real DB clock give exactly one anchor inside the race window, and a later `ChainBacklog=true` node can't override it); `ChainBacklog` and legacy keep chaining everything; the maintenance login can write the anchor. - 6 new verifier unit tests, plus service, CLI and management-wire tests. - **Mutation check: 9 of 9 killed.** Mutations: chainer ignores cutoff (old behaviour), no ChainedBeforeCutoff check, verifier genesis ignores anchor, old unchained count, old switch-out SQL deletes anchor, old head-store count, anchor hash unchecked, anchor written on a non-pristine DB, chainer genesis ignores anchor. - Suites: AuditLog 483, ConfigurationDatabase 551 (MSSQL-backed run locally, 0 skipped), ManagementService 794, CLI 613, Host 724. All passed. Release build of `ZB.MOM.WW.ScadaBridge.slnx`: 0 warnings, 0 errors (also after rebasing on #138). **Deploy impact:** no migration, so nothing joins the QA window's script. **The enablement needs a build that contains `52da02a7`.** QA pack `3a59c3f6` and DEV vd03 `424db155` would both still chain the backlog. The QA pack §7 enablement steps are updated (keep `ChainBacklog` unset, plus SQL checks for the anchor and for 0 pre-cutoff chained rows). DEV at migration 64 needs only a binary bump before `Enabled=true`. `docs/requirements/Component-AuditLog.md` has been updated. Closing.
dohertj2 commented 2026-11-13 07:22:44 -05:00
Author
Owner
Copy Link
Copy Source

Live on DEV vd03 (2026-11-13), build 8e32f353. I enabled the chain with AuditLog:HashChain:Enabled=true (Central service env), left ChainBacklog unset, and left head export off.

  • Central log: AuditLog hash chain started at 2026-11-13T12:16:53.1143422Z: only rows dated from then on are chained…
  • AuditLogChainTombstone has exactly one ChainSeq 0 row: month 2026-11, cutoff 2026-11-13T12:16:53.1143422, EventId 5CADAB1D-C4A1-4C0F-A000-000000000139.
  • 0 chained rows dated before the cutoff. Of 2,845 rows, 1 is chained: a post-cutoff InboundAuthFailure, ChainSeq 1, head LastSeq 1.
  • audit verify-chain --month 2026-11: Intact. Rows verified 1, Unchained 0, Chain from 2026-11-13T12:16:53.1143422Z, Pre-cutoff rows 507.
  • --month 2026-10: NotChained, Pre-cutoff rows 667.
  • Both commands exited 0. Both counts match direct SQL.

Record: docs/deployment/deployment-records/2026-11-13-wonder-app-vd03-8e32f353.md (581e25ca).

https://claude.ai/code/session_01GT67ACmpMR7b9LGRPLijMi

**Live on DEV vd03 (2026-11-13), build `8e32f353`.** I enabled the chain with `AuditLog:HashChain:Enabled=true` (Central service env), left `ChainBacklog` unset, and left head export off. - Central log: `AuditLog hash chain started at 2026-11-13T12:16:53.1143422Z: only rows dated from then on are chained…` - `AuditLogChainTombstone` has **exactly one** `ChainSeq 0` row: month 2026-11, cutoff `2026-11-13T12:16:53.1143422`, EventId `5CADAB1D-C4A1-4C0F-A000-000000000139`. - **0** chained rows dated before the cutoff. Of 2,845 rows, 1 is chained: a post-cutoff InboundAuthFailure, ChainSeq 1, head LastSeq 1. - `audit verify-chain --month 2026-11`: **Intact**. Rows verified 1, Unchained 0, Chain from `2026-11-13T12:16:53.1143422Z`, **Pre-cutoff rows 507**. - `--month 2026-10`: **NotChained**, Pre-cutoff rows 667. - Both commands exited 0. Both counts match direct SQL. Record: `docs/deployment/deployment-records/2026-11-13-wonder-app-vd03-8e32f353.md` (`581e25ca`). https://claude.ai/code/session_01GT67ACmpMR7b9LGRPLijMi
dohertj2 commented 2026-11-13 08:14:54 -05:00
Author
Owner
Copy Link
Copy Source

QA vq03/vq04 now runs 8e32f353 (2026-11-13, record 8ba5d5d7), which contains the cutoff. The chain stays off on QA per the owner's go: AuditLog__HashChain__* is not set, and 0 rows are chained. QA's later #107 §7 enablement therefore won't chain the 3,441-row backlog.

**QA vq03/vq04 now runs `8e32f353` (2026-11-13, record `8ba5d5d7`), which contains the cutoff.** The chain stays **off** on QA per the owner's go: `AuditLog__HashChain__*` is not set, and 0 rows are chained. QA's later #107 §7 enablement therefore won't chain the 3,441-row backlog.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
blocked
Blocked by dependency or external hold
 cross-repo
Needs coordinated change in another family repo (wire contract etc.)
 migrated
Migrated from a markdown backlog (2026-11 standardization)
 priority:p0
Must-have / blocking
 priority:p1
High priority
 priority:p2
Normal priority
 priority:p3
Low priority / nice to have
 type:bug
Defect in shipped behaviour
 type:debt
Technical debt / hygiene
 type:deploy-gate
Rollout/deploy/migration step not yet done on an environment
 type:docs
Documentation work
 type:feature
Feature implementation
 type:followup
Follow-up deferred from a plan, PR or gate
 type:infra
Infrastructure and scaffolding
 type:review-finding
Unfixed finding from an architecture/code review
 type:test
Test work
No Label priority:p2 type:deploy-gate
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/ScadaBridge#139
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?