Skip to Content
實戰案例WMS：倉儲與庫存

WMS：倉儲、流水與即時計算庫存

情境

ACME 的物流主管要讓台北、高雄與台中的倉庫共用同一份商品目錄，同時保留進貨、調撥、出貨的完整流水。倉管只輸入事實：哪個 SKU 在何時移動了多少；庫存價值、低庫存差額與已過帳流水的回放則由連結、rollup、lookup 與 formula 即時計算；現有量本身是儲存的值，由 move_stock command 負責扣減與增加。聊天室裡的倉儲助理可以回答營運問題，也能調整倉庫負責的商品（聊天室開啟 requires_confirmation_for_ct_action 時要等使用者確認；預設值則直接套用）。

資料模型

  • Products：SKU、Name、Category、Unit Price
  • Warehouses：Name、City、Capacity、Responsible Products → Products (many)，以及 Responsible Count（count）與 Catalog Value（單價加總）rollup
  • Movements：Movement No、Type、Status、Quantity、Moved At、Moved By、Note，連到 Product 與 From/To Warehouse
  • Stock：Warehouse + Product junction，有儲存的 On Hand 與 Reorder Point、Unit Price lookup、Stock Value 與 Low Stock Gap formula，以及兩個 filtered incoming rollup（Received Qty、Shipped Qty）回放已過帳的流水
Product ◀── Movement ── From / To ─▶ Warehouse
   ▲                                     │
   └──── Stock (Warehouse × Product) ────┘
          rollups → lookup → formulas

Note filtered sum 沒有符合資料列時是 null，不是 0。因此 Received Qty 與 Shipped Qty 在沒有任何已過帳流水符合之前都是 null；前端應呈現「無法判定」，不要顯示成零。

完整的 IaC 文件

下面這份文件會把整座倉儲建起來：依相依順序宣告四張資料表、算出價值並回放流水的 lookup 與 rollup、讓每個庫存格保持誠實的規則、把一次調撥收斂成單一交易的 move_stock command、揀貨佇列與補貨觀察清單，以及把倉管與庫存主管分開的 grant。有一個數字刻意用「儲存」而非「推導」：stock.on_hand 是 command 借貸的交易餘額，而 received_qty 與 shipped_qty 是推導出來的 rollup，獨立回放已過帳的流水——兩者對不起來就是真正的對帳訊號，不是四捨五入的雜訊。套用前請先把 $dept:Warehouse Floor 與 $user:inventory.manager 換成你自己的部門與使用者（使用者請選一般成員：聊天室建立者、聊天室管理者或資料表 moderator 本來就有完整權限，對他們寫 grant 會被拒絕，訊息為 Cannot set explicit permissions for table managers），並把 trigger 裡的 chatroom_id 換成該收到缺貨通知的聊天室。

{"kind":"header","version":1,"system":"acme-wms","description":"ACME 倉儲系統"}
{"kind":"table","ref":"products","spec":{"name":"商品","description":"共用目錄中每個 SKU 一列","key":"sku"}}
{"kind":"column","table":"products","ref":"sku","spec":{"name":"SKU","type":"string","required":true,"max_length":32}}
{"kind":"column","table":"products","ref":"name","spec":{"name":"品名","type":"string","required":true}}
{"kind":"column","table":"products","ref":"category","spec":{"name":"分類","type":"select","options":["Electronics","Furniture","Consumables"]}}
{"kind":"column","table":"products","ref":"unit_price","spec":{"name":"單價","type":"float"}}
{"kind":"table","ref":"warehouses","spec":{"name":"倉庫","description":"實際存放庫存的地點","key":"name"}}
{"kind":"column","table":"warehouses","ref":"name","spec":{"name":"名稱","type":"string","required":true,"max_length":64}}
{"kind":"column","table":"warehouses","ref":"city","spec":{"name":"城市","type":"string"}}
{"kind":"column","table":"warehouses","ref":"capacity","spec":{"name":"容量","type":"integer"}}
{"kind":"column","table":"warehouses","ref":"responsible_products","spec":{"name":"負責商品","type":"link","target":"products","cardinality":"many"}}
{"kind":"column","table":"warehouses","ref":"responsible_count","spec":{"name":"負責商品數","type":"rollup","link_field":"responsible_products","aggregation":"count"}}
{"kind":"column","table":"warehouses","ref":"catalog_value","spec":{"name":"目錄總價","type":"rollup","link_field":"responsible_products","aggregation":"sum","target_column":"unit_price"}}
{"kind":"table","ref":"stock","spec":{"name":"庫存","description":"每個倉庫與商品的組合一列","key":"stock_key"}}
{"kind":"column","table":"stock","ref":"stock_key","spec":{"name":"庫存鍵","type":"string","required":true,"max_length":64,"description":"單一庫存格的可讀代號，例如 TPE/SKU-001"}}
{"kind":"column","table":"stock","ref":"warehouse","spec":{"name":"倉庫","type":"link","target":"warehouses","cardinality":"one"}}
{"kind":"column","table":"stock","ref":"product","spec":{"name":"商品","type":"link","target":"products","cardinality":"one"}}
{"kind":"column","table":"stock","ref":"on_hand","spec":{"name":"現有量","type":"integer","required":true,"default_value":0}}
{"kind":"column","table":"stock","ref":"reorder_point","spec":{"name":"補貨點","type":"integer","default_value":0}}
{"kind":"column","table":"stock","ref":"unit_price","spec":{"name":"單價","type":"lookup","link_field":"product","target_column":"unit_price"}}
{"kind":"column","table":"stock","ref":"stock_value","spec":{"name":"庫存價值","type":"formula","expression":"[on_hand] * [unit_price]"}}
{"kind":"column","table":"stock","ref":"low_stock_gap","spec":{"name":"低庫存差額","type":"formula","expression":"[on_hand] - [reorder_point]"}}
{"kind":"table","ref":"movements","spec":{"name":"流水","description":"進貨、調撥、出貨的唯附加流水帳","key":"movement_no"}}
{"kind":"column","table":"movements","ref":"movement_no","spec":{"name":"流水編號","type":"string","required":true,"max_length":32}}
{"kind":"column","table":"movements","ref":"type","spec":{"name":"類型","type":"select","options":["import","transfer","export"],"default_value":"transfer"}}
{"kind":"column","table":"movements","ref":"status","spec":{"name":"狀態","type":"select","options":["queued","picked","posted"],"default_value":"queued"}}
{"kind":"column","table":"movements","ref":"quantity","spec":{"name":"數量","type":"integer","required":true}}
{"kind":"column","table":"movements","ref":"moved_at","spec":{"name":"異動時間","type":"datetime"}}
{"kind":"column","table":"movements","ref":"product","spec":{"name":"商品","type":"link","target":"products","cardinality":"one"}}
{"kind":"column","table":"movements","ref":"from_warehouse","spec":{"name":"來源倉庫","type":"link","target":"warehouses","cardinality":"one"}}
{"kind":"column","table":"movements","ref":"to_warehouse","spec":{"name":"目的倉庫","type":"link","target":"warehouses","cardinality":"one"}}
{"kind":"column","table":"movements","ref":"moved_by","spec":{"name":"操作人","type":"user"}}
{"kind":"column","table":"movements","ref":"note","spec":{"name":"備註","type":"text"}}
{"kind":"column","table":"stock","ref":"received_qty","spec":{"name":"入庫累計","type":"rollup","direction":"incoming","source":"movements","match":{"product":"product","to_warehouse":"warehouse"},"aggregation":"sum","target_column":"quantity","filter":[{"column":"status","op":"eq","value":"posted"}]}}
{"kind":"column","table":"stock","ref":"shipped_qty","spec":{"name":"出庫累計","type":"rollup","direction":"incoming","source":"movements","match":{"product":"product","from_warehouse":"warehouse"},"aggregation":"sum","target_column":"quantity","filter":[{"column":"status","op":"eq","value":"posted"}]}}
{"kind":"rule","table":"stock","ref":"one_cell_per_pair","spec":{"type":"unique","name":"同一倉庫與商品只有一列庫存","columns":["warehouse","product"],"case_insensitive":false}}
{"kind":"rule","table":"stock","ref":"never_negative","spec":{"type":"invariant","name":"現有量不得為負","policy":{"column":"on_hand","op":"gte","value":0},"enforcement":"enforce"}}
{"kind":"rule","table":"movements","ref":"positive_quantity","spec":{"type":"check","name":"數量必須為正","column":"quantity","op":"gt","value":0}}
{"kind":"command","ref":"move_stock","spec":{"name":"調撥庫存","description":"在同一筆交易內扣減來源庫存格、增加目的庫存格，並過帳流水資料列。","definition":{"version":2,"inputs":[{"name":"movement_no","type":"string","required":true,"max_length":32},{"name":"from_stock_id","type":"string","required":true,"max_length":36},{"name":"to_stock_id","type":"string","required":true,"max_length":36},{"name":"product_id","type":"string","required":true,"max_length":36},{"name":"from_warehouse_id","type":"string","required":true,"max_length":36},{"name":"to_warehouse_id","type":"string","required":true,"max_length":36},{"name":"quantity","type":"integer","required":true},{"name":"moved_at","type":"datetime","required":true}],"steps":[{"kind":"select","name":"source","from":{"table":"stock","as":"s"},"select":{"on_hand":"$row.s.on_hand"},"where":{"$eq":[{"$metadata":{"source":"s","name":"id"}},"$input.from_stock_id"]},"max_rows":1},{"kind":"assert","name":"enough_stock","condition":{"$gte":[{"$single":{"relation":"$rel.source","expr":"$item.on_hand"}},"$input.quantity"]},"message":"來源庫存格的數量不足以完成這次調撥。"},{"kind":"update","name":"debit","table":"stock","match":{"id":"$input.from_stock_id"},"set":{"on_hand":{"$sub":["$row.current.on_hand","$input.quantity"]}},"max_rows":1,"returning":{"remaining":"$row.new.on_hand"}},{"kind":"update","name":"credit","table":"stock","match":{"id":"$input.to_stock_id"},"set":{"on_hand":{"$add":["$row.current.on_hand","$input.quantity"]}},"max_rows":1},{"kind":"insert","name":"ledger","table":"movements","values":{"movement_no":"$input.movement_no","type":"transfer","status":"posted","quantity":"$input.quantity","moved_at":"$input.moved_at","product":"$input.product_id","from_warehouse":"$input.from_warehouse_id","to_warehouse":"$input.to_warehouse_id","moved_by":"$ctx.user_id"},"returning":{"movement_id":{"$metadata":{"source":"new","name":"id"}}}}],"outputs":{"movement_id":{"value":{"$single":{"relation":"$rel.ledger","expr":"$item.movement_id"}}},"remaining":{"value":{"$single":{"relation":"$rel.debit","expr":"$item.remaining"}}}}}}}
{"kind":"view","table":"movements","ref":"picking_queue","spec":{"name":"揀貨佇列","is_shared":true,"config":{"filters":{"status":"queued"},"sort_by":"moved_at","sort_order":"asc","columns":["movement_no","type","quantity","product","from_warehouse","to_warehouse","moved_at"]}}}
{"kind":"view","table":"stock","ref":"reorder_watchlist","spec":{"name":"補貨觀察清單","is_shared":true,"config":{"sort_by":"on_hand","sort_order":"asc","columns":["stock_key","warehouse","product","on_hand","reorder_point","low_stock_gap","stock_value"]}}}
{"kind":"trigger","table":"stock","ref":"depleted_alert","spec":{"name":"庫存歸零時通知","on":"updated","transition":{"column":"on_hand","to":0},"actions":[{"type":"notify","chatroom_id":"11111111-1111-4111-8111-111111111111","message":"庫存格 $row.stock_key 已經歸零。"}]}}
{"kind":"grant","table":"movements","principal":{"type":"department","id":"$dept:Warehouse Floor"},"spec":{"can_read":"all","can_insert":true,"can_edit":"filtered","edit_filter":{"and":[{"column":"status","op":"eq","value":"queued"}]},"visible_columns":["movement_no","type","status","quantity","moved_at","product","from_warehouse","to_warehouse","note"]}}
{"kind":"grant","table":"stock","principal":{"type":"department","id":"$dept:Warehouse Floor"},"spec":{"can_read":"all","can_insert":false,"can_edit":"none","visible_columns":["stock_key","warehouse","product","on_hand","reorder_point","low_stock_gap"]}}
{"kind":"grant","table":"products","principal":{"type":"department","id":"$dept:Warehouse Floor"},"spec":{"can_read":"all","can_insert":false,"can_edit":"none"}}
{"kind":"grant","table":"warehouses","principal":{"type":"user","id":"$user:inventory.manager"},"spec":{"can_read":"all","can_insert":true,"can_edit":"all"}}
{"kind":"grant","table":"stock","principal":{"type":"user","id":"$user:inventory.manager"},"spec":{"can_read":"all","can_insert":true,"can_edit":"all"}}
{"kind":"grant","table":"movements","principal":{"type":"user","id":"$user:inventory.manager"},"spec":{"can_read":"all","can_insert":true,"can_edit":"all"}}

真正承載倉儲邏輯的幾行

  • stock.one_cell_per_pair——對兩個 cardinality one 的 link 下 unique 規則，那才是庫存格真正的自然鍵；兩個「建立這一格」的請求同時進來時，由資料庫判輸贏，而不是無聲地分裂出兩份餘額。
  • stock.never_negative——處於 enforce 的 invariant：不論誰寫、走哪條通道，任何寫入的 post-image 都必須滿足 on_hand >= 0；這正是讓 command 的失敗模式成為規則、而不是祈禱的關鍵。
  • command:move_stock——以 version 2 definition 寫在 command line 上的原子化調撥：讀出來源庫存格、確認數量足夠、扣減來源、增加目的、再附加流水資料列；任何一步失敗就整筆回滾。command 以呼叫者自己的權限執行（stock 的編輯權與 movements 的新增權），所以在這些 grant 下只有庫存主管或資料表管理者跑得動；$dept:Warehouse Floor 對 stock 沒有編輯權，無法執行它。
  • move_stock 的 enough_stock 步驟——assert 會回傳 422 assertion_failed 並帶上你自己寫的訊息，呼叫端因此知道調撥「為什麼」被拒絕，而不是去讀一則約束違反。
  • stock.received_qty / stock.shipped_qty——兩個 filtered incoming rollup 以已過帳的流水回放儲存的餘額；它們是 on_hand 的稽核，不是它的來源。
  • movements.positive_quantity——check 規則（quantity > 0）。require 規則無法指向 link 欄位，所以「調撥兩個倉庫都要有」是由 move_stock command 必填的 from_warehouse_id 與 to_warehouse_id input 把關，而不是規則。
  • movements.picking_queue——倉管實際會打開的共用 view，只看還在 queued 的資料列並以最舊優先排序。
  • $dept:Warehouse Floor 在 movements 上的 grant——can_edit: "filtered" 搭配對 status 的 edit_filter，倉管可以修正待揀的流水，卻永遠改不動已過帳的那些。由於這個 filter 也會判斷寫入後的資料列，倉管無法把 status 從 queued 改走；要把揀貨推進到 picked 或 posted，需要主管的 grant。
  • stock.depleted_alert——用的是 transition trigger 而不是 when 條件：on_hand <= 0 會在已經歸零的庫存格每次被更新時都再發一次（例如改 reorder_point），而 transition: {"column":"on_hand","to":0} 只在把它變成零的那一次更新才發。

產品流程

1. 先規劃，再套用整套倉儲模型

把 header、table、column 逐行組成 JSONL。以下是同一份文件中的核心片段；ref 讓 link 與 rollup 不必預先知道 UUID：

{"kind":"header","version":1,"system":"acme-wms","description":"ACME 倉儲系統"}
{"kind":"table","ref":"products","spec":{"name":"商品","key":"sku"}}
{"kind":"column","table":"products","ref":"sku","spec":{"name":"SKU","type":"string","required":true}}
{"kind":"column","table":"products","ref":"unit_price","spec":{"name":"單價","type":"float"}}
{"kind":"table","ref":"warehouses","spec":{"name":"倉庫","key":"name"}}
{"kind":"column","table":"warehouses","ref":"name","spec":{"name":"名稱","type":"string","required":true}}
{"kind":"column","table":"warehouses","ref":"responsible_products","spec":{"name":"負責商品","type":"link","target":"products","cardinality":"many"}}
{"kind":"column","table":"warehouses","ref":"catalog_value","spec":{"name":"目錄總價","type":"rollup","link_field":"responsible_products","aggregation":"sum","target_column":"unit_price"}}

以 Content-Type: application/x-ndjson 將完整文件送給 iac.plan，檢查 actions 後，用原封不動的文件與 plan hash 呼叫 iac.apply：

POST /private/module/custom_tables/chatroom/11111111-1111-4111-8111-111111111111/tables/iac/plan
POST /private/module/custom_tables/chatroom/11111111-1111-4111-8111-111111111111/tables/iac/apply?plan_hash=9a1b2c3d4e5f60718293a4b5c6d7e8f90123456789abcdef0123456789abcdef

2. 批次寫入商品，再建立連結

完整文件裡的資料表與欄位顯示名稱是中文（例如 Products 叫「商品」、Unit Price 叫「單價」），下面的 REST 範例就使用這些名稱。商品適合用同步 bulk.mixed 一次寫入；回讀資料列取得商品 UUID 後，再把 UUID 陣列寫進倉庫的 many-link：

POST /private/module/custom_tables/chatroom/11111111-1111-4111-8111-111111111111/tables/22222222-2222-4222-8222-222222222222/records/bulk
 
{
  "actions": [
    { "action": "insert", "data": { "SKU": "SKU-001", "品名": "Industrial Sensor", "分類": "Electronics", "單價": 320.0 } },
    { "action": "insert", "data": { "SKU": "SKU-007", "品名": "Bookshelf", "分類": "Furniture", "單價": 45.0 } }
  ]
}
POST /private/module/custom_tables/chatroom/11111111-1111-4111-8111-111111111111/tables/33333333-3333-4333-8333-333333333333/records
 
{
  "data": {
    "名稱": "Taipei Hub",
    "城市": "Taipei",
    "容量": 5000,
    "負責商品": [
      "44444444-4444-4444-8444-444444444444",
      "55555555-5555-4555-8555-555555555555"
    ]
  },
  "created_by_ai": false
}

3. 只寫流水，讓 Stock 自動更新

進貨資料列要帶必填的 Movement No（「流水編號」），並把 Product（「商品」）與 To Warehouse（「目的倉庫」）寫成單元素 ID 陣列。它以預設的 queued 建立，所以要等到被過帳才會計入 Received Qty——由主管過帳，或由 move_stock command 過帳（它自己寫的流水列一開始就是 posted）。這是 records.create 的真實 link wire shape：

{
  "data": {
    "流水編號": "MV-0001",
    "類型": "import",
    "數量": 50,
    "異動時間": "2026-06-01 09:00",
    "備註": "首批進貨",
    "商品": ["44444444-4444-4444-8444-444444444444"],
    "目的倉庫": ["77777777-7777-4777-8777-777777777777"]
  },
  "created_by_ai": false
}

接著以 records.list 讀取 Stock；Products 的反向來源則用 records.incomingLinks：

GET /private/module/custom_tables/chatroom/11111111-1111-4111-8111-111111111111/tables/88888888-8888-4888-8888-888888888888/records?limit=100&expand_links=true
GET /private/module/custom_tables/chatroom/11111111-1111-4111-8111-111111111111/tables/22222222-2222-4222-8222-222222222222/records/44444444-4444-4444-8444-444444444444/incoming-links

4. 把倉儲不變量放進規則

物流主管以 rules.set 一次設定完整規則集合。正數量、出貨需有客戶編號，以及出貨日不得晚於到貨日，都是資料寫入當下就應成立的業務條件：

這組規則針對擴充後的流水表，額外包含 Ship Date、Arrive Date 與 Consumer Ref 三個欄位；規則裡的欄位名稱必須是該表實際的顯示名稱（這裡的 JSON 已改用中文版文件中的「數量」與「類型」）。

{
  "rules": [
    { "type": "check", "name": "positive quantity", "column": "數量", "op": "gt", "value": 0 },
    { "type": "compare", "name": "ship before arrival", "left": "Ship Date", "op": "lte", "right": "Arrive Date" },
    {
      "type": "require",
      "name": "export needs consumer",
      "column": "Consumer Ref",
      "when": [{ "column": "類型", "op": "eq", "value": "export" }]
    }
  ]
}

使用者會看到什麼

move_stock command 過帳一筆流水後，對應 Stock 資料列的 Received Qty 與 Shipped Qty 會把它算進去，Stock Value 也跟著新的 On Hand 更新；直接寫入的流水維持 queued，過帳之前不會被計入。物流主管在聊天室問「哪座倉庫負責的商品目錄總價最高？」，助理會讀 Catalog Value——連上步驟 2 的兩項商品後，Taipei Hub 顯示 365.0；若要求再把另一項商品加入該倉庫，助理會更新 link（聊天室開啟 requires_confirmation_for_ct_action 時要先確認），Responsible Count 與 Catalog Value 會同步變動。

變化與下一步

試試看

把 JSONL 貼進 IaC 工作台先看 plan diff；建立兩張最小資料表時，也可以直接執行連結與 rollup 流程精靈。

Last updated on
HR：人力與薪酬PMS：專案與工時