fix(campaigns): delete universes in dependency order

Co-authored-by: multica-agent <github@multica.ai>

apps/web/server/api/v1/campaigns/[id].delete.ts

@@ -1,42 +1,15 @@
-import { requireCampaignAccess, stageTwoApiError, stageTwoDatabase, stageTwoUuid } from '~/server/utils/stage-two-supabase'
+import { deleteStageTwoCampaign, requireCampaignAccess, stageTwoApiError, stageTwoUuid } from '~/server/utils/stage-two-supabase'
 import { requireStageTwoUser } from '~/server/utils/stage-two-supabase'
 
 export default defineEventHandler(async (event) => {
   try {
     const user = await requireStageTwoUser(event)
     const campaignId = stageTwoUuid(event.context.params?.id ?? '')
-    const { owner } = await requireCampaignAccess(campaignId, user.id, true)
+    const { campaign, owner } = await requireCampaignAccess(campaignId, user.id, true)
     if (!owner) throw createError({ statusCode: 403, statusMessage: 'Only the campaign owner can delete it.' })
 
-    // Delete associated data first to respect relational integrity
-    await stageTwoDatabase(`characters?campaign_id=eq.${campaignId}`, { method: 'DELETE' })
-    await stageTwoDatabase(`rounds?campaign_id=eq.${campaignId}`, { method: 'DELETE' })
-    await stageTwoDatabase(`game_events?campaign_id=eq.${campaignId}`, { method: 'DELETE' })
-    await stageTwoDatabase(`dice_rolls?campaign_id=eq.${campaignId}`, { method: 'DELETE' })
-    await stageTwoDatabase(`player_intents?campaign_id=eq.${campaignId}`, { method: 'DELETE' })
-    await stageTwoDatabase(`story_summaries?campaign_id=eq.${campaignId}`, { method: 'DELETE' })
-    await stageTwoDatabase(`memories?campaign_id=eq.${campaignId}`, { method: 'DELETE' })
-    await stageTwoDatabase(`relationships?campaign_id=eq.${campaignId}`, { method: 'DELETE' })
-    await stageTwoDatabase(`audit_entries?campaign_id=eq.${campaignId}`, { method: 'DELETE' })
-
-    // Delete linked world record if it has no other campaigns
-    const campaigns = await stageTwoDatabase<Array<{ world_id: string }>>(
-      `campaigns?select=world_id&id=eq.${campaignId}`,
-    )
-    const worldId = campaigns[0]?.world_id as string | undefined
-    if (worldId) {
-      const otherCampaigns = await stageTwoDatabase<Array<{ id: string }>>(
-        `campaigns?select=id&world_id=eq.${worldId}&id=neq.${campaignId}`,
-      )
-      if (!otherCampaigns.length) {
-        await stageTwoDatabase(`world_entities?world_id=eq.${worldId}`, { method: 'DELETE' })
-        await stageTwoDatabase(`worlds?id=eq.${worldId}`, { method: 'DELETE' })
-      }
-    }
-
-    await stageTwoDatabase(`campaign_members?campaign_id=eq.${campaignId}`, { method: 'DELETE' })
-    await stageTwoDatabase(`invites?campaign_id=eq.${campaignId}`, { method: 'DELETE' })
-    await stageTwoDatabase(`campaigns?id=eq.${campaignId}`, { method: 'DELETE' })
+    const worldId = stageTwoUuid(campaign.world_id, 'world id')
+    await deleteStageTwoCampaign(campaignId, worldId)
     return { success: true }
   } catch (error) {
     stageTwoApiError(error)

apps/web/server/utils/stage-two-supabase.test.ts

@@ -1,5 +1,5 @@
 import { afterEach, describe, expect, it, vi } from 'vitest'
-import { stageTwoDatabase } from './stage-two-supabase'
+import { deleteStageTwoCampaign, stageTwoDatabase } from './stage-two-supabase'
 
 describe('stage-two Supabase client', () => {
   afterEach(() => vi.unstubAllGlobals())
@@ -48,4 +48,64 @@ describe('stage-two Supabase client', () => {
       Authorization: 'Bearer header.payload.signature',
     })
   })
+
+  it('deletes a campaign before deleting its orphaned world', async () => {
+    vi.stubGlobal('useRuntimeConfig', () => ({
+      supabaseUrl: 'https://example.supabase.co',
+      supabaseServiceRoleKey: 'service-role-key',
+      public: { supabaseAnonKey: 'anon-key' },
+    }))
+    const fetchMock = vi.fn(async (url: URL, init?: RequestInit) => {
+      if (url.pathname === '/rest/v1/ai_usage') return new Response(null, { status: 204 })
+      if (url.pathname === '/rest/v1/campaigns' && init?.method === 'DELETE') {
+        return Response.json([{ id: '11111111-1111-4111-8111-111111111111' }])
+      }
+      if (url.pathname === '/rest/v1/campaigns') return Response.json([])
+      if (url.pathname === '/rest/v1/worlds') return new Response(null, { status: 204 })
+      return new Response(null, { status: 404 })
+    })
+    vi.stubGlobal('fetch', fetchMock)
+
+    await deleteStageTwoCampaign(
+      '11111111-1111-4111-8111-111111111111',
+      '22222222-2222-4222-8222-222222222222',
+    )
+
+    expect(fetchMock.mock.calls.map(([url, init]) => [
+      (url as URL).pathname,
+      (url as URL).search,
+      init?.method ?? 'GET',
+    ])).toEqual([
+      ['/rest/v1/ai_usage', '?campaign_id=eq.11111111-1111-4111-8111-111111111111', 'PATCH'],
+      ['/rest/v1/campaigns', '?select=id&id=eq.11111111-1111-4111-8111-111111111111', 'DELETE'],
+      ['/rest/v1/campaigns', '?select=id&world_id=eq.22222222-2222-4222-8222-222222222222&limit=1', 'GET'],
+      ['/rest/v1/worlds', '?id=eq.22222222-2222-4222-8222-222222222222', 'DELETE'],
+    ])
+  })
+
+  it('keeps a world that is still used by another campaign', async () => {
+    vi.stubGlobal('useRuntimeConfig', () => ({
+      supabaseUrl: 'https://example.supabase.co',
+      supabaseServiceRoleKey: 'service-role-key',
+      public: { supabaseAnonKey: 'anon-key' },
+    }))
+    const fetchMock = vi.fn(async (url: URL, init?: RequestInit) => {
+      if (url.pathname === '/rest/v1/ai_usage') return new Response(null, { status: 204 })
+      if (url.pathname === '/rest/v1/campaigns' && init?.method === 'DELETE') {
+        return Response.json([{ id: '11111111-1111-4111-8111-111111111111' }])
+      }
+      if (url.pathname === '/rest/v1/campaigns') {
+        return Response.json([{ id: '33333333-3333-4333-8333-333333333333' }])
+      }
+      return new Response(null, { status: 404 })
+    })
+    vi.stubGlobal('fetch', fetchMock)
+
+    await deleteStageTwoCampaign(
+      '11111111-1111-4111-8111-111111111111',
+      '22222222-2222-4222-8222-222222222222',
+    )
+
+    expect(fetchMock).toHaveBeenCalledTimes(3)
+  })
 })

apps/web/server/utils/stage-two-supabase.ts

@@ -123,6 +123,32 @@ export async function requireCampaignAccess(campaignId: string, userId: string,
   return { campaign, owner: false, memberId: members[0].id }
 }
 
+export async function deleteStageTwoCampaign(campaignId: string, worldId: string): Promise<void> {
+  // Usage is retained for account history, but its restrictive foreign key
+  // must no longer point at the campaign being removed.
+  await stageTwoDatabase(`ai_usage?campaign_id=eq.${campaignId}`, {
+    method: 'PATCH',
+    body: JSON.stringify({ campaign_id: null }),
+  })
+
+  // Campaign-owned rows use ON DELETE CASCADE. Deleting the parent lets
+  // Postgres remove them in dependency-safe order in a single statement.
+  const deleted = await stageTwoDatabase<Array<{ id: string }>>(
+    `campaigns?select=id&id=eq.${campaignId}`,
+    { method: 'DELETE', prefer: 'return=representation' },
+  )
+  if (!deleted[0]) throw createError({ statusCode: 404, statusMessage: 'Campaign not found.' })
+
+  const otherCampaigns = await stageTwoDatabase<Array<{ id: string }>>(
+    `campaigns?select=id&world_id=eq.${worldId}&limit=1`,
+  )
+  if (!otherCampaigns[0]) {
+    // World entities cascade from the world; confirmed coauthor sessions are
+    // retained and automatically clear their world reference.
+    await stageTwoDatabase(`worlds?id=eq.${worldId}`, { method: 'DELETE' })
+  }
+}
+
 export function stageTwoApiError(error: unknown): never {
   if (error && typeof error === 'object' && 'statusCode' in error) throw error
   if (error instanceof StageTwoDatabaseError) {