# SAD-003　資料架構與 CQRS

| 項目 | 內容 |
|---|---|
| 文件編號 | SAD-003 |
| 版本 | 1.2 |
| 狀態 | 已核定 |
| 撰寫者 | SA / DA |
| 審查者 | TL / SEC |
| 對應需求 | SYS-2.1、SYS-2.4、SYS-2.5、SYS-2.12；POC-14 |

---

## 1. 資料架構原則

| 編號 | 原則 | 說明 |
|---|---|---|
| DP-01 | **一服務一 schema** | 每個服務擁有自己的 schema，僅該服務可寫入。權威 OLTP schema 位於 **Oracle**（`CC_<SERVICE>`）；CQRS 讀模型、事件流存檔與報表 schema 位於 **PostgreSQL**（`proj`／`evt`／`rpt`）。落點分工見 `CSP-006 §2.4` |
| DP-02 | **權威資料單一來源** | 每張表只有一個服務為權威寫入者 |
| DP-03 | **金額為整數最小單位** | 一律以「分」儲存，**禁止浮點數**（`BRD-001 C-01`）。Oracle 用 `NUMBER(19,0)`，PostgreSQL 用 `bigint` |
| DP-04 | **原始紀錄不可變** | 交易紀錄不得 UPDATE／DELETE，僅能以沖正處理（`C-02`） |
| DP-05 | **前後值留存** | 餘額異動須記錄 Before／After Image |
| DP-06 | **個資加密與遮罩** | 卡號、身分證字號加密儲存、遮罩顯示（`SAD-005 §5`） |
| DP-07 | **時間為 UTC** | 資料庫以含時區型別儲存（Oracle `TIMESTAMP WITH TIME ZONE`／PostgreSQL `timestamptz`），顯示時轉換為本地時區 |
| DP-08 | **事件為跨服務唯一傳播方式** | 服務間資料同步以事件為之，非共用資料庫 |

---

## 2. 資料分類與儲存策略

| 資料類別 | 特性 | 儲存 | 一致性要求 |
|---|---|---|---|
| 主檔（持卡人、卡片、產品、商戶） | 讀多寫少 | 寫庫 + 讀庫 | 強一致（單庫交易） |
| 交易（授權、帳務、繳款） | 寫多、需稽核 | 寫庫（append-only） | 強一致（單庫交易） |
| 餘額（額度、帳務） | 高頻讀寫 | 寫庫 + 快取 | 強一致 + 快取失效 |
| 查詢視圖（明細、報表） | 讀多寫少 | 讀庫（CQRS） | 最終一致（≤ 1 秒） |
| 參數（產品、費率、規則） | 讀極多寫少 | 寫庫 + 快取 + 版本 | 強一致 + 版本控管 |
| 事件（領域事件） | 只增不改 | 事件流 + Outbox | 至少一次 + 冪等消費 |
| 稽核軌跡 | 只增不改 | 稽核表 + 不可竄改 | 強一致 |
| 日誌 | 只增 | 日誌儲存 | 最終一致 |

---

## 3. 資料庫部署架構

> **v1.1 變更**：權威 OLTP 由 PostgreSQL 改為 **Oracle**（客戶指定），
> PostgreSQL 轉為承擔 **CQRS 讀模型、事件流存檔與報表分析**。落點分工見 `CSP-006 §2.4`。

```mermaid
flowchart TD
  subgraph APP["應用層"]
    W1["寫入路徑<br/>Command Handler"]
    R1["查詢路徑<br/>Query Handler"]
  end
  subgraph ORA["Oracle — 權威 OLTP"]
    OP["Primary<br/>CC_SERVICE 各服務 schema<br/>（含該服務之 Outbox）"]
    OPLT["CC_PLT<br/>Saga 狀態"]
    OS["Standby<br/>Data Guard 同步複寫"]
  end
  subgraph PG["PostgreSQL — 讀側"]
    RA1["proj<br/>CQRS 讀模型"]
    RA2["evt<br/>事件流存檔"]
    RA3["rpt<br/>報表分析"]
  end
  subgraph EVT["事件層"]
    KF["Kafka"]
    PROJ["投影器 Projector"]
  end
  CA["Redis 快取"]
  W1 --> OP
  W1 --> OPLT
  OP --> OS
  OPLT --> KF
  KF --> PROJ
  KF --> RA2
  PROJ --> RA1
  R1 --> RA1
  R1 --> RA3
  R1 --> CA
  W1 --> CA
```

> **Outbox 位置之關鍵約束**：Outbox 表建於**每個服務自己的 schema 內**（`CC_<SERVICE>.outbox`），
> 依 `SDD-001 §9`「通用平台表（每服務各一份）」。如此「業務資料寫入」與「事件寫入」
> 可於**同一 schema、同一資料庫交易**內完成（SAD-001 AP-05、SDD-004 §4.3），
> 且不違反 DP-01「僅該服務可寫入自己 schema」。
>
> **不得**將 Outbox 集中至共用 schema（違反 DP-01），亦**不得**移至 PostgreSQL
> （跨庫即無法保證原子性）。`CC_PLT` 僅承載跨服務之 **Saga 協調狀態**（`SDD-004 §5.3`）。

### 3.1 讀寫分離策略

| 面向 | 寫入路徑 | 讀取路徑 |
|---|---|---|
| 資料庫 | **Oracle**（Primary） | **PostgreSQL**（`proj`／`rpt`） |
| 資料來源 | 服務自身 schema + `CC_PLT` | CQRS 投影視圖、報表視圖 |
| 一致性 | 強一致（Oracle 單庫交易） | 最終一致（≤ 1 秒） |
| 用途 | 授權佔用、入帳、狀態變更 | 餘額查詢、明細查詢、報表 |
| 併發控制 | 樂觀鎖（version）+ 資料庫鎖 | 無鎖 |
| 失敗處理 | 交易回滾 | 重試或回退至 Oracle 即時查詢 |
| 高可用 | Data Guard Standby | 串流複寫 Replica |

> **設計意涵**：讀路徑**完全不碰 Oracle**，故分析型與高頻查詢不佔用 Oracle 授權；
> 寫路徑維持 Oracle 之強一致與交易保證。此為本案「Oracle 主 + PostgreSQL 讀側」之核心價值。

### 3.2 同步機制（對應 POC-14）

```mermaid
sequenceDiagram
  participant CMD as 命令處理器
  participant DB as Oracle 寫入庫<br/>服務 schema
  participant OBX as Outbox<br/>同服務 schema
  participant KFK as Kafka
  participant PRJ as 投影器
  participant RDB as PostgreSQL 讀取庫<br/>proj
  participant API as 查詢 API

  CMD->>DB: 交易開始
  CMD->>DB: 更新餘額（樂觀鎖）
  CMD->>OBX: 寫入領域事件（同一交易）
  CMD->>DB: 交易提交
  Note over DB,OBX: 同一 Oracle 交易，資料與事件同時成立（不可分離）
  OBX->>KFK: 發送事件（至少一次）
  KFK->>PRJ: 消費事件（分區內有序）
  PRJ->>RDB: 更新查詢視圖（冪等）
  API->>RDB: 查詢餘額與明細
  Note over CMD,API: 端到端延遲目標 ≤ 1 秒
```

**保證**：
| 保證 | 機制 |
|---|---|
| 事件不遺失 | Outbox 與業務資料同一交易寫入 |
| 事件不重複生效 | 消費者以 `event_id` 冪等 |
| 順序正確 | 以 `aggregate_id` 為 Kafka 分區鍵 |
| 斷線可補 | 消費者 offset 由 Kafka 管理，恢復後續傳 |
| 可追蹤 | 事件帶 `trace_id`、`causation_id` |

---

## 4. CQRS 投影設計

### 4.1 跨服務投影清單（★ `CR-030 §2 #6` 裁定後改寫）

> **原表之 7 個 `q_*` 已全數廢除**（2026-10-01，`CR-030 §2 #6`）。
>
> **廢除理由（P1／P2）**：原 7 列**逐列比對後 7／7 皆為「自投影」**——「來源事件」之生產者
> 與「服務」欄（存放地）**為同一服務**：
>
> | 原視圖 | 來源事件 | 生產者 | 存放地 | 判定 |
> |---|---|---|---|---|
> | `q_account_balance` | `posting.balance.changed.v1` | posting | posting | **自投影** |
> | `q_available_limit` | `limit.limit.adjusted.v1`、`limit.hold.*` | limit | limit | **自投影** |
> | `q_transaction_history` | `posting.transaction.posted.v1` | posting | posting | **自投影** |
> | `q_authorization_history` | `authorization.*` | authorization | authorization | **自投影** |
> | `q_points_balance` | `rewards.points.*` | rewards | rewards | **自投影** |
> | `q_statement_summary` | `statement.statement.issued.v1` | statement | statement | **自投影** |
> | `q_merchant_settlement` | `merchant.settlement.submitted.v1` | merchant | merchant | **自投影** |
>
> **自投影不是 CQRS 讀模型**：它只是把**權威表複製一份**到讀側，帶來**雙寫、投影延遲、一致性**三重成本，
> 而**零收益**（權威表本即可直接查詢），且與權威表同名同義易誤用。
> **CQRS 讀模型之存在價值在「跨服務查詢」**——避免同步呼叫扇出（fan-out）與跨庫 join。
>
> **業界依據**：CQRS 之 read model 是「**為查詢而生的另一種視圖**」；同構複製不構成 read model。

**現行跨服務投影清單**（**由實際需求驅動**；`SAD-004 §5.2` 為事件之權威清單）

| 投影 | **存放地** | 餵養事件（**生產者 ≠ 存放地**） | 用途 | 依據 |
|---|---|---|---|---|
| `card_status_caches` | `authorization` | `card.card.status-changed.v1`（card） | 授權時免同步查卡況 | `#A-117`／`#A-118` |
| `account_limit_caches` | `authorization` | `limit.limit.adjusted.v1`（limit） | 授權時免同步查額度 | `#A-119` |
| `limit_holds`（本地投影） | `authorization` | `limit.hold.created.v1`／`.released.v1`（limit） | 佔用狀態本地可見（含 stand-in 對帳） | `#A-128` |

> ⚠️ **本清單之新增須經規格**（`CSP-001 §5` 條件 9：無 SCS 不寫程式），
> 並以「**生產者 ≠ 存放地**」為**必要條件**——否則即為自投影，**不應建立**。
> **後續候選**（待規格）：`party` 之卡片狀態投影（`CR-029 §4 #3`）、各服務之**參數快取**（`CR-029 §4 #4`）。

### 4.1.1 原表保留之歷史說明

> `SAD-003 §3.2` 之「同步機制（對應 POC-14）」與 `§4.2`「投影規則」、`§4.3`「投影延遲監控」**仍然有效**，
> 惟其適用對象為**本節所列之跨服務投影**（非自投影）。

### 4.2 投影規則

| 規則 | 說明 |
|---|---|
| 冪等 | 以 `event_id` 去重，重複事件直接略過 |
| 有序 | 同一聚合之事件依 `sequence_no` 套用；亂序則暫存等待 |
| 可重建 | 視圖可自事件流全量重建（`rebuild` 命令） |
| 可監控 | 投影延遲（event time → apply time）為監控指標 |
| 不可回寫 | 投影視圖**僅供查詢**，不得作為寫入來源 |

### 4.3 投影延遲監控

| 指標 | 目標 | 告警 |
|---|---|---|
| `projection_lag_seconds`（P95） | ≤ 1 秒 | > 2 秒 |
| `projection_lag_seconds`（P99） | ≤ 3 秒 | > 5 秒 |
| `projection_failed_total` | 0 | > 0 |
| `consumer_lag`（Kafka） | ≤ 1,000 | > 10,000 |

---

## 5. 分庫分表策略

### 5.1 分表（Partitioning）

| 表 | 分表鍵 | 策略 | 理由 |
|---|---|---|---|
| `posting.transactions` | `posted_at` | 月分區 | 量大、查詢多以時間範圍 |
| `authorization.authorizations` | `created_at` | 月分區 | 量大、生命週期短 |
| `posting.balance_history` | `account_id` | 雜湊分區 | 查詢以帳戶為主 |
| `statement.statements` | `statement_month` | 月分區 | 週期性產生 |
| `notification.notifications` | `created_at` | 月分區 | 量大 |
| `audit.audit_trails` | `created_at` | 月分區 | 量大、需歸檔 |

> **方言對照**：Oracle 以 `PARTITION BY RANGE (...)`／`PARTITION BY HASH (...)` 實作；
> PostgreSQL 以 `PARTITION BY RANGE`／`HASH` 實作。Oracle 之「雜湊分區」需注意
> 分區數須為 2 的次方以達均勻分布（`PARTITION BY HASH (account_id) PARTITIONS 16`）。

### 5.2 分庫（Sharding）

| 階段 | 策略 |
|---|---|
| 第一階段 | **不分庫**，以讀寫分離與分表達成 ≥ 1,000 TPS |
| 擴充階段 | 若需 > 2,000 TPS，以 `account_id` 雜湊分庫（4 庫起） |
| 分片鍵選擇 | `account_id`（歸戶），確保同一歸戶之交易同庫 |

> **決策**：`ADR-11` 第一階段不分庫。理由：分庫將使跨帳戶查詢（如歸戶合併帳單）複雜化，
> 且本行 1,000 萬客戶之規模以讀寫分離＋分表即可滿足。

---

## 6. 一致性與補償

| 情境 | 一致性需求 | 機制 |
|---|---|---|
| 授權佔用額度 | 強一致（不可超額） | 單庫交易 + 樂觀鎖 + 唯一鍵 |
| 入帳與餘額更新 | 強一致 | 單庫交易（同服務內） |
| 入帳與事件發布 | 原子 | Outbox（同一交易） |
| 跨服務（授權→帳務） | 最終一致 | 事件 + 補償（Saga） |
| 跨服務（繳款→額度） | 最終一致（目標 ≤ 1 秒） | 事件 + 同步呼叫雙軌 |
| 清算比對 | 強一致（差異須為零） | 批次比對 + 差異處理流程 |
| 投影視圖 | 最終一致 | 冪等投影 + 延遲監控 |

詳見 `SDD-004`。

---

## 7. 字碼與難字轉換（FR 2.3）

| 面向 | 設計 |
|---|---|
| 儲存編碼 | UTF-8（資料庫、檔案、訊息） |
| 傳輸編碼 | 對外電文（ISO 8583）依卡組織規範，以約定之替代編碼承載難字 |
| 難字對照 | `product.charset_mappings` 表維護「內部 Unicode ↔ 外部替代碼」對照 |
| 落地還原 | 接收時依對照表還原為 Unicode；查無對照則記錄待處理清單 |
| 稽核 | 難字轉換須留存原始與轉換後值 |

```mermaid
flowchart LR
  IN["外部電文<br/>替代編碼"] --> MAP{"查對照表"}
  MAP -->|"命中"| DEC["還原為 Unicode"]
  MAP -->|"未命中"| Q["寫入待處理清單"]
  DEC --> DB["寫入資料庫<br/>UTF-8"]
  DB --> OUT["產生對外電文"]
  OUT --> ENC["轉為替代編碼"]
  ENC --> SEND["送出"]
  Q --> MANUAL["人工確認並補對照"]
  MANUAL --> MAP
```

---

## 8. 備份、還原與歸檔

| 項目 | Oracle（權威 OLTP） | PostgreSQL（讀側） |
|---|---|---|
| 全量備份 | RMAN Level 0，每日一次（離峰） | `pg_basebackup`／`pgBackRest`，每日一次 |
| 增量備份 | RMAN Level 1 + **ARCHIVELOG** 歸檔（每 15 分鐘） | **WAL** 歸檔（每 15 分鐘） |
| 保留 | 全量 30 天、歸檔日誌 7 天 | 全量 30 天、WAL 7 天 |
| 異地備份 | 每日同步至異地 | 每日同步至異地（可由事件流重建，優先度較低） |
| 還原演練 | 每季一次，量測 RTO／RPO | 每季一次 |
| 目標 | RPO ≤ 5 分鐘、RTO ≤ 30 分鐘 | RPO ≤ 15 分鐘、RTO ≤ 2 小時 |
| 歸檔 | 超過線上保留期之交易移至歸檔庫（可查不可改） | 事件流依 `evt` 保留策略處理 |

> **可重建性**：PostgreSQL 讀側資料可自 Kafka 事件流（`evt`）與 Oracle 權威資料**全量重建**，
> 故其 RPO／RTO 要求低於 Oracle。但 `evt`（事件流存檔）本身**不可重建**，須與 Oracle 同級保護。

---

## 9. 資料字典與治理

| 治理項 | 規則 |
|---|---|
| 命名 | 見 `GOV-001 §4.3` |
| 型別 | 金額：Oracle `NUMBER(19,0)`／PostgreSQL `bigint`（分）；<br>比率：Oracle `NUMBER(9,6)`／PostgreSQL `numeric(9,6)`；<br>時間：Oracle `TIMESTAMP WITH TIME ZONE`／PostgreSQL `timestamptz` |
| 必填欄位 | 每表必有 `id`、`created_at`、`updated_at`、`version` |
| 軟刪除 | 主檔可軟刪除（`deleted_at`）；交易**不得刪除** |
| 稽核欄位 | 異動類表須有 `created_by`、`updated_by` |
| 個資標記 | 個資欄位須於資料字典標記 `PII`，並註明遮罩方式 |
| 變更 | 結構變更一律以 migration 檔為之，不得手動改線上結構 |
| 自動檢查 | `tools/validate_sql.py`（規則見 `CSP-001 §5.1`）於 CI 強制執行 |

完整資料字典見 `SDD-001 §4`。

---

## 10. 修訂記錄

| 版本 | 日期 | 修訂者 | 說明 |
|---|---|---|---|
| 1.0 | 2026-09-17 | SA / DA | 初版 |
| 1.1 | 2026-09-18 | SA / DA | 依客戶指定「Oracle 為主、PostgreSQL 於必要時採 Hybrid」調整資料架構：①§1 DP-01 由「一服務一 PostgreSQL schema」改為「Oracle 權威 OLTP schema + PostgreSQL 讀側 schema」，並明訂落點分工；②DP-03／DP-07 補 Oracle 型別對照；③§3 資料庫部署架構圖改為 Oracle（Primary + Data Guard Standby + `CC_PLT`）與 PostgreSQL（`proj`／`evt`／`rpt`）雙層，並補 Outbox 位置約束；④§3.1 讀寫分離策略改以資料庫分工表述；⑤§3.2 時序圖參與者標註實際資料庫；⑥§5.1 補 Oracle／PostgreSQL 分區方言對照；⑦§8 備份策略由單一 WAL 表述改為 Oracle RMAN/ARCHIVELOG 與 PostgreSQL WAL 雙軌，並補充讀側可重建性之 RPO 差異；⑧§9 型別對照與自動檢查納入 |
| 1.2 | 2026-09-18 | SA / DA | **更正 1.1 版對 Outbox 位置之錯誤敘述**：1.1 版誤載 Outbox 集中於 `CC_PLT`，與 `SDD-001 §9`「通用平台表（每服務各一份）」及 DP-01「僅該服務可寫入自己 schema」相牴觸。已更正為 **Outbox 建於每個服務自己的 schema 內**（`CC_<SERVICE>.outbox`），`CC_PLT` 僅承載跨服務之 Saga 協調狀態。§3 部署圖與 §3.2 時序圖之標註同步更正 |