PostGIS Lineage Schema Design

Part of: Storage, Indexing & Query Optimization

Relational databases remain the default system of record for most geospatial agencies, and PostGIS is the natural home for spatial provenance when the derivation history must live alongside the data it describes. The challenge is that lineage is a graph problem wearing a table-shaped costume: datasets are derived from other datasets through process steps, each step consumes sources and emits products, and the whole structure forms a directed acyclic graph (DAG) that a naive schema will flatten into unqueryable join soup. A deliberate schema — with dedicated tables for datasets, process steps, and sources, geometry columns for spatial extents, jsonb for process parameters, and foreign keys that encode the DAG edges — turns that costume into a genuine, auditable model.

This guide sits under the Storage, Indexing & Query Optimization overview and fills a specific gap: how to lay out the physical PostGIS schema so that provenance is immutable, spatially indexed, and compliant by construction. It complements the graph-native approach covered in Graph Databases for Lineage Graphs; if you have not yet decided which engine fits your workload, the trade-offs are weighed in PostGIS vs Neo4j for Spatial Lineage. Here we assume PostGIS has won and focus entirely on getting the tables, indexes, and triggers right.

Prerequisites

• PostgreSQL 15+ with the PostGIS 3.4+ extension available (CREATE EXTENSION postgis;).
• The pgcrypto extension for server-side digest() hashing, or client-side hashing in Python.
• Python 3.10+ with psycopg 3.1+ installed for the ingestion client shown below.
• A decided project-wide storage CRS (this guide standardizes on EPSG:4326 for extents; reproject on the way in).
• A DBA or migration role with rights to create tables, indexes, and triggers in the target schema.
• Agreement on which fields are immutable provenance facts versus mutable annotations, following the separation described in Structuring JSON/XML Lineage Documents.

Step-by-step

1. Create the core tables

The schema has three entity tables. source records external inputs the agency does not itself produce, process_step records a single transformation with its parameters, and dataset records a product. Every product points at the step that produced it, and every step points at the inputs it consumed through an association table — together these foreign keys are the edges of the derivation DAG. Spatial extents are stored as native geometry(Polygon, 4326) columns rather than raw coordinates so the planner can use spatial operators such as ST_Intersects.

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE source (
    source_id    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    origin_uri   text NOT NULL,
    media_type   text NOT NULL,
    extent       geometry(Polygon, 4326),
    sha256       char(64) NOT NULL,
    ingested_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE process_step (
    step_id      uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    algorithm    text NOT NULL,
    version      text NOT NULL,
    parameters   jsonb NOT NULL DEFAULT '{}'::jsonb,
    actor        text NOT NULL,
    run_at       timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE dataset (
    dataset_id   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    label        text NOT NULL,
    produced_by  uuid REFERENCES process_step(step_id),
    extent       geometry(Polygon, 4326),
    srid         integer NOT NULL,
    sha256       char(64) NOT NULL,
    created_at   timestamptz NOT NULL DEFAULT now()
);

-- Association table: the edges consumed by each step (source OR dataset).
CREATE TABLE step_input (
    step_id       uuid NOT NULL REFERENCES process_step(step_id),
    source_id     uuid REFERENCES source(source_id),
    input_dataset uuid REFERENCES dataset(dataset_id),
    role          text NOT NULL,
    CONSTRAINT one_input_kind CHECK (
        (source_id IS NOT NULL) <> (input_dataset IS NOT NULL)
    ),
    PRIMARY KEY (step_id, source_id, input_dataset)
);

The CHECK constraint enforces that each edge references exactly one kind of input, preventing ambiguous rows where both columns are populated or both are null.

2. Add spatial and temporal indexes

Two access patterns dominate lineage queries: “what touched this region” and “what ran in this time window”. Serve the first with a GiST index on every geometry column, and the second with a BRIN index on the naturally-ordered timestamp columns. BRIN is the right tool for append-only ingestion timestamps because the physical row order correlates with time, giving you a tiny index that still prunes effectively. A partial GiST index skips rows with no extent, which is common for non-spatial reference sources.

CREATE INDEX dataset_extent_gix ON dataset USING gist (extent)
    WHERE extent IS NOT NULL;
CREATE INDEX source_extent_gix  ON source  USING gist (extent)
    WHERE extent IS NOT NULL;

CREATE INDEX step_run_at_brin   ON process_step USING brin (run_at)
    WITH (pages_per_range = 32);
CREATE INDEX dataset_created_brin ON dataset USING brin (created_at);

-- Accelerate parameter lookups on the JSONB column.
CREATE INDEX step_params_gin ON process_step USING gin (parameters jsonb_path_ops);

-- The DAG-walk join columns.
CREATE INDEX dataset_produced_by_idx ON dataset (produced_by);
CREATE INDEX step_input_step_idx     ON step_input (step_id);

Index selection and maintenance for these access paths is treated in depth in the companion guide on tuning GiST and BRIN indexes for lineage.

3. Enforce immutability with a trigger

Provenance facts must not change after they are written. Rather than trusting application code, enforce append-only semantics in the database itself with a BEFORE UPDATE OR DELETE trigger that raises an exception. Attach it to the entity tables so that any attempt to rewrite history — accidental or malicious — fails loudly and leaves a Postgres error in the log.

CREATE OR REPLACE FUNCTION reject_mutation() RETURNS trigger AS $$
BEGIN
    RAISE EXCEPTION 'Table % is append-only; % rejected on %',
        TG_TABLE_NAME, TG_OP, now()
        USING ERRCODE = 'restrict_violation';
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER dataset_immutable
    BEFORE UPDATE OR DELETE ON dataset
    FOR EACH ROW EXECUTE FUNCTION reject_mutation();

CREATE TRIGGER process_step_immutable
    BEFORE UPDATE OR DELETE ON process_step
    FOR EACH ROW EXECUTE FUNCTION reject_mutation();

If a dataset genuinely supersedes another, write a new row and link it — never mutate the old one. The full append-only pattern, extended with a tamper-evident hash chain, is built step by step in designing a PostGIS lineage audit table.

4. Ingest records from Python

The ingestion client hashes the payload, reprojects the extent to the storage CRS, and writes the source, step, product, and edges inside a single transaction so a partial failure never leaves an orphaned product. Using psycopg 3 with a parameterized query keeps the geometry as well-known text (WKT) that PostGIS parses with ST_GeomFromText.

from __future__ import annotations

import hashlib
import json
from typing import Any

import psycopg


def ingest_derivation(
    conn: psycopg.Connection,
    *,
    label: str,
    algorithm: str,
    version: str,
    parameters: dict[str, Any],
    actor: str,
    extent_wkt: str,
    payload: bytes,
    source_ids: list[str],
) -> str:
    """Insert one process step and its product atomically; return the dataset UUID."""
    digest = hashlib.sha256(payload).hexdigest()
    with conn.transaction():
        step_id = conn.execute(
            """
            INSERT INTO process_step (algorithm, version, parameters, actor)
            VALUES (%s, %s, %s::jsonb, %s)
            RETURNING step_id
            """,
            (algorithm, version, json.dumps(parameters), actor),
        ).fetchone()[0]

        for src in source_ids:
            conn.execute(
                """
                INSERT INTO step_input (step_id, source_id, role)
                VALUES (%s, %s, 'primary')
                """,
                (step_id, src),
            )

        dataset_id = conn.execute(
            """
            INSERT INTO dataset (label, produced_by, extent, srid, sha256)
            VALUES (%s, %s, ST_GeomFromText(%s, 4326), 4326, %s)
            RETURNING dataset_id
            """,
            (label, step_id, extent_wkt, digest),
        ).fetchone()[0]
    return str(dataset_id)

Because the immutability trigger blocks UPDATE, the client must get each row right on the first insert — validate against the document schema in Structuring JSON/XML Lineage Documents before calling ingest_derivation.

The Edge Table Is the Design

The role column is the part most schemas omit and later wish they had. Every input to a step is not equally causal: a parcel layer being reprojected is the primary input, while a datum grid or a lookup table is a reference that shaped the result without being derived from. Recording that distinction at write time costs a text column; reconstructing it afterwards is guesswork, and without it impact analysis treats a shared reference table as an ancestor of everything, producing the same over-connection problem that afflicts badly modelled graphs.

Keep the association table narrow and let it be long. Millions of edge rows with three columns index and join efficiently; the temptation to denormalise attributes onto the edge — copying the input’s extent or digest for convenience — produces a table that must be updated when the input changes, which contradicts the append-only property the rest of the schema is built to guarantee.

Configuration reference

Choosing the Storage CRS

The schema above types every geometry column as geometry(Polygon, 4326), and that choice deserves justification rather than inheritance, because the arguments against it are real.

WGS 84 is the right default for lineage extents specifically. Extents are used for coarse spatial filtering — “what touched this region” — not for measurement, so the distortion that makes a geographic CRS unsuitable for area and distance calculations does not matter here. In exchange you get a single CRS across the whole estate, which means extents from datasets in a dozen different working projections are directly comparable without transformation at query time.

The counter-argument applies when lineage extents are used for anything metric. If a query needs to find records within five kilometres of a point, a geographic CRS forces either a spherical distance function or an on-the-fly transformation, both of which defeat the GiST index unless carefully written. Where that access pattern dominates, store a second geometry column in a suitable projected CRS and index both, rather than switching the canonical one.

What must not happen is a mixed estate. A geometry column typed without an SRID constraint will happily accept extents in whatever CRS the writer had, and ST_Intersects between two geometries with different SRIDs raises in some cases and returns nonsense in others. Constraining the type is what makes the reprojection happen at ingestion, where it is visible and can be logged, rather than at query time where it silently does not.

Common failure modes & mitigations

Compliance & governance alignment

For the full mapping of these regimes to lineage fields, see the regulatory overview at Regulatory Compliance Standards Mapping and the ISO 19115 lineage implementation guide.

Why the Trigger, and Not Just Permissions

The reason to have all three is that they fail to different adversaries. Permissions are administrative and can be changed by whoever administers them; the trigger is inside the database and survives a permission change but not a superuser who drops it; the chain stops nothing at all but makes any successful modification detectable afterwards. An implementation with only permissions is protecting against mistakes, which is worth doing and is not the claim an audit-record-protection control makes.

Note the ordering of cost, too. Revoking permissions is free, the trigger is a dozen lines, and the chain is the only one requiring ongoing operational work — publishing heads somewhere outside the database’s own control. That is why the chain tends to be skipped, and why skipping it is the difference between a system that resists accidents and one that produces evidence.

Where to go next

With the core schema in place, three follow-on tasks harden it for production: a tamper-evident audit table with a hash chain, spatial partitioning of high-volume lineage tables by ingestion month, and bitemporal versioning of lineage rows so historical states remain queryable. Each builds directly on the tables defined here. If you later find that recursive DAG walks dominate your workload, revisit the engine choice in PostGIS vs Neo4j for Spatial Lineage and the graph-native patterns in Graph Databases for Lineage Graphs.

Frequently Asked Questions

Related

• Designing a PostGIS Lineage Audit Table — the tamper-evident hash chain
• Spatial Partitioning for Lineage Tables — partitioning at volume
• Versioning Lineage Rows with Temporal Tables — valid time versus transaction time
• Spatial Index Tuning for Provenance Queries — GiST and BRIN in practice
• PostGIS vs Neo4j for Spatial Lineage — when to add a graph engine
• Part of: Storage, Indexing & Query Optimization