Atlas Commerce Operations — Workplace Data Analysis and Correction
Use this skill to query, analyze, or correct business records through the Atlas Commerce Operations workplace API. Follow every step — the API is opinionated and small mistakes in conventions produce wrong answers.
1. Environment Setup
The prompt supplies a base URL placeholder (usually <TASK_ENV_BASE_URL>).
Read the task's input/prompt.txt to get the actual base URL; it is
available from the runtime environment.
Every API call must carry this header:
Authorization: Bearer atlas-ops-token-022
Available endpoints:
| Method | Path | Purpose |
|---|---|---|
| GET | /api/schema | Full DDL for every table plus indexes |
| GET | /api/data-dictionary | Column descriptions, conventions, semantics |
| POST | /api/sql | Read-only analytical SQL |
| POST | /api/sql/transaction | Controlled write (INSERT/UPDATE only) |
| GET | /api/correction-audit | Public audit records of past corrections |
Call /api/schema and /api/data-dictionary first in every session. The
schema is stable but the dictionary carries convention notes (timestamp
formats, boolean encoding, money semantics) that change how queries must
be written.
2. Writing SQL Queries (POST /api/sql)
Send a JSON body:
{"sql": "SELECT ..."}
Rules:
UTC timestamps. Every stored timestamp ends in
Zand is ISO-8601. Calendar dates useYYYY-MM-DD. When the task specifies a boundary like"start": "2026-03-01", "end": "2026-04-30", "inclusive": true, useservice_date BETWEEN '2026-03-01' AND '2026-04-30'. When the boundary uses ISO-8601 timestamps use the same literal with the appropriate comparator.Integer booleans. Columns named
is_*use 0 for false and 1 for true. Compare with= 0or= 1, never withIS TRUE.Monetary minor units. Columns named
*_minorhold values in the smallest currency unit (cents for USD).gross_amount_minoronordersis in the order's currency.amount_minoronrefund_attemptsandpayment_eventsis in the row's currency. To convert to major units divide by 100. When the task requests USD, multiply by an FX rate from thefx_ratestable (see section 4).NULL handling.
corrected_atandcorrection_reasonin source-row tables (carrier_scans,inventory_movements) are NULL when the row has never been corrected.linked_refund_idonrefund_attemptsis NULL when there is no linked reversal.active_toonemployeesis NULL when the assignment is still active. Check the schema for each column's nullability.
3. Source-Data Deduplication
Every source-ingestion table has a deduplication index on (source_system, external_event_id, ingested_at). The same external event can arrive more
than once. Always deduplicate before analysis by picking the row with
the lowest ingested_at for each (source_system, external_event_id).
The affected tables and their dedup key:
| Table | Dedup Key |
|---|---|
| carrier_scans | source_system, external_event_id, ingested_at |
| case_events | source_system, external_event_id, ingested_at |
| inventory_movements | source_system, external_event_id, ingested_at |
| order_events | source_system, external_event_id, ingested_at |
| payment_events | source_system, external_event_id, ingested_at |
| refund_attempts | source_system, external_event_id, ingested_at |
| warehouse_task_events | source_system, external_event_id, ingested_at |
Use a subquery or CTE:
WITH dedup AS (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY source_system, external_event_id
ORDER BY ingested_at ASC
) AS rn
FROM <table>
)
SELECT ... FROM dedup WHERE rn = 1
After dedup, treat the surviving row as the single source of truth for
that external event. Reference tables (accounts, campaigns, orders,
products, shipments, support_cases, warehouse_tasks, warehouses,
employees) do not need dedup.
4. Effective Event Selection
Several tables have an "effective" index designed for picking the most recent event per entity:
| Effective Index | Key |
|---|---|
| carrier_scans | shipment_id, canonical_event_at, scan_row_id |
| case_events | case_id, event_at, case_event_id |
| order_events | order_id, event_at, event_id |
| warehouse_task_events | task_id, event_at, task_event_id |
When a task asks for the "effective final" state of an entity, use the row
with the latest canonical_event_at (carrier_scans) or event_at (other
tables), breaking ties with the row-id. Example for carrier scans:
WITH dedup AS (...),
effective AS (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY shipment_id
ORDER BY canonical_event_at DESC, scan_row_id DESC
) AS rn
FROM dedup
)
SELECT ... FROM effective WHERE rn = 1
The current_status column on orders, shipments, support_cases, and
warehouse_tasks is a convenience snapshot and may lag append-only event
history. When the task definition requires event-level precision, always
derive state from the effective event instead.
5. Monetary Conversion to USD
When a task calls for USD values, join fx_rates on the row's currency
and the appropriate date. The fx_rates table maps (rate_date, currency)
to usd_per_unit.
- For
refund_attempts, useservice_date. - For
payment_events, useDATE(event_at). - For
orders, use the date from the task's policy (typically the refund or payment service date relevant to the comparison).
Convert minor-unit amounts to major USD:
ROUND((r.amount_minor / 100.0) * fx.usd_per_unit, 2) AS amount_usd
The division by 100.0 converts minor units to major currency units. The
usd_per_unit rate is multiplied. Always round only the final displayed
USD value to the precision requested in the task's money_policy (usually 2
decimal places). Keep intermediate sums unrounded until the final step.
6. Controlled Corrections (POST /api/sql/transaction)
Only one of the five task families uses corrections, but the pattern is consistent. The endpoint accepts a JSON body with a single write statement:
{"sql": "UPDATE carrier_scans SET canonical_status = '<new>', corrected_at = '<ts>', correction_reason = '<code>' WHERE scan_row_id = '<id>'"}
The only allowed operations are INSERT and UPDATE. The response reports
affected_business_rows and audit_rows. The system automatically writes
a matching row to correction_audit.
The request payload (e.g., carrier_quality_request.json) provides:
- The field to change and the new value.
reason_code,actor,audit_id,correction_key,corrected_at.- A correction_status_rule defining when to report APPLIED vs NOT_APPLIED.
Workflow:
- Query pre-correction state to identify the target row and verify the expected old value.
- Submit the UPDATE via
POST /api/sql/transaction. Read the response to getaffected_business_rowsandaudit_rows. - Re-query the target row to confirm the new value is persisted.
- Fetch the audit record from
GET /api/correction-auditand confirm it matches. - Re-compute the business metric affected by the correction (e.g., backlog count).
- Report
APPLIEDonly when every condition in the request's correction_status_rule is satisfied; otherwise reportNOT_APPLIED.
Important: raw values must not be changed. Only canonical fields may
be corrected. The raw_status, raw_event_at, and identity fields like
shipment_id and scan_row_id stay untouched.
7. Answer Template Conformance
Every task includes an input/payloads/answer_template.json. The final
output must match that schema exactly:
- All
requiredfields must be present. additionalProperties: falsemeans no extra fields are allowed.- Enum fields must use exactly the listed values (case-sensitive).
- Numeric fields with
multipleOforminimum/maximummust satisfy those constraints. - Arrays with
uniqueItems: truemust not contain duplicates. - Array ordering specified in the request definition must be followed exactly.
- Output only pure JSON — no surrounding text, explanation, or markdown
fences unless the template itself is a JSON Schema and the prompt asks
for a file named
answer.json.
Read the answer template before running queries. Some templates
describe ordering rules (e.g., "ascending by order_id") that affect how
query results are sorted. Some embed pattern constraints that clarify
ID formats. Some have description notes that clarify business meaning.
8. Common Task Workflow
- Read
input/prompt.txtand every payload file underinput/payloads/. - Read
input/payloads/answer_template.json— note every required field and constraint. - Call
GET /api/schemaandGET /api/data-dictionaryto confirm table shapes and conventions. - Write dedup CTEs for every source-ingestion table you query.
- Write effective-event CTEs when the task asks for "final" or "effective" state.
- Filter to the exact cohort described in the request payload (account tier, region, campaign, time window, warehouse, batch, etc.).
- Apply the business definitions from the request payload literally — composite conditions, threshold rules, ranking rules.
- Compute all required output fields, respecting rounding and ordering rules.
- Write the result as a single JSON object to
answer.jsonthat conforms exactly to the answer template. No commentary outside the JSON.
9. Reference
For the full schema at-a-glance without calling the API, see references/schema-summary.md. Always prefer the live API responses for column-level details; use the reference only as a quick-lookup supplement.