How We Made Audit Logs Immutable at the Storage Layer

Everyone talks about the hash chain. It's the sexy part — a tamper-evident linked list of records an auditor can verify independently. But a hash chain only proves the bytes weren't changed. It does nothing if the storage layer underneath lets you rewrite history more quietly: shift a timestamp, change a value and re-anchor the chain, or have the whole table silently drift when a server's timezone changes.

Immutability is won or lost at the storage layer. Here's the concrete design we landed on for a self-hosted audit log system (PostgreSQL + Spring Boot + JPA).

1. Store absolute instants, never wall-clock strings

The single most important rule: a timestamp in an audit record must be an absolute moment, not a wall-clock label.

In Java that means java.time.Instant — a point on the timeline with no zone attached. In PostgreSQL it means TIMESTAMP WITH TIME ZONE (aka timestamptz), which stores the moment as UTC under the hood.

@Entity
@Table(name = "audit_log")
public class NormalizedLog {
    @Column(nullable = false)
    private Instant eventTime;          // absolute moment, zone-free

    // Canonical ISO string kept *purely* for hashing (see §3)
    @Column(name = "event_time_iso", length = 64)
    private String eventTimeIso;
    // ...prevHash / curHash, actor, action, etc.
}

The trap we deliberately avoid: storing LocalDateTime. A LocalDateTime like 2024-03-15 09:30:00 carries no zone. The moment it hits the database, its true meaning depends on whatever timezone the connection or JVM happens to be in. Move the server, change a config, restore from a backup taken elsewhere — and every "when" in your audit trail is now ambiguous or simply wrong. For a compliance log, that's fatal.

2. UTC at rest, local only at read time

We store everything as UTC. The raw database shows UTC values, and that is expected, not a bug. The display/analysis timezone is a runtime setting (kept in a config table, editable from the UI, applied live) — it is never nailed to the JVM.

That last point matters. We explicitly do not do any of these:

• -Duser.timezone=... on the JVM
• TimeZone.setDefault(...) in code
• hibernate.jdbc.time_zone (a no-op for Instant anyway)
• a Hikari connection-init-sql that SETs a session timezone

Why? Because those don't just display in a zone — they can mutate what gets written. The storage baseline must stay UTC, unambiguously.

A real bug this prevented: "today's events" and daily-trend buckets. If you bucket by the UTC date, an event at 07:30 Asia/Shanghai (which is 23:30 UTC the previous day) lands on the wrong day. So the day-bucketing query does the conversion in the database, per the user's configured zone:

SELECT to_char(event_time AT TIME ZONE :tz, 'YYYY-MM-DD') AS d, COUNT(*)
FROM audit_log
WHERE event_time >= :from
GROUP BY 1 ORDER BY 1;

And "since when" counts compare the timestamptz column directly (hitting the ix_al_event_time index) instead of string-slicing the ISO column — which would reintroduce the UTC/local mismatch.

![Time storage: UTC at rest, local at read]

3. Keep a canonical string — but only for the hash

One subtlety: Instant has nanosecond precision, and a DB round-trip can truncate or reformat it. If you hash the Instant object directly, the hash can change between writes and reads even when nothing was tampered with. So each record also stores a canonical ISO-8601 string (eventTimeIso) that the hash chain is computed over. The string is stable; the chain stays verifiable.

// TimeUtil: lenient ISO-8601 parse → Instant (UTC)
public static Instant parse(String s) {
    try { return OffsetDateTime.parse(s).toInstant(); } catch (Exception ignored) {}
    try { return Instant.parse(s); } catch (Exception ignored) {}
    try { return LocalDateTime.parse(s).toInstant(ZoneOffset.UTC); } catch (Exception ignored) {}
    return Instant.now();
}

Note the last branch: an input with no offset is treated as UTC. That's an explicit ingest convention — never infer a source's local zone, or you'll bake ambiguity into the chain.

4. Erasure vs. immutability: keep the hash, redact the PII

"Immutable" doesn't mean "keep every byte forever." Under GDPR's right-to-erasure you must be able to purge a person's PII. But if you delete or rewrite the record, the hash chain breaks and you lose tamper-evidence for everyone.

Our compromise: redact the PII fields in place (actor, IP, raw message, geo tags) but leave curHash untouched, and flip an erased flag. The chain (prevHash → curHash) stays mathematically intact and still verifies; verification simply skips erased records.

@Modifying
@Query("update NormalizedLog l set l.erased = true, l.erasedAt = :now, l.erasedBy = :by, "
     + "l.sourceIp = :mask, l.actor = :mask, l.rawMessage = :mask, "
     + "l.beforeValue = :mask, l.afterValue = :mask, "
     + "l.geoCountry = null, l.geoRegion = null, l.geoCity = null, l.geoIsp = null, l.geoScope = null "
     + "where l.erased = false and l.actor = :actor")
int eraseByActor(@Param("actor") String actor, @Param("now") Instant now,
                 @Param("by") String by, @Param("mask") String mask);

So erasure is itself an audited, non-destructive event — which is exactly what a regulator wants to see.

5. The hash chain is the glue

prevHash and curHash make the whole table an append-only, verifiable chain (the subject of my previous post on building a tamper-evident audit log). Storage immutability is what makes that chain trustworthy: fixed UTC instants, stable canonical strings, and erasure that preserves hashes instead of rewriting them.

Takeaways

• Audit timestamps = Instant + timestamptz. Never LocalDateTime.
• Store UTC. Convert to the user's zone only when reading / displaying / bucketing.
• Don't nail the JVM's timezone — it mutates writes, not just reads.
• Keep a canonical string for hashing; hash the string, not the Instant.
• Erasure and immutability aren't opposites: redact PII, keep the hash.

If you're building a self-hosted audit log and want the verifiable-chain side of this, I wrote about the hash-chain design here: https://dev.to/gentlyding/how-we-built-a-tamper-evident-audit-log-for-soc-2-and-iso-27001-evidence-jl4