break
70e680eb0a
All checks were successful
构建并部署到测试环境（无 SSH） / build-and-deploy (push) Successful in 9m2s
- 新增成对迁移 000223（tb_phone_asset_association，含有效关系部分唯一索引与 down 守卫）与 000224（解绑导入任务表），不回填历史 - H5：need_bind_phone 三支判定（开关关闭完全短路）；已有主号幂等建联；十项上限按手机号 advisory 串行化（含换绑到全新号的并发场景）；换绑原子迁移与冲突整单回滚；不写遗留列 - 后台：关联列表、单项/批量解绑、CSV 导入解绑（B1–B16），超管/平台 gate + 资产数据范围复核，三态统一文案 - 读侧：卡/设备列表与详情按页一次 IN 聚合；两类导出补「关联手机号」列并保留历史表头反解兼容 - 脱敏：关联审计走独立动作/资源只写脱敏手机号；访问日志手机号类字段脱敏 - 同步主 Spec openspec/specs/phone-asset-association 并归档 AUG26-009，补齐 requirement-evidence 与入口矩阵，context-health 通过
67 lines
5.0 KiB
SQL
67 lines
5.0 KiB
SQL
-- 手机号—资产当前有效关联。
|
||
-- 既有事实只有「客户↔手机号」与「客户↔资产」两条互不相交的链路，无法表达单项资产的验证关系，
|
||
-- 后台查看、按资产解除、换绑迁移与批量解绑都缺少可依据的关联事实。
|
||
-- 关联只能由 H5 短信验证建立（source 固定 h5_sms_verification），后台不提供创建或补录入口。
|
||
-- iot_card 与 device 的 ID 空间独立，资产身份一律取 (asset_type, asset_id)，同号互不覆盖也不合并计数。
|
||
-- 失效走 status 置 0 并同时写入失效时间、方式与操作人，关系行本身保留为业务事实，不做物理或软删除。
|
||
-- 单手机号最多 10 项有效关系，上限只统计 status = 1 的行；本迁移不回填任何历史关系。
|
||
-- 不使用数据库外键，关联以 ID 保存并由应用层显式校验。
|
||
|
||
CREATE TABLE tb_phone_asset_association (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
phone VARCHAR(20) NOT NULL,
|
||
asset_type VARCHAR(20) NOT NULL,
|
||
asset_id BIGINT NOT NULL,
|
||
status SMALLINT NOT NULL DEFAULT 1,
|
||
source VARCHAR(30) NOT NULL DEFAULT 'h5_sms_verification',
|
||
established_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
invalidated_at TIMESTAMPTZ,
|
||
invalidation_method VARCHAR(30) NOT NULL DEFAULT '',
|
||
invalidation_reason VARCHAR(500) NOT NULL DEFAULT '',
|
||
invalidator BIGINT NOT NULL DEFAULT 0,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
deleted_at TIMESTAMPTZ,
|
||
CONSTRAINT chk_phone_asset_association_phone CHECK (phone <> ''),
|
||
CONSTRAINT chk_phone_asset_association_asset_type CHECK (asset_type IN ('iot_card', 'device')),
|
||
CONSTRAINT chk_phone_asset_association_asset CHECK (asset_id > 0),
|
||
CONSTRAINT chk_phone_asset_association_status CHECK (status IN (0, 1)),
|
||
CONSTRAINT chk_phone_asset_association_source CHECK (source = 'h5_sms_verification'),
|
||
-- 有效关系不得带失效信息，已失效关系必须带失效时间：两者互为充要条件，避免出现半失效状态。
|
||
CONSTRAINT chk_phone_asset_association_invalidation CHECK (
|
||
(status = 1 AND invalidated_at IS NULL)
|
||
OR (status = 0 AND invalidated_at IS NOT NULL)
|
||
)
|
||
);
|
||
|
||
-- 同一手机号与同一 (资产类型, 资产ID) 同时只能有一条有效关系。
|
||
-- 部分唯一索引既是幂等建联的结构性保证，也是并发同资产建联的兜底：冲突由应用映射为幂等成功。
|
||
-- 迁移与「新号已存在的同资产有效关系」冲突时由本索引报错，换绑整次回滚。
|
||
CREATE UNIQUE INDEX uq_phone_asset_association_valid
|
||
ON tb_phone_asset_association (phone, asset_type, asset_id)
|
||
WHERE status = 1 AND deleted_at IS NULL;
|
||
-- 十项上限按手机号计数，计数只读有效关系。
|
||
CREATE INDEX idx_phone_asset_association_phone
|
||
ON tb_phone_asset_association (phone, status)
|
||
WHERE deleted_at IS NULL;
|
||
-- 列表、详情与两类导出按本批资产集合一次 IN 批量聚合，禁止逐资产查询。
|
||
CREATE INDEX idx_phone_asset_association_asset
|
||
ON tb_phone_asset_association (asset_type, asset_id, status)
|
||
WHERE deleted_at IS NULL;
|
||
|
||
COMMENT ON TABLE tb_phone_asset_association IS '手机号—资产当前有效关联，仅由 H5 短信验证建立，单手机号最多 10 项有效关系';
|
||
COMMENT ON COLUMN tb_phone_asset_association.id IS '主键';
|
||
COMMENT ON COLUMN tb_phone_asset_association.phone IS '已验证手机号，计数与唯一口径均以本列为准';
|
||
COMMENT ON COLUMN tb_phone_asset_association.asset_type IS '资产类型 iot_card-物联网卡 device-设备，与 pkg/constants.AssetTypeIotCard/AssetTypeDevice 一致';
|
||
COMMENT ON COLUMN tb_phone_asset_association.asset_id IS '资产ID，与 asset_type 共同构成资产身份，两类资产ID空间独立';
|
||
COMMENT ON COLUMN tb_phone_asset_association.status IS '状态 0-已失效 1-有效，10 项上限只统计有效关系';
|
||
COMMENT ON COLUMN tb_phone_asset_association.source IS '建立来源，固定为 h5_sms_verification；后台无创建或补录入口';
|
||
COMMENT ON COLUMN tb_phone_asset_association.established_at IS '关联建立时间，即短信验证通过时间';
|
||
COMMENT ON COLUMN tb_phone_asset_association.invalidated_at IS '失效时间，仅 status = 0 时非空';
|
||
COMMENT ON COLUMN tb_phone_asset_association.invalidation_method IS '失效方式 backend_single-后台单项 backend_batch-后台批量 csv_import-CSV导入';
|
||
COMMENT ON COLUMN tb_phone_asset_association.invalidation_reason IS '失效原因，后台解除必填，最长500字符';
|
||
COMMENT ON COLUMN tb_phone_asset_association.invalidator IS '失效操作人账号ID，系统任务为 0';
|
||
COMMENT ON COLUMN tb_phone_asset_association.created_at IS '创建时间';
|
||
COMMENT ON COLUMN tb_phone_asset_association.updated_at IS '最近更新时间';
|
||
COMMENT ON COLUMN tb_phone_asset_association.deleted_at IS '软删除时间，本表不使用：失效走 status 置 0 并保留关系行';
|