Orders & Shipments

How customer orders, their shipment boxes, line items, and recipients are modeled and kept in sync.

This page covers the core fulfillment tables. An Order is a customer purchase. It is fulfilled by one or more Shipments (physical boxes), each holding one or more Line Items (a quantity of one Product SKU). Every Order also points at an End Customer, the person the boxes ship to. Order totals such as label cost are rolled up from Shipments and Line Items by database triggers, so you rarely write those columns directly.

Orders

A customer order. Money and label totals are not entered by hand: they are recomputed from the Order's Shipments and Shipment Line Items whenever those change. shipping_threshold_exceeded flags orders whose shipping cost passed the configured postage threshold.

Column	Type	Notes
id	integer	PK.
Order_Number	text	Human-readable order number.
Order_Date_Time__Shiphero_	timestamp	Order date/time from ShipHero.
Subtotal	numeric	Line item subtotal.
Total_Tax	numeric	Tax total.
Total_Discounts	numeric	Discount total.
Total_Price	numeric	Order grand total.
total_label_cost	numeric	Sum of Shipment label costs. Default 0. Rolled up by triggers. See label cost.
total_postage_threshold	numeric	The postage threshold applied to this order.
shipping_proceeds	numeric	Shipping revenue collected from the customer.
shipping_threshold_exceeded	boolean	True when shipping passed the threshold. Default false.
shipping_threshold_exceeded_notification_sent	boolean	True once the crossing notification has been sent. Default false.
fulfillment_expense	numeric	Fulfillment cost. Default 0.0000. Must be `>= 0`.
End Customer (Amazon buyer)_id	integer	→ references End Customer (Amazon buyer).
brand	integer	→ references Brands. See brand.
store	bigint	→ references Stores.
created_at, updated_at, created_by, updated_by	audit	Standard audit columns.

Automation. total_label_cost and the money totals are recomputed by triggers on Shipments and Shipment Line Items (see below), so treat them as derived. When shipping_threshold_exceeded changes, an AFTER UPDATE trigger (shipping_threshold_exceeded_trigger) posts to an n8n webhook via supabase_functions.http_request so a notification can go out.

Relationships: each Order belongs to one Brand and one Store, and points at one End Customer. It is fulfilled by many Shipments, each of which links back to this Order.

Shipments

One physical box with its own shipping label, tracked by Tracking_ID and priced by Label_Cost. Each Shipment belongs to an Order. The shipping_zone (1..8, with 9 for territories and military addresses) is computed from zone_distance_miles.

Column	Type	Notes
id	integer	PK.
Shipment_Name	text	Box/shipment name.
Orders_id	integer	→ references Orders.
Warehouses_id	integer	→ references Warehouses. Ships from this warehouse.
Service_Level	text	Carrier service level.
Tracking_ID	text	Carrier tracking number.
Label_Cost	numeric	Cost of this box's label. See label cost.
Box_Name	text	Packaging/box type.
Box_Weight__oz_	numeric	Box weight in ounces.
Box_Length, Box_Width, Box_Height	numeric	Box dimensions.
Shipment_Time__Shiphero_	timestamp	Shipment time from ShipHero.
zone_type	enum	USER-DEFINED. Postgres enum.
zone_calculation_status	enum	USER-DEFINED. Postgres enum for the zone computation state.
zone_error_details	text	Error detail if zone computation failed.
shipping_zone	integer	Computed zone 1..8 (9 = territories/military).
zone_distance_miles	numeric	Distance used to compute the zone.
zone_calculated_at	timestamp	When the zone was last computed.
brand	integer	→ references Brands. Must be `> 0`.
store	integer	→ references Stores.
created_at, updated_at, created_by, updated_by	audit	Standard audit columns.

zone_type and zone_calculation_status are Postgres enum types (shown as USER-DEFINED in the DDL).

Automation. On insert, trigger_sync_shipment_store (BEFORE INSERT) runs sync_shipment_store() to copy the store from the linked Order. After a Shipment is updated or deleted, trigger_update_orders / trigger_update_orders_on_deleted_shipment recompute the parent Order's rollups (label cost and totals).

Relationships: each Shipment belongs to one Order, one Warehouse, one Store, and one Brand. It contains many Shipment Line Items.

Shipment Line Items

A quantity of one Product SKU inside a Shipment box. This is the line item level of an order's fulfillment.

Legacy sequence name

The primary key uses the sequence "Line items in shipment_id_seq", a leftover from an earlier table rename. It is unrelated to the current table name.

Column	Type	Notes
id	integer	PK.
Line_Item_Id	text	Source line item identifier.
Shipments_id	integer	→ references Shipments.
product_sku	integer	→ references Product SKUs. Must be `> 0`.
SKU_Quantity	bigint	Quantity of this SKU in the box.
shipment_tracking_id	text	Tracking ID copied from the parent Shipment (see automation).
out_from_inventory	numeric	Quantity drawn from inventory.
counter_for_inventory	boolean	Inventory counting flag. Default false.
Counted	boolean	Whether this line has been counted. Default false.
tmp_correct_line_item	boolean	Temporary correction flag. Default false.
brand	integer	→ references Brands.
created_at, updated_at, created_by, updated_by	audit	Standard audit columns.

Automation. After a Line Item is updated or deleted, trigger_update_orders (update_orders_on_new_line_item()) and trigger_update_orders_on_deleted_line_item (update_orders_on_deleted_line_item()) keep the parent Order's totals current. On insert or update, trigger_update_tracking_id_for_shipment_line_item fills shipment_tracking_id from the parent Shipment.

Relationships: each Line Item belongs to one Shipment, references one Product SKU, and carries a Brand. Through its Shipment it rolls up to an Order.

End Customer (Amazon buyer)

The shipping recipient for an Order: the person and address the boxes are delivered to.

Column	Type	Notes
id	integer	PK.
Full_Name	text	Recipient name.
Address_1	text	Street address line 1.
Address_2	text	Street address line 2.
City	text	City.
State	text	State. Standardized by a trigger (see automation).
Zip_Code	text	Postal code.
Country	text	Country.
Order_Date_Time__Shiphero_	timestamp	Order date/time from ShipHero.
created_at, updated_at, created_by, updated_by	audit	Standard audit columns.

Automation. standardize_state_on_insert (BEFORE INSERT/UPDATE) normalizes the State value so it is stored in a consistent form.

Relationships: one End Customer can be referenced by many Orders through Orders.End Customer (Amazon buyer)_id.

shipments_queue

Inbound work queue holding raw ShipHero shipment payloads waiting to be turned into Shipments.

Column	Type	Notes
id	bigint	PK. Generated always as identity.
payload	jsonb	Raw ShipHero shipment payload.
status	enum	Postgres enum `shipment_processed_status`. Defaults to `'new'`.
processing_output	text	Result/output from processing.
brand	integer	Owning brand.
created_at	timestamp	When the payload was queued.

status is the Postgres enum shipment_processed_status, defaulting to 'new'.

Automation. Rows are consumed by the process_shipment_queue() function, which parses each payload and creates the corresponding Shipments. See Automation for the full catalog.

shiphero_orders_export

A flat import/export staging table of ShipHero orders, keyed by shiphero_order_id. It has no foreign keys and is not part of the live Orders relationships. Treat it as staging data.

Column	Type	Notes
shiphero_order_id	bigint	PK. ShipHero order id.
order_number	text	Order number. Not null.
order_date_local	text	Order date, local, as text.
order_date_utc	timestamp	Order date in UTC.
store_name	text	Store name.
status	text	Order status text.
subtotal	numeric	Subtotal.
shipping	numeric	Shipping amount.
labels	integer	Number of labels.
city	text	City.
state	text	State.
zip	text	Postal code.

This table is populated by imports and read for exports; it does not link to Orders, Shipments, or other live tables.