[11.1] Project Folder Structure

Front Matter

References:

• GitHub Issue: #60 — Project Folder Structure

• Frontend sub-issues: #425, #426, #427, #428

• Supplementary migration: lib/supabase/migrations/20260512_add_project_folder_to_adventures.sql

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

Introduction & Goals

Problem Summary

Before this feature, WyzQuests creators had no way to organize their Projects (Quests and Adventures) into logical groupings. As creator libraries grew, content discovery became increasingly difficult - a flat, unstructured list of dozens of quests and adventures provided no meaningful navigation. The business need was to replicate a familiar file-system metaphor (like Google Drive folders) within the Creator dashboard.

Goals

• Provide creators with a hierarchical folder system for organizing their Projects (Quests and Adventures).

• Support nesting of folders (parent → child) up to a configurable depth (MAX_FOLDER_DEPTH = 20).

• Support creating, renaming, moving, reordering (drag-and-drop), and archiving folders.

• Support bulk-moving content (quests and adventures) between folders.

• Support full-text search across all folders and projects, returning folder breadcrumb paths.

• Return a full tree structure for the creator's folder hierarchy on demand.

• Persist UI state (expanded/collapsed) per folder.

Non-Goals

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

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

• No sharing or permissions between creators — folders are private to their creator; only agency admins have elevated read-only access.

• Not the same as Adventure Folders — this system organizes the Projects workspace (quests + adventures under a project context). The separate Adventure Folder system (Issue #62) organizes Adventures in the Adventures workspace.

Glossary

High-Level Architecture

System Diagram

The following diagram shows how a creator request flows through the system:

Entity Relationship Diagram

Technologies Used

Detailed Design & Implementation

Data Model / Schema

New Table: project_folders

Modified Table: quests

Modified Table: adventures

Note: Adventures were added to the project folder system in a follow-up migration on May 12, 2026, after it became clear that Adventures (multi-quest bundles) also belong in the Projects workspace.

Indexes

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

API Specification

Base path: /api/creator/project-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/project-folders

Lists folders for the authenticated creator.

Query Parameters:

Response (flat list): Array of ProjectFolderResponse objects.

Response (tree view): Nested ProjectFolderTreeNode[] — each node includes a children array of child nodes.

POST /api/creator/project-folders

Creates a new folder.

Request Body:

{
  "name": "Client A Projects",
  "description": "All projects for Client A",
  "parentFolderId": "550e8400-e29b-41d4-a716-446655440000"
}

Behavior:

1. Validates body with createProjectFolderSchema.

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/project-folders/[folderId]

Returns a single folder with its breadcrumb path, child folders, and content (quests + adventures).

Response:

{
  "meta": {
    "id": "...",
    "name": "Module 2",
    "breadcrumbs": [
      { "id": "...", "name": "Client A" },
      { "id": "...", "name": "Program 1" }
    ],
    "projectCount": 5,
    "childFolderCount": 2,
    ...
  },
  "content": {
    "questList": [...],
    "advList": [...]
  },
  "folders": [...]
}

Note: Breadcrumbs are built by walking parent_folder_id references upward (max MAX_FOLDER_DEPTH = 20 iterations to prevent infinite loops on corrupted data).

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

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

Move validation:

• Checks MAX_FOLDER_DEPTH is not exceeded.

• Calls checkIsDescendant() to prevent circular references (moving a folder into its own descendant).

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

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

PATCH /api/creator/project-folders/reorder

Reorders folders within the same parent level.

Request Body:

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

Behavior: Assigns new order_index values as (position + 1) * 10,000. Validates all folders share the same parent before updating. Implements rollback logic on partial failure.

POST /api/creator/project-folders/move-projects

Bulk-moves quests and/or adventures to a target folder (or to root if targetFolderId: null).

Request Body:

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

Deduplication: Items already in the target folder are skipped; the response reports only the count of items actually moved.

GET /api/creator/project-folders/search

Searches quests, adventures, and folder names matching a query string.

Query Parameters:

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

Behavior: Folder names that match the query appear as results with isFolder: true (Google Drive-style). Quest and adventure content is searched by title and description.

Logic & Workflows

Tree Building (buildFolderTree)

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

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

3. Iterate folders: attach each folder as a child of its parent node.

4. Collect nodes with parent_folder_id = NULL as the root array.

5. Return the root array (each node's children array is populated recursively).

Circular Reference Prevention

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

1. checkIsDescendant(tableName, folderId, newParentFolderId, userId) is called.

2. This recursively walks the subtree rooted at folderId looking for newParentFolderId.

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

4. Depth is capped at MAX_FOLDER_DEPTH = 20 to handle corrupted data gracefully.

Sparse Index Reordering

• All order_index values use gaps of 10,000 (10000, 20000, 30000, ...).

• This allows inserting items between two existing items without renumbering.

• The PATCH /reorder endpoint re-assigns (position + 1) * 10,000 for a full reorder.

Infrastructure & Operations

Dependencies

Monitoring & Alerting

• Error visibility: All database errors surface via ApiResponseHelper.internalError(), which returns a 500 response with the Supabase error message. These are logged by Supabase's built-in logging.

• RLS failures: A Supabase RLS violation returns a null data result, which routes return as a 404 Not Found. These do not log an explicit error but appear as unexpected 404s in analytics.

• Metrics to watch:

  • Rate of 500 responses on /api/creator/project-folders routes.

  • Supabase query execution time for treeView=true requests (scales with folder count per creator).

Deployment Plan

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

No feature flag required — the API endpoints are additive. Existing quests and adventures simply have project_folder_id = NULL (unfiled) until moved.

Testing & Quality Assurance

Test Strategy

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

Test coverage is focused on Zod schema validation (unit-level). The suite verifies:

Known Limitation: Tests are schema-level only and do not exercise actual route handlers or database state. Integration tests (with a test database) are tracked as future work.

Known Limitations

1. Non-atomic reorder: The PATCH /reorder endpoint performs row-by-row updates with manual rollback logic in application code. Unlike the Adventure Folder equivalent (which uses a PostgreSQL RPC), this is not a true database transaction. A crash between updates could leave partial order state.

2. Breadcrumb N+1 (project-folders [folderId] GET): The project folder single-item GET still walks the parent chain with sequential Supabase queries (one per ancestor), unlike the optimized adventure folder route (PR #436) which fetches all folders in a single query and traverses in-memory.

3. No integration tests for route handlers or database behavior.

4. is_expanded is creator-global: The expanded/collapsed state is stored per folder row, not per-session or per-device. Two browser tabs opened by the same creator share the same state.

Maintenance & Support

Troubleshooting

Symptom: Creator sees empty folder list despite having folders

• Check RLS: Verify the request JWT sub claim resolves to a valid clerk_id in app_users.

• Run: SELECT * FROM app_users WHERE clerk_id = '<clerk_id>'; — if no row, the Clerk/Supabase mapping is missing.

Symptom: 404 Not Found when moving a folder

• The target parent folder may not be owned by the same creator. RLS silently filters it out.

• Verify creator_id on both the folder and the target.

Symptom: Folder tree returns an unexpected nesting depth

• Check for parent_folder_id cycles in the database. Run:

  SELECT id, parent_folder_id FROM project_folders
  WHERE creator_id = '<creator_uuid>'
  ORDER BY parent_folder_id NULLS FIRST;

• The checkIsDescendant guard (capped at MAX_FOLDER_DEPTH = 20) should have prevented circular creation, but manual DB edits could introduce them.

Symptom: Quest/adventure disappeared after folder archive

• Check if archiveContents: true was used — this sets project_folder_id = NULL on all content, moving it to "Unfiled." The content itself is not deleted.

Symptom: order_index values are identical for sibling folders

• This can happen if multiple folders were created concurrently. Safe to manually run:

  UPDATE project_folders SET order_index = row_number * 10000
  FROM (SELECT id, ROW_NUMBER() OVER (PARTITION BY parent_folder_id ORDER BY created_at) AS row_number
        FROM project_folders WHERE creator_id = '<uuid>') sub
  WHERE project_folders.id = sub.id;

Changelog

Document version:

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

• 1.1 — Approved, Adventures table patched with project_folder_id column, 05/12/2026