Automating Inspection-Cycle Generation from Asset Age

Once a register has been risk-ranked, the next question is operational: when is each asset due to be looked at. Answering it by hand across a hundred thousand features is impossible, and answering it with a single global interval reproduces the calendar problem — a critical trunk main and a quiet lateral get inspected on the same clock. The defensible answer derives a due date per asset from three inputs already in the geodatabase: install date sets the age, material class sets a base inspection interval, and criticality tightens or relaxes that interval. Getting this wrong has direct compliance stakes: a missed inspection on a regulated asset is a finding, and a schedule that cannot show why an asset was due on a given date does not satisfy an AWWA G400 or ISO 55001 review. This page implements that derivation as a deterministic routine — one that takes an explicit reference date so every run is reproducible — and it is the scheduling half of condition-based maintenance scheduling within Asset Lifecycle & Maintenance Automation. It pairs with the priority computed in risk-scoring utility assets with Python and consumes the same criticality roll-up established in asset hierarchy design for water and electric.

Environment Prerequisites

A scheduler that reads wall-clock time or a malformed install date produces a schedule that cannot be reproduced or audited. Lock the following before generating cycles:

• Python runtime: Python 3.11 in an isolated environment (conda create -n un-inspection python=3.11). The routine relies only on the standard-library datetime and dataclasses modules plus pandas for batching.
• Dependencies: geopandas>=1.0, pandas>=2.0, and requests>=2.31 for the write-back, installed via conda install -c conda-forge geopandas pandas requests.
• Source register: An asset layer carrying asset_id, install_date, material, criticality, and an optional last_inspection_date, exported from a reconciled, unversioned snapshot.
• Interval policy: A versioned material-to-base-interval table and a criticality-multiplier table, committed alongside the code so a change to inspection frequency is a reviewable, dated change rather than a silent one.
• A fixed reference date: The run must accept its reference date as an explicit parameter — never call the wall clock inside the logic — so a re-run against the same snapshot yields byte-identical due dates. This is what makes the schedule auditable and testable.
• Consistent spatial reference: Where the criticality attribute was derived spatially, confirm CRS alignment and geodetic transformations upstream so the multiplier reflects real downstream exposure.

Schema-Aware Validation Protocol — Run Before Generation

Most scheduling errors trace to the inputs or to hidden nondeterminism, not the interval arithmetic. Work this ordered checklist first; the earliest item is the most frequent culprit.

1. Prove the run is deterministic. Confirm no code path reads the current time. The reference date is a parameter; a stray datetime.now() makes two runs of the same snapshot disagree and destroys auditability. This is the single most important check.
2. Detect missing or defaulted install dates. A null install date has no age to schedule from, and an import sentinel (1900-01-01, the load date) produces an absurd due date. Route both to an exception queue with a documented estimation rule.
3. Validate the material domain. A material outside the base-interval table must raise or route to exceptions, never fall to a default interval that silently mis-schedules the class.
4. Bound the criticality multiplier. Confirm every criticality value maps to a defined multiplier and that the product never collapses the interval below a sane floor (for example, no asset scheduled more often than annually unless policy allows).
5. Reconcile the last-inspection anchor. Where last_inspection_date is present but older than install date, or in the future, the anchor is corrupt; fall back to install date and log the conflict rather than trusting the bad value.

Minimal Reproducible Implementation

The routine below derives each asset’s next inspection due date. It resolves an effective interval from the material base interval and the criticality multiplier, anchors the cycle on the last inspection where valid (otherwise install date), advances whole intervals until the due date lands after the supplied reference date, and flags assets already overdue. Every date is computed from the reference_date argument, so the output is fully deterministic; malformed rows are collected into a separate exception list rather than silently mis-scheduled.

from __future__ import annotations
from dataclasses import dataclass, asdict
from datetime import date
import logging

import pandas as pd

logging.basicConfig(level=logging.INFO, format="%(levelname)s: %(message)s")

# Base inspection interval in whole years, keyed on material class.
BASE_INTERVAL_YEARS = {"DI": 10, "CI": 5, "PVC": 12, "PE": 10, "AC": 4, "STEEL": 6}
# Multiplier applied to the base interval; <1 inspects more often.
CRITICALITY_MULTIPLIER = {"low": 1.5, "medium": 1.0, "high": 0.6, "critical": 0.4}
MIN_INTERVAL_YEARS = 1  # policy floor: never schedule more often than annually


@dataclass(frozen=True)
class InspectionCycle:
    """One asset's derived inspection schedule."""
    asset_id: str
    interval_years: float
    anchor: date
    next_due: date
    overdue: bool


def _add_years(anchor: date, years: int) -> date:
    """Add whole years to a date, handling the Feb-29 edge case safely."""
    try:
        return anchor.replace(year=anchor.year + years)
    except ValueError:  # Feb 29 -> non-leap year
        return anchor.replace(year=anchor.year + years, day=28)


def derive_cycle(asset_id: str, install: date, material: str, criticality: str,
                 reference_date: date, last_inspection: date | None = None) -> InspectionCycle:
    """Derive the next inspection due date for one asset.

    All dates are computed relative to `reference_date`; the routine never
    reads the wall clock, so repeated runs on the same inputs are identical.
    """
    base = BASE_INTERVAL_YEARS.get(material)
    if base is None:
        raise KeyError(f"unknown material class {material!r}")
    mult = CRITICALITY_MULTIPLIER.get(criticality.lower())
    if mult is None:
        raise KeyError(f"unknown criticality class {criticality!r}")

    interval = max(MIN_INTERVAL_YEARS, round(base * mult))

    # Anchor on a valid last inspection, else on install date.
    anchor = install
    if last_inspection is not None and install <= last_inspection <= reference_date:
        anchor = last_inspection
    elif last_inspection is not None:
        logging.warning("%s: ignoring out-of-range last_inspection %s", asset_id, last_inspection)

    # Advance whole intervals until the due date is after the reference date.
    next_due = _add_years(anchor, interval)
    while next_due <= reference_date:
        next_due = _add_years(next_due, interval)

    # Overdue means the cycle *from the anchor* already elapsed before reference.
    overdue = _add_years(anchor, interval) <= reference_date
    return InspectionCycle(asset_id, float(interval), anchor, next_due, overdue)


def generate_schedule(register: pd.DataFrame, reference_date: date) -> dict:
    """Derive inspection cycles for a register; separate good rows from exceptions.

    Returns a structured dict with a sorted schedule and an exception list.
    """
    required = {"asset_id", "install_date", "material", "criticality"}
    missing = required - set(register.columns)
    if missing:
        raise KeyError(f"register missing required columns: {sorted(missing)}")

    cycles: list[dict] = []
    exceptions: list[dict] = []
    for row in register.to_dict("records"):
        try:
            install = pd.to_datetime(row["install_date"], errors="raise").date()
            last = row.get("last_inspection_date")
            last = pd.to_datetime(last).date() if pd.notna(last) else None
            cycle = derive_cycle(str(row["asset_id"]), install, str(row["material"]),
                                 str(row["criticality"]), reference_date, last)
            cycles.append(asdict(cycle))
        except (KeyError, ValueError, TypeError) as exc:
            exceptions.append({"asset_id": row.get("asset_id"), "error": str(exc)})

    cycles.sort(key=lambda c: (not c["overdue"], c["next_due"]))
    logging.info("generated %d cycles, %d exceptions", len(cycles), len(exceptions))
    return {"reference_date": reference_date.isoformat(),
            "schedule": cycles, "exceptions": exceptions,
            "overdue_count": sum(c["overdue"] for c in cycles)}


if __name__ == "__main__":
    demo = pd.DataFrame({
        "asset_id": ["MN-101", "MN-102", "SW-4", "BAD-1"],
        "install_date": ["1961-04-01", "2018-09-12", "1975-06-01", None],
        "material": ["CI", "PE", "STEEL", "PVC"],
        "criticality": ["critical", "low", "high", "medium"],
        "last_inspection_date": ["2020-05-01", None, None, None],
    })
    out = generate_schedule(demo, reference_date=date(2026, 7, 13))
    for c in out["schedule"]:
        print(f"{c['asset_id']:<8} interval={c['interval_years']:>4}y "
              f"due={c['next_due']} overdue={c['overdue']}")
    print("exceptions:", out["exceptions"])

The effective interval is the product of a material base and a criticality multiplier, so the same install date yields very different due dates depending on how much the asset matters. The reference table below is the policy surface an engineer tunes; keeping it explicit — rather than hard-coded in the loop — is what lets a reviewer see the scheduling basis at a glance.

Anchoring on the last inspection where it is valid, and only falling back to install date otherwise, is what keeps the cycle rolling forward correctly after each visit: once an inspection is logged, the next due date recomputes from that visit rather than from the original install, so completing work genuinely moves the asset down the queue instead of leaving it perpetually anchored to its birth date.

Production Deployment Pattern

A generated schedule is only useful once it is durable and enforced. Promote the routine as follows:

1. Run against a reconciled snapshot with a fixed reference date. Pass the pipeline’s run date explicitly and read the register from a read-only replica, so the schedule is reproducible and never contends with editor locks — the same discipline as any data ingestion pipeline for utility assets.
2. Write the schedule back with backoff. Push asset_id → next_due, interval_years into the GIS and the work-management system over their REST endpoints, wrapping each write in a bounded retry (three attempts, exponential backoff) so a transient lock produces a retry rather than a gap in the schedule.
3. Route exceptions, never suppress them. Open a remediation ticket for every row in the exception list — missing install dates, out-of-domain materials — so the schedule’s coverage is complete and provable rather than silently short.
4. Gate on overdue drift. Track the overdue_count between runs; a sudden jump signals a data regression upstream (a bad bulk edit to install dates) and should fail the build for review.
5. Persist the interval basis. Append each run’s reference date, base-interval and multiplier table versions, overdue count, and exception count to a timestamped, version-controlled log — the evidence an AWWA G400 or ISO 55001 auditor asks for when validating that inspections were scheduled on a defensible basis.

Conclusion

Inspection-cycle generation turns install date, material, and criticality into a concrete due date per asset, and doing it deterministically — against an explicit reference date, with exceptions routed rather than hidden — is what makes the resulting schedule auditable. Anchoring each cycle on the last valid inspection keeps the queue rolling forward as work completes, and the material-and-criticality interval table keeps the scheduling basis visible to any reviewer. Wired into CI with write-back and overdue-drift gating, the routine keeps a hundred-thousand-asset register continuously and provably current. The natural next step is to feed the overdue set straight into the risk ranking, so the assets that are both overdue and high-risk surface at the very top of the crew queue.

Related

• Up to the parent topic: Condition-Based Maintenance Scheduling
• Up to the section: Asset Lifecycle & Maintenance Automation
• Risk-Scoring Utility Assets with Python
• Lifecycle State Machines for Utility Assets
• Modeling Asset-Retirement Workflows in ArcGIS Pro