db-temporal-history (M8)

Data has a lifecycle: it is created, changed, retired, and — legally — sometimes must be erased. Modeling that lifecycle explicitly avoids resurrected "deleted" rows, missing audit trails when something goes wrong, and GDPR/CCPA exposure from data that should have been purged. This module is design-axis (Temporal category). It applies across paradigms.

What it checks

• Soft-delete consistency: a deleted_at/is_deleted column present on some tables but not others, or present without the matching guards — queries/uniques/FKs that ignore it (resurrecting deleted rows in joins, UNIQUE collisions). Ties to the M5 over-nullable UNIQUE trap.
• Audit / history trail: sensitive tables (financial, auth, permissions, config) with no change history (no *_history/*_audit table, no temporal columns, no trigger/CDC) — when something is wrong, there is no record of who changed what when.
• Retention & erasure (GDPR/CCPA): PII held with no retention window, no documented purge/anonymization path, or "soft delete" used where the law requires actual erasure or anonymization. Cross-reference PII detection in M10.
• Temporal validity: bitemporal/effective-dated data using a single timestamp where valid_from/valid_to is needed; missing end_date >= start_date CHECK.

Axis & severity

• Axis: design; magnitude banded, never a fabricated record count or fine amount.
• PII with no retention/erasure path (compliance exposure): severity 4, warn, confidence directional (legal applicability needs human review — never auto-caps).
• Inconsistent soft-delete with no query/unique guards: severity 3, warn.
• No audit trail on financial/auth tables: severity 3, warn.
• M8 holds no sev-5 cap; it shapes the Temporal category value.

Tier-0 static check

Parse DDL/snapshot via scripts/parse-schema.mjs: detect soft-delete columns and check whether they appear consistently and in UNIQUE/partial-index definitions; look for companion *_history/*_audit tables or temporal columns on sensitive tables; flag PII-bearing tables (email, name, phone, address, dob) with no retention/erasure marker. Static → directional.