Audience: BSAs/BSEs, ops leaders, support users who query shipment data.

System of record: Zendesk Custom Objects (Custom Data v2) in the dev instance.

Business purpose

The data model represents shipments and their scan history as first-class Zendesk records so that delivery status is queryable, linkable to tickets, and durable — rather than living transiently in carrier websites or ticket comments. Two custom objects plus a ticket lookup field capture everything operations needs to answer "where is this package, and what happened to it?"

Scope

In scope: the two custom objects (carrier_shipment, tracking_event), their fields and identity keys, the ticket→shipment relationship, the status lifecycle, and the integration correlation keys.

Out of scope: Zendesk-native ticket schema beyond the three ingestion fields; n8n's internal execution/persistence store; analytics or reporting models built on top of these objects.

Entity model

erDiagram
  TICKET ||--o{ CARRIER_SHIPMENT : "lookup field"
  CARRIER_SHIPMENT ||--o{ TRACKING_EVENT : "has scans"
  TICKET {
    number id "Zendesk ticket"
    text tracking_number_field "ingestion input"
    dropdown carrier_field "FedEx or UPS"
    lookup carrier_shipment_field "to carrier_shipment"
  }
  CARRIER_SHIPMENT {
    text name "tracking number (unique)"
    string external_id "carrier:tracking_number (upsert key)"
    dropdown carrier "fedex or ups"
    dropdown normalized_status "6 value enum"
    checkbox is_active "false when terminal"
    date latest_scan_at "most recent scan"
    date estimated_delivery_at "carrier ETA"
    date actual_delivery_at "set when delivered"
    date terminal_at "drives 90 day prune"
    text last_error "per shipment failure"
  }
  TRACKING_EVENT {
    string external_id "tracking_number:event_uid"
    lookup shipment "to carrier_shipment"
    date occurred_at "scan timestamp"
    text event_type "carrier code"
    text description "carrier description"
    text location "scan location"
    dropdown normalized_status "status at scan"
  }

carrier_shipment (parent)

The shipment of record. One per carrier+tracking-number pair.

Field Type Purpose
name text (unique) Set to the tracking number; enables name-based dedupe.
external_id string (unique) "<carrier>:<tracking_number>" — the upsert correlation key.
carrier dropdown fedex or ups; selects which carrier source is queried.
tracking_number text Raw carrier tracking number.
normalized_status dropdown Single internal status enum (see lifecycle).
is_active checkbox true while polled; false once terminal. Drives the poll query.
latest_scan_at / latest_scan_location / latest_scan_description date / text / text Most recent scan summary.
estimated_delivery_at / actual_delivery_at date Carrier ETA / delivery timestamp.
carrier_status_raw textarea Raw carrier code(s) retained for audit.
last_synced_at date When the integration last polled this shipment.
terminal_at date When it first reached a terminal status; drives the 90-day prune.
last_error text Most recent per-shipment error (invalid number, carrier error).

tracking_event (child, append-only)

One per carrier scan. Related to its shipment via a lookup field.

Field Type Purpose
name / external_id text / string (unique) "<tracking_number>:<event_uid>" — dedupes scans across polls.
shipment lookup → carrier_shipment Parent shipment relationship.
occurred_at date Scan timestamp.
event_type text Carrier event code.
description text Carrier event description.
location text Scan location.
normalized_status dropdown Status this scan represents.

Ticket relationship

A ticket lookup field (relationship_target_type: zen:custom_object:carrier_shipment) stores the related shipment id on the ticket, so an agent reaches live status from the ticket. The tracking-number and carrier ticket fields are the ingestion inputs the Zendesk trigger watches.

Status lifecycle (normalized enum)

PRE_TRANSIT → IN_TRANSIT → OUT_FOR_DELIVERY → DELIVERED with EXCEPTION (non-terminal, surfaced to ops) and RETURNED reachable from in-transit/exception states. Terminal: DELIVERED, RETURNED (set is_active=false + terminal_at; polling stops). A carrier "no scans yet"/404 maps to PRE_TRANSIT and stays active. Carrier codes map to this enum through a single source-of-truth table shared by the runtime and the unit tests to prevent drift.

Data inputs / outputs