SQLite WAL Snapshot 同步（二）：Node.js 實作、測試與維運

Created: 2026-08-24

日期：2026-08-24

環境：macOS、APFS、Node.js、SQLite WAL、加密 SQLite CLI

---

TL;DR

候選 snapshot 必須先完成 single-flight 同步、來源／副本 SHA-256、實際 SQLite 開庫與核心查詢驗證，再進入 active namespace。Freshness-sensitive API 在同步失敗時應保留 last-known-good snapshot 供調查，但一般資料查詢必須 fail closed，避免把 stale data 當成最新事實。

本篇延續第一篇的一致性模型，集中說明 Node.js 實作、狀態機、restart loader、測試矩陣、容量維運與安全邊界。

---

目錄

1. Fail-closed 狀態模型
2. Single-flight 併發控制
3. Restart snapshot loader
4. 測試策略
5. 量化驗證
6. 邊界與陷阱
7. 維運與 retention
8. API 安全與隱私
9. 漸進修正與完成條件

---

背景

第一篇建立的 candidate protocol 只解決「如何判斷副本可接受」。完整服務還必須回答：多個 request 同時到達時誰負責同步、process restart 後如何找回 active snapshot、同步失敗能否回舊資料、錯誤如何暴露，以及 append-only snapshot 如何避免變成新的磁碟風險。

---

Fail-closed 設計

問題

同步器通常已經有一份舊 snapshot。

當新同步失敗時，最直覺的行為是回舊資料。

這對一般 dashboard 可能合理。

對「幫 agent 讀最新訊息」則危險。

Agent 無法從正常的 200 OK 分辨：

• 確實沒有新資料。
• 同步失敗所以只看到舊資料。

正確契約

同步失敗時：

• 保留舊 snapshot。
• 不把失敗 candidate 升級為 active。
• 一般資料 query 回錯誤。
• status endpoint 保持可用。
• lastError 顯示原因。
• 下一次 query 自動 retry。

狀態模型

FALLBACK
  |
  | first successful sync
  v
SNAPSHOT_CURRENT
  |
  | source changed
  v
SYNCING
  |            |
  | success    | failure
  v            v
SNAPSHOT_NEW  STALE_BLOCKED
                  |
                  | next query retry
                  v
                SYNCING

Status endpoint

建議回傳：

{
  "enabled": true,
  "activeKind": "snapshot",
  "snapshotName": "snapshot-<timestamp>-<suffix>",
  "lastAttemptAt": "<ISO timestamp>",
  "lastSyncedAt": "<ISO timestamp>",
  "sourceSignature": "<opaque signature>",
  "lastError": null
}

不要回傳：

• source absolute path。
• database key。
• API token。
• message content。
• candidate directory 裡的敏感 metadata。

---

併發控制

同一時間可能有多個 API request。

如果每個 request 都各自同步：

• 會重複 clone。
• 會重複 hash。
• 會產生多份同版本 snapshot。
• active 切換順序變得不明確。

使用 single-flight promise：

class Synchronizer {
  #inFlight: Promise<string> | null = null;

  async ensureFresh(): Promise<string> {
    if (this.#inFlight !== null) {
      return await this.#inFlight;
    }

    this.#inFlight = this.#refresh().finally(() => {
      this.#inFlight = null;
    });

    return await this.#inFlight;
  }
}

這保證同一 process 內：

• 一次只有一個 refresh。
• 其他 request 等待相同結果。
• 成功後共用同一 active path。
• 失敗後所有等待者看到一致錯誤。

多 process 部署則還需要跨 process coordination。

可選方案：

• lock file with exclusive create。
• OS advisory lock。
• 單獨 snapshot service。
• 將同步責任固定給一個 leader。

不要輕率實作 stale lock 自動刪除。

錯刪活 lock 會重新引入競態。

---

Snapshot loader

Process restart 時需要找回最近有效 snapshot。

Loader 規則：

1. 只掃描 snapshot- prefix。
2. 忽略 .incoming。
3. 依不可變名稱排序。
4. 要求 snapshot.json 存在。
5. 要求主 DB 存在。
6. metadata JSON 必須可解析。
7. 第一個完整候選成為 active。
8. 啟動後仍執行一次 ensureFresh()。

Snapshot metadata 範例：

{
  "createdAt": "2026-08-24T12:00:00.000Z",
  "sourceSignature": "<opaque>",
  "databaseSha256": "<sha256>",
  "walSha256": "<sha256>"
}

Metadata 寫入使用 exclusive create。

正式目錄名稱也必須唯一。

不要把「目錄存在」當作成功標記。

---

測試策略

測試一：來源不變

步驟：

1. 建立 source DB fixture。
2. 建立 source WAL fixture。
3. initialize synchronizer。
4. 再呼叫一次 ensureFresh()。

驗證：

• 回傳同一路徑。
• validator 只執行一次。
• snapshot count 是 1。
• lastError 是 null。

測試二：WAL 改變

步驟：

1. 建立第一份有效 snapshot。
2. 改寫 WAL fixture。
3. 再呼叫 ensureFresh()。

驗證：

• active path 改變。
• snapshot count 從 1 變 2。
• 第一份 snapshot 仍存在。
• 新 metadata signature 不同。

測試三：Copy 中途變動

在 injected copier 完成第一個 copy 後，修改 source WAL。

驗證：

• S0 != S1。
• candidate 不升級。
• active 仍是 fallback 或上一份 snapshot。
• lastError 明確指出 copy race。

測試四：Hash 不符

在 destination 寫入不同 bytes。

驗證：

• validator 不執行。
• candidate 不升級。
• query fail closed。

測試五：Validator 失敗

模擬：

• wrong key。
• malformed WAL。
• 缺少核心表。
• CLI exit non-zero。
• CLI 回傳 invalid JSON。

驗證：

• active 不切換。
• lastError 不包含 secret。
• 下一次 request 仍可 retry。

測試六：Concurrent query

同時送出多個 request。

驗證：

• copier 只執行一次。
• validator 只執行一次。
• 所有成功 request 使用同一 snapshot。
• snapshot count 只增加 1。

測試七：Restart recovery

先建立兩份 snapshot，再建立新 synchronizer。

驗證：

• loader 選最新完整 snapshot。
• incomplete .incoming 被忽略。
• source 未變時不再 clone。

---

量化驗證

一組去識別化實測數據：

指標	Before	After
主 DB	約 70 MiB	約 70 MiB clone
WAL	約 1.8 MiB	約 2.0 MiB
Snapshot count	1	2
可見 message rows	120,701	120,703
Unit tests	10	14
Sync error	N/A	null
原始主 DB hash	基準值	相同

控制組

來源 signature 未變時連續查詢：

snapshots_before=1
snapshots_after=1

實驗組

來源 WAL 增長後執行下一次 query：

snapshots_before=1
snapshots_after=2
active_kind=snapshot
last_error=null

API 成功讀到基準點以後新增的 row。

Gate

typecheck: exit 0
lint: exit 0
tests: 14/14
build: exit 0
format: exit 0
real snapshot smoke: exit 0

這些數字只能證明該環境與該 workload。

它們不能證明所有 SQLite WAL database 都能用同樣延遲同步。

---

邊界與陷阱

⚠️ WAL reset

WAL 不一定永遠 append。

Checkpoint 完成後可能從頭覆寫或 reset。

所以不能只比較 WAL size 是否增加。

必須把 inode、mtime 與 size 一起看。

⚠️ WAL 消失

最後一個 connection 關閉時，SQLite 可能 checkpoint 並移除 WAL/SHM。

同步器必須接受：

wal:present -> wal:none

這也是 source signature 變動。

⚠️ DB 被替換

相同路徑可能指向新 inode。

只比較 path 或 size 會漏掉。

⚠️ Network filesystem

SQLite 官方說明 WAL 需要同一 host 上的 shared memory coordination。

不要把本機 WAL 同步方案直接搬到 NFS/SMB。

⚠️ Read-only 不等於 immutable

SQLite URI 的 immutable=1 是強假設。

如果來源其實會改，錯用 immutable 可能忽略 WAL 或 locking reality。

⚠️ copyFile() 非原子

Node.js 文件明確指出 copy operation 沒有 atomicity 保證。

所以必須：

• destination 唯一。
• copy 完再驗證。
• 驗證完再 publish。

⚠️ Candidate 累積

Failing candidate 若不刪除會占用空間。

自動刪除又是破壞性操作。

可選策略：

• 先保留並監控。
• 定期人工審核。
• 明確授權 retention policy。
• 只刪除可證明由同步器建立且非 active 的 candidate。

⚠️ Snapshot 累積

Append-only 的優點：

• 可稽核。
• 可 rollback。
• 不覆寫證據。

缺點：

• metadata 數量增加。
• WAL copy 持續占空間。
• APFS clone 的共享 extent 仍非零成本。

⚠️ Disk pressure

低磁碟空間下常見假象：

• copy 截斷。
• build 失敗。
• Git object 寫入失敗。
• test 出現不相關錯誤。

同步前應先觀察 disk free space。

不要在沒有授權下自動刪除其他專案資料。

⚠️ 附件不是 row

訊息 row 可能只有：

• content type。
• object ID。
• size。
• expiry。
• metadata JSON。

這不表示附件 bytes 已在本機。

API 應回：

metadata-only
cached
unavailable

不要把 metadata-only 呈現成已看見圖片或檔案。

---

維運設計

最少監控欄位

欄位	用途
lastAttemptAt	最近一次 freshness check
lastSyncedAt	最近一次成功切換
lastError	是否 stale-blocked
snapshotName	active version
sourceSignature	是否觀察到來源變動
snapshot count	容量趨勢
incoming count	race 或 validation failure 趨勢
disk free	避免磁碟壓力假錯

告警條件

• lastError 持續非 null。
• lastAttemptAt - lastSyncedAt 超過容許範圍。
• incoming count 持續增加。
• snapshot count 超過人工門檻。
• disk free 低於安全門檻。
• source WAL 長期成長沒有 reset。
• validator latency 異常增加。

Retention 決策票

實作自動刪除前要回答：

1. 保留最近幾份？
2. 保留多久？
3. active snapshot 永遠不可刪？
4. 前一份 known-good 是否必留？
5. incomplete candidate 可否刪？
6. 有沒有 legal hold？
7. 刪除是否要留 audit log？
8. APFS clone 的實際 allocated size 如何量測？

在答案不明時，append-only 是較保守的預設。

---

安全與隱私

API surface

推薦：

• 僅綁 loopback。
• /v1/* 需要 bearer token。
• token 比較使用 timing-safe comparison。
• 不提供 arbitrary SQL。
• 不提供 write route。
• limit 有上限。
• query parameter 先過 schema validation。

SQL injection boundary

所有 API query 應是固定 SQL template。

使用者文字不要直接插入 SQL。

若 CLI 無 parameter binding，可把 UTF-8 轉成 hex blob literal：

function sqlText(value: string): string {
  const hex = Buffer.from(value, "utf8").toString("hex");
  return `CAST(X'${hex}' AS TEXT)`;
}

數值參數必須先經 integer schema validation。

仍需拒絕多 statement。

Secret scan

Commit 前執行：

exact database key matches in trackable files = 0
exact API token matches in trackable files = 0

不要輸出 secret 本身。

只輸出 match count。

Log hygiene

不要記錄：

• Message text。
• Chat ID。
• Contact ID。
• Absolute source path。
• Token。
• Key。
• Full SQL if it contains user data。

可以記錄：

• Snapshot opaque name。
• Counts。
• Duration。
• Exit code。
• Sanitized error category。
• Source signature 的非定位版本。

---

漸進修正

第一版：只複製主 DB

copy database.db snapshot.db

❌ 問題：最近 commit 可能只在 WAL。

第二版：複製 DB＋WAL

copy database.db snapshot.db
copy database.db-wal snapshot.db-wal

❌ 問題：兩次 copy 間 source 可能變動。

第三版：前後 stat

S0 = stat source
copy DB + WAL
S1 = stat source
accept if S0 == S1

⚠️ 改善：可偵測多數 copy race。

❌ 問題：仍未證明 destination bytes 等於 source。

第四版：加入 SHA-256

S0
copy
S1
hash source + copy
S2
accept if signatures and hashes match

⚠️ 改善：建立內容等同性證據。

❌ 問題：副本仍可能無法被 SQLite 正確解讀。

第五版：實際開庫驗證

validate sqlite_master
validate core table
validate business count

✅ 改善：候選必須能實際查詢。

第六版：版本化 publish

.incoming/candidate
  -> validate
  -> rename snapshot-<version>
  -> switch active

✅ 改善：不完整 candidate 永遠不可見。

第七版：Fail closed

sync failure
  -> keep last good snapshot
  -> block normal query
  -> expose sanitized status
  -> retry next query

✅ 改善：消除 silent stale success。

---

完成條件

一個可宣稱完成的同步器至少要有：

• Source DB/WAL dirty detection。
• Unique candidate directory。
• DB 與 WAL 一起處理。
• 複製前後 signature 比較。
• Source/copy SHA-256 比較。
• SQLite 實際 open/query validation。
• Candidate publish boundary。
• Single-flight concurrency control。
• Restart loader。
• Fail-closed freshness semantics。
• Authenticated status endpoint。
• No arbitrary SQL。
• Secret exact-match scan。
• Real writer-change test。
• Source DB hash before/after evidence。
• Disk capacity observation。
• Retention boundary documented。

只完成「copy command exit 0」不算完成。

只完成「可以打開 DB」也不算完成。

真正的完成是：

來源發生可觀察的新 commit，下一次 API query 自動建立並切換新 snapshot，讀到新增 row，且來源資料未被同步器改動。

---

重點速查

• WAL 是 database persistent state 的一部分，不是可任意丟棄的 cache。
• 主 DB hash 沒變，不代表沒有新 commit。
• SHM 是 transient wal-index，通常應讓隔離副本自行重建。
• Filesystem clone 降低成本，但不提供跨檔一致性。
• Stat signature 適合 dirty check，不足以單獨證明內容完整。
• SHA-256 證明來源／副本 bytes 相同，不證明 DB 邏輯可查。
• 最終一定要實際 open/query 副本。
• Candidate 通過全部驗證以前不得進入 active namespace。
• Freshness-sensitive API 同步失敗時應 fail closed。
• Query-time refresh 可避免無查詢時的背景 I/O 與 snapshot 爆量。
• Single-flight 避免同一 process 重複同步。
• Append-only 是保守預設，但 retention 必須有明確授權。
• 能使用 SQLite Online Backup API 時，優先使用官方原生方案。
• 本文方法適合「不能以 SQLite 開啟來源」的特殊隔離需求。

---

更大的圖景

資料同步的核心不是「把檔案搬過去」，而是定義可驗證的發布條件。來源觀察、候選建立、內容驗證、語意驗證與 active 切換，是五個不同的責任；把它們壓成一條 cp 指令，只是把一致性風險藏起來。

同一套模式也適用於其他 immutable artifact pipeline：下載模型、匯入索引、產生報表、更新規則庫。共同原則是 candidate 永遠不等於 release；只有通過內容與語意驗證的 candidate，才有資格被原子地發布給讀者。

對 Agent 系統尤其如此。Agent 會把 API 的正常回應視為事實。如果系統無法區分「真的沒有新資料」與「同步失敗所以只剩舊資料」，再好的推理也只是在錯誤前提上工作。Freshness 必須成為 API contract，而不是藏在維運文件裡的期望。

---

參考資料

• SQLite Write-Ahead Logging — SQLite 官方 WAL、checkpoint、read concurrency、WAL/SHM 與 read-only 條件說明。
• SQLite: How To Corrupt An SQLite Database File — 官方列出 transaction 期間複製、DB/journal 配對錯誤與 rename open DB 的風險。
• SQLite Backup API — 官方 live database consistent snapshot 方案；能開來源 connection 時應優先評估。
• SQLite wal.c source — 官方 Git mirror 的 WAL frame、wal-index、SHM transient 性質與 reader end mark 實作註解。
• Node.js File System API — COPYFILE_FICLONE、fallback 行為，以及 copyFile() 不保證 atomicity。
• Apple APFS Tools and APIs — APFS clonefile、COPYFILE_CLONE 與 safe-save API 概覽。
• NIST FIPS 180-4 Secure Hash Standard — SHA-256 所屬 Secure Hash Standard。

---

上一篇：WAL 機制與一致性設計