visionA/docs/autoflow/04-architecture/database.md

# Database — 資料模型

> **雛形階段無真實 DB**（見 ADR-005）。本文件定義 Go struct + 未來 DB schema 的映射，讓雛形程式碼直接以這些 struct 操作記憶體，Phase 1 直接按這個結構建 PostgreSQL schema。
>
> **以 code 為準原則（2026-06-20 DB 接入規劃更新）**：本文件 §2 struct 與 §4 schema 自雛形以來與實際 code 有少數漂移。DB 接入時，**欄位定義一律以 `visionA-backend/internal/*` 的 Go struct 為準**；本次更新已將 §4 schema 對齊 code 並標註修正點（搜尋「以 code 為準」）。後續實作 PostgresRepository 時若再發現不一致，亦以 code 為準並回頭修本文件。

---

## 0. DB 接入規格摘要（2026-06-20 新增 — DB 接入前文件補齊）

> 本節為 DB 接入任務（visionA-backend 6 個 in-memory store → Postgres/Redis 持久化）的開發前依循，對應 `db-integration-plan.md` §10 缺口補齊。範圍 = **完整**（PG 全接 + session 接 Redis + 交易韌性）。

### 0.1 DB 供給前提（取代任何「130 現成可用」字眼）

- **PostgreSQL**：visionA 專用實例，**credential 已取得**。visionA 端**不負責 provision**（DB 機器 / database / 角色由他人在 stage host 192.168.0.130 另開），**只負責跑 migration 與接上**。
- **Redis**：visionA 專用實例，**由使用者自行在 130 另起**（設密碼），供 `userSession` 用。visionA 端同樣不 provision、只接上。
- 130:5432 上其他 container 一律不共用；任何「130 上已有 DB 可直接用」的舊敘述皆作廢。
- 整合測試一律走 **testcontainers**（本機/CI 一次性 DB），不依賴 130；130 僅用於 stage 收尾驗證。

### 0.2 持久化分工（對齊 `db-integration-plan.md` §3）

| Store | package | 落地 | 理由 |
|-------|---------|------|------|
| `modelRepo` | `internal/model` | **Postgres** | 長期保存、List by owner/chip/source、跨重啟不掉。使用者最關心。 |
| `deviceRepo` | `internal/device` | **Postgres** | 裝置綁定身分長期保存、owner+serial unique、關聯查詢。 |
| `pairingStore` | `internal/auth` | **Postgres** | 可撤銷長期憑證、要稽核（used_at/revoked_at）、重啟不掉。 |
| `sessionTokenStore` | `internal/auth` | **Postgres** | session token 90 天長效、可撤銷、查 parent token 稽核鏈。 |
| `userSessionStore` | `internal/usersession` | **Redis** | 瀏覽器 cookie session、高頻讀、idle/absolute 雙 TTL、掉了重登即可。Redis TTL 原生支援。 |
| remote-proxy `session` | `internal/session` | **in-memory（不變）** | 特例：value 是活的 yamux Handle，不可序列化進 Redis。單節點維持 in-memory；多節點才需 Redis 存 Summary（handle 仍留本地）。本期範圍外。 |

### 0.3 第一份 migration 清單（最小範圍 = 塊 0 + 塊 1）

第一個 migration（`migrations/0001_*`）建立**模型庫持久化的最小集合**：

- **`users`** — `models.owner_user_id` / `devices.owner_user_id` / token `user_id` 的 FK 目標，必須先建（Phase 1 stub，雛形固定 demo-user）。
- **`models`** — 使用者最關心、塊 1 主體。

> device / pairing_tokens / session_tokens 表隨塊 2 / 塊 3 的 migration（`0002_*` / `0003_*`）建立，不塞進第一份 migration。決策理由：第一份 migration 聚焦「重啟後模型庫資料還在」的最小可驗收，降低首次上 DB 的風險面。

### 0.4 兩個關鍵 schema 決策（DB 接入前定案）

1. **token 共表（by `kind`）vs 分表** → **決策：分表（`pairing_tokens` + `session_tokens` 兩張）**。理由見 §4 token 段落。
2. **已 soft-delete 的 device serial 能否重註冊** → **決策：能**，用 partial unique index `WHERE deleted_at IS NULL` 達成。語意見 §4 devices 段落。

## 1. 核心實體 ER 概念圖

```
User ─┬─ owns ── Device ─ has ── PairingToken
      ├─ owns ── Model
      ├─ owns ── Cluster ─ contains ── Device
      └─ has  ── ConverterJob ─ produces ── Model
```

---

## 2. Go struct 定義

### 2.1 User（雛形 stub，Phase 1 實作）

```go
// internal/user/types.go
package user

import "time"

type User struct {
    ID        string     `json:"id"`
    Email     string     `json:"email"`
    Name      string     `json:"name,omitempty"`

    // Phase 1
    PasswordHash string    `json:"-"`
    OrgID        string    `json:"orgId,omitempty"`
    Roles        []string  `json:"roles,omitempty"`

    CreatedAt time.Time  `json:"createdAt"`
    UpdatedAt time.Time  `json:"updatedAt"`
    DeletedAt *time.Time `json:"deletedAt,omitempty"`
}
```

**雛形**：`StaticAuthService` 永遠回 `User{ID: "demo-user", Email: "demo@visiona.local"}`，不落盤。

### 2.2 Device

```go
// internal/device/types.go
package device

import "time"

type Device struct {
    ID           string    `json:"id"`            // UUID
    OwnerUserID  string    `json:"ownerUserId"`
    Name         string    `json:"name"`          // 使用者命名
    DeviceType   string    `json:"deviceType"`    // kl520 / kl720 等
    SerialNumber string    `json:"serialNumber"`  // 從 local agent 上報

    // -------- 雙狀態模型（2026-04-22 Minor-3 新增 remoteStatus / lastSeenAt 語意釐清）--------
    //
    // Device 同時擁有兩組狀態欄位，代表不同觀察角度：
    //
    //   1. Status（USB-level status，既有）
    //      - 由 local agent 直接觀察到的「USB 接了什麼」
    //      - 值：online / offline / unknown
    //      - 來源：local agent 呼叫 KL SDK 得到；透過 tunnel 上報雲端
    //      - 意義：「此刻使用者電腦上這個 KL device 插著且正常」
    //
    //   2. RemoteStatus（tunnel-level status，新增）
    //      - 由雲端（remote-proxy / api-server）對 tunnel 連線的觀察
    //      - 值：online | offline | reconnecting | error
    //      - 來源：SessionStore 狀態 + tunnel heartbeat（見 tunnel.md §4.2）
    //      - 意義：「雲端能不能透過 tunnel 觸達使用者電腦上的 agent」
    //
    // 兩者可能出現 4 種組合：
    //   | remoteStatus | status   | 解讀 |
    //   |--------------|----------|------|
    //   | online       | online   | 正常：雲端可達 + USB 有裝置  |
    //   | online       | offline  | agent 連著但 USB 拔掉了     |
    //   | offline      | *        | agent 或網路斷：雲端完全無法觸及（status 顯示的是最後觀察值）|
    //   | reconnecting | *        | tunnel 短暫斷線、local agent 正重連中 |
    //
    // 前端應優先顯示 remoteStatus（雲端連線狀態），次要顯示 status（USB 狀態），
    // 詳見 TDD §10 前端消費方式。

    // tunnel-level 狀態（2026-04-22 新增）
    RemoteStatus   string     `json:"remoteStatus"`              // online | offline | reconnecting | error
    LastSeenAt     *time.Time `json:"lastSeenAt,omitempty"`      // 最後一次收到 tunnel 心跳時間（ISO 8601）
    LastConnectedAt *time.Time `json:"lastConnectedAt,omitempty"` // tunnel 最近一次建立時間

    // USB-level 狀態（既有，保留）
    Status          string     `json:"status"`                    // online / offline / unknown（USB）

    CreatedAt time.Time  `json:"createdAt"`
    UpdatedAt time.Time  `json:"updatedAt"`
    PairedAt  *time.Time `json:"pairedAt,omitempty"`        // 2026-06-20 補上 — code 有此欄位、原 schema 漏（配對完成時間）
    DeletedAt *time.Time `json:"deletedAt,omitempty"`
}
```

> **以 code 為準**（2026-06-20）：上方對齊 `internal/device/device.go`，新增 `PairedAt`（配對完成時間，nullable）。`SerialNumber` 在 code 為 `omitempty`。

**注意**：此「Device」記錄的是**抽象身分**（綁 user）。當瀏覽器呼叫 `GET /api/devices` 時，實際「此時 USB 上接了哪些」要透過 tunnel 問 local agent 的 `/api/devices`。雲端這張表負責「我曾經綁過哪些裝置、它們的名字、擁有者」+ 雙狀態快照。

**更新時機**：
- `remoteStatus` 由 `remote-proxy` 在 tunnel 事件發生時寫入（connect → `online`、heartbeat timeout → `offline`、中間短暫斷線 → `reconnecting`、yamux 錯誤 → `error`）
- `lastSeenAt` 由 `remote-proxy` 的 heartbeat 處理每 10s 更新一次（見 tunnel.md §4.2）
- `status`（USB）由 local agent 上報，走既有 POC 邏輯

### 2.3 Model

```go
// internal/model/types.go
package model

import "time"

type Model struct {
    ID            string    `json:"id"`
    OwnerUserID   string    `json:"ownerUserId"`
    Name          string    `json:"name"`
    Description   string    `json:"description,omitempty"`

    // 檔案資訊
    StorageKey    string    `json:"storageKey"`    // 在 Store 裡的 key（例：models/{user_id}/{id}.nef）
    FileSize      int64     `json:"fileSize"`
    FileChecksum  string    `json:"fileChecksum,omitempty"`  // sha256 hex

    // FAAObjectKey（ADR-017 (a) B1）：model 在 File Access Agent 上的 object key。
    // 只有 Source=converted（轉檔→promote 進 FAA）類有值；上傳類留空。
    // JSON tag = "-"（不對前端揭露內部 storage key，ADR-017 決策 2）。
    // DB 欄位 nullable（見 §4，2026-06-20 補上 — 原 schema 漏此欄）。
    FAAObjectKey  string    `json:"-"`

    // 模型 metadata（可選）
    TargetChip    string    `json:"targetChip,omitempty"`    // kl520 / kl720
    InputShape    []int     `json:"inputShape,omitempty"`
    Classes       []string  `json:"classes,omitempty"`
    Framework     string    `json:"framework,omitempty"` // onnx / keras 等（若有）

    // 來源
    Source        Source    `json:"source"`        // uploaded / converted / preset（Source = string 別名）
    SourceJobID   string    `json:"sourceJobId,omitempty"` // 若 source=converted

    CreatedAt  time.Time  `json:"createdAt"`
    UpdatedAt  time.Time  `json:"updatedAt"`
    UploadedAt *time.Time `json:"uploadedAt,omitempty"` // 2026-06-20 補上 — code 有此欄位、原 schema 漏
    DeletedAt  *time.Time `json:"deletedAt,omitempty"`
}
```

> **以 code 為準**（2026-06-20）：上方 struct 已對齊 `internal/model/model.go`。相對原雛形定義新增了 `FAAObjectKey`（ADR-017）與 `UploadedAt`，`FileChecksum` 改為 `omitempty`，`Source` 為 `type Source = string` 別名。

### 2.4 PairingToken / SessionToken（見 ADR-003）

> **以 code 為準**（2026-06-20）：實際 code 中 pairing 與 session 是**兩個獨立 struct + 兩個獨立 Store**（`internal/auth/auth.go`），欄位不同。原雛形文件畫的單一 `PairingInfo`（含 `RevokedBy` / `LastSeenAt` / `ParentToken` 共用欄）**與 code 不符、已作廢**。下方改列 code 實際的兩個 struct，schema 也據此分表（見 §4）。

```go
// internal/auth/auth.go

// PairingToken — 短期一次性配對 token（vAc_ + 32 hex，15min TTL，一次性）
type PairingToken struct {
    Plaintext string     `json:"-"` // 僅建立時回傳一次
    TokenHash string     `json:"-"` // sha256(Plaintext)，DB PK
    UserID    string     `json:"userId"`
    DeviceID  string     `json:"deviceId,omitempty"` // MarkUsed 綁定後才有
    Kind      TokenKind  `json:"kind"`               // "pairing"

    CreatedAt time.Time  `json:"createdAt"`
    ExpiresAt *time.Time `json:"expiresAt,omitempty"` // 15min TTL；nil = 永不過期（測試用）
    UsedAt    *time.Time `json:"usedAt,omitempty"`    // 一次性：MarkUsed 寫入後 Validate 失敗
    RevokedAt *time.Time `json:"revokedAt,omitempty"`
}

// SessionToken — 長期可撤銷 tunnel session token（vAs_ + 64 hex，90 天 TTL）
type SessionToken struct {
    Plaintext       string     `json:"-"`
    TokenHash       string     `json:"-"` // DB PK
    UserID          string     `json:"userId"`
    DeviceID        string     `json:"deviceId"`        // session token 必綁 device
    ParentTokenHash string     `json:"-"`               // 升級來源 pairing token 的 hash（稽核鏈）

    CreatedAt time.Time  `json:"createdAt"`
    ExpiresAt *time.Time `json:"expiresAt,omitempty"`  // 90 天；nil = 永不過期
    RevokedAt *time.Time `json:"revokedAt,omitempty"`
    // 注意：SessionToken 無 UsedAt（非一次性）、無 Kind（型別本身即 session）
}
```

**關鍵差異（影響 §4 分表決策）**：
- `PairingToken` 有 `UsedAt`（一次性語意）、`Kind`；無 `ParentTokenHash`。
- `SessionToken` 有 `ParentTokenHash`（稽核鏈）、`DeviceID` 必填；無 `UsedAt`、無 `Kind`。
- 兩者由**不同 Store interface** 管理（`PairingStore` / `SessionTokenStore`），方法集不同（pairing 有 `MarkUsed`、`Validate`；session 有 `Get`）。
- code 中**不存在** `RevokedBy` / `LastSeenAt` / `ParentToken`（原雛形文件的臆測欄位）。

### 2.5 Cluster（從 POC 搬）

```go
// internal/cluster/types.go
package cluster

type Cluster struct {
    ID          string         `json:"id"`
    OwnerUserID string         `json:"ownerUserId"`
    Name        string         `json:"name"`
    Devices     []DeviceMember `json:"devices"`
    ModelID     string         `json:"modelId,omitempty"`
    Status      ClusterStatus  `json:"status"`

    CreatedAt time.Time  `json:"createdAt"`
    UpdatedAt time.Time  `json:"updatedAt"`
    DeletedAt *time.Time `json:"deletedAt,omitempty"`
}

type DeviceMember struct {
    DeviceID   string       `json:"deviceId"`
    Weight     int          `json:"weight"`
    Status     MemberStatus `json:"status"`
    DeviceName string       `json:"deviceName,omitempty"`
    DeviceType string       `json:"deviceType,omitempty"`
}

type ClusterStatus string
const (
    ClusterIdle        ClusterStatus = "idle"
    ClusterInferencing ClusterStatus = "inferencing"
    ClusterDegraded    ClusterStatus = "degraded"
)
```

### 2.6 ConverterJob（stub）

```go
// internal/converter/types.go
package converter

import "time"

type Job struct {
    ID          string    `json:"id"`
    OwnerUserID string    `json:"ownerUserId"`
    Status      string    `json:"status"`    // queued / running / succeeded / failed
    SourceKey   string    `json:"sourceKey"` // 原始 onnx / keras 在 storage 的 key
    ResultKey   string    `json:"resultKey,omitempty"` // 產物（.nef）的 key
    TargetChip  string    `json:"targetChip"`
    Params      map[string]any `json:"params,omitempty"`

    ErrorCode   string    `json:"errorCode,omitempty"`
    ErrorMsg    string    `json:"errorMsg,omitempty"`

    CreatedAt   time.Time `json:"createdAt"`
    UpdatedAt   time.Time `json:"updatedAt"`
    StartedAt   *time.Time `json:"startedAt,omitempty"`
    CompletedAt *time.Time `json:"completedAt,omitempty"`
}
```

### 2.7 Session（**不落 DB**，但列出以對照）

```go
// internal/session/types.go
package session

import "time"

type SessionSummary struct {
    Token         string    // caller 已知
    UserID        string
    DeviceID      string
    ConnectedAt   time.Time
    LastSeenAt    time.Time
    ProxyNodeID   string    // Phase 1 多節點時使用
}
```

雛形 session 在 in-memory map，process 重啟就掉。Phase 1 考慮 Redis 存 summary（TTL + heartbeat）。

> **2026-06-20 補充（DB 接入）**：要區分兩種 session：
> - **`internal/usersession`（browser cookie session）→ 接 Redis**。Session struct（見 `internal/usersession/usersession.go`）含 OIDC pending state（OIDCState/Nonce/CodeVerifier）+ token snapshot（AccessToken/IDTokenRaw）+ `Extra map[string]any`。`RedisUserSessionStore` 用 Redis 雙 TTL（idle = `IdleTTL` 預設 24h、absolute = `AbsoluteTTL` 預設 168h）取代現有手動 cleanup goroutine；`Extra` 需 JSON 序列化；`OIDCCodeVerifier` 等敏感欄位照舊不可進 log。Redis 為 visionA 專用實例（使用者在 130 另起、設密碼）。
> - **`internal/session`（remote-proxy tunnel session）→ 維持 in-memory（不變）**。value 是活的 yamux Handle，**不可序列化進 Redis**。單節點維持 in-memory；多節點才需「Summary 放 Redis、handle 留本地」混合實作 —— **本期範圍外**。

---

## 3. Repository Interfaces

```go
// internal/device/repository.go
package device

import "context"

type Repository interface {
    Get(ctx context.Context, id string) (*Device, error)
    GetBySerial(ctx context.Context, userID, serial string) (*Device, error)
    List(ctx context.Context, userID string) ([]*Device, error)
    Save(ctx context.Context, d *Device) error
    Delete(ctx context.Context, id string) error
}

type InMemoryRepository struct {
    devices map[string]*Device
    mu      sync.RWMutex
}
// ...
```

每個 domain 都有：
- `repository.go`：interface + `InMemoryRepository` 實作
- `types.go`：struct 定義

Phase 1：新增 `postgres_repository.go`，實作同 interface。

---

## 4. Phase 1 PostgreSQL Schema（預覽）

雛形不建 DB，但 struct 直接對應以下 schema 的欄位：

```sql
-- users
-- 環境適配（塊 0，2026-06-20 真環境驗證）：目標 PG 14.23 未安裝 citext extension，
-- email 改用 TEXT + 函式索引達成大小寫不敏感唯一，取代原 CITEXT 寫法：
--   email TEXT NOT NULL;
--   CREATE UNIQUE INDEX users_email_lower_uniq ON users (lower(email));
-- 應用層查詢一律以 lower(email) 比對。若未來環境有 citext，可回退 CITEXT 簡化。
CREATE TABLE users (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email        TEXT UNIQUE NOT NULL,   -- 見上方環境適配註：PG 14.23 無 citext，實際以 lower(email) 唯一索引達成 CI 唯一
    name         TEXT,
    password_hash TEXT,
    org_id       UUID,
    roles        TEXT[] DEFAULT '{}',
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at   TIMESTAMPTZ
);

-- devices（2026-04-22 Minor-3：新增 remote_status + last_seen_at 雙狀態欄位）
CREATE TABLE devices (
    id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_user_id     UUID NOT NULL REFERENCES users(id),
    name              TEXT NOT NULL,
    device_type       TEXT,
    serial_number     TEXT,

    -- tunnel-level 狀態（雲端觀察）
    remote_status     TEXT NOT NULL DEFAULT 'offline',   -- online | offline | reconnecting | error
    last_seen_at      TIMESTAMPTZ,                        -- 最後 tunnel heartbeat 時間
    last_connected_at TIMESTAMPTZ,                        -- 最近 tunnel 建立時間

    -- USB-level 狀態（local agent 上報）
    status            TEXT NOT NULL DEFAULT 'unknown',    -- online | offline | unknown

    created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    paired_at         TIMESTAMPTZ,                        -- 2026-06-20 補：配對完成時間（code 有、原 schema 漏）
    deleted_at        TIMESTAMPTZ
);
-- 2026-06-20 改：table-level UNIQUE → partial unique index（soft-delete 後 serial 可重註冊）
-- 決策：已 soft-delete 的 device serial 「能」重新註冊。
-- 語意：唯一性只對「未刪除」紀錄成立；刪掉後同 (owner, serial) 可再 INSERT 一筆新的。
CREATE UNIQUE INDEX uq_devices_owner_serial_active
    ON devices (owner_user_id, serial_number)
    WHERE deleted_at IS NULL;
CREATE INDEX ON devices (owner_user_id) WHERE deleted_at IS NULL;
CREATE INDEX ON devices (remote_status) WHERE deleted_at IS NULL;

-- ── token 分表決策（2026-06-20）──────────────────────────────────────────
-- 決策：pairing_tokens 與 session_tokens 「分表」，不共表 by kind。
-- 理由：
--   1. code 中是兩個獨立 struct + 兩個 Store interface（PairingStore / SessionTokenStore），
--      欄位與方法集不同（pairing 有 used_at 一次性語意、kind；session 有 parent_token_hash、無 used_at）。
--   2. 共表會讓 used_at / parent_token_hash 對另一類永遠為 NULL，欄位語意混淆、CHECK 約束變複雜。
--   3. 分表後各表 schema 乾淨、index 各自最佳化，repository 一對一對映 Store，最直觀。
-- 代價：稽核「pairing→session 升級鏈」需跨表 join（session_tokens.parent_token_hash → pairing_tokens.token_hash）；
--      可接受（查詢頻率低，且 parent_token_hash 已是 hash 不需額外轉換）。

-- pairing_tokens（短期一次性配對 token；對齊 internal/auth.PairingToken）
CREATE TABLE pairing_tokens (
    token_hash    TEXT PRIMARY KEY,              -- sha256(plaintext)，永不存明文
    user_id       UUID NOT NULL REFERENCES users(id),
    device_id     UUID REFERENCES devices(id),   -- MarkUsed 綁定後才有
    kind          TEXT NOT NULL DEFAULT 'pairing', -- 固定 'pairing'（保留欄位，便於觀測/未來擴充）
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at    TIMESTAMPTZ,                   -- 15min TTL；NULL = 永不過期（測試用）
    used_at       TIMESTAMPTZ,                   -- 一次性：MarkUsed 後 Validate 失敗
    revoked_at    TIMESTAMPTZ
    -- 2026-06-20 移除原 schema 的 parent_token / revoked_by / last_seen_at —— code 不存在這些欄位
);
CREATE INDEX ON pairing_tokens (user_id) WHERE revoked_at IS NULL;
CREATE INDEX ON pairing_tokens (device_id);

-- session_tokens（長期可撤銷 tunnel session token；對齊 internal/auth.SessionToken）
CREATE TABLE session_tokens (
    token_hash         TEXT PRIMARY KEY,         -- sha256(plaintext)
    user_id            UUID NOT NULL REFERENCES users(id),
    device_id          UUID NOT NULL REFERENCES devices(id), -- session token 必綁 device
    parent_token_hash  TEXT,                     -- 升級來源 pairing token 的 hash（稽核鏈，可 join pairing_tokens.token_hash）
    created_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at         TIMESTAMPTZ,              -- 90 天 TTL；NULL = 永不過期
    revoked_at         TIMESTAMPTZ
    -- 注意：無 used_at（非一次性）、無 kind
);
CREATE INDEX ON session_tokens (user_id) WHERE revoked_at IS NULL;
CREATE INDEX ON session_tokens (device_id);
CREATE INDEX ON session_tokens (parent_token_hash);

-- models
CREATE TABLE models (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_user_id   UUID NOT NULL REFERENCES users(id),
    name            TEXT NOT NULL,
    description     TEXT,
    storage_key     TEXT NOT NULL,
    file_size       BIGINT NOT NULL,
    file_checksum   TEXT,
    faa_object_key  TEXT,                        -- 2026-06-20 補：ADR-017 (a) B1，nullable（上傳類留 NULL）
    target_chip     TEXT,
    input_shape     INT[],
    classes         TEXT[],
    framework       TEXT,
    source          TEXT NOT NULL,
    source_job_id   UUID,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    uploaded_at     TIMESTAMPTZ,                 -- 2026-06-20 補：code 有 UploadedAt、原 schema 漏
    deleted_at      TIMESTAMPTZ
);
CREATE INDEX ON models (owner_user_id) WHERE deleted_at IS NULL;
CREATE INDEX ON models (owner_user_id, target_chip) WHERE deleted_at IS NULL; -- List filter by chip
CREATE INDEX ON models (owner_user_id, source) WHERE deleted_at IS NULL;      -- List filter by source

-- clusters
CREATE TABLE clusters (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_user_id UUID NOT NULL REFERENCES users(id),
    name          TEXT NOT NULL,
    model_id      UUID REFERENCES models(id),
    status        TEXT DEFAULT 'idle',
    devices_json  JSONB NOT NULL DEFAULT '[]'::jsonb,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at    TIMESTAMPTZ
);

-- converter_jobs
CREATE TABLE converter_jobs (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_user_id UUID NOT NULL REFERENCES users(id),
    status        TEXT NOT NULL DEFAULT 'queued',
    source_key    TEXT NOT NULL,
    result_key    TEXT,
    target_chip   TEXT NOT NULL,
    params        JSONB,
    error_code    TEXT,
    error_msg     TEXT,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    started_at    TIMESTAMPTZ,
    completed_at  TIMESTAMPTZ
);
```

---

## 5. Migration 策略（Phase 1）

- 工具：`golang-migrate/v4`
- 位置：`visionA-backend/migrations/*.sql`
- 命名：`NNNN_description.up.sql` / `.down.sql`（序號式，如 `0001_create_users_models.up.sql`）
- runner：啟動時自動 migrate up（或獨立 `cmd/migrate`），由 `DatabaseConfig.Enabled()` 決定是否執行

### 5.1 Migration 清單（2026-06-20 補 — 對齊塊式範圍）

| Migration | 內容 | 對應塊 | 觸發範圍 |
|-----------|------|--------|---------|
| `0001_create_users_models` | `users` + `models`（含 faa_object_key / uploaded_at + 3 個 owner-scoped index） | 塊 1 | **最小範圍**（使用者最關心，重啟後模型庫還在） |
| `0002_create_devices` | `devices`（雙狀態欄位 + paired_at + partial unique index `uq_devices_owner_serial_active`） | 塊 2 | 持久資料範圍 |
| `0003_create_token_tables` | `pairing_tokens` + `session_tokens`（分表，見 §4 決策） | 塊 3 | 持久資料範圍 |
| `0004_*`（如需要） | `clusters` / `converter_jobs` | — | **本期範圍外**（main.go 未 wire，見 `db-integration-plan.md` §8） |

> **第一份 migration（`0001`）為何含 users**：`models.owner_user_id` 是 `REFERENCES users(id)` 的 FK，必須先有 users 表。雛形 users 為 stub（固定 demo-user），但 schema 與 FK 約束第一份就要到位，否則 models 建不起來。
>
> 決策：第一份 migration 聚焦「最小可驗收 = 重啟後模型庫資料還在」，故只含 users + models；device / token 隨各自塊的 migration 進場，降低首次上 DB 的風險面。

---

## 5.5 連線設定規格（2026-06-20 新增 — DatabaseConfig / RedisConfig）

> 對齊既有 `internal/config/config.go` 慣例：每個外部依賴一個 sub-config struct、掛在 `Config` 上、env 前綴 `VISIONA_*`、有 `Enabled()` 用「必要欄位全非空」判斷是否 wire（參照既有 `ConversionConfig.Enabled()` / `FileAccessConfig.Enabled()`）。新增欄位須同步 `.env.example`。

### 5.5.1 DatabaseConfig（PostgreSQL）

```go
// internal/config/config.go — 掛在 Config struct 上：Database DatabaseConfig

// DatabaseConfig 控制 PostgreSQL 連線（持久業務資料：model / device / token）。
//
// 啟用判定（Enabled()）：Host / User / DBName 三者全非空才視為啟用；
// 任一缺 → main.go 不建連線池、6 個 repository 仍用 in-memory（local dev fallback）。
//
// 安全：Password / DSN 永遠不印 log 全文（可印 host:port/dbname）；
// 部署走既有 secrets 機制（AWS Secrets Manager / Vault），禁止 commit 進 repo。
type DatabaseConfig struct {
    Host     string // VISIONA_DB_HOST
    Port     int    // VISIONA_DB_PORT，預設 5432
    User     string // VISIONA_DB_USER
    Password string // VISIONA_DB_PASSWORD（禁止 commit）
    DBName   string // VISIONA_DB_NAME
    SSLMode  string // VISIONA_DB_SSLMODE，預設 "require"（stage/prod）；本機 testcontainers 用 "disable"

    // 連線池（pgxpool）
    MaxConns        int           // VISIONA_DB_MAX_CONNS，預設 10
    MinConns        int           // VISIONA_DB_MIN_CONNS，預設 2
    MaxConnLifetime time.Duration // VISIONA_DB_MAX_CONN_LIFETIME，預設 1h
    ConnTimeout     time.Duration // VISIONA_DB_CONN_TIMEOUT，預設 5s（建池/ping 逾時）
}

// DSN 組裝（供 pgxpool）：
//   postgres://{User}:{Password}@{Host}:{Port}/{DBName}?sslmode={SSLMode}&pool_max_conns={MaxConns}
func (c DatabaseConfig) Enabled() bool {
    return c.Host != "" && c.User != "" && c.DBName != ""
}
```

| env | 預設 | 說明 |
|-----|------|------|
| `VISIONA_DB_HOST` | （空）| visionA 專用 PG host（credential 已取得；他人在 130 provision） |
| `VISIONA_DB_PORT` | `5432` | |
| `VISIONA_DB_USER` | （空）| app role |
| `VISIONA_DB_PASSWORD` | （空）| 禁止 commit；走 secrets 機制 |
| `VISIONA_DB_NAME` | （空）| visionA 專用 database |
| `VISIONA_DB_SSLMODE` | `require` | stage/prod 用 require/verify-full；testcontainers 用 disable |
| `VISIONA_DB_MAX_CONNS` | `10` | pgxpool 上限 |
| `VISIONA_DB_MIN_CONNS` | `2` | pgxpool 常駐 |
| `VISIONA_DB_MAX_CONN_LIFETIME` | `1h` | |
| `VISIONA_DB_CONN_TIMEOUT` | `5s` | 建池 / 啟動 ping 逾時 |

### 5.5.2 RedisConfig（userSession）

```go
// internal/config/config.go — 掛在 Config struct 上：Redis RedisConfig

// RedisConfig 控制 Redis 連線（僅 userSession：browser cookie session）。
//
// ⚠️ visionA 專用 Redis 實例：由使用者自行在 stage host(130) 另起、設密碼。
// visionA 端不 provision、只接上。
//
// 啟用判定（Enabled()）：Host 非空才視為啟用；
// 未啟用 → userSession 仍用 in-memory（雛形行為，process 重啟掉 session）。
//
// 安全：Password 永遠不印 log 全文。禁止 commit 進 repo。
type RedisConfig struct {
    Host     string // VISIONA_REDIS_HOST
    Port     int    // VISIONA_REDIS_PORT，預設 6379
    Password string // VISIONA_REDIS_PASSWORD（visionA 專用實例必設密碼；禁止 commit）
    DB       int    // VISIONA_REDIS_DB，預設 0（db index）

    ConnTimeout time.Duration // VISIONA_REDIS_CONN_TIMEOUT，預設 5s
}

func (c RedisConfig) Enabled() bool {
    return c.Host != ""
}
```

| env | 預設 | 說明 |
|-----|------|------|
| `VISIONA_REDIS_HOST` | （空）| visionA 專用 Redis（使用者在 130 另起） |
| `VISIONA_REDIS_PORT` | `6379` | |
| `VISIONA_REDIS_PASSWORD` | （空）| visionA 專用實例必設；禁止 commit |
| `VISIONA_REDIS_DB` | `0` | db index |
| `VISIONA_REDIS_CONN_TIMEOUT` | `5s` | 建連 / 啟動 ping 逾時 |

> userSession 的 idle / absolute TTL **不在 RedisConfig**，沿用既有 `UserSessionConfig.IdleTTL`（24h）/ `AbsoluteTTL`（168h）；`RedisUserSessionStore` 用這兩個值設 Redis key TTL 取代手動 cleanup goroutine。

---

## 6. 資料一致性考量

| 操作 | 需要的一致性 |
|------|-------------|
| 使用者建立 Device + PairingToken | 同一 transaction |
| 使用者刪除 Device | cascade 撤銷該 device 的所有 token（**pairing_tokens + session_tokens 兩張表**，by device_id）於同一 tx |
| Converter job 完成 → 建立 Model | transactional upsert + webhook idempotency |

雛形 in-memory 無交易；Phase 1 用 `pgx` 的 tx（塊 5 的 `internal/db/tx.go` helper）。

> token 分表後（§4 決策），「刪 device cascade 撤銷 token」需在同一 tx 內對 `pairing_tokens` 與 `session_tokens` 各跑一次 `UPDATE ... SET revoked_at = now() WHERE device_id = $1 AND revoked_at IS NULL`。

---

**雛形實作 / 未來擴展**：
- 雛形：所有 repository 用 `map + sync.RWMutex`；struct 欄位按上表定義（多出的欄位留空或 zero value）
- 未來：實作 `Postgres*Repository`；加上 migration；處理 soft delete（`WHERE deleted_at IS NULL`）

**DB 接入關鍵改動提醒（2026-06-20，對齊 `db-integration-plan.md` 塊 3）**：
- pairing / session token 兩個 in-memory store 目前**以 plaintext 當 map key**；接 Postgres 時改成 **`token_hash`（`HashToken(plaintext)`）當 PK**。`Validate` / `Get` 需先 `HashToken()` 再查；務必確認所有呼叫端傳入 plaintext、由 store 內部統一 hash（漏一個呼叫端就驗不過）。code 已有 `HashToken()`（`internal/auth/token.go`）。
- model / device 的 `Save` 是 upsert by ID，須保留既有 `CreatedAt`（對齊 in-memory `Save` 的語意：existing 且未刪除時保留 CreatedAt）。
- 整合測試走 testcontainers（不依賴 130）；stage 收尾才接 visionA 專用 PG / Redis。