03｜資料模型與儲存

1 實體關係

1-1 訂單與庫存

erDiagram
    owners ||--o{ owner_facilities : authorizes
    facilities ||--o{ owner_facilities : belongs_to
    owners ||--o{ products : owns
    products ||--o{ skus : composite_owner_product
    owner_facilities ||--o{ orders : composite_owner_facility
    orders ||--o{ order_lines : contains
    skus ||--o{ order_lines : composite_owner_sku
    facilities |o--o{ stock_locations : internal_location
    stock_locations ||--o{ stock_pools : internal_usage
    skus ||--o{ stock_pools : composite_owner_sku
    stock_operation_types ||--o{ stock_operations : classifies
    stock_operations |o--o{ stock_moves : groups
    stock_moves ||--o{ stock_move_lines : assigned_batches
    stock_pools ||--o{ stock_move_lines : references
    stock_operations ||--o{ stock_operation_cancellations : cancellation_history
    orders {
        uuid id PK
        uuid owner_id FK
        uuid facility_id FK
        varchar external_order_no
        varchar status
        bigint version
    }
    order_lines {
        uuid id PK
        uuid order_id FK
        uuid owner_id FK
        varchar sku_code FK
        integer quantity
    }
    stock_operations {
        uuid id PK
        uuid stock_operation_type_id FK
        varchar source_type
        varchar source_id
        varchar allocation_unit_key
        varchar state
        bigint version
    }
    stock_moves {
        uuid id PK
        uuid stock_operation_id FK
        varchar source_line_id
        integer demand_quantity
        varchar state
        bigint version
    }
    stock_move_lines {
        uuid id PK
        uuid move_id FK
        uuid stock_pool_id FK
        integer quantity
    }
    stock_pools {
        uuid id PK
        uuid owner_id FK
        uuid location_id FK
        varchar sku_code FK
        integer on_hand_quantity
        integer reserved_quantity
        bigint version
    }

載入圖表…

關聯讀法	意義
圖中實線	已存在 DB FK；此圖選取核心關聯，完整 FK 見資料字典各表約束
`stock_moves.stock_operation_id`	SQL 允許 NULL，圖中標 optional parent；不能因現行流程通常有值而改為 NOT NULL
跨 context correlation	`stock_operations.source_id`、`wms_shipments.order_id`、`wms_shipments.stock_operation_id`、`orders.fulfilled_by_shipment_id`、`cancellation_processes.order_id` 沒有相應跨 context DB FK；不能在 ERD 冒稱有實體 FK
批次唯一身分	owner + location + SKU + in_date + expiry_date；相同 SKU 在不同貨主或庫位完全分開
可承諾量	Domain／API 的 `availableToPromise` 為 `on_hand_quantity - reserved_quantity`，過期批也保留此差額；配貨 SQL 另以 `expiry_date >= today` 排除過期批。讀取端須同看 `expired`，不存第三個可變餘額

1-2 倉內作業

erDiagram
    wms_shipments ||--o{ wms_shipment_lines : lines
    wms_shipments ||--o| wms_wave_assignments : assignment
    wms_waves ||--o{ wms_wave_assignments : groups
    wms_shipments ||--o| wms_picking_works : work
    wms_waves ||--o{ wms_picking_works : release
    wms_picking_works ||--o{ wms_pick_tasks : tasks
    wms_shipments ||--o| wms_dispatches : dispatch
    wms_inbound_operations ||--o{ wms_inbound_expected_lines : expected
    wms_shipments {
        uuid id PK
        uuid order_id
        uuid stock_operation_id UK
        varchar status
        varchar cancellation_state
        bigint version
    }
    wms_wave_assignments {
        uuid shipment_id PK,FK
        uuid wave_id FK
    }
    wms_pick_tasks {
        uuid id PK
        uuid work_id FK
        uuid move_id UK
        integer requested_quantity
        integer picked_quantity
    }

載入圖表…

WMS 資料責任	說明
實體外鍵	assignments、works、tasks、dispatches 有 WMS 內部 FK；`wms_shipments.wave_id` 本身未定義 FK
WMS 與 Inventory	Shipment 保存 move／order line correlation，實際庫存餘額由 Inventory 管理
Inbound 模型	有 WMS inbound 與 expected lines 資料模型；`POST /stock-receipts` 的同步 Inventory 收貨流程不等同完整 WMS inbound 作業介面

2 資料責任與命名對照

資料群	資料維護責任	跨系統來源
owners、products、skus、facilities、owner_facilities	Logistics Data 主檔；現有讀 API，seed／fixture 初始化	貨主與商品識別資料，不包含本次未實作的 ERP 同步
stock_locations、stock_operation_types	Inventory 庫位與作業配置	facility 身分引用主檔
orders、order_lines	Ordering	externalOrderNo、shipTo、promisedDeliveryDate、dispatchBy、releasePriority、placedAt 由上游提供；receivedAt 由系統產生
stock_pools、stock_operations、stock_moves、stock_move_lines	Inventory	訂單需求由 `stock_order_movement_sources` view 讀取；收貨由 receipt command 提供
owner_allocation_policies	Inventory 配貨設定	缺省使用 `DISPATCH_DATE_FIRST`
stock_receipt_requests、stock_operation_cancellations	Inventory 冪等與取消協調紀錄	receiptId／取消 correlation 由呼叫端或流程傳入
wms_*	WMS 倉內執行	已配貨事實建立 Shipment；simulation driver 現行自動推進
cancellation_processes	Fulfillment（Events 模式）	外部不可變 request、WMS 與 Ordering outcome
event_outbox、event_inbox	共用 messaging infrastructure	publication／subscriber 消費事實；不是另一份可人工編輯的業務狀態

3 View、跨表規則與狀態防呆

flowchart LR
    O[orders + order_lines] --> V[stock_order_movement_sources]
    T[stock_operation_types OUTBOUND] --> V
    V --> A[Inventory 註冊需求]
    Write[operation／moves／move_lines／pools 寫入] --> Commit[COMMIT 時 deferred constraint triggers]
    Commit -->|一致| OK[提交]
    Commit -->|不一致| Rollback[整筆回滾]

載入圖表…

View 的逐欄型別與來源見資料字典：stock_order_movement_sources。

stock_order_movement_sources 是普通 view，僅排除 orders.cancelled_at IS NOT NULL 的訂單，不是另一個儲存的需求狀態，也沒有自帶 PK／索引。

防呆條件	實作位置	失敗處置
operation 至少一個 move，所有 move state 與 operation 一致	作業一致性延遲約束	deferred constraint trigger 在交易結束時拋 `check_violation`
CONFIRMED／CANCELLED 不可有 move lines；ASSIGNED／DONE 的 line 總量等於 demand	同上	整筆交易回滾
pool.reserved = 引用該批的 ASSIGNED move lines 數量加總	預留守恆延遲約束	整筆回滾，不能只改 pool 數字
reserved 介於 0 與 on-hand	庫存批次 CHECK 與餘額規則	DB CHECK 或領域檢核拒絕
取消／履約時間與 correlation 合法	訂單狀態規則與部分資料庫關聯 CHECK	Domain 拒絕；不可假設 DB 已限制 orders.status 全部列舉
WMS state 與取消 metadata 一致	資料庫 CHECK 與出貨單狀態規則	無效 status／timestamps 回滾
SQL nullable 與 CHECK	PostgreSQL CHECK 允許 UNKNOWN	字典保持實際 NULL 宣告；有語意限制的欄位不因此變成 NOT NULL

4 既有索引與查詢對齊

下表列出資料庫顯式索引；主鍵（PK）與唯一約束（UNIQUE）也會建立索引。

索引定義	所屬資料表
`CREATE INDEX idx_skus_product ON skus (owner_id, product_code);`	`skus`
`CREATE INDEX idx_orders_recent ON orders (received_at DESC, id DESC);`	`orders`
`CREATE UNIQUE INDEX uq_orders_cancellation_request ON orders (cancellation_request_id) WHERE (cancellation_request_id IS NOT NULL);`	`orders`
`CREATE UNIQUE INDEX uq_orders_fulfilled_shipment ON orders (fulfilled_by_shipment_id) WHERE (fulfilled_by_shipment_id IS NOT NULL);`	`orders`
`CREATE INDEX idx_order_lines_demand_fifo ON order_lines (owner_id, sku_code, order_id);`	`order_lines`
`CREATE INDEX idx_stock_pools_fefo ON stock_pools (owner_id, location_id, sku_code, expiry_date, in_date, id);`	`stock_pools`
`CREATE INDEX idx_stock_operations_confirmed_scope_queue ON stock_operations (owner_id, from_location_id, enqueued_at, id) WHERE (((state)::TEXT = 'CONFIRMED'::TEXT) AND ((direction)::TEXT = 'OUTBOUND'::TEXT));`	`stock_operations`
`CREATE INDEX idx_stock_operations_dispatch_sequence ON stock_operations (owner_id, from_location_id, dispatch_by, enqueued_at, id) WHERE (((state)::TEXT = 'CONFIRMED'::TEXT) AND ((direction)::TEXT = 'OUTBOUND'::TEXT));`	`stock_operations`
`CREATE UNIQUE INDEX uq_stock_operations_source_unit ON stock_operations (source_type, source_id, allocation_unit_key) WHERE (source_type IS NOT NULL);`	`stock_operations`
`CREATE INDEX idx_stock_moves_confirmed_operation_sku ON stock_moves (stock_operation_id, sku_code) WHERE ((state)::TEXT = 'CONFIRMED'::TEXT);`	`stock_moves`
`CREATE INDEX idx_stock_moves_confirmed_scope_sku ON stock_moves (owner_id, from_location_id, sku_code, stock_operation_id) WHERE ((state)::TEXT = 'CONFIRMED'::TEXT);`	`stock_moves`
`CREATE INDEX idx_stock_moves_operation_ordered ON stock_moves (stock_operation_id, line_sequence, id);`	`stock_moves`
`CREATE UNIQUE INDEX uq_stock_moves_operation_line_sequence ON stock_moves (stock_operation_id, line_sequence) WHERE (line_sequence IS NOT NULL);`	`stock_moves`
`CREATE UNIQUE INDEX uq_stock_moves_operation_source_line ON stock_moves (stock_operation_id, source_line_id) WHERE (source_line_id IS NOT NULL);`	`stock_moves`
`CREATE INDEX idx_stock_move_lines_stock_pool ON stock_move_lines (stock_pool_id);`	`stock_move_lines`
`CREATE INDEX idx_event_inbox_event_id ON event_inbox (event_id);`	`event_inbox`
`CREATE INDEX idx_event_outbox_timestamp ON event_outbox ("timestamp");`	`event_outbox`
`CREATE INDEX idx_wms_shipments_due_simulation ON wms_shipments (created_at, id) WHERE ((status)::TEXT = 'CREATED'::TEXT);`	`wms_shipments`
`CREATE INDEX idx_wms_shipments_order ON wms_shipments (order_id, created_at, id);`	`wms_shipments`
`CREATE INDEX idx_wms_shipments_wave_candidates ON wms_shipments (facility_id, release_priority DESC, dispatch_by, created_at, id) WHERE ((status)::TEXT = 'CREATED'::TEXT);`	`wms_shipments`
`CREATE INDEX idx_wms_shipment_lines_shipment ON wms_shipment_lines (shipment_id, order_line_id);`	`wms_shipment_lines`
`CREATE INDEX idx_wms_wave_assignments_wave ON wms_wave_assignments (wave_id, shipment_id);`	`wms_wave_assignments`
`CREATE INDEX idx_wms_pick_tasks_work ON wms_pick_tasks (work_id, id);`	`wms_pick_tasks`
`CREATE INDEX idx_wms_inbound_expected_lines_operation ON wms_inbound_expected_lines (inbound_operation_id, sku_code, id);`	`wms_inbound_expected_lines`
`CREATE UNIQUE INDEX ux_cancellation_process_active_order ON cancellation_processes (order_id) WHERE state IN ('WAITING_WMS', 'WAITING_ORDERING');`	`cancellation_processes`

高頻查詢／用途	已有支援與分析	規劃狀態
owner + location + sku 的有效批 FEFO	`idx_stock_pools_fefo` 支援 scope 與 expiry_date／in_date／id；供給 SQL 另濾 `expiry_date >= today` 與可用數量	已存在
待配貨 FIFO／dispatch deadline queue	`idx_stock_operations_confirmed_scope_queue` 與 `idx_stock_operations_dispatch_sequence`；partial predicate 限 CONFIRMED OUTBOUND	已存在
候選單是否包含 SKU、同 operation 行序	`idx_stock_moves_confirmed_scope_sku`、`idx_stock_moves_operation_ordered`	已存在
WMS 波次候選	`idx_wms_shipments_wave_candidates` 的 facility、priority DESC、dispatch_by、created_at、id	已存在
最近訂單的 owner 篩選	現有 `idx_orders_recent(received_at DESC,id DESC)` 未以 owner 開頭	建議先 EXPLAIN ANALYZE；必要時新增 `(owner_id, received_at DESC, id DESC)`，尚未實作
Inbox 保留清理	現有 PK 與 event_id index，沒有 processed_at index	若啟用大量時間窗清理，再評估 `(processed_at)`；不是現有自動清理功能

5 並行寫入與隔離

flowchart LR
    Read[讀候選與供給快照] --> Plan[完整配置計畫]
    Plan --> Tx[交易內再驗證與寫入]
    Tx --> Version[operation／move／quant version]
    Tx --> Locks[必要路徑以固定順序取得悲觀寫鎖]
    Version --> Guard[DB Unique／Check／Deferred Trigger]
    Locks --> Guard
    Guard -->|成功| Commit[業務 + Outbox 同交易]
    Guard -->|衝突| Retry[回滾；依呼叫路徑重試或回報]

載入圖表…

機制	控制方式	目的／界線
樂觀鎖	訂單、庫存作業、庫存移動、庫存批次及倉內資料使用 `version` 欄位	偵測 stale update；SQL DEFAULT 0 不等於所有寫入都能跳過版本檢查
operation／move 悲觀鎖	依作業識別碼鎖定資料；明細按識別碼排序取得寫鎖	取消、釋放與完成等需序列化的路徑；不是所有 SELECT 都 FOR UPDATE
批次全域寫入順序	按 SKU、效期、入庫日及批次識別碼排序取得寫鎖	使用 `PESSIMISTIC_WRITE`；降低多批次鎖競爭／死鎖風險
取消 process	`SELECT ... FOR UPDATE` 搭配條件更新；每次須恰好更新一筆	防 WMS／Ordering 重播與矛盾結果覆寫
唯一冪等 claim	receipt_id PK、process request_id PK、Inbox composite PK	與業務 transaction 一起提交；失敗不保留半筆 claim
事件 retry	本地 optimistic + Kafka 分類重投；詳見整合與交易時序	不能推論一般 HTTP request 也自動套用同一 retry policy