Schema 主表翻轉的 dual-write 過渡:一場不能 stop-the-world 的搬家

引言:當業務主表需要翻轉

某個 SaaS 系統長期以「客戶資料表」為核心:所有訂單、文件、操作紀錄都用客戶 ID 當外鍵。但這個客戶表的資料來源是外部 POS 系統匯入,每天同步幾百筆,schema 由廠商定義。

業務發展後問題浮現:自己 SaaS 的會員表(users)才是真正的「人」 — 有登入、有偏好、有應用內行為。新功能(個人化推薦、訂閱管理、社交綁定)都需要以 users 為主軸。

於是有了一個經典的 schema migration 需求:主表翻轉(primary table pivot)。把業務邏輯的中心從 customers(外部 POS 匯入)轉到 users(SaaS 自家會員),但歷史資料、新進資料、系統相依、回滾風險全部都要顧到。

名詞解釋

開始拆解之前,定義幾個會反覆出現的詞:

為什麼選 dual-write 而非 stop-the-world

最直接的搬家方式是「stop-the-world cut-over」:選一個維護窗口,停寫入、跑 batch script、改完所有 reference、開機。但這個 SaaS 的條件不允許:

選 dual-write 的核心原因不是「成本最低」,是 「失敗代價最低」。Stop-the-world 一旦 batch script 跑到一半某筆資料炸了,整個系統卡住,回滾還要面對「部分轉檔成功、部分失敗」的混亂狀態。Dual-write 過渡期間每筆寫入都同時走新舊兩條路,任何一條失敗都還有另一條當 fallback。

不選會怎樣:見過某團隊選 stop-the-world,預估 1 小時搞定,實際跑 4 小時、跑壞兩個 foreign key constraint、最後 restore from backup 重來。隔天會議檢討整整一週。

Schema 設計:兩個外鍵並存

第一步是讓引用客戶的業務表(姑且叫 documents,可能是訂單、報告、操作紀錄)同時持有兩個外鍵:

{
  "attributes": {
    "customer": {
      "type": "relation",
      "relation": "manyToOne",
      "target": "api::customer.customer"
    },
    "customerUser": {
      "type": "relation",
      "relation": "manyToOne",
      "target": "plugin::users-permissions.user"
    }
  }
}

兩個欄位都 nullable — 過渡期允許「只有舊外鍵、沒有新外鍵」的紀錄存在(很短的搬遷視窗內),也允許「只有新外鍵、沒有舊外鍵」(純 SaaS 註冊的會員從未在外部 POS 出現過的情境)。

users schema 同時加反向關聯,方便 ORM populate:

{
  "attributes": {
    "userType": {
      "type": "enumeration",
      "enum": ["member", "offline"],
      "default": "member"
    },
    "documents": {
      "type": "relation",
      "relation": "oneToMany",
      "target": "api::document.document",
      "mappedBy": "customerUser"
    }
  }
}

userType 區分 SaaS app 註冊的真會員(member)與從外部 POS 匯入但沒對應 SaaS 帳號的客戶(offline,後面會建 pseudo user 給他們)。

flowchart LR
    D[documents] -.legacy.-> C[customers]
    D ==NEW==> U[users]
    U <-.lifecycle sync.-> C

    classDef legacy fill:#f8d7da,stroke:#dc3545,color:#721c24
    classDef primary fill:#cce5ff,stroke:#007bff,color:#004085
    classDef neutral fill:#f5f5f5,stroke:#333,color:#333

    class C legacy
    class U primary
    class D neutral

Migration 三階段腳本

歷史資料怎麼補上 customerUser?拆成三個獨立、各自冪等的腳本。

Phase 1:配對既有 user + 搬欄位

外部客戶資料中有些「真會員」其實也用 SaaS app 註冊過,但兩邊用不同 ID 體系。做 fuzzy match:

const stages = [
  // Stage 1: 姓名 + email + 電話三者匹配(最強)
  (u, c) => normalizeName(u.real_name) === normalizeName(c.name)
    && u.email === c.email
    && u.phone_number === c.phone,
  // Stage 2: 姓名 + email + 生日
  (u, c) => normalizeName(u.real_name) === normalizeName(c.name)
    && u.email === c.email
    && u.birthday === c.birthday,
  // Stage 3-5: 兩兩組合 fallback...
];
// 同 key 多筆 user → 跳過不配(避免誤配對)

為什麼 5 階段? 真實資料髒:email 可能空、電話格式不一、姓名前面有來源系統加的 + 前綴。多階段 + 同 key 多筆跳過 = 寧可不配對也不錯配對。寧願留一筆未配對待 Phase 2 處理,也不要把「李○○ + 0912xxx789」配對到錯誤的 user 身上 — 一旦錯配,後面所有 historical document 都會掛到錯人頭上。

Phase 2:為未配對 customers 建 pseudo user

某些 customers 從未用 SaaS app 註冊(線下走進門市的客人)。如果直接 skip 他們,未來業務查詢路徑會出現「有 customer 沒 user」的孤兒,partial cut-over 時就會 join 不出資料。解法是建 pseudo user:

for (const unpaired of unpairedCustomers) {
  await knex('users').insert({
    email: `customer-${unpaired.dataId}@offline.example.local`,
    username: `customer-${unpaired.dataId}`,
    password: generateRandomPassword(), // 32 byte random,無人會用
    real_name: normalizeName(unpaired.name),
    blocked: true,                       // 永久禁登入
    user_type: 'offline',
    // + 13 個業務欄位 copy from customer
  });
}

關鍵設計:blocked = true + 假 email TLD .local 雙重保險,防止誤寄信、誤讓人登入。userType = 'offline' 讓 query 可以區分真會員 vs pseudo。

Phase 3:backfill 歷史資料的 link

純 SQL,最簡單:

INSERT INTO documents_customerUser_lnk (document_id, user_id)
SELECT d.document_id, link.user_id
FROM documents_customer_lnk d
JOIN customers_users_lnk link ON link.customer_id = d.customer_id
WHERE NOT EXISTS (
  SELECT 1 FROM documents_customerUser_lnk
  WHERE document_id = d.document_id
);

NOT EXISTS 保證冪等。Post-check 斷言 0 orphan:

SELECT COUNT(*) FROM documents_customer_lnk d
WHERE NOT EXISTS (
  SELECT 1 FROM documents_customerUser_lnk
  WHERE document_id = d.document_id
);
-- 預期 = 0;非 0 → 整個 migration abort + dump 問題 IDs

每個 phase 各自獨立 + 冪等:dry-run mode、互動式 yes 確認、跑兩次結果一樣。失敗時可以從中斷點重跑,不需要從頭來。

Lifecycle 持續同步:哪些欄位永不同步

外部 POS 系統一週 5 次匯入新 / 更新的客戶。Migration 不是一次性 — 需要持續同步。在 customers 表加 lifecycle hook:

async function syncToUser(customer) {
  try {
    const user =
      await findExistingLink(customer.id) ??
      await findMatchingUser(customer) ??
      await createPseudoUser(customer);

    await knex('users').where('id', user.id).update({
      data_id: customer.data_id,
      address: customer.address,
      phone_number: customer.phone,
      // ... 13 個業務欄位「覆蓋」
      // 但 NOT notes(見下)
    });
  } catch (err) {
    log.error(`[Sync] FAILED ${customer.id}`, err);
    // 不 rethrow — sync 失敗不能 rollback customer 寫入
  }
}

notes 欄位的「永不同步」規則

customers.notes 跟 users.notes 看起來是同名欄位,但語意不同:

• customers.notes 是外部 POS 系統那邊的客戶備註(門市人員填的)
• users.notes 是 SaaS 自己的後台會員備註(會計、客服填的)

如果無腦同步,會出現以下場景:

6/01 customers.notes = "客人很怕痛"
6/15 一次性 migration 跑完,users.notes = "客人很怕痛"
6/20 SaaS 會計在後台加註:"客人很怕痛 / 6/18已退費 NT$200"
6/25 外部 POS 一週 5 次同步進來,customers.notes 仍是 "客人很怕痛"
     → 如果同步:users.notes 被蓋回 "客人很怕痛"
                  → 會計加的「6/18已退費」消失 ❌

解法:notes 欄位永不從 customers sync 到 users。Phase 1 時搬一次當初始值,之後 user.notes 完全屬於 SaaS 端。

教訓:同名欄位不代表同語意。Migration 設計要逐欄位思考「資料的擁有者是誰」,不是「欄位名相同就 sync」。

API 雙路徑共存:給呼叫端漸進遷移空間

外部呼叫端(前端、Mobile、第三方)都在用舊路徑:

GET /api/documents?filters[customer][id][$eq]=12345

不可能一夜之間全部改完。設計 API 雙路徑:

GET /api/documents?filters[customer][id][$eq]=12345          ← 舊(保留)
GET /api/documents?filters[customerUser][id][$eq]=67890      ← 新(推薦)

兩個都 work、兩個 response shape 一致。呼叫端可以逐個改。

寫入面同樣設計:create / update 收到任一邊(customer 或 customerUser),後端透過 link 表反查另一邊自動補齊:

async function autoFillCustomerPair(data, knex) {
  if (data.customerUser && !data.customer) {
    const link = await findLinkByUserId(data.customerUser, knex);
    if (link) data.customer = link.customer_documentId;
  } else if (data.customer && !data.customerUser) {
    const link = await findLinkByCustomerId(data.customer, knex);
    if (link) data.customerUser = link.user_documentId;
  }
  // 純 SaaS 會員(沒對應 customer)case:customer 欄位保持 NULL,合法
}

新前端只需傳 customerUser,舊前端只傳 customer,兩邊 dual-write 都成功。

Partial cut-over:保留前端 contract,後端內部切讀

過渡期完成後,要把 read 路徑切到新表為主。但前端還沒準備好改 — 怎麼辦?

Partial cut-over = 後端內部走新 link 表查詢,但 response shape 完全不變(前端「以為」沒事發生)。

舊查詢路徑

LEFT JOIN documents_customer_lnk lnk
LEFT JOIN customers c ON c.id = lnk.customer_id
WHERE c.id = :customerId

新查詢路徑(內部多一個 hop)

LEFT JOIN documents_customerUser_lnk dcul              -- ← 新主路徑
LEFT JOIN customers_users_lnk uscl ON uscl.user_id = dcul.user_id
LEFT JOIN customers c ON c.id = uscl.customer_id      -- ← fallback hydrate
WHERE c.id = :customerId

前端 query 不變,response 仍含 customer.id 給 UI 顯示。

DISTINCT ON 處理一對多 link

實戰踩到的坑:少數 customer 在 link 表對應到多個 user(同名同電話的多帳號用戶)。新 join 後同一筆 document 出現 N 倍:

SELECT * FROM (
  SELECT DISTINCT ON (documents.id)
    documents.id, documents.title, c.id as customer_id, ...
  FROM documents
  LEFT JOIN documents_customerUser_lnk dcul ON dcul.document_id = documents.id
  LEFT JOIN customers_users_lnk uscl ON uscl.user_id = dcul.user_id
  LEFT JOIN customers c ON c.id = uscl.customer_id
  WHERE c.id = :customerId
  ORDER BY documents.id    -- DISTINCT ON 要求第一個 ORDER BY 欄位 = 去重欄位
) AS deduped
ORDER BY visit_date DESC, created_at DESC; -- 外層再依正常條件排序

為什麼 DISTINCT ON 而不是 GROUP BY? GROUP BY 需要列出所有 select 欄位,join 來的 doctor / nurse 等欄位要 MAX() 包起來,容易寫錯。DISTINCT ON 只需指定去重欄位,PostgreSQL 自動取每組第一筆,配合 inner ORDER BY 控制「第一筆」是誰,乾淨且明確。

實測 multi-user link 的客戶,raw join 出 26 筆 document,DISTINCT ON 去重後 13 筆,與舊路徑結果完全一致。

踩坑列表

1. 加新 column 不會 backfill 既有 row 的 default

Schema 寫 userType: { default: "member", required: true },但 ORM 跑 ALTER TABLE 只加 column,不會把既有 row 填 default。既有 user 全是 NULL,後續 WHERE user_type IN ('member', 'offline') 全濾掉。

解法:補一條 UPDATE users SET user_type='member' WHERE user_type IS NULL。寫進 migration plan 而非依賴 schema default。

2. ORM 升版改 link table 命名

某 ORM 升版時改了 morph 表名:files_related_morphs → files_related_mph。Phase 1 第一次 execute 全 44 筆失敗。Schema migration 時別假設 ORM 自動生成的表名穩定,先 query 一次 information_schema.tables 確認。

3. ORM 的 link table 沒有 metadata column

很多人會以為 users_customers_lnk 可以塞 match_type、created_at 之類 audit 欄位,但 ORM auto-generated link table 通常只有 3 個 column:id、user_id、customer_id。要塞 metadata 必須改成 explicit relation entity,多一層複雜度。實務上不值得,audit 寫到應用層 log 即可。

4. Migration script 在 K8s pod memory 不足

Pod memory limit 1 GiB,主 ORM runtime 已用 ~800 MiB,跑 migration script 時 spawn 第二個 ORM instance → 撞牆 → 被 cgroup OOM kill,exit 137。

解法:暫時 kubectl patch deploy 把 limit 提到 2 GiB,跑完改回 1 GiB。Prod 通常給 3 GiB+ 不會踩,但 STG 容易撞。

5. Default sort 影響首頁顯示

新查詢預設 ORDER BY id ASC,page 1 都是早期測試帳號(id 4-100,全是空 row),看起來像「資料壞掉」。實際上是排序問題。改成 ORDER BY last_consumption DESC NULLS LAST, id ASC 後,page 1 自動是最近活躍的客戶。

教訓:refactor query 時順手檢查 default sort 是否合理。靜默的 UX 退化最難 debug。

6. Multi-link 重複造成 join 後筆數 N 倍

64 筆 customer 對應到多個 user(pre-existing data quirk,同名同電話的測試資料)。Phase 3 backfill 用 INSERT…SELECT 從 link 表 join,每筆 document 出現多次。靠 DISTINCT ON 去重(前面已述)。

「不砍任何東西」政策

理論上 cut-over 後應該收尾:砍舊欄位、砍 dual-write、砍 legacy API 路徑、砍 customers 表。實務上呢?

老闆某天決定:不砍任何東西。所有 dual-write、雙欄位、雙路徑、customers 表全部永久保留。

理由很現實:

關鍵不是「技術上能不能砍」,是「砍掉之後外部 POS 系統匯入流程要不要重寫」。要重寫 = 牽涉外部廠商 = 排程不可控 = 無限期延後。乾脆永久 dual-write,用「每筆寫入多寫一個 link」的小成本換「不用協調外部廠商」的大價值。

設計反思:技術文章常說「temporary 的東西最持久」。Dual-write 設計上是過渡方案,但實務常變成永久狀態。設計過渡方案時就該把「萬一變永久」也納入考量 — performance overhead 是否可接受?文件能不能說清楚為什麼有兩個欄位?新人 onboard 看到會不會困惑?

結論

主表翻轉是個經典 schema migration 場景。3 個關鍵設計選擇:

1. Dual-write 過渡 vs Stop-the-world:選失敗代價最低的,不是成本最低的
2. Partial cut-over:read 跟 write 不同步切。後端先換內部路徑(response shape 不變),讓前端慢慢來
3. 「不砍」政策:當外部依賴難協調時,永久共存的維護成本通常比一次性收尾的協調成本低

實作面的關鍵技巧:

• 三階段 idempotent migration(搬欄位 / 建 pseudo entity / backfill)+ dry-run + 互動確認
• DISTINCT ON 用 inner subquery 處理 multi-link 重複
• 每個欄位獨立思考「同步 vs 不同步」的擁有權問題(特別是 notes 這種多人寫入的欄位)
• ORM auto-generated 表名 / column 行為要驗證,不要假設

最後要記住:這類 migration 的成功不在「跑完 script」,在「過渡期間每一筆寫入都正確」。Dual-write 過渡看起來複雜,實際上是用結構性的安全網換取每一筆資料的正確性。