Content Schema Reference

Complete reference for content and course data structures.

Content Table Fields

All content (blog posts, tutorials, case studies) use these fields:

Core Fields

Field	Type	Required	Description
id	UUID	✅	Unique content identifier
title	String(300)	✅	Content title (max 100 chars display)
slug	String	✅	URL-friendly identifier (auto-generated)
description	Text	✅	Short description (300-500 chars)
content	JSON	✅	Tiptap JSON content (rich text)
excerpt	String	✅	First ~150 chars of content
featured_image_url	URL	✅	Hero/thumbnail image URL
status	ENUM	✅	draft | published | archived

Organization

Field	Type	Required	Description
category_id	UUID	✅	Foreign key to category table
author_id	UUID	✅	Content creator user ID
tags	Array	❌	Search tags for filtering
section	String	❌	Content section (homepage_meta, hero, pillar, etc)

Content Details

Field	Type	Required	Description
read_time	Integer	❌	Estimated read time (minutes)
word_count	Integer	❌	Total word count
language	String	❌	ISO language code (default: en)
featured	Boolean	❌	Show on homepage featured section
position	Integer	❌	Display order in lists

Engagement

Field	Type	Required	Description
view_count	Integer	❌	Total views (incremented per view)
like_count	Integer	❌	Total likes
bookmark_count	Integer	❌	Total bookmarks

Metadata

Field	Type	Description
seo_title	String(60)	SEO title (different from display title)
seo_description	String(160)	Meta description for search engines
seo_keywords	Array	Keywords for SEO
created_at	Timestamp	Auto-set creation date
updated_at	Timestamp	Auto-updated on changes
published_at	Timestamp	When content was published

Content Interactions

Tracks user engagement with content:

Field	Type	Description
id	UUID	Interaction ID
user_id	UUID	User who interacted
content_id	UUID	Content interacted with
interaction_type	ENUM	view | like | bookmark
created_at	Timestamp	When interaction occurred

Categories

Content organization:

Field	Type	Description
id	UUID	Category ID
name	String	Category name (e.g., "AI Agents", "Tutorials")
slug	String	URL-friendly category slug
description	String	Category description
color	String	Hex color code for UI
icon	String	Icon name or emoji
position	Integer	Display order

Courses & Tracks

Courses (Nova Tracks)

Field	Type	Required	Description
id	UUID	✅	Course ID
title	String	✅	Course title
slug	String	✅	URL slug (auto-generated)
description	String	✅	Course description
thumbnail_url	URL	✅	Course image
instructor_name	String	✅	Instructor name
level	ENUM	✅	beginner | intermediate | advanced
status	ENUM	✅	draft | coming_soon | published

Course Details

Field	Type	Required	Description
estimated_hours	Decimal	❌	Course duration estimate
pricing_tier	String	❌	free | pro | enterprise
course_code	String	❌	Internal course code
category	String	❌	Course category
position	Integer	❌	Display order in course list

Lessons

Field	Type	Required	Description
id	UUID	✅	Lesson ID
track_id	UUID	✅	Parent course ID
title	String	✅	Lesson title
content	JSON	✅	Lesson content (Tiptap JSON)
video_url	URL	❌	YouTube video URL
video_duration	Integer	❌	Video length (seconds)
order	Integer	✅	Display order within course
created_at	Timestamp	✅	Creation timestamp

Enrollments & Progress

Enrollments

Field	Type	Description
id	UUID	Enrollment ID
user_id	UUID	Enrolled user
track_id	UUID	Course enrolled in
enrolled_at	Timestamp	Enrollment date
status	ENUM	active | completed | paused

Lesson Progress

Field	Type	Description
id	UUID	Progress record ID
user_id	UUID	User's progress
lesson_id	UUID	Lesson being tracked
video_watch_time	Integer	Seconds watched
completed	Boolean	Lesson completed?
completed_at	Timestamp	Completion timestamp

JSON Data Structures

Content JSON (Tiptap)

Content is stored as Tiptap editor JSON format:

{
  "type": "doc",
  "content": [
    {
      "type": "paragraph",
      "content": [
        {
          "type": "text",
          "text": "Paragraph text here"
        }
      ]
    },
    {
      "type": "heading",
      "attrs": {
        "level": 2
      },
      "content": [
        {
          "type": "text",
          "text": "Heading text"
        }
      ]
    }
  ]
}

Content Metadata JSON

{
  "seo_title": "SEO title",
  "seo_description": "Meta description",
  "seo_keywords": ["keyword1", "keyword2"],
  "og_image": "https://...",
  "og_type": "article"
}

Validation Rules

Content Fields

{
  title: {
    required: true,
    maxLength: 300,
    minLength: 3,
    pattern: /^[a-zA-Z0-9\s\-.,'"!?:;()]+$/
  },
  description: {
    required: true,
    maxLength: 500,
    minLength: 50
  },
  slug: {
    required: true,
    pattern: /^[a-z0-9]+(?:-[a-z0-9]+)*$/,
    unique: true
  },
  status: {
    required: true,
    enum: ["draft", "published", "archived"]
  }
}

Course Fields

{
  title: {
    maxLength: 200,
    minLength: 5
  },
  estimated_hours: {
    type: "number",
    min: 0,
    max: 1000
  },
  level: {
    enum: ["beginner", "intermediate", "advanced"]
  }
}

Best Practices

For Content

1. Titles: Keep under 100 chars for display (use SEO title separately)
2. Descriptions: 300-500 chars, write for search snippets
3. Images: Use Cloudinary URLs with transformations
4. Content: Use Tiptap JSON, avoid raw HTML
5. Tags: Keep 3-5 relevant tags per piece

For Courses

1. Course Code: Use format like "AI-001", "WEB-202"
2. Slug: Auto-generate from title, but can customize
3. Thumbnail: Use 16:9 aspect ratio (1280x720)
4. Description: Include learning outcomes
5. Hours: Estimate conservatively

For Lessons

1. Video Duration: Parse from YouTube API
2. Content: Rich formatting using Tiptap
3. Order: Use sequential numbers (1, 2, 3...)
4. Watch Time: 90% watched = completed

Database Relationships

users
├── enrollments (many)
├── content (authored, many)
├── lesson_progress (many)
└── user_profiles

categories
└── content (many)

content
├── user (author)
├── category
├── content_interactions (many)
└── homepage_sections

tracks (courses)
├── lessons (many)
├── enrollments (many)
└── certificates (many)

lessons
├── track (parent course)
├── lesson_progress (many)
└── video_metadata

contentCategories
└── content (many)

Related Documentation

• Content Management Guide - How to create and edit content
• AI Generation Guide - Using AI to generate content
• Database Schema - Full database schema
• API Architecture - API endpoints for content

---

Last Updated: 2025-12-24