> ## 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.

# Relationships and data model

> Learn the identities, ownership keys, grains, and explicit relationships that make Analytics composable.

Analytics is a graph of business-owned relational data. The model answers three questions for every
query:

1. What does one row represent?
2. Which business object owns the row?
3. Which evidence connects it to other objects?

## Core terms

| Term            | Meaning                                                             | Main key          |
| --------------- | ------------------------------------------------------------------- | ----------------- |
| Identity        | A stable business object known inside a recognized object scope     | `entity_id`       |
| Business entity | A current full object body received from CargoWise                  | `entity_id`       |
| Component       | A repeated or nested object owned by an entity                      | `component_id`    |
| Property        | A scalar value owned by an entity or component                      | `property_id`     |
| Relationship    | An observed, directed connection between two entities or components | `relationship_id` |
| Grain           | What one row in a relation represents                               | Relation-specific |

An identity can exist without a current full entity. This happens when CargoWise supplies an
explicit reference to an object whose full body has not arrived.

## The graph

```mermaid theme={null}
flowchart TD
  I["entity_identities<br/>all known endpoints"]
  B["business_entities<br/>current full objects"]
  S["business subtype relations"]
  D["entity detail relations"]
  C["entity_components"]
  P["entity_properties"]
  ER["entity_relationships"]
  CR["entity_component_relationships"]

  I -->|"0 or 1 full body"| B
  B -->|"0 or 1 subtype"| S
  B -->|"1 to many by entity_id"| D
  B -->|"1 to many by entity_id"| C
  C -->|"parent_component_id"| C
  B -->|"entity-owned values"| P
  C -->|"component_id"| P
  I -->|"from_entity_id"| ER
  I -->|"to_entity_id"| ER
  C -->|"from_component_id"| CR
  C -->|"to_component_id when resolved"| CR
```

`reporting.entity_identities` resolves every evidence-backed endpoint. A row with
`is_reference_only = true` has no current full object body.

`reporting.business_entities` contains current full recognized objects. It stores shared route,
cargo, direction, JobCosting summary, and lineage fields. Subtype relations add the business number
or subtype-specific fields:

* `business_shipments`
* `business_one_off_quotes`
* `business_consols`
* `business_bookings`

## Ownership through `entity_id`

Every current typed detail row belongs to a business entity through `entity_id`.

```text theme={null}
business_entities.entity_id
  |-- entity_dates.entity_id
  |-- entity_locations.entity_id
  |-- entity_staff_assignments.entity_id
  |-- entity_parties.entity_id
  |-- entity_charges.entity_id
  |-- entity_transport_legs.entity_id
  |-- entity_containers.entity_id
  |-- entity_packing_lines.entity_id
  `-- entity_properties.entity_id
```

This key is the normal join path for business reporting. A displayed job number, quote number,
organization code, name, or route is not a replacement for `entity_id`.

An organization code can appear in several party roles. A date code can occur several times. A
charge code can repeat. The owning entity and the row's documented grain preserve the meaning.

## Nested ownership through `component_id`

Repeated objects receive a `component_id`. Nested components also retain
`parent_component_id`.

Consider one shipment containing a container and a penalty:

```text theme={null}
Entity S1
  `-- Container component C1
        `-- ContainerPenalty component P1
              `-- penalty properties
```

The rows keep these keys:

```text theme={null}
Container C1
  entity_id = S1
  component_id = C1

Penalty P1
  entity_id = S1
  component_id = P1
  parent_component_id = C1
```

The typed `entity_container_penalties` view also exposes `container_component_id`, which joins
directly to `entity_containers.component_id`.

Analytics does not create a component for every XML tag. Repeated meaningful objects become
components. Scalar values become properties. Structural wrappers remain visible in source paths.

## Properties and the long tail

`reporting.entity_properties` stores current scalar values that do not need a dedicated typed
column.

Each property retains:

* the owning `entity_id`
* an optional owning `component_id`
* the CargoWise field name and relative path
* the original value
* safe typed and normalized values when available
* source lineage

Properties let you use an uncommon field without waiting for a new report-specific table. They are
still limited to recognized entity roots. Values outside those roots remain in the source
observation layer.

## Entity relationships

`reporting.entity_relationships` stores one observed directed edge. The important columns are:

| Column                            | Meaning                                     |
| --------------------------------- | ------------------------------------------- |
| `from_entity_id`                  | Directed source endpoint                    |
| `to_entity_id`                    | Directed target endpoint                    |
| `observing_entity_id`             | Full entity whose XML supplied the evidence |
| `observing_entity_version`        | Version that supplied the evidence          |
| `relationship_type`               | Business meaning of the edge                |
| `evidence_basis`                  | How the source proved the relationship      |
| source path and reference columns | Exact CargoWise evidence                    |

Both endpoints join to `entity_identities`. The observing entity joins to `business_entities`.

### Quote conversion

CargoWise can place `QuoteNumber` on a shipment, booking, or consol. Analytics creates a directed
edge only from that explicit evidence:

```text theme={null}
Reference-only or full quote identity
  from_entity_id
        |
        v
entity_relationships
        |
        v
  to_entity_id
Shipment, booking, or consol
```

The observer is the shipment, booking, or consol whose XML contained `QuoteNumber`.

Analytics never creates this link because a quote number resembles a job number or because route,
customer, date, weight, or amount happens to match.

### Parent and inherited context

Other entity relationship types include:

| Type                       | Meaning                                                       |
| -------------------------- | ------------------------------------------------------------- |
| `contains_subshipment`     | A parent entity contains a nested shipment                    |
| `subset_completeness`      | A top-level source identifies complete nested subset data     |
| `effective_transport_leg`  | A shipment uses a leg supplied by another entity              |
| `quote_to_shipment`        | A quote explicitly connects to a shipment                     |
| `quote_to_booking`         | A quote explicitly connects to a booking                      |
| `quote_to_consol`          | A quote explicitly connects to a consol                       |
| `quote_to_business_entity` | A quote explicitly connects to another recognized entity type |

The same logical edge can be observed in several versions. Use `EXISTS` for eligibility or
deduplicate the endpoint pair when you need one business relationship.

## Component relationships

Nesting and referencing are different.

`parent_component_id` records structural nesting. `entity_component_relationships` records an
explicit reference between components that can live in different parts of the XML tree.

Supported component relationships include:

* `packing_line_to_container`
* `instruction_container_link_to_container`
* `instruction_packing_line_link_to_packing_line`

For a packing line reference:

```text theme={null}
Packing line component P1
  from_component_id
        |
        v
entity_component_relationships
        |
        v
  to_component_id
Container component C1
```

`resolution_status` tells you whether the endpoint resolved. The source reference and diagnostic
remain queryable when it did not.

## Missing relationships

`reporting.relationship_projection_audits` explains why an expected entity link is present or
absent.

| Status                        | Meaning                                                       |
| ----------------------------- | ------------------------------------------------------------- |
| `resolved`                    | The relationship was created                                  |
| `source_reference_unresolved` | CargoWise supplied a reference but no safe endpoint was found |
| `ambiguous`                   | More than one possible endpoint remained                      |
| `not_observed`                | The expected source evidence was absent                       |
| `unsupported_shape`           | The source shape is not currently mapped                      |

A missing link does not remove either entity or its values.

## Current and historical relations

| Relation kind                                     | Behavior                                 |
| ------------------------------------------------- | ---------------------------------------- |
| `business_entities` and typed detail              | Current full or effective current data   |
| `entity_components` and `entity_properties`       | Current graph for recognized entities    |
| `entity_relationships`                            | Historical observed edge evidence        |
| `entity_component_relationships`                  | Historical observed component references |
| `relationship_projection_audits`                  | Historical projection decisions          |
| Payload, delivery, message, and observation views | Historical source evidence               |

Current detail can come from an earlier valid entity version after a partial update. Join current
business queries by `entity_id`. Do not require the detail version to equal the entity's current
version.

## Grain protects totals

A grain is what one row represents:

* one row in `business_entities` means one current full entity
* one row in `entity_charges` means one charge line
* one row in `entity_packing_lines` means one packing line
* one row in `entity_relationships` means one observed edge

Suppose one entity has two matching dates, three matching charges, and two matching staff
assignments. A direct join produces 12 rows for the same entity.

Use `EXISTS` when detail only decides whether an entity qualifies:

```sql theme={null}
SELECT
  entities.entity_type,
  count(*) AS entity_count,
  sum(entities.total_weight_kg) AS total_weight_kg
FROM reporting.business_entities AS entities
WHERE EXISTS (
  SELECT 1
  FROM reporting.entity_dates AS dates
  WHERE dates.entity_id = entities.entity_id
    AND dates.date_type_code = 'ETD'
)
AND EXISTS (
  SELECT 1
  FROM reporting.entity_staff_assignments AS staff
  WHERE staff.entity_id = entities.entity_id
    AND staff.staff_role = 'sales'
)
GROUP BY entities.entity_type;
```

Use a pre-aggregated subquery when you need a detail measure beside an entity measure:

```sql theme={null}
WITH charge_totals AS (
  SELECT
    entity_id,
    sum(sell_local_amount) AS sell_local_amount
  FROM reporting.entity_charges
  WHERE sell_is_posted IS TRUE
  GROUP BY entity_id
)
SELECT
  entities.entity_type,
  count(*) AS entity_count,
  sum(entities.total_weight_kg) AS total_weight_kg,
  sum(charge_totals.sell_local_amount) AS sell_local_amount
FROM reporting.business_entities AS entities
JOIN charge_totals
  ON charge_totals.entity_id = entities.entity_id
GROUP BY entities.entity_type;
```

Confirm currency before summing charge amounts. Confirm normalized units before summing weight.

Continue to [Power BI](/analytics/power-bi) for a reusable semantic model or
[Query with SQL](/analytics/sql) for query workflows.