Skip to main content

ADR-0181: Audit-hash timestamp normalisation, and a two-epoch chain

id: 0181-audit-hash-timestamp-normalisation-and-the-two-epoch-chain sidebar_position: 182 title: "ADR-0181: Audit-hash timestamp normalisation, and a two-epoch chain" ---# ADR-0181: Audit-hash timestamp normalisation, and a two-epoch chain

Date: 2026-08-10 Status: Accepted Amends: ADR-0109 (canonical representation of created_at; ADR-0109 stays Accepted) Deciders: Adrian (Soft4U BV), Claude Opus 5, Codex (review, PR #1118)

Context​

ADR-0109 makes audit_events tamper-evident: each row carries entry_hash = SHA-256(prev_hash || canonical(row)), replayed by verify_tenant_chain.

canonical_row rendered a datetime with .isoformat() and treated a naive value as-is, under a comment asserting the column was written consistently on a given deployment. That consistency was never available.

AuditEvent.created_at is mapped nullable=False with no DateTime(timezone=True), so SQLAlchemy infers a NAIVE column and asyncpg rejects an aware value outright. The physical column is timestamp with time zone, so Postgres hands the value back aware on read. The write path hashes before the round trip and the read path after it:

- writer hashes …T20:28:33.021463 - verifier hashes …T20:28:33.021463+00:00

Same instant, different string, different digest. 1,709 rows fell out of the chain. Which side of the round trip you stand on was the variable — not deployment configuration.

There is a second, quieter dependency. Because the naive value is bound against a timestamptz column, Postgres interprets it in the session's TimeZone. On a session set to Europe/Brussels the writer hashes 20:28:33Z, the database stores 18:28:33Z, and every row written on that deployment falls out of the chain — silently, with no error and no failed insert (Codex, PR #1118).

Decision​

1 · A naive datetime is interpreted as UTC. _normalise gives it the explicit offset, so a naive and an aware value denoting the same instant canonicalise identically. This removes the class rather than correcting one caller: a future writer that forgets tzinfo is now harmless.

2 · The session TimeZone is pinned to UTC and asserted at boot. The engine sets connect_args={"server_settings": {"timezone": "UTC"}}, and assert_session_is_utc() runs from both app/main.py (which verifies chains) and app/worker.py (which writes them). Pinned and checked, because "we set it in connect_args" is a claim about configuration while this is a claim about the live connection — a pooler in between, or a later edit, breaks the first without touching the second.

3 · Rows written under the old rule are verified against what was actually signed.

This ADR first said those rows "cannot be repaired: their stored digest was computed over a different string, and no re-derivation reproduces it without rewriting the row", and accepted a permanently unverifiable epoch on that basis.

That premise is false, and it was falsifiable from this branch's own regression test. The pre-fix writer hashed a NAIVE UTC timestamp; Postgres hands the value back aware. Re-canonicalising with the old rule at verification time reproduces the stored digest bit-for-bit — measured — and rewrites nothing (Codex, PR #1118).

So verify_chain tries the current rule and, on mismatch, the pre-fix rule. A row matching the second is verified, counted in legacy_verified, and disclosed in details. The chain is verifiable end to end, and ok can be True for a tenant carrying drift.

The widening is bounded to the timestamp representation and nothing else. An altered details, event_type or tenant_id fails both rules — pinned by a test. And the offset was never covered for these rows in the first place: their stored digest was computed over the naive form, so verifying against what was signed does not weaken them, it stops reporting untampered rows as tampered.

A post-fix row matches the current rule and never reaches the fallback, so the old contract cannot spread forward.

Consequences​

Positive​

- Naive and aware forms of the same instant hash identically, so the write/read asymmetry cannot recur. - A non-UTC session is refused at boot instead of silently breaking every row. - Post-fix rows are demonstrably verifiable on a tenant carrying historical drift, which is the state every existing deployment is in.

Negative​

- The verifier now knows two canonicalisations, and must keep knowing the old one for as long as any pre-fix row exists. That is permanent code carrying a historical rule. - A verdict that says ok=True may rest partly on the older contract, so legacy_verified has to be READ, not just ok. A surface rendering only the boolean under-reports. - The ORM/physical column mismatch is not fixed here. It remains real, and a migration reconciling created_at to DateTime(timezone=True) is tracked separately.

Neutral​

- No migration; no stored row changes. - relink detection and break_kind are unchanged. - The per-case verdict is NOT unchanged — see §"The declared legacy session offset" below. An earlier revision of this line said it was, which was true when written and false once the offset was introduced (Codex P1 on PR #1118).

The declared legacy session offset, and what it can attest​

A pre-#1053 writer signed a naive wall time it believed was UTC. Where the PostgreSQL session was NOT UTC, the server persisted a different instant, so replaying the stored value as UTC reconstructs the wrong string and the row fails both rules. audit_legacy_session_offset_hours lets a deployment DECLARE that offset so those rows can be read as what was actually signed.

There are TWO declaration mechanisms, not one, and this ADR previously named only the first (Codex P1 on PR #1118). A fixed numeric offset cannot describe a session in a zone that observes daylight saving: the same deployment signed some rows at +01:00 and others at +02:00, and one number is wrong for half of them. So audit_legacy_session_timezone declares the ZONE instead, and the offset is resolved PER ROW at the instant that row was signed.

Both are declarations, and the same limit applies to both: they are deployment configuration, not evidence the chain carries. Three properties bound them.

- Exactly one may be set. With both configured they can disagree about the same row, so the implementation logs an error and applies NEITHER — a contradiction resolves to the un-declared reading, never to a guess. - A zone offset is read at the instant, not the wall clock. ZoneInfo.utcoffset(dt) interprets a datetime's wall-clock fields as local to that zone, so passing a UTC instant asks the wrong question. Measured on Europe/Brussels at 2026-03-29 01:30Z, thirty minutes after the spring transition, that form answers +01:00 where the true offset is +02:00 — which reconstructs a wrong wall time and reports a legitimate row as ALTERED. A false tamper alarm on an audit chain is the most expensive kind of wrong answer this system can give. - A row recovered under EITHER mechanism is offset_dependent. The verdict below does not distinguish them, because the property that matters is that a mutable runtime value was consulted — not which setting held it. An earlier implementation asked only the numeric setting, so a zone-declared deployment reproduced a summer row with a +02:00 offset and then reported it offset_attested=True.

The register entry for this ADR said non-UTC legacy rows cannot be reconstructed "without audit_legacy_session_offset_hours". That is now false in the letter and was always the weaker statement: what they cannot be reconstructed without is a DECLARATION, of which there are two forms.

It is deployment configuration, not evidence the chain carries — and that changes what a successful verification means. Alter a stored timestamp by two hours, set the offset to two, and the re-derivation reproduces the unchanged digest. So this ADR records three things the implementation now does:

1. A third verdict shape: ok=False with NO break. A chain in which any row reproduced its digest only under a NONZERO offset returns ok=False, break_kind="legacy_offset_unattested", offset_dependent=<count> and offset_attested=False. The reason states plainly that no alteration is alleged — the rows are consistent with being intact; what is missing is an attestation independent of a mutable runtime value. Reporting them as "altered" would send an operator hunting a breach that may not exist, and a false alarm is how a control gets switched off (ADR-0121).

2. An epoch bound. audit_legacy_epoch_end confines a nonzero offset to rows written before it, so a recent row cannot be rescued by declaring an offset that happens to match an alteration. An unparseable value rescues NOTHING — the safe direction, since a typo must not widen the offset's reach. Unset means unbounded, which is exactly why an offset-dependent verification is reported unattested rather than verified.

3. Per-case verdicts fail closed on it. verify_case_segment requires offset_attested before reporting a segment intact, and both early returns in verify_chain carry the accumulated count — without which a chain whose EARLIER rows leaned on the offset, followed by a later break, reported offset_attested=True and green-badged the earlier case. Conservative by design: the count is chain-wide, so a case is refused attestation whenever any row in the tenant chain used the offset. Over-refusing withholds a claim; under-refusing makes a false one.

A default deployment is untouched: the offset defaults to 0.0, and zero takes the plain-strip path, which consumes no configuration at all.

Regulator-facing consequence, since it reaches a generated document: the AMLR dossier renders this as a distinct NOT ATTESTED state — verified: false, gate_class: blocked — never as VERIFIED and never as the "altered, deleted, or re-linked" wording reserved for a real break.

Alternatives Considered​

Alternative 1: Re-hash the historical rows to the new canonical form​

Why rejected: it rewrites rows in an append-only, immutability-triggered table (ADR-0064), and it destroys the only evidence that the drift happened. A chain that can be recomputed to look clean is not tamper-evident.

This remains rejected — but note it is no longer the only way to verify those rows. Re-deriving at READ time achieves it without touching a single row, which is what §3 now does.

Alternative 4: Accept a permanently unverifiable epoch (this ADR's first decision)​

Why rejected: it rests on a premise this branch's own regression test disproves — that the pre-fix digest cannot be reproduced. It can, exactly, so the epoch would have declared 1,709 untampered rows permanently unverifiable for no reason a measurement supports.

Alternative 2: Keep returning at the first break​

Why rejected: measured consequence — every case after the first historical mismatch reports unverified, so the fix has no observable effect through the production APIs. A correct fix nobody can see is indistinguishable from no fix.

Alternative 3: Rely on the server's default TimeZone being UTC​

Why rejected: that is a claim about how someone configured the cluster, and its failure mode is silent. Pinning it makes it a property of this application's connections; asserting it makes the property checkable.

Decision context​

- Latency: one SHOW TimeZone per process at boot. _normalise gains one replace/astimezone per datetime field — negligible against SHA-256. - Dependency surface: none. Standard library datetime and an asyncpg server setting. - Debuggability: improved. A non-UTC session now names the consequence at boot rather than producing rows that fail verification months later. - Reversibility: the normalisation and the epoch reporting are pure functions, revertible in one commit. The rows written under the new rule would then fall out of the chain in the same way — so reverting has the same one-way cost as adopting, which is the reason to get it right once. - Blast radius: every audit row written from now on, and every chain verification. relink detection is untouched; per-case verdicts gain one fail-closed condition (the offset contract below). - Alternative considered: re-hash the historical rows — rejected as destroying the evidence the chain exists to preserve (Alternative 1).