Database Schema

Core Tables

cin7_purchase_orders_headers

The main PO header table. Contains both Cin7-sourced columns and app-owned columns.

Cin7-Sourced Columns (updated by Airflow nightly sync):

Column	Type	Description
po_id	NVARCHAR	Primary key from Cin7
po_ref	NVARCHAR	PO reference number (e.g., PO-1525)
billing_company	NVARCHAR	Vendor company name
xfd	NVARCHAR	Original ex-factory date
factory	NVARCHAR	Factory name
payment_terms	NVARCHAR	Payment terms
po_date	NVARCHAR	PO creation date

App-Owned Columns (managed by this application, excluded from Airflow MERGE):

Column	Type	Description
app_po_status_id	INT	Foreign key to Ref_PO_Status (default: 100)
vendor_reply_xfd	DATE	Vendor's proposed ex-factory date
vendor_reply_factory	NVARCHAR(200)	Vendor's proposed factory
vendor_reply_comment	NVARCHAR(MAX)	Vendor's comment
has_pending_changes	BIT	Whether vendor has unsaved edits
vendor_confirmed_at	DATETIME2	When vendor last confirmed/submitted
manager_action_at	DATETIME2	When manager last acted
manager_rejection_reason	NVARCHAR(MAX)	Reason for rejection
is_fit_sample_approved	BIT	FIT sample status
is_cfm_sample_approved	BIT	CFM sample status
container_number	NVARCHAR(100)	Shipping container number
tracking_number	NVARCHAR(200)	Shipment tracking number
last_modified_by	NVARCHAR(200)	Last user to edit

cin7_po_lines

Line item detail table.

Column	Type	Description
po_id	NVARCHAR	Foreign key to headers
code	NVARCHAR	Product SKU
style	NVARCHAR	Style number
option1	NVARCHAR	Color
size	NVARCHAR	Size (e.g., 6M, 7M, 8M)
qty	NVARCHAR	Original quantity (from Cin7)
unit_price	NVARCHAR	Original unit price (from Cin7)
vendor_reply_qty	INT	Vendor's proposed quantity
vendor_reply_unit_price	DECIMAL(18,4)	Vendor's proposed price
is_modified	BIT	Whether this line has vendor edits
row_hash	NVARCHAR(64)	Unique hash for API updates

Reference Tables

Ref_PO_Status

Column	Type
Status_ID	INT (PK)
Status_Name	NVARCHAR(50)
Status_Label_EN	NVARCHAR(100)
Status_Label_ZH	NVARCHAR(100)
Sort_Order	INT
Is_Terminal	BIT
Color_Hex	NVARCHAR(7)
Is_Active	BIT

Ref_PO_Transitions

Column	Type
Transition_ID	INT (PK)
From_Status_ID	INT (FK)
To_Status_ID	INT (FK)
Action_Name	NVARCHAR(50)
Allowed_Roles	NVARCHAR(200)
Label_EN	NVARCHAR(100)
Label_ZH	NVARCHAR(100)
Gatekeeper_Check	NVARCHAR(100)
Sort_Order	INT
Is_Active	BIT

Log_PO_History

Column	Type
Log_ID	INT (PK, Identity)
PO_Ref	NVARCHAR(50)
Old_Status_ID	INT
New_Status_ID	INT
Action_Name	NVARCHAR(50)
Changed_By	NVARCHAR(200)
Changed_At	DATETIME2
Comment	NVARCHAR(MAX)

Entity Relationship Diagram

erDiagram
    cin7_purchase_orders_headers ||--o{ cin7_po_lines : "has lines"
    cin7_purchase_orders_headers }o--|| Ref_PO_Status : "has status"
    cin7_purchase_orders_headers ||--o{ Log_PO_History : "has history"
    Ref_PO_Transitions }o--|| Ref_PO_Status : "from_status"
    Ref_PO_Transitions }o--|| Ref_PO_Status : "to_status"