Heritage GIS

Scheduling Automated Boundary Validation Jobs

Designation data changes: Historic England schedules new monuments, local authorities revise conservation area boundaries, and your own feature dataset grows every field season. A validation run is only trustworthy if it reflects the current state of both, which means re-running it on a schedule rather than once at submission. This guide, part of the compliance reporting and statutory boundary validation workflow, wraps the validation logic in a nightly job — driven by a systemd timer or an Airflow DAG — that is idempotent, writes an immutable audit log, and alerts a human only when it detects a new violation.

Nightly validation job lifecycle A timer triggers a validation run that writes an audit log, compares against the previous run, and alerts only when new violations appear. Nightly validation job lifecycle Timer 02:00 nightly Validate new run_id Audit log append run Diff vs prev Alert on new violations

Context & When to Use

Schedule validation once a dataset is live and multiple people touch it. For a single self-contained database on one server, a systemd timer running a Python entry point is the least-moving-parts option — no scheduler daemon to maintain, journal integration for logs, and precise OnCalendar control. Reach for an Airflow DAG only when validation is one task in a larger pipeline (ingest designations, validate, export, notify) with dependencies and retries worth modelling. Whichever you use, the two properties that matter are idempotency (a re-run of the same night must not create duplicate alerts or corrupt the log) and change-detection (alert on the delta, not the full violation set, or reviewers drown in noise and stop reading).

Implementation

systemd service and timer

The service runs the job as a one-shot; the timer schedules it. Persisting missed runs (Persistent=true) means a server that was down at 02:00 still validates when it comes back.

# /etc/systemd/system/boundary-validation.service
[Unit]
Description=Nightly statutory boundary validation
After=postgresql.service

[Service]
Type=oneshot
User=heritage
WorkingDirectory=/opt/heritage-gis
ExecStart=/opt/heritage-gis/.venv/bin/python -m validation.nightly
Environment=PGCONN=postgresql://heritage@localhost/archaeology_db
# /etc/systemd/system/boundary-validation.timer
[Unit]
Description=Run boundary validation nightly at 02:00

[Timer]
OnCalendar=*-*-* 02:00:00
Persistent=true

[Install]
WantedBy=timers.target

Enable and inspect it:

# systemd 255
sudo systemctl daemon-reload
sudo systemctl enable --now boundary-validation.timer
systemctl list-timers boundary-validation.timer   # confirm NEXT / LEFT
journalctl -u boundary-validation.service -n 50    # read last run's output

The idempotent job with audit log and change detection

The audit log is append-only: one row per run recording inputs, counts, and the source currency, so any historical run is explainable. Idempotency comes from a per-day advisory lock — if last night’s run already committed, tonight’s re-trigger exits cleanly instead of double-alerting.

# requirements.txt
# psycopg==3.2.1
import os
import sys
import uuid
import logging

import psycopg

logging.basicConfig(level=logging.INFO, format="%(asctime)s [%(levelname)s] %(message)s")
log = logging.getLogger("nightly")

AUDIT_DDL = """
CREATE TABLE IF NOT EXISTS compliance.validation_audit (
    run_id            UUID PRIMARY KEY,
    run_date          DATE        NOT NULL,
    features_tested   INTEGER     NOT NULL,
    violations_found  INTEGER     NOT NULL,
    new_violations    INTEGER     NOT NULL,
    designation_src   TEXT        NOT NULL,
    started_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (run_date)
);
"""

# Advisory lock key derived from the calendar day makes the run idempotent per night.
LOCK_KEY = 0x5A_1D_0000  # arbitrary namespace constant


def run_nightly(conn_str: str) -> int:
    run_id = str(uuid.uuid4())
    with psycopg.connect(conn_str, autocommit=False) as conn:
        with conn.cursor() as cur:
            cur.execute(AUDIT_DDL)
            # Serialise concurrent triggers for the same day.
            cur.execute("SELECT pg_try_advisory_xact_lock(%s)", (LOCK_KEY,))
            if not cur.fetchone()[0]:
                log.warning("another validation run holds the lock; exiting")
                return 0
            # Skip if today already recorded — the idempotency guarantee.
            cur.execute("SELECT run_id FROM compliance.validation_audit "
                        "WHERE run_date = CURRENT_DATE")
            if cur.fetchone():
                log.info("validation already recorded for today; nothing to do")
                return 0

            # 1. Run the validation (reuses the section's VALIDATE_SQL).
            cur.execute(VALIDATE_SQL, {"run_id": run_id, "buffer": 50})
            found = cur.rowcount
            cur.execute("SELECT count(*) FROM features")
            tested = cur.fetchone()[0]

            # 2. Change detection: which violations are new vs the previous run?
            cur.execute("""
                SELECT count(*) FROM (
                    SELECT feature_id, list_entry, rule
                    FROM compliance.boundary_violations WHERE run_id = %(new)s
                    EXCEPT
                    SELECT feature_id, list_entry, rule
                    FROM compliance.boundary_violations
                    WHERE run_id = (
                        SELECT run_id FROM compliance.validation_audit
                        ORDER BY run_date DESC LIMIT 1)
                ) delta
            """, {"new": run_id})
            new_count = cur.fetchone()[0]

            cur.execute("""
                INSERT INTO compliance.validation_audit
                    (run_id, run_date, features_tested, violations_found,
                     new_violations, designation_src)
                VALUES (%s, CURRENT_DATE, %s, %s, %s,
                        'Historic England NHLE 2026-06')
            """, (run_id, tested, found, new_count))
        conn.commit()

    log.info("run %s: %d tested, %d violations, %d new", run_id, tested, found, new_count)
    if new_count > 0:
        alert(run_id, new_count)
    return new_count


def alert(run_id: str, new_count: int) -> None:
    """Non-zero exit and a message the timer surfaces via journald / email-on-failure."""
    msg = f"ALERT: {new_count} new boundary violation(s) in run {run_id}"
    log.error(msg)
    # Wire to your channel: SMTP, Slack webhook, or systemd OnFailure= mail unit.
    sys.stderr.write(msg + "\n")


if __name__ == "__main__":
    new = run_nightly(os.environ["PGCONN"])
    sys.exit(2 if new else 0)   # exit 2 lets OnFailure= escalate real alerts

The VALIDATE_SQL statement is the one defined in validating features against scheduled monument boundaries; import it rather than duplicating. When a run finds new violations you can chain the submission package export to produce a fresh deliverable automatically.

Field note. Alert on the delta, never the total. A site that has sat inside a scheduled area for three seasons will report the same containment violation every night — if the job emails the full set each run, reviewers filter it to a folder and miss the one night a newly scheduled monument appears. The EXCEPT against the previous run is what turns a nightly job from noise into a signal.

Verification

Trigger the job manually and confirm the audit row, the idempotent no-op on a second run, and the exit code.

# systemd 255 — run the unit now, outside the schedule
sudo systemctl start boundary-validation.service
journalctl -u boundary-validation.service -n 20 --no-pager
-- PostgreSQL 16 / PostGIS 3.4
SELECT run_date, features_tested, violations_found, new_violations, designation_src
FROM   compliance.validation_audit
ORDER  BY run_date DESC LIMIT 3;

Expected: exactly one audit row for today; a second immediate systemctl start logs validation already recorded for today; nothing to do and adds no row (idempotency holds). A run with new violations exits 2; a clean run exits 0. Confirm the timer is armed with systemctl list-timers, which should show a NEXT of tomorrow 02:00.

Common Errors & Fixes

  • psycopg.errors.UniqueViolation: duplicate key value violates unique constraint "validation_audit_run_date_key" — two runs raced for the same day and both passed the existence check before either committed. The pg_try_advisory_xact_lock serialises them; ensure the lock is taken before the existence SELECT, all inside one transaction.
  • Failed with result 'exit-code' in systemctl status — the job exited 2 by design because it found new violations. This is not a crash. Route it with an OnFailure= mail unit rather than treating it as a fault, or reserve non-zero exits strictly for real errors and alert from within alert().
  • Timer never fires after reboot — the timer was started but not enabled, so it did not persist. Run sudo systemctl enable boundary-validation.timer (not just start) and verify with systemctl is-enabled boundary-validation.timer returning enabled.

Part of Artifact & Feature Spatial Database Design.