Temporal Versioning & Snapshots

A zoning dataset that only knows its current state cannot answer the questions that matter for underwriting, litigation, or public accountability. When a developer asks “what was this parcel zoned when we signed the option in March,” when a planner needs to prove a variance was granted before an amendment took effect, or when a county silently backfills a correction three months late, a table that was overwritten in place has already lost the answer. Temporal versioning is the discipline that fixes this: instead of mutating a parcel row when its zoning changes, you append a new version and preserve the old one, so the history becomes an immutable, queryable asset rather than a casualty of the next refresh. This topic sits inside the Spatial Impact Analysis & Zoning Change Detection area and supplies the temporal backbone that everything downstream leans on — you cannot diff, alert on, or attribute a change you did not preserve. The goal here is narrow and demanding: any zoning fact must be provable, as-of-queryable at any past instant, and reversible without ever destroying what was previously recorded.

Bitemporal snapshot architecture for municipal zoning history An incoming refresh compares against the current versions; unchanged parcels are skipped, changed parcels close out the prior version by setting its valid-to bound and insert a new version. Every write records two time axes: valid time, when the zoning was in effect on the ground, and transaction time, when the system learned it. A GiST exclusion constraint prevents overlapping valid-time versions per parcel. As-of queries read the history table at any past instant on either axis; a retention job prunes superseded transaction-time rows past a defined horizon while keeping the valid-time timeline intact. Immutable snapshot files are written to versioned object storage in GeoParquet as a durable second copy. changed? unchanged → skip append version Refresh feed validated parcels Compare vs current Close-out set valid_to Insert version 2 time axes History table valid_time · txn_time GiST exclusion constraint As-of query any past instant either axis Snapshot store GeoParquet · versioned immutable second copy Retention / pruning superseded txn rows past horizon Append, never overwrite — two time axes make every zoning fact provable
Changed parcels close out their prior version and append a new one carrying both valid time and transaction time; an exclusion constraint guards the timeline, as-of queries read any past instant, and retention prunes superseded rows without erasing valid-time history.

Prerequisites and operational context

Temporal versioning is a layer you add on top of an ingestion stack that is already correct in the present tense. If today’s snapshot is wrong, a perfect history simply preserves the wrong answer forever. Several things should be in place before you turn it on:

  • A stable parcel identity. Every version must attach to a durable business key — usually a jurisdiction-qualified parcel_id — that survives re-plats and re-exports. This is exactly the identity contract established in municipal data structures; if the key drifts between refreshes, versioning will fabricate spurious “changes” that are really just renumbered rows.
  • A single working CRS. Geometry stored across versions must share one projection so that a diff against an old version measures real boundary movement, not a datum shift. Fix this upstream before the first snapshot lands.
  • Provenance on every record. Each version needs to know which feed, run, and payload hash produced it. That comes from data lineage & provenance tracking, and it is what lets you distinguish a genuine municipal amendment from an ingestion artifact when the two look identical in the data.
  • Indexed spatial and temporal columns. History tables grow without bound, and an unindexed as-of query will table-scan millions of dead rows. The GiST and B-tree strategy from spatial database indexing & performance is a hard prerequisite, not a later optimization.
  • A retention policy on paper. Decide, before you accumulate years of versions, what must be kept forever (the valid-time timeline) and what may be pruned (superseded transaction-time rows past a horizon). Retention that is invented after the fact tends to delete the wrong axis.

With those in place, versioning becomes additive: the ingestion path barely changes, but every write now leaves an auditable trail behind it.

Architecture: two time axes, immutable versions

The conceptual core of this topic is bitemporal modeling — the recognition that a zoning fact lives on two independent time axes, and that conflating them is the source of nearly every “our history is wrong” bug.

Valid time answers when was this true in the real world. A rezoning ordinance that takes effect on 1 April 2026 has a valid-time start of that date, regardless of when your pipeline saw it. Transaction time (also called system or knowledge time) answers when did our system believe it. If your crawler ingested that ordinance on 15 April, its transaction-time start is the 15th. The two axes diverge constantly in municipal data: councils publish amendments retroactively, counties backfill corrections weeks late, and portals occasionally re-issue a parcel with a datum fix that changes geometry but not zoning. A system that records only one axis cannot answer “what did we think this parcel was zoned on 10 April, and what do we now know it was actually zoned then” — and that exact double question is what an auditor or opposing counsel will ask.

The physical model that carries both axes is an append-only history table. Every parcel version is a row bounded by two ranges:

  • a valid-time range, [valid_from, valid_to), expressed as a PostGIS tstzrange, half-open so consecutive versions abut without overlapping or leaving gaps;
  • a transaction-time range, [txn_from, txn_to), closed out to a sentinel far-future value while the row is the current belief.

An open version has valid_to and txn_to set to infinity. When zoning changes, you never UPDATE the geometry or code column — you close out the prior version by stamping its upper bound and INSERT a fresh row. The invariant that makes this safe is a GiST exclusion constraint: for a given parcel and transaction-time slice, no two valid-time ranges may overlap. The database itself refuses to record a contradictory timeline, which turns a class of correctness bugs into loud constraint violations instead of silent corruption.

Alongside the live history table, keep an immutable snapshot copy in versioned object storage. After each successful refresh, serialize the full current state to a GeoParquet file named by run timestamp and content hash, and write it to a bucket with object versioning enabled. This second copy is not redundant — it is your disaster-recovery and reproducibility floor. If the database is ever restored to a bad point, or a reviewer needs to reproduce a report exactly as it stood, the snapshot files reconstruct any refresh deterministically without replaying the entire ingestion pipeline. The database answers fine-grained as-of queries; the object store answers “hand me the whole world as it was on this date.”

Production implementation

Below is the schema and the two operations that define this topic: a snapshot writer that closes out and appends versions transactionally, and an as-of query that reads either time axis. The DDL establishes the bitemporal history table and its exclusion constraint; the Python driver, built on psycopg, performs an idempotent versioned upsert per refresh.

-- Bitemporal zoning history. One row per parcel version.
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE zoning_history (
    version_id    bigserial PRIMARY KEY,
    parcel_id     text        NOT NULL,          -- durable business key
    jurisdiction  text        NOT NULL,
    zoning_code   text        NOT NULL,
    geom          geometry(MultiPolygon, 4326) NOT NULL,
    -- valid time: when the zoning was/is in effect on the ground
    valid_range   tstzrange   NOT NULL,
    -- transaction time: when our system believed this version
    txn_range     tstzrange   NOT NULL DEFAULT tstzrange(now(), 'infinity'),
    source_run_id text        NOT NULL,          -- lineage back to the ingest run
    payload_hash  char(64)    NOT NULL,          -- sha256 of the source feature
    CONSTRAINT valid_range_nonempty CHECK (NOT isempty(valid_range)),
    -- No two currently-believed versions of one parcel may overlap in valid time.
    CONSTRAINT no_valid_overlap EXCLUDE USING gist (
        parcel_id  WITH =,
        txn_range  WITH &&,
        valid_range WITH &&
    )
);

CREATE INDEX zoning_history_parcel_idx ON zoning_history (parcel_id, jurisdiction);
CREATE INDEX zoning_history_valid_idx  ON zoning_history USING gist (valid_range);
CREATE INDEX zoning_history_txn_idx    ON zoning_history USING gist (txn_range);
CREATE INDEX zoning_history_geom_idx   ON zoning_history USING gist (geom);

import hashlib
import logging
from dataclasses import dataclass
from datetime import datetime, timezone
from typing import Iterable, Optional

import psycopg
from psycopg.rows import dict_row

logging.basicConfig(level=logging.INFO, format="%(asctime)s | %(levelname)s | %(message)s")
logger = logging.getLogger("temporal_versioning")


@dataclass(frozen=True)
class ParcelVersion:
    """One incoming parcel, already validated and reprojected to EPSG:4326."""
    parcel_id: str
    jurisdiction: str
    zoning_code: str
    geom_wkt: str
    valid_from: datetime          # when this zoning took effect on the ground
    source_run_id: str

    @property
    def payload_hash(self) -> str:
        # Stable content hash: any change in code or geometry changes the hash.
        body = f"{self.parcel_id}|{self.zoning_code}|{self.geom_wkt}"
        return hashlib.sha256(body.encode("utf-8")).hexdigest()


def write_snapshot(conn: psycopg.Connection, incoming: Iterable[ParcelVersion]) -> dict:
    """Append changed parcel versions bitemporally.

    For each parcel: if the current version's payload hash is unchanged, skip.
    Otherwise close out the old version (bound its valid + txn ranges) and
    insert a new one. The whole refresh is one transaction so a crash leaves
    no half-versioned parcel behind.
    """
    counts = {"inserted": 0, "closed": 0, "skipped": 0}
    now = datetime.now(timezone.utc)

    with conn.cursor(row_factory=dict_row) as cur:
        for pv in incoming:
            # Current belief = the version whose txn_range is still open.
            cur.execute(
                """
                SELECT version_id, payload_hash, lower(valid_range) AS valid_from
                FROM zoning_history
                WHERE parcel_id = %s AND jurisdiction = %s
                  AND upper_inf(txn_range)
                FOR UPDATE
                """,
                (pv.parcel_id, pv.jurisdiction),
            )
            current = cur.fetchone()

            if current and current["payload_hash"] == pv.payload_hash:
                counts["skipped"] += 1
                continue  # no real change — do not churn the history

            if current:
                # Close valid time at the new effective date; close txn time now.
                # A guard prevents an out-of-order effective date from inverting a range.
                if pv.valid_from <= current["valid_from"]:
                    logger.warning(
                        "Parcel %s effective date %s precedes current version %s; "
                        "routing as back-dated correction, not a forward change.",
                        pv.parcel_id, pv.valid_from, current["valid_from"],
                    )
                cur.execute(
                    """
                    UPDATE zoning_history
                    SET valid_range = tstzrange(lower(valid_range), %s, '[)'),
                        txn_range   = tstzrange(lower(txn_range), %s, '[)')
                    WHERE version_id = %s
                    """,
                    (pv.valid_from, now, current["version_id"]),
                )
                counts["closed"] += 1

            try:
                cur.execute(
                    """
                    INSERT INTO zoning_history
                        (parcel_id, jurisdiction, zoning_code, geom,
                         valid_range, txn_range, source_run_id, payload_hash)
                    VALUES (%s, %s, %s, ST_GeomFromText(%s, 4326),
                            tstzrange(%s, 'infinity', '[)'),
                            tstzrange(%s, 'infinity', '[)'), %s, %s)
                    """,
                    (pv.parcel_id, pv.jurisdiction, pv.zoning_code, pv.geom_wkt,
                     pv.valid_from, now, pv.source_run_id, pv.payload_hash),
                )
                counts["inserted"] += 1
            except psycopg.errors.ExclusionViolation as exc:
                # The timeline would overlap — a data or ordering bug, not routine.
                conn.rollback()
                logger.error("Timeline conflict for parcel %s: %s", pv.parcel_id, exc)
                raise

    conn.commit()
    logger.info("Snapshot written: %s", counts)
    return counts


def query_as_of(
    conn: psycopg.Connection,
    parcel_id: str,
    valid_at: datetime,
    known_at: Optional[datetime] = None,
) -> Optional[dict]:
    """Return the version in effect at `valid_at`, as known at `known_at`.

    Leaving known_at as None asks 'as best known now'. Passing a past instant
    asks 'as we believed then', which is how you reproduce a historical report
    without back-dated corrections leaking into it.
    """
    known_at = known_at or datetime.now(timezone.utc)
    with conn.cursor(row_factory=dict_row) as cur:
        cur.execute(
            """
            SELECT parcel_id, zoning_code, ST_AsText(geom) AS geom_wkt,
                   lower(valid_range) AS effective_from, source_run_id
            FROM zoning_history
            WHERE parcel_id = %s
              AND valid_range @> %s::timestamptz   -- in effect on the ground then
              AND txn_range   @> %s::timestamptz   -- and known to us then
            """,
            (parcel_id, valid_at, known_at),
        )
        row = cur.fetchone()
        if row is None:
            logger.info("No version of %s valid at %s (known at %s)",
                        parcel_id, valid_at, known_at)
        return row

Three choices in this code are deliberate. The FOR UPDATE lock on the current version serializes concurrent refreshes so two workers cannot both close out the same row and race into an exclusion violation. The payload-hash skip means an idempotent re-run of the same feed writes nothing — history only grows when zoning actually moves, which keeps the table honest and the diff downstream meaningful. And the ExclusionViolation handler treats a timeline overlap as a hard error worth a rollback rather than something to swallow, because an overlap is never routine — it always signals a bad effective date or an out-of-order ingest.

Edge cases and gotchas

Bitemporal systems fail in quiet, specific ways. Handle these before they reach production:

  • Back-dated corrections that overwrite the present. A county issues a correction saying a parcel was always R-2, effective a date already in the past. Naively closing the current version at that past date inverts a range or overlaps the timeline. The correct move is to insert the correction on the valid-time axis while opening a new transaction-time slice, so the old belief remains queryable — the full mechanism is walked through in modeling bitemporal zoning history in PostGIS.
  • now() inside a transaction is frozen. PostgreSQL’s now() returns transaction start time, so batching thousands of parcels in one transaction stamps them all with the same txn_from. That is usually what you want for a refresh, but if you rely on it to order writes within the batch, it will bite you — use an explicit application timestamp.
  • Infinity is not NULL. Model open ranges with 'infinity', not NULL upper bounds. NULL breaks the exclusion constraint’s overlap test and makes @> containment silently miss the current version.
  • Geometry-only changes. A datum fix moves the polygon without changing the zoning code. Whether that is a new version depends on your hash: including geometry in payload_hash (as above) versions it; excluding geometry treats it as cosmetic. Decide explicitly and document it, because diff and alerting downstream inherit that decision.
  • Retention that prunes the wrong axis. It is safe to prune superseded transaction-time rows past a horizon — old beliefs you will never need to reproduce. It is never safe to prune valid-time history, because that is the substantive record. Conflate them and a retention job quietly deletes the answer to “what was it zoned in 2019.”
  • Snapshot and database drift. If the GeoParquet snapshot is written outside the same transaction that commits the versions, a crash between them leaves the two copies disagreeing. Write the snapshot from the committed state, keyed to the run ID, so it can always be regenerated deterministically.

Reference: which temporal model answers which question

Temporal model Stores Question it answers When it is not enough
Current-state only Latest row, overwritten “What is it zoned now?” Cannot answer any past-tense question
Valid-time (uni-temporal) valid_from/valid_to per version “What was it zoned on date X (as best known now)?” Cannot reproduce a report as it stood before a correction
Transaction-time (uni-temporal) txn_from/txn_to per version “What did our system believe on date X?” Cannot distinguish effective date from ingest date
Bitemporal Both ranges per version “What was it zoned on X, as we believed then vs as known now?” Costs storage and query complexity; needs indexing and retention
Immutable snapshot Full-state file per run “Hand me the entire dataset exactly as of run R” Coarse-grained; not for per-parcel point queries

Integration points

Temporal versioning is defined by what it feeds. Its most direct consumer is change detection & geometry diffing, which reads two consecutive snapshots and computes what moved — a diff is only meaningful because versioning guarantees the two states it compares are immutable and precisely dated. Without a preserved prior version, “detecting a change” degenerates into trusting that the current row differs from a memory you no longer have. Upstream, each version carries the lineage stamp from data lineage & provenance tracking, so a reviewer can walk from any historical zoning fact back to the exact feed, run, and payload that produced it. And the whole model rides on the index strategy from spatial database indexing & performance: as-of queries filter on temporal ranges and a spatial predicate simultaneously, and only GiST-indexed range and geometry columns keep those queries sub-second as the history table crosses millions of rows. The contract across all three boundaries is the version row itself — same identity, same two axes, same lineage — so any stage can be rebuilt from the history without touching the others.

Compliance and audit artifacts

For underwriting, litigation support, or FOIA-style transparency requests, a zoning history is only worth as much as its provability. Every temporal store should be able to emit, on demand:

  • An as-of certificate: the exact version in effect for a parcel at a stated valid-time instant, with the transaction-time context (“as we believed on this date”), signed by the source run ID and payload hash.
  • A change ledger: the ordered list of versions for a parcel with the effective date, ingest date, and lineage of each, so the sequence of amendments and corrections is reconstructable end to end.
  • A correction log: every back-dated correction as a distinct transaction-time slice, preserving the superseded belief, so “we changed our record on this date” is itself auditable — a regulator’s most common follow-up question.
  • A retention manifest: what was pruned, when, and under which policy, proving that no valid-time history was destroyed and that transaction-time pruning stayed inside its horizon.

Paired with the schema-level guarantees of the exclusion constraint, these artifacts form a defensible, tamper-evident chain from a live municipal publication to a dataset an underwriter or court will accept.

FAQ

What is the difference between valid time and transaction time?

Valid time records when a zoning fact was true in the real world — the effective date of an ordinance. Transaction time records when your system learned it — the ingest date. They diverge whenever a municipality publishes an amendment retroactively or backfills a correction, and only tracking both lets you answer "what was it zoned then, as we believed then, versus as we know now."

Why use a tstzrange and exclusion constraint instead of two timestamp columns?

A range type plus a GiST exclusion constraint makes the database itself refuse to store two overlapping versions of the same parcel. With plain valid_from/valid_to columns, an overlapping or inverted timeline is a silent data bug you discover months later; with the constraint it is an immediate, loud violation at write time.

How do I record a back-dated correction without rewriting history?

Never update the geometry or zoning code of an existing version. Close the current transaction-time slice, then insert the correction as a new version whose valid time reflects the corrected effective date and whose transaction time starts now. The prior belief stays queryable on the transaction-time axis, so both "what we used to think" and "what we now know" remain provable.

Can I prune old versions to control table growth?

Prune only superseded transaction-time rows past a defined horizon — old beliefs you will never need to reproduce. Never prune valid-time history, which is the substantive record of what a parcel was actually zoned. Keep a retention manifest documenting exactly what was removed so the pruning itself stays auditable.

Do I need both a database history table and GeoParquet snapshots?

They serve different queries. The history table answers fine-grained per-parcel as-of questions; the immutable GeoParquet snapshot answers "hand me the entire dataset exactly as of run R" and acts as a disaster-recovery and reproducibility floor. Writing both from the same committed state gives you point queries and whole-world reconstruction without replaying the ingestion pipeline.

Up: Spatial Impact Analysis & Zoning Change Detection