db-temporal-history
Audit temporal and lifecycle modeling — soft-delete consistency and its uniqueness/query traps, presence and integrity of an audit/history trail, and retention / GDPR right-to-erasure handling (hard delete vs anonymization, retention windows). Module M8. Feeds the Design & Integrity score (Temporal category).
How do I install this agent skill?
npx skills add https://github.com/hainrixz/claude-db --skill db-temporal-historyIs this agent skill safe to install?
- Gen Agent Trust Hubpass
The skill facilitates database auditing for temporal data integrity and GDPR compliance. It includes logic for identifying soft-delete inconsistencies and missing audit trails. A minor security surface was identified regarding the processing of untrusted database content, but no active malicious patterns were found.
- Socketpass
No alerts
- Snykpass
Risk: LOW · No issues
What does this agent skill do?
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_deletedcolumn 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/*_audittable, 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_tois needed; missingend_date >= start_dateCHECK.
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, confidencedirectional(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.
Tier-1 verification query
Find tables with soft-delete but no audit companion, and confirm soft-deleted rows still satisfy a "unique" key:
-- $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
Findings
Emit per schema/finding.schema.json. Examples:
M8.users.pii_no_retention_or_erasure— PII columns with no retention window or erasure/anonymization path (severity 4,warn, axisdesign, confidencedirectional).M8.orders.soft_delete_inconsistent—deleted_atpresent but joins/uniques ignore it, resurrecting deleted rows (severity 3,warn, axisdesign).M8.permissions.no_audit_trail— auth-critical table has no change history (severity 3,warn). Each finding:evidence.observedquotes the relevant DDL/column verbatim (PII values never echoed);verification.reproduceis a runnable query above (method: ddl_parse/schema_introspect/query_stat);expected_impactbanded + confidence-tagged.
Honesty
- GDPR/CCPA applicability is a legal determination, not a schema fact — flag the exposure (no erasure path) as
directionaland defer the legal call to a human; never assert a fine or a violation as established. - Soft delete is a valid pattern; flag only inconsistency (some tables/queries honour it, others don't), not its use.
- Never invent retention periods or record counts; band the impact and cite the missing mechanism, not a number.
How can the creator link this skill?
Add the canonical catalog link to the repository README so users can inspect current installs and available audits. The publishing guide covers the complete discovery path.
<a href="https://skillzs.dev/skills/hainrixz/claude-db/db-temporal-history">View db-temporal-history on skillZs</a>