Tenant deletion cascades completely

Incomplete tenant deletion leaves orphaned records that violate both contractual data-isolation guarantees and GDPR Art. 17 (Right to Erasure). CWE-404 (Improper Resource Shutdown or Release) covers the defect. Orphaned records create concrete risks: a future tenant who is assigned a recycled UUID can inadvertently access predecessor data; orphaned API keys remain valid credentials; and billing records for deleted tenants create accounting liabilities. The ISO 25010 functional suitability requirement is simple — delete means delete.

Use database cascade deletes as the primary mechanism and supplement with explicit cleanup for external systems. In schema.prisma, add onDelete: Cascade on every relation to Organization:

model Project {
  id             String       @id
  organizationId String
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
}

For external data, implement a cleanup routine in src/app/api/orgs/[id]/route.ts:

async function deleteOrganization(orgId: string) {
  await db.organization.delete({ where: { id: orgId } }) // cascades handle DB relations
  await deleteS3Prefix(`tenants/${orgId}/`)
  await algolia.deleteBy({ filters: `organizationId:${orgId}` })
  await redis.del(`tenant:${orgId}:*`)
}

Wrap the DB delete and cleanup in a transaction or saga pattern so a failed external cleanup triggers a retry, not a partial deletion state.

• ID: saas-multi-tenancy.tenant-management.tenant-deletion-cascades

• Severity: high

• What to look for: Examine the tenant deletion flow — what happens when an organization/team is deleted or deactivated. Check whether all tenant-owned records across all tables are deleted or anonymized: members/users, projects, documents, billing records, API keys, webhooks, invitations, audit logs, file storage objects, cache entries, and search index records. Look for database-level cascade delete rules (Prisma onDelete: Cascade, foreign key cascades in SQL migrations) and application-level cleanup code.

• Pass criteria: Enumerate all tenant-owned tables. Deleting a tenant triggers cascade cleanup of at least 100% of tenant-owned data across all tables. Either database-level cascade deletes handle relational data automatically, or explicit application-level deletion of each dependent resource type is implemented. External data (file storage, search index, cache) is also cleaned up. The deletion is either transactional or uses a soft-delete with scheduled hard-delete cleanup job.

• Fail criteria: Deleting a tenant leaves orphaned records in child tables. Data from deleted tenants remains in the database indefinitely and is accessible via direct record lookups. External storage, search indexes, or cache entries are not cleaned up when a tenant is deleted.

• Skip (N/A) when: No tenant deletion flow is detected. Signal: no "delete organization," "cancel account," or "deactivate workspace" endpoint or UI flow in the codebase.

• Detail on fail: Describe what's left behind. Example: "Organization deletion in src/app/api/orgs/[id]/route.ts only deletes the organization record. Members, projects, documents, and API keys tables have no cascade delete rules and no explicit cleanup code. Orphaned records remain after deletion."

• Remediation: Use database cascade deletes as the primary mechanism, supplemented by application-level cleanup for external data:

  // schema.prisma — cascade deletes on related records
  model Project {
    id             String   @id
    organizationId String
    organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
    documents      Document[]  // also cascades if Document has cascade to Project
  }
  

  For external data (S3 files, Algolia records, Redis cache), implement a cleanup job triggered by the deletion event:

  async function deleteOrganization(orgId: string) {
    // Delete DB records (cascades handle related tables)
    await db.organization.delete({ where: { id: orgId } })
  
    // Clean up external data
    await deleteS3Prefix(`tenants/${orgId}/`)
    await algolia.deleteBy({ filters: `organizationId:${orgId}` })
    await redis.del(`tenant:${orgId}:*`) // pattern delete
  }