# Data Retention Policy ## Purpose & Audience This document defines the retention periods, deletion mechanisms, and legal bases for all data categories stored by the Coda platform. It is intended for compliance officers, auditors, and engineers implementing retention automation. For the full data processing inventory, see [data-processing.md](data-processing.md). For erasure procedures, see [security.md](security.md) (crypto-shredding section). **Last reviewed:** 2026-04-10 **Phase:** 1 (Core Authorization) --- ## Retention Schedule | Data Category | Table(s) | Retention Period | Deletion Mechanism | Legal Basis for Retention | Legal Basis for Deletion | | ----------------------------- | -------------------------------------------------------------------------------------------- | --------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------- | -------------------------------------------------------------------------------- | | **User identity** | `users` | Until user requests deletion or tenant deactivation | Hard delete (cascade) | Contractual necessity (GDPR Art. 6(1)(b)) | GDPR Art. 17 (right to erasure), CCPA §1798.105 | | **Tenant membership** | `tenant_users` | Until membership deactivated or user/tenant deleted | `onDelete: Cascade` from `User` and `Tenant` | Contractual necessity | Cascade from parent deletion | | **OAuth tokens** | `oauth_connections` | Until user disconnects integration or account deleted | Explicit disconnect via API. **Note:** `onDelete: Cascade` NOT set on User relation — requires manual deletion or schema fix. | Consent (Art. 6(1)(a)) — user authorized the connection | User revocation or manual deletion | | **Conversations** | `chats`, `messages` | Indefinite (soft delete available) | Soft delete (`deleted_at` timestamp). **Note:** `onDelete: Cascade` NOT set on User relation — requires manual deletion or schema fix. | Contractual necessity | User request or tenant deactivation | | **Data sources** | `data_sources` | Until user deletes or account deleted | **Note:** `onDelete: Cascade` NOT set on User relation. S3 cached results (`cachedResultKey`) must be deleted separately. | Contractual necessity | User request or manual deletion | | **Conversation cache** | Redis | TTL-based (configurable, refreshed on access) | Automatic TTL expiry | Legitimate interest (performance) | Automatic | | **Audit logs** | `audit_logs` | **7 years** (SOX/SOC 2) | Never deleted. PII erased via crypto-shredding. Structural data retained. | Legal obligation (Art. 6(1)(c)) — SOX/SOC 2 | PII: crypto-shredding (Art. 17). Structural data: retained per legal obligation. | | **Permission change logs** | `permission_change_logs` | **7 years** (SOX) | Never deleted. `onDelete: Restrict`. | Legal obligation | Retained per legal obligation | | **Sessions** | `sessions` | Until expiry (`expiresAt`) + 90 days | Periodic cleanup job deletes expired + revoked sessions older than 90 days | Contractual necessity + legitimate interest | Expiry + cleanup | | **Step-up challenges** | `step_up_challenges` | Until expiry (`expiresAt`) + 30 days | Periodic cleanup job | Contractual necessity | Expiry + cleanup | | **Roles and permissions** | `roles`, `permissions`, `modules`, `role_permissions`, `role_inheritance`, `role_exclusions` | Lifetime of tenant | `onDelete: Cascade` from `Tenant` | Contractual necessity | Tenant deletion | | **User roles** | `user_roles` | Until role revoked or user/tenant deleted | `onDelete: Cascade` from `User` and `Role` | Contractual necessity | Revocation or cascade | | **User permission overrides** | `user_permissions` | Until override revoked, expires, or user/tenant deleted | `expiresAt` for automatic expiry. Manual revocation. Cascade from `User`/`Tenant`. | Contractual necessity | Expiry, revocation, or cascade | | **Super admin grants** | `super_admins` | Indefinite (historical grants retained with `revokedAt`) | Revocation sets `revokedAt`. Record retained for audit trail. | Legitimate interest (accountability) | Cascade from `User` | | **Audit encryption keys** | `audit_encryption_keys` | Until GDPR erasure request | Explicit deletion for crypto-shredding. Not cascade-linked. | Legal obligation (enables audit retention) | GDPR Art. 17 erasure request | | **Permission cache** | Redis (`perm:*`) | TTL: 30s (effective), 15m (roles), 1h (plans), 5m (super admin) | Automatic TTL expiry. Tenant suspension triggers prefix purge. | Legitimate interest (performance) | Automatic | | **Rate limit counters** | Redis (`ratelimit:*`) | 1-minute sliding window | Automatic TTL expiry | Legitimate interest (abuse prevention) | Automatic | --- ## Retention Rationale ### 7-Year Retention for Audit Logs The 7-year period for audit logs and permission change logs is driven by: 1. **SOX (Sarbanes-Oxley):** Requires retention of records relevant to financial audits for 7 years (Section 802). 2. **SOC 2 Type II:** Requires evidence of access controls and monitoring for the audit period (typically 12 months, but logs must be available for prior periods). 3. **Legal hold:** Any active litigation or regulatory investigation extends retention indefinitely. The platform achieves this without violating GDPR by using **crypto-shredding**: PII fields in audit logs are encrypted per-user. When a user exercises their right to erasure, the encryption key is deleted, rendering PII unreadable while preserving the structural audit data (action, resource, outcome, timestamp) that compliance requires. ### Indefinite Retention for Super Admin Grants Super admin grants are retained indefinitely (even after revocation) because: 1. They represent the highest privilege level in the system 2. Audit trail of who had cross-tenant access and when is critical for incident investigation 3. Historical grants with `revokedAt` timestamps enable temporal analysis ### TTL-Based Retention for Redis Redis data is transient and exists only for performance or operational purposes. TTLs are chosen to balance freshness against cache hit rates: - **30s for permissions:** Permission changes should take effect within 30 seconds - **5m for super admin status:** Less frequently changed, higher cache benefit - **15m for role permissions:** Role changes are infrequent - **1h for plan features:** Plan changes happen rarely (billing cycle) --- ## Deletion Procedures ### User Deletion (GDPR Art. 17 / CCPA §1798.105) When a user requests account deletion: 1. **Crypto-shred audit PII:** - Delete the user's `AuditEncryptionKey` row - All audit log entries for this user now show `[redacted]` for PII fields - Verify via: `SELECT * FROM audit_logs WHERE user_id = '{user_id}' LIMIT 5` — PII fields should be unreadable 2. **Delete user-owned data (manual — missing cascades):** - `OAuthConnection`, `Chat`, `DataSource` do NOT have `onDelete: Cascade` from `User`. Delete these explicitly before deleting the User record: ```sql DELETE FROM oauth_connections WHERE user_id = '{user_id}'; DELETE FROM data_sources WHERE user_id = '{user_id}'; -- For chats: soft-delete or hard-delete depending on policy UPDATE chats SET deleted_at = NOW() WHERE user_id = '{user_id}'; ``` - Delete S3 objects referenced by `DataSource.cachedResultKey` and `MessageAttachment.storageKey` 3. **Cascade delete user record:** - `User` deletion cascades through: `TenantUser`, `UserRole`, `UserPermission` (these have `onDelete: Cascade`) - `SuperAdmin` grants cascade (historical records are lost — acceptable per GDPR) - `AuditLog` entries are NOT cascade-deleted (`onDelete: Restrict` on `Tenant`, soft FK on `userId`) 4. **Flush caches:** - `redis-cli DEL "perm:{tenantId}:{userId}:*"` - `redis-cli DEL "access:superadmin:{userId}"` - Conversation cache entries keyed by HMAC hash will expire via TTL 5. **Verify:** - `SELECT COUNT(*) FROM users WHERE id = '{user_id}'` → 0 - `SELECT COUNT(*) FROM tenant_users WHERE user_id = '{user_id}'` → 0 - `SELECT COUNT(*) FROM audit_encryption_keys WHERE user_id = '{user_id}'` → 0 ### Tenant Deletion When a tenant is deactivated and deletion is requested: 1. **Deactivate the tenant** (sets `status = 'deactivated'`). Users can no longer authenticate. 2. **Audit log handling:** - `onDelete: Restrict` on `AuditLog.tenant` prevents accidental cascade - Crypto-shred all users in the tenant first (delete their `AuditEncryptionKey` rows) - Then, if required, manually delete audit log entries after the retention period 3. **Cascade delete:** - `Tenant` deletion cascades: `TenantUser`, `Module`, `Permission`, `Role` (and all join tables) - Requires explicit handling of `AuditLog` and `PermissionChangeLog` (Restrict constraint) 4. **Flush all tenant caches:** ```bash redis-cli KEYS "perm:{tenantId}:*" | xargs redis-cli DEL redis-cli KEYS "access:{tenantId}:*" | xargs redis-cli DEL ``` ### Session Cleanup (Automated) Periodic job (recommended: daily) to clean up expired sessions: ```sql -- Delete sessions that are both expired AND revoked/inactive for 90+ days DELETE FROM sessions WHERE expires_at < DATE_SUB(NOW(), INTERVAL 90 DAY) AND (revoked_at IS NOT NULL OR last_active_at < DATE_SUB(NOW(), INTERVAL 90 DAY)) LIMIT 1000; -- Delete expired step-up challenges older than 30 days DELETE FROM step_up_challenges WHERE expires_at < DATE_SUB(NOW(), INTERVAL 30 DAY) LIMIT 1000; ``` ### Expired Permission Override Cleanup (Automated) Periodic job (recommended: daily) to clean up expired overrides: ```sql -- Delete expired user permission overrides DELETE FROM user_permissions WHERE expires_at IS NOT NULL AND expires_at < DATE_SUB(NOW(), INTERVAL 7 DAY) LIMIT 1000; ``` The 7-day grace period after expiry ensures that any audit logs referencing the override still have a valid FK target during investigation. --- ## Legal Hold When a legal hold is issued: 1. **Suspend all automated deletion** for the affected scope (user, tenant, or date range) 2. **Mark affected records** — add a note to the incident tracking system 3. **Do not crypto-shred** any affected users' audit encryption keys 4. **Extend retention** indefinitely until the hold is lifted 5. **Document** the hold scope, start date, and requesting party Legal holds override all retention periods and deletion requests, including GDPR Art. 17 erasure requests (per Art. 17(3)(e) — defense of legal claims). --- ## Implementation Status | Mechanism | Status | Notes | | ---------------------------------- | ------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | Crypto-shredding | Implemented | `AuditEncryptionKey` deletion renders audit PII unreadable | | User cascade deletion | **Partial** | `onDelete: Cascade` set on `TenantUser`, `UserRole`, `UserPermission`. **Not set** on `OAuthConnection`, `Chat`, `DataSource` — these must be manually deleted before `User` delete or the schema must be updated. | | S3 object deletion | **Not implemented** | `DataSource.cachedResultKey` and `MessageAttachment.storageKey` S3 objects must be deleted manually; no cascade linkage. | | Tenant cascade deletion | Partial | `onDelete: Restrict` on audit tables requires manual handling | | Session cleanup job | **Not implemented** | Needs periodic cron job or scheduled Lambda | | Override expiry cleanup | **Not implemented** | Needs periodic cron job | | Conversation purge | **Not implemented** | Soft delete exists; no automated hard delete after retention period | | Legal hold mechanism | **Not implemented** | Manual process only; no DB-level hold flag | | 7-year audit retention enforcement | **Not implemented** | No automated deletion after 7 years (currently retained indefinitely) | --- ## Maintaining This Document - **Review cadence:** Annually, or when new data categories are added. - **Trigger for update:** New table with retention requirements, legal or regulatory change, new compliance certification. - **Owner:** Platform team lead + Legal.