Database Schema

Core Tables

Cin7_PO_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
status	NVARCHAR	Cin7 PO status (APPROVED, VOID). VOID rows are excluded from all application queries.
stage	NVARCHAR	Cin7 fulfillment stage (NULL, New, In Transit, Delivered, Received). Used for initial app_po_status_id mapping on sync.

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

App_Notifications

Tracks user read receipts for system notifications tied to PO history.

Column	Type	Description
Notification_ID	INT (PK, Identity)	Primary key
User_Email	NVARCHAR(255)	Email of the user receiving the notification
History_ID	INT (FK)	Foreign key to Log_PO_History
Is_Read	BIT	Whether the user has read the notification (default: 0)
Created_At	DATETIME2	Timestamp of notification creation

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)

Ref_Vendors

Reference data for vendors and factories.

Column	Type	Description
Vendor_ID	INT (PK)	Unique internal ID
Vendor_Code	NVARCHAR(50)	Short code (e.g., E1, F2)
Vendor_Name	NVARCHAR(200)	Full trading name
Contact_Name	NVARCHAR(200)	Primary contact person
Contact_Email	NVARCHAR(200)	Contact email (PII masked in some logs)
Is_Active	BIT	Active status

CloudMind Configuration & Chat

Config_Registry

Stores the source configurations for the CloudMind semantic layer.

Column	Type	Description
Source_ID	INT (PK, Identity)	Primary key
Source_Name	NVARCHAR(100)	Configuration name (e.g., cin7_po_headers)
Source_Type	NVARCHAR(50)	Type (table, view)
Config_YAML	NVARCHAR(MAX)	The full YAML configuration content
Is_Active	BIT	Whether this config is used by the system
Status	NVARCHAR(20)	Lifecycle status (draft, approved, deprecated)

Chat_Interactions

Logs all user interactions with the AI Data Assistant.

Column	Type	Description
id	INT (PK, Identity)	Primary key
user_email	NVARCHAR(255)	Email of the user
user_role	NVARCHAR(50)	Role at time of query
vendor_code	NVARCHAR(50)	Vendor code (if Vendor role)
user_message	NVARCHAR(MAX)	The user's query (PII masked)
ai_reply	NVARCHAR(MAX)	The AI's response (PII masked)
message_language	NVARCHAR(10)	Detected language (en, zh)
tools_called	NVARCHAR(MAX)	JSON array of tool names invoked
tool_params	NVARCHAR(MAX)	JSON object of parameters for the first tool
query_row_counts	NVARCHAR(MAX)	JSON mapping of results per tool
response_type	NVARCHAR(20)	Type of UI rendered (text, chart, table)
chart_type	NVARCHAR(20)	Type of chart (bar, line, donut)
guardrail_passed	BIT	Whether output validation passed
hallucination_detected	BIT	Whether hallucination was suspected
pii_detected	BIT	Whether PII was found and masked
duration_ms	INT	Total response time
openai_tokens_used	INT	Token usage for the interaction
auto_score	INT	Automated quality score (-3 to 8)
needs_improvement	BIT	Flag for manual review
is_good_example	BIT	Flag for few-shot learning pool
created_at	DATETIME2	Timestamp of interaction

Entity Relationship Diagram

erDiagram
    Cin7_PO_Headers ||--o{ Cin7_PO_Lines : "has lines"
    Cin7_PO_Headers }o--|| Ref_PO_Status : "has status"
    Cin7_PO_Headers }o--|| Ref_Vendors : "belongs to"
    Cin7_PO_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"
    Log_PO_History ||--o{ App_Notifications : "triggers"
    Chat_Interactions }o--o{ Config_Registry : "queries via"