Manual Skills

  1. Overview
  2. Hand Off Documentation
  3. Diagnose (Project Setup)
  4. Manual Skills

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

EXECUTIVE SUMMARY

What is this feature? Manual Skills provides skill tagging for quests and adventures via TEXT[] array columns with GIN indexes for efficient filtering. It includes a Question Hub for managing activities and questions (7 question types), with full CRUD API endpoints and a skills filter hook for the frontend.
Why does it matter? Creators need to tag content with skills for discoverability and curriculum mapping. Without skill tagging, quests and adventures cannot be filtered, searched, or categorized by learning outcomes.
What's the MVP scope? Skill arrays on quests and adventures, GIN-indexed for performance, useSkillsFilter hook, Question Hub page, activity CRUD (5 endpoints), and question management with 7 DB-supported types.

1. USER PAIN POINT & SOLUTION

Current State (Without Feature)

Content exists without structured skill metadata. Creators cannot categorize quests by competencies, and learners cannot discover content aligned to specific skills they want to develop.

Pain Point

Type

Impact

Emotional

Frustration when content is hard to find or organize

Functional

No filtering, searching, or grouping by skills

Business

Poor content discoverability reduces learner engagement and platform stickiness

Future State

Every quest and adventure is tagged with skills (TEXT[]). Creators use the skills filter to organize their library. The Question Hub centralizes question and activity management for skill-aligned assessments.

Marketing Hook

"Tag once, discover forever. Skills-powered content that connects learners to what they need."

2. 4D FRAMEWORK MAPPING

Phase

Mapping

Diagnose

Skills help identify gaps in the content library

Design

Skill tagging informs quest structure and assessment design

Develop

Activities and questions are aligned to tagged skills

Deliver

Skills metadata persists for export and analytics

3. USER FLOWS

Entry Point

  • Quest/Adventure creation form → Skills input field (multi-tag)

  • Creator Dashboard → Question Hub

  • Content Library → Skills filter dropdown

Success Criteria

  • Skills array saved and retrievable on quests and adventures

  • Skills filter returns correctly filtered content

  • Questions created with valid types persist correctly

  • Activity CRUD operations complete without errors

Main Flow

  1. Creator adds skills during content creation

  2. Skills stored as TEXT[] with GIN index

  3. useSkillsFilter hook provides filtering logic on frontend

  4. Creator navigates to Question Hub (app/creator/question-hub/page.tsx)

  5. Creates activities with questions of supported types

  6. Assigns grading_settings (JSONB) to activities

Edge Cases

  • Empty skills array: Allowed; content is unfiltered

  • Duplicate skills: Array accepts duplicates (no unique constraint)

  • Unsupported question types: DB allows 'reflection' and 'essay' types, but Zod activitySchema only allows 5 types — mismatch exists

Decision Points

  • Which skills to tag (free-text input)

  • Question type selection (5 supported types in Zod, 7 in DB)

4. INFORMATION ARCHITECTURE

Primary

  • Skills (TEXT[]), activity type (activity|quiz), question type ENUM

Secondary

  • grading_settings (JSONB), question content, answer options

Actions

  • Add/remove skills, filter by skills, create/edit/delete activities and questions

5. WIREFRAMES

Excluded — existing UI implemented.

6. WIREFLOWS

Excluded — existing UI implemented.

7. PROTOTYPE

Excluded — feature is fully implemented.

8. BACKEND SCHEMA

quests table (skills-related)

Column

Type

Notes

skills

TEXT[]

GIN indexed

id

UUID

FK reference for questions/activities

adventures table (skills-related)

Column

Type

Notes

skills

TEXT[]

GIN indexed

activities table (verified columns)

Column

Type

Notes

id

UUID

PK

creator_id

UUID

FK → app_users

quest_id

UUID

FK → quests

activity_type

TEXT

CHECK (activity_type IN ('activity', 'quiz'))

grading_settings

JSONB

created_at

TIMESTAMPTZ

updated_at

TIMESTAMPTZ

questions table (verified columns)

Column

Type

Notes

id

UUID

PK

activity_id

UUID

FK → activities

type

TEXT

CHECK (type IN ('multiple-choice', 'checkbox', 'true-false', 'identification', 'reflection', 'essay', 'image-map')) — 7 types total

content

JSONB

created_at

TIMESTAMPTZ

Known Schema Mismatch

Layer

Allowed Types

DB CHECK constraint

7 types: multiple-choice, checkbox, true-false, identification, reflection, essay, image-map

Zod activitySchema

5 types: multiple-choice, checkbox, true-false, identification, image-map

reflection and essay types exist in the database but are NOT supported by the Zod validation layer. This is a DB/Zod mismatch that may cause runtime errors if those types are used.

Migration

  • Skills columns added via migration 202602090001

9. API ENDPOINTS

Method

Path

Auth

Purpose

POST

/api/creator/create-activity

Clerk

Create activity with questions

GET

/api/creator/list-activities

Clerk

List activities for a quest

GET

/api/creator/get-activity

Clerk

Get single activity by ID

PATCH

/api/creator/update-activity

Clerk

Update activity and questions

DELETE

/api/creator/delete-activity

Clerk

Delete activity and cascade questions

10. DATA REQUIREMENTS

Frontend Needs

  • Skills array for tag input component (multi-select chip input)

  • useSkillsFilter hook (hooks/creator/useSkillsFilter.ts, 106 lines)

  • Question Hub page with activity/question management UI

API Calls

  • Activity CRUD: 5 endpoints covering full lifecycle

  • Skills filtering via Supabase array contains queries

Caching

  • Skills filter results: Client-side via hook

  • Activity list: Refetched on mutations

11. PERFORMANCE CONSIDERATIONS

DB Optimization

  • GIN indexes on quests.skills and adventures.skills for efficient array contains queries

  • JSONB grading_settings allows flexible schema without migrations

Response Time

  • GIN-indexed skills queries: Sub-100ms for typical creator library size

  • Activity CRUD: Standard query performance

12. SECURITY & AUTHORIZATION

Access Control

  • All activity/question endpoints require Clerk authentication

  • RLS ensures creators only access their own content

Auth Logic

  • activities.creator_id is FK → app_users (UUID)

  • Standard Clerk-to-Supabase ID mapping applies

Validation

  • Question types validated via Zod activitySchema (5 types)

  • Input sanitization on question content (JSONB)

13. ERROR HANDLING

Error

Response

Unsupported question type (reflection/essay)

Zod validation error — blocked at API layer before DB insert

Missing required question fields

Zod validation error with field details

Activity not found

404

Unauthorized access

403 (RLS-enforced)

Empty skills filter

Returns all content (no filter applied)

14. TESTING CHECKLIST

Happy Path

  • Add skills to quest during creation

  • Add skills to adventure during creation

  • Filter content library by skills using useSkillsFilter

  • Create activity with multiple-choice question

  • Create activity with checkbox question

  • Create activity with true-false question

  • Create activity with identification question

  • Create activity with image-map question

  • Update activity grading_settings

  • Delete activity (cascade deletes questions)

Edge Cases

  • Quest with empty skills array

  • Filter with skills that match no content

  • Activity with mixed question types

  • Attempt to create 'reflection' type question (should fail Zod validation)

  • Attempt to create 'essay' type question (should fail Zod validation)

  • Delete quest → verify activity_submissions cascade (migration 20260603)

15. OPEN QUESTIONS

  • Should Zod activitySchema be updated to include 'reflection' and 'essay' question types to match the DB CHECK constraint?

  • Is the 5-type limitation in Zod intentional (MVP scope) or a bug?

16. OUT OF SCOPE

  • Skill taxonomy/ontology management

  • Auto-suggestion of skills based on content

  • Skill progression tracking across quests

17. SUCCESS METRICS

  • Average number of skills tagged per quest/adventure

  • Skills filter usage rate

  • Question Hub activity creation volume

  • Question type distribution across created content

18. DEPENDENCIES

  • Quest/Adventure creation flow (for skills input)

  • activities table and FK relationships

  • questions table with CHECK constraint

  • Migration 202602090001 (skills columns + GIN indexes)

  • Migration 20260603 (cascade delete fix)

19. TIMELINE

Completed — Feature is fully implemented in Sprint #12.

Document Version

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

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

Written by Sean Patrick Last updated June 29, 2026
Was this article helpful?