# Atlas Commerce Ops Scorecard

> Solve Atlas Commerce Operations analytical scorecard, reconciliation, and data-correction requests against the authenticated workplace SQL service. Use when a task gives a business request JSON + an answer-template JSON and asks you to compute fulfillment, refund, carrier-quality, warehouse-productivity, or support-health metrics from the Atlas Operations database. Covers cohort scoping, snapshot-vs-event-history authority, raw/canonical fields, minor-unit money + FX, the controlled correction transaction, and the support active-time clock.

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

---


# Atlas Commerce Operations Scorecard & Reconciliation

## When to use

A request provides a **business request object** (scope, definitions, rules) and an **answer template JSON** (a schema with required fields, units, rounding, and ordering). The job is to compute the requested metrics from the Atlas Commerce Operations database and write one JSON object that conforms to the template *exactly* (no extra fields, no commentary). Requests are analytical (read-only) unless an approved data correction is explicitly requested.

## Environment

- Authenticated HTTP service (base URL and bearer token are given per environment in the access doc).
- `GET /api/schema` — table DDL.
- `GET /api/data-dictionary` — column descriptions and conventions.
- `GET /api/correction-audit` — public audit rows for applied corrections.
- `POST /api/sql` — read-only `SELECT`/`WITH` queries. Body `{"sql": "...", "params": [...]}`.
- `POST /api/sql/transaction` — controlled multi-statement transaction (only when a correction is requested). Body `{"statements": [{"sql","params"}], "expected_total_changes": N}`.

Always send `Authorization: Bearer <token>` and `Content-Type: application/json`.

## Hard-won environment constraints (respect these)

1. **Results are capped at 5000 rows silently.** Do all aggregation/ordering server-side. Never page large row sets into the client and merge — you will silently miss rows. If you need a per-entity list that could exceed 5000, compute it as a single server-side `SELECT ... ORDER BY ...` and confirm the returned row count matches a separate `COUNT(*)`.
2. **Complex queries are rejected ("query rejected")** past a threshold — roughly when a heavy `GROUP BY` CTE over a large event/scan table is combined with several more CTEs and multi-table joins. Mitigations that work:
   - Merge sibling CTEs (e.g. one `delivered` CTE with conditional `MIN`/`MAX` instead of separate `del` + `delany`).
   - Replace a final eligibility CTE+`JOIN` with an `IN (SELECT ...)` membership subquery, or a scalar subquery in the `SELECT`.
   - Compute intermediates server-side in one query, then feed IDs into a second query.
   - Prefer `NOT EXISTS` correlated subqueries over window functions when finding "latest row per group" inside a constrained query.
3. **Dialect is SQLite-like:** `datetime(col, '+24 hours')` for time math; `?` params; `GROUP_CONCAT`; window functions (`ROW_NUMBER() OVER`) work but add cost.
4. **Deliberate duplicate rows exist** in event/scan tables (a parallel high-numbered row echoing a low-numbered one, identical timestamps/values). Always dedup by the natural key (e.g. `(event_at, event_type)` for case events, `(canonical_event_at, canonical_status)` for scans, or by business id) before counting/summing. Verify duplicates are truly identical (`COUNT(DISTINCT amount)=1`) before assuming `MAX`/`MIN` collapse them.

## Core analytical principles

### A. Read the contract before querying
Open the answer template first. It pins: required field names, types, units, `multipleOf`/decimal precision, array `minItems`/`maxItems`, ordering rules, and enum values. Then open the request for the business definitions, scope windows, cutoffs, thresholds, and status/risk rules. The template is the source of truth for *shape*; the request is the source of truth for *semantics*.

### B. "Production" = strict
`accounts` has two production-exclusion flags: `is_internal` and `is_test`. **Production accounts = `is_internal=0 AND is_test=0`.** Do not relax to `is_test=0` only — internal accounts are non-production and excluding them is required. When a cohort says "production orders/accounts/shipments/cases," join to `accounts` and apply both flags.

### C. Snapshot columns lag; event/scan history is authoritative
The dictionary warns that `current_status` on `orders`, `shipments`, `support_cases`, `warehouse_tasks` "may lag append-only event history." Derive effective state/time from the append-only event or scan table, not the snapshot:
- **Shipment delivery** → `carrier_scans` with `canonical_status='DELIVERED'` (the `canonical_*` columns are the normalized "effective" values; `raw_*` are source-as-supplied). Effective delivery time = the `canonical_event_at` of the DELIVERED scan.
- **Order lifecycle** → `order_events` (`CREATED/PAYMENT_CONFIRMED/ALLOCATED/PACKED/SHIPPED/DELIVERED/CANCELLED`).
- **Warehouse task completion/rework** → `warehouse_task_events` (`COMPLETED`/`REWORK` rows carry the real `units` and `productive_minutes`).
- **Support case state & clocks** → `case_events` (see Active-time clock below).

When a definition says "effectively" or "by the cutoff," derive from events/scans with a time bound (`event_at <= cutoff` / `canonical_event_at <= cutoff`), and dedup.

### D. Cohort scoping is the highest-leverage, highest-risk step
Most failures come from the cohort, not the math. Nail every scope clause: campaign attribution **and** creation-window, account tier/segment/region **and** production flags, service-date / opened / created windows (inclusive boundaries unless stated), warehouse id, and named import batch. Check whether a request-level `warehouse_id` further restricts a batch-defined cohort (it does — apply it). Re-derive the window start/end from the reference table (e.g. `campaigns.starts_at/ends_at`) rather than guessing, then confirm the cohort size with a `COUNT(*)` before computing metrics.

### E. Money: minor units + daily FX
Monetary `*_minor` fields are the smallest unit of the row's currency (e.g. cents). `fx_rates.usd_per_unit` is USD per one *unit* (one unit = 100 minor) of the named currency, keyed by `(rate_date, currency)`, and it exists for USD too (it is not exactly 1.0). To convert an amount to USD: `(amount_minor / 100.0) * usd_per_unit`, joining on the row's `service_date`/event date and currency. Follow the request's money policy literally for which date's rate to use (often the refund/reversal `service_date`), even for USD rows. Round only final reported amounts (`ROUND(..., 2)`).

### F. Status / risk rules are ordered ladders
Status enums (HEALTHY/WATCH/CRITICAL, STABLE/PRESSURED/AT_RISK, LOW/MODERATE/HIGH, CONTROLLED/ELEVATED/SEVERE, APPLIED/NOT_APPLIED) come from ordered threshold ladders. Evaluate top-down per the request's explicit conditions; "otherwise"/"all other outcomes" is the fallback. Compute the inputs (rates, counts) with the exact denominators the request names (often "eligible" count, which includes incomplete/active cases in the denominator — do not drop them).

### G. Ordering and tie-breaks
For ordered lists (worst regions, top employees, worst accounts, id lists), sort by the *unrounded* underlying value first, then apply the stated tie-break (usually id ascending), and only round for display. Read ordering rules verbatim — e.g. "rate ascending, then region ascending" or "severe count desc, breach count desc, account id asc."

## The controlled correction (only when explicitly requested)

A correction request names one raw/canonical contradiction and an approved minimal canonical fix. Workflow:

1. **Find the contradiction.** Within the named `import_batch_id` (and warehouse scope), `GROUP BY raw_status, canonical_status` and find the single pairing where they diverge (e.g. `raw_status='DELIVERED'` but `canonical_status='IN_TRANSIT'`). Exactly one such contradiction exists; all other rows map cleanly. Capture `scan_row_id`, `shipment_id`, the `field_name` (`canonical_status`), `old_value`, `new_value`.
2. **Apply via one transaction** with `expected_total_changes = 2` and two statements:
   - Guarded UPDATE on `carrier_scans`:
     `UPDATE carrier_scans SET canonical_status=? WHERE scan_row_id=? AND canonical_status=?`
     with params `[new_value, scan_row_id, old_value]`. The guard **requires** the primary-key `scan_row_id` *and* the old `canonical_status` in the `WHERE`, and the `SET` may contain **only** `canonical_status` (do not set `corrected_at`/`correction_reason` — including them is rejected; those columns stay NULL).
   - Audit INSERT with all 11 columns:
     `INSERT INTO correction_audit (audit_id, correction_key, entity_type, entity_id, source_row_id, field_name, old_value, new_value, reason_code, corrected_at, actor) VALUES (?,?,?,?,?,?,?,?,?,?,?)`.
     `correction_key` is UNIQUE (a retry with the same key is rejected). Use `entity_type='carrier_scan'`, `entity_id` = the shipment business id, `source_row_id` = the scan row id.
3. **Verify** with a post-change `SELECT` confirming the corrected `canonical_status`, and confirm exactly one business row changed and one audit row exists.
4. `correction_status = APPLIED` only when exactly one business row + one audit row committed and the post-change value is confirmed; otherwise `NOT_APPLIED` with the observed results.

### Backlog / post-correction metrics
Define the cohort (warehouse-scoped + production + has an effective scan in the named batch at/before the cutoff). **Effective final carrier status** = `canonical_status` of the shipment's *latest* scan by `canonical_event_at <= cutoff` (tie-break `scan_row_id` desc), across **all** the shipment's scans (not only the named batch). Backlog = final status `<> 'DELIVERED'`. Report pre- and post-correction counts and the delta (post − pre); the corrected scan must be the shipment's latest scan ≤ cutoff for the correction to change the backlog.

## The support active-time clock

For support-health requests, `SUPPORT_ACTIVE_TIME` is wall-clock time the case is actively being worked, **excluding** waiting-on-customer intervals. Reconstruct each case's dedup'd, time-sorted `case_events`:

- Clock starts at `OPENED`. **Paused intervals** = each span from a `WAITING_CUSTOMER` event to the next `CUSTOMER_REPLIED` event. `active_time(start, end) = (end - start) - sum(paused intervals overlapping [start,end])`.
- **First-response active time** = `active_time(OPENED, first AGENT_RESPONDED <= cutoff)`; if no agent response by cutoff, use `active_time(OPENED, cutoff)`. Breach if it exceeds the priority first-response SLA from the request (a representative shape: URGENT 2 / HIGH 4 / MEDIUM 8 / LOW 24 hours — always read the actual values from the request).
- **Resolution active time** = `active_time(OPENED, final RESOLVED <= cutoff)`; for active (unresolved) cases use `active_time(OPENED, cutoff)`. Breach if it exceeds the resolution SLA from the request (representative shape: URGENT 16 / HIGH 24 / MEDIUM 48 / LOW 96 hours — read actual values from the request).
- **State at cutoff** is derived from events, not `current_status` (which lags). Resolved by cutoff = a `RESOLVED` event <= cutoff exists with no later reopen. `open_at_cutoff` = not resolved by cutoff (includes open + reopened). `reopened_at_cutoff` = open cases with a `REOPENED` event after their last `RESOLVED` (or no RESOLVED).
- **severe_active_case** = open/reopened at cutoff AND priority in the request's high-priority set (typically URGENT/HIGH) AND resolution active time beyond the threshold (uses elapsed for active cases). Return ids sorted ascending.
- **median_active_resolution_hours** = median of resolution active-time over cases *resolved* at cutoff; for an even count average the two central values; round to 2 decimals.
- **worst_accounts** = top N (usually 3) by severe-active-case-count desc, active-clock-breach-count desc, account_id asc.

## Workflow checklist per task

1. Read template → request → schema/data-dictionary. List every required field with its unit/precision/ordering.
2. Pin the cohort: write a `COUNT(*)` for the eligible set and sanity-check it against scope clauses. Apply production flags, windows, region/segment/tier/warehouse/batch filters.
3. Decide authoritative source per metric (events/scans, not snapshots). Dedup deliberate duplicate rows.
4. Compute counts/rates/amounts server-side; keep intermediates under the complexity threshold (merge CTEs, use `IN`/`NOT EXISTS`/scalar subqueries).
5. Apply status/risk ladders with the exact denominators and ordered conditions.
6. Build the JSON object to match the template *exactly* (field names, types, rounding, array sizes, ordering, enums). No extra keys, no commentary.
7. Self-check identities: e.g. `complete + incomplete = eligible`; `pre - delta = post`; rates in `[0,1]`.

## Reference files

- `references/schema.md` — concise table/column map for the Atlas schema.
- `references/conventions.md` — value enums, money/FX, and the duplicate-row/active-time conventions.