shipments

source 1 PII columns

Where parcels go and how long they take.

Definition

Per-order shipment rows joined to `addresses` for the ship-to destination. Carries lifecycle status (`label_created` → `in_transit` → `delivered` / `returned` / `lost`) plus the three timestamps that drive SLA metrics. `tracking_number` is tagged behavioural-PII (carrier audit trail).

12 of 12

Column

Description

Tests · PII

shipment_id

Primary key — BIGSERIAL on the source.

2 tests

order_id

FK to `orders.order_id`. NOT NULL. ON DELETE CASCADE.

customer +2 PII1 test

shipping_address_id

FK to `addresses.address_id`. Nullable.

carrier

`ups` / `fedex` / `dhl` / `usps` / `royal_mail`.

1 test

tracking_number

Carrier-issued tracking id. Nullable; behavioural PII.

PII

service_level

`standard` / `express` / `overnight`.

status

`label_created` / `in_transit` / `delivered` / `returned` / `lost`.

1 test

shipped_at

When the carrier picked up the parcel. Nullable for label-only shipments.

estimated_delivery_at

Carrier ETA at label time. Nullable.

delivered_at

When delivered. Nullable until delivery.

created_at

Row insert timestamp in the source DB.

updated_at

Row update timestamp in the source DB.