DB Schema

1. Public Schema: Tables & Columns

public.announcement

Stores announcements to be sent to users.

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
title	text	NULL
message	text	NULL
type	text	NULL
image_url	text	NULL
custom_icon	text	NULL
created_at	timestamp with time zone	NOT NULL, DEFAULT now()
send_at	timestamp with time zone	NULL
target_ids	text[]	NULL

public.assignment

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
created_by	uuid	NULL, FOREIGN KEY → public.user(id)
starts_at	timestamp with time zone	NOT NULL, DEFAULT now()
ends_at	timestamp with time zone	NULL
is_class_wise	boolean	NOT NULL, DEFAULT false
class_id	uuid	NOT NULL, FOREIGN KEY → public.class(id)
school_id	uuid	NOT NULL, FOREIGN KEY → public.school(id)
lesson_id	uuid	NOT NULL, FOREIGN KEY → public.lesson(id)
type	text	NULL
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	NULL, FOREIGN KEY → public.chapter(id)
course_id	uuid	NULL, FOREIGN KEY → public.course(id)
source	text	NULL
firebase_id	text	NULL
is_firebase	boolean	DEFAULT false

public.assignment_cart

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid(), FOREIGN KEY → public.user(id)
created_at	timestamp with time zone	NOT NULL, DEFAULT now()
is_deleted	boolean	DEFAULT false
lessons	text	NULL
updated_at	timestamp without time zone	DEFAULT now()
is_firebase	boolean	DEFAULT false

public.assignment_user

Column Name	Data Type	Constraints / Default
assignment_id	uuid	NOT NULL, FOREIGN KEY → public.assignment(id)
user_id	uuid	NOT NULL, FOREIGN KEY → public.user(id)
created_at	timestamp with time zone	DEFAULT now()
updated_at	timestamp with time zone	DEFAULT now()
is_deleted	boolean	DEFAULT false
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
is_firebase	boolean	DEFAULT false

public.auto_chatbot

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
school_id	uuid	NOT NULL, FOREIGN KEY → public.school(id)
task_data	jsonb	NULL
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

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
name	character varying	NOT NULL
image	text	NULL
description	text	NULL
updated_at	timestamp with time zone	DEFAULT now()
created_at	timestamp with time zone	DEFAULT now()
is_deleted	boolean	DEFAULT false

public.chapter

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
name	text	NULL
image	text	NULL
course_id	uuid	NULL, FOREIGN KEY → public.course(id)
sort_index	integer	NULL
sub_topics	text	NULL
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

Column Name	Data Type	Constraints / Default
lesson_id	uuid	NOT NULL, FOREIGN KEY → public.lesson(id)
chapter_id	uuid	NOT NULL, FOREIGN KEY → public.chapter(id)
sort_index	integer	NULL
created_at	timestamp with time zone	NOT NULL, DEFAULT now()
updated_at	timestamp with time zone	DEFAULT now()
is_deleted	boolean	DEFAULT false
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()

public.chapter_links

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
created_at	timestamp with time zone	NOT NULL, DEFAULT now()
chapter_id	uuid	NULL, FOREIGN KEY → public.chapter(id)
link	text	NULL
curriculum_id	uuid	NULL, FOREIGN KEY → public.curriculum(id)
course_id	uuid	NULL, FOREIGN KEY → public.course(id), DEFAULT gen_random_uuid()
grade_id	uuid	NULL, FOREIGN KEY → public.grade(id)
updated_at	timestamp without time zone	DEFAULT now()
is_deleted	boolean	DEFAULT false

public.chatbot

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, 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	NULL
phonenumber	text	NOT NULL

public.class

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
name	text	NOT NULL
image	text	NULL
school_id	uuid	NOT NULL, FOREIGN KEY → 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	NULL
is_firebase	boolean	DEFAULT false
status	text	DEFAULT 'active'
is_ops	boolean	DEFAULT false
standard	text	NULL
academic_year	text	NULL
ops_created_by	uuid	NULL, FOREIGN KEY → public.user(id)
group_id	text	NULL

public.class_course

Column Name	Data Type	Constraints / Default
class_id	uuid	NOT NULL, FOREIGN KEY → public.class(id)
course_id	uuid	NOT NULL, FOREIGN KEY → 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
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
is_firebase	boolean	DEFAULT false
is_ops	boolean	DEFAULT false
ops_created_by	uuid	NULL, FOREIGN KEY → public.user(id)

public.class_invite_code

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
code	integer	NOT NULL
expires_at	timestamp with time zone	NOT NULL
class_id	uuid	NOT NULL, FOREIGN KEY → 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

Column Name	Data Type	Constraints / Default
class_id	uuid	NOT NULL, FOREIGN KEY → public.class(id)
user_id	uuid	NOT NULL, FOREIGN KEY → public.user(id)
role	USER-DEFINED	NOT NULL
created_at	timestamp with time zone	DEFAULT now()
updated_at	timestamp with time zone	DEFAULT now()
is_deleted	boolean	DEFAULT false
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
is_firebase	boolean	DEFAULT false
is_ops	boolean	DEFAULT false
ops_created_by	uuid	NULL, FOREIGN KEY → public.user(id)

public.connector_users

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
email	text	NOT NULL
created_at	timestamp with time zone	DEFAULT now()

public.course

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
name	character varying	NOT NULL
image	text	NULL
description	text	NULL
sort_index	integer	NULL
subject_id	uuid	NULL, FOREIGN KEY → public.subject(id)
grade_id	uuid	NULL, FOREIGN KEY → public.grade(id)
curriculum_id	uuid	NULL, FOREIGN KEY → 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	NULL
code	text	NULL
firebase_id	text	NULL

public.curriculum

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
name	character varying	NOT NULL
image	text	NULL
description	text	NULL
sort_index	integer	NULL
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	NULL

public.favorite_lesson

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
lesson_id	uuid	NOT NULL, FOREIGN KEY → public.lesson(id)
user_id	uuid	NOT NULL, FOREIGN KEY → 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

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
name	character varying	NOT NULL
image	text	NULL
description	text	NULL
sort_index	integer	NULL
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	NULL
firebase_id	text	NULL

public.issue_debug

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
created_at	timestamp with time zone	NOT NULL, DEFAULT now()
student_id	text	NULL
result_id	text	NULL
firebase_id	text	NULL
is_firebase	boolean	NULL

public.language

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
name	character varying	NOT NULL
image	text	NULL
description	text	NULL
sort_index	integer	NULL
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	NULL
firebase_id	text	NULL

public.lesson

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
name	text	NULL
image	text	NULL
outcome	text	NULL
plugin_type	text	NULL
status	text	NULL
cocos_subject_code	text	NULL
cocos_chapter_code	text	NULL
created_by	text	NULL
subject_id	uuid	NULL, FOREIGN KEY → public.subject(id)
target_age_from	integer	NULL
target_age_to	integer	NULL
language_id	uuid	NULL, FOREIGN KEY → 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	NULL
color	text	NULL

public.live_quiz_room

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
created_at	timestamp with time zone	DEFAULT now()
results	jsonb	NULL
updated_at	timestamp with time zone	NULL
assignment_id	uuid	NOT NULL, FOREIGN KEY → public.assignment(id)
class_id	uuid	NOT NULL, FOREIGN KEY → public.class(id)
course_id	uuid	NOT NULL, FOREIGN KEY → public.course(id)
lesson_id	uuid	NOT NULL, FOREIGN KEY → public.lesson(id)
participants	ARRAY	NULL
is_deleted	boolean	DEFAULT false
school_id	uuid	NOT NULL, FOREIGN KEY → public.school(id)
starts_at	timestamp with time zone	NOT NULL

public.parent_user

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
parent_id	uuid	NOT NULL, FOREIGN KEY → public.user(id)
student_id	uuid	NOT NULL, FOREIGN KEY → 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
is_ops	boolean	DEFAULT false
ops_created_by	uuid	FOREIGN KEY → public.user(id)

public.program

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
name	text	NOT NULL
program_type	USER-DEFINED	NULL
implementation_partner	text	NULL
funding_partner	text	NULL
institute_partner	text	NULL
country	text	NULL
state	text	NULL
district	text	NULL
block	text	NULL
cluster	text	NULL
village	text	NULL
institutes_count	text	NULL
students_count	text	NULL
devices_count	text	NULL
start_date	date	NULL
end_date	date	NULL
created_at	timestamp with time zone	NOT NULL, DEFAULT now()
updated_at	timestamp with time zone	NOT NULL, DEFAULT now()
is_deleted	boolean	NULL
is_ops	boolean	NULL
model	text	NULL

public.program_user

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
user	uuid	FOREIGN KEY → public.user(id)
program_id	uuid	NOT NULL, FOREIGN KEY → public.program(id)
created_at	timestamp with time zone	NOT NULL, DEFAULT now()
updated_at	timestamp with time zone	NOT NULL, DEFAULT now()
role	USER-DEFINED	NULL
is_deleted	boolean	NOT NULL, DEFAULT false
is_ops	boolean	NULL

public.req_new_school

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
user_id	uuid	FOREIGN KEY → public.user(id)
name	text	NULL
state	text	NULL
district	text	NULL
city	text	NULL
udise_id	text	NULL
is_resolved	boolean	DEFAULT false
created_at	timestamp without time zone	DEFAULT now()
updated_at	timestamp without time zone	DEFAULT now()
is_deleted	boolean	DEFAULT false
image	text	NULL

public.result

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
assignment_id	uuid	FOREIGN KEY → public.assignment(id)
lesson_id	uuid	FOREIGN KEY → public.lesson(id)
school_id	uuid	FOREIGN KEY → public.school(id)
score	integer	NULL
student_id	uuid	NOT NULL, FOREIGN KEY → public.user(id)
correct_moves	integer	NULL
time_spent	integer	NULL
wrong_moves	integer	NULL
created_at	timestamp with time zone	NOT NULL, DEFAULT now()
updated_at	timestamp with time zone	DEFAULT now()
is_deleted	boolean	DEFAULT false
course_id	uuid	FOREIGN KEY → public.course(id)
chapter_id	uuid	FOREIGN KEY → public.chapter(id)
class_id	uuid	FOREIGN KEY → public.class(id)
firebase_id	text	NULL
is_firebase	boolean	DEFAULT false

public.reward

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, 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
monthly	text	NULL
weekly	text	NULL
weeklySticker	text	NULL
year	integer	NOT NULL

public.saved_queries

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
created_at	timestamp without time zone	DEFAULT now()
query	text	NOT NULL

public.school

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
name	text	NOT NULL
group1	text	NULL
group2	text	NULL
group3	text	NULL
image	text	NULL
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	NULL
is_firebase	boolean	DEFAULT false
udise	text	NULL
group4	text	NULL
academic_year	text	NULL
language	text	NULL
student_login_type	USER-DEFINED	NULL
is_ops	boolean	DEFAULT false
program_id	uuid	FOREIGN KEY → public.program(id)
model	USER-DEFINED	NULL
address	text	NULL
ops_created_by	uuid	FOREIGN KEY → public.user(id)

public.school_course

Column Name	Data Type	Constraints / Default
school_id	uuid	NOT NULL, FOREIGN KEY → public.school(id)
course_id	uuid	NOT NULL, FOREIGN KEY → 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
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
is_firebase	boolean	DEFAULT false
is_ops	boolean	DEFAULT false
ops_created_by	uuid	FOREIGN KEY → public.user(id)

public.school_data

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
school_name	text	NULL
district	text	NULL
village	text	NULL
udise_code	text	NULL
state	text	NULL
block	text	NULL
cluster	text	NULL
created_at	timestamp with time zone	NOT NULL, DEFAULT now()
updated_at	timestamp without time zone	DEFAULT now()
instruction_medium	text	NULL
total_teachers	integer	NULL
pre_primary_section_available	boolean	NULL
school_type	text	NULL
classes	text	NULL
head_teachers	text	NULL
head_teacher	text	NULL
country	text	DEFAULT 'India'

public.school_user

Column Name	Data Type	Constraints / Default
school_id	uuid	NOT NULL, FOREIGN KEY → public.school(id)
user_id	uuid	NOT NULL, FOREIGN KEY → public.user(id)
role	USER-DEFINED	NOT NULL
created_at	timestamp with time zone	NOT NULL, DEFAULT now()
updated_at	timestamp with time zone	DEFAULT now()
is_deleted	boolean	DEFAULT false
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
is_firebase	boolean	DEFAULT false
is_ops	boolean	DEFAULT false
ops_created_by	uuid	FOREIGN KEY → public.user(id)

public.special_users

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, 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 → public.user(id), DEFAULT auth.uid()
is_deleted	boolean	NULL
role	USER-DEFINED	NULL

public.sticker

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
name	character varying	NOT NULL
image	text	NULL
description	text	NULL
created_at	timestamp with time zone	NOT NULL, DEFAULT now()
updated_at	timestamp with time zone	DEFAULT now()
is_deleted	boolean	DEFAULT false

public.subject

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
name	character varying	NOT NULL
image	text	NULL
description	text	NULL
sort_index	integer	NULL
created_at	timestamp with time zone	NOT NULL, DEFAULT now()
updated_at	timestamp with time zone	DEFAULT now()
is_deleted	boolean	DEFAULT false

public.upload_queue

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
uploading_user	uuid	FOREIGN KEY → public.user(id)
start_time	timestamp with time zone	NULL
payload	jsonb	NOT NULL
status	text	DEFAULT 'pending'
error	text	NULL
process_started_at	timestamp with time zone	DEFAULT now()
batch_number	bigint	NULL
is_locked	boolean	DEFAULT false
locked_at	timestamp with time zone	NULL
locked_by	text	NULL

public.user

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
name	text	NULL
email	text	NULL
phone	text	NULL
gender	text	NULL
image	text	NULL
avatar	text	NULL
language_id	uuid	FOREIGN KEY → public.language(id)
curriculum_id	uuid	FOREIGN KEY → 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
is_tc_accepted	boolean	DEFAULT false
age	integer	NULL
grade_id	uuid	FOREIGN KEY → public.grade(id)
music_off	boolean	DEFAULT false
sfx_off	boolean	DEFAULT false
fcm_token	text	NULL
student_id	text	NULL
firebase_id	text	NULL
is_firebase	boolean	DEFAULT false
is_ops	boolean	DEFAULT false
stars	integer	NULL
learning_path	text	NULL
ops_created_by	uuid	FOREIGN KEY → public.user(id)

public.user_badge

Column Name	Data Type	Constraints / Default
user_id	uuid	NOT NULL, FOREIGN KEY → public.user(id)
badge_id	uuid	NOT NULL, FOREIGN KEY → public.badge(id)
created_at	timestamp with time zone	NOT NULL, DEFAULT now()
updated_at	timestamp with time zone	DEFAULT now()
is_deleted	boolean	DEFAULT false
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
is_seen	boolean	NULL
is_firebase	boolean	DEFAULT false

public.user_bonus

Column Name	Data Type	Constraints / Default
user_id	uuid	NOT NULL, FOREIGN KEY → public.user(id)
bonus_id	uuid	NOT NULL, FOREIGN KEY → public.lesson(id)
created_at	timestamp with time zone	NOT NULL, DEFAULT now()
updated_at	timestamp with time zone	DEFAULT now()
is_deleted	boolean	DEFAULT false
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
is_seen	boolean	NULL
is_firebase	boolean	DEFAULT false

public.user_course

Column Name	Data Type	Constraints / Default
user_id	uuid	NOT NULL, FOREIGN KEY → public.user(id)
course_id	uuid	NOT NULL, FOREIGN KEY → 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
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
is_firebase	boolean	DEFAULT false

public.user_sticker

Column Name	Data Type	Constraints / Default
user_id	uuid	NOT NULL, FOREIGN KEY → public.user(id)
sticker_id	uuid	NOT NULL, FOREIGN KEY → public.sticker(id)
created_at	timestamp with time zone	NOT NULL, DEFAULT now()
updated_at	timestamp with time zone	DEFAULT now()
is_deleted	boolean	DEFAULT false
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
is_seen	boolean	NULL
is_firebase	boolean	DEFAULT false

2. Database Tables Explained

public.announcement

Stores announcements to be sent to users — for example, notifications about new assignments, upcoming quizzes, reminders.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid().
  Unique ID for each announcement.

• title — text — Short headline for the announcement.
  Example: "Holi Announcement ✅✅✅"

• message — text — Detailed body or content of the announcement.
  Example: "On the event of Holi, we plan to celebrate in the Stadium."

• type — text — Category or purpose of the announcement, such as 'assignment', 'liveQuiz'.

• image_url — text — URL for an optional image to display with the announcement.
  Example: "https://upload.wikimedia.org/wikipedia/commons/thumb/6/6e/Golde33443.jpg/640px-Golde33443.jpg"

• custom_icon — text — Identifier for a custom icon to use instead of a default icon.
  Example: "ic_stat_test_icon_for_19feb_v_25"

• created_at — timestamp with time zone — NOT NULL, Default: now().
  Timestamp indicating when the announcement was created.

• send_at — timestamp with time zone — Optional scheduled date/time when the announcement should be sent or become visible.

• target_ids — ARRAY — Array of user IDs or group IDs that should receive this announcement.
  Example: [ "ccc55fb6-65cd-40f3-8301-91a4d9aa9bec", "59c52dbd-f920-4873-b548-6b4ebaa17cb1", "ae267303-6afb-4cab-bce9-6109bca82ed8" ]

---

public.assignment

Stores assignments given to students. Assignments can be regular homework, live quizzes, or any activity that students need to complete within a time window.

This table makes it easy for schools and teachers to track who created the assignment, who should receive it, and what lesson or course it belongs to.

---

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid().
  Unique ID for each assignment record.

• created_by — uuid — Foreign Key → public.user(id).
  ID of the teacher or admin who created the assignment.

• starts_at — timestamp with time zone — NOT NULL, Default: now().
  The date and time when the assignment becomes active and visible to students.

• ends_at — timestamp with time zone — Due date for the assignment.
  Defines when the assignment must be played by the student. After this timestamp, the assignment is considered closed and students can no longer play the assignment.

• is_class_wise — boolean — NOT NULL, Default: false.
  If true, the assignment is sent to an entire class; if false, it can be sent to specific students only.

• class_id — uuid — NOT NULL, Foreign Key → public.class(id).
  The class this assignment is linked to.

• school_id — uuid — NOT NULL, Foreign Key → public.school(id).
  The school that owns this assignment record.

• lesson_id — uuid — NOT NULL, Foreign Key → public.lesson(id).
  The lesson content that this assignment refers to.

• type — text — Type or category of assignment.
  Examples include 'assignment', 'liveQuiz'.

• created_at — timestamp with time zone — NOT NULL, Default: now().
  The timestamp when the assignment was created.

• updated_at — timestamp with time zone — Default: now().
  Tracks the last time this assignment was updated.

• is_deleted — boolean — Default: false.
  Soft-delete flag — if true, the assignment is hidden but not permanently removed from the database.

• chapter_id — uuid — Foreign Key → public.chapter(id).
  The chapter to which this assignment belongs, if applicable.

• course_id — uuid — Foreign Key → public.course(id).
  The course related to this assignment, if relevant.

• source — text — The source of creation for the assignment, e.g., 'chatbot'.

• firebase_id — text — ID of the assignment in Firebase, if this assignment was synced from Firebase.

• is_firebase — boolean — Default: false.
  true if this record was imported or synced from Firebase.

---

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	FK → public.user(id). The user associated with the program.
program_id	uuid	NOT NULL, FK → 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	FK → 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	FK → public.assignment(id). The assignment context, if any.
lesson_id	uuid	FK → public.lesson(id). The lesson that was played.
school_id	uuid	FK → public.school(id). The school of the student at the time of play.
score	integer	The score achieved.
student_id	uuid	NOT NULL, FK → 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	FK → public.course(id).
chapter_id	uuid	FK → public.chapter(id).
class_id	uuid	FK → public.class(id).
firebase_id	text	ID from Firebase, if applicable.
is_firebase	boolean	Default: false. True if synced from Firebase.

---

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	FK → 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	FK → 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, FK → public.school(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. 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	FK → public.user(id). The ops user who created the record.

---

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, FK → public.school(id).
user_id	uuid	NOT NULL, FK → 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	FK → 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	FK → 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	FK → public.user(id). 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 (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	FK → public.language(id). The user's preferred language.
curriculum_id	uuid	FK → 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	FK → 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	FK → public.user(id). 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, FK → public.user(id)
badge_id	uuid	NOT NULL, FK → 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, FK → public.user(id)
bonus_id	uuid	NOT NULL, FK → 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, FK → public.user(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. 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, FK → public.user(id)
sticker_id	uuid	NOT NULL, FK → 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.