# Atlas Commerce Ops Scorecard

> Solve Atlas Commerce Operations cutoff-based analytics scorecard tasks — fulfillment, refund, carrier-quality, warehouse-productivity, and support-health reviews produced from a live read-mostly SQL workplace service. Use at the start of any task whose input is a prompt.txt + a JSON request (scope + business definitions) + an answer_template.json output contract, backed by an authenticated SQL/schema/data-dictionary service.

- Skill: `prism-shadow/atlas-commerce-ops-scorecard-2` (Agent Skill, multi-file: 4 files)
- Install (CLI): `npx skillmds@latest add prism-shadow/atlas-commerce-ops-scorecard-2`
- Raw SKILL.md: https://api.skillmd.com/api/skills/prism-shadow/atlas-commerce-ops-scorecard-2/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: prism-shadow (https://skillmd.com/u/prism-shadow)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/prism-shadow/atlas-commerce-ops-scorecard-2

---


# Atlas Commerce Operations Scorecard

This skill solves the family of Atlas Commerce Operations "scorecard / reconciliation / health review" tasks. Each task gives you: a `prompt.txt`, a JSON **request** file (scope, business definitions, status/risk policies), and an `answer_template.json` (the exact output contract). The workplace data lives behind an authenticated read-mostly SQL service (schema, data-dictionary, read SQL, and a controlled SQL transaction for approved corrections). Your job is to compute the requested metrics from the live data and emit one JSON object that conforms exactly to the template.

Run the steps in order. Do not skip the verification step.

## 0. What kind of task is this?

Trigger this skill when the task matches this shape:
- Inputs are `input/prompt.txt`, a JSON request under `input/payloads/*request*.json`, and `input/payloads/answer_template.json`.
- The prompt references a workplace/service base URL and endpoints like `GET /api/schema`, `GET /api/data-dictionary`, read-only `POST /api/sql`, and (for correction tasks) `POST /api/sql/transaction` plus `GET /api/correction-audit`.
- The output is a single JSON object written to `answer.json`, conforming exactly to the template, with no commentary.

If those match, this is an Atlas scorecard task. Read on.

## 1. Read every input verbatim before querying

Read `prompt.txt`, the request JSON, and `answer_template.json` completely. Capture, literally:
- **Scope filters**: account tier/segment/region, "production" population, campaign id, warehouse id, batch id, and every date/timestamp boundary (note whether inclusive). Treat all timestamps as exact UTC (they end in `Z`).
- **Cutoff timestamp(s)**: the cutoff used for "by the cutoff" / "as-of" / "at cutoff" predicates, distinct from any case/event **opened/created window**.
- **Business definitions**: each metric's exact wording (complete vs on-time, severe exception, leakage candidate, units_per_hour, SLA breach, etc.). The wording is the spec — do not paraphrase it.
- **Rounding**: which values round and to how many decimals ("round only final reported rates"; display decimals). Rank keys and tie-breaks for every array ("… ascending, then … ascending"; "EXCEEDS" = strictly greater, not `>=`).
- **Status / risk policies**: ordered rules (HEALTHY/WATCH/CRITICAL, STABLE/PRESSURED/AT_RISK, LOW/MODERATE/HIGH, CONTROLLED/ELEVATED/SEVERE, etc.) — evaluate top-down, first match wins.
- **Output contract**: required fields, `additionalProperties:false` (no extra fields), enums, array min/max sizes, ID patterns (e.g. `^ORD-[0-9]{6}$`). The submitted object must match this exactly.

## 2. Learn the schema, then map "production" / scope terms to columns

Call `GET /api/schema` and `GET /api/data-dictionary` and read them. Key gotchas encoded in the data:
- `accounts` has `is_internal` and `is_test` integer flags. **"Production" accounts / orders / shipments / cases = `is_test=0 AND is_internal=0`.** Test accounts are named "Sandbox…"; internal accounts are named "Internal Account…". Exclude both from any cohort whose scope says PRODUCTION_* or "production accounts". Other tiers/segments/regions filter literally on their columns.
- Many tables carry a denormalized `current_status`/`promised_at`-style snapshot. The data dictionary states these "may lag append-only event history." **Prefer append-only event/scan tables** (order_events, carrier_scans, case_events, warehouse_task_events, refund_attempts) over the snapshot column whenever a metric depends on state "at the cutoff."
- Raw vs canonical: tables like `carrier_scans` and `inventory_movements` keep `raw_*` source values AND `canonical_*` normalized values. Operational analytics use the canonical fields.

## 3. Query safely — the SQL service has hard limits

The read SQL endpoint silently caps results and rejects some shapes. Follow these rules or your counts will be quietly wrong:
- **Every result is hard-capped at 5000 rows regardless of any `LIMIT`.** The response carries `row_count` and `truncated`. If `truncated` is true, the rows are incomplete — your aggregate is wrong. Always check `truncated` before using a result.
- To stay under the cap, **aggregate in SQL** (`GROUP BY`, `MIN`/`MAX`/`COUNT`, `SUM`) and **restrict to the in-scope cohort** with `WHERE <key> IN (SELECT … FROM … WHERE <scope>)`. A single level of `IN (subquery)` is allowed and is the main tool for keeping result sets small.
- **Nested subqueries-in-FROM that themselves contain a subquery (or a self-join subquery) are rejected** with HTTP 400 `{"error":"query rejected"}`. Keep to at most one FROM-subquery level. `SELECT alias.*` and deeply nested aggregates also get rejected. Plain `COUNT(DISTINCT …)` and `GROUP BY … HAVING` are fine.
- Pass literals via `params` (positional `?`) for safety. Timestamps are ISO-8601 UTC strings (`…Z`); dates are `YYYY-MM-DD`.
- If a query you need is too large to return, fetch the **in-scope cohort's keys first** (small result), then query each child table restricted to those keys, and join/reduce in your own code.

## 4. Reconcile "effective" state and dedup retries before counting

Several metrics hinge on "effective" state at the cutoff. Apply these consistently:
- **Retry dedup**: append-only tables can re-ingest the same upstream event. Dedup by keeping the row with the **latest `ingested_at` per `(source_system, external_event_id)`** before any "effective" aggregation. (You will see duplicate rows that are otherwise content-identical.)
- **Effective final carrier status** of a shipment = the `canonical_status` of its latest scan at/before the cutoff — rank scans by `canonical_event_at` then `ingested_at` then `scan_row_id`, after dedup. A shipment is "delivered by the cutoff" only when this **final** scan is `DELIVERED`, *not* merely when some DELIVERED scan exists. (Earliest-delivered vs final-status diverges on real data; final-status is the operational model the dictionary intends.)
- **Carrier backlog** ("effective final carrier status is not DELIVERED") is the count of in-scope shipments whose effective final status ≠ DELIVERED. Compute it over the *production* cohort.
- **Support case state at cutoff**: replay case events ≤ cutoff; the case is active (open) at cutoff unless its last terminal event is `RESOLVED`; `REOPENED` after a `RESOLVED` makes it active again. `open_at_cutoff` = Open ∪ Reopened active states; `reopened_at_cutoff` = the Reopened subset. Do **not** trust `support_cases.current_status` (it lags).
- **Warehouse task completion/rework**: a task is completed/reworked when it has an effective (deduped) `COMPLETED`/`REWORK` event at/before the state cutoff — again prefer events over `warehouse_tasks.current_status`.

## 5. Compute clocks and money exactly per the policy

- **Support active time (SUPPORT_ACTIVE_TIME)**: the clock runs while the case is agent-active and **pauses during `WAITING_CUSTOMER`** (resume on `CUSTOMER_REPLIED`/next agent event). Start the clock at the case's **`opened_at` header field, not the `OPENED` event timestamp** (they differ by minutes; the data is seeded so `opened_at` lands cases just over the SLA thresholds). For `first_response`, use active time from open to the first `AGENT_RESPONDED`; an unresponded case uses active elapsed time to the cutoff. For resolution, a resolved case uses active time open→`RESOLVED`; an active case uses active elapsed to the cutoff. SLA thresholds come from the request per priority; a breach **exceeds** the threshold (strictly `>`).
- **Median active resolution hours**: across cases resolved at the cutoff; for an even count average the two central values; round to the stated decimals.
- **Money / FX**: convert each row's minor units to USD by the **daily** `fx_rates.usd_per_unit` for that row's currency AND that row's service_date. The USD rate itself varies by date — do **not** hardcode 1.0. Sum full-precision and round only the final reported value to the stated decimals.
- **Refund model**: `refund_attempts.status` is consistent per `refund_id` (one effective status per logical refund). `SETTLED` = effective refund; `REVERSED` rows (carrying `linked_refund_id`) = linked reversals. Count in-scope linked reversals **even when they target a non-settled refund**, and **subtract their USD value from net refund amount** (net = Σ settled USD − Σ in-scope reversed USD). Leakage compares each order's effective settled refund USD against its gross order USD (gross valued in the order's own currency at the refund's service_date rate), OR ≥2 unreversed effective settled refunds sharing a normalized reason code.

## 6. Correction tasks (carrier quality / minimal canonical correction)

When the request asks to apply an approved minimal canonical correction:
1. Find the **single** raw/canonical contradiction in the named import batch: `WHERE import_batch_id = ? AND raw_status <> canonical_status` (also bound by the cutoff). The affected scan row and its shipment are the correction target; the canonical field should be set to match the raw value.
2. Apply the correction in **one** `POST /api/sql/transaction` with two statements: a guarded `UPDATE carrier_scans SET canonical_<field> = ?, corrected_at = ?, correction_reason = ? WHERE scan_row_id = ? AND canonical_<field> = ?` (scope the WHERE to that one row and its old value so it changes exactly one business row) and an `INSERT INTO correction_audit` with **all** audit columns (audit_id, correction_key, entity_type, entity_id, source_row_id, field_name, old_value, new_value, reason_code, corrected_at, actor) from the request's `approved_correction` block. Set `expected_total_changes` to 2.
3. The result is `APPLIED` only when `total_changes == 2` (one business row + one audit row) AND a follow-up read query confirms the new canonical value on that scan row. Otherwise report `NOT_APPLIED` with the actually observed results.
4. Report backlog over the **production** cohort (production shipments with an effective scan in the named batch at/before the cutoff): pre-correction count, post-correction count (recompute after the UPDATE), their delta, and the post-correction delivered count. Use effective final carrier status (step 4).
5. Leave raw source values, source identity fields, and unrelated rows unchanged — only the one canonical field is corrected.

## 7. Assemble, rank, round, and validate against the template

- Build the single output object with exactly the template's required keys, no extras.
- For every array, apply the stated ordering and tie-breaks (e.g. worst regions/accounts "by rate ascending, then id ascending"; top reason codes "by net USD descending, then code ascending"). Sort ID arrays ascending unless told otherwise.
- Round only the final reported numeric values to the stated decimals (`multipleOf`/`decimal_places` in the template). Sort/rank on **unrounded** values, then round for output.
- Evaluate status/risk from the **unrounded** rates against the ordered policy rules, first match wins.
- Validate the object against `answer_template.json` before finishing: correct types, enums, `additionalProperties:false` (no stray keys), array size bounds, and ID regex patterns. Write the final JSON to `answer.json` with no commentary outside the JSON.

## Supporting references

- `references/data-model.md` — the concrete domain models (production filter, effective final carrier status, refund/reversal chain, support active-time, warehouse task events) distilled for quick lookup.
- `references/query-patterns.md` — safe and rejected SQL shapes against this service, and the truncation guard.
- `references/pitfalls.md` — the recurring traps that quietly corrupt counts, with the resolution for each.