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

情境

ACME 的物流主管要讓台北、高雄與台中的倉庫共用同一份商品目錄，同時保留進貨、調撥、出貨的完整流水。倉管只輸入事實：哪個 SKU 在何時移動了多少；庫存量、低庫存旗標與庫存價值則由連結、rollup、lookup 與 formula 即時計算。聊天室裡的倉儲助理可以回答營運問題，也能在使用者確認後調整倉庫負責的商品。

資料模型

Products：SKU、Name、Category、Unit Price
Suppliers：基本資料 + Supplied Products → Products (many) + Supplied Count
Consumers：下游客戶名稱與 segment
Warehouses：Name、City、Capacity、Responsible Products → Products (many)，以及 count / sum / avg / max 價格 rollup
Transfers：Type、Quantity、Date、Note，並分別連到 Product、Supplier、From/To Warehouse、Consumer
Stock：Warehouse + Product junction；四個 filtered incoming rollup 算進貨、調入、調出、出貨，lookup 帶入單價，再算 Stock、Low Stock、Stock Value


Supplier ─┐                         ┌─▶ Consumer
          ▼                         │
Product ◀── Transfer ── From / To ─▶ Warehouse
   ▲                                  │
   └──── Stock (Warehouse × Product) ─┘
          rollups → lookup → formulas

Note filtered sum 沒有符合資料列時是 null，不是 0。因此某個庫存格只要缺少一段流水，Stock 公式也會保持 null；前端應呈現「無法判定」，不要顯示成零庫存。

完整的 IaC 文件

下面這份文件會把整座倉儲建起來：依相依順序宣告四張資料表、算出價值並回放流水的 lookup 與 rollup、讓每個庫存格保持誠實的規則、把一次調撥收斂成單一交易的 move_stock command、揀貨佇列與補貨觀察清單，以及把倉管與庫存主管分開的 grant。有兩件事刻意用「儲存」而非「推導」：stock.on_hand 是 command 借貸的交易餘額，而 received_qty 與 shipped_qty 則獨立回放已過帳的流水——兩者對不起來就是真正的對帳訊號，不是四捨五入的雜訊。套用前請先把 $dept:Warehouse Floor 與 $user:inventory.manager 換成你自己的部門與使用者，並把 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":"rule","table":"movements","ref":"source_required","spec":{"type":"require","name":"調撥或出貨必須有來源倉庫","column":"from_warehouse","when":[{"column":"type","op":"in","value":["transfer","export"]}]}}
{"kind":"rule","table":"movements","ref":"destination_required","spec":{"type":"require","name":"進貨或調撥必須有目的倉庫","column":"to_warehouse","when":[{"column":"type","op":"in","value":["import","transfer"]}]}}
{"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","when":[{"column":"on_hand","op":"lte","value":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 上的原子化調撥：讀出來源庫存格、確認數量足夠、扣減來源、增加目的、再附加流水資料列；任何一步失敗就整筆回滾。
move_stock 的 enough_stock 步驟——assert 會回傳 422 assertion_failed 並帶上你自己寫的訊息，呼叫端因此知道調撥「為什麼」被拒絕，而不是去讀一則約束違反。
stock.received_qty / stock.shipped_qty——兩個 filtered incoming rollup 以已過帳的流水回放儲存的餘額；它們是 on_hand 的稽核，不是它的來源。
movements.source_required / movements.destination_required——以 type 作為 when 條件的 require 規則，讓出貨必須有來源、進貨必須有目的，而不必強迫每一列都兩者俱全。
movements.picking_queue——倉管實際會打開的共用 view，只看還在 queued 的資料列並以最舊優先排序。
$dept:Warehouse Floor 在 movements 上的 grant——can_edit: "filtered" 搭配對 status 的 edit_filter，倉管可以修正待揀的流水，卻永遠改不動已過帳的那些。

產品流程

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

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


{"kind":"header","version":1,"system":"acme-wms","description":"ACME warehouse system"}
{"kind":"table","ref":"products","spec":{"name":"Products","key":"sku"}}
{"kind":"column","table":"products","ref":"sku","spec":{"name":"SKU","type":"string","required":true}}
{"kind":"column","table":"products","ref":"unit_price","spec":{"name":"Unit Price","type":"float"}}
{"kind":"table","ref":"warehouses","spec":{"name":"Warehouses","key":"name"}}
{"kind":"column","table":"warehouses","ref":"name","spec":{"name":"Name","type":"string","required":true}}
{"kind":"column","table":"warehouses","ref":"responsible_products","spec":{"name":"Responsible Products","type":"link","target":"products","cardinality":"many"}}
{"kind":"column","table":"warehouses","ref":"catalog_value","spec":{"name":"Catalog Value","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. 批次寫入商品，再建立連結

商品適合用同步 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", "Name": "Industrial Sensor", "Category": "Electronics", "Unit Price": 320.0 } },
    { "action": "insert", "data": { "SKU": "SKU-007", "Name": "Bookshelf", "Category": "Furniture", "Unit Price": 45.0 } }
  ]
}


POST /private/module/custom_tables/chatroom/11111111-1111-4111-8111-111111111111/tables/33333333-3333-4333-8333-333333333333/records
 
{
  "data": {
    "Name": "Taipei Hub",
    "City": "Taipei",
    "Capacity": 5000,
    "Responsible Products": [
      "44444444-4444-4444-8444-444444444444",
      "55555555-5555-4555-8555-555555555555"
    ]
  },
  "created_by_ai": false
}

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

進貨資料列把 Product、Supplier 與 To Warehouse 都寫成單元素 ID 陣列。這是 records.create 的真實 link wire shape：


{
  "data": {
    "Type": "import",
    "Quantity": 50,
    "Date": "2026-06-01",
    "Note": "first inbound shipment",
    "Product": ["44444444-4444-4444-8444-444444444444"],
    "Supplier": ["66666666-6666-4666-8666-666666666666"],
    "To Warehouse": ["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 三個欄位。


{
  "rules": [
    { "type": "check", "name": "positive quantity", "column": "Quantity", "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": "Type", "op": "eq", "value": "export" }]
    }
  ]
}

使用者會看到什麼

倉管新增一筆流水後，對應 Stock 資料列的 imported / transferred / exported rollup 立即重算，Stock Value 也跟著更新。物流主管在聊天室問「哪座倉庫負責的商品目錄總價最高？」會得到 Taipei Hub 與 845.0；若要求把 Bookshelf 加入該倉庫，助理先取得確認，再更新 link，Responsible Count 與 Catalog Value 會同步變動。

變化與下一步

要理解 junction、反向來源與 hydrated read，閱讀連結與 rollup 指南。
要調整低庫存計算，閱讀rollup、lookup與formula。
要加上 exists / not_exists 的現貨與品管封鎖條件，閱讀規則概念與查詢 filter。

試試看

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