# Reflect 3 Attempt 03

> Asteria Fleet Data Quality Hub — Audit & Certification Skill

- Skill: `prism-shadow/reflect-3-attempt-03-21` (Agent Skill, multi-file: 5 files)
- Install (CLI): `npx skillmds@latest add prism-shadow/reflect-3-attempt-03-21`
- Raw SKILL.md: https://api.skillmd.com/api/skills/prism-shadow/reflect-3-attempt-03-21/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Security
- Author: prism-shadow (https://skillmd.com/u/prism-shadow)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/prism-shadow/reflect-3-attempt-03-21

---

# Asteria Fleet Data Quality Hub — Audit & Certification Skill

## Purpose

Solve data-quality audit and certification tasks against the Asteria Fleet Data Quality Hub. Each task asks you to reconcile overlapping source records, identify data-quality issues, compute normalized metrics, assign internal control codes, and produce a certification or release decision. The answer is always a single JSON object conforming to a strict schema supplied in `payloads/answer_template.json`.

## Entry Instructions

1. **Read the task materials.** Open `payloads/case_scope.json` and `payloads/answer_template.json` from the task's input directory. The case scope gives you the collection ID, business cutoff, seed/stable IDs, decision panels, certification thresholds, and action maps. The answer template is a JSON Schema that your output must satisfy exactly.

2. **Read `environment_access.md`** for the base URL, auth token, and allowed endpoints.

3. **Discover the catalog and schema.** `GET /api/catalog/collections` lists collections by family (contacts, fuel, freight, maintenance, …) with approximate record counts and source systems. `GET /api/catalog/schema` describes every logical view (`v_contacts`, `v_fuel_transactions`, `v_freight_charges`, `v_maintenance_events`, `v_reference_aliases`, `v_source_snapshots`, `v_unit_conversions`, `v_fx_rates`) and its fields.

4. **Query all required data** using `POST /api/query` with body `{"query": "<SQL>"}`. Paginate with `LIMIT 500 OFFSET n`. Key queries per task:
   - Source snapshots: `SELECT * FROM v_source_snapshots WHERE collection_id = '<id>'`
   - Transaction / event / contact rows: `SELECT * FROM <view> WHERE collection_id = '<id>' ORDER BY <id_col>, snapshot_id`
   - Reference aliases: `SELECT * FROM v_reference_aliases WHERE domain = '<domain>'`
   - Unit conversions: `SELECT * FROM v_unit_conversions WHERE kind = '<kind>'`
   - FX rates: `SELECT * FROM v_fx_rates`

5. **Determine the authoritative snapshot.** Among snapshots whose `business_cutoff` matches the case scope, prefer `CERTIFIED` over `PROVISIONAL` over `STALE`. Break ties by earliest `created_at`. The authoritative snapshot resolves overlapping records.

6. **Deduplicate into logical records.** Group raw rows by the primary ID column (row_id, transaction_id, charge_id, event_id). For each logical entity keep the row from the authoritative snapshot; count the rest as duplicates.

7. **Reconcile / merge sources.** The reconcilation logic varies by collection family:

   - **Contacts family** — Cluster rows by normalised email (NFKC, lowercase, trimmed). Use Union-Find for transitive merging. Do NOT cluster by phone (shared helpdesk numbers cause false merges). For each canonical person, pick the survivor from the highest-priority source system. For canonical field values, apply source-system precedence: `Compliance Master > Partner Portal > CRM` (or `Identity Registry > HR Directory > Dispatch` for roster tasks). Handle sentinel null values (`None`, `none`, `NULL`, `N/A`, empty, whitespace) — they are not usable contact channels. Quarantine = entity with no usable email AND no usable phone. Readiness-eligible = active + at least one usable channel. Channel ready = eligible + consent GRANTED.

   - **Fuel / Freight family** — For each logical transaction/charge, match its description against reference aliases to determine the *recognised* category. Use greedy longest-match to avoid ambiguity (e.g., "bio diesel" should match the longer alias "bio diesel" → BIODIESEL, not also "diesel" → DIESEL). Only include aliases that are `ACTIVE` and valid for the cutoff period (`valid_from ≤ cutoff_date ≤ valid_to`). Classify: exactly one match and matches expected → valid; one match but differs from expected → mismatch (still valid for totals); zero matches → unrecognized; >1 match → ambiguous. Quarantine = unrecognized OR ambiguous OR invalid quantity/weight/distance. Mismatches are NOT quarantined — they enter normalized totals.

   - **Maintenance family** — Validate each event: parse timestamps, check odometer ≥ 0, labor ≥ 0 and ≤ threshold, detect odometer regression (later event with lower odometer for same asset). Invalid events are excluded from corrected metrics but reported in issue_counts. Odometer regression events also count toward regression counts and drive HOLD certification.

8. **Compute normalized metrics.** Convert quantities to canonical units using `v_unit_conversions` factors. Convert amounts to USD using certified FX rates from `v_fx_rates` with `rate_date ≤ transaction_date` (for USD, rate ≈ 1.0 — use 1.0). Round volumes/distances to 2 decimal places. Only include valid (non-quarantined) records in totals.

9. **Assign internal control codes.** These compact codes are opaque but follow observable patterns. Best-effort mapping from training:

   | Code family | Values | Likely meaning |
   |---|---|---|
   | IC (Identity) | IC-25, IC-40, IC-70, IC-90 | Verification strength: 0 verified → 25, 1 → 40, 2 → 70, 3+ → 90 |
   | FP (Field Provenance) | FP-20, FP-55, FP-75 | Source authority: CRM/low → 20, Partner Portal/HR → 55, Compliance Master/Registry → 75 |
   | OR (Outreach) | OR-15, OR-35, OR-60, OR-80 | Contact readiness: no channel/UNKNOWN → 15, PENDING → 35, DENIED → 60, GRANTED → 80 |
   | RB (Reference Basis) | RB-17, RB-42, RB-83 | Permanent ACTIVE → 83, time-bounded/PROVISIONAL → 42, INACTIVE/not-yet-valid → 17 |
   | SB (Source Basis) | SB-24, SB-61, SB-79 | From authoritative/certified → 79, provisional → 24, mixed → 61 |
   | LD (Ledger Disposition) | LD-14, LD-31, LD-53, LD-72, LD-88 | Accepted → 88, class mismatch → 53, unrecognized/ambiguous → 31, rejected → 14 |
   | MS (Maintenance Source) | MS-12, MS-47, MS-86 | Snapshot authority level |
   | HR (History Route) | HR-19, HR-33, HR-74 | Accepted → 74, flagged → 33, rejected → 19 |

   **Important:** These mappings are inferred from observed patterns and may not be fully correct. Validate them against the specific data characteristics of each collection.

10. **Determine certification / release status.** Apply the thresholds from `case_scope.json`:
    - Compare quarantine rate (or exception count) against `status_thresholds`.
    - Map the resulting status through `status_action_map` (or `certification_gate` for maintenance).
    - If odometer regressions exist → typically HOLD + BLOCK_AND_REMEDIATE (per `certification_gate`).

11. **Assemble the answer JSON** strictly matching `answer_template.json`. Pay attention to:
    - Required fields and their types.
    - Enum constraints on code fields.
    - `minItems`/`maxItems` for arrays (especially focus-cluster, decision-panel, and ranking arrays).
    - Sorting rules stated in descriptions or `x-ordering_rules` (usually lexicographic ascending by stable ID, or by count descending with ID ascending tiebreak).
    - Numeric precision (round to stated decimal places).

12. **Validate before submitting.** Check your JSON against the schema. Common mistakes:
    - Missing or extra top-level keys.
    - Wrong enum values in control codes.
    - Arrays with wrong number of items.
    - Floating-point where integer is expected.
    - Not rounding to required precision.

## Supporting Reference Files

- `reference/alias_matching.md` — Greedy longest-match algorithm for fuel/freight alias recognition
- `reference/control_codes.md` — Control code enumeration and inference rules
- `reference/quarantine_rules.md` — Quarantine criteria by collection family

## Key Gotchas

- **Phone numbers are NOT reliable cluster keys** for contacts — shared helpdesk numbers cause over-clustering.
- **`master_hint` on Compliance Master rows** signals contested identity (shared alias), not a merge instruction.
- **Alias validity dates** must be checked against the cutoff: FUA-016 ("priority" → PREMIUM_UNLEADED) starts 2026-03-01, so it is NOT valid for a January audit.
- **Sentinel null values** (`None`, `none`, `NULL`, `N/A`, empty string, whitespace) must be treated as absent, not as real data.
- **FX rate for USD** is sometimes ≈1.005 in the API but should be treated as 1.0 for base-currency conversion.
- **Mismatches ≠ quarantine.** A fuel/freight transaction whose recognised category differs from expected is still valid for normalized totals. Only unrecognized/ambiguous/invalid-measure records are quarantined.
- **ODOMETER_REGRESSION** in maintenance is reported in `corrected_metrics.regression_event_ids`, NOT in `invalid_event_ids` (sequence-only regressions vs. truly invalid events).