EPIC-01.1: Lesson Status & Authorship Schema

[Peleke]

Epic: EPIC-01 Database Foundation
Story Points: 5
Priority: P0

User Story

As a content author, I want lesson draft/publish status tracking, so that I can work on lessons privately before publishing.

Acceptance Criteria

• lessons table has status column (draft|published|archived)
• lessons table has author_id column (FK to auth.users)
• lessons.overview is nullable (draft-first approach)
• Indexes on status and author_id for performance
• updated_at trigger updates timestamp on changes

Technical Tasks

File: supabase/migrations/YYYYMMDD_lesson_authoring_foundation.sql

• Add status column with CHECK constraint
• Add author_id column with FK
• Make overview nullable
• Create indexes (status, author_id)
• Add updated_at trigger

Tests

• Draft lesson can be created with minimal data
• Status transitions work (draft → published → archived)
• Author_id correctly references auth.users

Related Docs

• EPIC-01 Details
• Implementation Spec

[Peleke]

✅ Migration created: supabase/migrations/20251106_lesson_authoring_foundation.sql

Added schema modifications:

• status column (draft|published|archived)
• author_id column (FK to auth.users)
• language column (es|is) for multi-language support
• Made overview nullable (draft-first approach)
• Created indexes for performance
• Added updated_at trigger

Next: Test migration locally before applying RLS policies.

[Peleke]

✅ Story Complete

Migration Applied: 20251106_lesson_authoring_foundation.sql

Changes:

• Added status column (draft|published|archived)
• Added author_id column (FK to auth.users)
• Added language column (es|is)
• Made overview nullable for draft-first workflow
• Created indexes for performance
• Added updated_at trigger

Tests:

• Schema verification passed
• Rollback migration created: 20251106_lesson_authoring_rollback.sql

Files:

• Migration: supabase/migrations/20251106_lesson_authoring_foundation.sql
• Rollback: supabase/migrations/20251106_lesson_authoring_rollback.sql

[Peleke]

Story verified complete. All acceptance criteria met.

[Peleke]

Reopening - keeping open until deployment/merge completes