Skip to main content

DealerConnect Source

The dealerconnect source reads pre-parsed PDF blob data from BigQuery tables populated by the ppm-ordering-services collection pipeline. It is specific to the Stellantis project (projects/ai_stellantis).

BigQuery Tables​

Table patternDescriptionPartition
inventory_{dealerId}In-stock vehicles with POC blobsDAY (fetch_date)
sales_{dealerId}Sold vehicles with POC blobsNone (filtered by fetch_date + sale_date)
feature_catalogFeatures available per model/trim (structured rows, not blobs)None (filtered by exact DATE(fetch_date))

Dealer tables are discovered dynamically at runtime by listing all tables in the configured dataset that match the relevant prefix (inventory_ or sales_). There is no static dealer ID list — new dealers are picked up automatically as they are onboarded to the collection pipeline.

Sold rows​

A sold row adds sale_date and drops status_code and status_description. The sales feed has no live status column; the only status available is the POC order document's Status: line, frozen when the document was generated. It reads KZ (released by plant and invoiced) for effectively every sold vehicle, where the active feed reads KZX (delivered to dealer) for the same vehicles. A sold vehicle was delivered, so the value is false rather than stale, and null is the honest answer. is_cancelled is derived from status_code == "ZA" and therefore reads False on every sold row; only the active feed distinguishes a cancelled order.

A vehicle that one dealer account sold can also appear, with the same sale_date, in the sales_{dealerId} tables of other accounts in the same dealer group. The POC's Sold to: block names the selling dealer, for example AUTO PARK EAST (68808). sold_to_dealer_code holds that code, and is_sold_to_dealer is true on the row whose dealer_code equals it. Both columns are null when no sold-to code parses from the POC. The transformed tier's tiebreak keeps the row where is_sold_to_dealer is true, and ranks a null below false.

Unwound sales​

An unwound sale is a sale that was reversed, with the vehicle returned to stock. DealerConnect's New Vehicle Delivery Report (NVDR) lists it as sale type M ("Unwinds"). The sales feed keeps the original row of an unwound sale and has no column that marks the reversal. The active feed keeps listing the vehicle.

Consolidated inventory uses sale_date as inferred_sold_date unless the active feed listed the vehicle more than 7 days after sale_date. For that vehicle, and for a vehicle without a sale_date, it infers the sale date from the last date the DealerConnect active feed or the public scrape listed the vehicle. The public scrape is not part of the 7-day check, because dealer websites keep listing a sold vehicle for about two weeks after the sale.

Cancelled orders​

A cancelled order (status_code = 'ZA') stays in the active feed for about two weeks after it is cancelled. After that, no feed lists it, and it never has a sale_date.

Consolidated inventory infers a sale date from the last date the active feed or the public scrape listed the vehicle, so it excludes cancelled orders from that inference. For a cancelled order, inferred_sold_date is null, and is_in_inventory and is_active are false. A cancelled order therefore counts in neither the sales statistics nor the inventory statistics.

POCs of another model year​

An order converted to the next model year keeps its VON and gets a new VIN. The collection pipeline requests a row's POC by VON, model code and model year, which the row's poc_order_data column records. A row of the converted order can therefore come with the POC of the cancelled earlier model year, which names the earlier VIN. A row's vin_partial column holds the last eight characters of its own VIN, and is empty before a VIN is assigned.

The parser ignores the POC when both the row's vin_partial and the POC's VIN are non-empty and the VIN does not end with vin_partial. It also ignores the POC of a sold row when the POC's status is ZA (cancelled), because a sold vehicle was not cancelled. The parser still takes a sold row's sold_to_dealer_code from an ignored POC, because the sold-to dealer belongs to the order. The transformed tier's tiebreak reads it through is_sold_to_dealer. A sold row whose POC the parser ignores has no VIN, model year or feature references. Consolidated inventory merges it with the order's active-feed row on the order number.

When an active row's vin_partial is empty or its POC has no VIN, the parser cannot compare the two and keeps the POC, even when the POC is of another model year.

Required Environment Variables​

VariableDescriptionExample
STELLANTIS_GCP_PROJECTGCP project ID containing the DealerConnect dataset (defaults to ai-app-stellantis)my-gcp-project
ZYTE_DEALERCONNECT_API_KEYZyte API key for requests to DealerConnect. When it is unset, the key is read from the Secrets Manager secret that ZYTE_DEALERCONNECT_API_KEY_SECRET_ID names. container_context.yaml sets that variable to ordering/zyte/dealerconnect-api-key—

The BigQuery dataset name defaults to dealerconnect and is set as a component YAML attribute (dataset:) on each DealerConnectRawSourceComponent subclass — it is not an environment variable.

Authentication​

GCP Access describes the BigQuery credentials.

The federated role requires the following BigQuery IAM roles in ai-app-stellantis:

  • roles/bigquery.dataViewer — read table data, and list tables for dealer discovery
  • roles/bigquery.jobUser — run queries
  • roles/bigquery.readSessionUser — read query results through the Storage Read API

Dagster Resource Key​

The BigQuery client is injected via the standard BIG_QUERY_RESOURCE_KEY resource (value: "big_query_resource") exported from ai_dagster.components. All DealerConnectRawSourceComponent subclasses declare this key in _required_resource_keys() and obtain the client via get_bq_resource(context) (exported from ai_dagster.components).

Model Code Structure​

A model_code is six characters, opening with a two-character body code: DT1H41 is a DT (RAM 1500), WLTH74 a WL (Grand Cherokee), JLJS74 a JL (Wrangler).

The body code is what ties a model to its place in the EZ Order wizard. A family's order_make opens with the same two characters — DT1500DTLIGHTDUTYCREWCAB… holds the DT… model codes — and order_year is the year code concatenated with it (T + DT = TDT for MY2026 RAM). Ordering uses this to reach a model's own families first instead of walking every family the dealer offers.

The relationship is a naming convention DealerConnect follows, not a guarantee it publishes, so resolution treats it as an ordering hint and still falls back to every family.

Raw Asset Contract​

Each raw asset row has the standard RAW_ROW_SCHEMA columns. The _response_body column contains a JSON-serialized BQ row dict with all original columns from the source table, including poc_raw_blob (for inventory/sales) or pdf_raw_blob (for code guides), which may be null if the collection pipeline failed to parse that PDF.

Feature Catalog​

feature_catalog is a structured (non-blob) table: one row per (brand_code, model_code, model_year, package_code, feature_code), describing a feature available on a model/trim. It feeds both the features and models entities.

Implementation: sources/feature_catalog.py (BQ fetch + extractors), StellantisFeatureCatalogRawComponent (raw), StellantisFeatureCatalogTransformedComponent (transformed, two assets: feature_catalog_features + feature_catalog_models).

Raw query​

BQHistoricalRawComponent._fetch_from_bq queries WHERE DATE(fetch_date) = @partition_date. The table holds many intra-day captures across multiple dealers, so duplicate rows per feature are kept verbatim in raw; the transformed tier's _clean collapses them to one row per entity key. The nested package_contents array is preserved in the raw _response_body.

Field mapping (transformed)​

Entity fieldSource
option_codefeature_code (package row) / package_contents[].code (member rows), prefix-stripped
feature_prefixleading * (seat) or - (interior color) stripped off the code
lower_level_packagepackage_code
trim_identifierpackage_code[-1]
model_code, model_yeardirect
brandbrand_code via dealer_locator.BRAND_CODES (J→jeep, C→chrysler, …)
short_descriptionfeature_name / content name
msrpmsrp (dropped on split seat/color rows — a combo price is not a per-half price)
category_* / subcategory_*backfilled by joining equipment_categories (not in this table)

Code normalization​

feature_code comes in two formats: 3-char codes (e.g. ACG) and 7-char concatenated seat/trim codes (*SEAT/-COLOR, e.g. *E7/-X9). The concatenated form is split on / into one feature per half, and the leading */- prefix is stripped into feature_prefix (bare code in option_code). This matches how every other source (equipment_categories, configure, catalog_option, inventory) represents seat and interior-color options, so the codes join cross-source.

Prefix removal has two implementations. ai_dealer_connect.codes serves this source and the other private dealer-portal sources, and ai_public_api.stellantis.configurator serves the public configurator. ai_public_api must not depend on the private dealer-portal client, so the two cannot share one implementation.

package_contents explosion​

Each row is emitted as a feature, and every package_contents item becomes a member row keyed parent_package_code = feature_code.

parent_package_code is null only where the feed offers the code in its own right. A member reaches us only through its package, so it gets no row of its own. A Package Content row is content of the CPP it names in package_code and is parented to that CPP — JHC (rain sensitive wipers) on Compass VF1L13 is offered nowhere on its own, and becomes parent_package_code = 22G / 22H.

feature_group gates none of this. A row is both an offer line and a package wherever it has members: all 40 STJ rows are filed under Additional Features, priced $1,495–$3,295, and list the four codes Wrangler 279738 p29 prints beneath STJ SKY ONE-TOUCH POWER TOP in GROUP DETAIL.

This matches code_guides, which is what lets the two feeds join.

Flags​

Entity fieldSource
is_initially_selectedinitially_selected
is_requiredrequired
is_orderable_standalonethe row is one of the feed's own offer lines, not a member

is_initially_selected is not standard. equipment_categories and code_guides own that field and mean the code is standard equipment; this feed says only what its configurator opens with.

This table does not populate is_configurable — code_guides answers it for consolidated (priority 3) and configure covers the brands the guides do not (priority 5). It is not a source group for that field. Paint procedures and seats are the two exceptions below.

Paint procedures are the first exception. A build has one paint procedure, chosen with its color. DealerConnect orders it as paintProc. The code guides list APA, APD and APP under STANDARD EQUIPMENT, so code_guides sets is_configurable to false for them. A code that is not configurable is never added to a candidate, and DealerConnect adds APA when it cures a candidate that has no paint procedure. Consolidated marks the three codes configurable where this table lists them on their own row or code_guides lists them. The subcategory visibility check does not apply to them, because the configurator renders no control for a paint procedure.

This table lists APA on its own row. It lists APP only as content of the MXS package, and it does not list APD. Consolidated therefore marks APP and APD configurable only where code_guides lists them.

Seats are the second exception. A code guide lists a trim's seat as content of the trim's customer preferred package (CPP). That listing says only that the trim gives access to the seat. The guide's color/trim grid prints the seat as orderable. The grid can also print, beside it, seats that the trim cannot be ordered with. For example, the 2027 Grand Cherokee guide prints B6 and D6 next to Nappa leather FL on Limited Reserve, but every Limited Reserve has FL.

This table lists the seats a dealer can order. A seat is a * code. Seats inside option packages count as offers, and content of the trim's own CPP does not. Where this table offers a trim exactly one seat, enriched/code_guides_features sets is_configurable to false on the guide's standalone row of that seat. enriched/code_guides_features reads this table's partition of the same date. For interior colors (- codes), is_configurable comes from the guides alone.

Co-occurring spare-tire codes on Grand Cherokee​

Grand Cherokee Laredo 4WD (model_code WLJH74 / WLJH75) installs exactly one spare tire, but the POC can list several spare-tire codes at once. Three codes matter here: ABR is the trailer-tow package, whose POC content includes the full-size spare TBP; TBK is the standalone compact spare; and TBP is the standalone full-size spare. The inventory transformed source builds feature_refs from the POC parent + indented children (option_details), so a car ordered with the compact spare TBK and the tow package ABR returns all of ABR, TBK, and TBP — TBP arriving as ABR's package child even though the flat order code string carries only TBK.

ai_dagster.components.FeatureCoOccurrenceTransformedComponent strips the superseded codes via the shared feature_co_occurrence_removals template var: ABR supersedes both standalone spares (if_present: ABR drops TBK and TBP), and TBK supersedes TBP. This mirrors the synthetic_candidates spare-tire mutual exclusion for the same body, so real inventory and synthetic candidates carry the same one-spare invariant.

Feature reference parentage​

The POC pricing matrix prints package content indented under its package, so the inventory transformed source populates feature_refs[].parent_package_code directly from the blob — DealerConnect is the only Stellantis inventory feed that knows its own package membership. The public search reports a bare comma-separated option string and has none.

Both feeds are routed through EnrichedInventoryComponent, which preserves a parent the blob populated and infers the rest from consolidated features. DealerConnect needs it for the codes the blob prints flat: POC indentation covers the packages the pricing matrix chose to nest, not every package membership the feature catalog records.

feature_co_occurrence_removals matches on option_code alone, so a superseded code is dropped whether the feed reported it standalone or as package content — on a WLJH74 car carrying ABR, the TBP printed as ABR's child is dropped along with the standalone TBP, leaving ABR to carry the spare.

Operational Notes​

  • NULL blobs are retained in the raw asset so collection failures are auditable. The transform-tier extractors skip NULL blobs gracefully and log a warning per skip.
  • Missing dealer tables are warned and skipped — a partition can materialize successfully even if some dealers have no data for that date.
  • Zero tables found is logged at info level and results in an empty DataFrame — this is valid for early backfill dates before any dealers were onboarded.

Upstream fragility: Struts transaction tokens​

The collection pipeline's allocation table (allocations_{dealerId}) is fed by a DealerConnect action that accepts a request only with a single-use Struts transaction token. DealerConnect generates the token when it serves the landing page (allocationMain.do) and clears it once the report request has sent it. A request that sends it again gets a generic "can not process your request" HTML page that is not a login page. DealerConnect can change which actions require a token (or how strictly it checks one) server-side, without notice, and independently of its sibling endpoints — a single table going stale while the rest keep updating is consistent with exactly this.

Two consequences for this repo:

  • load_allocations falls back to MAX(fetch_date), so a dead collector is invisible to ordering runs — they keep succeeding on the last good snapshot. A staleness tripwire in allocations_bq.py ERROR-logs (→ Sentry) when the snapshot is older than _STALENESS_ALERT_DAYS, a threshold set just above the longest benign collection gap. It logs rather than raises: staleness is a paging matter, not grounds to fail an in-flight run.
  • Any direct DealerConnect fetch added here (see ai_dealer_connect) must treat a non-PDF/non-login HTML response as a portal-side fault, not an expired session, and should check whether the action's landing page serves a strutsToken before assuming a bare GET works.

Full details: ppm-ordering-services docs/oems/stellantis/architecture.md, "Struts transaction tokens".

Allocation report​

The stellantis_us_dealerconnect sensor (Allocation Windows) reads DealerConnect's allocation report with a captured session from stellantis_user_info (cookies DCDP and SMSESSION). DealerConnect refuses direct requests from our deployments, so the requests go through Zyte. Each read costs two Zyte requests. The first request is for allocationMain.do, which serves a new Struts token. The second request is for the report and sends that token. ai_dealer_connect reads two values from the report PDF:

  • the month the report's allocations are for, from the title line Allocation Report - <Month> <Year>;
  • the note on the Notes: line, which gives the allocation date.

The allocation date is the date on which Stellantis next allocates units to dealers.

Once a month, DealerConnect changes the report to the next month. The change happens on the allocation date or on the morning after. The note usually changes on the allocation date or the day before.

The sensor treats the month as an allocation window with the code YYYY-MM. The window is open when a report the sensor read shows units allocated to its dealer. The report has no order deadline.

From the day before the allocation date to the day after it, the sensor reads the report every allocation_date_interval_minutes (30). When the note gives no allocation date, the sensor also uses allocation_date_interval_minutes. At other times it reads the report every interval_hours (4). The time of the next read is in the sensor's cursor.

Known limits:

  • When Stellantis allocates units a second time within the same month, the window already has a partition, so AllocationWindowsComponent posts no message for the second allocation. Such a note reads JUNE ALLOCATIONS FOR AUGUST WILL RUN AGAIN 6/16/2026***.
  • An allocation that covers several months shows as one month in the report, so no new window opens until the report's month changes.
  • When the note changes on the allocation date itself, the sensor reads the new note up to interval_hours (4) later.