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
batch_id	text	NULL

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.rive_reward

Column Name	Data Type	Constraints / Default
id	uuid	NOT NULL, PRIMARY KEY, DEFAULT gen_random_uuid()
accessory_name	text	NOT NULL
state_machine	text	NULL
state_number_input	integer	NULL
animation_name	text	NOT NULL
is_deleted	boolean	DEFAULT false
created_at	timestamp without time zone	DEFAULT now()
updated_at	timestamp without time zone	DEFAULT now()
state_input_name	text	NULL
type	public.rive_type	NOT NULL, DEFAULT 'normal'::rive_type
max_state_value	bigint	NOT NULL, DEFAULT 8

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_id	uuid	FOREIGN KEY → public.language(id)
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)
status	USER-DEFINED	NULL
key_contacts	jsonb	NULL
whatsapp_bot_number	text	NULL

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	public.user(id)
reward	text	NULL

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 class 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.

• batch_id — text — Optional batch identifier for grouped assignments.
  Used to group multiple assignments that were created in one action. Enables tracking assignment sets and generating deep links that point to grouped assignments.

---

public.assignment_cart

Temporary storage for a user’s selected lessons before assigning them — like a draft workspace for a teacher.

• id — uuid — Unique ID for this cart. Also FK → public.user(id); links the cart to its owner.
• created_at — timestamp with time zone — When the cart was created.
• is_deleted — boolean — Marks the cart as soft-deleted if true.
• lessons — text — Stores the list of lesson IDs in the cart (usually a JSON array).
• updated_at — timestamp without time zone — Last time the cart was updated.
• is_firebase — boolean — true if the cart was synced from Firebase.

---

public.assignment_user

Links an assignment to individual students — tracks which student gets which work.

• id — uuid — Unique ID for this link record.
• assignment_id — uuid — FK → public.assignment(id); the assignment being given.
• user_id — uuid — FK → public.user(id); the student’s ID — the student who the teacher assigned this assignment to.
• created_at — timestamp with time zone — When this link was created.
• updated_at — timestamp with time zone — When this link was last updated.
• is_deleted — boolean — Soft-delete flag.
• is_firebase — boolean — true if synced from Firebase.

---

public.auto_chatbot

Stores automated chatbot tasks — used for scheduling tasks and messages school-wise.

• id — uuid — Unique ID for this chatbot task.
• school_id — uuid — FK → public.school(id); identifies which school this task belongs to for scheduling.
• task_data — jsonb — Stores the JSON details for the chatbot’s actions for that school — like timing, content, and rules.
  Example: [ {"tasks": {"0": ["sendReports", "reminder"], "1": ["assign", "sendReports"], "2": ["sendReports"], "3": ["assign"], "4": [], "5": [], "6": []}, "assignPerDay": null, "maxAssignmentPerDay": 8, "programDurationInWeeks": 10}]
• created_at — timestamp with time zone — When the task was created.
• updated_at — timestamp with time zone — When it was last updated.
• is_deleted — boolean — Marks the task as soft-deleted if true.

---

public.badge

Stores details about badges for gamification — to reward students.

• id — uuid — Unique ID for this badge.
• name — character varying — Badge name — what the badge is called.
• image — text — URL for the badge image.
• description — text — Description about the badge — what it means or how to earn it.
• created_at — timestamp with time zone — When the badge was created.
• updated_at — timestamp with time zone — When it was last updated.
• is_deleted — boolean — Soft-delete flag.

---

public.chapter

Stores course chapters — each chapter is a section of a course that contains multiple lessons.

• id — uuid
  Unique ID for the chapter.
  Primary Key, NOT NULL, Default: gen_random_uuid().
  Example: 5f7e5b88-1b62-4f27-b6ec-7a9bbcd90b22

• name — text
  Title of the chapter.
  Example: "Fractions and Decimals"

• image — text
  URL of an image representing the chapter.
  Example: https://cdn.example.com/images/chapter1.png

• course_id — uuid
  References the course this chapter belongs to.
  Foreign Key → public.course(id)
  Example: c9a8f8d2-6e34-4c12-9b0f-3e60b6b9e8ab

• sort_index — integer
  Defines the order of the chapter within the course.
  Starts from 0 (first chapter = 0, second = 1, ...).
  Example: 0

• sub_topics — text
  Serialized list of sub-topics under this chapter.
  Example: ["Fractions Basics", "Adding Fractions"]

• created_at — timestamp with time zone
  When the chapter was created.
  NOT NULL, Default: now()
  Example: 2025-07-02T12:34:56Z

• updated_at — timestamp with time zone
  When the chapter was last updated.
  Default: now()
  Example: 2025-07-02T12:34:56Z

• is_deleted — boolean
  Soft delete flag — true means this chapter is hidden but not removed.
  Default: false

---

public.chapter_lesson

Links lessons to chapters — defines which lessons belong to which chapters, and in what order.

• id — uuid
  Unique ID for this mapping.
  Primary Key, NOT NULL, Default: gen_random_uuid()
  Example: 1c2e7d2f-6df2-42a8-b4ee-61eb2329fabb

• lesson_id — uuid
  Lesson being linked to the chapter.
  NOT NULL, Foreign Key → public.lesson(id)
  Example: 9b91b22e-4668-4ae1-8f26-123b5e4c5678

• chapter_id — uuid
  Chapter the lesson is linked to.
  NOT NULL, Foreign Key → public.chapter(id)
  Example: 5f7e5b88-1b62-4f27-b6ec-7a9bbcd90b22

• sort_index — integer
  Defines the lesson’s order within the chapter.
  Starts from 0 (first lesson = 0).
  Example: 1

• created_at — timestamp with time zone
  When this link was created.
  NOT NULL, Default: now()
  Example: 2025-07-02T12:34:56Z

• updated_at — timestamp with time zone
  When this link was last updated.
  Default: now()
  Example: 2025-07-02T12:34:56Z

• is_deleted — boolean
  Soft delete flag — true means the record is marked as deleted (logically removed) but is not physically deleted from the database.
  Default: false

---

public.chapter_links

Stores external links related to chapters — for example, QR links.

• id — uuid
  Unique ID for the external link.
  Primary Key, NOT NULL, Default: gen_random_uuid()
  Example: 2e46b055-f42b-43b8-9b2c-9f1e5e84ec98

• created_at — timestamp with time zone
  When the link was created.
  NOT NULL, Default: now()
  Example: 2025-07-02T12:34:56Z

• chapter_id — uuid
  Chapter this link is associated with.
  Foreign Key → public.chapter(id)
  Example: 5f7e5b88-1b62-4f27-b6ec-7a9bbcd90b22

• link — text
  The external URL or resource link.
  Example: https://www.youtube.com/watch?v=dQw4w9WgXcQ

• curriculum_id — uuid
  The curriculum this link and chapter is related to.
  Foreign Key → public.curriculum(id)
  Example: f8c2765a-5d1d-47d5-8d47-1e897e3219e3

• course_id — uuid
  The course this link and chapter is related to.
  Foreign Key → public.course(id)
  Example: c9a8f8d2-6e34-4c12-9b0f-3e60b6b9e8ab

• grade_id — uuid
  The grade level this link is related to.
  Foreign Key → public.grade(id)
  Example: 2a6db1c3-b421-4dc5-8ee0-71d1f65bd2e0

• updated_at — timestamp without time zone
  When the link was last updated.
  Default: now()
  Example: 2025-07-02T12:34:56Z

• is_deleted — boolean
  Soft delete flag — true means the record is marked as deleted (logically removed) but is not physically deleted from the database.
  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(Example., 919876543210).

---

public.class

Stores each class within a school.
Classes must follow the naming convention: number + optional section — for example, '1', '1A', '2B'.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid().
  Unique ID for the class.
• name — text — NOT NULL. Name of the class, following the format above.
  Example: '1', '1A', '2B'
• image — text — Optional image/icon URL for the class.
  Example: 'https://cdn.schoolapp.com/class-1A.png'
• school_id — uuid — NOT NULL. FK → public.school(id).
  Which school owns this class.
• created_at — timestamp with time zone — NOT NULL, Default: now().
  When the class was created.
• updated_at — timestamp with time zone — Default: now().
  Last updated timestamp.
• is_deleted — boolean — Default: false.
  Soft delete — true means the class is hidden but remains in the DB.
• firebase_id — text — If synced with Firebase, the Firebase ID.
• is_firebase — boolean — Default: false.
  true if imported/synced from Firebase.
• status — text — Default: 'active'.
  Example: 'active' means currently running; 'migrated' means the class has moved to the next academic year, and a new 'active' class replaces it.
• is_ops — boolean — Default: false.
  Marks if the class was created by the operations/admin team.
• standard — text — The grade level for this class.
  Example: '1', '2'
• academic_year — text — The academic year this class belongs to.
  Example: '2024-2025'
• ops_created_by — uuid — FK → public.user(id).
  Which ops user created it.
• group_id — text — WhatsApp group ID for this class.
  Example: '12034567890-123456789@g.us'

---

public.class_course

Connects a class to its courses.
For example, Class 1A → English, Maths, Science.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid().
• class_id — uuid — NOT NULL. FK → public.class(id).
  The class that has this course.
• course_id — uuid — NOT NULL. FK → public.course(id).
  The course being taught.
• 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).
  The ops/admin user who linked this course.

---

public.class_invite_code

Holds invite codes students can use to join a class.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid().
• code — integer — NOT NULL. 6-digit numeric code.
  Students enter this to join a class.
  Example: 123456
• expires_at — timestamp with time zone — NOT NULL. When the code expires.
  After this time, the code won’t work.
• class_id — uuid — NOT NULL. FK → public.class(id).
  The class this code is for.
• is_class_code — boolean — Default: true. Marks that this code is class-level.
• 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

Maps users to classes, defining what role they play.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid().
• class_id — uuid — NOT NULL. FK → public.class(id).
  The class they belong to.
• user_id — uuid — NOT NULL. FK → public.user(id).
  The user — could be a student, teacher, or parent.
• role — USER-DEFINED — NOT NULL. Defines the user’s role in this class.
  Examples:
  • 'student' → a learner enrolled in this class
  • 'teacher' → the teacher who manages this class
  • 'parent' → linked parent/guardian for a student
• created_at — timestamp with time zone — Default: now(). When they were linked.
• updated_at — timestamp with time zone — Default: now(). Last update.
• is_deleted — boolean — Default: false. Soft delete flag.
• is_firebase — boolean — Default: false. true means the record came from Firebase.
• is_ops — boolean — Default: false. Marks if added by ops team.
• ops_created_by — uuid — FK → public.user(id).
  Ops user who added the record, if applicable.

Example:
A student Amit (user_id) is linked as 'student' to Class 1A.
His mother Meera (user_id) might be linked with the same class_id but as 'parent'.
The teacher Mrs. Sharma (user_id) is linked with the same class_id as 'teacher'.

---

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 which is there in user table(Example., abc@gmail.com).
• created_at — timestamp with time zone — Default: now()

---

public.course

Stores details about courses — each course groups chapters and lessons under a subject and grade.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid().
  Unique ID for this course.
• name — character varying — NOT NULL. Name of the course.
  Example: 'English', 'Maths'
• image — text — URL for an optional course image.
  Example: 'https://cdn.schoolapp.com/courses/english-5.png'
• description — text — Short description of what the course covers.
  Example: 'Covers grammar, comprehension, and writing skills for Grade 5.'
• sort_index — integer — Used to order courses in the app UI.
  Starts from 0 and increments.
• subject_id — uuid — FK → public.subject(id).
  Links to the subject this course belongs to.
• grade_id — uuid — FK → public.grade(id).
  Defines which grade/standard this course is for.
• curriculum_id — uuid — FK → public.curriculum(id).
  Which curriculum this course follows.
• created_at — timestamp with time zone — NOT NULL, Default: now().
  When this course was created.
• updated_at — timestamp with time zone — Default: now().
  Last time the course was updated.
• is_deleted — boolean — Default: false.
  true means soft-deleted — but still in the database.
• color — text — A hex color code for UI styling.
  Example: '#FF5733'
• code — text — Unique short code for the course.
  Example: 'ENG-G5-2025'
• firebase_id — text — Unique ID if this course was synced from Firebase.

---

public.curriculum

Stores details about educational curricula — for example, NCERT, State Board, or Karnataka.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid().
  Unique ID for the curriculum.
• name — character varying — NOT NULL. Name of the curriculum.
  Example: 'NCERT', 'Maharashtra State Board'
• image — text — URL for an optional icon or logo.
  Example: 'https://cdn.schoolapp.com/curricula/ncert.png'
• description — text — Short description about this curriculum.
  Example: 'Central Board syllabus for Indian schools.'
• sort_index — integer — Used for ordering in UI lists.
  Starts at 0 and increments.
• created_at — timestamp with time zone — NOT NULL, Default: now().
  When the curriculum was added.
• updated_at — timestamp with time zone — Default: now().
  Last time it was updated.
• is_deleted — boolean — Default: false.
  true means the record is soft-deleted.
• firebase_id — text — Firebase ID if synced.

---

public.favorite_lesson

Maps which lessons a user has marked as favorite — for faster access later.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid().
  Unique ID for the favorite record.
• lesson_id — uuid — NOT NULL. FK → public.lesson(id).
  Lesson that’s marked favorite.
  Example: '9b1b2a73-54d3-4321-b1a2-7e9b2a3c4f5d'
• user_id — uuid — NOT NULL. FK → public.user(id).
  User who favorited this lesson.
• created_at — timestamp with time zone — Default: now().
  When it was favorited.
• updated_at — timestamp with time zone — Default: now().
  When last updated.
• is_deleted — boolean — Default: false.
  true = soft deleted, still stored.
• is_firebase — boolean — Default: false.
  If synced from Firebase.

---

public.grade

Stores school grades — Grade 1, Grade 2 etc.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid().
  Unique grade ID.
• name — character varying — NOT NULL. Grade name.
  Example: 'Grade 2'
• image — text — URL for a grade icon.
  Example: 'https://cdn.schoolapp.com/grades/grade-2.png'
• description — text — Short description.
  Example: 'Basic level for students aged 6-7'
• sort_index — integer — UI order. Starts from 0.
  Example: 0 for Grade 1, 1 for Grade 2.
• created_at — timestamp with time zone — NOT NULL, Default: now().
  When created.
• updated_at — timestamp with time zone — Default: now().
  Last update timestamp.
• is_deleted — boolean — Default: false.
  true means hidden.
• test — text — Optional test/debug field.
• firebase_id — text — Firebase ID if synced.

---

public.issue_debug

Helps devs track bugs — stores debug data for user-reported issues.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid().
  Unique ID for the debug record.
• created_at — timestamp with time zone — NOT NULL, Default: now().
  When the log was created.
• student_id — text — ID of the affected student.
  Example: 'ab959b86-fb41-494b-89d9-99f71abc0828'
• result_id — text — Related result ID.
  Example: 'a8d5414c-6d9c-4eae-9cd7-a70782ebba08'
• firebase_id — text — Firebase ID if synced.
• is_firebase — boolean — true = synced from Firebase.

---

public.language

Defines all languages offered for content, quizzes, or the app itself.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid().
  Unique ID for the language.
• name — character varying — NOT NULL. Language name.
  Example: 'English', 'ಕನ್ನಡ'
• image — text — Flag or icon.
  Example: 'https://cdn.schoolapp.com/flags/en.png'
• description — text — Short info.
  Example: 'English language for all modules'
• sort_index — integer — UI display order. Starts from 0.
  Example: 0 for 'English', 1 for 'Hindi'.
• created_at — timestamp with time zone — NOT NULL, Default: now().
  When added.
• updated_at — timestamp with time zone — Default: now().
  Last update timestamp.
• is_deleted — boolean — Default: false.
  Soft delete flag.
• code — text — Language code (ISO).
  Example: 'en', 'hi', 'kn'
• firebase_id — text — Firebase ID if any.

---

public.lesson

Stores lessons — e.g., alphabets, addition, activities for students.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid().
  Unique lesson ID.
• name — text — Name of the lesson.
  Example: 'Addition Basics'
• image — text — URL for thumbnail.
  Example: 'https://cdn.schoolapp.com/lessons/addition-basics.png'
• outcome — text — Learning goal.
  Example: 'Students understand basic addition up to 20'
• plugin_type — text — How it runs.
  Example: 'cocos'
• status — text — Status flag.
  Example: 'approved'
• cocos_subject_code — text — Cocos-specific code. Example: 'en'
• cocos_chapter_code — text — Cocos-specific code. Example: 'en00'
• created_by — text — Author or creator.
  Example: 'teacher_abc'
• subject_id — uuid — FK → public.subject(id).
  Subject it belongs to.
• target_age_from — integer — Minimum age.
  Example: 5
• target_age_to — integer — Maximum age.
  Example: 7
• language_id — uuid — FK → public.language(id).
  Lesson language.
• created_at — timestamp with time zone — NOT NULL, Default: now().
  Created on.
• updated_at — timestamp with time zone — Default: now().
  Last updated.
• is_deleted — boolean — Default: false.
  Soft delete flag.
• cocos_lesson_id — text — Cocos ID if linked. Example: 'en0005'
• color — text — UI color hex.
  Example: '#33CCFF'

---

public.live_quiz_room

Stores live quiz room details for students doing quizzes together or individual.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid().
  Unique quiz room ID.
• created_at — timestamp with time zone — Default: now().
  When quiz room was created.
• results — jsonb — JSON result map.
  Example: { "student1": 10, "student2": 8 }
• updated_at — timestamp with time zone — Last updated timestamp.
• assignment_id — uuid — NOT NULL. FK → public.assignment(id).
  Assignment or quiz this room or session belongs to.
• class_id — uuid — NOT NULL. FK → public.class(id).
  Class doing this quiz.
• course_id — uuid — NOT NULL. FK → public.course(id).
  Related course.
• lesson_id — uuid — NOT NULL. FK → public.lesson(id).
  Lesson of the quiz.
• participants — ARRAY — List of participant IDs.
  Example: ['d0f73c0f-cf9e-4676-b147-b87104e5708f', 'd0f73c0f-cf9e-4676-b147-b87104e5708f']
• is_deleted — boolean — Default: false.
  Soft delete flag.
• school_id — uuid — NOT NULL. FK → public.school(id).
  School that ran the quiz.
• starts_at — timestamp with time zone — NOT NULL.
  Start time of the quiz room.
  Example: '2025-07-03T12:00:00Z'

---

public.parent_user

Links parent users to their children (students).
One row means one parent–child relationship.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  Unique ID for this parent-child link. Example: 'b1a2c3d4-5678-90ef-abcd-1234567890ab'

• parent_id — uuid — NOT NULL. FK → (parent) public.user(id).
  The parent’s user ID.
  Example: '1e2d3c4b-5678-90ab-cdef-1234567890ab'

• student_id — uuid — NOT NULL. FK → (student) public.user(id).
  The child’s user ID.
  Example: '2f3e4d5c-6789-01bc-def0-2345678901bc'

• created_at — timestamp with time zone — Default: now()
  When this parent-child link was created. Example: '2024-07-01T12:00:00+00:00'

• updated_at — timestamp with time zone — Default: now()
  Last update time for this link. Example: '2024-07-02T09:30:00+00:00'

• is_deleted — boolean — Default: false
  Soft delete flag. true means hidden, not removed from DB. Example: false

• is_firebase — boolean — Default: false
true means synced from Firebase. Example: false

• is_ops — boolean — Default: false
true if created by an operations user. Example: false

• ops_created_by — uuid — FK → public.user(id)(https://github.com/chimple/cuba/wiki/DB-Schema#publicuser)
  The ops user who created this link. Example: '3c4d5e6f-7890-12ab-cdef-3456789012cd'

---

public.program

Stores information about high-level educational programs or initiatives.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  Unique ID for this program.
  Example: 'a1b2c3d4-5678-90ab-cdef-1234567890ab'

• name — text — NOT NULL. The name of the program.
  Example: 'Digital Learning Initiative 2024'

• program_type — USER-DEFINED — The type of program.
  Example: 'Government', 'CSR', 'private'

• implementation_partner — text — Organization that implements the program.
  Example: 'ABC Foundation'

• funding_partner — text — Organization providing funding.
  Example: 'XYZ Bank CSR'

• institute_partner — text — Related educational institute or training partner.
  Example: 'DEF University'

• country — text — Country where the program is active.
  Example: 'India'

• state — text — State/region of the program.
  Example: 'Karnataka'

• district — text — District of operation.
  Example: 'Bangalore Urban'

• block — text — Sub-district block.
  Example: 'Block A'

• cluster — text — Specific cluster under the block.
  Example: 'Cluster 3'

• village — text — Village or community covered.
  Example: 'Marathahalli'

• institutes_count — text — Number of institutes covered.
  Example: '15'

• students_count — text — Number of students enrolled.
  Example: '2500'

• devices_count — text — Number of devices deployed.
  Example: '500'

• start_date — date — Start date of the program.
  Example: '2024-06-01'

• end_date — date — End date of the program.
  Example: '2025-05-31'

• created_at — timestamp with time zone — NOT NULL, Default: now()
  When the program record was created. Example: '2024-07-01T12:00:00+00:00'

• updated_at — timestamp with time zone — NOT NULL, Default: now()
  Last updated. Example: '2024-07-02T12:00:00+00:00'

• is_deleted — boolean — Soft delete flag.
  true means hidden but not removed. Example: false

• is_ops — boolean — true if created by ops user. Example: false

• model — text — The operating model or delivery model.
  Example: 'Hybrid'

---

public.program_user

Links users to specific programs and defines their role in that program.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  Unique ID for this link.
  Example: '9f8e7d6c-5432-10ab-cdef-0987654321ab'

• user — uuid — FK → public.user(id)
  The user who is part of the program.
  Example: '123e4567-e89b-12d3-a456-426614174000'

• program_id — uuid — NOT NULL. FK → public.program(id)
  The program they are linked to.
  Example: 'a1b2c3d4-5678-90ab-cdef-1234567890ab'

• created_at — timestamp with time zone — NOT NULL, Default: now()
  When this link was created.
  Example: '2024-07-01T12:00:00+00:00'

• updated_at — timestamp with time zone — NOT NULL, Default: now()
  When this link was last updated.
  Example: '2024-07-02T12:30:00+00:00'

• role — USER-DEFINED — User’s role within the program.
  Example: 'program_manager', 'field_coordinator'

  program_manager — A manager responsible for a program.

  • Has access only to the schools that are connected to the program they manage
  • Cannot access schools outside of the program scope

  field_coordinator — A coordinator working under a program.

  • Assigned to specific schools within a block
  • Has access only to the schools they are directly assigned to

• is_deleted — boolean — NOT NULL, Default: false
  Soft delete flag — true means marked deleted but kept for history.
  Example: false

• is_ops — boolean — true if this link was added by an ops user.
  Example: false

---

public.req_new_school

Stores requests from users to add a new school — used for moderation before approval.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  Unique ID for this request.
  Example: 'abc12345-def6-7890-gh12-ijklmnopqrst'

• user_id — uuid — FK → public.user(id)
  The user who made the request.
  Example: '123e4567-e89b-12d3-a456-426614174000'

• name — text — Name of the requested school.
  Example: 'Government Primary School No. 5'

• state — text — State where the school is located.
  Example: 'Maharashtra'

• district — text — District where the school is located.
  Example: 'Pune'

• city — text — City or town of the school.
  Example: 'Pimpri-Chinchwad'

• udise_id — text — The UDISE ID if provided.
  Example: '27251400105'

• is_resolved — boolean — Default: false
true means this request has been reviewed and handled.
  Example: false

• created_at — timestamp without time zone — Default: now()
  When the request was made.
  Example: '2024-07-02T09:15:00'

• updated_at — timestamp without time zone — Default: now()
  When the request was last updated.
  Example: '2024-07-03T10:00:00'

• is_deleted — boolean — Default: false
  Soft delete — if true, this request is hidden but not removed.
  Example: false

• image — text — URL to an image or document uploaded with the request.
  Example: 'https://mycdn.com/uploads/request-doc.png'

---

public.result

Stores the outcome of a student’s lesson or assignment attempt — tracks score, time, and performance.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  Unique result record ID.
  Example: '789e1234-ab56-7890-cd12-efgh34567890'

• assignment_id — uuid — FK → public.assignment(id)
  Assignment this result belongs to (if applicable).
  Example: '111e2222-ab33-4444-bc55-666677778888'

• lesson_id — uuid — FK → public.lesson(id)
  The lesson the student played.
  Example: '222e3333-bb44-5555-cc66-777788889999'

• school_id — uuid — FK → public.school(id)
  School the student belonged to during this attempt.
  Example: '333e4444-cc55-6666-dd77-888899990000'

• score — integer — The score achieved by the student.
  Example: 85

• student_id — uuid — NOT NULL, FK → public.user(id)
  The student who attempted the lesson.
  Example: '444e5555-dd66-7777-ee88-999900001111'

• correct_moves — integer — Number of correct actions/answers.
  Example: 15

• time_spent — integer — Total time spent, in seconds.
  Example: 300 (5 minutes)

• wrong_moves — integer — Number of wrong answers/moves.
  Example: 2

• created_at — timestamp with time zone — NOT NULL, Default: now()
  When this result was recorded.
  Example: '2024-07-02T11:45:00Z'

• updated_at — timestamp with time zone — Default: now()
  When this result was last updated.
  Example: '2024-07-02T11:50:00Z'

• is_deleted — boolean — Default: false
  Soft delete flag — true means hidden, not removed.
  Example: false

• course_id — uuid — FK → public.course(id)
  Related course.
  Example: '555e6666-ee77-8888-ff99-000011112222'

• chapter_id — uuid — FK → public.chapter(id)
  Related chapter.
  Example: '666e7777-ff88-9999-aa00-111122223333'

• class_id — uuid — FK → public.class(id)
  Class the student was enrolled in.
  Example: '777e8888-gg99-0000-bb11-222233334444'

• firebase_id — text — Firebase ID if synced.
  Example: 'firebase_result_123456'

• is_firebase — boolean — Default: false
true if this result came from Firebase sync.
  Example: true

---

public.reward

Stores periodic reward info for gamification — like weekly/monthly rewards.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  Unique reward record ID.
  Example: 'abc12345-def6-7890-gh12-ijk345678901'

• created_at — timestamp with time zone — NOT NULL, Default: now()
  When this reward entry was created.
  Example: '2025-07-02T09:00:00Z'

• updated_at — timestamp with time zone — Default: now()
  Last time this reward entry was updated.
  Example: '2025-07-02T09:30:00Z'

• is_deleted — boolean — Default: false
  Soft delete flag — true means hidden, not removed.
  Example: false

• monthly — text — Info or ID for the monthly bonus reward.
  Example: {"monthly": {"1": [{"id": "0bddcc47-1d7d-438d-a612-a7ba233cb205", "type": "bonus"}], "2": [{"id": "49603369-0db7-4949-bdfb-7d3b4697eab3", "type": "bonus"}], "3": [{"id": "e538cc03-b136-48b2-9af8-45f7a425311b", "type": "bonus"}], "4": [{"id": "e5f0a1fa-6058-4703-aaa4-ad5c46b37261", "type": "bonus"}], "5": [{"id": "510f02bb-ecb3-4320-b7c1-1be81bf94013", "type": "bonus"}], "6": [{"id": "b06fa520-72e6-4ee3-9fd5-236640d30db2", "type": "bonus"}], "7": [{"id": "89a937d7-84da-484d-a494-1b3f1e91fef9", "type": "bonus"}], "8": [{"id": "94abe5bf-f7f4-4d31-a5cf-6795090e0246", "type": "bonus"}], "9": [{"id": "5e7eb19a-0cf0-4436-b4b1-f544ad90560e", "type": "bonus"}], "10": [{"id": "5f0b5e59-9268-4722-8a89-a8b3971d2b7b", "type": "bonus"}], "11": [{"id": "26105b58-8872-4c5d-986b-fd08cc5632c4", "type": "bonus"}], "12": [{"id": "cf1be8c5-8422-404d-b49c-18224034bf92", "type": "bonus"}]}}}

• weekly — text — Info or ID for the weekly badge reward.
  Example: {"weekly": {"1": [{"id": "0bddcc47-1d7d-438d-a612-a7ba233cb205", "type": "badge"}], "2": [{"id": "49603369-0db7-4949-bdfb-7d3b4697eab3", "type": "badge"}], "3": [{"id": "e538cc03-b136-48b2-9af8-45f7a425311b", "type": "badge"}], "4": [{"id": "e5f0a1fa-6058-4703-aaa4-ad5c46b37261", "type": "badge"}], "5": [{"id": "510f02bb-ecb3-4320-b7c1-1be81bf94013", "type": "badge"}], "6": [{"id": "b06fa520-72e6-4ee3-9fd5-236640d30db2", "type": "badge"}], "7": [{"id": "89a937d7-84da-484d-a494-1b3f1e91fef9", "type": "badge"}], "8": [{"id": "94abe5bf-f7f4-4d31-a5cf-6795090e0246", "type": "badge"}], "9": [{"id": "5e7eb19a-0cf0-4436-b4b1-f544ad90560e", "type": "badge"}], "10": [{"id": "5f0b5e59-9268-4722-8a89-a8b3971d2b7b", "type": "badge"}], "11": [{"id": "26105b58-8872-4c5d-986b-fd08cc5632c4", "type": "badge"}], "12": [{"id": "cf1be8c5-8422-404d-b49c-18224034bf92", "type": "badge"}]}}}

• weeklySticker — text — Info or ID for the weekly sticker reward.
  Example: ' {"weeklySticker": {"1": [{"id": "0bddcc47-1d7d-438d-a612-a7ba233cb205", "type": "sticker"}], "2": [{"id": "49603369-0db7-4949-bdfb-7d3b4697eab3", "type": "sticker"}], "3": [{"id": "e538cc03-b136-48b2-9af8-45f7a425311b", "type": "sticker"}], "4": [{"id": "e5f0a1fa-6058-4703-aaa4-ad5c46b37261", "type": "sticker"}], "5": [{"id": "510f02bb-ecb3-4320-b7c1-1be81bf94013", "type": "sticker"}], "6": [{"id": "b06fa520-72e6-4ee3-9fd5-236640d30db2", "type": "sticker"}], "7": [{"id": "89a937d7-84da-484d-a494-1b3f1e91fef9", "type": "sticker"}], "8": [{"id": "94abe5bf-f7f4-4d31-a5cf-6795090e0246", "type": "sticker"}], "9": [{"id": "5e7eb19a-0cf0-4436-b4b1-f544ad90560e", "type": "sticker"}], "10": [{"id": "5f0b5e59-9268-4722-8a89-a8b3971d2b7b", "type": "sticker"}], "11": [{"id": "26105b58-8872-4c5d-986b-fd08cc5632c4", "type": "sticker"}], "12": [{"id": "cf1be8c5-8422-404d-b49c-18224034bf92", "type": "sticker"}]}}}'

• year — integer — NOT NULL. The year these rewards apply to.
  Example: 2025

---

public.rive_reward

Stores metadata for Rive animation rewards such as accessories, states, and animations used in the app.

• id — uuid — Primary Key, NOT NULL, Default: extensions.uuid_generate_v4()
  Unique identifier for each reward entry.
  Example: 'bccb729a-299b-48ff-af5a-5bd1e476dde6'

• accessory_name — text — NOT NULL
  Name of the accessory or item associated with the Rive animation.
  Example: 'YellowHat'

• state_machine — text
  Name of the Rive state machine controlling animation logic.
  Example: 'State Machine 3'

• state_number_input — integer
  Numeric input value representing a particular animation state.
  Example: 1

• animation_name — text — NOT NULL
  Name of the animation sequence used in Rive.
  Example: 'idh1'

• is_deleted — boolean — Default: false
  Marks a reward as soft-deleted without removing it from the database.
  Example: false

• created_at — timestamp without time zone — Default: now()
  Timestamp when the reward entry was created.
  Example: '2025-09-10 10:16:04.454505'

• updated_at — timestamp without time zone — Default: now()
  Timestamp when the reward entry was last updated.
  Example: '2025-09-10 10:16:04.454505'

• state_input_name — text
  The name of the state input in the Rive file, usually matching a numeric control parameter.
  Example: 'Number 2'

• type — public.rive_type — NOT NULL, Default: 'normal'::rive_type
  The category or type of reward, referencing a custom PostgreSQL enum (rive_type).
  Example: 'celebrating'

• max_state_value — bigint — NOT NULL, Default: 8
  The maximum possible state value used in the Rive animation input.
  Example: 31

---

public.saved_queries

Stores saved SQL queries — used by admins or devs to reuse handy scripts.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  Unique ID for the saved query.
  Example: 'xyz7890a-bc12-34de-fg56-hijk78901234'

• created_at — timestamp without time zone — Default: now()
  When this query was saved.
  Example: '2025-07-02 09:15:00'

• query — text — NOT NULL. The actual SQL text.
  Example: 'SELECT * FROM public.user WHERE is_deleted = false;'

---

public.school

Stores information about schools — a key entity for managing students, teachers, and classes.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  Unique school ID.
  Example: 'school-1234-5678-90ab-cdef12345678'

• name — text — NOT NULL. School name.
  Example: 'St. Mary High School'

• group1 — text — The state.
  Example: 'Maharashtra'

• group2 — text — The district.
  Example: 'Pune'

• group3 — text — The city.
  Example: 'Pimpri'

• image — text — URL of the school’s logo or photo.
  Example: 'https://cdn.myapp.com/schools/st_mary_logo.png'

• created_at — timestamp with time zone — NOT NULL, Default: now()
  When this school was added.
  Example: '2025-07-02T10:00:00Z'

• updated_at — timestamp with time zone — Default: now()
  Last update timestamp.
  Example: '2025-07-02T10:30:00Z'

• is_deleted — boolean — Default: false
  Soft delete flag — true means hidden, not removed.
  Example: false

• firebase_id — text — ID if synced with Firebase.
  Example: 'firebase_school_987654'

• is_firebase — boolean — Default: false
true if this record came from Firebase.
  Example: false

• udise — text — Official UDISE code for the school.
  Example: '27250400123'

• group4 — text — The village name, if applicable.
  Example: 'Akurdi'

• academic_year — text — The current academic year for this school.
  Example: '2025-2026'

• language — text — FK → public.language(id)
  The main language of instruction for the school.
  Example: 'English'

• student_login_type — USER-DEFINED — How students log in.
  Example: 'phone_number' or 'email'

• is_ops — boolean — Default: false
true if created by an operations user.
  Example: true

• program_id — uuid — FK → public.program(id)
  Program this school is linked to.
  Example: '1111-2222-3333-444455556666'

• model — USER-DEFINED — Operational model type.
  Example: 'at_home' or 'at_school'

• address — text — The full address of the school.
  Example: 'Plot 45, ABC Road, Pimpri, Pune - 411018'

• ops_created_by — uuid — FK → public.user(id)
  User ID of the operations person who created this record.
  Example: 'ops-9999-8888-7777-666655554444'

• status — USER-DEFINED — The status of the school whether the school is active, rejected or migrated. Example: 'active','rejected','requested','migrated'

• key_contacts — jsonb — Contact details of the school requested user.
  Example: '[{"name": "Ajax","phone": 9876543210}]'

• whatsapp_bot_number — text — Stores a whatsapp bot number for the school.
  Example: '919876543210'

---

public.school_course

Links which courses are available for a given school.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  Unique link ID.
  Example: 'uuid-1234-5678-90ab-cdef12345678'

• school_id — uuid — NOT NULL. FK → public.school(id)
  The school using this course.
  Example: 'school-uuid-1111-2222-3333-444455556666'

• course_id — uuid — NOT NULL. FK → public.course(id)
  The course offered by this school.
  Example: 'course-uuid-7777-8888-9999-0000aaaabbbb'

• created_at — timestamp with time zone — NOT NULL, Default: now()
  When this link was created.
  Example: '2025-07-02T11:00:00Z'

• updated_at — timestamp with time zone — Default: now()
  Last update.
  Example: '2025-07-02T12:00:00Z'

• is_deleted — boolean — Default: false
  Soft delete flag.
  Example: false

• is_firebase — boolean — Default: false
  True if synced from Firebase.
  Example: false

• is_ops — boolean — Default: false
  True if created by an operations user.
  Example: true

• ops_created_by — uuid — FK → public.user(id)
  The ops user who added this course link.
  Example: 'ops-user-uuid-9999-8888-7777-6666aaaa5555'

---

public.school_data

Stores detailed administrative and demographic data for schools, including UDISE linkage.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  Unique ID for this school info record.
  Example: '1234-5678-90ab-cdef12345678'

• school_name — text
  The official name of the school which is mentioned in school table.
  Example: 'Govt. Primary School, Tibda'

• district — text
  The district where the school is located.
  Example: 'Moradabad'

• village — text
  The village or locality of the school.
  Example: 'Patti'

• udise_code — text — UNIQUE. The UDISE code that uniquely identifies the school in government data.
  Example: '09120304501'

• state — text
  The state where the school operates.
  Example: 'Uttar Pradesh'

• block — text
  The block or sub-district region.
  Example: 'Bakharwa'

• cluster — text
  Cluster name for administrative grouping.
  Example: 'Garhi Cluster'

• created_at — timestamp with time zone — NOT NULL, Default: now()
  When this record was added.
  Example: '2025-07-02T11:00:00Z'

• updated_at — timestamp without time zone — Default: now()
  When this record was last updated.
  Example: '2025-07-02T12:00:00'

• instruction_medium — text
  Medium of instruction(s). May list multiple separated by commas.
  Example: 'ENGLISH, HINDI'

• total_teachers — integer
  Total number of teachers at this school.
  Example: 12

• pre_primary_section_available — boolean
  True if the school has a pre-primary section.
  Example: true

• school_type — text
  Type of school (e.g., 'Government', 'Private', 'Aided').
  Example: 'Government'

• classes — text
  Class levels offered, may be range or comma-separated.
  Example: '1-5' or 'LKG, 1, 2, 3, 4, 5'

• head_teachers — text
  Name(s) of head teachers, if multiple.
  Example: 'Mr. Ajay Kumar, Mrs. Neha Sharma'

• head_teacher — text
  The main head teacher’s name.
  Example: 'Mr. Ajay Kumar'

• country — text — Default: 'India'
  The country.
  Example: 'India'

---

public.school_user

Links a user to a school with a specific role (like principal, coordinator).

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  Unique ID for this link.
  Example: 'su-uuid-1234-5678-90ab-cdef98765432'

• school_id — uuid — NOT NULL. FK → public.school(id)
  The school the user is attached to.
  Example: 'school-uuid-abc1-2345-6789-defg12345678'

• user_id — uuid — NOT NULL. FK → public.user(id)
  The user assigned to the school.
  Example: 'user-uuid-1111-2222-3333-444455556666'

• role — USER-DEFINED — NOT NULL. The user’s role.
  Example: 'principal', 'coordinator', 'sponser', 'field_coordinator'

  principal — Head of the school.

  • Can create classes
  • Can add courses to classes
  • Can edit class-related details

  coordinator — Supports the principal.

  • Has the same access as principal (class creation, course management, and editing)

  sponsor — External supporter (individual or organization).

  • View-only access to school data and reports
  • Cannot create or edit classes/courses

  field_coordinator — Works across multiple schools.

  • Has the same access as principal, but restricted only to the schools they are assigned to

• created_at — timestamp with time zone — NOT NULL, Default: now()
  When this user was linked to the school.
  Example: '2025-07-02T11:30:00Z'

• updated_at — timestamp with time zone — Default: now()
  Last update.
  Example: '2025-07-02T12:30:00Z'

• is_deleted — boolean — Default: false
  Soft delete flag.
  Example: false

• is_firebase — boolean — Default: false
  True if synced from Firebase.
  Example: false

• is_ops — boolean — Default: false
  True if created by an operations user.
  Example: true

• ops_created_by — uuid — FK → public.user(id)
  The ops user who added this record.
  Example: 'ops-user-uuid-abc1-2345-6789-defg98765432'

---

public.special_users

Defines system-level special users — e.g., super admins.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  Unique ID for this special user record.
  Example: 'sp-uuid-1234-5678-90ab-cdef54321098'

• created_at — timestamp with time zone — NOT NULL, Default: now()
  When this user got special status.
  Example: '2025-07-02T12:00:00Z'

• updated_at — timestamp with time zone — Default: now()
  Last update.
  Example: '2025-07-02T12:30:00Z'

• user_id — uuid — FK → public.user(id)
  The user who has this special status.
  Example: 'user-uuid-9999-8888-7777-666655554444'

• is_deleted — boolean — Flag for soft deletion.
  Example: false

• role — USER-DEFINED — The special role name.
  Example: 'super_admin', 'operational_director'

  super_admin — System-level administrator with full access across all programs, schools, and users.

  operational_director — Senior oversight role with full access across all programs, schools, and users.

---

public.sticker

Stores collectible stickers for rewards/gamification.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  Unique sticker ID.
  Example: 'st-uuid-1234-5678-90ab-cdefabcdef12'

• name — character varying — NOT NULL. Sticker name.
  Example: 'car'

• image — text — URL of the sticker’s image.
  Example: 'https://cdn.myapp.com/stickers/star_performer.png'

• description — text — Short description or how it’s earned.
  Example: 'Awarded to students who score above 90% in weekly quizzes.'

• created_at — timestamp with time zone — NOT NULL, Default: now()
  When this sticker was created.
  Example: '2025-07-02T12:15:00Z'

• updated_at — timestamp with time zone — Default: now()
  Last update.
  Example: '2025-07-02T12:45:00Z'

• is_deleted — boolean — Default: false
  Soft delete flag.
  Example: false

---

public.subject

Stores subjects.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  Unique subject ID.
  Example: 'sub-uuid-1234-5678-90ab-cdefabcd1234'

• name — character varying — NOT NULL. The name of the subject.
  Example: 'Maths', 'Hindi'

• image — text — URL of an icon or image for the subject.
  Example: 'https://cdn.myapp.com/subjects/maths_icon.png'

• description — text — Short description.
  Example: 'Covers numbers, algebra, geometry, and basic calculations.'

• sort_index — integer — Used for UI ordering.
  Example: 1

• created_at — timestamp with time zone — NOT NULL, Default: now()
  When this subject was created.
  Example: '2025-07-02T13:00:00Z'

• updated_at — timestamp with time zone — Default: now()
  Last update.
  Example: '2025-07-02T13:30:00Z'

• is_deleted — boolean — Default: false
  Soft delete flag.
  Example: false

---

public.upload_queue

Manages a queue for asynchronous processing of bulk data uploads in ops — useful for handling large, user batch creation, etc.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  Unique ID for this upload job.
  Example: 'uploadqueue-uuid-1234-5678-90ab-cdef12345678'

• uploading_user — uuid — FK → public.user(id)
  The user who initiated the upload request.
  Example: 'user-uuid-1111-2222-3333-444455556666'

• start_time — timestamp with time zone
  The scheduled time for the system to start processing this upload.
  Example: '2025-07-02T11:30:00Z'

• payload — jsonb — NOT NULL
  The data being uploaded — stored as JSONB for flexible schema. Typically contains a bulk of rows.
  Example: { "students": [ {"name": "Ankit"}, {"name": "Ravi"} ] }

• status — text — Default: 'pending'
  The current status of this upload job. Common values: 'pending', 'processing', 'completed', 'failed'.
  Example: 'processing'

• error — text
  Stores any error message if the upload job fails.
  Example: 'File format not supported. Only CSV allowed.'

• process_started_at — timestamp with time zone — Default: now()
  When the system actually started processing this upload.
  Example: '2025-07-02T11:35:00Z'

• batch_number — bigint
  An optional number to group related uploads (for example, multiple chunks of a huge dataset).
  Example: 20250702

• is_locked — boolean — Default: false
  True if this job is locked by a worker to prevent parallel processing.
  Example: true

• locked_at — timestamp with time zone
  When this job was locked by a worker for exclusive processing.
  Example: '2025-07-02T11:34:59Z'

• locked_by — text
  Identifier for the worker or server node that locked this job.
  Example: 'worker-1'

---

public.user

The central table for storing all user profile information: students, teachers, parents, admins.

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  Unique ID for this user.
  Example: 'user-uuid-1234-5678-90ab-cdef12345678'

• name — text
  Full name of the user.
  Example: 'Ankit Kumar'

• email — text
  User’s email address.
  Example: 'ankit.kumar@example.com'

• phone — text
  User’s phone number.
  Example: '+91-9876543210'

• gender — text
  Gender of the student.
  Example: 'Male'

• image — text
  URL to the profile picture.
  Example: 'https://cdn.schoolapp.com/profiles/user123.jpg'

• avatar — text
  user’s selected avatar.
  Example: 'owl'

• language_id — uuid — FK → public.language(id) Preferred language.
  Example: 'lang-uuid-aaaa-bbbb-cccc-ddddeeeeefff'

• curriculum_id — uuid — FK → public.curriculum(id) Curriculum preference.
  Example: 'curriculum-uuid-1234'

• created_at — timestamp with time zone — NOT NULL, Default: now()
  When the user was created.
  Example: '2025-07-02T12:00:00Z'

• updated_at — timestamp with time zone — Default: now()
  Last updated timestamp.
  Example: '2025-07-02T12:30:00Z'

• is_deleted — boolean — Default: false
  Soft delete flag.
  Example: false

• is_tc_accepted — boolean — Default: false
  True if user accepted Terms & Conditions.
  Example: true

• age — integer
  Age of the student.
  Example: 4

• grade_id — uuid — FK → public.grade(id) Current grade of student.
  Example: 'grade-uuid-9999-8888'

• music_off — boolean — Default: false
  True if user muted background music.
  Example: true

• sfx_off — boolean — Default: false
  True if user muted sound effects.
  Example: false

• fcm_token — text
  Firebase token for push notifications.
  Example: 'fcm:abc123xyz'

• student_id — text
  Unique student ID.
  Example: 'SCH12345'

• firebase_id — text
  Firebase Auth ID.
  Example: 'firebase-uid-abcdefg'

• is_firebase — boolean — Default: false
  True if synced from Firebase.
  Example: true

• is_ops — boolean — Default: false
  True if this is an ops/admin account.
  Example: true

• stars — integer
  Total stars student earned.
  Example: 80

• learning_path — text
  ID for assigned learning path.
  Example: '"courses":{"courseList":[{"path_id":"87b1b637-9e2a-465e-b962-8e4b233e75ae","course_id":"63e40488-3c1a-47ab-aa8a-6f07ad21709f","subject_id":"54abf22e-7102-4e14-915b-acd8eab47d56","path":[{"lesson_id":"59cfd524-4a11-4246-a7aa-bbb37f068fd1","chapter_id":"6500f059-c866-446b-a669-ccc1a82225e6"},{"lesson_id":"9e4aa671-074a-4855-a064-640300855a5e",}]}'

• ops_created_by — uuid → [public.user(id)]
  Admin user who created this record.
  Example: 'ops-user-uuid-5555'

• reward — text
  JSON object containing reward information (e.g., last received reward and timestamp).
  Example:

   {
    "reward_id": "262cc636-3618-4736-a7f6-dd253b990a5c",
    "timestamp": "2025-10-24T09:49:13.018Z"
   }
  
  

---

public.user_badge

Tracks which badges a user has earned.

• user_id — uuid — NOT NULL, FK → public.user(id) The user who owns the badge.
  Example: 'user-uuid-1234'

• badge_id — uuid — NOT NULL, FK → public.badge(id) The badge awarded.
  Example: 'badge-uuid-9876'

• created_at — timestamp with time zone — NOT NULL, Default: now()
  When the badge was earned.
  Example: '2025-07-02T13:00:00Z'

• updated_at — timestamp with time zone — Default: now()
  Last updated.
  Example: '2025-07-02T13:05:00Z'

• is_deleted — boolean — Default: false
  Soft delete.
  Example: false

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  Unique row ID.
  Example: 'userbadge-uuid-5555'

• is_seen — boolean
  True if user has seen the badge in app.
  Example: true

• is_firebase — boolean — Default: false
  Synced from Firebase?
  Example: false

---

public.user_bonus

Tracks bonus lessons unlocked by the user on every month.

• user_id — uuid — NOT NULL, FK → public.user(id) User receiving the bonus.
  Example: 'user-uuid-1234'

• bonus_id — uuid — NOT NULL, FK → public.lesson(id) Bonus lesson ID.
  Example: 'lesson-uuid-abc123'

• created_at — timestamp with time zone — NOT NULL, Default: now()
  When bonus was granted.
  Example: '2025-07-02T13:10:00Z'

• updated_at — timestamp with time zone — Default: now()
  Last update.
  Example: '2025-07-02T13:15:00Z'

• is_deleted — boolean — Default: false
  Soft delete.
  Example: false

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  Unique ID.
  Example: 'userbonus-uuid-3333'

• is_seen — boolean
  True if user saw this bonus.
  Example: true

• is_firebase — boolean — Default: false
  Synced from Firebase?
  Example: false

---

public.user_course

Links users to courses (enrollment).

• user_id — uuid — NOT NULL, FK → public.user(id) student enrolled.
  Example: 'user-uuid-5555'

• course_id — uuid — NOT NULL, FK → public.course(id) Course enrolled for the students.
  Example: 'course-uuid-9999'

• created_at — timestamp with time zone — NOT NULL, Default: now()
  Enrollment date.
  Example: '2025-07-02T14:00:00Z'

• updated_at — timestamp with time zone — Default: now()
  Last update.
  Example: '2025-07-02T14:05:00Z'

• is_deleted — boolean — Default: false
  Soft delete.
  Example: false

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  Unique ID.
  Example: 'usercourse-uuid-8888'

• is_firebase — boolean — Default: false
  Synced from Firebase?
  Example: false

---

public.user_sticker

Tracks stickers collected by a user.

• user_id — uuid — NOT NULL, FK → public.user(id) Student who owns the sticker.
  Example: 'user-uuid-5555'

• sticker_id — uuid — NOT NULL, FK → public.sticker(id) Sticker awarded.
  Example: 'sticker-uuid-7777'

• created_at — timestamp with time zone — NOT NULL, Default: now()
  When the sticker was awarded.
  Example: '2025-07-02T15:00:00Z'

• updated_at — timestamp with time zone — Default: now()
  Last update.
  Example: '2025-07-02T15:05:00Z'

• is_deleted — boolean — Default: false
  Soft delete.
  Example: false

• id — uuid — Primary Key, NOT NULL, Default: gen_random_uuid()
  Unique row ID.
  Example: 'usersticker-uuid-1010'

• is_seen — boolean
  True if user has seen the sticker.
  Example: true

• is_firebase — boolean — Default: false
  Synced from Firebase?
  Example: false