Skip to Content
實戰案例電商：品牌與門市

電商：品牌、門市、訂單與商品媒體

情境

ACME 經營一個 B2B2C 市集：品牌提供商品，品牌旗下的門市各自定價與備貨，顧客向門市下單。營運主管要看門市 GMV、平台費用與淨收入；商品經理要看售出量、上架門市數與剩餘庫存；客服則要從訂單一路找到顧客、門市與品項。商品圖片屬於同一套資料，但下載必須經過資料列權限，而不能把永久網址直接放進欄位。

資料模型

  • Brands：Name、Commission Rate、Country；Brand Products many-link 與 Product Count / Avg / Max MSRP
  • Stores：Name、City、Opened On、Brand → Brands；Brand Rate lookup、GMV、Cancelled Amount、Listing Count、Platform Fee、Net Revenue
  • Products：Name、Category、MSRP、Brand → Brands；Total Sold、Store Count、City Count
  • Listings：Store × Product junction；Price、Cost、Stocked Qty、MSRP lookup、Margin、Discount Ratio、Sold Qty、Remaining Stock / Value
  • Customers：Name、Level、City；Order Count、Total Spent、Last Order Date
  • Orders：Order No、Date、Status、Shipping Fee、Customer、Store；Items Subtotal / Count、Order Total
  • Order Items：Qty、Unit Price、Amount、Status；連到 Order、Product、Store、Customer，並以 Amount Check formula 核對金額
Brand ─▶ Store ─┐
  │             ├─▶ Order ◀─ Customer
  └─▶ Product ──┘      │
       ▲               ▼
       └─ Listing   Order Item

Note Order Items 刻意保存 Store、Customer 與 Status 的去正規化 link／值。這讓 Store GMV 與 Customer Total Spent 都能用單一 hop incoming rollup 計算。

完整的 IaC 文件

下面這份文件會把整個市集建起來：依相依順序宣告六張資料表、承載 GMV 與訂單金額的 list_price lookup 與 incoming rollup、把一張訂單與所有品項收斂成單一交易的 place_order command、把大額折扣送進審核的折扣關卡、各個角色實際打開的 view 與 grant，以及讓助理能讀這些資料表的聊天室 opt-in。請注意文件遵守的排序規則：一張資料表的計算欄位必須排在被彙總的來源資料表之後，這就是為什麼 orders 的 rollup 與 stores 的指標宣告在文件後段，而不是緊貼著自己的資料表。套用前請把 template_id 換成你公司裡實際可用的 Review 範本，並把 $dept:Support、$user:ops.lead 與 $room:Marketplace Ops 換成你自己的部門、使用者與聊天室。

{"kind":"header","version":1,"system":"acme-marketplace","description":"ACME B2B2C 市集"}
{"kind":"table","ref":"brands","spec":{"name":"品牌","description":"擁有商品並設定抽成比例的供應方","key":"code"}}
{"kind":"column","table":"brands","ref":"code","spec":{"name":"代號","type":"string","required":true,"max_length":16}}
{"kind":"column","table":"brands","ref":"name","spec":{"name":"名稱","type":"string","required":true}}
{"kind":"column","table":"brands","ref":"commission_rate","spec":{"name":"抽成比例","type":"float"}}
{"kind":"table","ref":"products","spec":{"name":"商品","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":"msrp","spec":{"name":"建議售價","type":"float"}}
{"kind":"column","table":"products","ref":"brand","spec":{"name":"品牌","type":"link","target":"brands","cardinality":"one"}}
{"kind":"table","ref":"stores","spec":{"name":"門市","key":"code"}}
{"kind":"column","table":"stores","ref":"code","spec":{"name":"代號","type":"string","required":true,"max_length":16}}
{"kind":"column","table":"stores","ref":"name","spec":{"name":"名稱","type":"string","required":true}}
{"kind":"column","table":"stores","ref":"city","spec":{"name":"城市","type":"string"}}
{"kind":"column","table":"stores","ref":"brand","spec":{"name":"品牌","type":"link","target":"brands","cardinality":"one"}}
{"kind":"column","table":"stores","ref":"brand_rate","spec":{"name":"品牌抽成","type":"lookup","link_field":"brand","target_column":"commission_rate"}}
{"kind":"table","ref":"customers","spec":{"name":"顧客","key":"email"}}
{"kind":"column","table":"customers","ref":"email","spec":{"name":"電子郵件","type":"string","required":true,"max_length":254}}
{"kind":"column","table":"customers","ref":"name","spec":{"name":"姓名","type":"string","required":true}}
{"kind":"column","table":"customers","ref":"level","spec":{"name":"等級","type":"select","options":["standard","gold","platinum"],"default_value":"standard"}}
{"kind":"table","ref":"orders","spec":{"name":"訂單","key":"order_no"}}
{"kind":"column","table":"orders","ref":"order_no","spec":{"name":"訂單編號","type":"string","required":true,"max_length":32}}
{"kind":"column","table":"orders","ref":"placed_at","spec":{"name":"下單時間","type":"datetime"}}
{"kind":"column","table":"orders","ref":"status","spec":{"name":"狀態","type":"select","options":["placed","shipped","completed","cancelled"],"default_value":"placed"}}
{"kind":"column","table":"orders","ref":"shipping_fee","spec":{"name":"運費","type":"float"}}
{"kind":"column","table":"orders","ref":"discount","spec":{"name":"折扣","type":"float"}}
{"kind":"column","table":"orders","ref":"customer","spec":{"name":"顧客","type":"link","target":"customers","cardinality":"one"}}
{"kind":"column","table":"orders","ref":"store","spec":{"name":"門市","type":"link","target":"stores","cardinality":"one"}}
{"kind":"column","table":"orders","ref":"platform_payload","spec":{"name":"平台原始封包","type":"json","description":"銷售平台送來的原始訂單封包，原樣保存"}}
{"kind":"table","ref":"order_items","spec":{"name":"訂單品項","key":"line_no"}}
{"kind":"column","table":"order_items","ref":"line_no","spec":{"name":"品項序號","type":"string","required":true,"max_length":40}}
{"kind":"column","table":"order_items","ref":"order","spec":{"name":"訂單","type":"link","target":"orders","cardinality":"one"}}
{"kind":"column","table":"order_items","ref":"product","spec":{"name":"商品","type":"link","target":"products","cardinality":"one"}}
{"kind":"column","table":"order_items","ref":"store","spec":{"name":"門市","type":"link","target":"stores","cardinality":"one"}}
{"kind":"column","table":"order_items","ref":"customer","spec":{"name":"顧客","type":"link","target":"customers","cardinality":"one"}}
{"kind":"column","table":"order_items","ref":"status","spec":{"name":"狀態","type":"select","options":["placed","shipped","completed","cancelled"],"default_value":"placed"}}
{"kind":"column","table":"order_items","ref":"qty","spec":{"name":"數量","type":"integer","required":true}}
{"kind":"column","table":"order_items","ref":"unit_price","spec":{"name":"單價","type":"float","required":true}}
{"kind":"column","table":"order_items","ref":"amount","spec":{"name":"小計","type":"float","required":true}}
{"kind":"column","table":"order_items","ref":"list_price","spec":{"name":"定價","type":"lookup","link_field":"product","target_column":"msrp"}}
{"kind":"column","table":"order_items","ref":"amount_check","spec":{"name":"小計核對","type":"formula","expression":"[qty] * [unit_price] - [amount]"}}
{"kind":"column","table":"orders","ref":"items_subtotal","spec":{"name":"品項小計","type":"rollup","direction":"incoming","source":"order_items","match":{"order":"$self"},"aggregation":"sum","target_column":"amount"}}
{"kind":"column","table":"orders","ref":"items_count","spec":{"name":"品項數","type":"rollup","direction":"incoming","source":"order_items","match":{"order":"$self"},"aggregation":"count"}}
{"kind":"column","table":"orders","ref":"order_total","spec":{"name":"訂單金額","type":"formula","expression":"[items_subtotal] + [shipping_fee]"}}
{"kind":"column","table":"orders","ref":"net_total","spec":{"name":"折後金額","type":"formula","expression":"[items_subtotal] + [shipping_fee] - [discount]"}}
{"kind":"column","table":"stores","ref":"gmv","spec":{"name":"GMV","type":"rollup","direction":"incoming","source":"order_items","match":{"store":"$self"},"aggregation":"sum","target_column":"amount","filter":[{"column":"status","op":"neq","value":"cancelled"}]}}
{"kind":"column","table":"stores","ref":"platform_fee","spec":{"name":"平台費用","type":"formula","expression":"[gmv] * [brand_rate]"}}
{"kind":"column","table":"stores","ref":"net_revenue","spec":{"name":"淨收入","type":"formula","expression":"[gmv] - ([gmv] * [brand_rate])"}}
{"kind":"column","table":"customers","ref":"total_spent","spec":{"name":"累計消費","type":"rollup","direction":"incoming","source":"order_items","match":{"customer":"$self"},"aggregation":"sum","target_column":"amount","filter":[{"column":"status","op":"neq","value":"cancelled"}]}}
{"kind":"column","table":"products","ref":"total_sold","spec":{"name":"累計售出","type":"rollup","direction":"incoming","source":"order_items","match":{"product":"$self"},"aggregation":"sum","target_column":"qty","filter":[{"column":"status","op":"neq","value":"cancelled"}]}}
{"kind":"rule","table":"orders","ref":"unique_order_no","spec":{"type":"unique","name":"訂單編號不得重複","columns":["order_no"],"case_insensitive":true}}
{"kind":"rule","table":"orders","ref":"non_negative_shipping","spec":{"type":"check","name":"運費不得為負","column":"shipping_fee","op":"gte","value":0}}
{"kind":"rule","table":"orders","ref":"large_discount_review","spec":{"type":"require_approval","name":"大額折扣需要審核","label":"大額訂單折扣","events":["create","update"],"template_id":"44444444-4444-4444-8444-444444444444","when":[{"column":"discount","op":"gte","value":1000}]}}
{"kind":"rule","table":"order_items","ref":"unique_line","spec":{"type":"unique","name":"同一張訂單內品項序號不得重複","columns":["order","line_no"],"case_insensitive":false}}
{"kind":"rule","table":"order_items","ref":"positive_qty","spec":{"type":"check","name":"數量必須為正","column":"qty","op":"gt","value":0}}
{"kind":"command","ref":"place_order","spec":{"name":"建立訂單","description":"在同一筆交易內建立一張訂單與它的所有品項。","definition":{"version":2,"inputs":[{"name":"order_no","type":"string","required":true,"max_length":32},{"name":"customer_email","type":"string","required":true,"max_length":254},{"name":"store_id","type":"string","required":true,"max_length":36},{"name":"placed_at","type":"datetime","required":true},{"name":"shipping_fee","type":"float","required":true},{"name":"discount","type":"float","required":true},{"name":"payload","type":"object","required":false,"nullable":true},{"name":"lines","type":"array","required":true,"min_items":1,"max_items":50,"item_schema":{"line_no":"string","product_id":"string","qty":"integer","unit_price":"float"}}],"steps":[{"kind":"select","name":"buyer","from":{"table":"customers","as":"c"},"select":{"customer_id":{"$metadata":{"source":"c","name":"id"}}},"where":{"$eq":["$row.c.email","$input.customer_email"]},"max_rows":1},{"kind":"assert","name":"known_buyer","condition":{"$exists":{"relation":"$rel.buyer"}},"message":"沒有任何顧客使用這個電子郵件註冊。"},{"kind":"let","name":"customer_id","value":{"$single":{"relation":"$rel.buyer","expr":"$item.customer_id"}}},{"kind":"insert","name":"order","table":"orders","values":{"order_no":"$input.order_no","placed_at":"$input.placed_at","status":"placed","shipping_fee":"$input.shipping_fee","discount":"$input.discount","customer":"$var.customer_id","store":"$input.store_id","platform_payload":"$input.payload"},"returning":{"order_id":{"$metadata":{"source":"new","name":"id"}}}},{"kind":"let","name":"order_id","value":{"$single":{"relation":"$rel.order","expr":"$item.order_id"}}},{"kind":"insert","name":"lines","table":"order_items","for_each":"$input.lines","values":{"line_no":"$item.line_no","order":"$var.order_id","product":"$item.product_id","store":"$input.store_id","customer":"$var.customer_id","status":"placed","qty":"$item.qty","unit_price":"$item.unit_price","amount":{"$mul":["$item.qty","$item.unit_price"]}},"returning":{"line_id":{"$metadata":{"source":"new","name":"id"}}}}],"outputs":{"order_id":{"value":"$var.order_id"},"lines":{"relation":"$rel.lines","select":["line_id"],"max_rows":50}}}}}
{"kind":"view","table":"orders","ref":"open_orders","spec":{"name":"未結訂單","is_shared":true,"config":{"filters":{"status":"placed"},"sort_by":"placed_at","sort_order":"desc","columns":["order_no","placed_at","customer","store","items_count","items_subtotal","order_total","net_total"]}}}
{"kind":"view","table":"orders","ref":"support_lookup","spec":{"name":"客服查詢","is_shared":true,"config":{"sort_by":"placed_at","sort_order":"desc","columns":["order_no","placed_at","status","customer","store","items_count","order_total"]}}}
{"kind":"grant","table":"orders","principal":{"type":"department","id":"$dept:Support"},"spec":{"can_read":"all","can_insert":false,"can_edit":"none","visible_columns":["order_no","placed_at","status","customer","store","items_count","items_subtotal","shipping_fee","order_total"]}}
{"kind":"grant","table":"order_items","principal":{"type":"department","id":"$dept:Support"},"spec":{"can_read":"all","can_insert":false,"can_edit":"none","visible_columns":["line_no","order","product","store","status","qty","unit_price","amount"]}}
{"kind":"grant","table":"orders","principal":{"type":"user","id":"$user:ops.lead"},"spec":{"can_read":"all","can_insert":true,"can_edit":"all"}}
{"kind":"grant","table":"orders","principal":{"type":"chatroom","id":"$room:Marketplace Ops","audience":"internal"},"spec":{"can_read":"filtered","can_insert":false,"can_edit":"none","read_filter":{"and":[{"column":"status","op":"neq","value":"cancelled"}]},"visible_columns":["order_no","placed_at","status","store","items_count","order_total","net_total"]}}
{"kind":"insight_selection","chatroom":"$room:Marketplace Ops"}

真正承載市集邏輯的幾行

  • command:place_order——以 version 2 definition 寫成的典型多筆寫入：先查出買家、找不到就拒絕、寫入訂單，再以 for_each 走過型別化的 array input 寫入每一個品項，全部都在同一筆會整體回滾的交易裡。
  • place_order 的兩個 let 步驟——$var.customer_id 與 $var.order_id 先把 id 命名一次，for_each 的品項寫入才能用 $item.<field> 指元素、用 $var.<name> 指母體，兩個 $item 作用域不會互相衝突。
  • orders.items_subtotal 與 orders.order_total——rollup 讀的是每一列品項已儲存的 amount，formula 再加上運費；這正是 order_items.amount 採用儲存 float 而非 formula 的原因，因為 rollup 只能彙總已儲存的純量。
  • order_items.amount_check——[qty] * [unit_price] - [amount]，當儲存的小計與它的來源一致時讀出 0，只要匯入端寫進別的數字就立刻變成非零。
  • order_items.list_price——把商品建議售價帶到品項上的 lookup，不必再讀一次就能看出折扣相對定價有多深。
  • orders.large_discount_review——require_approval 規則：折扣達到 1000 以上就不再直接落地，而是進入待審。寫入會回傳 409 approval_required，並帶著 process_id 與 staged_change_id；審核者看到的是遮蔽過的 plan——他讀不到的資料表，其資料列會收斂成一則 {table_id, action, row_count} 摘要，而 command 凍結的 plan，其 change_type 就是字面值 command_plan。由於 place_order 會寫入 orders，帶折扣的訂單經由 command 建立時，整個 plan 都會進入待審而不只是訂單那一列；也因此，table trigger 永遠不得用來觸發它。完整的審核路徑見 command 審核。
  • orders.platform_payload——原樣保存平台封包的 json 欄位。在 REST 上它只支援等值比對（eq、neq、in、is_null、is_not_null）、永遠不可排序，也不能當 rollup 或 lookup 的目標、更不能被 formula 引用——這正是為什麼所有業務會讀的數字，都來自它旁邊那些型別化的欄位，而不是這個封包。它同時也被排除在客服 grant 的 visible_columns 之外。
  • insight_selection——把 $room:Marketplace Ops 聊天室加入本文件的 acme-marketplace insight system，聊天室助理因此才讀得到這些資料表。它需要非空的 header.system，而且只在部門層級（department-scope）的文件中有效。

Apply 之前

require_approval 那一行引用的覆核範本 IaC 建不出來。請先在覆核模組建立群組與範本並換上 id，見用 REST 補完。文件裡其他東西（包含 command 與洞察系統選擇）都照寫的樣子套用。

產品流程

1. 以 IaC 套用七表模型

JSONL 先建立基礎表與 one-link，再在文件後段加入循環的 Brand Products、incoming rollups、lookups 與 formulas。以下片段展示訂單與品項的直接關係：

{"kind":"header","version":1,"system":"acme-marketplace","description":"ACME B2B2C marketplace"}
{"kind":"table","ref":"orders","spec":{"name":"Orders","key":"order_no"}}
{"kind":"column","table":"orders","ref":"order_no","spec":{"name":"Order No","type":"string","required":true}}
{"kind":"column","table":"orders","ref":"shipping_fee","spec":{"name":"Shipping Fee","type":"float"}}
{"kind":"table","ref":"order_items","spec":{"name":"Order Items"}}
{"kind":"column","table":"order_items","ref":"qty","spec":{"name":"Qty","type":"integer"}}
{"kind":"column","table":"order_items","ref":"unit_price","spec":{"name":"Unit Price","type":"float"}}
{"kind":"column","table":"order_items","ref":"amount","spec":{"name":"Amount","type":"float"}}
{"kind":"column","table":"order_items","ref":"order","spec":{"name":"Order","type":"link","target":"orders","cardinality":"one"}}
{"kind":"column","table":"orders","ref":"items_subtotal","spec":{"name":"Items Subtotal","type":"rollup","direction":"incoming","source":"order_items","match":{"order":"$self"},"aggregation":"sum","target_column":"amount"}}
{"kind":"column","table":"orders","ref":"items_count","spec":{"name":"Items Count","type":"rollup","direction":"incoming","source":"order_items","match":{"order":"$self"},"aggregation":"count"}}
{"kind":"column","table":"orders","ref":"order_total","spec":{"name":"Order Total","type":"formula","expression":"[items_subtotal] + [shipping_fee]"}}

將完整文件送給 iac.plan，確認 diff 後以同一份 body 呼叫 iac.apply。

2. 批次寫入商品、訂單與品項

商品與品項適合使用同步 bulk.mixed。Link cell 一律是 record ID 陣列；Amount 保存為 Qty × Unit Price 的結果，供 rollup 使用：

POST /private/module/custom_tables/chatroom/11111111-1111-4111-8111-111111111111/tables/22222222-2222-4222-8222-222222222222/records/bulk
 
{
  "actions": [
    {
      "action": "insert",
      "data": {
        "Qty": 2,
        "Unit Price": 120.0,
        "Amount": 240.0,
        "Status": "completed",
        "Order": ["33333333-3333-4333-8333-333333333333"],
        "Product": ["44444444-4444-4444-8444-444444444444"],
        "Store": ["55555555-5555-4555-8555-555555555555"],
        "Customer": ["66666666-6666-4666-8666-666666666666"]
      }
    }
  ]
}

大量初始資料可改用 bulk.insert；它回傳 ticket，前端應顯示背景進度，而不是假設資料已同步完成。

3. 讀取訂單與門市指標

用 records.get 或 list 的 expand_links=true 同時取得 Customer、Store 的可讀資料與計算欄位：

GET /private/module/custom_tables/chatroom/11111111-1111-4111-8111-111111111111/tables/77777777-7777-4777-8777-777777777777/records/33333333-3333-4333-8333-333333333333?expand_links=true

ORD-005 的 Items Subtotal 會是 252.0、Items Count 是 3、Shipping Fee 是 5.0，所以 Order Total 是 257.0。門市頁面則直接讀 GMV、Platform Fee 與 Net Revenue，不需要前端再掃描 Order Items。

4. 以受控附件保存商品圖片

建立 Images:attachment 欄位時可宣告 max_count、allowed_mime_types 與 max_file_bytes。上傳採三段式流程：

POST /private/module/custom_tables/chatroom/tables/88888888-8888-4888-8888-888888888888/blobs/multipart/init
Content-Type: multipart/form-data
 
filename=product-front.png
file_size=2056
content_type=image/png

把回傳的 session_id 與檔案 chunk 送到 /public/module/etl/multipart/chunk，再呼叫 attachments.multipartComplete。完成後將 blob ID 寫進資料列：

{
  "data": {
    "SKU": "SKU-IMG-001",
    "Images": ["99999999-9999-4999-8999-999999999999"]
  }
}

資料列回應會提供 filename、content type 與 blob ID，但 url 是 null。真正下載時使用 attachments.download，由每一次請求重新檢查資料列與附件可見性。

使用者會看到什麼

營運首頁可以直接排名門市 GMV，並顯示平台費用與淨收入；商品頁顯示上架門市數、售出量與剩餘價值；訂單頁展開顧客、門市與品項。聊天室分析員能回答「哪間門市 GMV 第一？」或在確認後把訂單的 Customer one-link 改到另一位顧客，而 Items rollups 與 Store link 保持不變。商品圖片只透過授權下載路徑取得。

變化與下一步

試試看

先在 IaC 工作台規劃 Brands／Stores／Products 的最小子圖，再到 API Playground寫入一張訂單與兩個品項，觀察 Order Total 自動更新。

Last updated on
預約：牙科診所QA：跨表營運分析