Skip to content

DB Schema

Jump to bottom
Renuka edited this page Jul 2, 2025 · 36 revisions

  1. Public Schema: Tables & Columns

public.announcement

Stores announcements to be sent to users.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
title text The title of the announcement.
message text The main content of the announcement.
type text Type of announcement (e.g., 'info', 'alert').
image_url text URL for an associated image.
custom_icon text Custom icon identifier.
created_at timestamp with time zone NOT NULL, Default: now(). The timestamp when the announcement was created.
send_at timestamp with time zone The scheduled time to send the announcement.
target_ids ARRAY Array of user IDs or target groups.

public.assignment

Stores assignments given to students, linking lessons to classes or individuals.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
created_by uuid Foreign Key to public.user(id). The user (teacher) who created the assignment.
starts_at timestamp with time zone NOT NULL, Default: now(). When the assignment becomes active.
ends_at timestamp with time zone When the assignment is due.
is_class_wise boolean NOT NULL, Default: false. True if assigned to a whole class.
class_id uuid NOT NULL, Foreign Key to public.class(id). The class this assignment belongs to.
school_id uuid NOT NULL, Foreign Key to public.school(id). The school this assignment belongs to.
lesson_id uuid NOT NULL, Foreign Key to public.lesson(id). The lesson content of the assignment.
type text Type of assignment (e.g., 'assignment', 'liveQuiz').
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
chapter_id uuid Foreign Key to public.chapter(id). The chapter associated with this assignment.
course_id uuid Foreign Key to public.course(id). The course associated with this assignment.
source text Source of the assignment creation (e.g., 'chatbot')..
firebase_id text ID from Firebase, if applicable.
is_firebase boolean Default: false. True if synced from Firebase.

public.assignment_cart

A temporary holding area for a user's selected lessons before they are formally assigned.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid(). Also a Foreign Key to public.user(id).
created_at timestamp with time zone NOT NULL, Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
lessons text A list of lesson IDs, likely in a serialized format (e.g., JSON array).
updated_at timestamp without time zone Default: now()
is_firebase boolean Default: false. True if synced from Firebase.

public.assignment_user

Junction table linking assignments to individual student users.

Column Name Data Type Description
assignment_id uuid NOT NULL, Foreign Key to public.assignment(id).
user_id uuid NOT NULL, Foreign Key to public.user(id) ('student id').
created_at timestamp with time zone Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
is_firebase boolean Default: false. True if synced from Firebase.

public.auto_chatbot

Stores automated chatbot task data for schools.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
school_id uuid NOT NULL, Foreign Key to public.school(id).
task_data jsonb The JSON data payload for the chatbot task.
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.

public.badge

Stores information about achievable badges for gamification.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
name character varying NOT NULL. The name of the badge.
image text URL of the badge image.
description text Description of how to earn the badge.
updated_at timestamp with time zone Default: now()
created_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.

public.chapter

Stores course chapters, which are collections of lessons.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
name text The name of the chapter.
image text URL of the chapter image.
course_id uuid Foreign Key to public.course(id). The course this chapter belongs to.
sort_index integer The order in which the chapter appears in a course.
sub_topics text Serialized list of sub-topics.
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.

public.chapter_lesson

Junction table linking lessons to chapters and defining their order.

Column Name Data Type Description
lesson_id uuid NOT NULL, Foreign Key to public.lesson(id).
chapter_id uuid NOT NULL, Foreign Key to public.chapter(id).
sort_index integer The order of the lesson within the chapter.
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()

public.chapter_links

Stores external links related to chapters, courses, and curricula.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
created_at timestamp with time zone NOT NULL, Default: now()
chapter_id uuid Foreign Key to public.chapter(id).
link text The URL of the external resource.
curriculum_id uuid Foreign Key to public.curriculum(id).
course_id uuid Foreign Key to public.course(id). Default: gen_random_uuid()
grade_id uuid Foreign Key to public.grade(id).
updated_at timestamp without time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.

public.chatbot

Stores general chatbot configuration and data.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
chatbot_doc jsonb The main JSON document containing payload or data.
phonenumber text NOT NULL. The phone number associated with the chatbot instance.

public.class

Stores information about classes within a school.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
name text NOT NULL. The name of the class (e.g., '1, 2A, 3B').
image text URL of the class image.
school_id uuid NOT NULL, Foreign Key to public.school(id).
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
firebase_id text ID from Firebase, if applicable.
is_firebase boolean Default: false. True if synced from Firebase.
status text Default: 'active'. Status of the class (e.g., 'active', 'migrated').
is_ops boolean Default: false. True if created by an operations user.
standard text The grade or standard of the class.
academic_year text The academic year for the class (e.g., '2023-2024').
ops_created_by uuid Foreign Key to public.user(id). The ops user who created the record.
group_id text WhatsApp group identifier.

public.class_course

Junction table linking courses to classes, indicating which courses are taught in a class.

Column Name Data Type Description
class_id uuid NOT NULL, Foreign Key to public.class(id).
course_id uuid NOT NULL, Foreign Key to public.course(id).
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
is_firebase boolean Default: false. True if synced from Firebase.
is_ops boolean Default: false. True if created by an operations user.
ops_created_by uuid Foreign Key to public.user(id). The ops user who created the record.

public.class_invite_code

Stores invite codes for joining classes.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
code integer NOT NULL. The numeric invite code.
expires_at timestamp with time zone NOT NULL. The expiration time for the code.
class_id uuid NOT NULL, Foreign Key to public.class(id).
is_class_code boolean Default: true.
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
is_firebase boolean Default: false. True if synced from Firebase.

public.class_user

Junction table linking users to classes with a specific role.

Column Name Data Type Description
class_id uuid NOT NULL, Foreign Key to public.class(id).
user_id uuid NOT NULL, Foreign Key to public.user(id).
role USER-DEFINED NOT NULL. The user's role in the class (e.g., 'teacher', 'student', 'parent').
created_at timestamp with time zone Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
is_firebase boolean Default: false. True if synced from Firebase.
is_ops boolean Default: false. True if created by an operations user.
ops_created_by uuid Foreign Key to public.user(id). The ops user who created the record.

public.connector_users

Stores user information for a connector service.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
email text NOT NULL, UNIQUE. The user's email address.
created_at timestamp with time zone Default: now()

public.course

Stores information about courses, which group chapters and lessons.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
name character varying NOT NULL. The name of the course.
image text URL of the course image.
description text A description of the course.
sort_index integer The display order of the course.
subject_id uuid Foreign Key to public.subject(id).
grade_id uuid Foreign Key to public.grade(id).
curriculum_id uuid Foreign Key to public.curriculum(id).
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
color text A hex color code associated with the course.
code text A unique code for the course.
firebase_id text UNIQUE. ID from Firebase, if applicable.

public.curriculum

Stores different educational curriculum (e.g., NCERT, UP, State Board).

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
name character varying NOT NULL. The name of the curriculum.
image text URL of an image representing the curriculum.
description text A brief description of the curriculum.
sort_index integer The display order for the curriculum in lists.
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
firebase_id text ID from Firebase, if applicable.

public.favorite_lesson

Junction table to store lessons that a user has marked as a favorite.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
lesson_id uuid NOT NULL, Foreign Key to public.lesson(id). The favorited lesson.
user_id uuid NOT NULL, Foreign Key to public.user(id). The user who favorited the lesson.
created_at timestamp with time zone Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
is_firebase boolean Default: false. True if the record is synced from Firebase.

public.grade

Stores grade levels (e.g., 'Grade 1', 'Grade 2').

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
name character varying NOT NULL. The name of the grade.
image text URL of an image for the grade.
description text A brief description of the grade level.
sort_index integer The display order for the grade in lists.
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
test text A field for testing purposes.
firebase_id text ID from Firebase, if applicable.

public.issue_debug

A table for logging information to help debug specific issues reported by users.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
created_at timestamp with time zone NOT NULL, Default: now()
student_id text The ID of the student experiencing the issue.
result_id text The ID of the result record related to the issue.
firebase_id text ID from Firebase, if applicable.
is_firebase boolean True if the record is synced from Firebase.

public.language

Stores available languages for content and user interface.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
name character varying NOT NULL. The name of the language (e.g., 'English', 'Hindi').
image text URL of an image or flag for the language.
description text A brief description of the language.
sort_index integer The display order for the language in lists.
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
code text The language code (e.g., 'en', 'hi').
firebase_id text ID from Firebase, if applicable.

public.lesson

Stores individual learning units, games, or activities. This is a core content table.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
name text The name of the lesson.
image text URL of the lesson's thumbnail image.
outcome text The intended learning outcome of the lesson.
plugin_type text Type of plugin used for the lesson (e.g., 'cocos').
status text The status of the lesson (e.g., 'approved').
cocos_subject_code text Subject code for Cocos-based lessons.
cocos_chapter_code text Chapter code for Cocos-based lessons.
created_by text The author or creator of the lesson.
subject_id uuid Foreign Key to public.subject(id). The subject this lesson belongs to.
target_age_from integer Minimum target age for the lesson.
target_age_to integer Maximum target age for the lesson.
language_id uuid Foreign Key to public.language(id). The language of the lesson content.
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
cocos_lesson_id text The unique ID for a Cocos-based lesson.
color text A hex color code associated with the lesson.

public.live_quiz_room

Stores data for real-time live quiz sessions conducted in a class.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
created_at timestamp with time zone Default: now()
results jsonb JSON object containing the live quiz results.
updated_at timestamp with time zone Timestamp of the last update.
assignment_id uuid NOT NULL, Foreign Key to public.assignment(id).
class_id uuid NOT NULL, Foreign Key to public.class(id).
course_id uuid NOT NULL, Foreign Key to public.course(id).
lesson_id uuid NOT NULL, Foreign Key to public.lesson(id).
participants ARRAY Array of participating user IDs.
is_deleted boolean Default: false. Flag for soft deletion.
school_id uuid NOT NULL, Foreign Key to public.school(id).
starts_at timestamp with time zone NOT NULL. The start time of the live quiz room.

public.parent_user

Junction table linking parent users to their children (student users).

Column Name Data Type Description
parent_id uuid NOT NULL, Foreign Key to public.user(id). The parent's user ID.
student_id uuid NOT NULL, Foreign Key to public.user(id). The student's user ID.
created_at timestamp with time zone Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
is_firebase boolean Default: false. True if the record is synced from Firebase.
is_ops boolean Default: false. True if created by an operations user.
ops_created_by uuid Foreign Key to public.user(id). The ops user who created the record.

public.program

Stores information about high-level educational programs or initiatives.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
name text NOT NULL. The name of the program.
program_type USER-DEFINED The type of the program.
implementation_partner text The partner responsible for implementation.
funding_partner text The partner providing funding.
institute_partner text The associated institute partner.
country text Country where the program is run.
state text State where the program is run.
block text Block/region where the program is run.
cluster text Cluster where the program is run.
village text Village where the program is run.
institutes_count text Number of institutes in the program.
students_count text Number of students in the program.
devices_count text Number of devices deployed in the program.
start_date date The start date of the program.
end_date date The end date of the program.
district text District where the program is run.
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone NOT NULL, Default: now()
is_deleted boolean Flag for soft deletion.
is_ops boolean True if created by an operations user.
model text The program model.

public.program_user

Junction table linking users to specific programs and defining their role within that program.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
user uuid Foreign Key to public.user(id). The user associated with the program.
program_id uuid NOT NULL, Foreign Key to public.program(id). The program the user is part of.
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone NOT NULL, Default: now()
role USER-DEFINED The user's role within the program.
is_deleted boolean NOT NULL, Default: false. Flag for soft deletion.
is_ops boolean True if the record was created by an operations user.

public.req_new_school

Stores requests from users to add a new school to the system. This acts as a moderation queue.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
user_id uuid Foreign Key to public.user(id). The user who made the request.
name text The name of the requested school.
state text The state of the requested school.
district text The district of the requested school.
city text The city of the requested school.
udise_id text The UDISE ID of the requested school, if provided.
is_resolved boolean Default: false. Flag to track if the request has been processed.
created_at timestamp without time zone Default: now()
updated_at timestamp without time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
image text URL of an image/document uploaded with the request.

public.result

Stores the outcome of a student's attempt at a lesson or assignment, including performance metrics.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
assignment_id uuid Foreign Key to public.assignment(id). The assignment context, if any.
lesson_id uuid Foreign Key to public.lesson(id). The lesson that was played.
school_id uuid Foreign Key to public.school(id). The school of the student at the time of play.
score integer The score achieved.
student_id uuid NOT NULL, Foreign Key to public.user(id). The student who performed the activity.
correct_moves integer Number of correct actions or answers.
time_spent integer Time spent on the lesson in seconds.
wrong_moves integer Number of incorrect actions or answers.
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
course_id uuid Foreign Key to public.course(id).
chapter_id uuid Foreign Key to public.chapter(id).
class_id uuid Foreign Key to public.class(id).
firebase_id text ID from Firebase, if applicable.
is_firebase boolean Default: false. True if synced from Firebase.

public.reward

Stores information about periodic rewards, likely for gamification purposes.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
monthly text Identifier or data for the monthly reward.
weekly text Identifier or data for the weekly reward.
weeklySticker text Identifier or data for the weekly sticker reward.
year integer NOT NULL. The year to which these rewards apply.

public.saved_queries

A utility table for developers or administrators to save frequently used SQL queries.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
created_at timestamp without time zone Default: now()
query text NOT NULL. The saved SQL query text.

public.school

Stores information about schools registered in the system. This is a central entity.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
name text NOT NULL. The name of the school.
group1 text state.
group2 text district
group3 text city
image text URL of the school's image or logo.
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
firebase_id text ID from Firebase, if applicable.
is_firebase boolean Default: false. True if synced from Firebase.
udise text The official UDISE code for the school.
group4 text village
academic_year text The current academic year for the school.
language text The primary language of instruction.
student_login_type USER-DEFINED The method students use to log in.
is_ops boolean Default: false. True if created by an operations user.
program_id uuid Foreign Key to public.program(id). The program this school is part of.
model USER-DEFINED The operational model of the school.
address text The physical address of the school.
ops_created_by uuid Foreign Key to public.user(id). The ops user who created the record.

public.school_course

Junction table linking which courses are available to a specific school.

Column Name Data Type Description
school_id uuid NOT NULL, Foreign Key to public.school(id).
course_id uuid NOT NULL, Foreign Key to public.course(id).
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
is_firebase boolean Default: false. True if synced from Firebase.
is_ops boolean Default: false. True if created by an operations user.
ops_created_by uuid Foreign Key to public.user(id). The ops user who created the record.

public.school_data

Stores pre-populated, potentially public, data about schools, likely from a government database, identified by UDISE code.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
school_name text
district text
village text
udise_code text UNIQUE. The key linking to external data.
state text
block text
cluster text
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp without time zone Default: now()
instruction_medium text (e.g.,'ENGLISH, KANNADA, HINDI')
total_teachers integer No. of teachers are in school
pre_primary_section_available boolean
school_type text
classes text
head_teachers text
head_teacher text
country text Default: 'India'

public.school_user

Junction table linking users to schools with a specific role (e.g., school admin, teacher).

Column Name Data Type Description
school_id uuid NOT NULL, Foreign Key to public.school(id).
user_id uuid NOT NULL, Foreign Key to public.user(id).
role USER-DEFINED NOT NULL. The user's role within the school.
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
is_firebase boolean Default: false. True if synced from Firebase.
is_ops boolean Default: false. True if created by an operations user.
ops_created_by uuid Foreign Key to public.user(id). The ops user who created the record.

public.special_users

Designates users with special roles or permissions across the system, like super-administrators.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
user_id uuid Foreign Key to public.user(id). Default: auth.uid().
is_deleted boolean Flag for soft deletion.
role USER-DEFINED The special role assigned to the user.

public.sticker

Stores information about collectible stickers used for gamification and rewards.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
name character varying NOT NULL. The name of the sticker.
image text URL of the sticker image.
description text A brief description of the sticker or how it's earned.
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.

public.subject

Stores academic subject areas (e.g., 'Maths', 'English', 'Kannada').

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
name character varying NOT NULL. The name of the subject.
image text URL of an image representing the subject.
description text A brief description of the subject.
sort_index integer The display order for the subject in lists.
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.

public.upload_queue

Manages a queue for asynchronous processing of bulk data uploads.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
uploading_user uuid The user who initiated the upload.
start_time timestamp with time zone The scheduled start time for processing the job.
payload jsonb NOT NULL. The data to be processed, stored in JSONB format.
status text Default: 'pending'. The current status of the job (e.g., 'pending', 'processing', 'completed', 'failed').
error text Stores error messages if the job fails.
process_started_at timestamp with time zone Default: now(). Timestamp when processing actually began.
batch_number bigint A number to group related upload jobs together.
is_locked boolean Default: false. A flag to prevent concurrent processing of the same job.
locked_at timestamp with time zone The time when the lock was acquired.
locked_by text An identifier for the worker process that locked the job.

public.user

The central table for storing all user profile information, including students, teachers, parents, and administrators.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
name text The user's full name.
email text The user's email address.
phone text The user's phone number.
gender text The user's gender.
image text URL of the user's profile picture.
avatar text Identifier for a user's selected avatar from a predefined set.
language_id uuid Foreign Key to public.language(id). The user's preferred language.
curriculum_id uuid Foreign Key to public.curriculum(id). The user's preferred curriculum.
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
is_tc_accepted boolean Default: false. Tracks if the user has accepted Terms & Conditions.
age integer The user's age.
grade_id uuid Foreign Key to public.grade(id). The user's current grade.
music_off boolean Default: false. User preference to disable background music.
sfx_off boolean Default: false. User preference to disable sound effects.
fcm_token text Firebase Cloud Messaging token for push notifications.
student_id text A custom student identifier, if applicable.
firebase_id text The user's unique ID from Firebase Authentication.
is_firebase boolean Default: false. True if synced from Firebase.
is_ops boolean Default: false. True if the user is an operations user.
stars integer The total number of stars the user has earned.
learning_path text Identifier for the user's assigned learning path.
ops_created_by uuid The operations user who created this user record.

public.user_badge

Junction table tracking which badges a user has earned.

Column Name Data Type Description
user_id uuid NOT NULL, Foreign Key to public.user(id).
badge_id uuid NOT NULL, Foreign Key to public.badge(id).
created_at timestamp with time zone NOT NULL, Default: now(). The time the badge was awarded.
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
is_seen boolean Tracks if the user has seen the badge.
is_firebase boolean Default: false. True if synced from Firebase.

public.user_bonus

Junction table tracking bonus rewards (in the form of lessons) that a user has unlocked.

Column Name Data Type Description
user_id uuid NOT NULL, Foreign Key to public.user(id).
bonus_id uuid NOT NULL, Foreign Key to public.lesson(id). The ID of the bonus lesson.
created_at timestamp with time zone NOT NULL, Default: now(). The time the bonus was awarded.
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
is_seen boolean Tracks if the user has seen the bonus.
is_firebase boolean Default: false. True if synced from Firebase.

public.user_course

Junction table linking users to courses, typically to signify enrollment.

Column Name Data Type Description
user_id uuid NOT NULL, Foreign Key to public.user(id).
course_id uuid NOT NULL, Foreign Key to public.course(id).
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
is_firebase boolean Default: false. True if synced from Firebase.

public.user_sticker

Junction table tracking which stickers a user has collected.

Column Name Data Type Description
user_id uuid NOT NULL, Foreign Key to public.user(id).
sticker_id uuid NOT NULL, Foreign Key to public.sticker(id).
created_at timestamp with time zone NOT NULL, Default: now(). The time the sticker was awarded.
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
is_seen boolean Tracks if the user has seen the sticker.
is_firebase boolean Default: false. True if synced from Firebase.

  1. Database Tables Explained

public.announcement

Stores announcements to be sent to users.

  • id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  • title — text — The title of the announcement.
  • message — text — The main content of the announcement.
  • type — text — Type of announcement (e.g., 'info', 'alert').
  • image_url — text — URL for an associated image.
  • custom_icon — text — Custom icon identifier.
  • created_at — timestamp with time zone — NOT NULL, Default: now()
  • send_at — timestamp with time zone — The scheduled time to send.
  • target_ids — ARRAY — Array of user IDs or target groups.

public.assignment

Stores assignments given to students, linking lessons to classes or individuals.

  • id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  • created_by — uuid — FK → public.user(id) (teacher who created it)
  • starts_at — timestamp with time zone — NOT NULL, Default: now()
  • ends_at — timestamp with time zone — Due date.
  • is_class_wise — boolean — NOT NULL, Default: false — True if for entire class.
  • class_id — uuid — NOT NULL, FK → public.class(id)
  • school_id — uuid — NOT NULL, FK → public.school(id)
  • lesson_id — uuid — NOT NULL, FK → public.lesson(id)
  • type — text — e.g., 'assignment', 'liveQuiz'.
  • created_at — timestamp with time zone — NOT NULL, Default: now()
  • updated_at — timestamp with time zone — Default: now()
  • is_deleted — boolean — Default: false
  • chapter_id — uuid — FK → public.chapter(id)
  • course_id — uuid — FK → public.course(id)
  • source — text — e.g., 'chatbot'
  • firebase_id — text — Firebase ID if applicable.
  • is_firebase — boolean — Default: false

public.assignment_cart

A temporary holding area for a user’s selected lessons before they’re formally assigned.

  • id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid(). Also FK → public.user(id)
  • created_at — timestamp with time zone — NOT NULL, Default: now()
  • is_deleted — boolean — Default: false
  • lessons — text — List of lesson IDs (e.g., JSON array)
  • updated_at — timestamp without time zone — Default: now()
  • is_firebase — boolean — Default: false

public.assignment_user

Links assignments to individual students.

  • id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  • assignment_id — uuid — NOT NULL, FK → public.assignment(id)
  • user_id — uuid — NOT NULL, FK → public.user(id) (student ID)
  • created_at — timestamp with time zone — Default: now()
  • updated_at — timestamp with time zone — Default: now()
  • is_deleted — boolean — Default: false
  • is_firebase — boolean — Default: false

public.auto_chatbot

Stores automated chatbot task data for schools.

  • id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  • school_id — uuid — NOT NULL, FK → public.school(id)
  • task_data — jsonb — JSON payload for the chatbot task.
  • created_at — timestamp with time zone — NOT NULL, Default: now()
  • updated_at — timestamp with time zone — Default: now()
  • is_deleted — boolean — Default: false

public.badge

Stores information about achievable badges for gamification.

  • id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  • name — character varying — NOT NULL. Name of the badge.
  • image — text — URL of the badge image.
  • description — text — How to earn the badge.
  • created_at — timestamp with time zone — Default: now()
  • updated_at — timestamp with time zone — Default: now()
  • is_deleted — boolean — Default: false

public.chapter

Stores course chapters (collections of lessons).

  • id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  • name — text — Name of the chapter.
  • image — text — URL for chapter image.
  • course_id — uuid — FK → public.course(id)
  • sort_index — integer — Display order.
  • sub_topics — text — Serialized sub-topics.
  • created_at — timestamp with time zone — NOT NULL, Default: now()
  • updated_at — timestamp with time zone — Default: now()
  • is_deleted — boolean — Default: false

public.chapter_lesson

Links lessons to chapters and defines order.

  • id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  • lesson_id — uuid — NOT NULL, FK → public.lesson(id)
  • chapter_id — uuid — NOT NULL, FK → public.chapter(id)
  • sort_index — integer — Order within chapter.
  • created_at — timestamp with time zone — NOT NULL, Default: now()
  • updated_at — timestamp with time zone — Default: now()
  • is_deleted — boolean — Default: false

public.chapter_links

Stores external links related to chapters, courses, and curricula.

  • id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  • created_at — timestamp with time zone — NOT NULL, Default: now()
  • chapter_id — uuid — FK → public.chapter(id)
  • link — text — External URL.
  • curriculum_id — uuid — FK → public.curriculum(id)
  • course_id — uuid — FK → public.course(id)
  • grade_id — uuid — FK → public.grade(id)
  • updated_at — timestamp without time zone — Default: now()
  • is_deleted — boolean — Default: false

public.chatbot

Stores general chatbot configuration and payload data.

  • id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  • created_at — timestamp with time zone — NOT NULL, Default: now()
  • updated_at — timestamp with time zone — Default: now()
  • is_deleted — boolean — Default: false
  • chatbot_doc — jsonb — JSON document for chatbot logic.
  • phonenumber — text — NOT NULL. Associated phone number.

public.class

Stores information about classes within a school.

  • id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  • name — text — NOT NULL. Name of the class (e.g., '1', '2A').
  • image — text — URL for class image.
  • school_id — uuid — NOT NULL. FK → public.school(id).
  • created_at — timestamp with time zone — NOT NULL, Default: now()
  • updated_at — timestamp with time zone — Default: now()
  • is_deleted — boolean — Default: false
  • firebase_id — text — Firebase ID if applicable.
  • is_firebase — boolean — Default: false
  • status — text — Default: 'active' (e.g., 'active', 'migrated').
  • is_ops — boolean — Default: false
  • standard — text — Grade or standard of the class.
  • academic_year — text — Academic year (e.g., '2023-2024').
  • ops_created_by — uuid — FK → public.user(id) (ops user who created).
  • group_id — text — WhatsApp group ID.

public.class_course

Links courses to classes — defines which courses are taught in a class.

  • id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  • class_id — uuid — NOT NULL. FK → public.class(id)
  • course_id — uuid — NOT NULL. FK → public.course(id)
  • created_at — timestamp with time zone — NOT NULL, Default: now()
  • updated_at — timestamp with time zone — Default: now()
  • is_deleted — boolean — Default: false
  • is_firebase — boolean — Default: false
  • is_ops — boolean — Default: false
  • ops_created_by — uuid — FK → public.user(id)

public.class_invite_code

Stores invite codes for joining classes.

  • id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  • code — integer — NOT NULL. Numeric invite code.
  • expires_at — timestamp with time zone — NOT NULL. Expiration time.
  • class_id — uuid — NOT NULL. FK → public.class(id)
  • is_class_code — boolean — Default: true
  • created_at — timestamp with time zone — NOT NULL, Default: now()
  • updated_at — timestamp with time zone — Default: now()
  • is_deleted — boolean — Default: false
  • is_firebase — boolean — Default: false

public.class_user

Links users to classes with a specific role.

  • id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  • class_id — uuid — NOT NULL. FK → public.class(id)
  • user_id — uuid — NOT NULL. FK → public.user(id)
  • role — USER-DEFINED — NOT NULL. e.g., 'teacher', 'student', 'parent'.
  • created_at — timestamp with time zone — Default: now()
  • updated_at — timestamp with time zone — Default: now()
  • is_deleted — boolean — Default: false
  • is_firebase — boolean — Default: false
  • is_ops — boolean — Default: false
  • ops_created_by — uuid — FK → public.user(id)

public.connector_users

Stores user information for a connector service.

  • id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  • email — text — NOT NULL, UNIQUE. User’s email.
  • created_at — timestamp with time zone — Default: now()

public.course

Stores information about courses — groups chapters and lessons.

  • id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  • name — character varying — NOT NULL. Name of the course.
  • image — text — URL for course image.
  • description — text — Description of the course.
  • sort_index — integer — Display order.
  • subject_id — uuid — FK → public.subject(id)
  • grade_id — uuid — FK → public.grade(id)
  • curriculum_id — uuid — FK → public.curriculum(id)
  • created_at — timestamp with time zone — NOT NULL, Default: now()
  • updated_at — timestamp with time zone — Default: now()
  • is_deleted — boolean — Default: false
  • color — text — Hex color code.
  • code — text — Unique course code.
  • firebase_id — text — UNIQUE. Firebase ID if any.

public.curriculum

Stores educational curricula (e.g., NCERT, State Board).

  • id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  • name — character varying — NOT NULL. Curriculum name.
  • image — text — Image for the curriculum.
  • description — text — Brief description.
  • sort_index — integer — Display order.
  • created_at — timestamp with time zone — NOT NULL, Default: now()
  • updated_at — timestamp with time zone — Default: now()
  • is_deleted — boolean — Default: false
  • firebase_id — text — Firebase ID if any.

public.favorite_lesson

Links users to lessons they marked as favorites.

  • id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  • lesson_id — uuid — NOT NULL. FK → public.lesson(id)
  • user_id — uuid — NOT NULL. FK → public.user(id)
  • created_at — timestamp with time zone — Default: now()
  • updated_at — timestamp with time zone — Default: now()
  • is_deleted — boolean — Default: false
  • is_firebase — boolean — Default: false

public.grade

Stores grade levels (e.g., Grade 1, Grade 2).

  • id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  • name — character varying — NOT NULL. Grade name.
  • image — text — Image for the grade.
  • description — text — Short description.
  • sort_index — integer — Display order.
  • created_at — timestamp with time zone — NOT NULL, Default: now()
  • updated_at — timestamp with time zone — Default: now()
  • is_deleted — boolean — Default: false
  • test — text — For testing purposes.
  • firebase_id — text — Firebase ID if any.

public.issue_debug

Logs info to help debug issues reported by users.

  • id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  • created_at — timestamp with time zone — NOT NULL, Default: now()
  • student_id — text — ID of the student with issue.
  • result_id — text — ID of the related result.
  • firebase_id — text — Firebase ID if any.
  • is_firebase — boolean — True if synced from Firebase.

public.language

Stores available languages for content and UI.

  • id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  • name — character varying — NOT NULL. Language name (e.g., English, Hindi).
  • image — text — Flag or image.
  • description — text — Short description.
  • sort_index — integer — Display order.
  • created_at — timestamp with time zone — NOT NULL, Default: now()
  • updated_at — timestamp with time zone — Default: now()
  • is_deleted — boolean — Default: false
  • code — text — Language code (e.g., 'en', 'hi').
  • firebase_id — text — Firebase ID if any.

public.lesson

Stores individual learning units, games, or activities.

  • id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  • name — text — Lesson name.
  • image — text — Thumbnail image.
  • outcome — text — Intended learning outcome.
  • plugin_type — text — Plugin type (e.g., 'cocos').
  • status — text — e.g., 'approved'.
  • cocos_subject_code — text — Cocos subject code.
  • cocos_chapter_code — text — Cocos chapter code.
  • created_by — text — Author/creator.
  • subject_id — uuid — FK → public.subject(id)
  • target_age_from — integer — Min target age.
  • target_age_to — integer — Max target age.
  • language_id — uuid — FK → public.language(id)
  • created_at — timestamp with time zone — NOT NULL, Default: now()
  • updated_at — timestamp with time zone — Default: now()
  • is_deleted — boolean — Default: false
  • cocos_lesson_id — text — Cocos lesson ID.
  • color — text — Hex color code.

public.live_quiz_room

Stores real-time live quiz session data.

  • id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  • created_at — timestamp with time zone — Default: now()
  • results — jsonb — JSON of quiz results.
  • updated_at — timestamp with time zone — Last update.
  • assignment_id — uuid — NOT NULL, FK → public.assignment(id)
  • class_id — uuid — NOT NULL, FK → public.class(id)
  • course_id — uuid — NOT NULL, FK → public.course(id)
  • lesson_id — uuid — NOT NULL, FK → public.lesson(id)
  • participants — ARRAY — Array of participant IDs.
  • is_deleted — boolean — Default: false
  • school_id — uuid — NOT NULL, FK → public.school(id)
  • starts_at — timestamp with time zone — NOT NULL. Start time.

public.parent_user

Links parent users to their children (students).

  • id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  • parent_id — uuid — NOT NULL. FK → public.user(id) (parent)
  • student_id — uuid — NOT NULL. FK → public.user(id) (student)
  • created_at — timestamp with time zone — Default: now()
  • updated_at — timestamp with time zone — Default: now()
  • is_deleted — boolean — Default: false
  • is_firebase — boolean — Default: false
  • is_ops — boolean — Default: false
  • ops_created_by — uuid — FK → public.user(id)

public.program

Stores information about high-level educational programs or initiatives.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
name text NOT NULL. The name of the program.
program_type USER-DEFINED The type of the program.
implementation_partner text The partner responsible for implementation.
funding_partner text The partner providing funding.
institute_partner text The associated institute partner.
country text Country where the program is run.
state text State where the program is run.
block text Block/region where the program is run.
cluster text Cluster where the program is run.
village text Village where the program is run.
institutes_count text Number of institutes in the program.
students_count text Number of students in the program.
devices_count text Number of devices deployed in the program.
start_date date The start date of the program.
end_date date The end date of the program.
district text District where the program is run.
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone NOT NULL, Default: now()
is_deleted boolean Flag for soft deletion.
is_ops boolean True if created by an operations user.
model text The program model.

public.program_user

Junction table linking users to specific programs and defining their role within that program.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
user uuid Foreign Key to public.user(id). The user associated with the program.
program_id uuid NOT NULL, Foreign Key to public.program(id). The program the user is part of.
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone NOT NULL, Default: now()
role USER-DEFINED The user's role within the program.
is_deleted boolean NOT NULL, Default: false. Flag for soft deletion.
is_ops boolean True if the record was created by an operations user.

public.req_new_school

Stores requests from users to add a new school to the system. This acts as a moderation queue.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
user_id uuid Foreign Key to public.user(id). The user who made the request.
name text The name of the requested school.
state text The state of the requested school.
district text The district of the requested school.
city text The city of the requested school.
udise_id text The UDISE ID of the requested school, if provided.
is_resolved boolean Default: false. Flag to track if the request has been processed.
created_at timestamp without time zone Default: now()
updated_at timestamp without time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
image text URL of an image/document uploaded with the request.

public.result

Stores the outcome of a student's attempt at a lesson or assignment, including performance metrics.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
assignment_id uuid Foreign Key to public.assignment(id). The assignment context, if any.
lesson_id uuid Foreign Key to public.lesson(id). The lesson that was played.
school_id uuid Foreign Key to public.school(id). The school of the student at the time of play.
score integer The score achieved.
student_id uuid NOT NULL, Foreign Key to public.user(id). The student who performed the activity.
correct_moves integer Number of correct actions or answers.
time_spent integer Time spent on the lesson in seconds.
wrong_moves integer Number of incorrect actions or answers.
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
course_id uuid Foreign Key to public.course(id).
chapter_id uuid Foreign Key to public.chapter(id).
class_id uuid Foreign Key to public.class(id).
firebase_id text ID from Firebase, if applicable.
is_firebase boolean Default: false. True if synced from Firebase.

public.reward

Stores information about periodic rewards, likely for gamification purposes.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
monthly text Identifier or data for the monthly reward.
weekly text Identifier or data for the weekly reward.
weeklySticker text Identifier or data for the weekly sticker reward.
year integer NOT NULL. The year to which these rewards apply.

public.saved_queries

A utility table for developers or administrators to save frequently used SQL queries.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
created_at timestamp without time zone Default: now()
query text NOT NULL. The saved SQL query text.

public.school

Stores information about schools registered in the system. This is a central entity.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
name text NOT NULL. The name of the school.
group1 text state.
group2 text district
group3 text city
image text URL of the school's image or logo.
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
firebase_id text ID from Firebase, if applicable.
is_firebase boolean Default: false. True if synced from Firebase.
udise text The official UDISE code for the school.
group4 text village
academic_year text The current academic year for the school.
language text The primary language of instruction.
student_login_type USER-DEFINED The method students use to log in.
is_ops boolean Default: false. True if created by an operations user.
program_id uuid Foreign Key to public.program(id). The program this school is part of.
model USER-DEFINED The operational model of the school.
address text The physical address of the school.
ops_created_by uuid Foreign Key to public.user(id). The ops user who created the record.

public.school_course

Junction table linking which courses are available to a specific school.

Column Name Data Type Description
school_id uuid NOT NULL, Foreign Key to public.school(id).
course_id uuid NOT NULL, Foreign Key to public.course(id).
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
is_firebase boolean Default: false. True if synced from Firebase.
is_ops boolean Default: false. True if created by an operations user.
ops_created_by uuid Foreign Key to public.user(id). The ops user who created the record.

public.school_data

Stores pre-populated, potentially public, data about schools, likely from a government database, identified by UDISE code.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
school_name text
district text
village text
udise_code text UNIQUE. The key linking to external data.
state text
block text
cluster text
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp without time zone Default: now()
instruction_medium text (e.g.,'ENGLISH, KANNADA, HINDI')
total_teachers integer No. of teachers are in school
pre_primary_section_available boolean
school_type text
classes text
head_teachers text
head_teacher text
country text Default: 'India'

public.school_user

Junction table linking users to schools with a specific role (e.g., school admin, teacher).

Column Name Data Type Description
school_id uuid NOT NULL, Foreign Key to public.school(id).
user_id uuid NOT NULL, Foreign Key to public.user(id).
role USER-DEFINED NOT NULL. The user's role within the school.
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
is_firebase boolean Default: false. True if synced from Firebase.
is_ops boolean Default: false. True if created by an operations user.
ops_created_by uuid Foreign Key to public.user(id). The ops user who created the record.

public.special_users

Designates users with special roles or permissions across the system, like super-administrators.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
user_id uuid Foreign Key to public.user(id). Default: auth.uid().
is_deleted boolean Flag for soft deletion.
role USER-DEFINED The special role assigned to the user.

public.sticker

Stores information about collectible stickers used for gamification and rewards.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
name character varying NOT NULL. The name of the sticker.
image text URL of the sticker image.
description text A brief description of the sticker or how it's earned.
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.

public.subject

Stores academic subject areas (e.g., 'Maths', 'English', 'Kannada').

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
name character varying NOT NULL. The name of the subject.
image text URL of an image representing the subject.
description text A brief description of the subject.
sort_index integer The display order for the subject in lists.
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.

public.upload_queue

Manages a queue for asynchronous processing of bulk data uploads.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
uploading_user uuid The user who initiated the upload.
start_time timestamp with time zone The scheduled start time for processing the job.
payload jsonb NOT NULL. The data to be processed, stored in JSONB format.
status text Default: 'pending'. The current status of the job (e.g., 'pending', 'processing', 'completed', 'failed').
error text Stores error messages if the job fails.
process_started_at timestamp with time zone Default: now(). Timestamp when processing actually began.
batch_number bigint A number to group related upload jobs together.
is_locked boolean Default: false. A flag to prevent concurrent processing of the same job.
locked_at timestamp with time zone The time when the lock was acquired.
locked_by text An identifier for the worker process that locked the job.

public.user

The central table for storing all user profile information, including students, teachers, parents, and administrators.

Column Name Data Type Description
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
name text The user's full name.
email text The user's email address.
phone text The user's phone number.
gender text The user's gender.
image text URL of the user's profile picture.
avatar text Identifier for a user's selected avatar from a predefined set.
language_id uuid Foreign Key to public.language(id). The user's preferred language.
curriculum_id uuid Foreign Key to public.curriculum(id). The user's preferred curriculum.
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
is_tc_accepted boolean Default: false. Tracks if the user has accepted Terms & Conditions.
age integer The user's age.
grade_id uuid Foreign Key to public.grade(id). The user's current grade.
music_off boolean Default: false. User preference to disable background music.
sfx_off boolean Default: false. User preference to disable sound effects.
fcm_token text Firebase Cloud Messaging token for push notifications.
student_id text A custom student identifier, if applicable.
firebase_id text The user's unique ID from Firebase Authentication.
is_firebase boolean Default: false. True if synced from Firebase.
is_ops boolean Default: false. True if the user is an operations user.
stars integer The total number of stars the user has earned.
learning_path text Identifier for the user's assigned learning path.
ops_created_by uuid The operations user who created this user record.

public.user_badge

Junction table tracking which badges a user has earned.

Column Name Data Type Description
user_id uuid NOT NULL, Foreign Key to public.user(id).
badge_id uuid NOT NULL, Foreign Key to public.badge(id).
created_at timestamp with time zone NOT NULL, Default: now(). The time the badge was awarded.
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
is_seen boolean Tracks if the user has seen the badge.
is_firebase boolean Default: false. True if synced from Firebase.

public.user_bonus

Junction table tracking bonus rewards (in the form of lessons) that a user has unlocked.

Column Name Data Type Description
user_id uuid NOT NULL, Foreign Key to public.user(id).
bonus_id uuid NOT NULL, Foreign Key to public.lesson(id). The ID of the bonus lesson.
created_at timestamp with time zone NOT NULL, Default: now(). The time the bonus was awarded.
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
is_seen boolean Tracks if the user has seen the bonus.
is_firebase boolean Default: false. True if synced from Firebase.

public.user_course

Junction table linking users to courses, typically to signify enrollment.

Column Name Data Type Description
user_id uuid NOT NULL, Foreign Key to public.user(id).
course_id uuid NOT NULL, Foreign Key to public.course(id).
created_at timestamp with time zone NOT NULL, Default: now()
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
is_firebase boolean Default: false. True if synced from Firebase.

public.user_sticker

Junction table tracking which stickers a user has collected.

Column Name Data Type Description
user_id uuid NOT NULL, Foreign Key to public.user(id).
sticker_id uuid NOT NULL, Foreign Key to public.sticker(id).
created_at timestamp with time zone NOT NULL, Default: now(). The time the sticker was awarded.
updated_at timestamp with time zone Default: now()
is_deleted boolean Default: false. Flag for soft deletion.
id uuid Primary Key, NOT NULL, Default: gen_random_uuid()
is_seen boolean Tracks if the user has seen the sticker.
is_firebase boolean Default: false. True if synced from Firebase.

This is footer part. Add any data as page footer

Clone this wiki locally