Auditing Trust Boundary Crossings in PostGIS

A trust boundary is only real if crossing it leaves a record. Most spatial platforms have the boundary — a staging schema, a promotion step, a gate that checks projection and validity — and no audit of what actually crossed it, which means that when a bad feature turns up in a published layer nobody can say when it arrived, which batch carried it, or whether the gate ran at all. This guide covers how to record every crossing in PostGIS cheaply, what to record so the audit answers the questions an incident actually asks, and how to detect features that entered the trusted schema without crossing the boundary at all. It belongs to defining spatial data trust boundaries under geospatial observability architecture fundamentals.

Audited promotion path beside an unaudited side door into the trusted schema Staging data flows through a promotion gate that checks projection, validity and structure, writes an audit row for every batch, and then inserts into the trusted schema. A second path bypasses the gate entirely: a manual load and a restore both write directly into the trusted schema without producing an audit row. A detector compares the trusted schema's contents against the audit ledger and flags any feature with no corresponding crossing record, labelling this the side door that most audits miss. Audit the gate, then audit for what never went through it staging untrusted promotion gate SRID · validity · structure writes an audit row audit ledger batch · counts · verdict gate version · timestamp trusted schema published to consumers manual load restore · hotfix no audit row the side door Reconciling the trusted schema against the ledger is what turns an audit into a control.

Problem framing: what the audit has to be able to answer

Design the ledger backwards from the questions an incident asks. Four come up every time.

When did this feature first appear in the trusted schema? Answering it needs a per-feature ingestion timestamp on the trusted table, not merely a per-batch record, because a batch can span hours and the exposure calculation in calculating data exposure windows after a bad load bisects on exactly this column.

Which batch carried it, and what did the gate say about that batch? This needs a batch identifier on the feature and a ledger row per batch recording the gate’s verdict, the counts it saw, and the gate version that produced them.

Did the gate actually run? A ledger that only records passes cannot distinguish “checked and clean” from “never checked”. Record every evaluation, including the ones that pass, and record the gate’s own version so a change in behaviour is attributable.

Did anything arrive without crossing? The hardest and most valuable question. It requires reconciling the trusted table against the ledger, because a manual load, a restore from backup, or a well-meaning hotfix all write rows that no gate ever saw.

Implementation: a ledger and a stamp

Two structures do the work: a per-batch ledger and a per-feature stamp on the trusted table.

-- Per-batch ledger. One row per gate evaluation, pass or fail.
CREATE TABLE IF NOT EXISTS audit.boundary_crossing (
  crossing_id     bigserial PRIMARY KEY,
  layer           text        NOT NULL,
  batch_id        text        NOT NULL,
  source          text        NOT NULL,
  gate_version    text        NOT NULL,      -- attribute behaviour changes
  verdict         text        NOT NULL,      -- admitted | rejected
  features_in     bigint      NOT NULL,
  features_out    bigint      NOT NULL,      -- differs when the gate filters
  srid_observed   integer,
  invalid_geoms   bigint      NOT NULL DEFAULT 0,
  extent_area     double precision,
  evaluated_at    timestamptz NOT NULL DEFAULT now(),
  UNIQUE (layer, batch_id)
);

-- Per-feature stamp on the trusted table.
ALTER TABLE prod.parcels
  ADD COLUMN IF NOT EXISTS batch_id    text,
  ADD COLUMN IF NOT EXISTS ingested_at timestamptz NOT NULL DEFAULT now();

CREATE INDEX IF NOT EXISTS parcels_ingested_at_idx ON prod.parcels (ingested_at);
CREATE INDEX IF NOT EXISTS parcels_batch_idx       ON prod.parcels (batch_id);

The promotion itself writes both in one transaction, so a crash cannot leave features without a ledger row or a ledger row without features.

-- Promotion: gate, stamp, ledger — atomically.
BEGIN;

WITH checked AS (
  SELECT s.*,
         ST_SRID(s.geom) = 27700 AS srid_ok,
         ST_IsValid(s.geom)      AS valid_ok
  FROM staging.parcels s
  WHERE s.batch_id = :batch_id
),
promoted AS (
  INSERT INTO prod.parcels (parcel_id, geom, land_use, batch_id, ingested_at)
  SELECT parcel_id, geom, land_use, :batch_id, now()
  FROM checked
  WHERE srid_ok AND valid_ok            -- the boundary condition, in one place
  RETURNING 1
)
INSERT INTO audit.boundary_crossing
  (layer, batch_id, source, gate_version, verdict,
   features_in, features_out, srid_observed, invalid_geoms, extent_area)
SELECT 'parcels_authoritative', :batch_id, :source, :gate_version,
       CASE WHEN COUNT(*) FILTER (WHERE NOT (srid_ok AND valid_ok)) = 0
            THEN 'admitted' ELSE 'partial' END,
       COUNT(*),
       (SELECT COUNT(*) FROM promoted),
       MIN(ST_SRID(geom)),
       COUNT(*) FILTER (WHERE NOT valid_ok),
       ST_Area(ST_Extent(geom)::geometry)
FROM checked;

COMMIT;

Recording features_in and features_out separately is what makes silent filtering visible. A gate that drops eleven thousand features because they failed validity is doing its job; a gate that does so every night without anyone noticing is a coverage problem hiding inside a correctness control.

Detecting the side door

The reconciliation query is short and finds the class of problem no gate can prevent.

-- Features in the trusted schema with no corresponding crossing record.
SELECT p.batch_id,
       COUNT(*)            AS features,
       MIN(p.ingested_at)  AS first_seen,
       MAX(p.ingested_at)  AS last_seen
FROM prod.parcels p
LEFT JOIN audit.boundary_crossing c
       ON c.layer = 'parcels_authoritative'
      AND c.batch_id = p.batch_id
WHERE c.crossing_id IS NULL
GROUP BY 1
ORDER BY 3;
-- Any row here is a feature that entered the trusted schema unchecked.
-- A NULL batch_id group is the classic signature of a manual load or restore.

Run it on a schedule and emit the count as a gauge. It should be exactly zero, permanently, and any non-zero value is a control failure rather than a data-quality one — which makes it one of the few spatial signals that genuinely warrants a target of 100%, in the sense described in spatial data contracts and SLO design.

The four incident questions and the audit field that answers each Four rows pair an incident question with the specific audit structure that answers it. When did this feature appear is answered by the per-feature ingested-at column. Which batch carried it and what did the gate see is answered by the per-feature batch identifier joined to the ledger row. Did the gate run at all is answered by the ledger recording every evaluation including passes, together with the gate version. Did anything arrive unchecked is answered by reconciling the trusted table against the ledger. A note marks the fourth as the only one that finds problems the gate itself cannot prevent. Design the ledger backwards from the questions “When did this feature appear?” prod.parcels.ingested_at — per feature, indexed, bisectable “Which batch, and what did the gate see?” prod.parcels.batch_id → audit.boundary_crossing (counts, SRID, invalid geoms, extent) “Did the gate actually run?” a ledger row for every evaluation including passes, plus gate_version “Did anything arrive unchecked?” reconcile trusted table against ledger — the only check that finds what the gate never saw

Verification

Insert a row directly into prod.parcels with a null batch identifier and confirm the reconciliation query reports it within one scheduled run. Then delete it and confirm the count returns to zero. This single test proves the control works end to end and takes a minute.

Separately, run a batch containing a known number of invalid geometries and confirm features_in minus features_out matches, and that invalid_geoms equals the injected count. A mismatch here means the gate’s filter and its accounting disagree, which is worse than either being wrong alone because the ledger then reports confidently incorrect numbers.

Query cost of the four incident questions, with and without the audit columns Four incident questions are compared on the cost of answering them. With the ingestion timestamp and batch identifier present, each is an indexed lookup taking under a second. Without them, the first question requires a full-table scan, the second is unanswerable, the third requires reading application logs, and the fourth is impossible. The bars show relative effort rather than absolute time. Two columns and one ledger table decide whether an incident is answerable when did it appear? (with columns) indexed lookup when did it appear? (without) full scan, approximate which batch? (with) indexed lookup which batch? (without) unanswerable did the gate run? (with ledger) one row did the gate run? (without) unanswerable

Gotchas

Recording only rejections. Makes “the gate passed it” indistinguishable from “the gate never ran”. Record every evaluation.

No gate version. When the gate’s behaviour changes, historical ledger rows become uninterpretable. One short version string prevents an entire class of confusion during a review.

Ledger written outside the promotion transaction. A crash between the insert and the ledger write produces features with no record — exactly the state the audit exists to detect, created by the audit itself.

Timestamp defaulted at the wrong layer. If ingested_at defaults on the staging table and is copied through, it records staging arrival rather than promotion. The bisect then points at the wrong moment. Stamp it at promotion.

Reconciliation run manually. A control that depends on someone remembering is not a control. Schedule it and alert on any non-zero result.

FAQ

How much storage does this add?

Two columns per feature and one small ledger row per batch. On a twelve-million-feature parcel layer that is a few hundred megabytes for the columns and a negligible amount for the ledger, against an index that also accelerates the incident queries you would otherwise run as sequential scans.

Should the ledger record the rejected features themselves?

Record their count and reason in the ledger, and keep the features in a quarantine table rather than in the ledger. Mixing per-batch accounting with per-feature payload makes the ledger large and slow exactly when an incident needs to query it quickly.

Can this replace database audit logging?

No — they answer different questions. Database audit logs record statements and principals; this ledger records data-quality verdicts about batches. Both are useful during an incident, and the ledger is the one that tells you whether the data was checked.

What about layers loaded by tools you do not control?

Those are precisely the side door. If a vendor tool writes directly to the trusted schema, either route it through the gate or move it to staging and promote from there. Where neither is possible, the reconciliation at least makes the unchecked volume visible, which is the argument for keeping the exception explicit rather than pretending the boundary is intact.

How long should the ledger be retained?

Longer than any incident you might reasonably investigate, which in practice means years rather than weeks. The ledger is small — one row per batch per layer — and its entire value is answering questions asked long after the fact. Truncating it to save space is a false economy that surfaces the first time somebody asks when a defect entered a compliance extract. Retaining the per-feature stamps is the more meaningful cost, and those should live as long as the features themselves.

Does this help with the exposure calculation after an incident?

It is the prerequisite. Without ingested_at and batch_id, the first bad batch cannot be located and the exposure window cannot be bounded, which is the finding most reviews of unaudited platforms end up producing.