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. The draft tables also hold each cycle's replenishment requests.
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. |
| discrepancy_notification_sent | boolean | NOT NULL, default false. Whether the receiving discrepancy email for this PO has gone out. Set once, never reset. See When a discrepancy is flagged. |
| 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. Legacy. The old per-line email set it. Nothing writes it now, because the email is tracked per PO on Inventory Purchase Orders. |
| 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_statusis'closed', so the PO has finished receiving.received_quantitydiffers fromtotal_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 put a number in the discrepancy email 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.
The discrepancy email goes out once per PO, listing every flagged line. The
Purchase Order Update Handler
calls it after writing every line item of a closed PO, and
discrepancy_notification_sent on the PO stops it from sending twice. Nothing
resets that flag, so a quantity that changes after the email has gone out is not
announced again.
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_idin 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 neitherbase_skunorcase_skuas0.
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
Two triggers act on this table:
line_item_status_update(BEFORE INSERT/UPDATE) runsupdate_purchase_order_line_items()to setdiscrepancy_detected. See When a discrepancy is flagged.trigger_update_inventory_sum(AFTER DELETE/UPDATE) runsupdate_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.
The same table also holds replenishment requests: a row with a request_token is a request sent to a brand, not a PO.
| 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. A replenishment request uses Awaiting brand response, Brand responded, or Drafts created instead. |
| 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 draft PO email has been sent. Nothing sets it now, because Notify: New Draft PO Created was retired on October 7, 2026. |
| 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. |
| request_token | text | UNIQUE. The magic-link token of a replenishment request. Null on a draft PO, and what tells the two apart. |
| request_created_by | uuid | Who created the request. |
| request_responded_at | timestamptz | When the brand submitted its response. |
| request_replaced_at | timestamptz | When a newer request for the same cycle closed this one's link. |
| brand_notes | text | The brand's "Any notes for National Commerce" text. |
| source_request_id | bigint | → references this table's id. ON DELETE SET NULL. On a draft PO, the request it was created from. |
| 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. On a request line, set on every line except an older new-SKU row the brand typed in. |
| 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. On a request line, the line's own warehouse. |
| recommended_quantity | integer | Request lines only. Recommended, in trays: the Replen Report's Amount To Reorder at snapshot. Never edited. Null on a new-SKU row and on a line NC added after the request was made, which is how the app tells an added line apart. |
| requested_quantity | integer | Request lines only. Requested, in trays: what NC asks the brand for. Starts at recommended_quantity, or at what NC entered on a line NC added; NC staff can change it (whole number, 1 or more) until the brand responds. Approve copies it into approved_quantity. Null on a new-SKU row. |
| required_quantity | integer | Request lines only. The old single Required number, replaced by recommended_quantity and requested_quantity. Still written on new requests so a rollback reads the same number, but not on lines added later, and dropped in a later migration. Don't read it in new code. |
| deleted_at | timestamptz | Request lines only. When NC staff removed the line from the request. Removed lines are kept for the record and left out everywhere the app reads lines. |
| deleted_by | uuid | Request lines only. The NC staff user who removed the line. |
| decision | text | Request lines only. CHECK value is Approve or Cut. Cut shows on screen as Edit (the button) and Edited (the line). Null until the brand responds, and on a new-SKU row. |
| approved_quantity | integer | Request lines only. CHECK approved_quantity >= 0. Approved, in trays. |
| item_number | text | Request lines only. The Case SKU code, or the Base SKU code when there is no Case SKU, or, on an older new-SKU row, the Item # the brand typed. Names the draft PO line. The screens and ERP CSV show the Base SKU's inventory_sku instead, read live through base_sku. |
| flavor | text | Request lines only. The Case SKU product name, or the Base SKU name, or, on an older new-SKU row, what the brand typed. Names the draft PO line. The screens and ERP CSV show the Base SKU's inventory_name instead. |
| is_new_sku | boolean | NOT NULL, default false. True on a row the brand added on the magic link, which has approved_quantity but no Recommended, Requested or decision. Brands pick from their active Base SKUs, so the row carries base_sku and case_sku and goes onto the draft POs like any approved line. Older rows the brand typed in have no base_sku and stay off the drafts. |
| 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 requests
A replenishment request is stored on these two tables rather than in tables of its own. One header row in Inventory Replen Draft Purchase Orders is one request sent to one brand for one cycle. It has a request_token, replenishment_cycle_id, and brand_id, and no warehouse_id or vendor_id. Its line items are one per Base SKU per warehouse, each carrying its own warehouse in purchase_order_warehouse_id.
statusmoves fromAwaiting brand responsetoBrand respondedwhen the brand submits, then toDrafts createdwhen NC creates draft POs from it. The line items mirror the status.- Only the latest request on a cycle is open. Creating a new one sets
request_replaced_aton the earlier ones. - While the latest request is
Awaiting brand response, NC staff can change a line'srequested_quantityor remove the line, which setsdeleted_atanddeleted_byrather than deleting the row. Both only write while the line has nodecision. The last line can't be removed. - In the same window NC staff can add lines: one Base SKU at a time, one line per warehouse, from the account's active Base SKUs and linked warehouses. An added line has
recommended_quantitynull. A SKU and warehouse can be on a request only once. The add is checked again once the rows exist, and if the brand submitted meanwhile or the same SKU and warehouse went on twice at the same moment, the new rows are removed throughdeleted_at. - The brand's submit writes each line only if
requested_quantitystill holds the number the brand decided against anddeleted_atis null. If NC changed the line in between, or added a line the brand hasn't seen, the submit is rolled back and the brand is asked to look again. - Draft POs created from a request are ordinary drafts (status
Draft) withsource_request_idpointing back at it. - Request rows never appear in the Purchase Orders list or on a cycle's Purchase Orders tab. The list only reads draft statuses, and the cycle reads rows where
request_tokenis null.
The brand's magic link reads and writes a request with the service role, matched on request_token, because the brand has no login. See Replenishment requests.
Client Users can't read these tables. To show a signed-in brand the link on its replenishment, the app reads only the open request's request_token with the service role, after reading the cycle as the brand and checking its account.
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. |
| deleted_at | timestamptz | When NC staff deleted the cycle. Null unless deleted. |
| deleted_by | uuid | → references auth.users(id), ON DELETE SET NULL. Who deleted 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. The app no longer offers 050, 299, or 300 in any status dropdown or filter, but the constraint still allows them and their timestamp columns stay. Two statuses read differently on screen: 000 New shows as Internal Draft and 1000 Cycle Completed as Replenishment Completed. See Status pipeline.
Soft delete. Deleting a replenishment in the app sets deleted_at and deleted_by and keeps the row. The app's screens and the Assistant leave the cycle out (deleted_at IS NULL), and its drafts and requests are hidden with it. Its drafts can't be submitted to ShipHero, and its requests take no response and make no draft POs. A cycle with a synced PO or a pushed draft attached can't be deleted until that PO is unlinked. To restore one, set deleted_at and deleted_by back to null. See Deleting a replenishment.
Row-Level Security. Every user on an account reads its cycles, Client Users included, so brands can see their replenishments. Only NC staff (is_nc_staff()) insert or update a cycle, and only on their own accounts. No rule allows a delete. The service role is unrestricted. The full set is in supabase/migrations/20261105100000_brands_read_replenishments.sql. 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.