AI conversation data stored separately from core user data

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.

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.

// 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.

• 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:

  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
  }