13 min read2,753 words

Syncing Work Orders Between ArcGIS and Maximo

A work order that lives in ArcGIS and a work order that lives in IBM Maximo describe the same repair from two vantage points, and keeping them agreed by hand does not survive contact with a real maintenance program. A crew closes a hydrant-repair job in Maximo from a tablet; the ArcGIS network model still flags the hydrant for inspection because nothing carried the completion west across the boundary. A GIS planner opens an inspection against a segment that Maximo retired last quarter. Neither system is wrong in isolation — the defect is in the exchange between them, where an asset identifier, a status, and a timestamp have to cross a REST boundary reliably enough to survive duplicate deliveries, clock skew, and the occasional 502. This page implements the durable bridge that the parent guide on CMMS and GIS integration for work orders specifies, and it slots into the broader asset lifecycle and maintenance automation practice as the concrete, runnable coupling between the spatial system of record and the maintenance system of record. Doing it ad hoc — a one-way nightly CSV export, a name-based asset match — is precisely what manufactures the drift; a deterministic, idempotent, both-directions sync is the only pattern that holds at the scale of an enterprise asset register.

Environment Prerequisites

A misconfigured environment produces the most dangerous outcome: a sync that reports success while writing to the wrong asset. Lock the following before running anything:

  • Python runtime: Python 3.11 in an isolated environment (python -m venv un-cmms or a dedicated conda env) so the requests and TLS stack stay reproducible across engineers.
  • Dependencies: requests>=2.31. No geospatial libraries are needed — this bridge speaks only REST — which keeps it deployable as a lightweight scheduled job or container.
  • ArcGIS access: An ArcGIS Enterprise 11.x feature service exposing the work-order layer, reachable at its .../FeatureServer/<id>/query and .../applyEdits endpoints, plus a token-generating account scoped to just that layer. See the ArcGIS Pro feature-service editing documentation for the applyEdits transaction model.
  • Maximo access: An IBM Maximo REST endpoint over the MXWO (or MXAPIWODETAIL) object structure, with an apikey or maxauth credential scoped to work orders. Confirm the object structure exposes the fields you intend to sync before wiring anything.
  • A persisted crosswalk: The authoritative table mapping each ArcGIS GlobalID to its Maximo ASSETNUM and SITEID, loaded through the same discipline as any data ingestion pipeline for utility assets. Never derive identity from a name match at runtime.
  • Clock discipline: Both systems must emit a comparable last-modified timestamp; treat everything as UTC internally so conflict resolution never turns on a timezone accident.
  • A dead-letter destination: A table, queue, or ticket endpoint for work orders whose asset cannot be resolved, so identity failures surface loudly instead of vanishing.

Schema-Aware Reconciliation Protocol — Run Before the Sync

Most sync failures originate in setup, not payload. Work this ordered checklist first; the earliest item is the most frequent culprit.

  1. Confirm identity resolves through the crosswalk, not a name. Every work order must map to exactly one GlobalID and one (ASSETNUM, SITEID) pair. A reused Maximo asset number or a fuzzy match that slipped into the crosswalk mis-associates records that then pass every field check while pointing at the wrong physical asset.
  2. Verify per-field ownership is declared. ArcGIS owns location and network association; Maximo owns the work itself — trade, labor, actual finish. Closure is a maintenance fact, so wo_status may open from either side but close only from Maximo. If ownership is undeclared, the bridge cannot decide a conflict without guessing.
  3. Normalize timestamps to UTC before comparing. ArcGIS EditDate arrives as epoch milliseconds; Maximo changedate arrives as an ISO string with an offset. Convert both to timezone-aware UTC before any comparison, or the newest-wins rule silently picks the loser.
  4. Confirm the idempotency column exists on both targets. The target work order must carry the last-applied idempotency key so a replayed message is detected and collapsed rather than duplicated.
  5. Pin the watermark. Pull only records modified since the last successful run’s high-water mark; a full-table sync on every tick is both slow and a source of needless write contention.

Minimal Reproducible Implementation

The following bridge authenticates to both REST APIs, pulls work orders modified since a watermark, resolves identity through the crosswalk, derives a deterministic idempotency key, resolves conflicts by ownership and last-modified timestamp, and applies the winning change in whichever direction it must go — all inside a bounded retry with exponential backoff. It returns a structured report rather than printing, so a scheduler or CI job can gate on it.

Per-record decision path from a source work order to a target write A single work order enters from the left and passes through four gates. First a crosswalk lookup: an unmapped asset branches down to the dead-letter destination. Then an idempotency-key check: an already-applied key branches down to skipped-duplicate. Then a conflict decision: a dead heat branches down to the review queue. Records that pass all three gates reach the apply stage, which writes to the target via REST with retry and backoff and records the key in the audit ledger. Source WO GIS or Maximo Crosswalk mapped? Idempotency key seen? Conflict winner? APPLY + AUDIT REST · retry/backoff write ledger Dead-letter unmapped asset Skip duplicate key already applied Review queue timestamp dead heat 
import hashlib
import logging
import time
from dataclasses import dataclass, field
from datetime import datetime, timezone
from typing import Any, Callable

import requests

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

GIS_OWNED = {"location", "network_assoc"}   # ArcGIS owns spatial fields
CMMS_OWNED = {"trade", "labor_hours", "actual_finish", "wo_status"}  # Maximo owns the work


@dataclass
class SyncReport:
    """Structured, gateable outcome of one sync run."""
    applied: list[str] = field(default_factory=list)
    skipped_duplicate: list[str] = field(default_factory=list)
    conflicts_for_review: list[str] = field(default_factory=list)
    dead_letter: list[str] = field(default_factory=list)
    errors: list[str] = field(default_factory=list)

    @property
    def ok(self) -> bool:
        return not self.errors


def _to_utc(value: Any) -> datetime:
    """Coerce an ArcGIS epoch-ms or Maximo ISO timestamp to timezone-aware UTC."""
    if isinstance(value, (int, float)):
        return datetime.fromtimestamp(value / 1000.0, tz=timezone.utc)
    dt = datetime.fromisoformat(str(value).replace("Z", "+00:00"))
    return dt.astimezone(timezone.utc)


def idempotency_key(source: str, source_id: str, modified: datetime) -> str:
    """Deterministic key; identical source state always yields the same key."""
    stamp = modified.astimezone(timezone.utc).isoformat(timespec="seconds")
    return hashlib.sha256(f"{source}:{source_id}:{stamp}".encode()).hexdigest()[:32]


def resolve_conflict(field_name: str, source: str,
                     src_mod: datetime, tgt_mod: datetime) -> str:
    """Return 'source', 'target', or 'review' for one contested field."""
    if field_name in GIS_OWNED:
        return "source" if source == "GIS" else "target"
    if field_name in CMMS_OWNED:
        return "source" if source == "CMMS" else "target"
    if src_mod > tgt_mod:
        return "source"
    if src_mod < tgt_mod:
        return "target"
    return "review"


def _request_with_backoff(session: requests.Session, method: str, url: str,
                          *, attempts: int = 4, base: float = 0.5,
                          **kwargs) -> requests.Response:
    """Issue a REST call, retrying transient failures with exponential backoff."""
    last_exc: Exception | None = None
    for attempt in range(attempts):
        try:
            resp = session.request(method, url, timeout=30, **kwargs)
            if resp.status_code in (429, 500, 502, 503, 504):
                raise requests.HTTPError(f"transient {resp.status_code}")
            resp.raise_for_status()
            return resp
        except (requests.ConnectionError, requests.Timeout, requests.HTTPError) as exc:
            last_exc = exc
            sleep = base * (2 ** attempt)
            logging.warning("REST %s %s failed (%s); retry in %.1fs", method, url, exc, sleep)
            time.sleep(sleep)
    raise RuntimeError(f"exhausted retries for {method} {url}: {last_exc}")


def sync_work_orders(
    session: requests.Session,
    arcgis_url: str,
    maximo_url: str,
    crosswalk: dict[str, tuple[str, str]],   # GlobalID -> (ASSETNUM, SITEID)
    reverse_crosswalk: dict[tuple[str, str], str],
    fetch_arcgis: Callable[[], list[dict]],
    fetch_maximo: Callable[[], list[dict]],
    applied_keys: set[str],
) -> SyncReport:
    """Reconcile work orders both directions between ArcGIS and Maximo.

    `fetch_*` return work orders modified since the last watermark. `applied_keys`
    is the persisted set of idempotency keys already written to a target.
    Returns a structured SyncReport; never raises on a single bad record.
    """
    report = SyncReport()

    # --- GIS -> Maximo: push spatially originated changes -------------------
    for wo in fetch_arcgis():
        gid = wo["GlobalID"]
        target = crosswalk.get(gid)
        if target is None:
            report.dead_letter.append(gid)
            logging.error("unmapped ArcGIS asset %s -> dead-letter", gid)
            continue
        src_mod = _to_utc(wo["EditDate"])
        key = idempotency_key("GIS", gid, src_mod)
        if key in applied_keys:
            report.skipped_duplicate.append(key)
            continue
        assetnum, siteid = target
        tgt_mod = _to_utc(wo.get("maximo_changedate", 0))
        decision = resolve_conflict("wo_status", "GIS", src_mod, tgt_mod)
        if decision == "review":
            report.conflicts_for_review.append(gid)
            continue
        if decision == "target":
            continue  # Maximo already holds the authoritative value.
        payload = {
            "assetnum": assetnum, "siteid": siteid,
            "description": wo.get("description", ""),
            "un_globalid": gid, "un_idempotency": key,
        }
        try:
            _request_with_backoff(
                session, "POST", maximo_url,
                json=payload, headers={"properties": "*"},
            )
            applied_keys.add(key)
            report.applied.append(key)
        except RuntimeError as exc:
            report.errors.append(f"GIS->Maximo {gid}: {exc}")

    # --- Maximo -> GIS: push work completion back to the model --------------
    edits: list[dict] = []
    for wo in fetch_maximo():
        ident = (wo["assetnum"], wo["siteid"])
        gid = reverse_crosswalk.get(ident)
        if gid is None:
            report.dead_letter.append(f"{ident}")
            logging.error("unmapped Maximo asset %s -> dead-letter", ident)
            continue
        src_mod = _to_utc(wo["changedate"])
        key = idempotency_key("CMMS", wo["wonum"], src_mod)
        if key in applied_keys:
            report.skipped_duplicate.append(key)
            continue
        edits.append({
            "attributes": {
                "GlobalID": gid,
                "WO_STATUS": wo["status"],
                "ACTUAL_FINISH": wo.get("actualfinish"),
                "UN_IDEMPOTENCY": key,
            }
        })
        applied_keys.add(key)
        report.applied.append(key)

    if edits:  # One batched applyEdits, not one call per work order.
        try:
            _request_with_backoff(
                session, "POST", f"{arcgis_url}/applyEdits",
                data={"updates": _json(edits), "f": "json"},
            )
        except RuntimeError as exc:
            report.errors.append(f"Maximo->GIS applyEdits: {exc}")

    logging.info(
        "sync done: applied=%d dup=%d review=%d dead=%d err=%d",
        len(report.applied), len(report.skipped_duplicate),
        len(report.conflicts_for_review), len(report.dead_letter), len(report.errors),
    )
    return report


def _json(obj: Any) -> str:
    import json
    return json.dumps(obj)


if __name__ == "__main__":
    # Illustrative dry run with stub fetchers and an in-memory crosswalk.
    xwalk = {"{A1}": ("HYD-1001", "WATER")}
    rxwalk = {("HYD-1001", "WATER"): "{A1}"}
    sess = requests.Session()
    result = sync_work_orders(
        sess,
        arcgis_url="https://gis.example.org/server/rest/services/WO/FeatureServer/0",
        maximo_url="https://maximo.example.org/maximo/api/os/mxwo",
        crosswalk=xwalk,
        reverse_crosswalk=rxwalk,
        fetch_arcgis=lambda: [{"GlobalID": "{A1}", "EditDate": 1_760_000_000_000,
                               "description": "valve exercise", "maximo_changedate": 0}],
        fetch_maximo=lambda: [{"assetnum": "HYD-1001", "siteid": "WATER",
                               "wonum": "WO-77", "status": "COMP",
                               "changedate": "2026-07-13T10:00:00+00:00",
                               "actualfinish": "2026-07-13T09:40:00+00:00"}],
        applied_keys=set(),
    )
    print(result)

The applied_keys set is the crux of idempotency: because the key is derived from the source id and its last-modified time, replaying the same message — a duplicated webhook, a re-run after a crash — recomputes the same key, finds it already applied, and does nothing. Conflicts never silently overwrite; a dead heat on a shared field lands in conflicts_for_review for a human, and an unresolved identity lands in dead_letter rather than fabricating an asset on the far side. Batching the Maximo-to-GIS direction into a single applyEdits keeps the write count proportional to runs, not to records.

Production Deployment Pattern

A one-off run is a diagnostic; a hardened job is an engineering control. Promote the bridge as follows:

  1. Persist the watermark and applied keys. Store the last successful run’s high-water timestamp and the set of applied idempotency keys in a durable table, not in memory, so a restart resumes cleanly instead of replaying or skipping records.
  2. Pair an event path with a batch sweep. Fire the sync from Maximo escalations and ArcGIS webhooks for low latency, and run the same function on a schedule over the full extent to catch anything the event path missed — the completeness-plus-latency pairing the parent guide describes.
  3. Hold credentials in a secret store. Never inline the ArcGIS token or Maximo apikey; pull them from a vault at startup and refresh the ArcGIS token before expiry inside the same requests.Session.
  4. Gate on the report. Wire sync_work_orders into the scheduler or CI so a non-empty errors list fails the run and a growing dead_letter or conflicts_for_review count opens remediation tickets without blocking healthy syncs.
  5. Persist an audit trail. Append each run’s applied keys, conflict decisions, dead-letter identities, and the requests version to a timestamped, version-controlled ledger, satisfying the ISO 55001 and AWWA G400 change-of-custody expectations the compliance framework requires.

Conclusion

This bridge replaces a fragile one-way export with a deterministic, idempotent, both-directions sync that resolves asset identity through an authoritative crosswalk, keys every message so replays are harmless, resolves conflicts by declared ownership and UTC timestamp, and retries transient REST failures with backoff. Enforcing it keeps ArcGIS and Maximo agreed about every work order, prevents the silent drift that corrupts inspection scheduling and reliability reporting, and produces the version-stamped audit trail that a rate case or asset-management review demands. The natural next step is to standardize the per-field ownership matrix across every asset class and codify it as shared configuration, so every integration in the estate resolves a conflict the same way.