Skip to content

Ledger audit hash timezone fix

Date: 2026-06-02 Type: Hotfix Domain: app/graphql/ledger_audit/ Related feature page: ledger-audit.md

Problem / motivation

ledgerAuditChainVerify returned isValid: false for every tenant whose timezone is not UTC (e.g. all Panama tenants on America/Panama, −05:00), even though the audit data is completely intact.

Root cause is an asymmetric timestamp serialization between the writer and the verifier:

  • LedgerAuditService.record hashed at = self.context.now.isoformat(), where context.now is pendulum.now(tz=self.tz_info) — a tenant-local, timezone-aware datetime. For Panama that string is e.g. 2026-05-26T09:13:01.397875-05:00.
  • The at column is TIMESTAMP(timezone=True). Postgres returns it normalized to UTC, so LedgerAuditService.verify_chain recomputed the hash with row.at.isoformat() = 2026-05-26T14:13:01.397875+00:00.

Same instant, different string → different SHA-256. The genesis row already fails, so the whole chain reports invalid.

This was proven by recomputing the seq-1 genesis hash directly from production data: only the −05:00 local-time string reproduces the stored row_hash (e46d6dce…); the UTC string does not.

Two secondary weaknesses were found while diagnosing and are folded into this hotfix because the rehash migration is the natural moment to address them:

  1. Tamper-coverage gap. The hash material was only prev_hash | payload | at | actor_id. operation, entry_id, line_id and sequence_number were not covered, so those columns could be altered without breaking the chain — defeating the point of a tamper-evident log.
  2. No tests. The module shipped with zero tests, and tz_info defaults to "UTC", so any UTC-based fixture has writer == verifier and never exercises the bug.

In scope

  • Make at hashing timezone-independent: canonicalize to UTC ISO-8601 on both the write and verify paths, by hashing the datetime itself (not a pre-formatted string) so the two paths cannot drift.
  • Harden the hash to cover sequence_number, entry_id, line_id, operation in addition to prev_hash | payload | at | actor_id.
  • A data migration that recomputes prev_hash + row_hash for all existing ledger_audit_logs rows, in sequence_number order, using the corrected canonicalization (safe — the underlying data is verified intact).
  • Tests for LedgerAuditService.record / verify_chain, including a non-UTC tenant regression that fails before the fix and passes after.
  • Update the existing ledger-audit.md hash-formula description.
  • Version bump + changelog.

Out of scope

  • The concurrency race on sequence_number (two concurrent record calls read the same last_row). Tracked as a future addition below.
  • Reporting the resolved end_sequence back in the response when the caller omits it. Future addition.
  • Any change to the GraphQL surface — types, queries, and field names are unchanged.

Data model changes

No schema changes. ledger_audit_logs columns are unchanged. This ships one data-only migration:

  • Revision rehash_ledger_audit_logs (down_revision = add_position_to_users): upgrade() walks ledger_audit_logs ordered by sequence_number, recomputes each row's prev_hash (previous row's new row_hash, NULL for the first) and row_hash under the new formula, and writes them back. downgrade() is a no-op by necessity (the pre-fix hashes were timezone-dependent and not reconstructable post-UTC-normalization); this is documented in the migration docstring and is safe because hashes are derived data, fully rebuildable by re-running upgrade().

GraphQL surface

Unchanged:

ledgerEntryHistory(entryId: UUID!): [LedgerAuditLog!]!
ledgerAuditChainVerify(startSequence: Int = 1, endSequence: Int = null): LedgerAuditChainVerification!

RBAC

No change. Both queries continue to require Path.AUDIT_LOG.

Background tasks / cron

None.

Frontend contract

No change to wire up. ledgerAuditChainVerify simply starts returning isValid: true for non-UTC tenants once the migration has run. The "Audit integrity" badge described in ledger-audit.md will go green. No query, field, or type changed.

What's being implemented

  • Changed service — LedgerAuditService:
  • _compute_hash(...) now accepts the at datetime directly and normalizes it to UTC ISO-8601 internally; new positional members sequence_number, entry_id, line_id, operation added to the material.
  • record(...) passes now (the datetime) and the new identity fields.
  • verify_chain(...) passes row.at and the new identity fields.
  • New migration — alembic/versions/20260602_rehash_ledger_audit_logs.py (revision = rehash_ledger_audit_logs).
  • New tests — tests/graphql/ledger_audit/test_ledger_audit_service.py covering: record→verify symmetry on a non-UTC (America/Panama) tenant; a clean multi-row chain verifies True; a tampered payload, operation, entry_id, and broken link each verify False.
  • Docs — corrected hash formula in ledger-audit.md; this page wired into mkdocs.yml.

Hash material (new)

sha256(
  (prev_hash or "") | sequence_number | entry_id | (line_id or "") |
  operation_int | canonical_json(payload) | at_utc_isoformat | (actor_id or "")
)

where at_utc_isoformat = at.astimezone(UTC).isoformat() and canonical_json is json.dumps(payload, sort_keys=True, separators=(",", ":"), default=str).

What shipped

Everything in the in-scope list landed:

  • LedgerAuditService._compute_hash now takes the at datetime and normalizes it to UTC internally, and the material covers prev_hash | sequence_number | entry_id | line_id | operation | payload | at_utc | actor_id. record and verify_chain were updated to match.
  • Migration rehash_ledger_audit_logs rebuilds prev_hash + row_hash for every existing row in sequence_number order. Verified: the migration's inlined hash is byte-identical to the service hash across the value types the driver returns (raw-int vs enum operation, UUID vs str ids).
  • tests/graphql/ledger_audit/test_ledger_audit_service.py — 12 tests, all green: tz-stability, identity-field coverage, a 5-row Panama record→verify round-trip, an all-timezones parametrization, and tamper detection for payload / operation / entry_id / a broken link.
  • ledger-audit.md hash formula corrected; this page wired into mkdocs.yml.

task all is green (lint, typecheck, file-length, both schema exports) and the ledger-audit test module passes 12/12.

Future additions

  • Sequence-number concurrency guard — record derives the next sequence from repository.last_row(); two concurrent posts collide on the sequence_number / row_hash unique constraints. Deferred: needs a row lock or an INSERT-with-retry, out of scope for a hash-correctness hotfix.
  • Report resolved end_sequence — when the caller omits endSequence, the response still echoes null instead of the last sequence actually verified. Cosmetic; deferred.