Course & Curriculum Hierarchical Data Schema
Course & Curriculum Hierarchical Data Schema
Version 1.0.3 introduces the foundational database schema that models the full structure of a course — from the top-level course record down to individual lesson attachments. This schema is the single source of truth consumed by the course builder, the Teachable import engine, and the learner-facing experience.
Hierarchy Overview
Course content is modelled as a strict four-level nested hierarchy:
Course
└── Section (ordered)
└── Lesson (ordered)
└── Attachment
Each level is a distinct entity in the database with its own record, relationships, and ordering metadata.
Entities
Course
The top-level entity. A course belongs to a single organization (tenant) and acts as the root of the content tree.
| Field | Description |
|---|---|
id | Unique identifier |
title | Display name of the course |
organization_id | Tenant scoping — links the course to its owning organization |
Section
Sections represent ordered groupings within a course — equivalent to modules or chapters. A course contains one or more sections.
| Field | Description |
|---|---|
id | Unique identifier |
course_id | Parent course reference |
title | Display name of the section |
position | Integer used to order sections within their course |
Lesson
Lessons are the primary content units. Each lesson is nested inside a section and carries the actual learning content.
| Field | Description |
|---|---|
id | Unique identifier |
section_id | Parent section reference |
title | Display name of the lesson |
content | Rich text / HTML body of the lesson |
video_url | Optional embedded video link (e.g. from Wistia, YouTube, or Vimeo) |
position | Integer used to order lessons within their section |
Attachment
Attachments are document or asset records linked to a specific lesson. They support file references such as PDFs, images, and downloadable resources.
| Field | Description |
|---|---|
id | Unique identifier |
lesson_id | Parent lesson reference |
filename | Original file name |
url | Stored or proxied URL of the asset |
Ordering
Sections and lessons both carry an explicit position field. This ensures deterministic ordering across all consumers — the course builder renders sections and lessons in position order, the import engine writes position values when reconstructing a Teachable curriculum, and the learner experience navigates lessons sequentially using the same field.
Position values are 1-indexed integers. When inserting a new section or lesson, the platform assigns the next available position within the parent scope.
Multi-Tenant Scoping
All course data is scoped to an organization. The organization_id on the Course entity propagates implicitly through the hierarchy — sections, lessons, and attachments inherit their tenant context through their parent course. No cross-tenant data access is possible through normal query paths.
Consumers
Three platform subsystems read from and write to this schema:
Course Builder
The course builder uses the full hierarchy to power the authoring interface. Authors create and reorder sections, add lessons with rich text and video content, and attach documents — all of which are persisted using this schema.
Import Engine
When importing from a Teachable school, the scraper extracts the course structure and maps it directly onto this schema. Each Teachable section becomes a Section record, each lecture becomes a Lesson record (with text content, video URL, and images preserved), and downloadable files become Attachment records.
Learner Experience
The learner-facing UI reads course, section, and lesson data to render the curriculum sidebar, track progress, and display lesson content. The position fields drive the sequential navigation between lessons.
Migration Notes
- This is an additive migration introduced in v1.0.3. No existing tables or columns were modified.
- Existing platform data is unaffected.
- Downstream services that reference course content should use this schema as the authoritative source rather than any intermediate representations.