# LicenseManager Platform - 数据库设计文档

| 项目 | 内容 |
| --- | --- |
| 文档版本 | v1.0 |
| 文档类型 | 数据库设计文档 (Database Design Document) |
| 关联文档 | [RDP v1.0.1](./LicenseManager-Platform-RDP-v1.0.1.md) · [TAD v1.0](./LicenseManager-Platform-TAD-v1.0.md) |
| 目标数据库 | 设计目标:PostgreSQL 16(主) · Redis 7(缓存) · S3 兼容(对象存储) |
| 实际实现 | MySQL 8(主,EF Core/Pomelo)· 支持 SQLite / PostgreSQL / SQL Server(EF 提供程序切换) |
| 文档日期 | 2026-07-23 |

---

## 0. 修订历史

| 版本 | 日期 | 修订内容 |
| --- | --- | --- |
| v1.0 | 2026-07-23 | 初稿,基于 RDP v1.0.1 数据模型部分展开 |
| v1.1 | 2026-08-05 | 新增"实现现状与设计差异"章节,将文档对齐到当前实际实现(MySQL + EF Core),标注各表/字段的实现状态 |
| v1.2 | 2026-09 | 按代码实测同步:迁移 7 → 10 个(补 `WidenWebhookEndpointSecret`、`AddFeatureCatalogFields`、`_V2`);TenantId 全局过滤器实体 24 → 27 个(反射动态附加) |
| v1.3 | 2026-10-08 | 商业化裁剪：移除订单/支付/发票/PSP/优惠券等资金流相关章节，资金流移交外部销售系统 |
| v1.4 | 2026-10-16 | 按代码实测同步：9.3/9.4 节修正（`license.RowVersion` 乐观锁与 4 张安全台账表均已随 InitialCreate 建表）；实体表 72 张（含迁移历史表共 73 张）；迁移补齐 `PersistRemainingEntities`（32 个实体）与 `AddTenantAndLookupIndexes`（14 个 TenantId 索引 + `license_record.LicenseKey` 唯一索引）；consume 消费计量链路已接通（RowVersion 乐观并发权威计数） |
| v1.5 | 2026-10-16 | 新增 9.4 节"提供程序行为差异"：5 方言 EF 翻译审计结论（仅 Sqlite 不支持 DateTimeOffset 下推，EfRepository 内存回退兜底）；搜索大小写敏感性随提供程序/排序规则差异；DateTimeOffset 偏移量存储语义差异 |
| v1.6 | 2026-10-17 | Sqlite DateTimeOffset 翻译缺口根治：`SqliteDateTimeOffsetConversionConvention`（仅 Sqlite 方言生效）为全部 DateTimeOffset 属性注册 UTC DateTime 值转换器，比较/排序可下推，存储归一化 UTC（偏移丢弃）；新增空操作锚点迁移 `SqliteDateTimeOffsetAsUtc`；存量 SQLite 库旧格式（含偏移后缀 TEXT）读取行为实测（非 UTC 机器抛 ArgumentException）及数据清洗/重建建议；9.4 节矩阵与机制说明同步更新 |

---

> ## ⚠️ 实现现状与设计差异(权威说明,请先阅读)
>
> 本文档第 1–9 章是**目标/理想化设计**(PostgreSQL + Redis + S3、PII 加密、RLS、时序分区、软删除、乐观锁等)。
> **当前实际实现**以 **EF Core 实体模型 + Migrations 为准**,与下述设计存在差异。实践与评审时请以
> [`LicenseManagerDbContext`](../../src/LicenseManager.Infrastructure/Persistence/LicenseManagerDbContext.cs) 及其
> `Migrations/` 目录为**权威 schema 来源**。本章节用于明确"已实现"与"设计目标未实现"的边界,避免误读。
>
> ### 9.1 数据库引擎
>
> | 项目 | 设计目标 | 实际实现 |
> | --- | --- | --- |
> | 主数据库 | PostgreSQL 16 | **MySQL 8**(Pomelo EF 提供程序);通过 `DatabaseOptions.Provider` 可切换 SQLite / PostgreSQL / SQL Server |
> | 缓存 | Redis 7 | 应用内 `IMemoryCache`(自托管无依赖) |
> | 对象存储 | S3 兼容 | 本地目录(见 `DataPrivacy:ExportDir`)/ 待接入 |
> | 主键生成 | UUID v7(时间排序) | CLR `Guid.NewGuid()`(随机 UUID,非 v7) |
>
> ### 9.2 已实现的设计原则
>
> | 原则 | 实现方式 |
> | --- | --- |
> | JSON 列 | `Dictionary`/`List` 属性映射为 JSON 列(`JsonType` 按提供程序输出 `json`/`jsonb`) |
> | 多租户隔离 | 17 个实体含 `TenantId`;通过 EF Core **全局查询过滤器**(反射动态附加,非 PostgreSQL RLS)实现租户可见性 |
> | 审计 | `AuditLog` 表 + `AuditService` 应用层写入(非数据库触发器镜像) |
> | 限流 | `RateLimitingMiddleware`(Redis 原子固定窗口,无 Redis 时回退 IMemoryCache) |
> | 乐观并发 | `license.RowVersion` 并发令牌(`IsConcurrencyToken` + 客户端 GUID 盖章,见 `StampLicenseRowVersions`);激活/停用/consume 的读-改-写据此防超签超扣,冲突抛 `DbUpdateConcurrencyException` |
> | 消费计量 | consume 链路已接通:`POST /v1/customer/licenses/{id}/features/{code}/consume`(需认证)→ `ILicenseService.ConsumeFeatureAsync` 真实 Features 上下文 + 原子更新权威计数 + `consumption_log` 追加 |
>
> ### 9.3 设计目标但**尚未实现**(勿按此建表)
>
> - **PII 字段级加密**:`*_cipher` / `email_cipher` / `email_hash` / `phone_cipher` 等并不存在;`email`/`phone` 目前**明文存储**。
> - **软删除**:实体无 `DeletedAt`/`IsDeleted`,`deleted_at` 列与硬删除行为未实现(EF 查询过滤器未过滤已删记录)。
> - **乐观锁(除 `license` 外)**:`license.RowVersion` 已实现乐观并发(`IsConcurrencyToken` + 客户端 GUID 盖章,已包含在各方言 `InitialCreate` 迁移中;原 `payment_intent` 的 `row_version` 已随商业化裁剪删除);其余实体仍无并发令牌。
> - **RLS / 时序按月分区 / 冷热分层(S3 归档) / 物化视图 `mv_*` / LTREE / INET / `gen_random_uuid_v7()`**:PostgreSQL 专属能力,MySQL 下不适用。
> - **`user_role` 关联表**:当前 `User.RoleIds`(JSON 数组)维护用户-角色关系,无独立关联表。
>
> ### 9.4 实际表清单(EF 实体,权威)
>
> Schema 权威来源为 `src/LicenseManager.Infrastructure.Migrations.{MySql,PostgreSql,Sqlite,SqlServer,Oracle}/Migrations/` 目录。MySQL / PostgreSQL / SQLite / SQL Server 方言各 **8** 个迁移:`InitialCreate`、`AddSsoSamlFields`、`AddUserPhone`、`SystemConfigTable`、`AddCustomerContactFields`、`RemoveCommerce`(商业化裁剪:移除订单/支付/发票/PSP/优惠券等资金流相关表,资金流移交外部销售系统)、`PersistRemainingEntities`(32 个领域实体补持久化:MFA/设备黑名单/工单/法务/配额/导入导出等)、`AddTenantAndLookupIndexes`(14 个 `TenantId` 普通索引 + `license_record.LicenseKey` 唯一索引);Oracle 方言另有 `NormalizeDecimalFormat`,共 9 个。
>
> `LicenseManagerDbContext` 注册的实体表共 **72** 张(另有 EF 迁移历史表 `__EFMigrationsHistory`,合计 73 张),按领域分组:
>
> | 领域 | 表 |
> | --- | --- |
> | 身份与访问 | `user_account`, `role`, `api_key`, `personal_access_token`, `tenant`, `mfa_enrollment`, `mfa_challenge` |
> | 产品目录 | `product`, `customer`, `feature_catalog`, `license_template`, `plan`, `custom_field_definition`, `custom_field_value`, `customer_quota` |
> | 许可证生命周期 | `license`, `activation`, `device`, `consumption_log`, `license_event`, `license_record`, `refresh_file`, `device_blacklist`, `offline_activation_request_record`, `batch_operation`, `borrowed_seat` |
> | 订阅 | `subscription` |
> | 浮动许可 | `floating_server`, `lease` |
> | 审计与 Webhook | `audit_log`, `audit_actor_snapshot`, `webhook_endpoint`, `webhook_delivery` |
> | 系统管理 | `system_key`, `system_config`, `notification`, `notification_template`, `notification_log`, `translation` |
> | 安全台账 | `login_attempt`, `ip_whitelist_entry`, `nonce_record`, `idempotency_record` |
> | 高级模块 | `NamedUserAssignments`, `EntitlementSets`, `LicenseEntitlements`, `LicenseUsers`, `TokenPools`, `TokenConsumptionLogs`, `Eulas`, `EulaAcceptances`, `SsoProviders`, `SsoUserBindings`, `Integrations`, `IntegrationSyncLogs` |
> | 命名用户分组 | `license_user_group`, `license_user_group_member` |
> | 支持与运维 | `ticket`, `support_tier`, `customer_support_tier`, `maintenance_window`, `incident`, `kb_article` |
> | 发布与伙伴 | `release`, `release_file`, `partner` |
> | 隐私与法务 | `dsr_request`, `legal_document`, `legal_acceptance` |
> | 遥测导出 | `telemetry_export_config`, `telemetry_export_log` |
> | 导入 | `import_job` |
>
> **注意**:4 张安全台账表(`login_attempt` / `ip_whitelist_entry` / `nonce_record` / `idempotency_record`)已在各方言 `InitialCreate` 迁移中建表,无额外补迁移要求。
>
> **索引现状**:`AddTenantAndLookupIndexes` 为 14 个含 `TenantId` 且未被复合索引覆盖的实体补 `TenantId` 普通索引(`ApplyTenantIndexes` 反射附加,对齐租户过滤器谓词列),并将 `license_record.LicenseKey` 设为唯一索引;`license.LicenseKey` 唯一索引自 `InitialCreate` 起存在。
>
> 注:原支付域表(`psp_config` / `webhook_event_record` 等)已随商业化裁剪删除,资金流由外部销售系统承接。
>
> ### 9.5 关键表字段差异(文档 vs 实际)
>
> 下表列出第 4 章中与实现差异较大的表,字段以实际实体为准:
>
> **`user`(实际表名 `user_account`)**
>
> | 文档字段 | 实际字段 | 说明 |
> | --- | --- | --- |
> | `email_cipher` / `email_hash` / `phone_cipher` | `email`(明文) | PII 加密未实现 |
> | `full_name` / `locale` / `timezone` / `mfa_*` / `failed_login_count` / `locked_until` | — | 未实现部分由 `DisplayName` / `Status` 承担 |
> | `last_login_at` | `last_login_at` | 一致 |
> | `role` 关联 | `role_ids`(JSON) | 无 `user_role` 表 |
> | `password_hash` 算法(Argon2id) | PBKDF2-HMACSHA256(600000) | 格式 `pbkdf2$600000$salt$hash`,兼容旧 `salt:hash` 与 210000 迭代哈希,登录时透明重哈希 |
>
> **`role`**
>
> | 文档字段 | 实际字段 | 说明 |
> | --- | --- | --- |
> | `code` / `is_system` / `is_builtin` | — | 以 `Name` 作为角色标识 |
> | `name` / `description` / `permissions`(JSON) | 同名 | 一致 |
>
> **`customer`**
>
> | 文档字段 | 实际字段 | 说明 |
> | --- | --- | --- |
> | `organization_id` / `external_id` / `*_cipher` / `country_code` / `tax_id` | — | 未实现 |
> | `email` / `phone` / `company`(明文) | 同名 | PII 未加密 |
>
> **`product`**
>
> | 文档字段 | 实际字段 | 说明 |
> | --- | --- | --- |
> | `business_line` / `min_sdk_version` / `eula_type` / `custom_fields` | — | 未实现 |
> | `default_license_type` / `default_max_activations` / `default_max_devices` / `default_validity` | 同名 | 实际含默认签发参数 |
>
> **`license_template`**
>
> | 文档字段 | 实际字段 | 说明 |
> | --- | --- | --- |
> | `duration_type` / `duration_value` / `subscription_*` / `max_floating_clients` / `lease_*` / `cooldown_*` / `server_sync_*` / `grace*` / `hardware_lock_*` / `fingerprint_*` / `air_gap` / `validation_rules` / `entitlement_set_id` / `tier_id` | — | 未实现,由 `Validity` / `IsPerpetual` / `IsTrial` 简化表达 |
> | `license_type` / `max_devices` / `max_activations` / `features` (JSON) / `metadata` (JSON) | 同名 | 一致 |
>
> **`organization` / `reseller` / `feature` / `entitlement_tier` / `mv_*` / `v_*` 等** | — | 设计目标,当前**未实现**或由其他实体承担 |
>
> ---

## 1. 设计原则

| 原则 | 说明 |
| --- | --- |
| **规范化优先** | 3NF 为基础,关键路径可适度反规范化 |
| **不可变优先** | 审计/事件类表 append-only,只插入不更新 |
| **软删除为主** | `deleted_at`,由 EF Core Query Filter 过滤 |
| **硬删除为辅** | 仅 GDPR DSR,审批后清理 |
| **actor 快照** | 审计写入用户快照,用户变更不影响历史 |
| **PII 加密** | email/电话/地址/税号 等 PII 字段级加密 |
| **多租户隔离** | `tenant_id` 列 + RLS |
| **多品牌隔离** | `brand_id` 列(品牌配置已随商业化裁剪下线,未实现) |
| **时序分区** | license_event / audit_log / heartbeat 等大表按月分区 |
| **冷热分层** | 热数据 12 个月,冷数据归档 S3 兼容存储 |
| **文档完整** | 每张表都明确职责、生命周期、索引 |

---

## 2. 命名约定

### 2.1 表名

- 全部小写 + 下划线分隔(snake_case)
- 单数形式:`user` / `license` / `customer`
- 关联表:`user_role` / `license_feature` / `customer_subscription`
- 事件/日志表:`*_event` / `*_log`
- 计数表(物化):`mv_*`(materialized view)
- 视图:`v_*` 前缀
- 临时表:`tmp_*` 前缀

### 2.2 列名

- 全部小写 + 下划线分隔
- 主键:`id`(UUID v7)
- 外键:`{table}_id`(如 `customer_id`)
- 时间戳:`created_at` / `updated_at` / `deleted_at`
- 布尔:`is_*` 或 `has_*`
- 枚举:`*_type` / `*_status`(用 enum 或 text)
- JSONB:`*_json` / `*_data`
- 加密:`*_cipher`(区分明文 `*_value`)

### 2.3 索引名

- `idx_{table}_{column(s)}`
- 唯一索引:`uk_{table}_{column(s)}`
- 外键索引:`fk_{table}_{ref_table}`(自动)
- 部分索引:`idx_{table}_{column(s)}_where_...`

### 2.4 约束名

- 主键:`pk_{table}`
- 外键:`fk_{table}_{ref_table}`
- 唯一:`uk_{table}_{column(s)}`
- 检查:`ck_{table}_{description}`

---

## 3. 通用字段

每张业务表(除审计/事件/日志)都包含:

| 字段 | 类型 | 说明 |
| --- | --- | --- |
| `id` | UUID | 主键,UUID v7(时间排序) |
| `tenant_id` | UUID NOT NULL | 多租户隔离 |
| `brand_id` | UUID NULL | 多品牌隔离(可空,平台级记录为 NULL)(品牌配置已随商业化裁剪下线,未实现) |
| `created_at` | TIMESTAMPTZ NOT NULL DEFAULT now() | 创建时间 |
| `updated_at` | TIMESTAMPTZ NOT NULL DEFAULT now() | 更新时间(触发器自动) |
| `created_by` | UUID NULL | 创建者 |
| `updated_by` | UUID NULL | 更新者 |
| `deleted_at` | TIMESTAMPTZ NULL | 软删除时间 |
| `version` | INT NOT NULL DEFAULT 1 | 乐观锁版本号 |
| `metadata` | JSONB NULL | 扩展元数据(避免频繁加列) |

### 3.1 通用触发器

- `updated_at` 自动更新(每次 UPDATE)
- `version` 自增(每次 UPDATE)
- `audit_*` 表镜像写入(对所有表)

---

## 4. 表结构

### 4.1 身份与权限

#### 4.1.1 `user` - 用户

```sql
CREATE TABLE "user" (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  email_cipher    BYTEA NOT NULL,           -- AES-GCM 加密
  email_hash      TEXT NOT NULL,            -- HMAC(用于搜索,脱敏)
  email_verified  BOOLEAN NOT NULL DEFAULT FALSE,
  phone_cipher    BYTEA NULL,
  full_name       TEXT NULL,
  display_name    TEXT NULL,
  avatar_url      TEXT NULL,
  locale          TEXT NOT NULL DEFAULT 'en-US',
  timezone        TEXT NOT NULL DEFAULT 'UTC',
  status          TEXT NOT NULL DEFAULT 'active',  -- active, suspended, locked, deactivated
  mfa_enabled     BOOLEAN NOT NULL DEFAULT FALSE,
  mfa_secret_cipher BYTEA NULL,             -- TOTP secret 加密
  last_login_at   TIMESTAMPTZ NULL,
  last_login_ip   INET NULL,
  failed_login_count INT NOT NULL DEFAULT 0,
  locked_until    TIMESTAMPTZ NULL,
  password_hash   TEXT NOT NULL,            -- Argon2id
  password_changed_at TIMESTAMPTZ NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at      TIMESTAMPTZ NULL,
  version         INT NOT NULL DEFAULT 1,
  metadata        JSONB NULL,
  
  CONSTRAINT uk_user_email_hash UNIQUE (tenant_id, email_hash),
  CONSTRAINT ck_user_status CHECK (status IN ('active', 'suspended', 'locked', 'deactivated'))
);

CREATE INDEX idx_user_tenant ON "user" (tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_user_email_hash ON "user" (tenant_id, email_hash);
CREATE INDEX idx_user_status ON "user" (tenant_id, status) WHERE deleted_at IS NULL;
CREATE INDEX idx_user_last_login ON "user" (tenant_id, last_login_at DESC NULLS LAST);
```

#### 4.1.2 `role` - 角色

```sql
CREATE TABLE role (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NULL,                -- NULL 表示平台内置角色
  code            TEXT NOT NULL,            -- 'super_admin' / 'product_manager' / ...
  name            TEXT NOT NULL,
  description     TEXT NULL,
  is_system       BOOLEAN NOT NULL DEFAULT FALSE,  -- 系统内置不可删
  is_builtin      BOOLEAN NOT NULL DEFAULT FALSE,  -- 平台内置
  permissions     JSONB NOT NULL DEFAULT '[]'::jsonb,  -- ['license:read', 'license:write', ...]
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at      TIMESTAMPTZ NULL,
  version         INT NOT NULL DEFAULT 1,
  
  CONSTRAINT uk_role_code UNIQUE (tenant_id, code) WHERE deleted_at IS NULL
);

-- 平台内置角色 seed
INSERT INTO role (code, name, is_builtin, permissions) VALUES
  ('super_admin', 'Super Admin', TRUE, '["*"]'),
  ('product_manager', 'Product Manager', TRUE, '["product:*", "template:*", "feature:*"]'),
  ('sales_ops', 'Sales/Operations', TRUE, '["customer:*", "license:issue"]'),
  ('support', 'Support', TRUE, '["customer:read", "license:read", "ticket:*"]'),
  ('auditor', 'Auditor', TRUE, '["*:read", "audit:read"]'),
  ('customer_admin', 'Customer Admin', TRUE, '["self:*"]'),
  ('customer_user', 'Customer User', TRUE, '["self:read"]');
```

#### 4.1.3 `user_role` - 用户角色关联

```sql
CREATE TABLE user_role (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  user_id         UUID NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
  role_id         UUID NOT NULL REFERENCES role(id) ON DELETE RESTRICT,
  resource_type   TEXT NULL,                -- 资源范围: 'customer' / 'organization' / 'brand'(品牌域已随商业化裁剪下线)
  resource_id     UUID NULL,                -- 资源范围 ID
  granted_by      UUID NULL REFERENCES "user"(id),
  granted_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at      TIMESTAMPTZ NULL,
  
  CONSTRAINT uk_user_role UNIQUE (user_id, role_id, resource_type, resource_id)
);

CREATE INDEX idx_user_role_user ON user_role (user_id);
CREATE INDEX idx_user_role_resource ON user_role (resource_type, resource_id) WHERE resource_id IS NOT NULL;
```

#### 4.1.4 `api_key` - API Key

```sql
CREATE TABLE api_key (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  label           TEXT NOT NULL,            -- 友好名称
  prefix          TEXT NOT NULL,            -- 显示前缀(后 8 位)
  hash            TEXT NOT NULL,            -- SHA-256(key + salt)
  hashed_secret_cipher BYTEA NULL,          -- AES 加密 secret(可选,用于服务端签名场景)
  scopes          JSONB NOT NULL DEFAULT '[]'::jsonb,  -- ['license:activate', 'license:verify']
  status          TEXT NOT NULL DEFAULT 'active',  -- active, revoked, expired
  last_used_at    TIMESTAMPTZ NULL,
  last_used_ip    INET NULL,
  expires_at      TIMESTAMPTZ NULL,
  created_by      UUID NOT NULL REFERENCES "user"(id),
  revoked_by      UUID NULL REFERENCES "user"(id),
  revoked_at      TIMESTAMPTZ NULL,
  revoke_reason   TEXT NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  
  CONSTRAINT uk_api_key_prefix UNIQUE (prefix)
);

CREATE INDEX idx_api_key_tenant ON api_key (tenant_id, status);
CREATE INDEX idx_api_key_expires ON api_key (expires_at) WHERE expires_at IS NOT NULL;
```

#### 4.1.5 `personal_access_token` - 个人访问令牌

```sql
CREATE TABLE personal_access_token (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  user_id         UUID NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
  label           TEXT NOT NULL,
  prefix          TEXT NOT NULL,            -- 显示前缀
  hash            TEXT NOT NULL,            -- SHA-256(token + salt)
  scopes          JSONB NOT NULL DEFAULT '[]'::jsonb,
  expires_at      TIMESTAMPTZ NOT NULL,
  last_used_at    TIMESTAMPTZ NULL,
  last_used_ip    INET NULL,
  revoked_at      TIMESTAMPTZ NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  
  CONSTRAINT uk_pat_prefix UNIQUE (prefix)
);

CREATE INDEX idx_pat_user ON personal_access_token (user_id, expires_at DESC);
CREATE INDEX idx_pat_expires ON personal_access_token (expires_at);
```

### 4.2 组织与客户

#### 4.2.1 `organization` - 组织(层级树)

```sql
CREATE TABLE organization (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  parent_id       UUID NULL REFERENCES organization(id) ON DELETE RESTRICT,
  name            TEXT NOT NULL,
  code            TEXT NOT NULL,            -- 短代码
  level           INT NOT NULL DEFAULT 0,   -- 层级深度
  path            LTREE NOT NULL,           -- 物化路径
  status          TEXT NOT NULL DEFAULT 'active',
  max_licenses    INT NULL,                 -- 配额
  max_devices     INT NULL,
  metadata        JSONB NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at      TIMESTAMPTZ NULL,
  version         INT NOT NULL DEFAULT 1,
  
  CONSTRAINT uk_org_code UNIQUE (tenant_id, code) WHERE deleted_at IS NULL,
  CONSTRAINT ck_org_status CHECK (status IN ('active', 'suspended', 'merged'))
);

CREATE INDEX idx_org_tenant ON organization (tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_org_parent ON organization (parent_id);
CREATE INDEX idx_org_path_gist ON organization USING GIST (path);
```

#### 4.2.2 `customer` - 客户(企业)

```sql
CREATE TABLE customer (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  organization_id UUID NOT NULL REFERENCES organization(id),
  name            TEXT NOT NULL,
  code            TEXT NOT NULL,            -- 客户代码
  external_id     TEXT NULL,                -- 外部 CRM ID
  hubspot_account_id TEXT NULL,
  stripe_customer_id TEXT NULL,
  salesforce_account_id TEXT NULL,
  primary_email_cipher BYTEA NOT NULL,
  primary_email_hash TEXT NOT NULL,
  country_code    CHAR(2) NULL,             -- ISO 3166-1
  tax_id_cipher   BYTEA NULL,               -- 税号加密
  vat_number_cipher BYTEA NULL,             -- 欧盟 VAT 加密
  status          TEXT NOT NULL DEFAULT 'active',
  tier            TEXT NULL,                -- standard / gold / platinum
  metadata        JSONB NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at      TIMESTAMPTZ NULL,
  version         INT NOT NULL DEFAULT 1,
  
  CONSTRAINT uk_customer_code UNIQUE (tenant_id, code) WHERE deleted_at IS NULL,
  CONSTRAINT uk_customer_external UNIQUE (tenant_id, external_id) WHERE external_id IS NOT NULL
);

CREATE INDEX idx_customer_tenant ON customer (tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_customer_org ON customer (organization_id);
CREATE INDEX idx_customer_email_hash ON customer (tenant_id, primary_email_hash);
CREATE INDEX idx_customer_external ON customer (tenant_id, external_id) WHERE external_id IS NOT NULL;
```

#### 4.2.3 `reseller` - 渠道商

```sql
CREATE TABLE reseller (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  name            TEXT NOT NULL,
  code            TEXT NOT NULL,
  tier            TEXT NOT NULL DEFAULT 'authorized',  -- authorized / gold / platinum
  contact_email_cipher BYTEA NOT NULL,
  contact_email_hash TEXT NOT NULL,
  status          TEXT NOT NULL DEFAULT 'active',
  contract_start  DATE NULL,
  contract_end    DATE NULL,
  commission_rate NUMERIC(5,2) NULL,
  metadata        JSONB NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at      TIMESTAMPTZ NULL,
  version         INT NOT NULL DEFAULT 1,
  
  CONSTRAINT uk_reseller_code UNIQUE (tenant_id, code) WHERE deleted_at IS NULL
);
```

### 4.3 产品与模板

#### 4.3.1 `product` - 产品

```sql
CREATE TABLE product (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  code            TEXT NOT NULL,            -- 产品代码
  name            TEXT NOT NULL,
  description     TEXT NULL,
  business_line   TEXT NULL,
  status          TEXT NOT NULL DEFAULT 'active',  -- draft, active, deprecated, archived
  min_sdk_version TEXT NULL,
  eula_type       TEXT NOT NULL DEFAULT 'workstation',  -- workstation, server, enterprise, oem
  custom_fields   JSONB NULL,               -- 产品级自定义字段定义
  metadata        JSONB NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at      TIMESTAMPTZ NULL,
  version         INT NOT NULL DEFAULT 1,
  
  CONSTRAINT uk_product_code UNIQUE (tenant_id, code) WHERE deleted_at IS NULL,
  CONSTRAINT ck_product_status CHECK (status IN ('draft', 'active', 'deprecated', 'archived'))
);

CREATE INDEX idx_product_tenant ON product (tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_product_status ON product (tenant_id, status);
```

#### 4.3.2 `feature` - 特性

```sql
CREATE TABLE feature (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  product_id      UUID NOT NULL REFERENCES product(id) ON DELETE CASCADE,
  code            TEXT NOT NULL,            -- 特性代码
  name            TEXT NOT NULL,
  type            TEXT NOT NULL,            -- bool / int / float / string / datetime / binary
  default_value   JSONB NULL,
  description     TEXT NULL,
  is_consumption  BOOLEAN NOT NULL DEFAULT FALSE,  -- 是否为计量特性
  max_consumption NUMERIC NULL,             -- 最大消耗
  max_overages    NUMERIC NULL,             -- 软限
  allow_unlimited BOOLEAN NOT NULL DEFAULT FALSE,
  allow_negative  BOOLEAN NOT NULL DEFAULT FALSE,
  reset_interval  TEXT NULL,                -- daily/weekly/monthly/annually
  metadata        JSONB NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at      TIMESTAMPTZ NULL,
  version         INT NOT NULL DEFAULT 1,
  
  CONSTRAINT uk_feature_code UNIQUE (product_id, code) WHERE deleted_at IS NULL,
  CONSTRAINT ck_feature_type CHECK (type IN ('bool', 'int', 'float', 'string', 'datetime', 'binary'))
);

CREATE INDEX idx_feature_product ON feature (product_id) WHERE deleted_at IS NULL;
```

#### 4.3.3 `entitlement_set` - 权利集

```sql
CREATE TABLE entitlement_set (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  product_id      UUID NOT NULL REFERENCES product(id) ON DELETE CASCADE,
  code            TEXT NOT NULL,            -- 'standard' / 'pro' / 'enterprise'
  name            TEXT NOT NULL,
  description     TEXT NULL,
  is_default      BOOLEAN NOT NULL DEFAULT FALSE,
  metadata        JSONB NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at      TIMESTAMPTZ NULL,
  version         INT NOT NULL DEFAULT 1,
  
  CONSTRAINT uk_eset_code UNIQUE (product_id, code) WHERE deleted_at IS NULL
);
```

#### 4.3.4 `entitlement_tier` - 权利档位

```sql
CREATE TABLE entitlement_tier (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  entitlement_set_id UUID NOT NULL REFERENCES entitlement_set(id) ON DELETE CASCADE,
  code            TEXT NOT NULL,            -- 'starter' / 'growth' / 'scale'
  name            TEXT NOT NULL,
  rank            INT NOT NULL,             -- 排序
  feature_overrides JSONB NOT NULL DEFAULT '{}'::jsonb,  -- { feature_code: value }
  metadata        JSONB NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at      TIMESTAMPTZ NULL,
  
  CONSTRAINT uk_tier_code UNIQUE (entitlement_set_id, code) WHERE deleted_at IS NULL
);
```

#### 4.3.5 `license_template` - 授权模板

```sql
CREATE TABLE license_template (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  product_id      UUID NOT NULL REFERENCES product(id),
  name            TEXT NOT NULL,
  code            TEXT NOT NULL,
  license_type    TEXT NOT NULL,            -- node_locked / floating / consumption / named_user / bundle / trial / perpetual
  duration_type   TEXT NOT NULL,            -- perpetual / subscription / timed / trial
  duration_value  INT NULL,                 -- 天数(若 timed)
  subscription_interval TEXT NULL,          -- monthly/quarterly/annually(若 subscription)
  subscription_start_trigger TEXT NULL,     -- immediate / first_activation / scheduled
  max_devices     INT NULL,                 -- -1 表示无限制
  max_activations INT NULL,                 -- -1 表示无限制
  max_floating_clients INT NULL,
  allow_transfer  BOOLEAN NOT NULL DEFAULT TRUE,
  transfer_limit  INT NULL,                 -- 转移次数
  cooldown_time_seconds INT NULL,
  lease_duration_seconds INT NULL,
  lease_time_check_out_seconds INT NULL,
  lease_time_cache_seconds INT NULL,
  server_sync_interval_seconds INT NULL,    -- 最小 180
  server_sync_grace_period_seconds INT NULL,
  grace_period_seconds INT NOT NULL DEFAULT 0,
  hardware_lock_enabled BOOLEAN NOT NULL DEFAULT TRUE,
  fingerprint_components JSONB NULL,        -- ['cpu', 'mac', 'disk', ...]
  allow_air_gap   BOOLEAN NOT NULL DEFAULT FALSE,
  allow_offline   BOOLEAN NOT NULL DEFAULT TRUE,
  clock_skew_tolerance_seconds INT NOT NULL DEFAULT 60,
  validation_rules JSONB NULL,              -- [{ "type": "...", "params": {...} }]
  entitlement_set_id UUID NULL REFERENCES entitlement_set(id),
  tier_id         UUID NULL REFERENCES entitlement_tier(id),
  feature_overrides JSONB NULL,             -- 模板级覆盖
  status          TEXT NOT NULL DEFAULT 'draft',
  custom_fields   JSONB NULL,               -- 模板级自定义字段
  metadata        JSONB NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at      TIMESTAMPTZ NULL,
  version         INT NOT NULL DEFAULT 1,
  
  CONSTRAINT uk_template_code UNIQUE (tenant_id, product_id, code) WHERE deleted_at IS NULL,
  CONSTRAINT ck_template_license_type CHECK (license_type IN ('node_locked', 'floating', 'consumption', 'named_user', 'bundle', 'trial', 'perpetual'))
);

CREATE INDEX idx_template_product ON license_template (product_id) WHERE deleted_at IS NULL;
```

### 4.4 授权核心

#### 4.4.1 `license` - 授权

```sql
CREATE TABLE license (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  customer_id     UUID NOT NULL REFERENCES customer(id),
  product_id      UUID NOT NULL REFERENCES product(id),
  template_id     UUID NOT NULL REFERENCES license_template(id),
  subscription_id UUID NULL,                -- 关联订阅
  
  -- 标识
  license_key     TEXT NOT NULL,            -- 业务 key
  activation_key  TEXT NULL,                -- 激活码
  key_version     INT NOT NULL DEFAULT 1,   -- 系统密钥版本
  
  -- 类型与状态
  license_type    TEXT NOT NULL,
  status          TEXT NOT NULL DEFAULT 'not_activated',
  is_trial        BOOLEAN NOT NULL DEFAULT FALSE,
  is_perpetual    BOOLEAN NOT NULL DEFAULT FALSE,
  
  -- 期限
  issued_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  first_activated_at TIMESTAMPTZ NULL,
  activated_at    TIMESTAMPTZ NULL,
  expires_at      TIMESTAMPTZ NULL,
  grace_until     TIMESTAMPTZ NULL,
  
  -- 数量限制
  max_devices     INT NULL,
  max_activations INT NULL,
  max_floating_clients INT NULL,
  current_devices INT NOT NULL DEFAULT 0,
  current_activations INT NOT NULL DEFAULT 0,
  
  -- 浮动
  lease_duration_seconds INT NULL,
  cooldown_until  TIMESTAMPTZ NULL,
  
  -- 订阅
  subscription_interval TEXT NULL,
  subscription_start_trigger TEXT NULL,
  next_renewal_at TIMESTAMPTZ NULL,
  
  -- 权利
  entitlement_set_id UUID NULL,
  tier_id         UUID NULL,
  feature_values  JSONB NOT NULL DEFAULT '{}'::jsonb,  -- { feature_code: value }
  feature_expirations JSONB NULL,            -- { feature_code: expires_at }
  
  -- 签名
  signed_payload  TEXT NULL,                -- 签名 payload(Base64)
  signature       TEXT NULL,                -- 签名
  
  -- 自定义字段
  custom_fields   JSONB NULL,
  
  -- 备注
  notes           TEXT NULL,
  
  -- 生命周期
  suspended_at    TIMESTAMPTZ NULL,
  suspended_reason TEXT NULL,
  revoked_at      TIMESTAMPTZ NULL,
  revoked_reason  TEXT NULL,
  
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at      TIMESTAMPTZ NULL,
  version         INT NOT NULL DEFAULT 1,
  metadata        JSONB NULL,
  
  CONSTRAINT uk_license_key UNIQUE (tenant_id, license_key),
  CONSTRAINT uk_license_activation_key UNIQUE (activation_key) WHERE activation_key IS NOT NULL,
  CONSTRAINT ck_license_status CHECK (status IN ('not_activated', 'active', 'inactive', 'disabled', 'suspended', 'expired', 'revoked'))
);

CREATE INDEX idx_license_tenant ON license (tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_license_customer ON license (customer_id);
CREATE INDEX idx_license_product ON license (product_id);
CREATE INDEX idx_license_status ON license (tenant_id, status);
CREATE INDEX idx_license_expires ON license (expires_at) WHERE status IN ('active', 'suspended');
CREATE INDEX idx_license_subscription ON license (subscription_id) WHERE subscription_id IS NOT NULL;
CREATE INDEX idx_license_renewal ON license (next_renewal_at) WHERE next_renewal_at IS NOT NULL;
```

#### 4.4.2 `activation` - 激活

```sql
CREATE TABLE activation (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  license_id      UUID NOT NULL REFERENCES license(id) ON DELETE CASCADE,
  device_id       UUID NOT NULL,
  user_id         UUID NULL,                -- Named User 时使用
  
  -- 设备
  hardware_id     TEXT NOT NULL,
  hostname        TEXT NULL,
  os_version      TEXT NULL,
  sdk_version     TEXT NULL,
  ip_address      INET NULL,
  
  -- 状态
  status          TEXT NOT NULL DEFAULT 'active',  -- active, deactivated, lease_expired
  
  -- 浮动 lease
  lease_token     TEXT NULL,                -- 浮动 lease token
  lease_expires_at TIMESTAMPTZ NULL,
  
  -- 时间
  activated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_verified_at TIMESTAMPTZ NULL,
  last_heartbeat_at TIMESTAMPTZ NULL,
  deactivated_at  TIMESTAMPTZ NULL,
  
  -- Scope
  activation_scope TEXT NOT NULL DEFAULT 'device',  -- device / user / system
  scope_migrated_at TIMESTAMPTZ NULL,        -- scope 迁移时间
  
  metadata        JSONB NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  
  CONSTRAINT uk_activation UNIQUE (license_id, hardware_id, activation_scope)
);

CREATE INDEX idx_activation_license ON activation (license_id);
CREATE INDEX idx_activation_device ON activation (device_id);
CREATE INDEX idx_activation_hardware ON activation (hardware_id);
CREATE INDEX idx_activation_lease ON activation (lease_expires_at) WHERE lease_expires_at IS NOT NULL;
CREATE INDEX idx_activation_heartbeat ON activation (last_heartbeat_at);
```

#### 4.4.3 `device` - 设备

```sql
CREATE TABLE device (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  customer_id     UUID NOT NULL REFERENCES customer(id),
  hardware_id     TEXT NOT NULL,
  hostname        TEXT NULL,
  os              TEXT NULL,
  os_version      TEXT NULL,
  ip_address      INET NULL,
  
  first_seen_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_seen_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  sdk_version     TEXT NULL,
  
  status          TEXT NOT NULL DEFAULT 'active',  -- active, blacklisted, deactivated
  
  device_group_id UUID NULL,
  metadata        JSONB NULL,
  
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at      TIMESTAMPTZ NULL,
  version         INT NOT NULL DEFAULT 1,
  
  CONSTRAINT uk_device_hardware UNIQUE (tenant_id, customer_id, hardware_id) WHERE deleted_at IS NULL
);

CREATE INDEX idx_device_customer ON device (customer_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_device_status ON device (tenant_id, status);
```

#### 4.4.4 `heartbeat` - 心跳(分区表)

```sql
CREATE TABLE heartbeat (
  id              UUID DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  activation_id   UUID NOT NULL,
  device_id       UUID NOT NULL,
  license_id      UUID NOT NULL,
  received_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  ip_address      INET NULL,
  payload         JSONB NULL,
  error_code      TEXT NULL,
  
  PRIMARY KEY (id, received_at)
) PARTITION BY RANGE (received_at);

-- 月度分区
CREATE TABLE heartbeat_2026_07 PARTITION OF heartbeat
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
-- ... 持续添加

CREATE INDEX idx_heartbeat_activation ON heartbeat (activation_id, received_at DESC);
CREATE INDEX idx_heartbeat_device ON heartbeat (device_id, received_at DESC);
```

#### 4.4.5 `consumption_log` - 计量日志(分区表)

```sql
CREATE TABLE consumption_log (
  id              UUID DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  license_id      UUID NOT NULL,
  feature_code    TEXT NOT NULL,
  delta           NUMERIC NOT NULL,
  total_after     NUMERIC NOT NULL,
  period_start    TIMESTAMPTZ NOT NULL,
  period_end      TIMESTAMPTZ NOT NULL,
  source          TEXT NOT NULL,            -- 'sdk_local' / 'sdk_server' / 'admin'
  activation_id   UUID NULL,
  occurred_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  metadata        JSONB NULL,
  
  PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);

CREATE INDEX idx_consumption_license_feature ON consumption_log (license_id, feature_code, occurred_at DESC);
```

#### 4.4.6 `license_event` - 授权事件(分区表,不可变)

```sql
CREATE TABLE license_event (
  id              UUID DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  license_id      UUID NOT NULL,
  event_type      TEXT NOT NULL,            -- 'created' / 'activated' / 'first_activated' / 'deactivated' / 'expires' / 'expires_at_updated' / 'suspended' / 'resumed' / 'revoked' / 'subscription_updated' / 'subscription_expired' / 'lease_expired' / 'activation_limit_reached' / 'feature_consumed' / 'feature_expired' / 'device_registered' / 'device_deactivated'
  event_data      JSONB NOT NULL,
  actor_type      TEXT NOT NULL,            -- 'user' / 'system' / 'sdk' / 'webhook'
  actor_id        UUID NULL,
  actor_snapshot  JSONB NULL,               -- 用户快照
  request_id      UUID NULL,                -- 关联请求
  source_ip       INET NULL,
  occurred_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  
  PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);

CREATE INDEX idx_license_event_license ON license_event (license_id, occurred_at DESC);
CREATE INDEX idx_license_event_type ON license_event (event_type, occurred_at DESC);
```

#### 4.4.7 `refresh_token` - 离线刷新令牌

```sql
CREATE TABLE refresh_token (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  license_id      UUID NOT NULL REFERENCES license(id) ON DELETE CASCADE,
  token           TEXT NOT NULL,            -- 一次性 refresh token
  used_at         TIMESTAMPTZ NULL,
  expires_at      TIMESTAMPTZ NOT NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  
  CONSTRAINT uk_refresh_token UNIQUE (token)
);

CREATE INDEX idx_refresh_license ON refresh_token (license_id);
```

### 4.5 订阅

> 注:原 4.5.1 `order`、4.5.2 `order_item`、4.5.5 `payment_intent`、4.5.6 `invoice`、4.5.7 `refund` 及 `quote` / `payment_method` / `currency` / `exchange_rate` / `psp_config` 等表已随商业化裁剪删除,资金流由外部销售系统承接。

#### 4.5.1 `subscription` - 订阅

```sql
CREATE TABLE subscription (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  customer_id     UUID NOT NULL REFERENCES customer(id),
  product_id      UUID NOT NULL REFERENCES product(id),
  
  status          TEXT NOT NULL DEFAULT 'active',  -- trialing, active, past_due, canceled, paused
  
  interval_type   TEXT NOT NULL,            -- daily, weekly, monthly, quarterly, annually
  interval_count  INT NOT NULL DEFAULT 1,
  start_trigger   TEXT NOT NULL DEFAULT 'immediate',  -- immediate, first_activation, scheduled
  start_at        TIMESTAMPTZ NOT NULL,
  
  current_period_start TIMESTAMPTZ NOT NULL,
  current_period_end   TIMESTAMPTZ NOT NULL,
  next_renewal_at TIMESTAMPTZ NULL,
  
  external_id     TEXT NULL,                -- 外部订阅 ID(外部销售系统)
  provider        TEXT NULL,                -- 外部提供方标识(PSP 直连已随商业化裁剪下线)
  
  cancel_at       TIMESTAMPTZ NULL,
  canceled_at     TIMESTAMPTZ NULL,
  cancel_reason   TEXT NULL,
  trial_end_at    TIMESTAMPTZ NULL,
  
  metadata        JSONB NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at      TIMESTAMPTZ NULL,
  version         INT NOT NULL DEFAULT 1,
  
  CONSTRAINT uk_sub_external UNIQUE (provider, external_id) WHERE external_id IS NOT NULL
);

CREATE INDEX idx_sub_customer ON subscription (customer_id);
CREATE INDEX idx_sub_status ON subscription (tenant_id, status);
CREATE INDEX idx_sub_renewal ON subscription (next_renewal_at) WHERE next_renewal_at IS NOT NULL;
CREATE INDEX idx_sub_period_end ON subscription (current_period_end);
```

#### 4.5.2 `subscription_log` - 订阅日志(不可变)

```sql
CREATE TABLE subscription_log (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  subscription_id UUID NOT NULL REFERENCES subscription(id),
  event_type      TEXT NOT NULL,            -- 'created' / 'renewed' / 'payment_failed' / 'canceled' / 'paused' / 'resumed' / 'upgraded' / 'downgraded' / 'expired'
  event_data      JSONB NOT NULL,
  actor_type      TEXT NOT NULL,
  actor_id        UUID NULL,
  occurred_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_sub_log_sub ON subscription_log (subscription_id, occurred_at DESC);
```

### 4.6 浮动

#### 4.6.1 `floating_server` - 浮动 server

```sql
CREATE TABLE floating_server (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  customer_id     UUID NULL REFERENCES customer(id),
  name            TEXT NOT NULL,
  hostname        TEXT NULL,
  region          TEXT NULL,
  version         TEXT NULL,
  capacity        INT NOT NULL DEFAULT 0,
  status          TEXT NOT NULL DEFAULT 'active',  -- active, suspended, deregistered
  
  shared_key_hash TEXT NOT NULL,            -- 共享密钥 hash
  shared_key_cipher BYTEA NOT NULL,         -- 共享密钥加密
  
  last_seen_at    TIMESTAMPTZ NULL,
  registered_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  metadata        JSONB NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at      TIMESTAMPTZ NULL,
  version         INT NOT NULL DEFAULT 1
);

CREATE INDEX idx_fs_tenant ON floating_server (tenant_id) WHERE deleted_at IS NULL;
```

#### 4.6.2 `lease` - 浮动席位借出

```sql
CREATE TABLE lease (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  license_id      UUID NOT NULL REFERENCES license(id) ON DELETE CASCADE,
  floating_server_id UUID NOT NULL REFERENCES floating_server(id),
  activation_id   UUID NULL REFERENCES activation(id),
  
  lease_token     TEXT NOT NULL,
  hardware_id     TEXT NOT NULL,
  
  status          TEXT NOT NULL DEFAULT 'active',  -- active, expired, released
  
  issued_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at      TIMESTAMPTZ NOT NULL,
  released_at     TIMESTAMPTZ NULL,
  
  renewal_count   INT NOT NULL DEFAULT 0,
  last_renewed_at TIMESTAMPTZ NULL,
  
  metadata        JSONB NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  
  CONSTRAINT uk_lease_token UNIQUE (lease_token)
);

CREATE INDEX idx_lease_license ON lease (license_id);
CREATE INDEX idx_lease_expires ON lease (expires_at) WHERE status = 'active';
```

### 4.7 审计与可观测

#### 4.7.1 `audit_log` - 审计日志(分区表,不可变,WORM 存储)

```sql
CREATE TABLE audit_log (
  id              UUID DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  
  -- 操作
  action          TEXT NOT NULL,            -- 'create' / 'update' / 'delete' / 'login' / 'logout' / 'export' / ...
  resource_type   TEXT NOT NULL,            -- 'license' / 'customer' / 'user' / ...
  resource_id     UUID NULL,
  
  -- 前后值
  before_value    JSONB NULL,
  after_value     JSONB NULL,
  diff            JSONB NULL,
  
  -- actor 快照(关键)
  actor_type      TEXT NOT NULL,            -- 'user' / 'system' / 'sdk' / 'webhook' / 'api_key' / 'pat'
  actor_id        UUID NULL,
  actor_snapshot  JSONB NOT NULL,           -- { id, email, role, ip, user_agent, ... }
  
  -- 请求上下文
  request_id      UUID NULL,
  trace_id        TEXT NULL,
  source_ip       INET NULL,
  user_agent      TEXT NULL,
  
  -- 业务上下文
  customer_id     UUID NULL,
  brand_id        UUID NULL,
  
  occurred_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  
  PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);

-- WORM:REVOKE UPDATE, DELETE ON audit_log FROM PUBLIC;

CREATE INDEX idx_audit_tenant_time ON audit_log (tenant_id, occurred_at DESC);
CREATE INDEX idx_audit_resource ON audit_log (resource_type, resource_id, occurred_at DESC);
CREATE INDEX idx_audit_actor ON audit_log ((actor_snapshot->>'id'), occurred_at DESC);
```

#### 4.7.2 `webhook_endpoint` - Webhook 端点

```sql
CREATE TABLE webhook_endpoint (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  url             TEXT NOT NULL,
  description     TEXT NULL,
  events          JSONB NOT NULL,           -- ['license.*', 'subscription.renewed', ...]
  secret_cipher   BYTEA NOT NULL,           -- HMAC 签名 secret
  status          TEXT NOT NULL DEFAULT 'active',  -- active, suspended
  version         INT NOT NULL DEFAULT 1,   -- 端点版本
  
  max_retries     INT NOT NULL DEFAULT 10,
  timeout_seconds INT NOT NULL DEFAULT 30,
  
  created_by      UUID NOT NULL REFERENCES "user"(id),
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at      TIMESTAMPTZ NULL
);
```

#### 4.7.3 `webhook_delivery` - Webhook 投递(分区表)

```sql
CREATE TABLE webhook_delivery (
  id              UUID DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  endpoint_id     UUID NOT NULL,
  event_id        UUID NOT NULL,            -- 唯一事件 ID(去重)
  event_type      TEXT NOT NULL,
  payload         JSONB NOT NULL,
  
  status          TEXT NOT NULL DEFAULT 'pending',  -- pending, succeeded, failed, suspended, dead_letter
  
  attempt_count   INT NOT NULL DEFAULT 0,
  max_attempts    INT NOT NULL DEFAULT 10,
  
  next_retry_at   TIMESTAMPTZ NULL,
  last_attempt_at TIMESTAMPTZ NULL,
  last_response_status INT NULL,
  last_response_body TEXT NULL,
  
  succeeded_at    TIMESTAMPTZ NULL,
  failed_at       TIMESTAMPTZ NULL,
  suspended_at    TIMESTAMPTZ NULL,
  
  occurred_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  
  PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);

CREATE INDEX idx_delivery_endpoint ON webhook_delivery (endpoint_id, occurred_at DESC);
CREATE INDEX idx_delivery_status ON webhook_delivery (status, next_retry_at) WHERE status = 'pending';
```

### 4.8 通知

> 注:原 4.8.1 `brand_profile`(品牌档案)已随商业化裁剪删除,品牌配置由外部销售系统承接。

#### 4.8.1 `notification` - 通知

```sql
CREATE TABLE notification (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  user_id         UUID NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
  
  type            TEXT NOT NULL,            -- 'email' / 'sms' / 'webhook' / 'in_app'
  category        TEXT NOT NULL,            -- 'license.expiring' / 'payment.failed' / ...
  subject         TEXT NULL,
  body            TEXT NOT NULL,
  payload         JSONB NULL,
  
  status          TEXT NOT NULL DEFAULT 'pending',  -- pending, sent, failed, read
  
  sent_at         TIMESTAMPTZ NULL,
  read_at         TIMESTAMPTZ NULL,
  failed_reason   TEXT NULL,
  
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_notif_user ON notification (user_id, created_at DESC);
CREATE INDEX idx_notif_status ON notification (status, created_at) WHERE status = 'pending';
```

### 4.9 安全

#### 4.9.1 `system_key` - 系统密钥(签名密钥)

```sql
CREATE TABLE system_key (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  
  key_version     INT NOT NULL,
  algorithm       TEXT NOT NULL DEFAULT 'RSA-2048',
  
  public_key      TEXT NOT NULL,            -- PEM(Base64)
  private_key_cipher BYTEA NULL,            -- AES 加密的私钥(KMS 加密更佳)
  private_key_hsm_ref TEXT NULL,            -- HSM 引用
  
  is_active       BOOLEAN NOT NULL DEFAULT TRUE,
  is_primary      BOOLEAN NOT NULL DEFAULT FALSE,
  
  activated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at      TIMESTAMPTZ NULL,         -- 轮换时间
  
  created_by      UUID NOT NULL REFERENCES "user"(id),
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  
  CONSTRAINT uk_key_version UNIQUE (tenant_id, key_version)
);
```

#### 4.9.2 `idp_config` - 身份提供方

```sql
CREATE TABLE idp_config (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  
  provider        TEXT NOT NULL,            -- 'azure_ad' / 'okta' / 'keycloak' / ...
  client_id       TEXT NOT NULL,
  additional_client_ids JSONB NULL,         -- 多客户端
  issuer_url      TEXT NOT NULL,
  default_role    TEXT NULL,
  email_claim     TEXT NOT NULL DEFAULT 'email',
  name_claim      TEXT NOT NULL DEFAULT 'name',
  role_claim      TEXT NOT NULL DEFAULT 'role',
  scope           TEXT NOT NULL DEFAULT 'openid profile email',
  
  secret_cipher   BYTEA NULL,
  metadata_url    TEXT NULL,
  status          TEXT NOT NULL DEFAULT 'active',
  
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at      TIMESTAMPTZ NULL,
  
  CONSTRAINT uk_idp_provider UNIQUE (tenant_id, provider) WHERE deleted_at IS NULL
);
```

### 4.10 工单与支持

#### 4.10.1 `ticket` - 工单

```sql
CREATE TABLE ticket (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  number          TEXT NOT NULL,            -- 友好编号, TICK-2026-00001
  
  customer_id     UUID NULL REFERENCES customer(id),
  user_id         UUID NULL REFERENCES "user"(id),
  assigned_to     UUID NULL REFERENCES "user"(id),
  
  subject         TEXT NOT NULL,
  description     TEXT NOT NULL,
  category        TEXT NOT NULL,            -- 'technical' / 'billing' / 'account' / ...
  priority        TEXT NOT NULL DEFAULT 'normal',  -- 'low' / 'normal' / 'high' / 'urgent'
  status          TEXT NOT NULL DEFAULT 'open',  -- 'open' / 'pending' / 'in_progress' / 'resolved' / 'closed'
  
  sla_response_due_at TIMESTAMPTZ NULL,
  sla_resolve_due_at TIMESTAMPTZ NULL,
  first_responded_at TIMESTAMPTZ NULL,
  resolved_at     TIMESTAMPTZ NULL,
  closed_at       TIMESTAMPTZ NULL,
  
  satisfaction_rating INT NULL,             -- 1-5
  
  metadata        JSONB NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at      TIMESTAMPTZ NULL,
  version         INT NOT NULL DEFAULT 1,
  
  CONSTRAINT uk_ticket_number UNIQUE (tenant_id, number) WHERE deleted_at IS NULL
);
```

### 4.11 其它辅助表

#### 4.11.1 `release` - 软件发布

```sql
CREATE TABLE release (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  tenant_id       UUID NOT NULL,
  product_id      UUID NOT NULL REFERENCES product(id),
  version         TEXT NOT NULL,            -- 1.0.0.123
  channel         TEXT NOT NULL DEFAULT 'stable',  -- stable, beta, rc, lts
  status          TEXT NOT NULL DEFAULT 'draft',  -- draft, published, archived
  notes           TEXT NULL,
  min_license_version TEXT NULL,           -- 最低授权版本
  published_at    TIMESTAMPTZ NULL,
  archived_at     TIMESTAMPTZ NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at      TIMESTAMPTZ NULL,
  
  CONSTRAINT uk_release UNIQUE (product_id, version, channel) WHERE deleted_at IS NULL
);
```

#### 4.11.2 `release_file` - 发布文件

```sql
CREATE TABLE release_file (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  release_id      UUID NOT NULL REFERENCES release(id) ON DELETE CASCADE,
  platform        TEXT NOT NULL,            -- windows-x64 / linux-arm64 / macos-universal / ...
  filename        TEXT NOT NULL,
  storage_key     TEXT NOT NULL,            -- S3 key
  size_bytes      BIGINT NOT NULL,
  sha256          TEXT NOT NULL,
  signature       TEXT NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

---

## 5. 索引策略总览

### 5.1 索引原则

- **覆盖高频查询**:WHERE / ORDER BY / JOIN 列
- **复合索引前缀**:列顺序按选择性高 → 低
- **部分索引**:WHERE deleted_at IS NULL / WHERE status = 'pending'
- **避免冗余**:已被覆盖的列不重复建
- **不索引大字段**:JSONB / TEXT 不建 BTree,用 GIN
- **GIN / GIST**:JSONB、数组、LTREE 用 GIN/LMGIST

### 5.2 主要索引分类

| 类型 | 例子 |
| --- | --- |
| 主键 | UUID v7,`id` |
| 唯一 | `(tenant_id, email_hash)`、`license_key` |
| 外键 | `customer_id` / `license_id` |
| 复合 | `(tenant_id, status, created_at DESC)` |
| 部分 | `WHERE deleted_at IS NULL` |
| JSONB | GIN(`feature_values`) |
| 数组 | GIN(`license_ids`) |
| 网络 | `INET` 类型自带索引 |
| 时间 | BRIN(`occurred_at`) 用于时序表 |

---

## 6. 约束与触发器

### 6.1 通用触发器

```sql
-- updated_at 自动更新
CREATE OR REPLACE FUNCTION trigger_set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  NEW.version = OLD.version + 1;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 应用到所有业务表
CREATE TRIGGER set_updated_at BEFORE UPDATE ON license
  FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();
```

### 6.2 审计镜像触发器

```sql
-- 监听关键表,写入 audit_log
CREATE OR REPLACE FUNCTION trigger_audit_log()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (
    tenant_id, action, resource_type, resource_id,
    before_value, after_value,
    actor_type, actor_id, actor_snapshot
  ) VALUES (
    COALESCE(NEW.tenant_id, OLD.tenant_id),
    TG_OP,
    TG_TABLE_NAME,
    COALESCE(NEW.id, OLD.id),
    to_jsonb(OLD),
    to_jsonb(NEW),
    current_setting('app.actor_type', true),
    current_setting('app.actor_id', true)::uuid,
    current_setting('app.actor_snapshot', true)::jsonb
  );
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;
```

### 6.3 关键检查约束

```sql
-- 数量限制
ALTER TABLE license ADD CONSTRAINT ck_license_max_devices_positive
  CHECK (max_devices IS NULL OR max_devices >= -1);
ALTER TABLE license ADD CONSTRAINT ck_license_count_le_max
  CHECK (current_devices <= COALESCE(max_devices, 999999) OR max_devices = -1);

-- 订阅日期
ALTER TABLE subscription ADD CONSTRAINT ck_sub_period_valid
  CHECK (current_period_end > current_period_start);

-- 续费日期
ALTER TABLE subscription ADD CONSTRAINT ck_sub_renewal_after
  CHECK (next_renewal_at IS NULL OR next_renewal_at >= current_period_end);

-- License 期限
ALTER TABLE license ADD CONSTRAINT ck_license_grace
  CHECK (grace_until IS NULL OR grace_until >= expires_at);
```

---

## 7. 多租户隔离

### 7.1 策略

- **应用层**:EF Core Global Query Filter + 业务逻辑校验
- **数据库层**:PostgreSQL Row-Level Security(RLS)
- **强制**:任何查询必须带 `tenant_id`

### 7.2 RLS 策略示例

```sql
-- 启用 RLS
ALTER TABLE license ENABLE ROW LEVEL SECURITY;
ALTER TABLE license FORCE ROW LEVEL SECURITY;

-- 策略:tenant_id 必须匹配会话变量
CREATE POLICY tenant_isolation_policy ON license
  USING (tenant_id::text = current_setting('app.current_tenant_id', true));
```

```csharp
// EF Core 中
public override Task<int> SaveChangesAsync(CancellationToken ct = default)
{
    var tenantId = _tenantContext.TenantId;
    
    // 设置会话变量,用于 RLS
    var conn = (NpgsqlConnection)Database.GetDbConnection();
    using var cmd = conn.CreateCommand();
    cmd.CommandText = $"SET app.current_tenant_id = '{tenantId}'";
    if (conn.State != ConnectionState.Open) conn.Open();
    cmd.ExecuteNonQuery();
    
    return base.SaveChangesAsync(ct);
}
```

---

## 8. 软删除与硬删除

### 8.1 软删除(默认)

```sql
ALTER TABLE license ADD COLUMN deleted_at TIMESTAMPTZ NULL;
```

```csharp
// EF Core Query Filter
modelBuilder.Entity<License>()
  .HasQueryFilter(l => l.DeletedAt == null);
```

### 8.2 硬删除(GDPR DSR)

```sql
-- DSR 申请批准后
DELETE FROM license 
WHERE customer_id = 'xxx' AND created_at < '2023-01-01';
```

- 操作前需审批
- 操作后写 `audit_log`(特例)
- 不可逆

### 8.3 匿名化(账户销户)

```sql
-- 将 PII 替换为加密随机值
UPDATE "user"
SET 
  email_cipher = encrypt_random_bytes(),
  email_hash = 'ANONYMIZED',
  full_name = 'ANONYMIZED',
  phone_cipher = NULL
WHERE id = 'xxx';
```

---

## 9. 分区策略

### 9.1 分区表清单

| 表 | 分区键 | 分区粒度 | 保留 |
| --- | --- | --- | --- |
| `heartbeat` | `received_at` | 月 | 3 个月 |
| `consumption_log` | `occurred_at` | 月 | 12 个月 |
| `license_event` | `occurred_at` | 月 | 12 个月在线 + 3 年冷存 |
| `audit_log` | `occurred_at` | 月 | 12 个月在线 + 7 年冷存 |
| `webhook_delivery` | `occurred_at` | 月 | 90 天 |

### 9.2 分区维护

```sql
-- 创建未来分区(cronjob 每月执行)
CREATE TABLE heartbeat_2026_08 PARTITION OF heartbeat
  FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

-- 清理过期分区
DROP TABLE heartbeat_2024_01;
```

### 9.3 冷数据归档

```sql
-- 导出过期分区到 S3 兼容存储
pg_dump --table=heartbeat_2024_01 --file=heartbeat_2024_01.sql
aws s3 cp heartbeat_2024_01.sql s3://license-manager-cold-storage/heartbeat/2024/

-- 之后 DROP 分区
DROP TABLE heartbeat_2024_01;
```

### 9.4 提供程序行为差异（EF 翻译与语义）

> 经 `ProviderTranslationAuditTests`（Infrastructure.Tests）对 5 个 EF 提供程序 × 6 种实际查询模式做翻译审计（`ToQueryString` 级），结论：

| 差异点 | Sqlite | PostgreSQL | SQL Server | MySQL (Pomelo) | Oracle |
| --- | --- | --- | --- | --- | --- |
| DateTimeOffset 比较/排序下推 | ✅（UTC DateTime 转换器） | ✅ | ✅ | ✅ | ✅ |
| `string.Contains`（LIKE）大小写 | ASCII 不敏感 | 敏感 | 随排序规则（通常不敏感） | 随排序规则（通常不敏感） | 不敏感 |
| DateTimeOffset 偏移量存储 | 文本（UTC 归一化，偏移丢弃） | timestamptz（归一化 UTC，偏移丢弃） | datetimeoffset（保留，比较按 UTC 瞬间） | datetime(6)（归一化 UTC，偏移丢弃） | TIMESTAMP WITH TIME ZONE（保留） |

- **Sqlite DateTimeOffset 翻译已根治（UTC DateTime 转换器）**：`Microsoft.Data.Sqlite` 原生不支持 DateTimeOffset 的比较（`InvalidOperationException "could not be translated"`）与排序（`NotSupportedException`）。现由 `SqliteDateTimeOffsetConversionConvention` 在 **仅 `Database:Provider=Sqlite`** 时为模型中全部 `DateTimeOffset`/`DateTimeOffset?` 属性注册 `DateTimeOffset → DateTime` 值转换器（写入取 `UtcDateTime`，读取按零偏移重建），比较/排序随之按 DateTime 正常翻译为 SQL；其他四方言模型映射不变。存储仍为 TEXT，但格式归一化为 UTC（无偏移后缀），语义与 PostgreSQL timestamptz / MySQL datetime(6) 对齐。`EfRepository` 的翻译失败回退保留为安全网，但现有查询模式不再触发。
- **存量 SQLite 库注意事项（迁移 `SqliteDateTimeOffsetAsUtc` 为空操作）**：值转换器不改变列定义（两种映射均 TEXT），迁移不含 DDL、不改写存量数据。无转换器时代的数据按 `"yyyy-MM-dd HH:mm:ss.fffffffzzz"`（含偏移后缀）存储；新转换器读取该旧格式时 `Microsoft.Data.Sqlite` 按 `DateTime.Parse` 隐式解析，行为受机器时区影响（实测）：**非 UTC 机器**上解析结果为 `Kind=Local`，与转换器的零偏移冲突，物化时抛 `ArgumentException`；**UTC 机器**上偏移被解析并归一化，恰好读回正确瞬间（依赖隐式解析，属巧合可用）。因此存量 SQLite 库升级前必须执行一次性数据清洗（将偏移归一化为 UTC TEXT）或重建数据库，否则旧行在非 UTC 机器上将无法读取。
- **搜索大小写差异**：同一 `search` 关键词在不同后端的命中集可能不同（如 PG 区分大小写而 MySQL 通常不敏感），管理端搜索行为随部署数据库而定。
- **比较语义一致**：跨后端 DateTimeOffset 比较均按 UTC 瞬间进行，业务逻辑无差异；仅存储精度不同。
- 翻译能力由 `ProviderTranslationAuditTests`（30 个「提供程序 × 模式」组合，无已知缺口）与 `SqliteDateTimeOffsetConversionTests`（真实执行 + 存量格式兼容探针）持续回归。

---

## 10. 数据迁移

### 10.1 工具

- **EF Core Migrations**:`dotnet ef migrations add Xxx`
- **Flyway / Liquibase**(可选)
- **自定义 SQL 脚本**:`/migrations/V001__init.sql`

### 10.2 命名规范

- `V{版本}_{日期}_{描述}.sql`
- 例:`V20260723_01__create_user_table.sql`

### 10.3 迁移原则

- **向前兼容**:旧版本代码可读新表
- **加列允许**:NULL 列可热加
- **删列慎用**:先标记废弃,1 个版本后删除
- **大表变更**:低峰期;考虑 `pg_repack` 重建
- **回滚准备**:每迁移都有反向 SQL

### 10.4 种子数据

```sql
-- 平台内置角色
INSERT INTO role (code, name, is_builtin, permissions) VALUES
  ('super_admin', 'Super Admin', TRUE, '["*"]'),
  ('product_manager', 'Product Manager', TRUE, '["product:*"]'),
  -- ...
ON CONFLICT (code) DO NOTHING;
```

---

## 11. 性能与监控

### 11.1 性能基线

- 关键查询 P95 < 50ms
- 激活 P95 < 100ms(含签名)
- 验证 P95 < 50ms
- Webhook 投递 P95 < 200ms

### 11.2 慢查询监控

```sql
-- 启用 pg_stat_statements
CREATE EXTENSION pg_stat_statements;

-- 阈值告警(> 200ms)
SELECT mean_exec_time, calls, query
FROM pg_stat_statements
WHERE mean_exec_time > 200
ORDER BY mean_exec_time DESC
LIMIT 20;
```

### 11.3 关键监控指标

| 指标 | 来源 | 告警 |
| --- | --- | --- |
| 连接数 | `pg_stat_activity` | > 80% 上限 |
| 慢查询 | `pg_stat_statements` | > 200ms |
| 锁等待 | `pg_locks` | > 5s |
| 复制延迟 | `pg_stat_replication` | > 30s |
| 死元组 | `pg_stat_user_tables` | > 10% |
| 表膨胀 | `pgstattuple` | > 30% |
| 缓存命中率 | `pg_stat_database` | < 99% |
| 表大小 | `pg_total_relation_size` | 超预期 |
| 索引使用 | `pg_stat_user_indexes` | < 50 次/日 |

### 11.4 索引维护

```sql
-- 重建膨胀索引
REINDEX INDEX CONCURRENTLY idx_license_status;

-- 更新统计
ANALYZE license;
```

---

## 12. 备份与恢复

### 12.1 备份策略

| 类型 | 频率 | 保留 | 工具 |
| --- | --- | --- | --- |
| 全量 | 每日 1 次 | 7 天本地 + 30 天 S3 | `pg_basebackup` |
| 增量(WAL) | 持续归档 | 7 天 | `archive_command` |
| 逻辑备份 | 每周 1 次 | 12 周 | `pg_dump` |
| 密钥备份 | 每次轮换 | 永久 | KMS/HSM |
| 审计冷存 | 月度导出 | 7 年 | S3 冷存储 |

### 12.2 恢复演练

- 季度 1 次
- RTO / RPO 测量
- 文档化:演练记录、时间、问题、改进

### 12.3 PITR(Point-in-time Recovery)

```bash
# 恢复到指定时间
pg_basebackup -D /var/lib/postgresql/backup
# 配置 recovery.conf
restore_command = 'cp /var/lib/postgresql/wal/%f %p'
recovery_target_time = '2026-07-23 12:00:00'
```

---

## 13. 安全与合规

### 13.1 字段级加密

```sql
-- PII 字段加密(AES-GCM)
email_cipher BYTEA NOT NULL,    -- 加密值
email_hash TEXT NOT NULL,       -- HMAC 索引(脱敏查询)
```

### 13.2 行级安全

- 启用 RLS
- 会话变量注入 `tenant_id`
- 强制策略(不可绕过)

### 13.3 审计

- 所有写操作写 `audit_log`
- 不可变(WORM 存储,REVOKE UPDATE/DELETE)
- 7 年保留

> 注:原 13.4 数据驻留章节已随商业化裁剪删除(多区域数据驻留功能下线)。

---

## 14. 附录

### 14.1 关联文档

- [RDP v1.0.1](./LicenseManager-Platform-RDP-v1.0.1.md) - 产品需求
- [TAD v1.0](./LicenseManager-Platform-TAD-v1.0.md) - 技术架构
- [LicenseManager.Core Dev Guide](./LicenseManager.Core-Development-Guide-v1.0.md) - 核心库
- [API Spec](./LicenseManager-Platform-API-Spec-v1.0.md) - API 规范
- [Deployment Guide](./LicenseManager-Platform-Deployment-Guide-v1.0.md) - 部署

### 14.2 表清单(共 ~37 张)

| 分类 | 表 |
| --- | --- |
| 身份 | `user` / `role` / `user_role` / `api_key` / `personal_access_token` |
| 组织 | `organization` / `customer` / `reseller` |
| 产品 | `product` / `feature` / `entitlement_set` / `entitlement_tier` / `license_template` |
| 授权 | `license` / `activation` / `device` / `heartbeat` / `consumption_log` / `license_event` / `refresh_token` |
| 订阅 | `subscription` / `subscription_log` |
| 浮动 | `floating_server` / `lease` |
| 审计 | `audit_log` / `webhook_endpoint` / `webhook_delivery` |
| 通知 | `notification` |
| 安全 | `system_key` / `idp_config` |
| 安全台账 | `login_attempt` / `ip_whitelist_entry` / `nonce_record` / `idempotency_record`(已在各方言 `InitialCreate` 迁移中建表) |
| 其它 | `ticket` / `release` / `release_file` |

### 14.3 ER 总览

参见 RDP v1.0.1 §6 数据模型。