Heritage GIS

Merging Field Audit Trails into the Master Database

A synced batch of device edits is worthless to an archive if you cannot say who recorded each feature, when, and on which device. The moment field GeoPackages are flattened into a master table with a blind INSERT, provenance is gone and the data-quality lineage a heritage archive requires becomes a guess. This guide implements the last leg of the pipeline: appending reconciled device edits into a master PostGIS store with editor, timestamp, and provenance columns, capturing every change in a trigger-based audit log, and reconciling device feature IDs against the authoritative UUID so nothing collides or overwrites. It is part of Participatory GIS and Mobile Field Sync.

Appending device edits with an audit trail Device GeoPackages are staged, upserted on UUID into the master table, and every change is written to an audit log by trigger. Append with provenance and audit Device GPKG reconciled edits Staging table stamp provenance Master upsert on uuid Audit log by trigger

Context & When to Use

Use this pattern whenever field edits from more than one device converge into a shared archive and you must preserve an audit trail for funders, planning authorities, or a national heritage register. The design principle is never mutate the master directly from a device file. Load device edits into a staging table first, stamp them with provenance, then upsert into the master on the UUID sync key with INSERT ... ON CONFLICT. Reconciliation is entirely about identity: the device’s local GeoPackage fid is meaningless in the master (two devices both start at fid = 1), so the UUID minted at capture is the only key you join on. The performance cost of the audit trigger is small — a row per change — but its value is total, since it is the difference between an archive you can defend and one you cannot. The choice of GeoPackage at the field edge and PostGIS at the archive is examined in comparing GeoPackage and PostGIS for field-to-archive handoff.

Implementation

First, the master table, an audit log, and the trigger that records every insert, update, and delete.

-- PostgreSQL 16 / PostGIS 3.4
CREATE TABLE finds_master (
    fid          bigserial PRIMARY KEY,
    uuid         uuid NOT NULL UNIQUE,            -- device sync key, join on this
    site_id      text NOT NULL,
    feature_type text,
    collector_id text,                            -- provenance: who
    device_id    text,                            -- provenance: which device
    recorded_at  timestamptz,                     -- provenance: when captured
    synced_at    timestamptz DEFAULT now(),       -- when it entered the archive
    geom         geometry(Point, 27700)           -- substitute your site's EPSG
);

CREATE TABLE finds_audit (
    audit_id   bigserial PRIMARY KEY,
    uuid       uuid,
    action     text,                              -- INSERT / UPDATE / DELETE
    changed_by text,                              -- session user performing the merge
    changed_at timestamptz DEFAULT now(),
    old_row    jsonb,
    new_row    jsonb
);

CREATE OR REPLACE FUNCTION log_finds_change() RETURNS trigger AS $$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO finds_audit(uuid, action, changed_by, old_row)
        VALUES (OLD.uuid, TG_OP, current_user, to_jsonb(OLD));
        RETURN OLD;
    ELSE
        INSERT INTO finds_audit(uuid, action, changed_by, old_row, new_row)
        VALUES (NEW.uuid, TG_OP, current_user,
                CASE WHEN TG_OP = 'UPDATE' THEN to_jsonb(OLD) ELSE NULL END,
                to_jsonb(NEW));
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER finds_audit_trg
AFTER INSERT OR UPDATE OR DELETE ON finds_master
FOR EACH ROW EXECUTE FUNCTION log_finds_change();

Now the Python side loads a device GeoPackage into a staging table and runs a provenance-stamping upsert. The audit trigger fires automatically on every affected master row.

# requirements.txt
# psycopg==3.1.18
# geopandas==0.14.3
# GDAL/ogr2ogr==3.8.4  (system binary)
# psycopg 3.1.18 / PostGIS 3.4
import subprocess
import psycopg

DSN = "postgresql://[email protected]:5432/heritage"
DEVICE_GPKG = "/data/sync/tablet_07/survey.gpkg"
DEVICE_ID = "tablet_07"

def stage_device_gpkg(gpkg=DEVICE_GPKG, dsn=DSN):
    # push the device layer into a transient staging table via ogr2ogr
    subprocess.run([
        "ogr2ogr", "-f", "PostgreSQL",
        f"PG:{dsn}", gpkg,
        "finds",                       # source layer in the GeoPackage
        "-nln", "finds_staging",       # target staging table
        "-overwrite",
        "-t_srs", "EPSG:27700",        # substitute your site's EPSG
        "-lco", "GEOMETRY_NAME=geom",
    ], check=True)

def merge_staging(dsn=DSN, device_id=DEVICE_ID):
    with psycopg.connect(dsn, autocommit=False) as conn:
        with conn.cursor() as cur:
            # upsert on the UUID sync key; device fid is deliberately ignored
            cur.execute("""
                INSERT INTO finds_master
                    (uuid, site_id, feature_type, collector_id,
                     device_id, recorded_at, geom)
                SELECT uuid, site_id, feature_type, collector_id,
                       %(device_id)s, recorded_at, geom
                FROM finds_staging
                ON CONFLICT (uuid) DO UPDATE SET
                    feature_type = EXCLUDED.feature_type,
                    collector_id = EXCLUDED.collector_id,
                    device_id    = EXCLUDED.device_id,
                    recorded_at  = EXCLUDED.recorded_at,
                    geom         = EXCLUDED.geom,
                    synced_at    = now()
                -- only apply the device edit if it is genuinely newer
                WHERE EXCLUDED.recorded_at >= finds_master.recorded_at;
            """, {"device_id": device_id})
            merged = cur.rowcount
            cur.execute("DROP TABLE finds_staging;")
        conn.commit()
    print(f"Merged {merged} device edits from {device_id}")
    return merged

if __name__ == "__main__":
    stage_device_gpkg()
    merge_staging()

The WHERE EXCLUDED.recorded_at >= finds_master.recorded_at guard is the reconciliation safety net: a stale device that re-syncs old edits cannot clobber a newer correction already in the archive.

Field note. Device clocks drift, and a tablet whose battery died keeps recording with a reset clock — so recorded_at can read as 1970 or as tomorrow. Before trusting a timestamp for conflict resolution, validate it against the known field-season window and fall back to the server synced_at for anything implausible, or a single mis-clocked device will silently win every upsert and overwrite good archive data.

Verification

Confirm the merge stamped provenance on every row and that the audit log captured the batch:

-- PostgreSQL 16 / PostGIS 3.4
-- no archived feature may lack originator or capture time
SELECT count(*) AS ungoverned
FROM finds_master
WHERE collector_id IS NULL OR recorded_at IS NULL;

-- the audit log should hold one row per feature touched by this sync
SELECT action, count(*)
FROM finds_audit
WHERE changed_at > now() - interval '5 minutes'
GROUP BY action;

Expected: ungoverned returns 0, and the audit query lists INSERT and/or UPDATE counts matching the merge’s reported rowcount. A non-zero ungoverned count means a device form omitted a mandatory provenance field — quarantine those UUIDs rather than archiving them.

Common Errors & Fixes

  • duplicate key value violates unique constraint "finds_master_uuid_key" — the upsert used INSERT without ON CONFLICT, or two staging rows share a UUID (a device that duplicated a feature). Deduplicate the staging table on uuid (keep the latest recorded_at) before merging.
  • ERROR: Geometry SRID (0) does not match column SRID (27700) — ogr2ogr loaded geometries without a CRS because the device GeoPackage layer had none assigned. Re-run with -a_srs EPSG:27700 (assign) or -t_srs (reproject) so the staging geometry carries the master SRID.
  • Audit log stays empty after a merge — the trigger was created but the merge ran as a superuser session with triggers disabled, or ALTER TABLE ... DISABLE TRIGGER is still in effect from a bulk load. Re-enable with ALTER TABLE finds_master ENABLE TRIGGER finds_audit_trg; and re-run.

Part of Heritage GIS Architecture & Fundamentals.