Purchase Orders

Tables that track imported purchase orders, draft replenishment POs, their line items, and the queues that keep them in sync with ShipHero.

Purchase orders record the stock a brand has ordered into a warehouse. This page covers both the real POs imported from ShipHero and the draft replenishment POs built in the portal, along with the queues that process updates and re-imports.

Two parallel PO families

The schema keeps two separate families of tables. Real / imported POs live in Inventory Purchase Orders with their rows in Inventory Purchase Order Line Items; these come from ShipHero and track received vs. rejected quantities. Draft / replen POs live in Inventory Replen Draft Purchase Orders with their rows in Inventory Replen Draft Purchase Order Line Items; these are built in the portal and later pushed to ShipHero. Two supporting tables sit alongside them: purchase_order_updates_queue processes incoming ShipHero update payloads, and deleted_pos_pending_reimport tracks POs deleted in the portal that still need to be re-imported. A third, replenishment_cycles, groups the POs for one brand's reorder period; both PO tables link to it.

Inventory Purchase Orders

A real purchase order imported from ShipHero. Each row represents one PO ordered by a brand into a warehouse.

Column	Type	Notes
id	integer	Primary key. Defaults from the `Inventory Purchase Orders_id_seq` sequence.
purchase_order_name	text	Human-readable PO name.
purchase_order_uuid	text	The ShipHero PO id.
vendor_id	text	Plain text vendor id, not a foreign key.
brand_id	integer	→ references Brands.
warehouse_id	integer	→ references Warehouses.
status	text	Current PO status.
purchase_order_created_at	timestamptz	When the PO was created in ShipHero.
purchase_order_updated_at	timestamptz	When the PO was last updated in ShipHero.
purchase_order_closed_at	timestamptz	When the PO was closed.
purchase_order_expected_to_arrive_at	timestamptz	Expected arrival date.
replenishment_cycle_id	bigint	→ references replenishment_cycles. Set when a draft is pushed or an existing PO is linked to a cycle.
source_draft_po_id	integer	The draft PO this was pushed from. Deliberately not a foreign key, because the two PO tables share one id sequence.
created_at, updated_at, created_by, updated_by	timestamp / varchar	Standard audit columns.

Automation. The update_purchase_order_status_trigger (AFTER UPDATE) runs update_purchase_order_status_for_line_items(), which cascades the PO's status down to its line items. Full catalog at /docs/database/automation.

A PO belongs to one brand and one warehouse. Its line items are held in Inventory Purchase Order Line Items via their inventory_purchase_order column.

Inventory Purchase Order Line Items

Line items on a real PO. Each row records a Case SKU or Base SKU ordered on the PO and tracks ordered vs. received vs. rejected quantities. The discrepancy columns drive the discrepancy workflow, and the classification columns hold a line item whose SKU code has not been classified yet.

Column	Type	Notes
id	integer	Primary key. Defaults from `Inventory Purchase Order Line Items_id_seq`.
purchase_order_line_item_id	text	UNIQUE. The ShipHero line item id.
purchase_order_line_item_name	text	Human-readable line item name.
inventory_purchase_order	integer	→ references Inventory Purchase Orders.
base_sku	integer	→ references Base SKUs. CHECK `base_sku > 0`. Null while the code is held for classification.
case_sku	integer	→ references Case SKUs. CHECK `case_sku > 0`. Null while the code is held for classification.
shiphero_sku	text	The raw ShipHero SKU code on the line. Written on every import so a held line item can be found again once its code is classified.
pending_queue_id	bigint	→ references sku_pending_queue. `ON DELETE SET NULL`. The queue row this line item is waiting on. Cleared once the SKU is linked.
sku_classification_status	text	NOT NULL, default `'resolved'`. CHECK value is `'resolved'` or `'pending_classification'`.
sku_classified_at	timestamptz	When the SKU code was classified and the line item linked.
total_quantity	integer	Quantity ordered.
received_quantity	integer	Quantity received.
rejected_quantity	integer	Quantity rejected.
discrepancy_detected	boolean	NOT NULL, default `false`. CHECK value is `true`/`false`. Flags a received/ordered mismatch. Only set once the PO is closed, see When a discrepancy is flagged.
discrepancy_notification_sent	boolean	NOT NULL, default `false`. Whether the discrepancy alert has been sent.
is_resolved	text	Default `'No'`. Resolution state of the receiving discrepancy, not of SKU classification.
resolved_at	timestamptz	When the discrepancy was resolved.
purchase_order_status	text	PO status copied onto the line item.
purchase_order_warehouse_id	integer	Warehouse id copied onto the line item.
fulfillment_status	text	Fulfillment state of the line item.
line_item_created_at	timestamptz	When the line item was created in ShipHero.
line_item_updated_at	timestamptz	When the line item was last updated in ShipHero.
created_at, updated_at, created_by, updated_by	timestamp / varchar	Standard audit columns.

Two unrelated kinds of resolution

is_resolved and resolved_at belong to the receiving-discrepancy workflow. They record whether someone has dealt with a received-vs-ordered mismatch, and they drive the Resolved filter on the receiving discrepancies screen.

sku_classification_status and sku_classified_at are separate and track whether the line item's SKU code has been classified. A line item can be classified but have an open discrepancy, or the reverse. Do not read one pair as a proxy for the other.

When a discrepancy is flagged

update_purchase_order_line_items() sets discrepancy_detected on every insert and update. A line item counts as a discrepancy only when both of these hold:

purchase_order_status is 'closed', so the PO has finished receiving.
received_quantity differs from total_quantity.

While a PO is still 'pending' the flag stays false, no matter how far receiving has progressed. That is deliberate. Receiving arrives in pieces, so a line that is 2 of 30 received mid-unload is not short by 28, it is simply not finished. Flagging it would fire the discrepancy webhook and email a number that is wrong by the time anyone reads it.

'canceled' POs are never flagged either. A cancelled order with nothing received is not a receiving problem.

A line that was never received at all still flags once the PO closes, because 0 differs from the ordered quantity. That is the most serious case and it is the one the notification exists for.

The cascade write is skipped on purpose

When a PO closes, update_purchase_order_status_for_line_items() stamps the new purchase_order_status onto every line item straight away, but the Purchase Order Update Handler writes the final quantities in a later statement. For a moment the line item reads as closed while still holding the previous sync's counts.

update_purchase_order_line_items() detects that write, where purchase_order_status changed but neither quantity did, and keeps the existing flag instead of recomputing. The quantity write that follows settles it against final counts. Removing that guard reintroduces wrong figures in the discrepancy email, just later in the process.

Nothing resets discrepancy_notification_sent, so each line item emails at most once for its whole lifetime. A discrepancy that appears, is corrected, and then appears again is not announced a second time.

Line items held for classification

When a PO imports a SKU code that has not been classified yet, the import does not create a Base SKU for it. The code goes to sku_pending_queue and the line item is saved with base_sku and case_sku both null, sku_classification_status set to 'pending_classification', and shiphero_sku plus pending_queue_id recorded so it can be found later.

A held line item is excluded from stock figures automatically, with no separate filter:

On-hand is keyed on base_sku_id in the Inventory table. A line item with no Base SKU has no Inventory row to attach to, so it cannot inflate on-hand.
Inbound is recomputed by update_inventory_sum_for_single_inventory(), which scores a line item with neither base_sku nor case_sku as 0.

Once the code is classified from the queue, every held line item for it is linked to the resulting SKU and flipped to 'resolved'. See Resolve the SKU queue.

Receiving and closing a PO is never blocked by a held line item. PO status comes from ShipHero and is not affected.

Automation on line items

Three triggers act on this table:

line_item_status_update (BEFORE INSERT/UPDATE) runs update_purchase_order_line_items() to set discrepancy_detected. See When a discrepancy is flagged.
purchase_order_line_item_discrepancy_trigger (AFTER UPDATE) posts to an n8n webhook via supabase_functions.http_request when a discrepancy is detected.
trigger_update_inventory_sum (AFTER DELETE/UPDATE) runs update_inventory_sum_for_single_inventory() to recompute the linked Inventory row's inbound total (see /docs/database/inventory).

Each line item belongs to one PO through inventory_purchase_order and points at the Base SKU and Case SKU it fulfills.

Inventory Replen Draft Purchase Orders

Draft replenishment POs built in the portal before being pushed to ShipHero. These are the portal-side counterpart to Inventory Purchase Orders.

Column	Type	Notes
id	integer	Primary key. Shares the `Inventory Purchase Orders_id_seq` sequence with the real-PO table.
purchase_order_name	text	Human-readable draft PO name.
vendor_id	text	Plain text vendor id, not a foreign key.
brand_id	integer	→ references Brands.
warehouse_id	integer	→ references Warehouses.
status	text	Current draft status. One of `Draft`, `PO In Progress`, or `ShipHero PO Created`.
purchase_order_created_at	timestamptz	When the draft was created.
sent_to_shiphero_at	timestamp	When the draft was pushed to ShipHero.
notification_sent	boolean	Default `false`. Whether the push notification has been sent.
tracking_number	text	Tracking number, once available.
replenishment_cycle_id	bigint	→ references replenishment_cycles. The cycle this draft belongs to. Null for a draft created outside a cycle.
shiphero_po_uuid	text	The ShipHero PO id returned when this draft was pushed. Pairs the draft with its synced PO in `Inventory Purchase Orders`.
updated_at	timestamptz	Standard audit column.

Shared sequence

This table draws its id from the same Inventory Purchase Orders_id_seq sequence as the real-PO table. IDs are unique across the sequence but not scoped per table, so never assume an id belongs to only one of the two tables.

A draft PO belongs to one brand and one warehouse. Its line items are held in Inventory Replen Draft Purchase Order Line Items via draft_inventory_purchase_order.

Inventory Replen Draft Purchase Order Line Items

Line items on a draft replen PO. Each row records a Case SKU or Base SKU with an intended quantity and price before the draft is pushed to ShipHero.

Column	Type	Notes
id	integer	Primary key. Shares the `Inventory Purchase Order Line Items_id_seq` sequence with the real-PO line item table.
purchase_order_line_item_name	text	Human-readable line item name.
draft_inventory_purchase_order	integer	→ references Inventory Replen Draft Purchase Orders.
base_sku	integer	→ references Base SKUs. CHECK `base_sku > 0`.
case_sku	integer	→ references Case SKUs. CHECK `case_sku > 0`.
quantity	numeric	Quantity to order.
price	numeric	Unit price.
vendor_sku_name	text	Vendor's SKU name for the line.
purchase_order_status	text	Draft status copied onto the line item.
purchase_order_warehouse_id	integer	Warehouse id copied onto the line item.
created_at, updated_at, updated_by	timestamp / varchar	Standard audit columns.

Each line item belongs to one draft PO through draft_inventory_purchase_order and points at the Base SKU and Case SKU it covers.

replenishment_cycles

One brand's reorder cycle for one period. A cycle belongs to one brand and has many purchase orders, which attach to it through replenishment_cycle_id on both PO tables. Cycles are created by a person, one at a time, from the Replenishment screens.

Column	Type	Notes
id	bigint	Primary key. `GENERATED ALWAYS AS IDENTITY`.
brand_id	integer	NOT NULL. → references Brands.
period_start_date	date	NOT NULL. The reorder period this cycle covers. Picked on create.
period_end_date	date	Auto-filled to `period_start_date` plus the account's `reorder_cadence_days`, and editable.
status	text	NOT NULL, default `'000 New'`. Constrained to the nine pipeline values below.
status_new_at	timestamptz	Default `now()`. Stamped when the cycle enters `000 New`.
status_replen_review_at	timestamptz	Stamped when the cycle enters `050 Replen Review In Progress`.
status_order_submitted_at	timestamptz	Stamped when the cycle enters `100 Order Submitted to Client`.
status_client_confirmed_at	timestamptz	Stamped when the cycle enters `200 Client Confirmed Order`.
status_followed_up_at	timestamptz	Stamped when the cycle enters `299 Followed Up For Client To Process Order`.
status_client_processed_at	timestamptz	Stamped when the cycle enters `300 Client Processed Order`.
status_pos_confirmed_at	timestamptz	Stamped when the cycle enters `400 POs Confirmed Created`.
status_completed_at	timestamptz	Stamped when the cycle enters `1000 Cycle Completed`.
status_cancelled_at	timestamptz	Stamped when the cycle enters `-10 Cancelled / Archived`.
status_changed_at	timestamptz	NOT NULL, default `now()`. The most recent transition, whichever status it was into.
created_at, updated_at	timestamptz	NOT NULL, default `now()`.
created_by	uuid	→ references `auth.users(id)`. Who created the cycle.

Status pipeline. status is constrained to 000 New, 050 Replen Review In Progress, 100 Order Submitted to Client, 200 Client Confirmed Order, 299 Followed Up For Client To Process Order, 300 Client Processed Order, 400 POs Confirmed Created, 1000 Cycle Completed, and -10 Cancelled / Archived. The numeric prefix gives the pipeline its order.

Row-Level Security. Select, insert, and update are scoped to the caller's accounts through userRoles, matching the rest of the schema. See Users & Access.

Why the timestamps are separate columns

Each stage has its own column rather than a single history table because the screens need two figures cheaply: days in the current status, from status_changed_at, and days since the brand's last cycle. Re-entering a status the cycle already holds is treated as a no-op, so status_changed_at is not reset and days-in-status keeps counting.

purchase_order_updates_queue

A work queue for ShipHero PO-update payloads. Each row holds one incoming update as JSON to be applied to the matching PO.

Column	Type	Notes
id	bigint	Primary key. `GENERATED ALWAYS AS IDENTITY`.
payload	jsonb	The raw ShipHero update payload.
processing_output	text	Result or error text from processing.
brand	integer	NOT NULL. → references Brands.
status	enum	NOT NULL, default `'new'`. Enum `purchase_order_update_processed_status`.
created_at	timestamptz	Default `now()`.

Automation. Rows are processed by process_po_updates_queue(), which reads pending payloads and applies them to the PO tables. See /docs/database/automation.

deleted_pos_pending_reimport

Tracks POs deleted in the portal that must be re-imported from ShipHero. Each row marks one deleted PO and the SKUs it contained until the re-import completes.

Column	Type	Notes
id	bigint	Primary key. `GENERATED ALWAYS AS IDENTITY`.
brand_id	integer	NOT NULL. → references Brands.
purchase_order_uuid	text	NOT NULL. The ShipHero PO id.
purchase_order_name	text	NOT NULL. Human-readable PO name.
deleted_sku_codes	text[]	NOT NULL, default `'{}'`. Array of SKU codes that were on the deleted PO.
deleted_by	uuid	→ references `auth.users(id)` (Supabase auth).
deleted_at	timestamptz	Default `now()`.
reimported_at	timestamptz	Set once the re-import completes. A null value means the PO is still pending.

A row belongs to one brand. deleted_by links to the Supabase auth user who performed the deletion.

On this page