db-temporal-history

db-temporal-history（M8）

数据具有生命周期：创建、变更、停用，而且在法律要求下，有时必须删除。明确建模这一生命周期，可以避免已“删除”的行死而复生、出错时缺少审计轨迹，以及因本应清除的数据而面临 GDPR/CCPA 风险。本模块属于 design 轴（Temporal 类别），适用于各种范式。

检查内容

• 软删除一致性：部分表有 deleted_at/is_deleted 列，其他表却没有；或者虽然有这些列，却缺少相应的保护措施——查询、唯一约束或外键忽略了这些列（导致已删除的行在联接中死而复生、发生 UNIQUE 冲突）。这与 M5 的过度可空 UNIQUE 陷阱相关。
• 审计／历史轨迹：敏感表（财务、身份验证、权限、配置）没有变更历史（没有 *_history/*_audit 表、时态列或触发器/CDC）——出问题时，无法记录谁在何时更改了什么。
• 保留与删除（GDPR/CCPA）：PII 没有保留期限、没有记录清楚的清除／匿名化途径，或者在法律要求实际删除或匿名化时使用了“软删除”。参见 M10 中的 PII 检测。
• 时态有效性：双时态／按生效日期确定的数据只使用单个时间戳，而实际需要 valid_from/valid_to；缺少 end_date >= start_date CHECK。

轴与严重程度

• 轴：design；影响程度分档，绝不捏造记录数或罚款金额。
• PII 没有保留／删除途径（合规风险）：严重程度 4，warn，置信度 directional（法律适用性需要人工审核——绝不自动封顶）。
• 软删除不一致且没有查询／唯一约束保护：严重程度 3，warn。
• 财务／身份验证表没有审计轨迹：严重程度 3，warn。
• M8 不设严重程度 5 的上限；它会影响 Temporal 类别的值。

Tier-0 静态检查

通过 scripts/parse-schema.mjs 解析 DDL／快照：检测软删除列，并检查其是否一致地出现在 UNIQUE／部分索引定义中；检查敏感表是否有配套的 *_history/*_audit 表或时态列；标记包含 PII 的表（email、name、phone、address、dob），如果没有保留／删除标记。静态检查 → directional。

Tier-1 验证查询

查找带有软删除但没有审计配套表的表，并确认软删除的行仍满足某个“唯一”键：

-- $DATABASE_URL, read-only
SELECT table_name FROM information_schema.columns
WHERE column_name IN ('deleted_at','is_deleted')
  AND table_schema NOT IN ('pg_catalog','information_schema');
-- resurrection check: deleted rows colliding on a natural key
SELECT email, count(*) FROM users GROUP BY email HAVING count(*) > 1;  -- if soft-deleted dupes appear

检查结果

按 schema/finding.schema.json 输出。示例：

• M8.users.pii_no_retention_or_erasure — PII 列没有保留期限或删除／匿名化途径（严重程度 4，warn，轴 design，置信度 directional）。
• M8.orders.soft_delete_inconsistent — 存在 deleted_at，但联接／唯一约束忽略该列，导致已删除的行死而复生（严重程度 3，warn，轴 design）。
• M8.permissions.no_audit_trail — 身份验证关键表没有变更历史（严重程度 3，warn）。 每项检查结果：evidence.observed 原样引用相关 DDL／列（绝不回显 PII 值）；verification.reproduce 为上方可运行的查询（method: ddl_parse / schema_introspect / query_stat）；expected_impact 按影响程度分档并标注置信度。

严谨性

• GDPR/CCPA 是否适用属于法律判断，而非模式事实——将风险（没有删除途径）标记为 directional，并将法律判断留给人工；绝不将罚款或违规断言为既定事实。
• 软删除是有效模式；仅标记不一致之处（部分表／查询遵循软删除规则，其他则没有），而非标记其使用本身。
• 绝不捏造保留期限或记录数；对影响程度分档，并指出缺失的机制，而非编造数字。