# Atlas Commerce Ops Review

> Solve Atlas Commerce Operations cutoff-based analytical review tasks (fulfillment, refund, carrier, warehouse, support). Read request + template contracts literally, replay event history at a cutoff, dedup imported rows, and produce a JSON object that conforms exactly to the answer template.

- Skill: `prism-shadow/atlas-commerce-ops-review` (Agent Skill)
- Install (CLI): `npx skillmds@latest add prism-shadow/atlas-commerce-ops-review`
- Raw SKILL.md: https://api.skillmd.com/api/skills/prism-shadow/atlas-commerce-ops-review/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-review

---


# Atlas Commerce Operations Review

These tasks ask for a **cutoff-based operational review**: take a cohort of production business records, freeze their state at an as-of cutoff, apply stated business definitions/SLAs/status policies, and emit one JSON object conforming **exactly** to an answer template. The plumbing (query service, schema, data dictionary, auth) is provided in the environment; this skill is the analytical methodology.

## 0. Read the contract before writing a line of SQL

Every task ships two JSON files in `input/payloads/`:
- a **request** (`*_request.json`): scope, windows, cutoff, business definitions, thresholds, status rules, ordering/tie-breaks.
- an **answer template** (`answer_template.json`): the exact output schema.

Treat both as load-bearing contracts:
- The **template** is the output law. `required`, `additionalProperties:false`, `minItems/maxItems`, `pattern`, `multipleOf`, and each field's `description` / `x-precision` define what to compute and how to format it. Emit **only** the schema's fields, in its shape, no commentary, no extra keys. Mismatched precision or an extra key fails the whole object.
- The **request definitions** are literal. Every clause carries weight: `inclusive`/`exclusive`, `at or before` vs `strictly before`, `more than 24 hours`, `below` (strict `<`) vs `at least` (>=), which count is the **denominator**. Decide each threshold operator from the wording; `>` vs `>=` flips borderline counts and everything downstream (rates, status, IDs).

If a definition and the template field description disagree, reconcile both before computing; the template field description usually restates the precise formula.

## 1. Two timestamps you must never conflate: window vs cutoff

- **Eligibility window** (e.g. `created_at`/`opened_at` in `[start, end]`): selects the cohort. Boundary inclusivity comes from the request.
- **As-of cutoff**: where you **freeze state** to measure elapsed time and terminal status. Events after the cutoff do not exist for this review.

A record is eligible by its window; its *state and metrics* are measured at the cutoff. Do not pull state from after the cutoff.

## 2. Production/test/internal scoping

- Always exclude test rows (`is_test = 0`).
- "Production accounts" scope: also exclude internal accounts (`is_internal = 0`) unless the request explicitly includes them. This is the most common population error — decide it deliberately from the request's `population` field.
- Segment/tier/region/warehouse filters come from the request's `account_scope`/cohort. Note whether region is the **account's** region or the **warehouse's** region — regional rollups use the assigned facility's region, not the customer's.

## 3. Imported rows are append-only and duplicated — dedup first

Raw event/scan/attempt tables receive import retries. The same logical event arrives multiple times under the same `source_system` + `external_event_id` with different `ingested_at`. Before any analysis, **dedup by `(source_system, external_event_id)` keeping the earliest `ingested_at`**. This applies to lifecycle event tables (support case events, warehouse task events) and to carrier scans.

Logical-business-row dedup is different: e.g. a `refund_id` is one logical refund that may have multiple settled rows — keep one row per logical id (min row id) to avoid double counting. Know which identity is the *logical* id vs the *physical* row id for each table; the data dictionary states this.

## 4. Derive state from event replay, never the status snapshot

Header tables carry a `current_status` convenience column the dictionary warns **"may lag append-only event history."** Never trust it. Replay the **deduped, cutoff-filtered** events in `(event_at, row_id)` order to derive the true state at the cutoff. Examples:
- support case open/resolved/reopened — terminal state is whatever the last relevant event leaves it as.
- warehouse task completed/rework — completeness comes from `COMPLETED`/`REWORK` events ≤ cutoff, not `current_status`.
- carrier shipment status — the latest canonical scan ≤ cutoff by `(canonical_event_at, scan_row_id)`.

## 5. Recurring / terminal events: first vs last

A status event can recur (e.g. `RESOLVED` followed by another `RESOLVED`). When measuring "time to resolution" you almost always want the **final** occurrence of the terminal event, not the first — the work was actually completed at the last one. Decide per definition; for *state-at-cutoff* questions, replay gives the final state automatically. Getting first-vs-last wrong shifts medians and breach tallies silently.

## 6. Breach populations and elapsed-time clocks

A "breach" metric is measured on the **whole eligible population**, not only cases that reached a terminal state:
- A **resolved/closed** case uses elapsed time from its start to its terminal event.
- A **still-open** case uses elapsed time from its start **to the cutoff** (the definition says "an active case uses active elapsed time at the cutoff").

Measure each case to its own endpoint. Do not restrict the breach count to resolved cases only.

### Support active-time clock
"Support active time" excludes periods the case sat waiting on the customer. Implement a clock that:
- starts at the case `opened_at`;
- **pauses** on `WAITING_CUSTOMER`;
- **resumes** on `CUSTOMER_REPLIED`, `REOPENED`, or `OPENED`;
- ignores other events for the waiting state.

Walk deduped events ≤ endpoint in `(event_at, case_event_id)` order accumulating only non-waiting seconds. First-response time = active time to the first `AGENT_RESPONDED`; if none, active time to the cutoff. Resolution time = active time to the (final) `RESOLVED`, or active time to the cutoff if still open. Thresholds are per priority and compared strictly (`exceeds` = `>`).

### Other elapsed clocks
Warehouse/productivity tasks use **productive minutes** attached to completion events (units / productive_minutes × 60), not wall-clock time. Use the unit each metric specifies.

## 7. Money, FX, and reversals

- Monetary amounts are **minor units** (smallest unit of the row's currency). Convert the minor amount by `(amount_minor / 100) * usd_per_unit`.
- `fx_rates.usd_per_unit` is **USD per one unit** of the named currency, keyed by `(rate_date, currency)`. Use the rate for the **transaction's service date and the row's currency** — not the order's date — unless the request names a different basis.
- **Effective** = settled minus reversals. A reversal links to its parent via `linked_refund_id`; count a reversal only if its parent is an in-scope effective settled logical refund. Net = Σ settled USD − Σ effective reversal USD.
- Comparison FX basis: when comparing a refund value to an order's gross, value the order gross **in its own currency at the refund's service-date rate** (per the request's `order_gross_comparison` clause).

## 8. Corrections (carrier/inventory) — minimal canonical, never raw

When a task approves a canonical correction:
- Correct **one canonical field on one business row** with a guarded `UPDATE`. Never touch raw source values, source-identity fields (`source_system`, `external_event_id`, row ids), or unrelated rows.
- Append **one** `correction_audit` row carrying every audit column (audit_id, correction_key, entity_type, entity_id, source_row_id, field_name, old_value, new_value, reason_code, corrected_at, actor) using the approved values from the request.
- Run the update + audit insert in one transaction with the stated `expected_total_changes`.
- Report `APPLIED` **only if** exactly one business row and one audit row commit **and** a post-change read query confirms the new canonical value. Any other outcome → `NOT_APPLIED` with the results actually observed. Do not claim success without the verifying read.
- Backlog/quality counts are taken **after** the correction (post state), and only over the in-scope cohort (e.g. shipments with an effective scan in the named batch at/before the cutoff).

## 9. Rankings, rollups, and ordering

- Honor the request's **order list exactly**, including tie-breaks. "Rate descending then id ascending," "first two by rate ascending then region ascending," "top three by units/hour then employee_id ascending" — each key and its direction is significant; ties resolved by the next key.
- Regional/segment rollups: pick the entity the request names (assigned warehouse region for fulfillment; account tier/segment for refunds).
- Re-derive each ranking from the same deduped, cutoff-frozen population as the counts — a different scoping for the rollup vs the totals is a common silent error.

## 10. Status classification

Apply the status rules **in the order written** (e.g. HEALTHY → WATCH → CRITICAL; STABLE → PRESSURED → AT_RISK; LOW → MODERATE → HIGH; CONTROLLED → ELEVATED → SEVERE). The last rule is the catch-all ("otherwise" / "all other outcomes"). Rates use the **specified denominator** (often eligible count — incomplete/active cases stay in the denominator). Evaluate conditions at full precision.

## 11. Rounding and precision

- Round **only final reported values** to the precision the template/request states (e.g. rates to 4 decimals, money to 2, medians to 2). Keep full float precision through every intermediate step; rounding mid-pipeline drifts rates and status boundaries.
- Median: over the resolved-at-cutoff population; for an even count, average the two central values, then round. `x-precision: 0` means integer.

## 12. Self-verification before submitting

- **Schema-check**: validate the emitted JSON against the template keys, types, precision (`multipleOf`), array lengths/uniqueness, and that no extra fields exist. A single schema slip fails the object.
- **Population consistency**: does the sum of mutually-exclusive states (open + resolved, complete + incomplete) equal the eligible count? Do rate numerators/denominators use the populations the request named?
- **Variant cross-check**: when a definition is genuinely ambiguous (first vs last terminal event; breach over resolved-only vs all cases; include internal or not), compute every defensible variant and pick the one matching the literal wording. If feedback indicates an error, the wrong field is almost always **downstream of one interpretation choice** — isolate which metric moved rather than re-rolling the whole answer.
- **Sanity bounds**: breach rates, completion rates, medians should sit in plausible ranges; a median at exactly a threshold or a 0%/100% rate usually means an off-by-one or a population bug.

## Quick task-type map

- **Fulfillment scorecard**: order cohort by campaign + creation window; complete = ≥1 physical shipment AND all physical shipments effectively DELIVERED by cutoff; on-time = every shipment delivered ≤ its promised_delivery_at; severe-exception by the 24h-over-promise rule; worst regions by unrounded regional rate asc then region asc.
- **Refund reconciliation**: dedup logical refunds, settle−reversals net in USD via service-date FX, top reasons by net USD desc then reason asc, leakage candidates (refund > gross in USD, OR ≥2 same-recode settled refunds), cohort risk by candidate rate + net USD thresholds.
- **Carrier quality**: one raw/canonical contradiction → minimal canonical correction + audit + post-verify; backlog = cohort whose final carrier status ≠ DELIVERED.
- **Warehouse productivity**: completion/rework from events ≤ cutoff; units/hour per employee from completed-event units & productive minutes; top employees and lowest team by stated orders; delayed high-priority = HIGH/URGENT, due before cutoff, not completed by cutoff.
- **Support health**: eligible case population; active-case state at cutoff; first-response and resolution active-time breaches over all cases (open cases measured to cutoff); severe-active = open/reopened at cutoff AND URGENT/HIGH AND beyond resolution threshold; worst 3 accounts by severe desc, breach desc, account asc; resolved-at-cutoff active-time median; risk by severe + first-response rates.