[v2] Database Schema: Learning Goals and Skill Trees

[nicholaspsmith]

Overview

Create new database tables for the goal-based learning system.

Part of

Memoryloop v2 Pivot - Branch 014-goal-based-learning

New Tables

learning_goals

• id (uuid, primary key)
• userId (foreign key to users)
• title (varchar)
• description (text, optional)
• status (enum: active, paused, completed, archived)
• createdAt, updatedAt

skill_trees

• id (uuid, primary key)
• goalId (foreign key to learning_goals)
• type (enum: ai_generated, curated)
• curatedSourceId (optional, for future curated trees)
• createdAt

skill_nodes

• id (uuid, primary key)
• treeId (foreign key to skill_trees)
• parentId (self-referential, nullable for root nodes)
• title (varchar)
• depth (integer: 0=root, 1=category, 2=topic, 3=subtopic)
• isSelected (boolean, default true)
• masteryScore (integer 0-100, calculated)
• createdAt

Schema Modifications

• Add skillNodeId to flashcards table (nullable foreign key)
• Add goalId to decks table (nullable foreign key)

Acceptance Criteria

• Drizzle schema updated with new tables
• Migration generated and tested
• Foreign key relationships properly defined
• Indexes on frequently queried columns (userId, goalId, treeId)

Related Spec

See specs/014-goal-based-learning/spec.md for full requirements