# AI conversation data stored separately from core user data

- **Pattern:** `ab-000168` (`ai-data-privacy.data-retention-deletion.ai-logs-separation`)
- **Severity:** low
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000168
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000168)

## Why it matters

GDPR Article 25 (Data Protection by Design) and ISO/IEC 27001:2022 A.8.10 both require that data lifecycle controls be built into the system architecture, not retrofitted. When AI conversation history is stored as a JSON blob column on the users table — `users.chat_history jsonb` — it becomes structurally impossible to apply independent retention policies, deletion on request, or access controls to conversation data without touching the user record. Every conversation row deletion becomes a user record mutation, and a data retention query must read the entire users table. Proper table separation is a prerequisite for every other data lifecycle control in this category.

## Severity rationale

Low because embedded-JSON storage is an architectural deficiency rather than an active vulnerability, but it structurally blocks all independent lifecycle controls for AI conversation data.

## Remediation

Define AI conversation data in a dedicated Prisma model (or SQL table) with a foreign key to users — never as a JSON column on the users model.

```prisma
// schema.prisma
model Conversation {
  id        String    @id @default(cuid())
  userId    String
  user      User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  createdAt DateTime  @default(now())
  messages  Message[]
}

model Message {
  id             String       @id @default(cuid())
  conversationId String
  conversation   Conversation @relation(fields: [conversationId], references: [id], onDelete: Cascade)
  role           String       // "user" | "assistant"
  createdAt      DateTime     @default(now())
}
```

With this structure, retention cleanup is a simple `DELETE FROM conversations WHERE created_at < $cutoff` — no user record touched. User-facing delete is a row delete with no mutation risk. If migrating from an embedded JSON column, write a one-time migration script that extracts the JSON rows into the new table before dropping the column.

## Detection

- **ID:** `ai-logs-separation`
- **Severity:** `low`
- **What to look for:** Enumerate every relevant item. Examine the database schema for where AI conversation data is stored. In Prisma, look for whether a `conversations` or `messages` model exists as a separate model with a foreign key to `User`, or whether conversation data is stored as a JSON column directly on the `User` model. In SQL migrations, look for separate tables vs. embedded JSON. In Mongoose, look for separate Schema definitions vs. embedded subdocuments on the User schema.
- **Pass criteria:** At least 1 of the following conditions is met. AI conversation history is stored in a dedicated table/collection (`conversations`, `messages`, `ai_logs`, etc.) with a foreign key relationship to users — not embedded as a JSON field on the user record itself.
- **Fail criteria:** AI conversation history is stored as a JSON blob column directly on the users table (e.g., `users.chat_history jsonb`, `users.conversations json`), making it impossible to independently manage the lifecycle of conversation data.
- **Skip (N/A) when:** No AI conversation data is persisted to a database. Or the project stores conversations in a dedicated external service (e.g., a vector database or a conversation-as-a-service provider) with its own data lifecycle controls.
- **Detail on fail:** `"AI conversation history appears embedded as JSON in the users table — this prevents independent data lifecycle management for conversation records"`
- **Remediation:** Storing AI conversations in a dedicated table makes it straightforward to: apply retention policies (delete old conversations without touching the user record), implement user-facing delete (delete conversation rows without touching the user row), and run analytics on conversations without joining user PII.

  In Prisma:

  ```prisma
  model User {
    id            String         @id @default(cuid())
    email         String         @unique
    // ...core user fields...
    conversations Conversation[]
  }

  model Conversation {
    id        String    @id @default(cuid())
    userId    String
    user      User      @relation(fields: [userId], references: [id], onDelete: Cascade)
    createdAt DateTime  @default(now())
    messages  Message[]
  }

  model Message {
    id             String       @id @default(cuid())
    conversationId String
    conversation   Conversation @relation(fields: [conversationId], references: [id], onDelete: Cascade)
    role           String       // "user" | "assistant"
    createdAt      DateTime     @default(now())
    // Note: store message content carefully — consider encryption at rest
  }
  ```

## External references

- gdpr Art. 25
- iso-27001 A.8.10

Taxons: data-integrity

HTML version: https://auditbuffet.com/patterns/ab-000168