Asteria Fleet Dq Certification

Asteria Fleet DQ Hub Certification

When to use

Use this skill for any task that:

• Points at the "Asteria Fleet Data Quality Hub" via the placeholder <TASK_ENV_BASE_URL>, and
• Stages prompt.txt, payloads/case_scope.json, and payloads/answer_template.json, and
• Asks for a reconciled audit / certification / readiness brief returned as a single JSON object matching the template.

The canonical shapes are: fuel-purchase normalization, freight-charge accrual reconciliation, partner/contact-master certification, field-service roster contact-readiness, and maintenance-log integrity. A new collection maps to one of these families via the catalog family field (fuel, freight, contacts, maintenance).

Inputs

• prompt.txt — narrative of what to audit and which interfaces to use.
• payloads/case_scope.json — parameters: collection_id, cutoff timestamp, focus / decision-panel stable IDs, ranking policy, numeric precision, certification thresholds or gate, and the status→action map.
• payloads/answer_template.json — the exact output contract (JSON Schema or field contract). Authoritative for required keys, enums, patterns, ordering, and precision. additionalProperties: false is pervasive — never add keys.
• environment_access.md — the ONLY source for the hub base URL, the bearer token, and the allow-list of endpoints. Read it at runtime; do not hardcode credentials.

Connection (detail in references/hub_interfaces.md)

• Base URL and Authorization: Bearer <token> come from environment_access.md.
• Primary data access: POST /api/query with {"query": "<SQL>"} over logical views (v_fuel_transactions, v_freight_charges, v_contacts, v_maintenance_events, v_source_snapshots, v_fx_rates, v_reference_aliases, v_unit_conversions). Response: {columns, row_count, rows, truncated}. Page with LIMIT n OFFSET m; stop when truncated is false.
• Reference data: GET /api/reference/fx (unfiltered), GET /api/reference/aliases?domain=<fuel|freight|...>, GET /api/reference/conversions?kind=<volume|weight|distance|...>. All paginate as {items, limit, offset, total}.
• Discovery: GET /api/catalog/collections, GET /api/catalog/schema.

Operating procedure

Phase 0 — Intake & integrity

1. Read all three staged files. Parse case_scope.json and answer_template.json.
2. Confirm the staged task directory contains only the expected files; if anything unexpected is present, stop and surface it — never fold stray material into the answer.
3. From the scope extract: collection_id, cutoff, focus IDs, decision-panel ID lists, ranking policy (sort keys + tie-breaks), precision, and the certification gate/thresholds. From the template extract: every required key, enum, pattern, minItems/maxItems, and ordering rule.

Phase 1 — Discover hub structure

1. GET /api/catalog/collections; confirm the collection_id exists and note its family and source_systems.
2. GET /api/catalog/schema; note the fields of the view for that family.
3. Query v_source_snapshots for the collection (SELECT ... FROM v_source_snapshots WHERE collection_id='<id>'). Note each snapshot's snapshot_id, snapshot_status (CERTIFIED/PROVISIONAL/STALE), business_cutoff, created_at, row_count, checksum.

Phase 2 — Reconcile source records

1. Choose the AUTHORITATIVE snapshot: prefer CERTIFIED over PROVISIONAL over STALE; break ties by newest created_at (then ingested_at). It resolves overlapping logical records. Record its snapshot_id as authoritative_snapshot_id.
2. Pull all in-scope raw rows for the collection across snapshots (via the family's view, WHERE collection_id='<id>' AND <cutoff filter>). Apply the business cutoff to the correct timestamp field exactly as the scope states.
3. Deduplicate: rows representing the same logical entity/transaction across snapshots collapse to one logical record. The retained occurrence is the one from the authoritative snapshot (record retained_snapshot_id / survivor_row_id / master_id). raw_row_count = all rows; logical_*_count = deduped; duplicate_raw_count = raw − logical (per the template's definition).

Phase 3 — Classify (family-specific; see references/reconciliation.md)

Apply the classification the template's counts require. Categories recurring across families:

• Recognized — description/alias maps to exactly one canonical category (fuel type / service class).
• Unrecognized — zero alias matches.
• Ambiguous — more than one alias match.
• Mismatch — recognized canonical category differs from the source's expected_* field. Mismatches are VALID (they enter normalized totals) unless the task says otherwise.
• Quarantine — row cannot enter totals: unrecognized/ambiguous category, invalid/nonpositive quantity/weight/distance, invalid timestamp/odometer/labor, or no usable contact channel. Quarantined rows are EXCLUDED from normalized totals and from "valid" counts.
• Contacts only: duplicate-cluster merge, survivor selection, contested-identifier cases, channel readiness (usable email/phone + consent GRANTED + record ACTIVE).
• Maintenance only: missing/invalid timestamp, invalid odometer, negative/extreme labor, odometer regression (regressions go in corrected_metrics, not invalid_event_ids, unless stated).

Phase 4 — Normalize

1. Units: convert quantity/weight/distance to the scope's canonical unit (L, KG, KM) using v_unit_conversions (factor; respect valid_from/valid_to and precision).
2. Currency: convert amount to the scope's base currency (USD) using v_fx_rates (usd_per_unit) matched by currency and the relevant date (rate_date ≤ transaction date; acceptable rate_status).
3. Precision: round every numeric output to the decimal places declared in the scope/template (typically 2 for money/volume/distance; 4 for rates such as quarantine_rate). Counts are exact integers — never float.
4. Exclude quarantined rows from all normalized totals. Include valid mismatches.

Phase 5 — Aggregate & rank

1. Build the required totals (overall + per-category/per-class/per-fuel-type), counts, and rollups (per asset / region / depot / carrier / merchant).
2. Rankings: apply the scope's primary_sort + tie_breaks exactly. rank is 1-indexed. Honor limit / merchant_ranking_limit / carrier_ranking_limit.
3. For "exception" rankings, an exception is usually a distinct logical record that is a valid mismatch OR quarantined — read each template's definition; do not assume.

Phase 6 — Code panels (see references/code_panels.md)

For every public ID in the scope's decision panels / focus clusters / control cases, assign the opaque internal control code(s) the template requires.

• Allowed values come from THIS task's answer_template.json enums — do not import values from other tasks.
• Expansions are deliberately not supplied; infer each code from the underlying record's evidence (source system, snapshot status/basis, field-precedence system, validity/mismatch/quarantine outcome).
• Never guess; if evidence is insufficient, re-query the hub. One coded object per scoped ID, sorted as the template requires.

Phase 7 — Certify

1. Compute the gate metric the scope defines (e.g., quarantine_rate = quarantined / canonical_entities, or an odometer-regression flag).
2. Map to status via the scope's status_thresholds / certification_gate (PASS / PASS_WITH_EXCEPTIONS / HOLD).
3. Map status → action via the scope's status_action_map (RELEASE / REVIEW_EXCEPTIONS / BLOCK_AND_REMEDIATE). Some scopes hard-code the gate (e.g., any regression ⇒ HOLD ⇒ BLOCK_AND_REMEDIATE).
4. Match the template's exact key names (status/action, or status/routing, or status/next_action).

Phase 8 — Emit

1. Produce exactly ONE JSON object. No commentary, no Markdown, no trailing text.
2. Conform exactly to answer_template.json: every required key present, no extra keys (additionalProperties: false), correct types/enums/patterns and minItems/maxItems.
3. Ordering: sort every ID list lexicographically ascending unless the template says otherwise; sort object arrays by the template's stated key; apply tie-breaks.
4. Deduplicate all ID sets. Round numerics to the declared precision.

Cross-cutting rules (gotchas)

• Read environment_access.md at runtime for base URL, token, and allowed endpoints. Use only those endpoints.
• Quarantined rows are excluded from normalized totals; valid mismatches are included. The most common error.
• additionalProperties: false everywhere — never add helper or extra keys.
• Pagination truncates: both /api/query (truncated, LIMIT/OFFSET) and GET reference endpoints (limit/offset/total). Always page to completion; never assume a single response is complete (collections routinely span multiple pages).
• The authoritative snapshot resolves overlaps — duplicates collapse to the authoritative-snapshot occurrence.
• The cutoff is a filter — apply it to the correct timestamp field before counting.
• Counts are exact integers; only declared numeric fields are rounded floats.
• Codes are inferred from evidence, constrained by the current template's enum — not memorized.
• Stable IDs only — use IDs present in the public data or the scope; preserve required ordering.

References

• references/hub_interfaces.md — endpoint catalog, request/response envelopes, SQL query contract, pagination, view field reference.
• references/reconciliation.md — per-family classify / normalize / rank specifics.
• references/code_panels.md — opaque code vocabulary and inference method.