Permanent Delete

Feature Owner: scorevi (Sean Patrick Caintic)
Module: Diagnose
Priority: P1
Sprint #12: Fully Implemented
Date: 2026-06-29

EXECUTIVE SUMMARY

What is this feature? Permanent Delete enables creators to irreversibly remove archived quests and adventures from the database. It enforces an archive-first requirement, requires typed confirmation from the user, includes race condition protection, performs audit logging, and attempts storage cleanup.
Why does it matter? Creators need a definitive way to remove content they're certain they no longer need. Without permanent delete, archived content accumulates indefinitely, consuming storage and cluttering the database.
What's the MVP scope? Permanent delete endpoint with archive-first gating, typed confirmation ("DELETE"), race condition protection (status check at delete time), audit logging, and storage file cleanup. A legacy simpler delete endpoint also exists.

1. USER PAIN POINT & SOLUTION

Current State (Without Feature)

Content can be archived but never fully removed. Database bloat accumulates over time, and creators have no way to permanently dispose of sensitive or abandoned content.

Pain Point

Future State

Creators can permanently delete archived content after explicit typed confirmation. The system verifies the content is archived (not active), logs the deletion for audit, and attempts to clean up associated storage files.

Marketing Hook

"Your data, your control. Permanent delete with safety checks that prevent accidents."

2. 4D FRAMEWORK MAPPING

3. USER FLOWS

Entry Point

• TrashContent.tsx → "Delete Forever" button on archived item

• Confirmation dialog requiring typed "DELETE" text

Success Criteria

• Content permanently removed from database

• Audit log entry created

• Storage files cleaned up

• Race condition protected (only deletes if still "archived")

Main Flow

1. Creator views archived content in TrashContent.tsx

2. Clicks "Delete Forever"

3. Confirmation dialog appears: must type "DELETE" to proceed

4. System validates confirm_text matches

5. System verifies content is in "archived" status (.eq("publishing_status","archived"))

6. System checks verifyContentOwnership()

7. System calls cleanupStorageFiles() to remove associated storage objects

8. System deletes the database row

9. System writes audit log entry

10. UI refreshes to reflect deletion

Edge Cases

• Delete non-archived content: Blocked — must be archived first

• Race condition (status changed between list and delete): Protected by .eq("publishing_status","archived") filter

• Storage cleanup failure: Logged but does not block deletion

• Empty confirm_text: Blocked by Zod z.string().min(1)

• Legacy endpoint bypass: DELETE /api/creator/delete-content exists with no archive requirement

Decision Points

• Delete Forever vs. Keep Archived (irreversible vs. reversible)

• Typed confirmation prevents accidental clicks

4. INFORMATION ARCHITECTURE

Primary

• Content ID (UUID), content type (quests | adventures), publishing_status, confirm_text

Secondary

• Audit log entry, storage cleanup results

Actions

• Delete Forever (from TrashContent.tsx)

5. WIREFRAMES

Excluded — existing UI (TrashContent.tsx Delete Forever button + confirmation dialog).

6. WIREFLOWS

Excluded — existing UI implemented.

7. PROTOTYPE

Excluded — feature is fully implemented.

8. BACKEND SCHEMA

permanentDeleteSchema

{ content_id: UUID, content_type: z.enum(["quests","adventures"]), confirm_text: z.string().min(1) }

FK Cascade Dependencies

Cascade behavior fixed in migration 20260603.

9. API ENDPOINTS

permanent-delete (DELETE) — PRIMARY ENDPOINT

• Schema: permanentDeleteSchema

• Logic:

  1. Validate input (content_id UUID, content_type enum, confirm_text min 1)

  2. verifyContentOwnership() — confirms creator owns the content

  3. Verify content is archived: .eq("publishing_status","archived") — race condition protection

  4. cleanupStorageFiles() — attempts to delete from storage bucket

  5. Delete database row

  6. Audit logging

  7. Return success/error

• Race condition protection: If publishing_status changed between list and delete (e.g., someone restored it), the .eq("publishing_status","archived") filter prevents deletion.

Known Bug: cleanupStorageFiles()

• Line 27: cleanupStorageFiles() deletes from .from("assets")

• Actual table name: asset_metadata

• Impact: Storage cleanup may silently fail because the bucket/table name is incorrect. Files in Supabase Storage associated with the deleted content may persist as orphans.

delete-content (DELETE) — LEGACY ENDPOINT

• No archive-first requirement

• No typed confirmation (no confirm_text)

• Uses .match() instead of .eq() filters

• Simpler, less safe — likely predates the archive workflow

10. DATA REQUIREMENTS

Frontend Needs

• Delete Forever button with confirmation dialog

• Text input for typed confirmation ("DELETE")

• Visual feedback on deletion success

• Automatic list refresh after deletion

API Calls

• DELETE /api/creator/permanent-delete

Caching

• Archived list: Invalidate on permanent delete

• Audit log: Write-only (no read endpoint in MVP)

11. PERFORMANCE CONSIDERATIONS

DB Optimization

• FK CASCADE handles related row cleanup automatically (quest_content_cards, activity_submissions, adventure_sequences)

• Single DELETE operation triggers cascades

Response Time

• Dependent on cascade depth and storage cleanup latency

• Sub-200ms typical for simple content; may increase with many related rows

12. SECURITY & AUTHORIZATION

Access Control

• Clerk authentication required

• verifyContentOwnership() prevents cross-creator deletion

• Archive-first gating prevents accidental deletion of active content

• Typed confirmation prevents UI misclicks

Auth Logic

• Ownership verified before any destructive operation

• Status verified at time of deletion (race condition protection)

• Audit log creates paper trail for compliance

Validation

• Input validated via permanentDeleteSchema (Zod)

• confirm_text must be non-empty string (z.string().min(1))

• content_id must be valid UUID

• content_type must be "quests" or "adventures"

13. ERROR HANDLING

14. TESTING CHECKLIST

Happy Path

• Archive quest → permanently delete → row removed from DB

• Archive adventure → permanently delete → row removed from DB

• Typed confirmation "DELETE" required before request is sent

• Audit log entry created on successful deletion

• Quest cascade: quest_content_cards and activity_submissions deleted

• Adventure cascade: adventure_sequences deleted

Edge Cases

• Attempt to delete non-archived content (blocked)

• Attempt to delete with empty confirm_text (blocked by Zod)

• Attempt to delete with wrong confirm_text (blocked by Zod)

• Race condition: content restored between list and delete (blocked by .eq filter)

• Attempt to delete content owned by another creator (blocked)

• Storage cleanup failure does not block deletion

15. OPEN QUESTIONS

• Should the cleanupStorageFiles() bug (.from("assets") vs "asset_metadata") be fixed?

• Should the legacy DELETE /api/creator/delete-content endpoint be deprecated and removed?

• Is the typed confirmation text "DELETE" hardcoded or configurable?

16. OUT OF SCOPE

• Bulk permanent delete (multiple items at once)

• Deletion scheduling (auto-delete after X days in archive)

• Soft-delete with recovery window (undo permanent delete)

• GDPR-compliant data erasure with full cascade audit

17. SUCCESS METRICS

• Zero accidental permanent deletions (gated by archive + typed confirmation)

• Storage reclaimed per deletion

• Audit log completeness for compliance

• Delete-to-archive ratio (healthy if archive >> delete)

18. DEPENDENCIES

• Archive Project feature (1.4) — archive-first requirement

• verifyContentOwnership() utility function

• FK cascade constraints (migration 20260603)

• Supabase Storage for cleanupStorageFiles()

• Audit logging system

19. TIMELINE

Completed — Feature is fully implemented in Sprint #12.

Document Version

1.0 - Initial version - 2026-06-29 08:17 UTC

1.1 - Added Document Version section and update author to have full name - 2026-06-29 08:45 UTC