Grio AI Platform: Database Schema & Data Model

Version: 1.0 Last Updated: 2026-03-24 Status: Internal Technical Documentation

1. Data Model Overview

The Grio AI platform uses a normalized relational model that mirrors the curriculum hierarchy, enabling multi-curriculum support (Uganda NCDC, Kenya CBC, Zambia ZEC, etc.) while maintaining flexible content scaling. The design separates structural entities (Curriculum → Class → Subject) from content entities (Topic → Lesson → LessonContent) and decorrelates user/session data for analytics.

Key Principles

Curriculum-agnostic structure: Curricula differ only in class names, subjects, and topic sequences
Many-to-many relationships: Classes offer multiple subjects; subjects appear in multiple classes
Temporal hierarchy: Terms and Topics provide semester-level organization
AI integration: Context rules and system prompts are parameterized by subject, class, and topic
Multi-tenancy: Row-level security isolates schools and their students

Entity Relationship Diagram (ASCII)

┌─────────────────┐
│   Curriculum    │
│  (e.g., Uganda  │
│     NCDC)       │
└────────┬────────┘
         │ 1
         │ curriculum_id FK
         │
    ┌────▼────────┐          ┌──────────────┐
    │   School    │────────┤ SchoolEnroll │
    └─────────────┘ 1..N    │ (student→    │
                            │  school)     │
                            └──────────────┘
         │ curriculum_id FK
         │
    ┌────▼────────┐         ┌────────────────────┐
    │   Class     │◄────────┤  ClassSubject      │
    │  (e.g.,     │  1 (M:N)│  (junction table)  │
    │  Senior 1)  │ class_id│                    │
    └─────────────┘         └────────┬───────────┘
                                     │ subject_id FK
                            ┌────────▼──────────┐
                            │    Subject       │
                            │  (e.g., Math,   │
                            │   English)       │
                            └─────────────────┘
                                     │ subject_id FK
                            ┌────────▼──────────────────┐
                            │  AI_Context_Rules         │
                            │  (system prompts, conds)  │
                            └───────────────────────────┘

    ┌─────────────┐
    │    Term     │
    │  (e.g.,     │
    │   Term 1)   │
    └────────┬────┘
             │ term_id FK
             │ 1
    ┌────────▼─────────────┐
    │     Topic            │
    │ (e.g., Number        │
    │  Bases, order_index) │
    └────────┬─────────────┘
             │ topic_id FK
             │ 1
    ┌────────▼──────────────────┐
    │    Lesson                 │
    │  (Subtopic, e.g.,        │
    │   "Converting to Binary") │
    └────────┬──────────────────┘
             │ lesson_id FK
             │ 1
    ┌────────▼──────────────────┐
    │   LessonContent           │
    │  (text, video, quiz, etc) │
    └───────────────────────────┘

    ┌─────────────────────┐
    │     ClassSession    │  (Links class, subject, topic, lesson, teacher)
    │ (session context)   │
    └────────┬────────────┘
             │ session_id FK
             │ 1..N
    ┌────────▼────────────────────┐
    │   StudentProgress           │  (Topic completion, scores)
    │                             │
    └─────────────────────────────┘

             ┌─────────────────────┐
             │  LearningHistory    │  (Interactions, state for continuity)
             └─────────────────────┘

2. Core Curriculum Tables

Curriculum

Defines regional education standards (Uganda NCDC, Kenya CBC, Zambia ZEC).

Field	Type	Constraints	Notes
curriculum_id	UUID	PK	Generated UUID
name	VARCHAR(255)	NOT NULL, UNIQUE	“Uganda NCDC 2022”, “Kenya CBC”
country	VARCHAR(100)	NOT NULL	“Uganda”, “Kenya”, “Zambia”
description	TEXT	NULL	Curriculum overview, context
version	VARCHAR(50)	NULL	“2022”, “2023”
created_at	TIMESTAMP	DEFAULT NOW()	Audit trail

Example Data:

curriculum_id: 550e8400-e29b-41d4-a716-446655440000
name: Uganda NCDC 2022
country: Uganda
version: 2022

Class

Represents student cohorts (Senior 1, Primary 5, etc.).

Field	Type	Constraints	Notes
class_id	UUID	PK	Generated UUID
curriculum_id	UUID	FK (Curriculum.curriculum_id)	NOT NULL
name	VARCHAR(255)	NOT NULL	“Senior 1”, “Primary 5”
grade_level	INT	NOT NULL	1–13 (Uganda), 1–12 (Kenya)
description	TEXT	NULL	Class context, student age range
created_at	TIMESTAMP	DEFAULT NOW()	Audit trail

Example Data:

class_id: 660e8400-e29b-41d4-a716-446655440001
curriculum_id: 550e8400-e29b-41d4-a716-446655440000
name: Senior 1
grade_level: 10

Subject

Academic subjects taught across classes.

Field	Type	Constraints	Notes
subject_id	UUID	PK	Generated UUID
name	VARCHAR(255)	NOT NULL, UNIQUE	“Mathematics”, “English”, “Physics”
description	TEXT	NULL	Subject scope, prerequisites
created_at	TIMESTAMP	DEFAULT NOW()	Audit trail

Example Data:

subject_id: 770e8400-e29b-41d4-a716-446655440002
name: Mathematics
description: Core subject covering numeracy, algebra, geometry, and data analysis

ClassSubject (M:N Junction Table)

Maps classes to subjects; indicates whether subject is compulsory.

Field	Type	Constraints	Notes
class_subject_id	UUID	PK	Generated UUID
class_id	UUID	FK (Class.class_id)	NOT NULL
subject_id	UUID	FK (Subject.subject_id)	NOT NULL
is_compulsory	BOOLEAN	DEFAULT TRUE	Compulsory or elective
order_index	INT	NULL	Display order in class view
created_at	TIMESTAMP	DEFAULT NOW()	Audit trail

Unique Index: (class_id, subject_id)

Example Data:

class_id: 660e8400-e29b-41d4-a716-446655440001
subject_id: 770e8400-e29b-41d4-a716-446655440002
is_compulsory: true
order_index: 1

Term

Semester or term divisions within an academic year.

Field	Type	Constraints	Notes
term_id	UUID	PK	Generated UUID
curriculum_id	UUID	FK (Curriculum.curriculum_id)	NOT NULL
name	VARCHAR(100)	NOT NULL	“Term 1”, “Semester 1”
order_index	INT	NOT NULL	1, 2, 3 (ordering within year)
start_date	DATE	NULL	Academic calendar
end_date	DATE	NULL	Academic calendar
created_at	TIMESTAMP	DEFAULT NOW()	Audit trail

Example Data:

term_id: 880e8400-e29b-41d4-a716-446655440003
curriculum_id: 550e8400-e29b-41d4-a716-446655440000
name: Term 1
order_index: 1
start_date: 2026-01-12
end_date: 2026-04-10

Topic

Learning units within a class-subject term (e.g., “Number Bases” in Senior 1 Math).

Field	Type	Constraints	Notes
topic_id	UUID	PK	Generated UUID
class_subject_id	UUID	FK (ClassSubject.class_subject_id)	NOT NULL
term_id	UUID	FK (Term.term_id)	NOT NULL
title	VARCHAR(255)	NOT NULL	“Number Bases”, “Quadratic Equations”
description	TEXT	NOT NULL	Learning outcomes, scope
order_index	INT	NOT NULL	Sequence within term
estimated_hours	DECIMAL(5,2)	NULL	Curriculum time allocation
created_at	TIMESTAMP	DEFAULT NOW()	Audit trail

Example Data:

topic_id: 990e8400-e29b-41d4-a716-446655440004
class_subject_id: [ClassSubject Senior 1 Math]
term_id: 880e8400-e29b-41d4-a716-446655440003
title: Number Bases
order_index: 2
estimated_hours: 8.0

Lesson (Subtopic)

Individual lessons within a topic with learning objectives and content mapping.

Field	Type	Constraints	Notes
lesson_id	UUID	PK	Generated UUID
topic_id	UUID	FK (Topic.topic_id)	NOT NULL
title	VARCHAR(255)	NOT NULL	“Converting Decimal to Binary”
learning_objective	TEXT	NOT NULL	Clear, measurable outcomes (Bloom’s)
difficulty_level	VARCHAR(50)	DEFAULT ‘intermediate’	‘beginner’, ‘intermediate’, ‘advanced’
estimated_duration_mins	INT	NULL	Typical lesson duration
order_index	INT	NOT NULL	Sequence within topic
created_at	TIMESTAMP	DEFAULT NOW()	Audit trail

Example Data:

lesson_id: aa0e8400-e29b-41d4-a716-446655440005
topic_id: 990e8400-e29b-41d4-a716-446655440004
title: Converting Decimal to Binary
learning_objective: Students will convert decimal numbers to binary (base 2) using division method
difficulty_level: intermediate
estimated_duration_mins: 45
order_index: 1

LessonContent

Content assets and resources for lessons (text, videos, quizzes, examples).

Field	Type	Constraints	Notes
content_id	UUID	PK	Generated UUID
lesson_id	UUID	FK (Lesson.lesson_id)	NOT NULL
content_type	VARCHAR(50)	NOT NULL	‘text’, ‘video’, ‘quiz’, ‘example’, ‘image’, ‘pdf’
content_body	TEXT	NULL	Markdown text, formatted content
resource_url	VARCHAR(512)	NULL	URL to external resource (video, PDF)
ai_prompt	TEXT	NULL	Pre-crafted prompt for AI context generation
order_index	INT	NOT NULL	Sequence within lesson
created_at	TIMESTAMP	DEFAULT NOW()	Audit trail

Example Data:

content_id: bb0e8400-e29b-41d4-a716-446655440006
lesson_id: aa0e8400-e29b-41d4-a716-446655440005
content_type: text
content_body: "To convert 45 (decimal) to binary: 45÷2=22 r1, 22÷2=11 r0, ..."
order_index: 1

content_id: bb0e8400-e29b-41d4-a716-446655440007
lesson_id: aa0e8400-e29b-41d4-a716-446655440005
content_type: video
resource_url: "https://storage.grio.ai/videos/decimal-to-binary-en.mp4"
order_index: 2

content_id: bb0e8400-e29b-41d4-a716-446655440008
lesson_id: aa0e8400-e29b-41d4-a716-446655440005
content_type: quiz
content_body: "[Quiz JSON: questions about decimal-to-binary conversion]"
order_index: 3

3. AI & Context Tables

AI_Context_Rules

Parameterized system prompts and constraints for AI responses, scoped by subject/class/topic.

Field	Type	Constraints	Notes
rule_id	UUID	PK	Generated UUID
subject_id	UUID	FK (Subject.subject_id)	NULL (if global)
class_id	UUID	FK (Class.class_id)	NULL (if subject-wide)
topic_id	UUID	FK (Topic.topic_id)	NULL (if class-wide)
system_prompt	TEXT	NOT NULL	E.g., “You are teaching Senior 1 Math…”
constraints	JSONB	NULL	{“max_tokens”: 500, “tone”: “encouraging”}
knowledge_sources	JSONB	NULL	{“include”: [“curriculum_docs”], “exclude”: [“external_web”]}
created_at	TIMESTAMP	DEFAULT NOW()	Audit trail

Hierarchy: Topic rules override Class rules; Class rules override Subject rules; Subject rules override global.

Example Data:

rule_id: cc0e8400-e29b-41d4-a716-446655440009
subject_id: 770e8400-e29b-41d4-a716-446655440002  (Mathematics)
class_id: 660e8400-e29b-41d4-a716-446655440001    (Senior 1)
topic_id: 990e8400-e29b-41d4-a716-446655440004    (Number Bases)
system_prompt: "You are a patient math tutor teaching Senior 1 students about number systems..."
constraints: {"max_tokens": 800, "difficulty_adjust": "intermediate"}

SubjectPaper (Referenced in Legacy Docs)

Optional record linking subjects to official curriculum documents or exam papers.

Field	Type	Constraints	Notes
paper_id	UUID	PK	Generated UUID
subject_id	UUID	FK (Subject.subject_id)	NOT NULL
curriculum_id	UUID	FK (Curriculum.curriculum_id)	NOT NULL
paper_type	VARCHAR(100)	NULL	‘curriculum_guide’, ‘exam_paper’, ‘sample_questions’
document_url	VARCHAR(512)	NULL	Link to PDF or document
academic_year	VARCHAR(10)	NULL	“2023”, “2024”
created_at	TIMESTAMP	DEFAULT NOW()	Audit trail

4. Session & Progress Tables

ClassSession

Represents teaching/learning sessions, linking teacher, class, subject, and lesson.

Field	Type	Constraints	Notes
session_id	UUID	PK	Generated UUID
class_id	UUID	FK (Class.class_id)	NOT NULL
subject_id	UUID	FK (Subject.subject_id)	NOT NULL
topic_id	UUID	FK (Topic.topic_id)	NULL (if topic not selected yet)
lesson_id	UUID	FK (Lesson.lesson_id)	NULL (if lesson not selected yet)
teacher_id	UUID	FK (Users.user_id)	NULL (for home learning, NULL)
start_time	TIMESTAMP	NOT NULL	Session start
end_time	TIMESTAMP	NULL	Session end (NULL if ongoing)
status	VARCHAR(50)	DEFAULT ‘active’	‘active’, ‘completed’, ‘paused’
notes	TEXT	NULL	Teacher notes, session summary
created_at	TIMESTAMP	DEFAULT NOW()	Audit trail

Example Data:

session_id: dd0e8400-e29b-41d4-a716-446655440010
class_id: 660e8400-e29b-41d4-a716-446655440001
subject_id: 770e8400-e29b-41d4-a716-446655440002
topic_id: 990e8400-e29b-41d4-a716-446655440004
lesson_id: aa0e8400-e29b-41d4-a716-446655440005
teacher_id: ee0e8400-e29b-41d4-a716-446655440011
start_time: 2026-03-24 09:00:00
status: active

StudentProgress

Tracks student completion and performance per topic.

Field	Type	Constraints	Notes
progress_id	UUID	PK	Generated UUID
student_id	UUID	FK (Users.user_id)	NOT NULL
topic_id	UUID	FK (Topic.topic_id)	NOT NULL
completion_percentage	INT	0–100	% lessons completed in topic
average_score	DECIMAL(5,2)	0.00–100.00	Average quiz/assessment score
status	VARCHAR(50)	‘not_started’, ‘in_progress’, ‘completed’	Progress state
last_accessed_at	TIMESTAMP	NULL	Last activity timestamp
created_at	TIMESTAMP	DEFAULT NOW()	Audit trail
updated_at	TIMESTAMP	DEFAULT NOW()	Updated on progress change

Unique Index: (student_id, topic_id)

LearningHistory

Detailed interaction log for analytics and “Continue Learning” feature (Grio Continuity).

Field	Type	Constraints	Notes
history_id	UUID	PK	Generated UUID
student_id	UUID	FK (Users.user_id)	NOT NULL
session_id	UUID	FK (ClassSession.session_id)	NOT NULL
lesson_id	UUID	FK (Lesson.lesson_id)	NOT NULL
interaction_type	VARCHAR(50)	NOT NULL	‘view’, ‘quiz_attempt’, ‘ask_question’, ‘complete’
duration_seconds	INT	NULL	Time spent on lesson
quiz_score	DECIMAL(5,2)	NULL	Score if interaction is quiz
ai_interaction_log	JSONB	NULL	{“prompt”: “…”, “response”: “…”, “timestamp”: “…”}
timestamp	TIMESTAMP	NOT NULL	When interaction occurred

5. User & Access Tables

Users

Platform users across all roles and schools.

Field	Type	Constraints	Notes
user_id	UUID	PK	Generated UUID
email	VARCHAR(255)	NOT NULL, UNIQUE	Unique login identifier
name	VARCHAR(255)	NOT NULL	Full name
role	VARCHAR(50)	NOT NULL	‘student’, ‘teacher’, ‘school_admin’, ‘super_admin’, ‘independent_learner’
phone_number	VARCHAR(20)	NULL	Optional contact
password_hash	VARCHAR(512)	NOT NULL	bcrypt or similar
is_active	BOOLEAN	DEFAULT TRUE	Soft-delete flag
created_at	TIMESTAMP	DEFAULT NOW()	Audit trail
updated_at	TIMESTAMP	DEFAULT NOW()	Profile updates

Schools

Educational institutions using the Grio platform.

Field	Type	Constraints	Notes
school_id	UUID	PK	Generated UUID
name	VARCHAR(255)	NOT NULL	School name
curriculum_id	UUID	FK (Curriculum.curriculum_id)	NOT NULL
location	VARCHAR(255)	NULL	City/region, used for analytics
country	VARCHAR(100)	NULL	E.g., “Uganda”, “Kenya”
admin_user_id	UUID	FK (Users.user_id)	NULL (principal/admin)
is_active	BOOLEAN	DEFAULT TRUE	Soft-delete flag
created_at	TIMESTAMP	DEFAULT NOW()	Audit trail

SchoolEnrollment

Links students to schools; enables multi-tenancy and row-level security.

Field	Type	Constraints	Notes
enrollment_id	UUID	PK	Generated UUID
student_id	UUID	FK (Users.user_id)	NOT NULL
school_id	UUID	FK (Schools.school_id)	NOT NULL
enrollment_date	DATE	NOT NULL	When student joined school
status	VARCHAR(50)	DEFAULT ‘active’	‘active’, ‘graduated’, ‘transferred’
class_id	UUID	FK (Class.class_id)	NULL (may change)
created_at	TIMESTAMP	DEFAULT NOW()	Audit trail

Unique Index: (student_id, school_id)

Multi-Tenancy & RLS: - Query filter: WHERE school_id IN (user_school_ids) ensures data isolation - Session context: User school IDs passed in JWT token for efficient filtering - Teachers see only their school’s students and classes

6. Analytics Tables

SchoolAnalytics (Aggregated)

Daily/weekly aggregations of student engagement and performance by school.

Field	Type	Constraints	Notes
metric_id	UUID	PK	Generated UUID
school_id	UUID	FK (Schools.school_id)	NOT NULL
date	DATE	NOT NULL	Metric date
total_active_students	INT	NULL	Count of active learners
lessons_completed	INT	NULL	Topics completed in date window
average_quiz_score	DECIMAL(5,2)	NULL	School-wide average
engagement_rate	DECIMAL(5,2)	NULL	% students active in period

NationalAnalytics (Aggregated)

Country-level metrics for curriculum oversight.

Field	Type	Constraints	Notes
metric_id	UUID	PK	Generated UUID
curriculum_id	UUID	FK (Curriculum.curriculum_id)	NOT NULL
date	DATE	NOT NULL	Metric date
total_schools	INT	NULL	Schools using curriculum
total_students	INT	NULL	Students across all schools
average_topic_completion	DECIMAL(5,2)	NULL	% completion per topic

7. Data Flow Examples

Example 1: Teacher Starts Senior 1 Math, Number Bases Lesson

Flow: 1. Teacher navigates to Class (Senior 1) and selects Subject (Mathematics) - Query: ClassSubject WHERE class_id = 'Senior 1' AND subject_id = 'Math'

System displays Terms for curriculum
- Query: Term WHERE curriculum_id = 'Uganda NCDC' ORDER BY order_index
Teacher selects Term 1, sees Topics
- Query: Topic WHERE class_subject_id = <S1-Math> AND term_id = 'Term 1' ORDER BY order_index
Teacher picks Number Bases topic, sees Lessons
- Query: Lesson WHERE topic_id = 'Number Bases' ORDER BY order_index
Teacher clicks “Converting Decimal to Binary” lesson
- Query: LessonContent WHERE lesson_id = 'Decimal-to-Binary' ORDER BY order_index
System creates ClassSession
- INSERT INTO ClassSession (class_id, subject_id, topic_id, lesson_id, teacher_id, start_time, status)
AI system fetches AI_Context_Rules matching (Topic, Class, Subject)
- Hierarchy: Topic rule > Class rule > Subject rule > Global rule
- Constructs system prompt for “Senior 1 Math, Number Bases” context

Example 2: Student Continues Learning at Home (Grio Continuity)

Flow: 1. Student (independent_learner or school student outside class hours) opens Grio app 2. System queries LearningHistory for recent sessions - SELECT DISTINCT lesson_id FROM LearningHistory WHERE student_id = ? ORDER BY timestamp DESC LIMIT 5

System displays “Continue Learning” — last lesson accessed
Student clicks “Continue” → redirects to that Lesson and LessonContent
Student progresses through quiz, AI interaction logged to LearningHistory with interaction_type = 'quiz_attempt'
System updates StudentProgress
- Recalculates completion_percentage and average_score for the Topic

8. Multi-Curriculum Design

Supporting Uganda NCDC, Zambia ZEC, Kenya CBC

The schema decouples structural hierarchy from curriculum details:

Curriculum table: Stores curriculum_id, name, country, version
- One row per curriculum standard per country/version
Class table: curriculum_id FK — different countries have different class names
- Uganda: “Senior 1, 2, 3, 4” (grades 11–14)
- Kenya: “Form 1, 2, 3, 4” (grades 9–12)
- Zambia: “Grade 10, 11, 12” (grades 10–12)
Subject, Term, Topic, Lesson: Tied to Class (via ClassSubject) and Curriculum (via Term.curriculum_id)
- Same subject (e.g., “Mathematics”) can have different topics, lessons, content by curriculum
- No structural schema changes needed
AI_Context_Rules: Scope by subject_id, class_id, topic_id — allows curriculum-specific prompts
- E.g., “Senior 1 Math” prompt differs from “Form 1 Math”
Multi-tenancy via Schools: Each school picks a curriculum; students filtered by school enrollment
- Uganda schools → Uganda NCDC; Kenya schools → Kenya CBC

Query Pattern:

-- Fetch curriculum for a school
SELECT c.* FROM Curriculum c
JOIN Schools s ON s.curriculum_id = c.curriculum_id
WHERE s.school_id = ?

-- Fetch classes in curriculum
SELECT * FROM Class WHERE curriculum_id = ? ORDER BY grade_level

-- Fetch subjects offered to a class
SELECT s.* FROM Subject s
JOIN ClassSubject cs ON cs.subject_id = s.subject_id
WHERE cs.class_id = ? ORDER BY cs.order_index

9. Indexing & Performance Notes

Recommended Indexes

Table	Index	Rationale
ClassSubject	(class_id, subject_id)	Enforce uniqueness; common query
StudentProgress	(student_id, topic_id)	Enforce uniqueness; query by student
StudentProgress	(topic_id)	Analytics — completion by topic
LearningHistory	(student_id, timestamp DESC)	Recent interactions, continuity
LearningHistory	(session_id)	Fetch history for a session
ClassSession	(teacher_id, start_time DESC)	Teacher’s session timeline
ClassSession	(class_id, status)	Active sessions per class
SchoolEnrollment	(school_id, status)	Students in school, by status
SchoolEnrollment	(student_id, status)	Student’s enrollment
LessonContent	(lesson_id, order_index)	Fetch content in sequence
Topic	(class_subject_id, term_id, order_index)	Topics per term per class-subject
Users	(email)	Login lookups
Schools	(curriculum_id)	Schools per curriculum

Caching Strategy

Curriculum hierarchy (Curriculum → Class → ClassSubject → Subject): Cache at application startup
- TTL: 24 hours or on-demand refresh
- Small size, high read volume
AI_Context_Rules: Cache by (subject_id, class_id, topic_id) tuple
- TTL: 6 hours
- Invalidate on rule update
StudentProgress aggregations: Redis cache per student
- TTL: 1 hour (recalculated on quiz/topic completion)
LearningHistory: No caching (audit-sensitive); indexed for speed

10. Data Integrity & Constraints

Foreign Key Integrity

ClassSubject.class_id → Class.class_id (CASCADE on delete: dangling topics removed)
Topic.class_subject_id → ClassSubject.class_subject_id (CASCADE)
StudentProgress.topic_id → Topic.topic_id (CASCADE)
LearningHistory.student_id → Users.user_id (RESTRICT — cannot delete user with learning history)

Business Logic Constraints

Class grade_level must match curriculum conventions (e.g., 1–4 for Uganda Secondary)
StudentProgress.completion_percentage = (lessons_completed / total_lessons_in_topic) × 100
StudentProgress.average_score = AVG(quiz_scores) for topic’s lessons
LearningHistory.duration_seconds auto-calculated from lesson view time
SchoolEnrollment.class_id must reference a class in the school’s curriculum

End of Database Schema Document

Last reviewed: 2026-03-24