Atlas Operations Queries
Quick Reference
- Base URL: read from the task prompt (usually
<TASK_ENV_BASE_URL>)
- Auth header:
Authorization: Bearer atlas-ops-token-022
- Allowed endpoints:
GET /api/schema, GET /api/data-dictionary, POST /api/sql, POST /api/sql/transaction, GET /api/correction-audit
Workflow
1. Gather Context
Every task provides a prompt and one or more payload files. Read them all:
input/prompt.txt — narrative instructions, boundaries, and output contract
input/payloads/<request>.json — scope, business definitions, metric formulas, status rules
input/payloads/answer_template.json — exact JSON schema for the output
The request JSON defines: cohort eligibility, cutoff timestamps, business definitions
(e.g. what "complete" means), rollup rules, ranking/tiebreak rules, status classification
rules, and rounding policies. Every SQL query must faithfully implement these definitions.
2. Load Schema and Data Dictionary
curl -s -H "Authorization: Bearer atlas-ops-token-022" <BASE>/api/schema
curl -s -H "Authorization: Bearer atlas-ops-token-022" <BASE>/api/data-dictionary
The schema returns DDL + indexes. The data dictionary returns column descriptions.
Cross-reference the full schema in references/schema.md.
3. Write and Run SQL Queries
Submit queries to POST /api/sql with body {"sql": "<query>"}. Build queries
incrementally — start with cohort scoping, then add metric computations.
Consult references/query_patterns.md for deduplication,
effective-state derivation, FX conversion, ranking, median computation, and other
common patterns. Every query touching an imported table must apply deduplication.
4. Controlled Corrections (when the task requires a data change)
Use POST /api/sql/transaction for atomic write + audit:
curl -s -X POST -H "Authorization: Bearer atlas-ops-token-022" \
-H "Content-Type: application/json" \
-d '{"statements": ["<update>", "<insert audit>"]}' \
<BASE>/api/sql/transaction
The transaction commits both statements atomically or rolls back entirely. After
committing, verify with a read-only query and confirm the audit record via
GET /api/correction-audit?correction_key=<key>.
Correction rules:
- Change only canonical fields (
canonical_status, canonical_event_at, canonical_quantity_each), never raw fields.
- Set
corrected_at and correction_reason on the updated row.
- Insert exactly one row into
correction_audit with the caller-provided audit_id and correction_key.
- Use the exact
reason_code, corrected_at, and actor values from the request.
5. Compute Results and Write Answer
Apply rounding only at the final step, not during intermediate computations.
Follow ranking/tiebreak rules exactly as stated in the request JSON.
Classify status by evaluating rules in the order given (first match wins if
exclusive; otherwise evaluate all).
Write the result as a single JSON object to answer.json that conforms exactly
to the answer template schema. Include no commentary outside the JSON.
Key Gotchas
- Deduplication is mandatory on every imported table:
carrier_scans, case_events,
inventory_movements, order_events, payment_events, refund_attempts,
warehouse_task_events. Use ROW_NUMBER() OVER (PARTITION BY source_system, external_event_id ORDER BY ingested_at DESC) and keep rn = 1.
current_status columns are stale snapshots. Derive final state from the
effective event table rows instead when correctness at a cutoff matters.
- Carrier scan deduplication and effective row: For a given shipment, the
effective scan is the deduplicated row with the latest
canonical_event_at
(tiebreak on scan_row_id).
- Inclusive timestamps: All window boundaries in task requests are inclusive.
Use
>= and <=, not > or <.
- Monetary math:
amount_minor / gross_amount_minor are in the smallest
currency unit (cents for USD). Divide by 100 for display dollars. FX rates are
usd_per_unit — multiply by the rate, not divide.
- Rounding: Round only final reported values. Carry full precision through
intermediate computations. Use
ROUND(value, 4) for rates and ROUND(value, 2)
for dollar amounts.
- Integer booleans:
is_internal, is_test, is_active are INTEGER with
0 = false, 1 = true. Write = 0 not = FALSE.
- NULL handling:
campaign_id on orders is nullable. linked_refund_id and
linked_event_id are nullable. shipped_at on shipments is nullable. Always
handle NULLs explicitly.
- SQLite dialect: The backend is SQLite. Use
julianday() for time math,
|| for string concatenation, and window functions (ROW_NUMBER, COUNT(*) OVER)
for ranking and aggregation.
- Correction endpoint: Only
POST /api/sql/transaction accepts writes.
POST /api/sql is read-only.
1---2name: atlas-ops-queries3description: Query and correct the Atlas Commerce Operations database via its authenticated HTTP API. Use when the task involves the Atlas workplace service (typically at a TASK_ENV_BASE_URL), needs SQL analysis against the Atlas schema (orders, shipments, carrier_scans, refund_attempts, warehouse_tasks, support_cases, accounts, etc.), requires a controlled canonical-data correction with an audit record, or calls for a JSON output conforming to a supplied answer template. Do not use for general SQL tasks outside the Atlas domain.4metadata:5 source: https://skillmd.com/skills/prism-shadow/atlas-ops-queries6---78# Atlas Operations Queries910## Quick Reference1112- **Base URL**: read from the task prompt (usually `<TASK_ENV_BASE_URL>`)13- **Auth header**: `Authorization: Bearer atlas-ops-token-022`14- **Allowed endpoints**: `GET /api/schema`, `GET /api/data-dictionary`, `POST /api/sql`, `POST /api/sql/transaction`, `GET /api/correction-audit`1516## Workflow1718### 1. Gather Context1920Every task provides a prompt and one or more payload files. Read them all:21- `input/prompt.txt` — narrative instructions, boundaries, and output contract22- `input/payloads/<request>.json` — scope, business definitions, metric formulas, status rules23- `input/payloads/answer_template.json` — exact JSON schema for the output2425The request JSON defines: cohort eligibility, cutoff timestamps, business definitions26(e.g. what "complete" means), rollup rules, ranking/tiebreak rules, status classification27rules, and rounding policies. Every SQL query must faithfully implement these definitions.2829### 2. Load Schema and Data Dictionary3031```bash32curl -s -H "Authorization: Bearer atlas-ops-token-022" <BASE>/api/schema33curl -s -H "Authorization: Bearer atlas-ops-token-022" <BASE>/api/data-dictionary34```3536The schema returns DDL + indexes. The data dictionary returns column descriptions.37Cross-reference the full schema in [references/schema.md](references/schema.md).3839### 3. Write and Run SQL Queries4041Submit queries to `POST /api/sql` with body `{"sql": "<query>"}`. Build queries42incrementally — start with cohort scoping, then add metric computations.4344Consult [references/query_patterns.md](references/query_patterns.md) for deduplication,45effective-state derivation, FX conversion, ranking, median computation, and other46common patterns. Every query touching an imported table must apply deduplication.4748### 4. Controlled Corrections (when the task requires a data change)4950Use `POST /api/sql/transaction` for atomic write + audit:5152```bash53curl -s -X POST -H "Authorization: Bearer atlas-ops-token-022" \54 -H "Content-Type: application/json" \55 -d '{"statements": ["<update>", "<insert audit>"]}' \56 <BASE>/api/sql/transaction57```5859The transaction commits both statements atomically or rolls back entirely. After60committing, verify with a read-only query and confirm the audit record via61`GET /api/correction-audit?correction_key=<key>`.6263Correction rules:64- Change only canonical fields (`canonical_status`, `canonical_event_at`, `canonical_quantity_each`), never raw fields.65- Set `corrected_at` and `correction_reason` on the updated row.66- Insert exactly one row into `correction_audit` with the caller-provided `audit_id` and `correction_key`.67- Use the exact `reason_code`, `corrected_at`, and `actor` values from the request.6869### 5. Compute Results and Write Answer7071Apply rounding only at the final step, not during intermediate computations.72Follow ranking/tiebreak rules exactly as stated in the request JSON.73Classify status by evaluating rules in the order given (first match wins if74exclusive; otherwise evaluate all).7576Write the result as a single JSON object to `answer.json` that conforms exactly77to the answer template schema. Include no commentary outside the JSON.7879## Key Gotchas8081- **Deduplication is mandatory** on every imported table: `carrier_scans`, `case_events`,82 `inventory_movements`, `order_events`, `payment_events`, `refund_attempts`,83 `warehouse_task_events`. Use `ROW_NUMBER() OVER (PARTITION BY source_system,84 external_event_id ORDER BY ingested_at DESC)` and keep `rn = 1`.85- **`current_status` columns are stale snapshots.** Derive final state from the86 effective event table rows instead when correctness at a cutoff matters.87- **Carrier scan deduplication and effective row**: For a given shipment, the88 effective scan is the deduplicated row with the latest `canonical_event_at`89 (tiebreak on `scan_row_id`).90- **Inclusive timestamps**: All window boundaries in task requests are inclusive.91 Use `>=` and `<=`, not `>` or `<`.92- **Monetary math**: `amount_minor` / `gross_amount_minor` are in the smallest93 currency unit (cents for USD). Divide by 100 for display dollars. FX rates are94 `usd_per_unit` — multiply by the rate, not divide.95- **Rounding**: Round only final reported values. Carry full precision through96 intermediate computations. Use `ROUND(value, 4)` for rates and `ROUND(value, 2)`97 for dollar amounts.98- **Integer booleans**: `is_internal`, `is_test`, `is_active` are `INTEGER` with99 `0` = false, `1` = true. Write `= 0` not `= FALSE`.100- **NULL handling**: `campaign_id` on orders is nullable. `linked_refund_id` and101 `linked_event_id` are nullable. `shipped_at` on shipments is nullable. Always102 handle NULLs explicitly.103- **SQLite dialect**: The backend is SQLite. Use `julianday()` for time math,104 `||` for string concatenation, and window functions (`ROW_NUMBER`, `COUNT(*) OVER`)105 for ranking and aggregation.106- **Correction endpoint**: Only `POST /api/sql/transaction` accepts writes.107 `POST /api/sql` is read-only.