[11.3] Adventure Folder Structure

Front Matter

References:

• GitHub Issue: #62 — Adventure Folder Structure

• Frontend sub-issues: #429, #430

• Shared utility: lib/utils/folder-helpers.ts

• Related feature: Project Folder Structure (Issue #60)

Introduction & Goals

Problem Summary

WyzQuests creators can produce Adventures — multi-quest bundles that group related quests into a learner journey. As creators built more Adventures, the Adventures dashboard became an unsorted flat list with no organizational structure. Creators working for multiple clients or building themed curricula had no way to separate or group their work. This feature introduces a hierarchical folder system dedicated to the Adventures workspace, solving this organization problem for the Adventures content type.

Goals

• Allow creators to organize their Adventures into named, nested folders.

• Support full CRUD on folders: create, read, update (rename/move), delete (archive).

• Support drag-and-drop reorder of folders within the same parent via a dedicated endpoint.

• Support bulk-moving one or more adventures into a target folder.

• Provide a full tree-view response for rendering the creator's folder hierarchy.

• Provide full-text search across adventure folders and adventure titles with folder breadcrumb paths.

• Detect and surface orphan folders (folders whose parent has been deleted outside the normal archive flow) for data integrity visibility.

• Ensure folder reordering is atomic — either all order_index updates succeed or none do.

Non-Goals

• No UI implementation — this PR is backend-only. Frontend is tracked in issues #429–#430.

• No hard deletion — folders are soft-deleted via archiving only.

• No sharing between creators — folders are private; only agency admins get elevated read-only access.

• Not the same as Project Folders — Adventure Folders exclusively organize Adventures in the Adventures workspace. The Project Folder system (Issue #60) organizes the Projects workspace (quests and adventures under a project context). These are two separate systems with separate tables.

• No quest organization — this folder system is for Adventures only. Quests are organized under the Project Folder system.

Glossary

Technologies Used

Detailed Design & Implementation

Data Model / Schema

New Table: adventure_folders

Difference from Project Folders: Adventure Folders do not have an is_expanded column. UI expansion state for the Adventures workspace is not persisted server-side.

Modified Table: adventures

Indexes

RLS Policies

PostgreSQL RPC: reorder_adventure_folders

File: lib/supabase/migrations/20260427_add_reorder_adventure_folders_rpc.sql

Added in response to PR #436 blocking feedback (non-atomic row-by-row reorder). The function accepts an array of {id, order_index} objects and updates them all inside a single PostgreSQL transaction. If any row fails (not found or wrong owner), the entire transaction rolls back.

reorder_adventure_folders(
    p_creator_id UUID,
    p_folder_orders JSONB  -- [{id: "...", order_index: 10000}, ...]
) RETURNS INTEGER          -- count of rows updated

Zod Schemas (lib/schemas/adventure-folder.schema.ts)

API Specification

Base path: /api/creator/adventure-folders
Authentication: All endpoints require a valid Clerk JWT (authenticateUserWithRole()).
Response format: All responses use the ApiResponse wrapper:

{ "success": true, "data": { ... }, "error": null }
{ "success": false, "data": null, "error": { "code": "VALIDATION_ERROR", "message": "..." } }

Cache-Control header (all GET responses): private, max-age=30, stale-while-revalidate=60

GET /api/creator/adventure-folders

Lists adventure folders for the authenticated creator.

Query Parameters:

Response (flat list): Array of AdventureFolderResponse objects, each with adventureCount and childFolderCount computed fields.

Response (tree view): The roots array of AdventureFolderTreeNode is returned. Each node may carry isOrphan: true if its parent_folder_id references a missing row (orphan detection, added PR #436). The full tree response also exposes orphanCount for monitoring purposes.

POST /api/creator/adventure-folders

Creates a new adventure folder.

Request Body:

{
  "name": "Fantasy Adventures",
  "description": "All fantasy-themed adventures",
  "parentFolderId": "550e8400-e29b-41d4-a716-446655440000"
}

Behavior:

1. Validates body with createAdventureFolderSchema.

2. If parentFolderId provided, verifies it exists and belongs to the creator.

3. Calculates order_index as (max existing order_index at parent level) + 10,000.

4. Inserts the folder row.

GET /api/creator/adventure-folders/[folderId]

Returns a single folder with its breadcrumb path, child folders, and contained adventures.

Performance (PR #436 optimization): Fetches all of the creator's folders in a single query, then builds the breadcrumb trail via in-memory map traversal (Map<folderId, folder>). This replaces the previous approach of one Supabase query per ancestor (N+1 problem).

Response:

{
  "meta": {
    "id": "...",
    "name": "Beginner Series",
    "breadcrumbs": [
      { "id": "...", "name": "Fantasy" }
    ],
    "adventureCount": 4,
    "childFolderCount": 1,
    ...
  },
  "content": { "advList": [...] },
  "folders": [...]
}

PUT /api/creator/adventure-folders/[folderId]

Updates a folder's metadata (name, description) or moves it to a new parent.

Move validation:

• Checks the new depth would not exceed MAX_FOLDER_DEPTH = 20.

• Calls checkIsDescendant() to prevent circular references.

DELETE /api/creator/adventure-folders/[folderId]

Archives a folder (soft delete). When archiveContents: true, recursively archives all child folders and sets adventure_folder_id = NULL on all contained adventures (unfiling them).

PATCH /api/creator/adventure-folders/reorder

Atomically reorders folders within the same parent level using the PostgreSQL RPC.

Request Body:

{
  "parentFolderId": "...",
  "orderedFolderIds": ["id-1", "id-2", "id-3"]
}

Behavior:

1. Validates body with reorderAdventureFoldersSchema.

2. Calls the reorder_adventure_folders(userId, [{id, order_index}]) RPC with values (position + 1) * 10,000.

3. If the RPC raises an exception (any folder not found or wrong owner), the entire transaction rolls back — no partial state.

Key difference from Project Folders: This endpoint uses a PostgreSQL RPC for true atomicity. The project folder reorder uses application-level rollback logic.

POST /api/creator/adventure-folders/move-adventures

Bulk-moves one or more adventures to a target folder (or to root if targetFolderId: null).

Request Body:

{
  "contentId": ["adventure-uuid-1", "adventure-uuid-2"],
  "targetFolderId": "folder-uuid"
}

Deduplication: Adventures already in the target folder are filtered out before the update; the response reports only the count of items actually moved.

Count reliability (PR #436 fix): Moved count is computed as adventureIds.length instead of the Supabase count property, which was found to be unreliable.

GET /api/creator/adventure-folders/search

Searches adventure folders and adventure titles matching a query string.

Query Parameters:

Security: User input is run through escapeForPostgrestFilter() before being passed to Supabase .ilike() filters, preventing PostgREST grammar injection and LIKE wildcard attacks.

Behavior: Matching folder names appear in results with isFolder: true (Google Drive-style). Adventure content is searched by title and description.

Logic & WorkflowsTree Building with Orphan Detection (buildAdventureFolderTree)

1. Fetch all of the creator's folders in one query.

2. Build a Map<folderId, node> for O(1) lookups.

3. Iterate all folders:

   • If parent_folder_id is set but the parent is not in the map → mark node isOrphan: true and add to roots.

   • Otherwise, attach as a child of the parent node.

4. Collect nodes with parent_folder_id = NULL as the normal roots.

5. Increment orphanCount for each orphan detected.

6. Return { roots: AdventureFolderTreeNode[], orphanCount }.

Only roots is returned in the API response for consistency. orphanCount is retained for potential future monitoring endpoints.

Breadcrumb Building (Optimized — PR #436)

Rather than issuing one Supabase query per ancestor:

1. Fetch all of the creator's folders in a single query (reused from the main fetch).

2. Build a Map<folderId, folder> for O(1) parent access.

3. Walk parent_folder_id references in-memory, prepending each ancestor to the breadcrumb array.

4. Stop at MAX_FOLDER_DEPTH = 20 to guard against cycles in corrupted data.

Circular Reference Prevention

When moving a folder (PUT /[folderId] with newParentFolderId):

1. checkIsDescendant("adventure_folders", folderId, newParentFolderId, userId) is called.

2. Recursively walks the subtree rooted at folderId looking for newParentFolderId.

3. If found, the move is rejected with 400 Bad Request.

4. Depth is capped at MAX_FOLDER_DEPTH = 20.

Sparse Index Reordering

• order_index values use gaps of 10,000.

• Insertions between existing items can use the midpoint without renumbering.

• PATCH /reorder re-assigns (position + 1) * 10,000 for a full reorder via the atomic RPC.

Infrastructure & Operations

Dependencies

Monitoring & Alerting

• Error visibility: All database errors surface via ApiResponseHelper.internalError() (HTTP 500). These are logged by Supabase's built-in logging dashboard.

• RLS failures: A Supabase RLS denial returns null data, causing the route to return 404 Not Found. These appear as unexpected 404s in API analytics and should be investigated if frequent.

• Orphan detection: The tree-view response includes an orphanCount field when non-zero. A non-zero count indicates a data integrity issue (e.g., a parent folder was hard-deleted or its id was changed outside the normal archive flow).

• Metrics to watch:

  • Rate of 500 errors on /api/creator/adventure-folders routes.

  • treeView=true query performance (scales with creator's folder count).

  • Non-zero orphanCount values in tree responses.

Deployment Plan

Two database migrations must be run in order before the API routes go live:

No feature flag required — the API is additive. Existing adventures have adventure_folder_id = NULL (unfiled) until moved.

Testing & Quality Assurance

Test Strategy

File: tests/api/adventure-folders.test.ts

Test coverage note (PR #436): These are schema-level validation tests only. They do not exercise actual route handlers or database operations. See Known Limitations for the integration test gap.

Known Limitations

1. Schema-only tests — no integration tests for route handlers or actual database operations. A test database fixture with mock Supabase calls or a real test schema is needed to cover route-level logic.

2. No is_expanded column — unlike Project Folders, the Adventures folder tree UI state is not persisted server-side. If this is needed in future, a migration to add this column would be required.

3. Breadcrumb optimization is single-folder GET only — the tree-view response does not embed per-node breadcrumbs. Client code must derive breadcrumbs from the tree structure itself.

4. RPC grant scope — the reorder_adventure_folders function uses SECURITY DEFINER and is granted to the authenticated role. While the function enforces creator_id ownership, the broad role grant should be reviewed if the function is ever expanded.

Maintenance & Support

Troubleshooting

Symptom: Creator's folder tree shows isOrphan: true nodes

• A folder's parent_folder_id references a row that no longer exists.

• Check for manual DB interventions or failed archive cascades:

  SELECT id, name, parent_folder_id
  FROM adventure_folders
  WHERE parent_folder_id IS NOT NULL
    AND parent_folder_id NOT IN (SELECT id FROM adventure_folders)
    AND creator_id = '<creator_uuid>';

• Resolution: Set parent_folder_id = NULL on orphaned rows to promote them to root, or re-create their missing parent.

Symptom: 400 Bad Request — "All folders must have the same parent folder" on reorder

• The orderedFolderIds array contains folders from different parent levels.

• The client-side drag-and-drop UI should constrain reordering to siblings only. If this error occurs in production, check for a client-side bug where cross-parent drag is permitted.

Symptom: Reorder returns a 500 error after partial update

• This should not occur since the atomic RPC rolls back the entire transaction on failure.

• If it does, check the Supabase function logs for the reorder_adventure_folders RPC. Look for the exception message: "Folder <id> not found or not owned by creator".

Symptom: Creator sees an empty folder after moving adventures in

• Verify the adventure_folder_id column was updated:

  SELECT id, adventure_folder_id FROM adventures
  WHERE id IN ('<adventure-uuid-1>', '<adventure-uuid-2>');

• If the column is still NULL, check if the move-adventures request returned a moved: 0 count (deduplication may have skipped them if they were already in the target folder).

Symptom: 404 when accessing a valid folder

• The folder exists but the creator_id in the RLS policy doesn't match the JWT sub claim.

• Run:

  SELECT id, creator_id FROM adventure_folders WHERE id = '<folder-uuid>';
  SELECT id, clerk_id FROM app_users WHERE clerk_id = '<clerk-sub-claim>';

  and verify that app_users.id matches adventure_folders.creator_id.

Symptom: Reorder not persisting after page refresh

• Check order_index values in the database for the affected folders.

• Confirm the PATCH /reorder response returned success: true.

• Verify the RPC function exists in Supabase:

  SELECT proname FROM pg_proc WHERE proname = 'reorder_adventure_folders';

Changelog

Document version:

• 1.0 — Draft, Initial backend implementation pushed to develop after initial dev review, 04/21/2026

• 1.1 — Approved, PR #436 blocking issues resolved and non-blocking improvements applied, 04/27/2026