GDPR Article 25 requires data protection by design and by default. Database schema design is a data protection design decision. Whether you use soft or hard deletes, how you structure audit tables, and whether you store personal data in JSON columns all have GDPR implications.
Key Analysis
Soft deletes (deleted_at timestamp) preserve data for audit and litigation purposes but conflict with GDPR erasure obligations. The correct design is: soft delete for operational purposes, plus a separate documented erasure workflow for GDPR requests.
Audit tables that record old and new values for sensitive fields are both a data protection tool (they prove what changed) and a data protection risk (they retain old values indefinitely). Implement data categorisation before designing audit tables.
JSON columns containing personal data are difficult to index, redact, or selectively erase. Personal data in JSON columns should be avoided or tightly scoped.
Risk Signals
Soft delete as the only deletion mechanism — with no GDPR erasure workflow that actually removes personal data from the database.
Audit tables that retain indefinitely without a documented retention schedule.
Personal data stored in JSON columns without a documented erasure path.
Action Items
Define two deletion modes in the data model: soft delete (sets deleted_at) for operational recovery, and GDPR erasure (overwrites personal fields with null or pseudonym) for data subject rights.
Document audit table retention: suggested maximum of 7 years for legal contexts, 1 year for operational contexts.
Avoid storing personal data in JSON columns; if unavoidable, document the erasure procedure for each JSON field.