Hash-Chained Audit Logging

A log that can be silently edited is not a log. It is a draft.

The canonical evidence system you have built across the preceding chapters produces sealed, signed attestations. An attestation carries its own cryptographic proof of integrity — the signature fails if the content changes. But attestations answer a narrow question: is this artifact intact? They do not answer the question your opposing counsel will ask first: when did you claim to have these? Can you prove the timeline?

The audit log answers that question. Every attestation emission, every read of a sensitive document, every export to a production set, every key rotation — each of these events writes one row to audit_log. The rows accumulate in order. And because each row's hash incorporates the hash of the previous row, the sequence is tamper-evident: modify any row and you break every hash that follows it. The verifier detects the break at exactly the modified row. This chapter explains how that chain works, what it does and does not protect, and how to operate it in a proceeding where someone will inevitably challenge the timeline. ## At a glance - The audit log is hash-chained and append-only: each row's hash incorporates the prior row's hash, so any modification, deletion, or out-of-order insertion breaks every subsequent hash and is detected by the verifier. - Every Canon emission writes one audit row: the attest action in the audit_log records the attestation_id and chain_hash at the moment of emission, making the audit log the timeline proof that attestations cannot back-date. - The chain is verifiable by the SQL function verify_audit_chain(): any principal with SELECT access can run a complete integrity check and receive a deterministic INTACT or TAMPERED verdict with the first-failing row identified. ## Learning objectives By the end of this chapter you should be able to: 1. Explain how the BEFORE INSERT trigger computes the chain hash — including the role of the separator, the coalesce for the first row, and why clock_timestamp() is used instead of now(). 2. Implement verify_audit_chain() from the schema, including the running_hash initialization, the forward walk in id order, and the early-exit behavior on first mismatch. 3. Describe the two primary failure modes a hash chain can detect — row modification and row deletion — and explain what the verifier reports in each case and why. 4. Explain what the chain does NOT protect against: garbage-in (incorrect data logged at insertion), superuser recompute attacks, and events that were never logged at all. ## The sealed-envelope metaphor Before the math, an image. Imagine a stack of sealed envelopes, numbered in order. Inside envelope 2 is the document for event 2 — and also a photograph of envelope 1, sealed. Inside envelope 3 is the document for event 3 — and a photograph of envelope 2, sealed. And so on. Now suppose an adversary wants to alter the contents of envelope 1 without detection. They open it, change the document, and reseal it. But the photograph inside envelope 2 shows the old envelope 1 — the seam, the stamp, the fold, exactly as it was. The new envelope 1 looks different from the photograph. The adversary must now open envelope 2 to replace the photograph. But that photograph is captured in envelope 3. Each subsequent envelope captures the prior state. To alter envelope 1 silently, the adversary must also alter envelopes 2 through N. The chain of photographs makes the tamper cascade. A hash chain works identically. The "photograph" is a SHA-256 digest. The digest does not preserve visual appearance; it preserves the precise byte content of the prior row. A changed byte produces a completely different digest. The adversary who changes one row must recompute every subsequent row's hash — which requires access to the entire chain and the ability to write to the database. That access is blocked by design, and any attempt to recompute a consistent chain would still be detectable by a separate audit of the row count and timestamps. ## The precise construction Let row N have a payload P_N — the concatenation of its occurred_at, actor_id, matter_id, action, resource_type, resource_id, and payload fields. Let h_ be the hash stored in the prior row's hash column.

Then:

hash_N = SHA-256( h_{N-1} || "|" || P_N )

Row 1 uses an empty string in place of h_0: hash_1 = SHA-256("" || "|" || P_1). The separator | is a pipe character. It is present in the actual trigger to distinguish the end of one field from the start of the next — coalesce(prev, '') || '|' || .... Without a separator, a payload that ends with an actor UUID and a payload that begins with the same bytes would produce identical inputs for different rows. A verifier reads rows in id order (ascending, because id is a bigserial), recomputes each hash, and compares the recomputed hash to the stored hash column. The first mismatch is the earliest tampered row. Every subsequent row is also invalid, because its prev_hash field records the hash of the tampered row — a hash the verifier will not see when it recomputes from clean inputs. ## The schema The audit_log table is defined in schema/10_core.sql. Every column is load-bearing.

CREATE TABLE audit_log (
  id              bigserial PRIMARY KEY,
  occurred_at     timestamptz NOT NULL DEFAULT clock_timestamp(),
  actor_id        uuid REFERENCES actors(id),
  matter_id       uuid REFERENCES matters(id),
  action          text NOT NULL,
  resource_type   text,
  resource_id     uuid,
  payload         jsonb NOT NULL DEFAULT '{}',
  ip_address      inet,
  user_agent      text,
  prev_hash       text,
  hash            text NOT NULL
);

id is a bigserial — an auto-incrementing integer — not a UUID. That matters. UUID primary keys are unordered; you cannot walk a hash chain by UUID without an additional ordering constraint. With a bigserial, row order is insertion order, and the chain walk is ORDER BY id ASC. occurred_at uses clock_timestamp(), not now(). In a transaction that inserts multiple audit rows, now() would give every row in the transaction the same timestamp. clock_timestamp() returns the actual wall time at the moment of each INSERT, so audit rows are distinguishable even inside a single transaction. prev_hash stores the hash of the prior row, as fetched by the trigger. This field is informational — a verifier recomputes it from the chain — but its presence lets you spot a deleted row: if row 7's prev_hash does not match row 5's hash, either row 6 was deleted or row 7 or 5 was modified. hash is the chain hash of this row. It is computed by the trigger on every INSERT. No application code touches it. payload is a jsonb column. For an attest action, it holds {"attestation_id": "01J...", "chain_hash": "sha256:..."}. For a read, it holds the document's source_id and pii_tier. For a key_rotation, it holds the old and new key fingerprints. The schema imposes no structure on payload — the application code is responsible for logging what matters. ## The trigger The hash-chain trigger fires BEFORE INSERT on every row. It is worth reading in full, because every clause is intentional.

CREATE OR REPLACE FUNCTION audit_log_hash_trigger() RETURNS trigger AS $$
DECLARE
  prev text;
BEGIN
  SELECT hash INTO prev FROM audit_log ORDER BY id DESC LIMIT 1;
  NEW.prev_hash := prev;
  NEW.hash := sha256_hex(
    coalesce(prev, '') || '|' ||
    NEW.occurred_at::text || '|' ||
    coalesce(NEW.actor_id::text, '') || '|' ||
    coalesce(NEW.matter_id::text, '') || '|' ||
    NEW.action || '|' ||
    coalesce(NEW.resource_type, '') || '|' ||
    coalesce(NEW.resource_id::text, '') || '|' ||
    coalesce(NEW.payload::text, '{}')
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_log_hash
  BEFORE INSERT ON audit_log
  FOR EACH ROW EXECUTE FUNCTION audit_log_hash_trigger();

The trigger's first line selects the current tail of the chain — the most recently inserted row's hash. It uses ORDER BY id DESC LIMIT 1, not MAX(id), because MAX requires a full sequential scan without an index on id; the sort on id DESC uses the primary-key index and returns in microseconds. The result goes into prev, which may be NULL for the first row. coalesce(prev, '') handles the first row cleanly. If there is no prior row, prev is NULL, and coalesce substitutes an empty string. The separator | is still present, so row 1's hash input begins | followed by its payload. This is different from a hash chain that omits the separator for row 1; the convention must be documented so the verifier can replicate it. Every field is cast to text. Postgres has deterministic text casts for uuid, timestamptz, jsonb, and inet within a single server; the representation is consistent across rows inserted to the same database. The one fragility here is jsonb::text: key ordering in JSON output from Postgres is not guaranteed to be stable across major version upgrades. If you migrate Postgres major versions, audit the serialization of payload before asserting chain continuity across the migration boundary. The convenience function audit() is how application code writes rows:

SELECT audit('attest', 'attestation', attestation_uuid,
             '{"attestation_id": "01J...", "chain_hash": "sha256:..."}'::jsonb);

The function inserts the row; the trigger computes the hash. Application code never constructs hash or prev_hash. This is the enforcement point: the hash is a database-level invariant, not an application-level convention. ## What gets logged The audit log's value is proportional to the completeness of what it captures. Six action types are mandatory in a litigation-grade system. attest — every Canon attestation emission. The payload must include attestation_id and chain_hash. This is the log entry an expert points to when asked "when was this attestation issued?" The occurred_at column gives the timestamp; the chain verifier establishes that the row has not been modified since insertion. read — every access to a sensitive document or recording above pii_tier = internal. If a worker reads a document marked privileged and the read is not logged, that access is invisible to the audit. Log every read. Storage is cheap; missing an access event is expensive. export — every export to opposing counsel's production set. The payload should include the production batch ID and the Bates range. When opposing counsel claims they never received a document, the export log shows the timestamp, the actor, and the production identifier. key_rotation — every signing key change. Key rotation is a high-value event: it changes what signature is expected on future attestations. A key-rotation audit row with the old and new key fingerprints in its payload establishes the timeline for which key signed which attestations. privilege_assert — every assertion that a document is attorney-client privileged or work product. If the privilege assertion is later challenged, the audit row shows when it was made and by whom. produce — every inclusion of a document in a formal discovery production. Distinct from export: produce is the legal act of responding to a discovery request; export is the technical act of writing files to a delivery medium. rekor_published — every successful publication of a sealed attestation to a Rekor transparency log. Active when MERIDIAN_REKOR_ENABLED=1. The payload must include entry_uuid and log_index returned by the Rekor API. See the transparency log section later in this chapter for the full three-layer integrity model this event participates in. > § For the Record — FRE 803(6), Records of Regularly Conducted Activity. > > "A record of an act, event, condition, opinion, or diagnosis if: (A) the > record was made at or near the time by someone with knowledge; (B) the > record was kept in the course of a regularly conducted activity of a > business or organization; (C) making the record was a regular practice > of that activity." > > The hash-chain trigger that fires on every INSERT satisfies elements > (B) and (C) with cryptographic evidence. The trigger cannot be selectively > disabled without superuser access; its consistent firing is the regular > practice. Element (A) — made at or near the time — is satisfied by the > clock_timestamp() default on occurred_at and, in a proceeding, by > the expert's testimony that the database clock was synchronized via NTP. ## Chain verification Verification is a forward walk: read rows in id order, recompute each hash, compare to the stored hash, report the first mismatch.

CREATE OR REPLACE FUNCTION verify_audit_chain()
RETURNS TABLE(status text, first_bad_id bigint, detail text) AS $$
DECLARE
  r audit_log%ROWTYPE;
  expected text;
  running_hash text := '';
BEGIN
  FOR r IN SELECT * FROM audit_log ORDER BY id ASC LOOP
    expected := sha256_hex(
      coalesce(running_hash, '') || '|' ||
      r.occurred_at::text || '|' ||
      coalesce(r.actor_id::text, '') || '|' ||
      coalesce(r.matter_id::text, '') || '|' ||
      r.action || '|' ||
      coalesce(r.resource_type, '') || '|' ||
      coalesce(r.resource_id::text, '') || '|' ||
      coalesce(r.payload::text, '{}')
    );
    IF expected <> r.hash THEN
      RETURN QUERY SELECT 'TAMPERED'::text, r.id,
        format('expected %s, stored %s', expected, r.hash);
      RETURN;
    END IF;
    running_hash := r.hash;
  END LOOP;
  RETURN QUERY SELECT 'INTACT'::text, NULL::bigint, NULL::text;
END;
$$ LANGUAGE plpgsql;

running_hash starts as an empty string, matching the coalesce(prev, '') in the trigger for row 1. After each verified row, running_hash is set to the stored hash of that row. The next iteration uses this as the prior hash — replicating exactly what the trigger did at insertion time. If the function returns INTACT, every row in the log matches its expected hash. If it returns TAMPERED, first_bad_id is the earliest row that fails verification. Every row after it also fails, but the function stops at the first failure — the earliest tampered row is the meaningful one. Running SELECT * FROM verify_audit_chain() is a complete integrity check of the entire audit history. On a 100,000-row log it completes in under two seconds on commodity hardware. This is not the O(N) bottleneck it might appear: verifying that a hash chain is intact requires reading all N rows exactly once. There is no shortcut. But N is bounded by the size of the litigation record, and the record is orders of magnitude smaller than the scale at which O(N) chain verification becomes a problem. > ◆ Going Deeper — Merkle trees vs. hash chains. > > A hash chain is a degenerate Merkle tree with one leaf per level. To > verify row N, you must read all N prior rows. Verification cost is O(N). > > A proper Merkle tree has verification cost O(log N): to prove that a > specific leaf is in a tree of 1 million entries, you need only log_2(1M) > ≈ 20 hashes. Certificate Transparency (RFC 6962) and its successor > Trillian-Tessera use Merkle trees precisely because they operate at > hundreds of millions of entries — scales where O(N) verification is > impractical. > > For a litigation audit log, O(N) verification completing in under two > seconds is sufficient. The stub in this chapter's outline notes: "The > audit_log is the private version of the same idea." That is accurate. > If Meridian-Cannon's data ever feeds CASEFORUM's public transparency > layer, the log structure should migrate from a hash chain to a > Trillian-Tessera log — not because the math is better, but because > public verifiability requires third-party witnesses who can audit log > membership, which requires Merkle inclusion proofs. > > For now, the chain is sufficient and correct. When it is not sufficient, > the migration path is well-defined. ## This is not a blockchain The term "blockchain" will occur to every non-technical reader of this chapter. It is the wrong frame, and saying so clearly saves time in court. A hash chain has one authoritative copy. There is no distributed consensus mechanism. No proof-of-work, no proof-of-stake, no peer network of validators. The audit_log table sits in one Postgres instance. Its integrity comes from: 1. Database permissions — DELETE and UPDATE are revoked on audit_log for all roles except the Postgres superuser. 2. The trigger — the BEFORE INSERT trigger computes the hash before the row is visible; application code has no path to insert a row with an arbitrary hash value. 3. The verifier — any person with SELECT access can run verify_audit_chain() at any time and reach a deterministic verdict. That is the security model. It is a database-integrity guarantee, not a decentralization guarantee. The threat model is an insider with write access — a disgruntled worker, an adversary who has compromised the application layer — not a nation-state attacking a network of validators. The defense is appropriate to the threat. > ▼ Why It Matters — The two-layer tamper defense. > > Consider two attacks that opposing counsel might construct against your > attestations. > > Attack 1: The attestation was backdated. The attestation's issued_at > field is inside the Canon seal. Altering issued_at breaks the Ed25519 > signature — step 2 of the reference verifier fails. Simultaneously, the > audit log's attest row records occurred_at at insertion time using > clock_timestamp(). Altering the audit row breaks the chain. Both > defenses must fail simultaneously; they are cryptographically > independent. > > Attack 2: A damaging audit row was deleted. DELETE is revoked at > the database level. Deleting a row requires the Postgres superuser role, > which is not held by any application account. If a superuser deletes row > N, every row after N has a prev_hash that refers to a hash the > verifier will not see when it recomputes from row N-1. The verifier > detects the gap at row N+1 and reports TAMPERED. > > No single defense is sufficient against an adversary with superuser > access. Two independent defenses — the Canon seal and the hash chain — > require simultaneous compromise of both. In practice, "simultaneous > compromise of both" is either a very motivated insider or a very > thorough investigation that should itself have generated audit rows. ## What the hash chain does not protect Honest documentation of a system's limits is part of its credibility. Three failure modes survive the hash chain intact. Garbage-in. If application code logs incorrect data — the wrong actor_id, a fabricated occurred_at, a missing attestation_id in the payload — the chain faithfully chains incorrect data. The hash of incorrect data is still a valid hash. A hash chain verifies that the log has not been modified since insertion; it does not verify that what was inserted was true. This is why the audit() function is called by application code and not by a separate auditing system: the application must be correct. Superuser access. A Postgres superuser can disable the trigger, update rows, re-enable the trigger, and recompute hashes across the entire log in a script. This leaves no trace in the chain itself. The defense against superuser compromise is operational, not cryptographic: require two-person authorization for the database superuser role; log all superuser sessions to an external system (e.g., pgaudit with a remote log destination); store the signing key outside the database (in the macOS Keychain, per meridian/canon/keys.py) so a database compromise does not compromise the signing key. Log absence. Events that were never logged cannot be detected by the chain verifier. If the application code never called audit('read', ...) for a particular document, there is no read row, and the verifier cannot detect the absence. The verifier proves the log is intact; it cannot prove the log is complete. Completeness is enforced by code coverage and review — every code path that touches pii_tier >= internal must call audit(). JSONB serialization version fragility. PostgreSQL's jsonb::text cast — used in the trigger's hash input — produces key-ordered JSON, but this ordering is not guaranteed stable across major Postgres version upgrades. A migration from Postgres 15 to Postgres 18 that changes the serialization order of any payload field will cause the chain verifier to report TAMPERED for those rows even though the data is authentic. This is a real false-positive risk, not a theoretical one. Before any major Postgres version cutover, run SELECT * FROM verify_audit_chain() on a read-replica and resolve any breaks before they enter the production record. > ☉ In the Wild — The Theranos quality-control logs. > > Theranos reported normal quality-control results from its proprietary > Edison analyzers while, internally, QC tests on those machines were > failing. Theranos was simultaneously running QC tests on standard Siemens > instruments, which showed acceptable results — but those results were > being reported against the Edison tests they did not represent. > > The fraud was discovered partly by comparing internal QC logs to the > results submitted to regulators. Internal logs had been selectively > edited: results that fell outside acceptable ranges were modified or > removed. The edited logs were self-consistent; there was no hash chain to > break. Investigators found the discrepancy only because external records — > the Siemens instruments' own logs and shipping records for reagent > quantities — did not match the Edison logs' claimed volume. > > A hash-chained log makes the selective-editing approach immediately > detectable at the entry point. Modifying any entry breaks the chain at > that entry. The adversary must either leave the chain broken (which the > verifier catches) or recompute every subsequent hash (which requires > disabling the trigger and has no legitimate use case). The question is > not whether hash chains would have stopped Theranos — a motivated and > well-resourced adversary would have found a path — but whether they raise > the cost of the attack above the threshold that a typical insider or > record-custodian can clear. They do. ## The running case The evidentiary hearing in the 2026 TPR proceeding reaches day three. The parent's counsel has submitted fifteen attestations spanning three source systems: Gmail exports, iMessage threads, and DHS casefile PDFs. Opposing counsel's cross-examination targets one question: "How do we know these attestations were issued before the hearing date? How do we know they weren't generated last week?" The forensic expert opens a terminal. The database connection uses a read-only role — expert in the RBAC model, which has SELECT on all tables but no INSERT, UPDATE, or DELETE.

SELECT * FROM verify_audit_chain();

One row returns: status = INTACT, first_bad_id = NULL. The expert turns to the court: "Every attestation emission in this matter is in the audit log. The log is hash-chained — each row's hash incorporates the hash of the row before it. If any row had been modified or deleted after insertion, the verification would fail at that row and report which row failed. It does not fail. The chain is intact from the first event to the present." Opposing counsel: "How do we know the clock was accurate?" The expert: "The database server synchronizes via NTP. The occurred_at timestamps are consistent with the email metadata timestamps — the Gmail export shows an email received at 14:32 UTC on March 4th; the audit log shows the attestation over that email emitted at 14:33 UTC on March 4th, sixty-one seconds later. If the clock had been manipulated, the internal consistency of timestamps across six independent source systems would not hold." This is the argument the hash chain enables. It does not prove the timestamps are correct in an absolute sense — NTP can be wrong, a motivated insider can set a system clock. What it proves is internal consistency: the log has not been modified since those timestamps were recorded, and those timestamps are consistent with external records from systems the adversary did not control. > ✻ Try This — Tamper and detect. > > Set up a minimal audit_log table with the schema above (you can use > a local Postgres or a Supabase instance). Insert three rows by calling > the audit() function with distinct actions: 'login', 'read', > 'attest'. Run SELECT * FROM verify_audit_chain() — it should return > INTACT.

Now connect as a superuser and run:

UPDATE audit_log SET payload = '{"tampered": true}' WHERE id = 2;

Run verify_audit_chain() again. Which row does it report as TAMPERED?

Now restore the original payload and instead delete row 2:

DELETE FROM audit_log WHERE id = 2;

Run verify_audit_chain() again. Which row is now first reported as bad? > Why is it not row 2 — which no longer exists? > > The answer illuminates the chain's behavior on deletion: row 2 is gone, > so the verifier's cursor jumps from row 1 to row 3. Row 3's hash was > computed with row 2's hash as its prior input. The verifier, having > processed row 1 last, holds row 1's hash as running_hash. When it > recomputes row 3's expected hash using row 1's hash as prior, the result > does not match the stored hash — which was computed using row 2's hash > as prior. Row 3 is the first row the verifier sees, and row 3 is the > first failure. ## Operational considerations Two implementation concerns the trigger's comment acknowledges explicitly. High write contention. The trigger's SELECT hash INTO prev FROM audit_log ORDER BY id DESC LIMIT 1 is a point-in-time read. In a multi-writer scenario, two concurrent INSERT transactions can both read the same prior hash, producing two row-3s with identical prev_hash values and different hash values. The chain forks and the verifier fails at the second row-3. At Meridian-Cannon's current scale — one owner, a small set of background workers — this race does not occur: the workers serialize their audit writes through the audit() function, and transaction-level serialization prevents it. If the system scales to concurrent multi-user writes, add an advisory lock around the INSERT: SELECT pg_advisory_xact_lock(hashtext('audit_chain')) before the insert. Timezone consistency. occurred_at::text in the trigger produces a timestamptz in the session's TimeZone setting. If two writers connect with different TimeZone settings (e.g., one UTC, one America/Chicago), the text representation of the same timestamp differs, and the hash recomputed by the verifier — which must replicate the session's timezone at write time — would not match. The operational fix is a database-level default: ALTER DATABASE litigation SET TimeZone = 'UTC'. Every connection then serializes timestamps as UTC, and the verifier connects with SET TimeZone = 'UTC' to replicate the behavior.

Rekor / Sigstore transparency log

v0.2.0 adds an optional third tamper-detection layer: publication to a public, append-only transparency log via the Rekor API (Sigstore).

Install the optional extra:

pip install meridian-canon[transparency]

Enable with MERIDIAN_REKOR_ENABLED=1. When disabled (the default for local development), publish_attestation() returns immediately with status="disabled" and no network call is made. What Rekor adds. Rekor is a Merkle-tree log operated by Sigstore (rekor.sigstore.dev). When you publish a sealed attestation to Rekor, you receive back an entry_uuid and a log_index. Anyone can later confirm that the attestation was sealed before a given timestamp by querying Rekor — without relying on your own clock or database. The rekor_entries table (created by schema/E0_rekor.sql) stores seal_id, entry_uuid, log_index, rekor_url, and integrated_time. API (meridian.canon.transparency):

from meridian.canon.transparency import publish_attestation, verify_log_entry

result = publish_attestation(
    sealed,                    # the DSSEEnvelope after emit_dsse()
    public_key_pem=pem_bytes,
    rekor_url="https://rekor.sigstore.dev",
)
print(result.entry_uuid)   # e.g. "24296fb24b8ad77a..."
print(result.log_index)    # integer position in the log

entry = verify_log_entry(result.entry_uuid)  # dict from Rekor API

Audit log integration. Publication to Rekor generates a seventh mandatory audit event when the feature is enabled: event_type = "rekor_published". Its payload follows the same pattern as other audit events:

{"entry_uuid": "24296fb24b8ad77a...", "log_index": 12345678}

This makes Rekor publication itself part of the hash-chained audit record. An auditor can verify: (1) the attestation was sealed (the attest row); (2) it was published to Rekor (the rekor_published row); (3) Rekor confirms the entry is in the tree (via verify_log_entry). Three independent records, each hard to forge independently. Privacy. The public Rekor instance makes entries publicly searchable by entry_uuid. If the attestation payload is sensitive, run a private Rekor instance, or publish only the envelope hash rather than the full payload. The publish_attestation() function accepts a payload_only_hash flag for this purpose. > ◆ Going Deeper — Merkle tree vs. hash chain, revisited. > > The audit log uses a private hash chain: O(N) verification, single > authoritative copy. Rekor uses a public Merkle tree: O(log N) inclusion > proofs, distributed witnesses, no single point of control. These two > structures serve different adversary models. The hash chain detects insiders > who tamper with your own database. Rekor provides third-party, tamper-evident > timestamping that your database superuser cannot retroactively alter. > Running both is not redundancy — it closes two different attack surfaces. ## Connecting forward Chapter 25 is the reference verifier — the seven-step protocol applied to a sealed Canon attestation. The audit log and the attestation are two independent tamper-detection layers, and they are connected: the attest audit row records the attestation_id and chain_hash of the emitted attestation in its payload column. When Rekor is enabled, a third layer joins the chain. A complete integrity check runs all three: 1. SELECT * FROM verify_audit_chain() — confirms the audit log is intact. 2. python -m meridian.canon.walk <attestation.json> — confirms each attestation's cryptographic seal is valid. 3. verify_log_entry(entry_uuid) — confirms the Rekor entry is in the public Merkle tree (when applicable). If all three pass, the timeline established by the log, the content established by the attestation, and the third-party timestamp from Rekor are each verifiably unmodified. If any fails, the failure locates the compromise. The capstone in Chapter 27 assembles both verifiers into the admissibility report — a document a forensic expert can hand to a judge that walks through both checks and records the output.

ALTER DATABASE <your_db_name> SET TimeZone = 'UTC';

Then reconnect. Without this, the hash-chain trigger's occurred_at::text output varies by session timezone setting, and the chain verification SQL will produce false chain-break results across reconnections. Every writer and every verifier must serialize timestamptz values identically; UTC is the only safe default. ### Warm-up 1. Read schema/10_core.sql end-to-end. For each column in audit_log, write one sentence explaining why it is included in the hash input. Which column would you most regret omitting, and why? 2. The trigger uses clock_timestamp() for occurred_at. Write a SQL query that inserts two audit rows inside a single BEGIN ... COMMIT block and confirms that their occurred_at values differ. Then replace clock_timestamp() with now() and repeat. What changes? ### Core 3. Implement verify_audit_chain() as written in this chapter. Insert ten rows, tamper with row 5's payload, and run the verifier. Confirm it reports first_bad_id = 5. 4. The audit() convenience function does not log ip_address or user_agent. Extend it to accept optional parameters for both. Update the trigger to include them in the hash input. What happens to existing rows' hashes if you change the hash input formula? 5. The action column is text NOT NULL with no enumeration constraint. Add a CHECK constraint that restricts action to the six mandatory action types described in this chapter. What are the trade-offs? ### Stretch 6. Write a Python script that reads all rows from audit_log and verifies the chain without using the verify_audit_chain() SQL function. The script should replicate the hash input exactly — including jsonb::text serialization — and report the same first-failure row. Where does the serialization most likely diverge? 7. Implement the advisory-lock solution to the high-concurrency write race described in this chapter. Write a test that spawns ten concurrent writers and confirms the chain is intact and unforked after all ten complete. What is the performance cost of the lock at 100 concurrent writers? 8. Consider the superuser-access threat. Design an external monitoring system — separate from the Postgres instance — that would detect a superuser recompute. What external anchor would you use? Where would you store it? ## Build-your-own prompt For your own matter: identify the six most important event types in your evidence workflow that are not currently logged. For each, write the audit() call — specifying the action string, the resource_type, the resource_id, and the minimum payload content. Then trace the call path: which function in which worker file would make this call, and what would ensure it is called on every code path that triggers the event? Your answer connects directly to Chapter 27's admissibility report: the auditor must enumerate what the log does not contain and why. A well-considered answer here is the raw material for that section. ## Further reading - schema/10_core.sql — the authoritative table and trigger definition. - schema/99_rls.sql — where DELETE and UPDATE are revoked on audit_log. - meridian/canon/emit.py — the audit row written on every attestation emission.

• Russ Cox, Transparent Logs for Skeptical Clients, https://research.swtch.com/tlog — the foundational article on append-only hash-linked logs.
• Ben Laurie, Certificate Transparency, RFC 6962 — the production deployment of the same idea at internet scale.
• Google Trillian-Tessera, https://github.com/transparency-dev/trillian-tessera — the Merkle log infrastructure referenced in the Going Deeper sidebar.
• NIST SP 800-92, Guide to Computer Security Log Management — the federal standard for log integrity requirements.
• John Carreyrou, Bad Blood (2018), chapters 15–18 — the Theranos investigation, including the QC log discrepancies.
• FRE 803(6) — business-records exception; FRE 902(13) — self-authentication of certified records generated by an electronic process or system.

Next: Chapter 25 — The Reference Verifier (Seven-Step Protocol). Where the audit log proves the timeline; the verifier proves the content.