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 pattern | Description | Partition |
|---|---|---|
inventory_{dealerId} | In-stock vehicles with POC blobs | DAY (fetch_date) |
sales_{dealerId} | Sold vehicles with POC blobs | None (filtered by fetch_date + sale_date) |
feature_catalog | Features 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
| Variable | Description | Example |
|---|---|---|
STELLANTIS_GCP_PROJECT | GCP project ID containing the DealerConnect dataset (defaults to ai-app-stellantis) | my-gcp-project |
ZYTE_DEALERCONNECT_API_KEY | Zyte 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 discoveryroles/bigquery.jobUser— run queriesroles/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 field | Source |
|---|---|
option_code | feature_code (package row) / package_contents[].code (member rows), prefix-stripped |
feature_prefix | leading * (seat) or - (interior color) stripped off the code |
lower_level_package | package_code |
trim_identifier | package_code[-1] |
model_code, model_year | direct |
brand | brand_code via dealer_locator.BRAND_CODES (J→jeep, C→chrysler, …) |
short_description | feature_name / content name |
msrp | msrp (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 field | Source |
|---|---|
is_initially_selected | initially_selected |
is_required | required |
is_orderable_standalone | the 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_allocationsfalls back toMAX(fetch_date), so a dead collector is invisible to ordering runs — they keep succeeding on the last good snapshot. A staleness tripwire inallocations_bq.pyERROR-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 astrutsTokenbefore 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
AllocationWindowsComponentposts no message for the second allocation. Such a note readsJUNE 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.