Courses — Technical Reference

This page covers the data models, video hosting integration, student authentication, and edge routing architecture behind the Courses & Builder and Student Portal modules.

Data Model

courses

Indexes:

• idx_courses_workspace on (workspace_id)
• idx_courses_preview_token on (preview_token) WHERE preview_token IS NOT NULL

course_modules

Indexes:

• idx_course_modules_order on (course_id, sequence_order)

course_lessons

Indexes:

• idx_course_lessons_order on (module_id, sequence_order)

course_lesson_blocks

Indexes:

• idx_course_lesson_blocks_order on (lesson_id, sequence_order)

course_enrollments

Constraints & Indexes:

• Unique constraint: course_enrollments_contact_course_unique on (contact_id, course_id)
• idx_course_enrollments_access_token on (access_token)
• idx_course_enrollments_workspace_contact on (workspace_id, contact_id)
• idx_course_enrollments_active_count on (course_id) WHERE revoked_at IS NULL
• course_enrollments_completed_at_idx on (completed_at)

course_progress

Constraints & Indexes:

• Unique constraint: course_progress_enrollment_lesson_unique on (enrollment_id, lesson_id)
• idx_course_progress_lookup on (enrollment_id, lesson_id)

Workspace Column Extensions

Architecture

Video Storage Integration (Bunny.net Stream)

The Courses module uses a tenant-isolated storage integration powered by Bunny.net Stream.

1. API Credentials Isolation:

   • Platform Key: The server uses process.env.BUNNY_API_KEY for account-level actions (e.g., creating and deleting workspace video libraries).
   • Workspace Credentials: Stored dynamically within settings.integrations.bunny on the workspaces table, including library_id, api_key (write/delete), read_only_api_key (signature verification), and token_auth_key (signed streaming).

2. Upload Handshake & TUS Protocol:

   • Direct file uploads from the browser use tus-js-client to offload traffic from the application server.
   • The upload parameters request generates a dynamic signature using: Signature = SHA256(libraryId + apiKey + expiresTimestamp + videoId)
   • This temporary signature is passed as a header to authorize the TUS upload directly to Bunny's servers.

3. Quota and Caching Mechanics:

   • Quota Resolution Priority:
     1. Workspace settings override: workspaces.settings.courses.storage_quota_gb
     2. Platform default settings: default_course_quota_gb in platform_settings
     3. Hard fallback: 5 GB
   • Usage Tracking: Bunny's account-wide summaries have reporting delays. The server queries library usage in real-time by paging through the API and summing all video files' storageSize.
   • Caching Strategy: Real-time storage counts are cached server-side using Next.js unstable_cache with a 5-minute TTL under the tag library-usage-[libraryId].

4. Webhook & Post-Processing Guard:

   • The /api/courses/bunny-webhook route receives progress notifications.
   • The server verifies signatures via the x-bunnystream-signature header against a SHA256 hash of the request body generated with the library's read_only_api_key.
   • When status 3 (FINISHED) is received, the server checks the workspace's quota. If the quota is exceeded:
     • The video is immediately purged via deleteVideo.
     • The database lesson block is updated with an error payload (error: "Video removed: exceeded workspace storage quota").
     • An in-app alert is generated for the workspace admins.
     • The library usage cache tag is invalidated.

Security

Row-Level Security (RLS)

Student Authentication

1. Authentication Token:

   • Access tokens are generated as random UUID values and stored in course_enrollments.access_token.
   • When a student lands on /c/[courseId]?token=[accessToken], the server validates the token and sets a session cookie: course_access_[courseId].
   • Subsequent navigation to lessons resolves the token from this cookie.

2. Access Gating & Verification Rules:

   • Preview Mode: If a preview_token matches and is not expired (preview_token_expires_at > NOW()), all validation checks (publication status, draft blocks, legal consent) are bypassed.
   • Standard Gating: The validation pipeline verifies:
     • The enrollment has not expired (expires_at IS NULL or expires_at > NOW()).
     • Access has not been suspended (revoked_at IS NULL).
     • The course is published (is_published = true).
     • Legal Consent Gate: If the workspace has active Terms of Service or Privacy policies and the student has not yet acknowledged them (checked via contact_policy_acknowledgements), access is restricted. The server returns requiresConsent = true, instructing the frontend to mask lesson blocks and overlay a policy acceptance modal.

Routing

Vercel Edge Config Custom Domain Routing

Next.js Middleware intercepts incoming portal requests to support instant dynamic custom domains with minimal database latency.

1. Key Sanitization:
   • Vercel Edge Config does not support dots (.) in keys. Domain names are sanitized: edgeKey = hostname.replace(/\./g, "_") Example: academy.clientdomain.com maps to academy_clientdomain_com.
2. REST Item Lookup:
   • To avoid loading the entire config map into Edge memory, the middleware targets the exact item key endpoint directly:

     const url = connectionString.replace(/(\/ecfg_[a-zA-Z0-9]+)(\?|$)/, `$1/item/${edgeKey}$2`);
   • This achieves lookup times of under 15ms.
3. Rewrite & Redirect Logic:
   • Internal Rewriting: If routing matches a verified custom portal domain, incoming paths matching /c/[courseId] are rewritten internally to /learn/c/[courseId], leaving the custom hostname unchanged in the address bar.
   • Default Domain Redirection: If a user hits the default portal domain learn.gordoncrm.com/c/[courseId] but the course owner has verified a custom_portal_domain, the landing page initiates a 302 Temporary Redirect to the custom domain.
   • Admin Subdomain Isolation: Any path starting with /learn/c/... on the main app subdomain app.gordoncrm.com triggers a 301 Permanent Redirect to the default portal learn.gordoncrm.com, isolating admin cookies from student cookies.