> ## Documentation Index
> Fetch the complete documentation index at: https://docs.jaantonio.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Reporting views

> Reference for every view in the Analytics reporting schema.

All client access goes through schema `reporting`. The login is already limited to your organization.

You can also browse this contract in Lambda under **Analytics → Reporting**.

## Pick a view (30 seconds)

| You need…                       | Query                                                 |
| ------------------------------- | ----------------------------------------------------- |
| Current shipments               | `reporting.shipments`                                 |
| Current cotizaciones            | `reporting.quotes`                                    |
| History of a shipment           | `shipment_versions` / `shipment_changes`              |
| History of a quote              | `quote_versions` / `quote_changes`                    |
| A field that is not curated yet | `cargowise_xml_fields` → `cargowise_xml_observations` |

Shipments and quotes are **different tables**. Same column names (`transport_mode`, `total_weight`, …) are fine — do not mix them in one query unless you mean to.

Converted quote → shipment: join `quotes.quote_number = shipments.job_number`. Never join `quote_key` to `shipment_key` for that.

## All views

| View                         | Grain                                  | Everyday use                       |
| ---------------------------- | -------------------------------------- | ---------------------------------- |
| `shipments`                  | One current row per ForwardingShipment | Main curated shipment table        |
| `shipment_versions`          | One row per job version                | History snapshots                  |
| `shipment_changes`           | One row per accepted version change    | When current version moved         |
| `quotes`                     | One current row per OneOffQuote        | Main curated cotización table      |
| `quote_versions`             | One row per quote version              | History snapshots                  |
| `quote_changes`              | One row per accepted version change    | When current quote version moved   |
| `cargowise_payloads`         | One row per deduplicated payload       | Payload metadata                   |
| `cargowise_deliveries`       | One row per delivery event             | When delivery arrived              |
| `cargowise_messages`         | One row per classified message         | Message type + DataSource identity |
| `cargowise_xml_fields`       | One row per observed semantic field    | Discover field names / paths       |
| `cargowise_xml_observations` | One row per observed field value       | Query raw field values             |

Consols and bookings are **not** curated views yet. They may still appear in discovery / messages.

## `reporting.shipments` — CW nodes → columns

Source: CargoWise `UniversalShipment` with `DataSource/Type = ForwardingShipment`.

| Column                                          | CargoWise node(s)                                    |
| ----------------------------------------------- | ---------------------------------------------------- |
| `job_number`                                    | `DataContext/DataSource/Key`                         |
| `shipment_key`                                  | `ForwardingShipment:` + Key                          |
| `source_type`                                   | `DataSource/Type` (always ForwardingShipment here)   |
| `transport_mode`                                | `TransportMode/Code` (fallback `ContainerMode/Code`) |
| `origin_code`                                   | `PortOfOrigin/Code`                                  |
| `destination_code`                              | `PortOfDestination/Code`                             |
| `total_weight`                                  | `TotalWeight` (fallback `DocumentedWeight`)          |
| `total_volume`                                  | `TotalVolume`                                        |
| `goods_description`                             | `GoodsDescription`                                   |
| `current_version` / `current_message_id`        | Analytics versioning (not CW leaves)                 |
| `first_seen_at` / `last_seen_at` / `updated_at` | Analytics timestamps                                 |

## `reporting.quotes` — CW nodes → columns

Source: CargoWise `UniversalShipment` with `DataSource/Type = OneOffQuote`.

| Column                                          | CargoWise node(s)                                    |
| ----------------------------------------------- | ---------------------------------------------------- |
| `quote_number`                                  | `DataContext/DataSource/Key`                         |
| `quote_key`                                     | `OneOffQuote:` + Key                                 |
| `source_type`                                   | Always `OneOffQuote`                                 |
| `transport_mode`                                | `TransportMode/Code` (fallback `ContainerMode/Code`) |
| `origin_code`                                   | `PortOfOrigin/Code`                                  |
| `destination_code`                              | `PortOfDestination/Code`                             |
| `total_weight`                                  | `TotalWeight` (fallback `DocumentedWeight`)          |
| `total_volume`                                  | `TotalVolume`                                        |
| `goods_description`                             | `GoodsDescription`                                   |
| `is_approved`                                   | `IsQuoteApprovedByManager`                           |
| `total_revenue`                                 | `JobCosting/TotalRevenue`                            |
| `total_job_profit`                              | `JobCosting/TotalJobProfit`                          |
| `converted_shipment_job_number`                 | Soft match: quote Key equals a shipment `job_number` |
| `has_converted_shipment`                        | Boolean from that match                              |
| `current_version` / `current_message_id`        | Analytics versioning                                 |
| `first_seen_at` / `last_seen_at` / `updated_at` | Analytics timestamps                                 |

`QuoteNumber` XML leaf is **not** a curated column in v1 — business id is `DataSource/Key` → `quote_number`.

## About `classification = unknown`

`cargowise_messages.classification` / `message_type` may show `unknown` for OneOffQuote, ForwardingConsol, and ForwardingBooking. That means the **stamping-domain** classifier has not named the type yet.

Raw XML is still stored. Analytics still projects by XML `DataSource/Type`. Quotes land in `reporting.quotes`. This is not missing data.

## Discovery columns

<AccordionGroup>
  <Accordion title="cargowise_xml_fields">
    `semantic_field_key`, `semantic_path`, `label`, `inferred_type`, `total_observation_count`,
    `first_seen_at`, `last_seen_at`
  </Accordion>

  <Accordion title="cargowise_xml_observations">
    `semantic_field_key`, `payload_id`, `label`, `semantic_path`, `original_text`, typed value
    columns (`numeric_value`, `boolean_value`, `timestamp_value`), `created_at`
  </Accordion>
</AccordionGroup>

## Known curated gaps

These are **not** curated `shipments` columns today — the ForwardingShipment feed does not supply them cleanly:

| Concept                      | Status                                               |
| ---------------------------- | ---------------------------------------------------- |
| Operations Rep staff code    | Needs outbound source with staff code + shipment key |
| Posted transaction Post Date | Needs Universal Transaction (or equivalent) feed     |
| Revenue by Post Date         | Needs agreed revenue definition + matching feed      |

If those leaves exist on another message type your org already sends, they may still appear in [field discovery](/analytics/discover-fields). They will not become friendly curated columns until the feed and meaning are confirmed.

Not curated yet as whole views: consols, bookings, charge-line grain.

## Access rules

* Read-only `SELECT`
* No writes
* No cross-organization rows
* No access to internal schemas behind `reporting`
* Up to 5 concurrent sessions per credential
* Up to 2 active credentials per organization

For connection setup see [Connect](/analytics/connect). For Power BI model notes see [Power BI](/analytics/power-bi).