Definition
- Append-only is not enough – A plain append-only table only controls who may change data; a DBA or compromised account can still alter rows and the table looks normal afterward.
- Hash chain – Each record stores
hash(content + previous_hash), so every record's hash depends on the entire history, and a single change breaks all later links. - Signed checkpoints – Periodic signed snapshots of the chain head catch whole-chain rewrites because the attacker cannot forge the signature.
- WORM storage – Write-once-read-many storage prevents tampering; the hash chain detects it. Both prevention and detection are needed.
- Tiered storage – Recent data stays in a fast queryable store; old data moves to cheap cold storage with an index for searchability.
Imagine National Bank of India keeps a logbook where every page's corner has a wax seal made by melting together the current page's text and the seal from the previous page. If anyone tears out or rewrites a page, its seal no longer matches, and because each seal depends on the one before, every later seal breaks too. The logbook is kept in a vault that only allows adding pages. Every night the manager photographs the latest seal and signs the photo. Just locking the logbook in a drawer (append-only table) is not enough, because the branch manager with the key could still quietly rewrite a page; the seals are what make tampering visible.
Understanding the Concepts
Why is a plain append-only table not tamper-evident?
An append-only table is one where you revoke UPDATE and DELETE permissions from the application role. That stops the ordinary application from changing history, but a database administrator, a superuser, or someone with direct storage access can still alter or delete rows, and the data looks perfectly normal afterward. Append-only controls who should change data; it does not detect that a change happened.
-- Append-only table: revoke UPDATE/DELETE
CREATE TABLE audit_log (
seq_no BIGSERIAL PRIMARY KEY,
event_type VARCHAR(100) NOT NULL,
actor VARCHAR(100) NOT NULL,
payload JSONB NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
);
REVOKE UPDATE, DELETE ON audit_log FROM app_role;
-- A DBA can still: UPDATE audit_log SET payload = '...' WHERE seq_no = 42;
-- Nothing in the table reveals this happened.
How does the hash chain make tampering detectable?
Each record stores a hash computed over its own content plus the previous record's hash. Changing any record changes its hash, which no longer matches what the next record hashed over, breaking every later link.
package com.bank.audit;
import java.nio.charset.StandardCharsets;
import java.security.MessageDigest;
import java.util.HexFormat;
public class HashChainBuilder {
public static String computeHash(long seqNo, String eventType, String actor,
String payload, String previousHash) {
try {
String content = seqNo + "|" + eventType + "|" + actor + "|"
+ payload + "|" + previousHash;
MessageDigest md = MessageDigest.getInstance("SHA-256");
byte[] digest = md.digest(content.getBytes(StandardCharsets.UTF_8));
return HexFormat.of().formatHex(digest);
} catch (Exception e) {
throw new RuntimeException("Hash computation failed", e);
}
}
public static void main(String[] args) {
String hash0 = "0000000000000000"; // genesis
String hash1 = computeHash(1, "LOGIN", "Rajesh", "{}", hash0);
String hash2 = computeHash(2, "TRANSFER", "Rajesh",
"{\"amount\":50000}", hash1);
System.out.println("Record 1 hash: " + hash1);
System.out.println("Record 2 hash: " + hash2);
// If record 1 is altered, recomputing hash1 gives a different value,
// which does not match the previousHash stored in record 2.
}
}
Record 1 hash: a3f2b8c1... (depends on content + genesis)
Record 2 hash: 7e4d9a0f... (depends on content + hash of record 1)
# Changing record 1 -> new hash1' -> record 2's previousHash mismatch -> TAMPER DETECTED
How do you verify the chain?
Walk from a known-good starting point, recomputing each hash and comparing. The first mismatch pinpoints where tampering occurred.
package com.bank.audit;
import java.util.List;
public class ChainVerifier {
public boolean verify(List<AuditRecord> records) {
String expectedPrevHash = "0000000000000000"; // genesis
for (AuditRecord record : records) {
if (!record.getPreviousHash().equals(expectedPrevHash)) {
System.err.println("TAMPER DETECTED at seq " + record.getSeqNo()
+ ": expected prev=" + expectedPrevHash
+ " but found prev=" + record.getPreviousHash());
return false;
}
String recomputed = HashChainBuilder.computeHash(
record.getSeqNo(), record.getEventType(), record.getActor(),
record.getPayload(), record.getPreviousHash());
if (!recomputed.equals(record.getHash())) {
System.err.println("TAMPER DETECTED at seq " + record.getSeqNo()
+ ": hash mismatch");
return false;
}
expectedPrevHash = record.getHash();
}
System.out.println("Chain verified: all " + records.size() + " records intact.");
return true;
}
}
How do signed checkpoints catch whole-chain rewrites?
If an attacker has full write access, they could recompute every hash to produce a consistent new chain. Periodically signing the chain head with a private key (stored in an HSM) and saving the signed checkpoint to WORM storage prevents this.
package com.bank.audit;
import java.security.*;
import java.util.Base64;
public class CheckpointSigner {
private final PrivateKey privateKey; // from HSM
public CheckpointSigner(PrivateKey privateKey) {
this.privateKey = privateKey;
}
public String signChainHead(String chainHeadHash) throws Exception {
Signature sig = Signature.getInstance("SHA256withRSA");
sig.initSign(privateKey);
sig.update(chainHeadHash.getBytes());
return Base64.getEncoder().encodeToString(sig.sign());
}
// Store signed checkpoint in WORM storage.
// A rewritten chain's head won't match the previously signed checkpoint.
}
How do you tier data for 7-year retention?
Recent data (last 90 days) stays in a fast, indexed, queryable store. Older data moves to cheap cold object storage with object-lock immutability, partitioned by day.
package com.bank.audit;
import java.time.LocalDate;
public class StorageTieringPolicy {
public String determineStorage(LocalDate eventDate) {
long daysOld = java.time.temporal.ChronoUnit.DAYS.between(
eventDate, LocalDate.now());
if (daysOld <= 90) {
return "HOT: PostgreSQL (fast, indexed, queryable)";
} else if (daysOld <= 365) {
return "WARM: S3 Standard (compressed, indexed)";
} else {
return "COLD: S3 Glacier (object-lock, 7-year retention)";
}
}
}
How do you keep cold data queryable?
A lightweight index maps query keys (time, actor, entity) to the object and offset in cold storage.
package com.bank.audit;
public class ColdStorageIndex {
// Index entry: actor=Rajesh, date=2023-01-15 -> s3://audit-cold/2023/01/15/chunk-42.gz, offset=1024
public String findInColdStorage(String actor, String date) {
// Look up in the lightweight index (Elasticsearch or metadata table)
return "s3://audit-cold/" + date.replace("-", "/") + "/chunk-42.gz";
}
}
What are common mistakes?
The most common mistake is assuming append-only is sufficient for tamper-evidence. The second is signing the chain head with a key stored in the same database, which means an attacker who owns the DB also owns the key.
package com.bank.audit.antipattern;
// WRONG: signing key stored in the same DB as the audit log
// If the attacker owns the DB, they own the key and can forge checkpoints.
// CORRECT: signing key in an HSM (Hardware Security Module)
// The attacker cannot extract the key even with DB access.
What are common interview follow-up questions?
How does this compare to blockchain? What is the performance overhead of hashing every record? How do you handle the case where the hash chain verifier itself is compromised? How would you implement this with Amazon QLDB? What is the difference between tamper-evident and tamper-proof?
Visual Overview
┌───────────────────────────────────────────────────────────┐ │ TAMPER-EVIDENT AUDIT LOG │ ├───────────────────────────────────────────────────────────┤ │ │ │ Record 1 Record 2 Record 3 │ │ ┌──────────┐ ┌──────────┐ ┌──────────┐ │ │ │ content │ │ content │ │ content │ │ │ │ prev: 00 │───▶│ prev: H1 │───▶│ prev: H2 │ │ │ │ hash: H1 │ │ hash: H2 │ │ hash: H3 │ │ │ └──────────┘ └──────────┘ └──────────┘ │ │ │ │ If Record 1 is altered: │ │ H1' != H1 → Record 2's prev (H1) != H1' → BROKEN │ │ → H2' changes → Record 3's prev (H2) != H2' → BROKEN │ │ │ │ Signed Checkpoints (in WORM storage): │ │ ┌───────────────────────────┐ │ │ │ Checkpoint: H3 signed by │ │ │ │ HSM private key │ │ │ │ Stored in S3 Object Lock │ │ │ └───────────────────────────┘ │ │ A full rewrite → new H3' != signed H3 → CAUGHT │ │ │ │ Storage Tiers: │ │ HOT (0-90d) → PostgreSQL │ │ WARM (90d-1y) → S3 Standard │ │ COLD (1y-7y) → S3 Glacier + Object Lock │ └───────────────────────────────────────────────────────────┘
Key Takeaway
A tamper-evident audit log records every significant event so that any later edit, deletion, or insertion is cryptographically detectable. At National Bank of India, an auditor pointed out that the append-only audit table could still be edited by a database administrator with nothing to show for it. Append-only controls who may change data under normal operation, but it does not answer whether anyone did.
The hash chain closes that gap. Each record stores a hash computed over its content plus the previous record's hash, so every record's hash transitively depends on the entire history. Changing any record changes its hash, breaking every later link, and a verification walk detects the first mismatch instantly. To prevent a full-chain rewrite where the attacker recomputes all hashes, Pankaj periodically signs the chain head with a private key in an HSM and stores the signed checkpoint in WORM storage. A rewritten chain's head will not match the previously signed checkpoint.
For scale (1 million events per day for 7 years), data is tiered by age: recent events stay in a fast queryable store, and older events move to cheap cold storage with object-lock immutability, partitioned by day, with a lightweight index so old data stays searchable. The hash chain spans tiers because each record carries its previous hash regardless of where it is stored.
Remember one sentence: Tamper-evidence comes from chaining each record's hash to the previous one so any change breaks the chain, reinforced by signed checkpoints to catch full rewrites and WORM storage to prevent tampering, because a plain append-only table only controls who may write and cannot reveal that a privileged user changed history.
---