PostGIS Lineage Schema Design

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.

Configuration reference

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.

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.