# LeadPilot — Data Retention & Soft Deletion Policy

**Document Version:** 1.0.0 (Phase 5 Database Architecture Lock)  
**Status:** Approved & Formally Recorded  

---

## 1. Soft Deletion Evaluation & Policy

LeadPilot strictly rejects the anti-pattern of adding `deleted_at` to every table. Soft deletion is applied only where recovery of accidental user deletion is commercially valuable:

```
+---------------------------------------------------------------------------------------------------------------+
| ENTITY / TABLE             | DELETION STRATEGY     | RATIONALE                                                |
+----------------------------+-----------------------+----------------------------------------------------------+
| `leads`                    | Hard Delete / Status  | Inactive leads transition to `status = 'archived'` rather|
|                            | Transition            | than soft-delete; hard deletion is an explicit action.   |
| `users`                    | Hard Delete (Cascade) | GDPR compliance requires total data purge upon deletion. |
| `workspaces`               | Hard Delete (Cascade) | Clean tenant removal purging all child rows.             |
| `lead_notes`               | Hard Delete           | Internal notes can be deleted directly by author/admin.  |
| `lead_activities`          | Immutable (No Delete) | Audit timeline cannot be modified or deleted by users.   |
| `message_templates`        | Hard Delete           | Templates deleted directly; FK on steps is `RESTRICT`.   |
| `follow_ups`               | Hard Delete / Cancel  | Cancelled follow-ups transition to `status='cancelled'`. |
+---------------------------------------------------------------------------------------------------------------+
```

---

## 2. Retention Schedules (Periodic Cleanup Crons)

```
+---------------------------------------------------------------------------------------------------------------+
| TABLE NAME                 | RETENTION PERIOD      | PURGE MECHANISM                                          |
+----------------------------+-----------------------+----------------------------------------------------------+
| `webhook_logs`             | 30 Days               | Daily background cron: `DELETE WHERE created_at < 30d`   |
| `ai_usage_logs`            | 90 Days               | Daily background cron: `DELETE WHERE created_at < 90d`   |
| `jobs` (completed)         | Deleted immediately   | Native Laravel database queue consumer lifecycle         |
| `failed_jobs`              | 14 Days               | Weekly background cron: `php artisan queue:prune-failed` |
| `notifications` (read)     | 60 Days               | Monthly background cron: `DELETE WHERE read_at < 60d`    |
+---------------------------------------------------------------------------------------------------------------+
```