Asteria Fleet Data Quality Hub
Overview
You are auditing operational fleet data through a stateless read-only HTTP hub at the URL in environment_access.md (key base_url). The hub exposes collections through views you query with SQL via POST /api/query. Every audit produces a single machine-readable JSON answer matching the provided answer template — no commentary, no markdown wrapping.
Quick Start
- Read the task
prompt.txtfully — identify the collection, cutoff, and answer template. - Read
payloads/case_scope.jsonfor business parameters (focus entities, decision panels, thresholds, ranking policies). - Read
payloads/answer_template.jsonfor the exact output shape, required fields, allowed enum values, and ordering rules. - Read
environment_access.mdfor the base URL and any query credentials. - Discover the hub:
GET /api/catalog/schemaandGET /api/catalog/collections. - Query data through
POST /api/querywith JSON body{"sql": "<query>"}. Use the scripts helper for paginated queries. - Produce one JSON object conforming to every constraint in the answer template. Order lists per template rules (ascending sort, lexicographic for strings).
Hub API
Endpoints
| Method | Path | Purpose |
|---|---|---|
| GET | /api/catalog/collections |
List all collections with family, source systems, time range, approximate row counts |
| GET | /api/catalog/schema |
Unified schema for all views (field names, types, meanings) |
| GET | /api/contacts |
Contact records (query param: collection_id) |
| GET | /api/transactions/fuel |
Fuel transaction records (query param: collection_id) |
| GET | /api/transactions/freight |
Freight charge records (query param: collection_id) |
| GET | /api/maintenance/events |
Maintenance event records (query param: collection_id) |
| GET | /api/reference/aliases |
Alias-to-canonical mappings for fuel types, service classes, and freight categories |
| GET | /api/reference/conversions |
Unit conversion factors (kind, from_unit, to_unit, factor, precision) |
| GET | /api/reference/fx |
FX rates with USD-per-unit values |
| GET | /api/source-snapshots |
Snapshot metadata (query param: collection_id) |
| POST | /api/query |
Execute read-only SQL against schema views. Include any credential from environment_access.md. |
Views (from /api/catalog/schema)
- v_contacts —
collection_id,row_id,snapshot_id,source_system,source_record_id,person_or_org_name,email,phone,city,region,country,consent_status,record_status,verified_flag,business_updated_at,ingested_at,master_hint - v_fuel_transactions —
collection_id,transaction_id,snapshot_id,asset_id,merchant_id,purchased_at,expected_fuel_type,purchased_description,quantity,quantity_unit,currency,amount,record_status,business_updated_at,ingested_at - v_freight_charges —
collection_id,charge_id,snapshot_id,invoice_id,invoice_line_no,carrier_id,lane_id,service_date,expected_service_class,description,billed_weight,weight_unit,distance,distance_unit,currency,amount,record_status,business_updated_at,ingested_at - v_maintenance_events —
collection_id,snapshot_id,event_id,work_order_id,asset_id,event_type,event_time_raw,odometer_value,odometer_unit,labor_hours,parts_cost,currency,technician_id,event_status,business_updated_at,ingested_at - v_reference_aliases —
domain,alias_id,alias_text,canonical_value,valid_from,valid_to,reference_status,published_at - v_source_snapshots —
collection_id,snapshot_id,source_system,snapshot_status,business_cutoff,created_at,ingested_at,row_count,checksum - v_unit_conversions —
kind,from_unit,to_unit,factor,valid_from,valid_to,precision - v_fx_rates —
rate_date,currency,usd_per_unit,rate_status,published_at
Snapshot Resolution
Every collection has multiple snapshots. Resolve the authoritative source:
- Query
v_source_snapshotsfiltered bycollection_id. - The authoritative snapshot is the one with
snapshot_status = 'CERTIFIED'whosebusiness_cutoffis on or before the task cutoff. If multiple certified snapshots exist, take the latest. - For most audits, filter all data queries to the certified snapshot.
- For cross-snapshot deduplication: when the same logical ID (transaction_id, charge_id, event_id) appears in multiple snapshots, keep the certified occurrence and report the others as duplicates. Always prefer the certified snapshot's row.
Querying
- Use
POST /api/querywith JSON{"sql": "<SQL>"}. - The hub returns paginated results: check if
total> number of returneditems. If so, useLIMITandOFFSETto page. - Use scripts/query_hub.py for deterministic paginated queries — it handles paging and returns the full result set.
- Reference data endpoints (
/api/reference/aliases,/api/reference/conversions,/api/reference/fx) may or may not be paginated. Check response structure and page if needed. - When using collection-specific endpoints like
/api/contacts, supply thecollection_idquery parameter.
Audit Patterns
Contact Reconciliation
See references/contact-rules.md for the full deduplication, survivor selection, and readiness rules.
- Load all contact rows from the certified snapshot.
- Cluster into canonical people: match on normalized email (NFKC lowercase, trimmed), phone digits, and name similarity. The
master_hintfield indicates pre-grouped clusters when populated. - Select a survivor (master) row per cluster by source-system precedence and recency. Determine canonical field values by field-level source-system precedence.
- Quarantine rows with no usable contact channel (null/empty email AND null/empty phone).
- Compute readiness: an entity is eligible if
record_status = 'ACTIVE'AND has at least one usable email or phone; a channel is ready only whenconsent_status = 'GRANTED'. - Apply control codes per references/codes.md.
Transaction Audit (Fuel & Freight)
See references/aliases.md for alias lookup and classification rules.
- Load all transactions/charges from the certified snapshot.
- Load reference aliases for the relevant domain (
fuelorfreight), filtered by transaction dates againstvalid_from/valid_to. - Load unit conversions and FX rates for normalization.
- For each row, resolve the recognized category by matching
purchased_description/descriptionagainst aliases (case-insensitive, trimmed). See references/aliases.md for the matching strategy. - Classification outcomes:
- Valid match: Exactly one recognized canonical value.
- Unrecognized: No matching alias.
- Ambiguous: Multiple distinct canonical values match.
- Mismatch: Valid match whose canonical value differs from
expected_fuel_type/expected_service_class.
- Quarantine: Unrecognized, ambiguous, non-positive quantity/weight/distance. Do NOT include quarantined rows in normalized totals.
- Mismatch rows are valid (included in normalized totals) but tracked separately.
- Normalize amounts to USD using the effective FX rate for each row's currency on or before the row's date, preferring
rate_status = 'final'. Normalize quantities to canonical units (L for fuel, KG and KM for freight) using conversions. - For ranking: follow the ranking policy in
case_scope.json(typically exception counts descending, then ID ascending as tiebreak).
Maintenance Integrity
- Load all maintenance events — from ALL snapshots for duplicate detection, from the certified snapshot for the authoritative data.
- For each row, detect issues:
missing_timestamp:event_time_rawis null or empty.invalid_timestamp:event_time_rawcannot be parsed as a UTC timestamp.invalid_odometer:odometer_valueis null or outside [0, 999999].negative_labor:labor_hours< 0.extreme_labor:labor_hours> 24.odometer_regression: within an asset, events ordered by parsedevent_timewhereodometer_valuedecreases (after converting to km). Check only among valid (non-rejected) events within the certified snapshot.
- invalid_event_ids: Rows rejected for missing/invalid timestamp, invalid odometer, negative labor, or extreme labor. Odometer regression events are NOT rejected — they stay valid but are flagged in
corrected_metrics. - Duplicates: same
event_idacross snapshots, retain certified. - Calculate corrected distance: sum across assets of (last valid odometer - first valid odometer), converting all odometer readings to km.
- Asset risk ranking: per ranking policy in
case_scope.json. - Apply decision codes per references/codes.md.
Certification Decision
- Compute the exception/quarantine rate as specified by the task (usually quarantined / canonical entities, or exception transactions / total logical count).
- Compare against thresholds in
case_scope.json:- If rate <=
pass_max_quarantine_rate->PASS/RELEASE. - If rate <=
pass_with_exceptions_max_quarantine_rate->PASS_WITH_EXCEPTIONS/REVIEW_EXCEPTIONS. - Otherwise ->
HOLD/BLOCK_AND_REMEDIATE. - Some cases use hard gates (e.g., odometer regression always ->
HOLDregardless of rate).
- If rate <=
- Apply the status/action mapping from
case_scope.jsonor thecertification_gatein the case scope.
Resources
scripts/
- query_hub.py — Deterministic helper that executes paginated SQL queries against the hub and returns full result sets.
references/
- codes.md — Control/policy/decision code catalog and assignment rules.
- aliases.md — Alias domains, canonical categories, and lookup strategy.
- contact-rules.md — Contact deduplication, survivor selection, and channel readiness.