ADR-002: Soft Deletes and Audit Strategy
Status: Accepted Owner: @bilal @deen Date: 2025-12-06
Context
Envo handles property maintenance records with long-term value. GDPR requires ability to delete personal data on request. We need audit trails for compliance and debugging.
Requirements
- Never accidentally lose data
- Maintain referential integrity
- Support GDPR deletion requests
- Track who changed what and when
- Keep audit overhead reasonable
Decision
Soft deletes with targeted event logging.
Soft Delete Implementation
All primary entities have deleted_at TIMESTAMPTZ DEFAULT NULL.
Tables with soft delete: organisations, properties, tenants, issues, vendors, user_organisation_access
Tables WITHOUT soft delete: users (Supabase Auth), events (audit, never deleted), attachments (hard-deleted for GDPR)
All queries must filter: WHERE deleted_at IS NULL. Views created for convenience.
Audit Events
| Event Type | Trigger | Data Captured |
|---|---|---|
issue_created | New issue | issue_id, channel, category |
issue_status_changed | Status update | old_status, new_status |
vendor_assigned | Vendor linked | issue_id, vendor_id |
vendor_accepted | Vendor accepts | issue_id, vendor_id |
issue_completed | Resolution | resolution_notes |
notification_sent | SMS/email | channel, recipient |
user_login | Auth event | user_id |
user_access_granted/revoked | Access change | user_id, org_id, by |
csv_import | Bulk import | record_count, user_id |
GDPR Deletion Process
- Soft delete tenant record
- Retain issue records (property history, not personal data)
- Nullify personal data on issues if required (
[REDACTED]) - Hard delete attachments containing personal images
- Log deletion event
Timestamps
All tables include created_at and updated_at (auto-updated via trigger). All timestamps stored as UTC (TIMESTAMPTZ), converted to user timezone in application layer.
Consequences
Positive
- Data never accidentally lost
- Clean audit trail for compliance
- GDPR-compliant deletion path
Negative
- All queries must remember
WHERE deleted_at IS NULL - GDPR deletion is multi-step