Designing a PostGIS Lineage Audit Table

Part of: PostGIS Lineage Schema Design

An append-only table stops accidental overwrites, but on its own it cannot prove that no row was ever quietly rewritten by someone with elevated privileges. This how-to adds tamper evidence: an audit table where every row carries a SHA-256 hash of its own contents chained to the hash of the previous row, plus a BEFORE UPDATE OR DELETE trigger that rejects mutation outright. It extends the core PostGIS Lineage Schema Design with a structure that lets an auditor detect a single altered byte anywhere in the history.

Prerequisites

  • PostgreSQL 15+ with PostGIS 3.4+ and the pgcrypto extension enabled.
  • Python 3.10+ with psycopg 3.1+ for the verification client.
  • A database role permitted to create tables, functions, and triggers.
  • The dataset and process_step tables from the parent schema already present (the audit table references their identifiers).
Why a separate audit table at all The entity tables are already append-only; the audit table adds a total order, a hash chain and an externally anchored head. Entity tables already give you append-only, enforced by trigger referential integrity across edges per-row digests of the artefacts enough for most questions The audit table adds a total order across all tables a hash chain linking row to row a head that can be published externally the three things AU-9 actually needs Do not duplicate the entity data here — record a digest and a reference, not a copy.

The warning in the caption is the design error that makes audit tables unmanageable. Copying entity rows into the audit table doubles storage, doubles write cost, and creates two representations that can disagree — at which point the audit table is a second source of truth rather than a proof about the first. Store the entity type, its primary key, a digest of its canonical serialisation, and the chain fields. Everything else is already one join away.

Canonicalising the serialisation before hashing is the part that needs care. Two logically identical rows must produce the same digest regardless of column order, JSON key order, or how a timestamp happened to be formatted by the driver, or verification will fail on rows nobody touched. Serialise to a fixed field order with sorted JSON keys and timestamps normalised to UTC ISO 8601, and write that function once so the writer and the verifier cannot drift apart — a chain whose two ends disagree about canonical form is worse than no chain, because it produces false alarms that train people to ignore it, and an integrity check nobody believes is worth less than none at all.

Total ordering is the property that justifies a separate table rather than a chain column on each entity table. Chains per table cannot answer “what happened across the system between these two timestamps” without merging orderings that have no defined relationship, and an assessor’s questions are almost always system-wide rather than table-scoped.

Implementation

The audit table stores one immutable event per row. Each row computes row_hash from its own payload columns concatenated with the prev_hash of the row before it, forming a hash chain: change any historical row and every subsequent row_hash fails to recompute. A BEFORE INSERT trigger fills in the chain server-side so clients cannot forge it, and a second trigger blocks UPDATE and DELETE.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE lineage_audit (
    seq        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_time timestamptz NOT NULL DEFAULT clock_timestamp(),
    actor      text        NOT NULL,
    operation  text        NOT NULL,          -- e.g. 'REPROJECT', 'CLIP'
    dataset_id uuid        NOT NULL,
    extent     geometry(Polygon, 4326),
    payload    jsonb       NOT NULL DEFAULT '{}'::jsonb,
    prev_hash  char(64)    NOT NULL,
    row_hash   char(64)    NOT NULL
);

-- Compute the chained hash before the row is written.
CREATE OR REPLACE FUNCTION lineage_audit_chain() RETURNS trigger AS $$
DECLARE
    last_hash char(64);
    canonical text;
BEGIN
    SELECT row_hash INTO last_hash
    FROM lineage_audit
    ORDER BY seq DESC
    LIMIT 1;

    -- Genesis row chains from 64 zeroes.
    NEW.prev_hash := COALESCE(last_hash, repeat('0', 64));

    canonical := NEW.event_time::text || '|' || NEW.actor || '|'
              || NEW.operation || '|' || NEW.dataset_id::text || '|'
              || COALESCE(ST_AsText(NEW.extent), '') || '|'
              || NEW.payload::text || '|' || NEW.prev_hash;

    NEW.row_hash := encode(digest(canonical, 'sha256'), 'hex');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER lineage_audit_chain_bi
    BEFORE INSERT ON lineage_audit
    FOR EACH ROW EXECUTE FUNCTION lineage_audit_chain();

-- Block any mutation of a written row.
CREATE OR REPLACE FUNCTION lineage_audit_freeze() RETURNS trigger AS $$
BEGIN
    RAISE EXCEPTION 'lineage_audit is append-only; % rejected', TG_OP
        USING ERRCODE = 'restrict_violation';
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER lineage_audit_freeze_bud
    BEFORE UPDATE OR DELETE ON lineage_audit
    FOR EACH ROW EXECUTE FUNCTION lineage_audit_freeze();

Note the use of clock_timestamp() rather than now(): within a batch transaction, now() returns the same value for every row, which is fine for the chain but obscures true event ordering. clock_timestamp() advances per statement. The canonical string uses ST_AsText so the geometry contributes to the hash in a stable textual form; if you need bit-exact geometry hashing across PostGIS versions, substitute ST_AsBinary and hash the bytea directly.

The Python side simply inserts events; the server computes the chain, so the client never sets prev_hash or row_hash.

from __future__ import annotations

import json

import psycopg


def append_audit_event(
    conn: psycopg.Connection,
    *,
    actor: str,
    operation: str,
    dataset_id: str,
    extent_wkt: str | None,
    payload: dict,
) -> int:
    """Append one tamper-evident audit event; returns its sequence number."""
    row = conn.execute(
        """
        INSERT INTO lineage_audit (actor, operation, dataset_id, extent, payload)
        VALUES (
            %s, %s, %s,
            CASE WHEN %s IS NULL THEN NULL ELSE ST_GeomFromText(%s, 4326) END,
            %s::jsonb
        )
        RETURNING seq
        """,
        (actor, operation, dataset_id, extent_wkt, extent_wkt, json.dumps(payload)),
    ).fetchone()
    conn.commit()
    return row[0]
The chain, and what breaks when a row is altered Three chained audit rows; editing the middle one changes its hash, so the third row's prev_hash no longer matches and verification fails from that point on. seq 1 prev: genesis self: a41f… seq 2 — ALTERED prev: a41f… self: was b2c8… now 91de… seq 3 prev: b2c8… ✗ no longer matches seq 2 Verification is a single pass: recompute each row's hash and compare to the next row's prev_hash. The first mismatch localises the tampering to one row — which is more than a bare digest of the table gives you. An attacker who can rewrite the whole chain still cannot match the head you published externally.

Two properties make the chain worth its cost over a simpler scheme. It localises: a single verification pass identifies which row was altered rather than telling you that something, somewhere, changed. And it is append-friendly — adding a row requires reading only the previous hash, not rehashing the table — so verification cost is linear and insertion cost is constant.

Its limitation is the one the caption names. Anyone who can rewrite one row can recompute every subsequent hash and produce a self-consistent chain, which is why the head must be published somewhere they cannot reach. That external anchor is the whole point; the chain without it demonstrates internal consistency and nothing about integrity.

Verification

First, prove the table is truly append-only by attempting a mutation and observing the rejection:

-- Should fail with: lineage_audit is append-only; UPDATE rejected
UPDATE lineage_audit SET actor = 'tamperer' WHERE seq = 1;

-- Should fail with: lineage_audit is append-only; DELETE rejected
DELETE FROM lineage_audit WHERE seq = 1;

Second, recompute the whole chain and confirm that every stored row_hash matches. This query walks the rows in order, rebuilds each canonical string using the previous row’s stored hash, and returns only rows where the recomputed hash disagrees — an empty result means the chain is intact:

WITH recomputed AS (
    SELECT
        seq,
        row_hash AS stored,
        encode(digest(
            event_time::text || '|' || actor || '|' || operation || '|'
            || dataset_id::text || '|' || COALESCE(ST_AsText(extent), '') || '|'
            || payload::text || '|'
            || LAG(row_hash, 1, repeat('0', 64)) OVER (ORDER BY seq),
            'sha256'), 'hex') AS rebuilt
    FROM lineage_audit
)
SELECT seq, stored, rebuilt
FROM recomputed
WHERE stored <> rebuilt;

If an attacker altered row 5, that row and every row after it would appear in the result set, because each subsequent prev_hash no longer matches. A weekly job that runs this query and alerts on any output gives you continuous tamper detection.

Sequencing, Concurrency and the Genesis Row

Concurrent appends must be serialised Two writers reading the same previous hash produce a forked chain; an advisory lock or single-writer path makes the read-and-append atomic. UNSERIALISED — both read the same prev_hash writer A reads a41f… writer B reads a41f… two rows both claiming the same parent the chain has forked; verification fails SERIALISED — advisory lock around read-and-append A takes the lock B waits, then reads one linear chain, deterministic order The audit chain is the one place in a lineage system where a serialisation bottleneck is acceptable.

Serialising appends caps throughput on the audit table, and that is the correct trade. The chain only needs to carry summary rows — one per batch, or one per step for a Tier 1 dataset — not every event the pipeline produces, so the volume through the serialised path is a fraction of total lineage writes. Sizing it that way keeps the lock uncontended in practice while preserving the property that makes the chain verifiable.

The genesis row deserves an explicit decision rather than a null. A first row whose prev_hash is null works, and it means a verifier cannot distinguish “this is the start of the chain” from “somebody deleted everything before this”. Use a fixed, documented sentinel value instead, and record the chain’s creation as an event of its own — so the beginning of the chain is itself an assertion rather than an absence.

Gotchas & edge cases

  • Geometry text stability. ST_AsText output can vary in coordinate precision across PostGIS point releases. Pin the precision explicitly with ST_AsText(extent, 8) in both the trigger and the verification query, or hash ST_AsEWKB bytes, so an engine upgrade never invalidates an otherwise-untouched chain.
  • NULL extents. Non-spatial audit events have a null extent. The COALESCE(..., '') in the canonical string keeps those rows hashable; if you forget it, digest() receives a NULL and the whole concatenation becomes NULL, producing an unverifiable row.
  • Concurrent inserts. Two transactions inserting at once can both read the same “last” row and chain from it, forking the hash chain. Serialize appends with an advisory lock (pg_advisory_xact_lock) inside the trigger, or route all audit writes through a single queue, if strict linearity matters for your audit posture.