PMS：專案、阻塞關係與工時獲利

情境

ACME 的工程主管想把 Jira／Linear 式的任務追蹤、Sprint 容量與工時成本放在同一套模型裡。產品經理建立 Epic 與 Issue，工程師填寫 WorkLog，主管則關心完成率、阻塞鏈與專案毛利。最關鍵的建模選擇是 Issues.Blocked By → Issues 自我連結：同一張表同時描述「我被誰擋住」與「我擋住誰」。

資料模型

Teams → Members：Member 有 Hourly Rate；Issue 與 WorkLog 都連回 Member
Epics ← Issues：Total / Done Points、Open Issues、Logged Hours、Labor Cost，再計算 Epic Progress、Net Profit、Profit Margin
Sprints ← Issues：Committed / Completed Points，再計算 Completion 與 At Risk
Issues：Title、Status、Priority、Story Points、Created；連到 Epic、Sprint、Assignee、Labels
- Blocked By → Issues (many)
- outgoing Blocker Count、incoming Blocks Count、Is Blocked formula
- Assignee Name / Epic Name lookup
WorkLogs：Hours、Date、Rate Snapshot、Cost，連到 Member、Issue 與去正規化的 Epic


Teams ◀─ Members ◀─ Assignee ─ Issues ─ Epic ─▶ Epics
                         │        │
                         │        └─ Blocked By ─┐
                         │                       └─▶ Issues
                         └─ WorkLogs ───────────────▶ Issues / Epics

Note 「沒有符合資料」與「合計為 0」不同。Phoenix Modernization 的實際故事點數為 0，所以 Total / Done 都是 0.0；0 / 0 的 Epic Progress 則是 null。

一份文件就是整套專案系統

下面這份 bundle 把上面的資料模型一次做到底：依相依順序排好的七張表、故事需要的每一個衍生欄位、讓狀態機與範圍變更真正成立的 rule、把結案動作跨兩張表帶過去的一組自動化，以及能分辨工程師與交付主管視角的 grant。把它貼進 IaC 工作台，先跑 iac.plan，再帶著回傳的 plan_hash 與位元組完全相同的文件呼叫 iac.apply。其中有四樣東西與環境相關，套用前必須先替換：header.system、$dept: 與 $room: 身分 token、notify 動作的 chatroom_id，以及 require_approval rule 的 template_id。


{"kind":"header","version":1,"system":"acme-pms","description":"ACME delivery tracker"}
{"kind":"table","ref":"teams","spec":{"name":"Teams","key":"name"}}
{"kind":"column","table":"teams","ref":"name","spec":{"name":"Name","type":"string","required":true}}
{"kind":"table","ref":"members","spec":{"name":"Members","key":"email"}}
{"kind":"column","table":"members","ref":"email","spec":{"name":"Email","type":"string","required":true}}
{"kind":"column","table":"members","ref":"full_name","spec":{"name":"Full Name","type":"string"}}
{"kind":"column","table":"members","ref":"hourly_rate","spec":{"name":"Hourly Rate","type":"float"}}
{"kind":"column","table":"members","ref":"team","spec":{"name":"Team","type":"link","target":"teams","cardinality":"one"}}
{"kind":"column","table":"members","ref":"account","spec":{"name":"Account","type":"user","description":"Platform identity; ACL row policies match this with $me"}}
{"kind":"column","table":"members","ref":"team_name","spec":{"name":"Team Name","type":"lookup","link_field":"team","target_column":"name"}}
{"kind":"table","ref":"epics","spec":{"name":"Epics","key":"code"}}
{"kind":"column","table":"epics","ref":"code","spec":{"name":"Code","type":"string","required":true}}
{"kind":"column","table":"epics","ref":"name","spec":{"name":"Name","type":"string","required":true}}
{"kind":"column","table":"epics","ref":"budget","spec":{"name":"Budget","type":"float"}}
{"kind":"column","table":"epics","ref":"last_closed_on","spec":{"name":"Last Closed On","type":"date"}}
{"kind":"table","ref":"sprints","spec":{"name":"Sprints","key":"name"}}
{"kind":"column","table":"sprints","ref":"name","spec":{"name":"Name","type":"string","required":true}}
{"kind":"column","table":"sprints","ref":"starts_on","spec":{"name":"Starts On","type":"date"}}
{"kind":"column","table":"sprints","ref":"ends_on","spec":{"name":"Ends On","type":"date"}}
{"kind":"column","table":"sprints","ref":"committed_points","spec":{"name":"Committed Points","type":"integer"}}
{"kind":"table","ref":"labels","spec":{"name":"Labels","key":"name"}}
{"kind":"column","table":"labels","ref":"name","spec":{"name":"Name","type":"string","required":true}}
{"kind":"table","ref":"issues","spec":{"name":"Issues","key":"title"}}
{"kind":"column","table":"issues","ref":"title","spec":{"name":"Title","type":"string","required":true}}
{"kind":"column","table":"issues","ref":"status","spec":{"name":"Status","type":"string"}}
{"kind":"column","table":"issues","ref":"priority","spec":{"name":"Priority","type":"integer"}}
{"kind":"column","table":"issues","ref":"story_points","spec":{"name":"Story Points","type":"integer"}}
{"kind":"column","table":"issues","ref":"created_on","spec":{"name":"Created","type":"date"}}
{"kind":"column","table":"issues","ref":"due_on","spec":{"name":"Due On","type":"date"}}
{"kind":"column","table":"issues","ref":"completed_on","spec":{"name":"Completed On","type":"date"}}
{"kind":"column","table":"issues","ref":"owner","spec":{"name":"Owner","type":"user","description":"Platform identity a $me row policy can match; Assignee is the Member record"}}
{"kind":"column","table":"issues","ref":"epic","spec":{"name":"Epic","type":"link","target":"epics","cardinality":"one"}}
{"kind":"column","table":"issues","ref":"epic_code","spec":{"name":"Epic Code","type":"string","description":"Denormalized epic key so automation can address the epic without a join"}}
{"kind":"column","table":"issues","ref":"sprint","spec":{"name":"Sprint","type":"link","target":"sprints","cardinality":"one"}}
{"kind":"column","table":"issues","ref":"assignee","spec":{"name":"Assignee","type":"link","target":"members","cardinality":"one"}}
{"kind":"column","table":"issues","ref":"labels","spec":{"name":"Labels","type":"link","target":"labels","cardinality":"many"}}
{"kind":"column","table":"issues","ref":"blocked_by","spec":{"name":"Blocked By","type":"link","target":"issues","cardinality":"many"}}
{"kind":"column","table":"issues","ref":"blocker_count","spec":{"name":"Blocker Count","type":"rollup","link_field":"blocked_by","aggregation":"count"}}
{"kind":"column","table":"issues","ref":"blocks_count","spec":{"name":"Blocks Count","type":"rollup","direction":"incoming","source":"issues","match":{"blocked_by":"$self"},"aggregation":"count"}}
{"kind":"column","table":"issues","ref":"is_blocked","spec":{"name":"Is Blocked","type":"formula","expression":"[blocker_count] > 0"}}
{"kind":"column","table":"issues","ref":"assignee_name","spec":{"name":"Assignee Name","type":"lookup","link_field":"assignee","target_column":"full_name"}}
{"kind":"column","table":"issues","ref":"epic_name","spec":{"name":"Epic Name","type":"lookup","link_field":"epic","target_column":"name"}}
{"kind":"column","table":"issues","ref":"assignee_rate","spec":{"name":"Assignee Rate","type":"lookup","link_field":"assignee","target_column":"hourly_rate"}}
{"kind":"column","table":"issues","ref":"estimated_cost","spec":{"name":"Estimated Cost","type":"formula","expression":"[story_points] * [assignee_rate]"}}
{"kind":"table","ref":"worklogs","spec":{"name":"WorkLogs","key":"reference"}}
{"kind":"column","table":"worklogs","ref":"reference","spec":{"name":"Reference","type":"string","required":true}}
{"kind":"column","table":"worklogs","ref":"hours","spec":{"name":"Hours","type":"float"}}
{"kind":"column","table":"worklogs","ref":"logged_on","spec":{"name":"Date","type":"date"}}
{"kind":"column","table":"worklogs","ref":"rate_snapshot","spec":{"name":"Rate Snapshot","type":"float"}}
{"kind":"column","table":"worklogs","ref":"cost","spec":{"name":"Cost","type":"float","description":"Stored, not derived: a rollup can only aggregate a stored scalar"}}
{"kind":"column","table":"worklogs","ref":"logged_by","spec":{"name":"Logged By","type":"user"}}
{"kind":"column","table":"worklogs","ref":"member","spec":{"name":"Member","type":"link","target":"members","cardinality":"one"}}
{"kind":"column","table":"worklogs","ref":"issue","spec":{"name":"Issue","type":"link","target":"issues","cardinality":"one"}}
{"kind":"column","table":"worklogs","ref":"epic","spec":{"name":"Epic","type":"link","target":"epics","cardinality":"one"}}
{"kind":"column","table":"epics","ref":"total_points","spec":{"name":"Total Points","type":"rollup","direction":"incoming","source":"issues","match":{"epic":"$self"},"aggregation":"sum","target_column":"story_points"}}
{"kind":"column","table":"epics","ref":"done_points","spec":{"name":"Done Points","type":"rollup","direction":"incoming","source":"issues","match":{"epic":"$self"},"aggregation":"sum","target_column":"story_points","filter":[{"column":"status","op":"eq","value":"done"}]}}
{"kind":"column","table":"epics","ref":"open_issues","spec":{"name":"Open Issues","type":"rollup","direction":"incoming","source":"issues","match":{"epic":"$self"},"aggregation":"count","filter":[{"column":"status","op":"neq","value":"done"}]}}
{"kind":"column","table":"epics","ref":"logged_hours","spec":{"name":"Logged Hours","type":"rollup","direction":"incoming","source":"worklogs","match":{"epic":"$self"},"aggregation":"sum","target_column":"hours"}}
{"kind":"column","table":"epics","ref":"labor_cost","spec":{"name":"Labor Cost","type":"rollup","direction":"incoming","source":"worklogs","match":{"epic":"$self"},"aggregation":"sum","target_column":"cost"}}
{"kind":"column","table":"epics","ref":"progress","spec":{"name":"Epic Progress","type":"formula","expression":"[done_points] / [total_points]"}}
{"kind":"column","table":"epics","ref":"net_profit","spec":{"name":"Net Profit","type":"formula","expression":"[budget] - [labor_cost]"}}
{"kind":"column","table":"epics","ref":"profit_margin","spec":{"name":"Profit Margin","type":"formula","expression":"[net_profit] / [budget]"}}
{"kind":"column","table":"sprints","ref":"completed_points","spec":{"name":"Completed Points","type":"rollup","direction":"incoming","source":"issues","match":{"sprint":"$self"},"aggregation":"sum","target_column":"story_points","filter":[{"column":"status","op":"eq","value":"done"}]}}
{"kind":"column","table":"sprints","ref":"open_issues","spec":{"name":"Open Issues","type":"rollup","direction":"incoming","source":"issues","match":{"sprint":"$self"},"aggregation":"count","filter":[{"column":"status","op":"neq","value":"done"}]}}
{"kind":"column","table":"sprints","ref":"completion","spec":{"name":"Completion","type":"formula","expression":"[completed_points] / [committed_points]"}}
{"kind":"column","table":"sprints","ref":"at_risk","spec":{"name":"At Risk","type":"formula","expression":"[completion] < 0.5"}}
{"kind":"rule","table":"issues","ref":"status_flow","spec":{"type":"transition","name":"Issue status machine","column":"status","pairs":[[null,"todo"],["todo","in_progress"],["todo","cancelled"],["in_progress","in_review"],["in_progress","blocked"],["in_progress","cancelled"],["blocked","in_progress"],["in_review","in_progress"],["in_review","done"]]}}
{"kind":"rule","table":"issues","ref":"unique_title_per_epic","spec":{"type":"unique","name":"An issue title is unique inside its epic","columns":["epic","title"],"case_insensitive":true}}
{"kind":"rule","table":"issues","ref":"points_cap","spec":{"type":"check","name":"Story points stay on the agreed scale","column":"story_points","op":"lte","value":13}}
{"kind":"rule","table":"issues","ref":"completed_on_required","spec":{"type":"require","name":"A done issue carries its completion date","column":"completed_on","when":[{"column":"status","op":"eq","value":"done"}]}}
{"kind":"rule","table":"sprints","ref":"window_order","spec":{"type":"compare","name":"A sprint cannot end before it starts","left":"starts_on","op":"lte","right":"ends_on"}}
{"kind":"rule","table":"sprints","ref":"scope_change_approval","spec":{"type":"require_approval","name":"Sprint scope changes need delivery-lead approval","label":"Sprint scope change","events":["update"],"template_id":"99999999-9999-4999-8999-999999999999","when":[{"column":"committed_points","op":"gte","value":1}]}}
{"kind":"rule","table":"worklogs","ref":"hours_not_negative","spec":{"type":"check","name":"A work log cannot record negative time","column":"hours","op":"gte","value":0}}
{"kind":"command","ref":"close_issue","spec":{"name":"Close issue","description":"Refresh the epic's last-closed date and file the closure entry, in one transaction.","definition":{"version":1,"inputs":[{"name":"issue_title","type":"string","required":true,"nullable":false,"max_length":200},{"name":"epic_code","type":"string","required":true,"nullable":false,"max_length":36},{"name":"closed_on","type":"date","required":true,"nullable":false}],"steps":[{"name":"stamp_epic","table":"epics","action":"update","rows":[{"match":{"code":"$input.epic_code"},"data":{"last_closed_on":"$input.closed_on"},"max_rows":1}]},{"name":"closure_log","table":"worklogs","action":"insert","rows":[{"data":{"reference":"$input.issue_title","logged_on":"$input.closed_on","hours":0,"cost":0,"logged_by":"$ctx.user_id"}}]}]}}}
{"kind":"trigger","table":"issues","ref":"close_out","spec":{"name":"Close out an accepted issue","on":"updated","transition":{"column":"status","from":"in_review","to":"done"},"actions":[{"type":"invoke_command","command":"close_issue","inputs":{"issue_title":"$row.title","epic_code":"$row.epic_code","closed_on":"$row.completed_on"}}]}}
{"kind":"trigger","table":"issues","ref":"blocked_alert","spec":{"name":"Announce a new blocker","on":"updated","when":[{"column":"status","op":"eq","value":"blocked"}],"actions":[{"type":"notify","chatroom_id":"11111111-1111-4111-8111-111111111111","message":"Blocked: $row.title (due $row.due_on)"}]}}
{"kind":"view","table":"issues","ref":"due_next_7_days","spec":{"name":"Due in the next 7 days","is_shared":true,"config":{"stored_filters":[{"column":"due_on","op":"within_next","value":{"amount":7,"unit":"days"}}],"any_of":[{"stored_filters":[{"column":"status","op":"eq","value":"todo"}]},{"stored_filters":[{"column":"status","op":"eq","value":"in_progress"},{"column":"priority","op":"lte","value":2}]}],"sort":[{"column":"priority","order":"asc"},{"column":"due_on","order":"asc"}],"columns":["title","status","priority","due_on","assignee","epic"]}}}
{"kind":"view","table":"issues","ref":"blocked_now","spec":{"name":"Blocked right now","is_shared":true,"config":{"computed_filters":[{"column":"blocker_count","op":"gte","value":1}],"sort_by":"blocker_count","sort_order":"desc","columns":["title","status","blocker_count","blocks_count","assignee_name"]}}}
{"kind":"view","table":"issues","ref":"unassigned","spec":{"name":"Nobody owns this yet","is_shared":true,"config":{"stored_filters":[{"column":"assignee","op":"is_empty"}],"sort":[{"column":"priority","order":"asc"},{"column":"created_on","order":"asc"}],"columns":["title","status","priority","created_on","epic"]}}}
{"kind":"view","table":"issues","ref":"overdue","spec":{"name":"Overdue and still open","is_shared":true,"config":{"stored_filters":[{"column":"due_on","op":"older_than","value":{"amount":1,"unit":"days"}}],"any_of":[{"stored_filters":[{"column":"status","op":"eq","value":"todo"}]},{"stored_filters":[{"column":"status","op":"eq","value":"in_progress"}]},{"stored_filters":[{"column":"status","op":"eq","value":"blocked"}]}],"sort":[{"column":"due_on","order":"asc"},{"column":"priority","order":"asc"}],"columns":["title","status","due_on","assignee_name"]}}}
{"kind":"view","table":"epics","ref":"margin_watch","spec":{"name":"Margin watch","is_shared":true,"config":{"computed_filters":[{"column":"profit_margin","op":"lte","value":0.15}],"sort_by":"profit_margin","sort_order":"asc","columns":["code","name","budget","labor_cost","net_profit","profit_margin","progress"]}}}
{"kind":"view","table":"worklogs","ref":"last_30_days","spec":{"name":"Time logged in the last 30 days","is_shared":true,"config":{"stored_filters":[{"column":"logged_on","op":"within_last","value":{"amount":30,"unit":"days"}}],"sort":[{"column":"logged_on","order":"desc"},{"column":"hours","order":"desc"}],"columns":["reference","logged_on","hours","cost","member"]}}}
{"kind":"grant","table":"issues","principal":{"type":"department","id":"$dept:Engineering"},"spec":{"can_read":"filtered","can_insert":true,"can_edit":"filtered","read_filter":{"and":[{"column":"owner","op":"eq","value":"$me"}]},"edit_filter":{"and":[{"column":"owner","op":"eq","value":"$me"}]}}}
{"kind":"grant","table":"issues","principal":{"type":"department","id":"$dept:Delivery Management"},"spec":{"can_read":"all","can_insert":true,"can_edit":"all"}}
{"kind":"grant","table":"issues","principal":{"type":"chatroom","id":"$room:Delivery/Daily Standup","audience":"internal"},"spec":{"can_read":"filtered","can_insert":false,"can_edit":"none","read_filter":{"and":[{"column":"due_on","op":"gte","value":"$today"},{"column":"due_on","op":"lt","value":"$today+7d"}]}}}
{"kind":"grant","table":"worklogs","principal":{"type":"department","id":"$dept:Engineering"},"spec":{"can_read":"filtered","can_insert":true,"can_edit":"filtered","read_filter":{"and":[{"column":"logged_by","op":"eq","value":"$me"}]},"edit_filter":{"and":[{"column":"logged_by","op":"eq","value":"$me"}]}}}
{"kind":"grant","table":"epics","principal":{"type":"department","id":"$dept:Engineering"},"spec":{"can_read":"all","can_insert":false,"can_edit":"none","visible_columns":["code","name","progress","open_issues","total_points","done_points"]}}
{"kind":"grant","table":"epics","principal":{"type":"department","id":"$dept:Delivery Management"},"spec":{"can_read":"all","can_insert":true,"can_edit":"all"}}
{"kind":"grant","table":"sprints","principal":{"type":"department","id":"$dept:Delivery Management"},"spec":{"can_read":"all","can_insert":true,"can_edit":"all"}}

值得理解的幾行

issues.owner 與 worklogs.logged_by 是 user 欄位，存在的理由只有一個：row policy 只能引用已儲存的純量，所以 $me 可以比對 principal、user、social_client、string 或 text，唯獨不能比對 link。今天新寫的表在這裡會改用單一 principal 欄位，額外換到「$me 也能比對到檢視者所屬聊天室」這件事。這也是為什麼 assignee（Member 資料列，適合 lookup 與顯示）與 owner（ACL 比對的平台身分）要同時存在，而不是其中一個多餘。
Issues 上的 $dept:Engineering grant 就是「我的任務」：read_filter 與 edit_filter 都把 owner 與 $me 比對。token 以原樣存在 grant 資料列上，每次請求時才代入當前身分的 id，所以同一筆 grant 對每個人是不同的切片，而且解析刻意不快取。權限編輯器必須原樣往返這個字串；把解析後的 id 顯示出來再存回去，就等於把 grant 去 token 化。
$room:Delivery/Daily Standup grant 是「未來七天到期」：due_on gte $today 且 due_on lt $today+7d。要兩個述詞，是因為 row policy 的語法只有 and，一個區間需要上下兩半；$today±Nd 依 Asia/Taipei 的日界解析，N 的範圍是 1 到 730。
這些 token 只在 ACL row policy 裡合法。同樣一對述詞放進 view 的 stored_filters 永遠不會被代入，它只是一個安靜地什麼都比不到的字面字串。你無法用 search 做出「我的任務」快速篩選；請把它寫成 grant，伺服器本來就會套用在每一次讀取上。詳見row policy tokens。
issues.status_flow 用白名單表達狀態機：只有列出的舊值／新值配對能通過，null 代表空狀態端點。它檢查 update 與還原，不檢查建立——初始狀態由 completed_on_required 與欄位預設值決定，不是這條 rule。
sprints.scope_change_approval 把 committed points 的修改變成一次審查。寫入者不會拿到驗證錯誤：第一次符合條件的寫入回傳 409，body 帶著 process_id、staged_change_id 與這條 rule 的 label——那是「已送審成功」，請以「已送出審查」呈現，且絕不自動重試。審查者看到的是一個 Review 流程，上面有那個 label、暫存的前後值，以及是誰嘗試修改；寫入要等流程核准並重放後才會落地。一張表最多只能有一條 require_approval，而在有待審變更時修改它會得到 409 approval_rule_locked。
issues.close_out 在 in_review → done 的 transition 上觸發，呼叫同一份文件前面宣告的 close_issue command——同文件內雙向引用都合法，執行器會把 trigger 這行延後到 command 套用之後。command 以 trigger 作者的身分執行，而不是那個把狀態改掉的人，所以該作者必須維持有效帳號，且同時是 Epics 與 WorkLogs 的 table manager，否則每一次執行都會失敗。
command 是這個故事的跨表半邊：同一個交易更新 epics.last_closed_on，並把結案紀錄寫進 WorkLogs。這兩張表都沒有 require_approval——被 trigger 呼叫的 command 只要碰到有審查閘門的表就是 409 approval_trigger_conflict，這正是上面那道閘門要放在 Sprints 的原因。
issues.epic_code 是刻意的去正規化。$row. trigger 輸入只能來自已儲存的純量，所以計算而來的 epic_name lookup 餵不進 command，但存下來的 code 可以，而且讓 command 不必 join 就能定位 epic。
worklogs.cost 選擇儲存而非衍生，也是同一類理由：rollup 只能彙總已儲存的純量，所以 epics.labor_cost 只加得動真實欄位。存起來同時把費率凍結在當下，不受日後 members.hourly_rate 調整影響——這正是 rate_snapshot 想說明的事。
issues.blocks_count 是自我連結的 incoming 那一半——source 就是同一張表，match: {"blocked_by": "$self"}——而 blocker_count 沿同一條 link 走 outgoing。一個 link 欄位、兩個方向，is_blocked 只讀 outgoing 的計數。
issues.estimated_cost 把已儲存的整數乘上 assignee_rate，那是一個 cardinality-one 的 lookup。one-link lookup 是合法的 formula 運算元；沒有 pick 的 cardinality-many lookup 則不是。
這些 view 是依「觀眾」而不是依「身分」設計的：due_next_7_days 與 overdue 給站立會議、blocked_now 與 unassigned 給主管分流、margin_watch 給錢的對話、last_30_days 給工時。blocked_now 與 margin_watch 使用單鍵 sort_by，因為計算欄位永遠不是合法的多鍵 sort 鍵。
兩筆 Delivery Management grant 與兩筆 Engineering grant 就是成員與專案經理之間的全部差異：主管在 Issues、Epics、Sprints 上都能讀能改；工程師只讀自己的資料列、可以新增，並透過 visible_columns 只看得到 Epics 的進度，看不到預算與成本。

Apply 之前

那份文件裡有兩件事不是自給自足的。require_approval 規則引用的覆核範本要先在覆核模組建立；而 $me / $today±Nd 雖然是每次請求才解析，授權自己的 principal id 仍需要真實的部門或使用者，這就是 $dept: 與 $user: 代號的用途。見用 REST 補完。

產品流程

1. 用 JSONL 按依賴順序建立模型

先宣告 Issues 的純量與外部 link，再建立 self-link，最後才建立依賴它的 rollup 與 formula：


{"kind":"header","version":1,"system":"acme-pms","description":"ACME delivery tracker"}
{"kind":"table","ref":"issues","spec":{"name":"Issues","key":"title"}}
{"kind":"column","table":"issues","ref":"title","spec":{"name":"Title","type":"string","required":true}}
{"kind":"column","table":"issues","ref":"status","spec":{"name":"Status","type":"string"}}
{"kind":"column","table":"issues","ref":"story_points","spec":{"name":"Story Points","type":"integer"}}
{"kind":"column","table":"issues","ref":"blocked_by","spec":{"name":"Blocked By","type":"link","target":"issues","cardinality":"many"}}
{"kind":"column","table":"issues","ref":"blocker_count","spec":{"name":"Blocker Count","type":"rollup","link_field":"blocked_by","aggregation":"count"}}
{"kind":"column","table":"issues","ref":"blocks_count","spec":{"name":"Blocks Count","type":"rollup","direction":"incoming","source":"issues","match":{"blocked_by":"$self"},"aggregation":"count"}}
{"kind":"column","table":"issues","ref":"is_blocked","spec":{"name":"Is Blocked","type":"formula","expression":"[blocker_count] > 0"}}

將完整文件依序送給 iac.plan 與 iac.apply，再以 tables.list 取得七張表與各自的 column_mapping。

2. 先建立 blocker，再寫 self-link

link 儲存的是資料列 ID，因此先建立「Chart rendering engine」，再建立被它擋住的「CSV export」：


POST /private/module/custom_tables/chatroom/11111111-1111-4111-8111-111111111111/tables/22222222-2222-4222-8222-222222222222/records
 
{
  "data": {
    "Title": "Chart rendering engine",
    "Status": "in_progress",
    "Priority": 1,
    "Story Points": 8,
    "Created": "2026-05-02",
    "Labels": ["33333333-3333-4333-8333-333333333333"]
  },
  "created_by_ai": false
}


POST /private/module/custom_tables/chatroom/11111111-1111-4111-8111-111111111111/tables/22222222-2222-4222-8222-222222222222/records
 
{
  "data": {
    "Title": "CSV export",
    "Status": "todo",
    "Priority": 2,
    "Story Points": 3,
    "Blocked By": ["44444444-4444-4444-8444-444444444444"],
    "Assignee": ["55555555-5555-4555-8555-555555555555"],
    "Epic": ["66666666-6666-4666-8666-666666666666"],
    "Sprint": ["77777777-7777-4777-8777-777777777777"]
  },
  "created_by_ai": false
}

3. 讀取完整阻塞脈絡與工作量

單筆讀取加上 expand_links=true，會同時拿到原始 ID cell、可讀的 blocker 資料與計算欄位：


GET /private/module/custom_tables/chatroom/11111111-1111-4111-8111-111111111111/tables/22222222-2222-4222-8222-222222222222/records/88888888-8888-4888-8888-888888888888?expand_links=true

若要從 blocker 反查所有被擋住的工作，使用 records.incomingLinks。要找 Status=done 並按 Priority 排序，則先從 column_mapping 取得內部 key，再呼叫 records.search：


{
  "filters": {
    "col_99999999_9999_4999_8999_999999999999": "done"
  },
  "sort_by": "col_aaaaaaaa_aaaa_4aaa_8aaa_aaaaaaaaaaaa",
  "sort_order": "asc",
  "limit": 100
}

4. 讓前端看懂整張 schema graph

使用 tables.schemaGraph 取得節點與 link、rollup_source、lookup edges：


GET /private/module/custom_tables/chatroom/11111111-1111-4111-8111-111111111111/tables/schema-graph

前端可以把 Issues → Issues 畫成 self-loop，並把 Blocked By、Blocker Count、Blocks Count 三條關係放在同一個節點旁。

使用者會看到什麼

「Scheduled reports」會顯示兩個 blocker、Is Blocked=true、Assignee Name Devon 與 Epic Name Reporting Dashboard。Sprint 頁面直接顯示 Completion 與 At Risk；Epic 頁面則同時顯示進度、工時成本與獲利。主管在聊天室詢問「哪些任務被封鎖、分別被誰封鎖？」時，助理能沿著 self-link 列出完整配對。

變化與下一步

要設計 self-link、incoming rollup 與 lookup，閱讀連結與 rollup 指南。
要建立 Status 的 in filter 或排序，閱讀查詢資料列。
要處理誤刪後的 blocker 與版本回復，閱讀軟刪除與歷程及歷程回復指南。

試試看

先用 IaC 工作台檢查 self-link 的 ref 是否可解析，再用 API Playground呼叫 records.get、records.incomingLinks 與 tables.schemaGraph。