資料庫同步的隱藏陷阱：Link Table 的重要性

問題現象：登入成功卻被拒於門外

最近在 Staging 環境遇到一個詭異的問題：使用者登入成功，拿到了有效的 JWT Token，但存取任何需要認證的 API 都回傳 401 Unauthorized。

# 登入成功，拿到 token
POST /api/auth/local → 200 OK
{
  "jwt": "eyJhbGc...xxxxx...your-jwt-token",
  "user": { "id": 1001, "email": "user@example.com" }
}

# 但存取個人資料失敗
GET /api/users/me → 401 Unauthorized

Token 驗證通過、使用者存在、帳號未被封鎖。問題到底在哪？

根本原因：遺失的 Link Table

經過一番追查，發現問題出在資料庫同步時漏掉了關聯表（Link Table）。

什麼是 Link Table？

在關聯式資料庫中，多對多關係需要透過中間表來建立。這個中間表就是 Link Table（也稱為 Junction Table、Join Table、或 Pivot Table）。

使用者與角色的關係：

一個使用者可以有多個角色（User → Roles）
一個角色可以分配給多個使用者（Role → Users）
這是典型的多對多關係

各種 ORM 的 Link Table 命名

不同框架的 Link Table 命名慣例不同，但概念完全相同：

ORM/Framework	Link Table 範例	備註
Django	`user_groups`, `user_permissions`	使用 `_` 連接
Laravel	`role_user`, `permission_role`	字母順序排列
TypeORM	`user_roles_role`	較長的命名
Prisma	`_UserToRole`	以 `_` 開頭
Sequelize	`UserRoles`	駝峰命名

問題的本質：資料不完整

當我們同步資料庫時，通常會注意主要的資料表：

-- 我們記得同步這些表
pg_dump -t users -t roles -t products -t orders ...

但很容易忘記那些「看起來不重要」的 Link Table：

-- 卻忘了這些
pg_dump -t users_roles_lnk -t products_categories_lnk ...

結果：

使用者資料存在 ✅
角色定義存在 ✅
但使用者沒有被分配任何角色 ❌

為什麼這會導致 401？

讓我們看看認證流程：

關鍵差異：

操作	需要角色？	結果
登入 (POST /auth/local)	❌ 不需要	成功
取得個人資料 (GET /users/me)	✅ 需要 Authenticated 角色	失敗
任何 CRUD 操作	✅ 需要角色權限	失敗

大多數系統的權限設計是：有角色才有權限，沒角色等於訪客。

401 vs 403：這其實是 Authorization 問題

這裡有個常見的誤解：看到 401 Unauthorized，直覺會去查 JWT、Token、密鑰設定。但這個案例其實是 Authorization（授權） 問題，不是 Authentication（認證） 問題。

理想的 HTTP 狀態碼設計：

情況	正確狀態碼	意義
沒有 Token	401	請先登入
Token 無效/過期	401	Token 有問題
Token 有效但沒權限	403	已登入但權限不足

但很多系統（包括這次遇到的）把「沒權限」也回傳 401。為什麼？

因為系統把「沒有角色」視為「等同未登入」：

有 Token + 有角色 → 已認證使用者 → 檢查權限
有 Token + 沒角色 → 視為訪客 → 401（你不是「已認證使用者」）

這是一種設計選擇：系統認為「Authenticated」角色是「已登入」的證明，沒有這個角色就不算完成認證流程。從這個角度看，回傳 401 在邏輯上是正確的 — 但對除錯者來說，這個設計隱藏了真正的問題。

如果系統能區分「Token 無效」和「Token 有效但無角色」 (403)，回傳不同的錯誤訊息，除錯會容易很多。

為什麼 ORM Migration 救不了你？

你可能會問：ORM 不是會幫我處理 schema 嗎？

ORM 關注的是 Schema Shape，不是 Data Integrity：

✅ ORM 會檢查：表存在嗎？欄位對嗎？型別正確嗎？
❌ ORM 不會檢查：Link Table 有資料嗎？關係正確嗎？

對 ORM 來說，一張空的 users_roles_lnk 是完全合法的狀態 — 它只是代表「目前沒有使用者被分配角色」。ORM 無法分辨這是「設計如此」還是「同步遺漏」。

這就是為什麼整合測試（Integration Test）才會發現問題，而 migration 和 schema validation 都通過。

如何診斷這個問題

步驟 1：確認使用者存在

SELECT id, email, confirmed, blocked
FROM users
WHERE email = 'user@example.com';

-- 結果：使用者存在且狀態正常
-- | id   | email             | confirmed | blocked |
-- | 1001 | user@example.com  | true      | false   |

步驟 2：檢查角色分配

-- 查詢使用者的角色
SELECT u.id, u.email, r.name as role_name
FROM users u
LEFT JOIN users_roles_lnk lnk ON u.id = lnk.user_id
LEFT JOIN roles r ON lnk.role_id = r.id
WHERE u.email = 'user@example.com';

-- 如果 role_name 是 NULL，問題就在這裡！

步驟 3：統計受影響的使用者

-- 找出所有沒有角色的使用者
SELECT COUNT(*) as users_without_roles
FROM users u
LEFT JOIN users_roles_lnk lnk ON u.id = lnk.user_id
WHERE lnk.role_id IS NULL;

-- 我們的案例：3,371 個使用者沒有角色！

解決方案

修復：批次分配角色

確認問題後，修復其實很簡單：

-- 找出 Authenticated 角色的 ID
SELECT id FROM roles WHERE type = 'authenticated';
-- 假設是 1

-- 為所有沒有角色的使用者分配 Authenticated 角色
INSERT INTO users_roles_lnk (user_id, role_id, user_ord)
SELECT u.id, 1, 1
FROM users u
LEFT JOIN users_roles_lnk lnk ON u.id = lnk.user_id
WHERE lnk.role_id IS NULL
ON CONFLICT DO NOTHING;

預防：Bootstrap 自動檢查

在應用程式啟動時自動檢測並修復：

// 應用程式啟動時執行
async function ensureUserRoles() {
  // 找出沒有角色的使用者
  const usersWithoutRoles = await db.raw(`
    SELECT u.id FROM users u
    LEFT JOIN users_roles_lnk lnk ON u.id = lnk.user_id
    WHERE lnk.role_id IS NULL
  `);

  if (usersWithoutRoles.length > 0) {
    console.warn(`Found ${usersWithoutRoles.length} users without roles`);

    // 取得預設角色 ID
    const defaultRole = await db('roles')
      .where({ type: 'authenticated' })
      .first();

    // 批次分配角色
    const values = usersWithoutRoles
      .map(u => `(${u.id}, ${defaultRole.id}, 1)`)
      .join(', ');

    await db.raw(`
      INSERT INTO users_roles_lnk (user_id, role_id, user_ord)
      VALUES ${values}
      ON CONFLICT DO NOTHING
    `);

    console.info(`Assigned default role to ${usersWithoutRoles.length} users`);
  }
}

資料庫同步 Checklist

未來進行資料庫同步時，請確認：

## 同步前檢查

- [ ] 列出所有需要同步的表（包含 Link Table）
- [ ] 確認外鍵關係完整
- [ ] 備份目標環境資料

## 同步後驗證

- [ ] 檢查 users 表筆數
- [ ] 檢查 Link Table 筆數（應該 > 0）
- [ ] 執行整合測試（登入 + API 存取）
- [ ] 抽查使用者權限是否正常

如果沒發現這個問題會怎樣？

讓我們模擬一下「這個問題上到 Production」的災難情境：

📅 Day 1
- 資料庫同步完成，看起來一切正常
- 新使用者註冊正常（會自動分配角色）
- QA 測試用的是新建帳號 ✅

📅 Day 3
- 舊使用者開始抱怨：「我登入成功但什麼都看不到」
- 客服以為是個案，請使用者清快取、重新登入
- 問題回報增加，但登入功能確實正常 🤔

📅 Day 5
- 發現 3,000+ 使用者都有問題
- 工程師開始查 JWT、Token、密鑰設定
- 一直找不到問題（因為方向錯了）

📅 Day 7
- 終於有人想到查資料庫權限
- 發現 Link Table 是空的
- 緊急修復，但商譽已受損 💸

最可怕的是： 這種問題很難被自動化測試發現，因為：

單元測試不會跨表查詢
整合測試通常用新建的測試資料
舊使用者的狀態沒人測

Link Table：資料庫的暗物質

我喜歡把 Link Table 比喻為「資料庫的暗物質（Dark Matter）」：

特性	業務資料表	Link Table
有後台管理介面	✅	❌
有 API 直接操作	✅	❌
會出現在 Log	✅	很少
被人記得存在	✅	❌
影響系統運作	✅	✅✅✅

Link Table 的應用遠不只 User-Role：

RBAC（Role-Based Access Control）權限系統：users_roles_lnk, roles_permissions_lnk
標籤系統：posts_tags_lnk, products_categories_lnk
Feature Flag：users_features_lnk
A/B Test：users_experiments_lnk

這些表沒有自己的 UI，沒有獨立的 CRUD API，但它們決定了「誰可以做什麼」、「誰看到什麼」。

結論

這個問題表面上是「401 認證失敗」，實際上是資料庫同步不完整。Link Table 雖然「看起來不重要」，卻是權限系統的關鍵。

核心教訓：

Link Table 是隱形的關鍵 — 它們沒有業務邏輯，但定義了實體間的關係
同步時要思考「關係」而非「表」 — 不只是同步資料，更要同步資料間的關聯
防禦性程式設計 — 在應用層加入自動檢測和修復機制

這次問題在 Staging 環境被發現，讓我們有機會在上線前修正。下次遇到「登入成功但 API 失敗」的詭異問題時，記得檢查使用者是否真的有被分配角色。那個看起來無害的 Link Table，可能就是問題的根源。

延伸思考

如果你的系統把角色資訊 cache 在 Redis、JWT claim、或 BFF 層，當 Link Table 資料不正確時：

系統應該立即全站拒絕？
還是繼續相信 cache？
或是進入 degraded mode？

這已經不只是資料庫問題，而是系統可信度設計的議題了。

👉 我在下一篇文章分享了我的選擇：當授權資料不可信時，我選擇讓系統安靜地退後一步

系列文章

資料庫同步的隱藏陷阱：Link Table 的重要性（本文）
當授權資料不可信時，我選擇讓系統安靜地退後一步

問題現象：登入成功卻被拒於門外#

根本原因：遺失的 Link Table#

什麼是 Link Table？#

各種 ORM 的 Link Table 命名#

問題的本質：資料不完整#

為什麼這會導致 401？#

401 vs 403：這其實是 Authorization 問題#

為什麼 ORM Migration 救不了你？#

如何診斷這個問題#

步驟 1：確認使用者存在#

步驟 2：檢查角色分配#

步驟 3：統計受影響的使用者#

解決方案#

修復：批次分配角色#

預防：Bootstrap 自動檢查#

資料庫同步 Checklist#

如果沒發現這個問題會怎樣？#

Link Table：資料庫的暗物質#

結論#

延伸思考#

系列文章#