Cascade Delete Patterns

Cascade Delete Patterns

Normalized schema requires explicit cascade deletes. Convex has no foreign key constraints or automatic cascades - must handle manually.

Why Cascade Deletes Needed

Normalized schema separates related data into multiple tables. Deleting a conversation must also delete:

• Junction tables (projectConversations, conversationParticipants)
• Child records (messages, attachments, toolCalls, sources)
• Dependent entities (bookmarks, shares, canvasDocuments)

Leaving orphaned records wastes storage, breaks queries, violates data integrity.

Core Strategy

1. Parallel queries: Fetch all related records with Promise.all
2. Batch deletions: Delete independent tables in parallel
3. Sequential for dependencies: Delete children before parents when order matters
4. Nullify reusable entities: Files/​memories can exist without conversation

Conversation Cascade Delete

From packages/​backend/​convex/​lib/​utils/​cascade.ts:

export async function cascadeDeleteConversation(
  ctx: MutationCtx,
  conversationId: Id<"conversations">,
  options?: { deleteMessages?: boolean; deleteConversation?: boolean },
): Promise<void> {
  const deleteMessages = options?.deleteMessages ?? true;
  const deleteConversation = options?.deleteConversation ?? true;

  // Step 1: Parallel queries for all related records
  const [
    bookmarks,
    shares,
    files,
    memories,
    junctions,
    participants,
    tokenUsage,
    attachments,
    toolCalls,
    sources,
    canvasDocs,
  ] = await Promise.all([
    ctx.db
      .query("bookmarks")
      .withIndex("by_conversation", (q) =>
        q.eq("conversationId", conversationId),
      )
      .collect(),
    ctx.db
      .query("shares")
      .withIndex("by_conversation", (q) =>
        q.eq("conversationId", conversationId),
      )
      .collect(),
    // ... more queries
  ]);

  // Step 2: Parallel deletions for independent tables
  await Promise.all([
    ...bookmarks.map((b) => ctx.db.delete(b._id)),
    ...shares.map((s) => ctx.db.delete(s._id)),
    ...junctions.map((j) => ctx.db.delete(j._id)),
    ...participants.map((p) => ctx.db.delete(p._id)),
    ...tokenUsage.map((t) => ctx.db.delete(t._id)),
    ...attachments.map((a) => ctx.db.delete(a._id)),
    ...toolCalls.map((tc) => ctx.db.delete(tc._id)),
    ...sources.map((src) => ctx.db.delete(src._id)),
  ]);

  // Step 3: Nullify files/​memories (can exist independently)
  await Promise.all([
    ...files.map((f) => ctx.db.patch(f._id, { conversationId: undefined })),
    ...memories.map((m) => ctx.db.patch(m._id, { conversationId: undefined })),
  ]);

  // Step 4: Handle parent-child relationships (history before documents)
  for (const doc of canvasDocs) {
    const history = await ctx.db
      .query("canvasHistory")
      .withIndex("by_document", (q) => q.eq("documentId", doc._id))
      .collect();
    await Promise.all(history.map((h) => ctx.db.delete(h._id)));
    await ctx.db.delete(doc._id);
  }

  // Step 5: Delete messages (optional)
  if (deleteMessages) {
    const messages = await ctx.db
      .query("messages")
      .withIndex("by_conversation", (q) =>
        q.eq("conversationId", conversationId),
      )
      .collect();
    await Promise.all(messages.map((msg) => ctx.db.delete(msg._id)));
  }

  // Step 6: Delete conversation itself (optional)
  if (deleteConversation) await ctx.db.delete(conversationId);
}

Usage in mutations:

export const deleteConversation = mutation({
  args: { conversationId: v.id("conversations") },
  handler: async (ctx, args) => {
    const user = await getCurrentUserOrCreate(ctx);
    const conv = await ctx.db.get(args.conversationId);
    if (!conv || conv.userId !== user._id) throw new Error("Not found");

    await cascadeDeleteConversation(ctx, args.conversationId);
  },
});

Junction Table Cleanup

Junction tables represent many-to-many relationships. Must delete before parent entities.

// ✅ CORRECT: Delete junction first
const junctions = await ctx.db
  .query("projectConversations")
  .withIndex("by_conversation", (q) =>
    q.eq("conversationId", conversationId),
  )
  .collect();

await Promise.all(junctions.map((j) => ctx.db.delete(j._id)));

Junction tables to handle:

• projectConversations - projects ↔ conversations
• projectNotes - projects ↔ notes
• projectFiles - projects ↔ files
• conversationParticipants - users ↔ conversations
• bookmarkTags, snippetTags, noteTags, taskTags - tags ↔ entities

Nullify vs Delete Strategy

Delete - Entity only exists for this parent:

• attachments - message-specific files
• toolCalls - execution results tied to message
• sources - citations for specific message
• bookmarks - reference to message in conversation
• shares - share link for conversation

Nullify - Entity can exist independently:

• files - user uploads, can be reused across conversations
• memories - extracted facts, retain even after conversation deleted

// Nullify pattern - preserve entity, remove association
await Promise.all([
  ...files.map((f) => ctx.db.patch(f._id, { conversationId: undefined })),
  ...memories.map((m) => ctx.db.patch(m._id, { conversationId: undefined })),
]);

Parent-Child Ordering

When parent-child relationship exists, delete children first to avoid orphans.

Example: canvasDocuments (parent) → canvasHistory (child)

// ❌ WRONG: Delete parent first, orphans children
await ctx.db.delete(doc._id);
const history = await ctx.db
  .query("canvasHistory")
  .withIndex("by_document", (q) => q.eq("documentId", doc._id))
  .collect();
await Promise.all(history.map((h) => ctx.db.delete(h._id))); // Already orphaned

// ✅ CORRECT: Delete children first
for (const doc of canvasDocs) {
  const history = await ctx.db
    .query("canvasHistory")
    .withIndex("by_document", (q) => q.eq("documentId", doc._id))
    .collect();
  await Promise.all(history.map((h) => ctx.db.delete(h._id)));
  await ctx.db.delete(doc._id);
}

Other parent-child relationships:

• messages (parent) → attachments, toolCalls, sources (children)
• knowledgeSources (parent) → knowledgeChunks (children)
• files (parent) → fileChunks (children)

GDPR User Data Deletion

From packages/​backend/​convex/​lib/​utils/​cascade.ts, 5-phase approach:

export async function cascadeDeleteUserData(
  ctx: MutationCtx,
  userId: Id<"users">,
): Promise<void> {
  // Phase 1: Delete junction tables (many-to-many)
  const [bookmarkTags, snippetTags, noteTags, taskTags, ...] = await Promise.all([
    ctx.db.query("bookmarkTags").withIndex("by_user", (q) => q.eq("userId", userId)).collect(),
    // ...
  ]);
  await Promise.all([
    ...bookmarkTags.map((r) => ctx.db.delete(r._id)),
    // ...
  ]);

  // Phase 2: Delete child records (have FKs to parents deleted later)
  const [toolCalls, sources, attachments, ...] = await Promise.all([
    ctx.db.query("toolCalls").withIndex("by_user", (q) => q.eq("userId", userId)).collect(),
    // ...
  ]);
  await Promise.all([...toolCalls.map((r) => ctx.db.delete(r._id)), ...]);

  // Phase 3: Delete parent records (messages, canvasDocuments, knowledgeSources)
  const [messages, canvasDocuments, knowledgeSources] = await Promise.all([
    ctx.db.query("messages").withIndex("by_user", (q) => q.eq("userId", userId)).collect(),
    // ...
  ]);
  await Promise.all([...messages.map((r) => ctx.db.delete(r._id)), ...]);

  // Phase 4: Delete main content entities
  const [conversations, bookmarks, snippets, notes, tasks, ...] = await Promise.all([
    ctx.db.query("conversations").withIndex("by_user", (q) => q.eq("userId", userId)).collect(),
    // ...
  ]);
  await Promise.all([...conversations.map((r) => ctx.db.delete(r._id)), ...]);

  // Phase 5: Delete user config/​metadata
  const [userPreferences, userOnboarding, usageRecords, ...] = await Promise.all([
    ctx.db.query("userPreferences").withIndex("by_user", (q) => q.eq("userId", userId)).collect(),
    // ...
  ]);
  await Promise.all([...userPreferences.map((r) => ctx.db.delete(r._id)), ...]);
}

Usage:

export const deleteMyData = mutation({
  args: { confirmationText: v.string() },
  handler: async (ctx, { confirmationText }) => {
    if (confirmationText !== "DELETE MY DATA") {
      throw new Error('Please type "DELETE MY DATA" to confirm');
    }

    const identity = await ctx.auth.getUserIdentity();
    if (!identity) throw new Error("Unauthorized");

    const user = await ctx.db
      .query("users")
      .withIndex("by_clerk_id", (q) => q.eq("clerkId", identity.subject))
      .first();

    if (!user) throw new Error("User not found");

    await cascadeDeleteUserData(ctx, user._id);

    return { success: true };
  },
});

Performance Optimization

Parallel queries - Fetch all related records at once:

// ✅ GOOD: Single round-trip, parallel execution
const [bookmarks, shares, files] = await Promise.all([
  ctx.db.query("bookmarks").collect(),
  ctx.db.query("shares").collect(),
  ctx.db.query("files").collect(),
]);

// ❌ BAD: Sequential, 3x latency
const bookmarks = await ctx.db.query("bookmarks").collect();
const shares = await ctx.db.query("shares").collect();
const files = await ctx.db.query("files").collect();

Parallel deletions - Delete independent records simultaneously:

// ✅ GOOD: All deletions in parallel
await Promise.all([
  ...bookmarks.map((b) => ctx.db.delete(b._id)),
  ...shares.map((s) => ctx.db.delete(s._id)),
  ...attachments.map((a) => ctx.db.delete(a._id)),
]);

// ❌ BAD: Sequential deletions
for (const b of bookmarks) await ctx.db.delete(b._id);
for (const s of shares) await ctx.db.delete(s._id);

Index usage - Always use indexed queries for related records:

// ✅ GOOD: Uses index, fast lookup
ctx.db
  .query("messages")
  .withIndex("by_conversation", (q) => q.eq("conversationId", conversationId))
  .collect()

// ❌ BAD: Full table scan
ctx.db
  .query("messages")
  .filter((q) => q.eq(q.field("conversationId"), conversationId))
  .collect()

Key Files

• packages/​backend/​convex/​lib/​utils/​cascade.ts - Cascade delete utilities
• packages/​backend/​convex/​conversations.ts - Conversation deletion
• packages/​backend/​convex/​users.ts - GDPR user data deletion

Common Patterns

Full cascade delete:

await cascadeDeleteConversation(ctx, conversationId);

Preserve conversation, delete messages only:

await cascadeDeleteConversation(ctx, conversationId, {
  deleteMessages: true,
  deleteConversation: false,
});

GDPR compliance:

await cascadeDeleteUserData(ctx, userId); // Keeps user account
await ctx.db.delete(userId); // Full account deletion

Anti-Patterns

Don't use nested promises:

// ❌ BAD: Nested promises, hard to reason about
await Promise.all(
  bookmarks.map(async (b) => {
    const tags = await ctx.db.query("tags").collect();
    await ctx.db.delete(b._id);
  })
);

// ✅ GOOD: Flat structure, clear dependencies
const bookmarks = await ctx.db.query("bookmarks").collect();
const tags = await ctx.db.query("tags").collect();
await Promise.all([
  ...bookmarks.map((b) => ctx.db.delete(b._id)),
  ...tags.map((t) => ctx.db.delete(t._id)),
]);

Don't skip indexes:

// ❌ BAD: Filter scans entire table
const messages = await ctx.db
  .query("messages")
  .filter((q) => q.eq(q.field("userId"), userId))
  .collect();

// ✅ GOOD: Index lookup, O(log n)
const messages = await ctx.db
  .query("messages")
  .withIndex("by_user", (q) => q.eq("userId", userId))
  .collect();

Don't forget junction tables:

// ❌ BAD: Orphans projectConversations records
await ctx.db.delete(conversationId);

// ✅ GOOD: Clean up junction first
const junctions = await ctx.db
  .query("projectConversations")
  .withIndex("by_conversation", (q) => q.eq("conversationId", conversationId))
  .collect();
await Promise.all(junctions.map((j) => ctx.db.delete(j._id)));
await ctx.db.delete(conversationId);

Don't delete parents before children:

// ❌ BAD: Orphans canvasHistory
await ctx.db.delete(doc._id);

// ✅ GOOD: Delete history first
const history = await ctx.db
  .query("canvasHistory")
  .withIndex("by_document", (q) => q.eq("documentId", doc._id))
  .collect();
await Promise.all(history.map((h) => ctx.db.delete(h._id)));
await ctx.db.delete(doc._id);