# Reflect 3 Attempt 02

> Atlas Commerce Operations — Analytical Task Skill

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

---

# Atlas Commerce Operations — Analytical Task Skill

## When to Use

Invoke this skill when asked to produce a data-analysis answer from the Atlas Commerce Operations workplace: scorecards, reconciliations, quality reviews, productivity reports, or health reviews that require SQL queries against the provided database and submission of a structured JSON answer matching a supplied template.

## Core Environment

A workplace service is available at the base URL provided in the task prompt. All requests require the header `Authorization: Bearer <token>` where `<token>` is supplied in the prompt or environment. The service exposes these endpoints:

- `GET /api/schema` — table DDL and indexes.
- `GET /api/data-dictionary` — column descriptions, conventions (timestamps are ISO‑8601 UTC, money uses smallest currency units, raw vs canonical fields).
- `POST /api/sql` — read‑only analytical queries. Body: `{"sql": "<SQL>", "params": []}`.
- `GET /api/correction-audit` — lists applied canonical corrections.
- `POST /api/sql/transaction` — controlled writes (see Correction Workflow below).

Always begin by fetching `/api/schema` and `/api/data-dictionary` so you know every table, column, index, and convention before writing any analytical query.

## Deduplication (Universal Rule)

Any table whose indexes include a deduplication index named `idx_<table>_dedupe` on `(source_system, external_event_id, ingested_at)` **must** be deduplicated before any other processing:

1. Group rows by `(source_system, external_event_id)`.
2. Keep the single row with the **maximum `ingested_at`**.
3. Discard all other copies — they are import-retry duplicates.

Tables requiring dedup include: `carrier_scans`, `case_events`, `refund_attempts`, `payment_events`, `inventory_movements`, `order_events`, `warehouse_task_events`.

The resulting set is the **effective** rows. All status checks, counts, and time calculations operate on the deduplicated set only.

## Effective Final State (Append‑Only Event Tables)

For tables that record an append-only event history (`carrier_scans`, `case_events`, `warehouse_task_events`), determine the effective current state of an entity as follows:

1. Deduplicate the event rows first.
2. For each entity (shipment, case, task), find the single row with the **maximum event timestamp** (`canonical_event_at` / `event_at`).
3. If multiple rows share the maximum timestamp, tie‑break by taking the **maximum row identifier** (`scan_row_id` / `case_event_id` / `task_event_id`).
4. The status on that row is the **effective final status**.

Do **not** use the denormalised `current_status` column on header tables (`shipments`, `support_cases`, `warehouse_tasks`) — the data dictionary notes it "may lag append‑only event history." Use the event tables with the dedup‑then‑latest pattern instead.

## Money and FX

- All monetary amounts are stored in **minor units** (cents, pence — divide by 100 for major units).
- Cross‑currency conversion uses `fx_rates.usd_per_unit` matched on `(rate_date = service_date, currency = row_currency)`.
- Convert: `amount_usd = (amount_minor / 100.0) * fx_rates.usd_per_unit`.
- When both refund and order are in the same non‑USD currency, both are multiplied by the same FX rate for comparison, so the relative comparison is preserved.
- Round final reported USD amounts to the decimal places specified in the answer template.

## Correction Workflow

When the task describes a data correction with an approved audit record:

1. Identify the exact contradiction from the data (e.g., raw status ≠ canonical status).
2. The `/api/sql/transaction` endpoint accepts only `INSERT` statements into the `correction_audit` table. `UPDATE` statements against business tables are rejected.
3. Insert one audit row containing all required fields: `audit_id`, `correction_key`, `entity_type`, `entity_id`, `source_row_id`, `field_name`, `old_value`, `new_value`, `reason_code`, `corrected_at`, `actor`.
4. The audit record **is** the correction — check `GET /api/correction-audit` afterwards to confirm it was recorded.
5. When computing post‑correction state, apply the audit record's `new_value` as an override for the specified `(source_row_id, field_name)`.
6. Set `correction_status` to `"APPLIED"` when exactly one audit row committed and a post‑change query confirms the corrected value through the audit table.

## Query Strategy

- The `/api/sql` endpoint returns at most 5000 rows. When a result set would exceed this, use **aggregation** (`COUNT`, `SUM`, `GROUP BY`) to push computation into the database.
- For data that must be fetched row‑by‑row, **batch** queries using `WHERE … IN ('id1','id2',…)` with 500–800 IDs per batch, then merge in the host language.
- Avoid deeply nested subqueries — the endpoint may reject them. Prefer simple `JOIN` … `GROUP BY` patterns or pull data in stages.
- SQLite's `GROUP BY` may return an arbitrary value for non‑aggregated columns when a strict mode is not enforced. Always use a deterministic method (row‑id tie‑break after fetching) rather than relying on `GROUP BY` alone for the status column.

## General Workflow

1. **Read the request payload** — it contains the exact business definitions, scope boundaries, rounding rules, and status‑classification rules. Every term in the payload has a precise meaning; do not substitute a generic interpretation.
2. **Read the answer template** — the output must conform exactly to its `required` fields, `type` constraints, `enum` values, `pattern` restrictions, and `additionalProperties: false`.
3. **Explore the data** with small, focused queries before attempting the full computation.
4. **Build incrementally** — verify intermediate counts (e.g., eligible population size, status distributions) before computing derived metrics.
5. **Match rounding and ordering** — round only final reported values to the specified decimal places; use unrounded values for intermediate sorting and comparisons unless the request says otherwise.
6. **Sort arrays** exactly as specified (ascending IDs, ranked metrics with tie‑breaks).