Cin7 Integration & Status Mapping¶
This document explains how purchase orders flow from Cin7 (the source of truth for PO data) into the PO Management App, and how Cin7's own status/stage model maps to the app's internal status system.
Overview¶
Cin7 is the company's ERP/inventory system. POs are created and managed in Cin7. A nightly Airflow DAG syncs PO data from the Cin7 API into the Azure SQL database. The PO Management App then adds its own workflow layer on top of this synced data.
[!IMPORTANT] The app does not write back to Cin7. All workflow actions (approve, reject, ship, etc.) only update the app's own status columns (
app_po_status_id,app_po_status). Cin7 remains the originating system of record for PO line item data, dates, and vendor info.
Cin7 Data Model vs. App Data Model¶
Cin7 tracks a PO using two separate fields:
| Cin7 Field | What It Means | Example Values |
|---|---|---|
status |
High-level PO approval status | APPROVED, VOID |
stage |
Fulfillment/shipping stage | NULL, New, In Transit, Delivered, Received |
The app collapses these two fields into a single status ID (app_po_status_id) that drives
the entire workflow.
Status Mapping Table¶
The following table shows how every meaningful Cin7 status + stage combination maps to an
app status. This mapping is applied during the initial nightly sync and also via the
Phase 3 backfill migration.
Cin7 status |
Cin7 stage |
App Status ID | App Status Label | Notes |
|---|---|---|---|---|
APPROVED |
NULL / New |
100 | New | PO just arrived, vendor hasn't acted yet |
APPROVED |
In Transit |
500 | Shipped | Goods already on the way when synced |
APPROVED |
Delivered |
600 | Delivered | Already received when synced |
APPROVED |
Received |
600 | Delivered | Cin7 uses both terms interchangeably |
VOID |
(any) | (hidden) | — | Excluded from the app entirely via SQL filter |
[!NOTE] Why does Cin7 say "In Transit" but the app says "Shipped"?
Cin7's
stage = 'In Transit'means goods are physically on their way — which is what the app calls Shipped (status ID 500). The app's terminology was aligned to the internal team's language ("Shipped" is clearer for managers confirming delivery). The KPI card on the Dashboard is labeled "Shipped" to match this.
Airflow Nightly Sync Behavior¶
The Airflow DAG runs a MERGE operation against cin7_purchase_orders_headers each night:
- Matched rows (PO already exists): Cin7-sourced columns are updated (dates, vendor name, line items, etc.). App-owned columns are never overwritten (see list below).
- New rows (PO not yet in DB): A fresh row is inserted with
app_po_status_id = 100(New) unless the Cin7stageindicates a more advanced state (see mapping above).
App-Owned Columns (Protected from Sync)¶
These columns are owned by the app and are never touched by Airflow:
| Column | Purpose |
|---|---|
app_po_status_id |
Current workflow status |
app_po_status |
Status label (denormalized for query performance) |
vendor_reply_xfd |
Vendor's proposed ex-factory date |
vendor_reply_factory |
Vendor's proposed factory |
vendor_reply_comment |
Vendor's comments |
has_pending_changes |
Whether vendor has unsaved edits |
vendor_confirmed_at |
Last vendor submission timestamp |
manager_action_at |
Last manager action timestamp |
manager_rejection_reason |
Rejection feedback |
container_number |
Shipping container # |
tracking_number |
Shipment tracking # |
is_locked |
Whether PO is locked for editing |
is_fit_sample_approved |
FIT sample sign-off |
is_cfm_sample_approved |
CFM sample sign-off |
VOID PO Handling¶
Cin7 marks cancelled/voided POs with status = 'VOID'. The app handles these as follows:
- All API queries include the filter
ISNULL(h.status, '') <> 'VOID', so VOID POs are completely invisible in the UI (PO list, dashboard, PO details). - VOID POs are not deleted from the database — they remain for audit purposes.
- The Phase 3 migration script also sets
app_po_status_id = 900(Cancelled) on VOID rows for data integrity, even though they are never displayed.
Status Color Reference¶
For quick reference, each app status has a unique color used in badges throughout the UI:
| Status | ID | Badge Color |
|---|---|---|
| New | 100 | Blue (#0078d4) |
| Requires Revision | 150 | Red (#c50f1f) |
| Pending Approval | 200 | Orange (#ca5010) |
| Confirmed | 300 | Green (#107c10) |
| In Production | 400 | Green (#0e7a0d) |
| Shipped | 500 | Purple (#8764b8) |
| Delivered | 600 | Teal (#038387) |
| Cancelled | 900 | Grey (#797775) |
Common Questions¶
Q: A PO shows as "Shipped" in the app but I updated it to "Delivered" in Cin7 — why didn't it change?
The app does not read Cin7's stage field after initial sync. Once a PO has been assigned an
app_po_status_id by the app (e.g., through the Confirm Delivery action), Cin7's stage has no
further effect. Status transitions in the app are driven exclusively by manager/vendor actions.
Q: A brand-new PO synced from Cin7 shows as "New" but it was already shipped. Why?
The nightly sync checks the Cin7 stage field on insert. If the Cin7 stage wasn't updated before
the sync ran, the PO will land as "New". A manager can manually advance it, or the Phase 3
migration script can be re-run to backfill the correct status.
Q: Why are some POs missing from the app entirely?
Check if the PO has status = 'VOID' in Cin7. VOID POs are filtered out of all app queries.
Also verify po_ref IS NOT NULL and po_ref <> 'N/A' — rows with invalid PO references are
also excluded.