Atlas Commerce Operations
Authenticated operational analytics, reconciliation, and controlled data correction for the Atlas Commerce Operations database. The workplace runs a read-only SQL query service, a controlled SQL transaction endpoint, and an audit view. All business data lives in ISO-8601 UTC timestamps, monetary minor units, and append-only event tables with canonical as well as raw fields.
Connection
Every request to the Atlas API must use:
- Base URL: the value of
<TASK_ENV_BASE_URL>from the task prompt, defaulting tohttp://task-env:9022/ - Authorization header:
Authorization: Bearer atlas-ops-token-022
Allowed endpoints:
| Method | Path | Purpose |
|---|---|---|
| GET | /api/schema |
Table DDL, indexes, foreign keys |
| GET | /api/data-dictionary |
Column descriptions, conventions |
| POST | /api/sql |
Read-only analytical SQL queries |
| POST | /api/sql/transaction |
Controlled single-statement mutations |
| GET | /api/correction-audit |
Public audit records |
The SQL endpoint returns JSON with columns, rows, row_count, and truncated.
The transaction endpoint expects a JSON body with a single SQL statement and returns affected-row counts.
Discovery Workflow
When approaching an unfamiliar Atlas task, work in this order every time:
- Fetch the schema (
GET /api/schema) to learn table structures, foreign keys, and indexes. - Fetch the data dictionary (
GET /api/data-dictionary) to understand column meanings, conventions (timestamps, money, source vs canonical), and business semantics. - Explore with small queries — check distinct values for enums (
event_type,status,priority,segment,region), spot row counts, and verify assumptions before writing the big query. - Read the request payload thoroughly — the business definitions, cutoffs, rounding rules, SLAs, and classification policies embedded in the request JSON are authoritative over any general pattern in this skill.
Deduplication
Every event table (carrier_scans, order_events, payment_events, refund_attempts, warehouse_task_events, case_events, inventory_movements) can contain import-retry duplicates. The schema indexes confirm the deduplication key is (source_system, external_event_id) and the tiebreak is MAX(ingested_at).
For any event table, wrap the query in a deduplication subquery:
SELECT ... FROM (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY source_system, external_event_id
ORDER BY ingested_at DESC
) AS rn
FROM <table>
) WHERE rn = 1
Alternatively, use GROUP BY source_system, external_event_id with MAX(ingested_at) and join back to the table. The window-function approach is preferred because it preserves all columns cleanly.
Never count or aggregate rows from an undeduplicated event table — duplicates inflate counts and break correctness.
Production Filtering
Accounts: Exclude internal and test accounts with is_internal = 0 AND is_test = 0. The data dictionary confirms 0 means false for integer booleans.
Warehouse tasks: Filter to production work with work_class = 'PRODUCTION'.
Always apply these filters before applying any task-specific cohort scoping.
Money and Currency
- Monetary fields (
amount_minor,gross_amount_minor) store values in the smallest unit of the row'scurrencycolumn. - Convert to USD using the
fx_ratestable: join onrate_date(use the service date or event date) andcurrency, then multiplyamount_minor * usd_per_unit / 100.0(or the appropriate minor-unit divisor) to get USD. - For order gross comparison, use the order's own
currencyand the refund'sservice_daterate. - Report final USD amounts with 2 decimal places unless the request specifies otherwise.
Order Fulfillment Analytics
Orders are the central entity. Key relationships:
orders→accounts(viaaccount_id): account segment, tier, regionorders→warehouses(viawarehouse_id): facility region, timezone, cutofforders→campaigns(viacampaign_id): campaign active windoworders→shipments(viaorder_id): physical delivery trackingshipments→carrier_scans(viashipment_id): carrier scan eventsorders→order_events(viaorder_id): order lifecycle events
Effective shipment delivery status: Derive from the deduplicated carrier_scans table, not from shipments.current_status (which may lag). Group scans by shipment_id, take the latest by canonical_event_at, and use that row's canonical_status as the effective delivery state. A shipment is DELIVERED when its latest canonical_status is DELIVERED.
On-time delivery: A delivered shipment is on time when its latest scan's canonical_event_at <= the shipment's promised_delivery_at.
Severe exceptions: Identify orders that are incomplete with cutoff past the latest promise by >24h, or completed with any shipment delivered >24h past its promise.
Refund Reconciliation
refund_attemptscontains provider refund attempts linked to orders. Status values:SETTLED,FAILED,VOIDED,REVERSED.- A logical refund is a distinct
refund_id. An effective settled logical refund is one where at least one row for thatrefund_idhasstatus = 'SETTLED'. - Linked reversals: A refund row where
status = 'REVERSED'andlinked_refund_idpoints to another settledrefund_id. The reversal offsets the original refund's value. - Net refund amount: Sum settled refund amounts minus reversal amounts, all converted to USD.
- Leakage detection: For each order, compare effective net refund USD against the order's gross amount in USD (converted at the refund service date rate). Also check for multiple unreversed settled refunds with the same
reason_codeon the same order. - Reason ranking: Group by
reason_code, sum net refund USD per code, rank descending by USD then ascending by code.
Carrier Quality Corrections
When the task involves finding and fixing a raw/canonical contradiction in carrier_scans:
- Query scans where
raw_status != canonical_statuswithin the scoped batch and warehouse cohort. - Identify the single contradiction the request targets (the request payload specifies exactly one exists).
- Verify the correction target: raw status is correct, canonical should match it.
- Backlog analysis: A shipment is in backlog when its effective final carrier status (latest canonical status by
canonical_event_at) is notDELIVERED. Count the pre-correction backlog, then simulate (or apply and count) the post-correction backlog.
Applying a correction via POST /api/sql/transaction:
UPDATE carrier_scans
SET canonical_status = '<new_value>',
corrected_at = '<timestamp>',
correction_reason = '<reason>'
WHERE scan_row_id = '<row_id>'
The transaction endpoint returns affected_business_rows. Then verify with a read query.
Audit records: After a correction, query GET /api/correction-audit for the matching correction_key. The audit is appended automatically by the transaction.
Correction status: Report APPLIED only when exactly 1 business row was mutated, exactly 1 audit row exists, and a post-correction read confirms the new canonical value.
Warehouse Productivity
warehouse_tasksdefines planned work (task type, priority, work class, assigned employee, planned units, created/due dates).warehouse_task_eventsrecords execution events for those tasks.- Task scoping: filter by
created_atin the request window,warehouse_id, andwork_class = 'PRODUCTION'.
Completion: A task is completed when it has at least one COMPLETED event (after dedup). Use SUM(units) from the latest completion event per task for completed units.
Rework: A task with a REWORK event is a rework task.
Productive minutes: From the task events, sum productive_minutes for completed tasks.
Units per hour: For each employee, SUM(units) / SUM(productive_minutes) * 60 from completed production task events.
Delayed high-priority tasks: Tasks with priority IN ('HIGH', 'URGENT') where due_at < cutoff and the task is not completed.
Team performance: Group by team_id (from employees table), compute completion rate per team, rank ascending.
Support Health
support_casesholds case headers with priority, open time, current status.case_eventsholds the append-only lifecycle events for each case.
Active time computation (clock basis: SUPPORT_ACTIVE_TIME): Active time is elapsed time minus time spent in WAITING_CUSTOMER state. Track the timeline: OPENED starts the clock, WAITING_CUSTOMER pauses it, CUSTOMER_REPLIED resumes it, RESOLVED stops it. For each WAITING_CUSTOMER interval, subtract its duration from the total elapsed time.
First response: Active time from OPENED to the first AGENT_RESPONDED event. If no agent response exists by the cutoff, use active elapsed time to cutoff.
Resolution time: Active time from OPENED to RESOLVED. If unresolved at cutoff, use active elapsed time to cutoff for breach detection.
Breach detection: Compare first-response active time and resolution active time against the priority's SLA thresholds from the request.
Severe active cases: Cases open or reopened at cutoff, priority URGENT or HIGH, and beyond the resolution active-time SLA threshold.
Median: For an even count, average the two central values. Round to 2 decimal places as specified.
Status at cutoff: Derive effective status from the latest event per case by event_at, not from support_cases.current_status (which may lag). OPEN, REOPENED → active/open; RESOLVED → resolved.
Output Formatting
Every Atlas task requires the answer as one JSON object conforming to a template:
- Read the
input/payloads/answer_template.jsonto understand required fields, types, ranges, and ordering constraints. - Compute all values according to the business definitions in the request payload.
- Round floating-point values only at the final reported output, using the decimal places specified in the template or request (typically 2 or 4).
- Sort arrays exactly as the template or request specifies (typically ascending by natural sort order for IDs, descending for ranked metrics then ascending for tiebreaks).
- Write the JSON to
answer.jsonwith no commentary or additional fields outside the JSON document. - Use
APPLIED/NOT_APPLIEDfor correction status, facility status enums, and risk classification enums exactly as defined in the request payloads.
Reference Files
For the full schema DDL, indexes, and column-level data dictionary, read these whenever a task needs precise column types or relationship details:
- schema.md — complete table DDL and index definitions
- data-dictionary.md — column descriptions, conventions, and domain semantics
Load the appropriate reference when the task involves a table you have not yet queried or when you need to understand specific column semantics like dedup keys, nullable behavior, or the meaning of a status/enum value.