The Canonical Traffic Field Data Dictionary

Every traffic system speaks its own dialect. Avion calls a commercial’s unique key HouseNo; Avstar calls it house_number; a legacy WideOrbit export calls it CART; a network’s SCTE-35 splice descriptor carries it as a segmentation_upid. When those dialects meet inside one automation pipeline, a spot that aired once can be counted three times, a billing code can attach to the wrong advertiser, and an as-run log can fail to reconcile against the schedule it was supposed to fulfil. The fix is a single authoritative vocabulary that every ingestion adapter, rule engine, and billing export resolves into — one field, one type, one constraint, one meaning. This page is that vocabulary: the canonical traffic field data dictionary for Broadcast Traffic Architecture & Taxonomy, written so a traffic manager can read the definitions in plain language and a Python developer can lift the typed model straight into production.

Treat this as the reference the rest of the architecture points at. The broadcast spot schema describes the shape of a single record; this dictionary fixes the exact name, type, and constraint of every field inside it and of the entities the record joins to. Where billing code normalization standardizes a value, the dictionary names the field that value lives in. Where avails mapping for linear TV resolves an inventory slot, the dictionary defines the avail_id that ties the slot to the spot. If two systems disagree about what a field means, this document is the tiebreaker.

Concept & Data Model #

The canonical record is not one flat row — it is a spot fact joined to four reference entities. A CanonicalSpot is the airing-level grain: one row per scheduled or aired commercial occurrence. It carries foreign keys up a hierarchy that answers where does this spot live, who bought it, and how does it bill. Modelling the hierarchy explicitly — rather than flattening advertiser and campaign attributes onto every spot row — is what keeps a category change or a rate correction from having to be rewritten across ten thousand airings.

The join direction is always child-to-parent: a spot references exactly one avail and one order, an order references exactly one campaign, a campaign references one advertiser, and sponsorship attribution resolves through the sponsor. That single-parent rule is what makes the record safe to denormalize for a report and safe to re-normalize for an audit.

Figure — The canonical entity hierarchy: a spot resolves up through avail, order, campaign, advertiser, and sponsor by single-parent foreign keys, so denormalized reports and re-normalized audits stay consistent.

Field Reference #

The tables below are the heart of this document. Every field an adapter is allowed to emit into the canonical layer appears exactly once, grouped by the concern it serves. Read the columns as a contract: type is the Python/JSON type after normalization; constraints is what a validator must enforce; null states whether the field may be absent on a well-formed record; source / SCTE mapping names where the value typically originates in a vendor export or SCTE-35/104 signal. Field names are snake_case and stable — an adapter renames vendor fields to these, never the reverse. The companion guide on mapping vendor fields to canonical names shows how to build that rename map.

Identity fields #

These fields establish which airing, which slot, which buy. They are the join keys the entire architecture depends on, so their constraints are the strictest — a null or duplicated identity fractures billing lineage.

The isci and ad_id fields both identify creative, not airing — a single creative airs across many spot_ids. Keep them distinct: ISCI is the older 8–12 character industry code, Ad-ID is the registered 11–12 character successor, and reconciling the two against a house_number is a recurring task covered in mapping ISCI and Ad-ID to house numbers. When a spot carries neither, playout has no way to bind the SCTE-35 signal to a creative, so both being null is a hard warning rather than a silent pass.

Timing fields #

Timing is where broadcast data quietly corrupts itself. Every timestamp is stored timezone-aware in UTC; local wall-clock time is a presentation concern, never a storage one, because a naive datetime crossing a DST boundary silently shifts an airing by an hour and breaks separation and reconciliation math.

aired_at is nullable on purpose: a scheduled spot that has not yet run — or was preempted and never recovered — legitimately has no airing time. Its absence is the signal that as-run reconciliation must resolve, either by matching a playout event or by opening a make-good. duration_ms is stored in milliseconds, not seconds, because SCTE-35 carries duration as a 90 kHz tick count that rounds cleanly to milliseconds but loses precision as integer seconds.

Commercial and billing fields #

These fields carry money and category. They feed revenue posting and competitive-separation logic, so their normalization is not cosmetic — a mis-typed rate_cents or a free-text advertiser_category posts revenue wrong or lets two car dealers share a break.

rate_cents as an integer is a deliberate guard against floating-point drift: a rate held as 29.99 dollars accumulates rounding error across thousands of airings, whereas 2999 cents sums exactly. billing_code arrives here already standardized — the dictionary assumes billing code normalization ran upstream, so the validator only checks the canonical shape, not the many vendor variants. separation_class is derived rather than sourced because two different advertiser_category values (say qsr and casual-dining) may share one separation group.

Compliance and audit fields #

The last group exists to satisfy regulators and reconstruct history. None of it drives placement, but all of it is what an FCC public-inspection request or a SOC 2 reproducibility review reads.

record_hash is the audit anchor: it fingerprints the canonical field values so any later mutation is detectable, and it lets an archived export be replayed and byte-compared during a review. schema_version travels with every record so a consumer knows which revision of this dictionary the data obeys — a field added in a later version never silently changes the meaning of an older record.

Implementation Approach #

Three decisions turn the tables above into a model that survives contact with real vendor data.

One canonical model, many thin adapters. The dictionary is enforced in exactly one place — the CanonicalSpot model — and every source system gets a small adapter that renames and coerces its fields into that model. This is the opposite of letting each pipeline invent its own record. It means the difference between an Avion and an Avstar export is contained to a mapping table, as catalogued in the Avion vs Avstar export format comparison, rather than leaking into the scheduler.

Validate at the boundary, trust within it. Coercion and constraint-checking happen once, when a record crosses into the canonical layer, using the Pydantic validator pattern the ingestion pillar standardizes on. Downstream rule engines and billing exports then trust the record’s types absolutely, which is what lets them stay fast and simple. The detailed enforcement recipe lives in validating field constraints in Python.

Derive, don’t duplicate. Fields like daypart, separation_class, and record_hash are computed from other fields, not accepted from the source. Accepting them invites contradiction — a vendor daypart that disagrees with the scheduled_at it was supposedly derived from. Computing them makes the record internally consistent by construction.

Production Python Implementation #

The module below is the executable form of the dictionary: a Pydantic v2 CanonicalSpot mirroring the field tables, with derived fields computed in a model validator and structured logging in the traffic-ops timestamp | level | module | spot_id format. It is deployable as the shared schema library every adapter imports.

from __future__ import annotations

import hashlib
import logging
from datetime import datetime, timezone
from enum import Enum
from typing import Optional

from pydantic import BaseModel, Field, computed_field, field_validator, model_validator

# --- Structured logging: timestamp | level | module | spot_id ---------------
logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s | %(levelname)s | %(name)s | %(message)s",
)
logger = logging.getLogger("traffic.canonical.dictionary")


def _log(level: int, spot_id: str, msg: str) -> None:
    # spot_id first so every audit line is greppable by placement.
    logger.log(level, "%s | %s", spot_id, msg)


class SpotType(str, Enum):
    COMMERCIAL = "commercial"
    PROMO = "promo"
    PSA = "psa"
    POLITICAL = "political"


class SourceSystem(str, Enum):
    AVION = "avion"
    AVSTAR = "avstar"
    LEGACY = "legacy"


# Standard commercial lengths in ms; airings must fall within tolerance of one.
STANDARD_DURATIONS_MS: frozenset[int] = frozenset({10_000, 15_000, 30_000, 60_000})
DURATION_TOLERANCE_MS: int = 500

# advertiser_category -> separation_class grouping (categories that must not
# share a break collapse to one class).
SEPARATION_GROUPS: dict[str, str] = {
    "auto": "automotive",
    "auto-dealer": "automotive",
    "qsr": "food",
    "casual-dining": "food",
    "pharma": "pharma",
    "telco": "telecom",
}

# Daypart boundaries by UTC hour is naive; real grids are per-station. Here we
# derive from the station-local hour the caller supplies on scheduled_at.
def _daypart_for(dt: datetime) -> str:
    hour = dt.astimezone(timezone.utc).hour
    if 6 <= hour < 10:
        return "morning_drive"
    if 10 <= hour < 15:
        return "daytime"
    if 15 <= hour < 19:
        return "afternoon_drive"
    if 19 <= hour < 23:
        return "prime"
    return "overnight"


class CanonicalSpot(BaseModel):
    """One airing occurrence, obeying the canonical field data dictionary."""

    model_config = {"frozen": False, "extra": "forbid"}

    # --- identity ---
    spot_id: str
    house_number: str = Field(pattern=r"^[A-Z0-9-]{3,32}$")
    avail_id: str = Field(min_length=1)
    order_id: str = Field(min_length=1)
    campaign_id: str = Field(min_length=1)
    sponsor_id: str = Field(min_length=1)
    isci: Optional[str] = Field(default=None, pattern=r"^[A-Z0-9]{8,12}$")
    ad_id: Optional[str] = Field(default=None, pattern=r"^[A-Z0-9]{11,12}$")

    # --- timing ---
    scheduled_at: datetime
    aired_at: Optional[datetime] = None
    duration_ms: int = Field(gt=0)

    # --- commercial / billing ---
    rate_cents: int = Field(ge=0)
    currency: str = Field(default="USD", pattern=r"^[A-Z]{3}$")
    billing_code: str = Field(pattern=r"^[A-Z0-9]{4,}$")
    advertiser_category: str
    spot_type: SpotType = SpotType.COMMERCIAL
    priority: int = Field(ge=1, le=9, default=5)
    is_sponsored: bool = False

    # --- compliance / audit ---
    political_flag: bool = False
    sponsorship_id: Optional[str] = None
    source_system: SourceSystem
    schema_version: str = "1.0.0"

    @field_validator("scheduled_at", "aired_at")
    @classmethod
    def _utc_aware(cls, v: Optional[datetime]) -> Optional[datetime]:
        # Naive datetimes corrupt DST-crossing separation and reconciliation math.
        if v is None:
            return v
        if v.tzinfo is None:
            raise ValueError("timestamps must be timezone-aware (UTC)")
        return v.astimezone(timezone.utc)

    @field_validator("house_number", "billing_code", "currency")
    @classmethod
    def _upper(cls, v: str) -> str:
        return v.strip().upper()

    @model_validator(mode="after")
    def _cross_field_rules(self) -> "CanonicalSpot":
        # A sponsored airing must carry its FCC sponsorship identifier.
        if self.is_sponsored and not self.sponsorship_id:
            raise ValueError("is_sponsored requires a sponsorship_id (FCC 317)")
        # Duration must be within tolerance of a recognised standard length.
        nearest = min(STANDARD_DURATIONS_MS, key=lambda d: abs(d - self.duration_ms))
        if abs(nearest - self.duration_ms) > DURATION_TOLERANCE_MS:
            _log(logging.WARNING, self.spot_id,
                 f"non-standard duration_ms={self.duration_ms}")
        # An aired spot cannot air before it was scheduled (beyond a grace).
        if self.aired_at and self.aired_at < self.scheduled_at:
            raise ValueError("aired_at precedes scheduled_at")
        return self

    @computed_field  # type: ignore[prop-decorator]
    @property
    def daypart(self) -> str:
        # Derived, never sourced — keeps daypart consistent with scheduled_at.
        return _daypart_for(self.scheduled_at)

    @computed_field  # type: ignore[prop-decorator]
    @property
    def separation_class(self) -> str:
        return SEPARATION_GROUPS.get(self.advertiser_category, self.advertiser_category)

    @computed_field  # type: ignore[prop-decorator]
    @property
    def record_hash(self) -> str:
        # Immutable fingerprint over the identity + money + timing fields.
        seed = "|".join([
            self.spot_id, self.house_number, self.order_id,
            self.scheduled_at.isoformat(), str(self.duration_ms),
            str(self.rate_cents), self.billing_code,
        ])
        return hashlib.sha256(seed.encode()).hexdigest()


def build_spot(**fields: object) -> CanonicalSpot:
    # Boundary constructor: validate once, then trust the record downstream.
    spot = CanonicalSpot(**fields)  # type: ignore[arg-type]
    _log(logging.INFO, spot.spot_id,
         f"validated | daypart={spot.daypart} | class={spot.separation_class} "
         f"| hash={spot.record_hash[:12]}")
    return spot

Because every derived field is a computed_field, a record round-trips through model_dump() with daypart, separation_class, and record_hash present but never accepts them as input — the source cannot contradict the derivation. That property is what makes the model safe to share across adapters that each know only their own vendor’s dialect.

Validation & Edge Cases #

The dictionary’s constraints exist because real broadcast data violates every one of them at some point.

• DST and timezone drift. The _utc_aware validator rejects naive datetimes outright. A spot scheduled at 02:30 local on a spring-forward night does not exist; storing everything UTC-aware makes that airing an explicit validation failure instead of a silent one-hour shift that corrupts competitive separation.
• Sports overrun and zero-position breaks. An overrun slides a break, so break_position can arrive as 0 or negative from a vendor that counts from the pod’s original start. The constraint >= 1 forces the adapter to renumber against the actual aired pod rather than the planned one.
• Preemption leaves aired_at null. A preempted spot legitimately has no airing time. The model allows the null and lets as-run reconciliation resolve it, rather than forcing a placeholder timestamp that would falsely reconcile.
• Zero and non-standard durations. duration_ms > 0 is a hard gate; a 0 duration is a malformed signal, quarantined not stored. A 28_500 ms airing is logged as non-standard but accepted, because trimmed spots are real and billing still needs the record.
• Sponsored without a sponsorship ID. The cross-field rule fails a record that is flagged sponsored but carries no sponsorship_id, because airing sponsored content without the FCC-mandated identifier is a compliance violation, not a data-quality nit.
• Category that has no separation group. An advertiser_category absent from SEPARATION_GROUPS falls back to itself as its own separation_class — a fail-safe that treats an unknown category as maximally restrictive rather than silently un-separated.

Integration Points #

Upstream, ingestion adapters translate vendor exports into the canonical model. Each adapter owns one vendor’s rename map and coercion rules; the shared CanonicalSpot owns the constraints. This is why the Avion vs Avstar export format comparison reads as a diff between two mapping tables rather than two schemas — both resolve into this one dictionary. Avail-level fields arrive through avails mapping for linear TV, which supplies the avail_id, daypart grid, and flight windows a spot binds to.

Downstream, consumers receive the canonical record as a stable wire contract. The message a scheduler or billing engine reads looks like this:

{
  "message_type": "canonical.spot.v1",
  "schema_version": "1.0.0",
  "spot_id": "6ba7b811-9dad-11d1-80b4-00c04fd430c8",
  "house_number": "RCPRIME30",
  "avail_id": "AV-PRIME-1930-04",
  "order_id": "ORD-2026-4471",
  "campaign_id": "CMP-SUMMER-AUTO",
  "sponsor_id": "SPN-8823",
  "isci": "AUTX0142",
  "ad_id": "ABCD1234567",
  "scheduled_at": "2026-07-17T23:30:00Z",
  "aired_at": null,
  "duration_ms": 30000,
  "daypart": "prime",
  "rate_cents": 285000,
  "currency": "USD",
  "billing_code": "RCPRIME30",
  "advertiser_category": "auto",
  "separation_class": "automotive",
  "spot_type": "commercial",
  "is_sponsored": false,
  "political_flag": false,
  "source_system": "avstar",
  "record_hash": "9f2c1ab4e77d0c5a2b6e91f0…"
}

The schema_version and record_hash on the wire let a consumer detect both a dictionary upgrade and any in-flight mutation. Rule engines that enforce separation join on separation_class; billing joins on billing_code and order_id; as-run reconciliation matches aired_at against playout telemetry keyed by spot_id. Access to the underlying store is scoped per consumer through the practices in security boundaries for traffic database access.

Compliance & Audit Considerations #

The dictionary is where regulatory obligations become concrete fields. Three matter most.

FCC sponsorship identification (Section 317). Any airing with is_sponsored = true must carry a sponsorship_id; the cross-field validator makes shipping a sponsored spot without it impossible at the schema boundary. political_flag and lowest_unit_rate similarly turn political-file obligations into typed fields a report can query rather than tribal knowledge a traffic manager has to remember.

Immutable audit fingerprint. record_hash is a SHA-256 over the identity, money, and timing fields. Shipped to append-only storage alongside ingested_at and source_system, it lets a reviewer prove a record was not altered after ingestion and reproduce it byte-for-byte from an archived export — the reproducibility a SOC 2 review demands.

Billing lineage. Because billing_code and rate_cents are canonical and typed, revenue posts against one code with exact-cent arithmetic, and a recovered or reconciled airing bills against the same code as the spot it replaces. This is the join the discrepancy-handling process in as-run reconciliation reconciles against.

Troubleshooting & Common Errors #

An ERR_UNKNOWN_FIELD is almost always a mapping gap, not a data error — a vendor added a column and the adapter has not caught up. Resolve it in the rename map rather than loosening extra="forbid", because a silently absorbed unknown field is how a mis-typed value slips into the canonical layer unnoticed.

Related #

• Broadcast Traffic Architecture & Taxonomy — the parent architecture this dictionary anchors, defining the entities and contracts every other guide inherits.
• Understanding Broadcast Spot Schemas and Metadata — the record-shape companion to this field-level reference, covering immutable identifiers and metadata structure.
• Standardizing Billing Codes Across Traffic Systems — the normalization that produces the canonical billing_code this dictionary assumes upstream.
• Mapping Vendor Fields to Canonical Names — how to build and apply the rename map that resolves any vendor export into these fields.
• Validating Canonical Field Constraints in Python — the enforcement recipe that turns the constraint columns above into a runnable validation report.