Initial APIs

[hadron43]

Schema for Database

Users

Field Name	Data Type	Description
id	SERIAL PRIMARY KEY	Internal user ID
google_user_id	VARCHAR(255)	Google sub claim – unique user identifier
email	VARCHAR(320)	User's email address
full_name	VARCHAR(255)	Full name of the user
profile_pic_url	TEXT	URL to user's Google profile picture
last_login_at	TIMESTAMP	Timestamp of last login
created_at	TIMESTAMP DEFAULT CURRENT_TIMESTAMP	Record creation time

Pupil

Field Name	Data Type	Description
id	SERIAL PRIMARY KEY	Unique internal identifier for each pupil
full_name	VARCHAR(255)	Pupil's full name
email	VARCHAR(320)	Email address (optional/unique)
mobile	VARCHAR(15)	Mobile number
father_name	VARCHAR(255)	Father's full name
mother_name	VARCHAR(255)	Mother's full name
date_of_birth	DATE	Date of birth
gender	VARCHAR(20)	Gender (M/F/Other)
enrolled_on	DATE	Date of enrollment
created_at	TIMESTAMP DEFAULT CURRENT_TIMESTAMP	Record creation time
updated_at	TIMESTAMP DEFAULT CURRENT_TIMESTAMP	Last modified time

Groups

📦 Table: groups

Field Name	Data Type	Description
id	SERIAL PRIMARY KEY	Unique group ID
name	VARCHAR(255)	Group name (e.g., "Math Batch A")
description	TEXT	Optional details about the group
created_at	TIMESTAMP DEFAULT CURRENT_TIMESTAMP	Creation timestamp

🔗 Table: pupil_group_membership

(This junction table connects pupils with groups.)

Field Name	Data Type	Description
id	SERIAL PRIMARY KEY	Unique ID for each membership row
pupil_id	INTEGER	References pupils(id)
group_id	INTEGER	References groups(id)
joined_at	TIMESTAMP DEFAULT CURRENT_TIMESTAMP	When the pupil was added

🔒 Add foreign key constraints to ensure referential integrity:

ALTER TABLE pupil_group_membership
ADD CONSTRAINT fk_pupil
FOREIGN KEY (pupil_id) REFERENCES pupils(id) ON DELETE CASCADE;

ALTER TABLE pupil_group_membership
ADD CONSTRAINT fk_group
FOREIGN KEY (group_id) REFERENCES groups(id) ON DELETE CASCADE;

Calendar Events

📅 Calendar Events Schema

🔸 Table: events

Field Name	Data Type	Description
id	SERIAL PRIMARY KEY	Unique event ID
title	VARCHAR(255)	Short name or title for the event
description	TEXT	Optional detailed description
event_type	VARCHAR(20)	'once' or 'repeat'
start_time	TIMESTAMP	When the event starts
end_time	TIMESTAMP	When the event ends
repeat_pattern	VARCHAR(30)	'weekly', 'monthly', or 'custom_days' (nullable)
repeat_until	DATE	Optional end date for recurring events
created_at	TIMESTAMP DEFAULT CURRENT_TIMESTAMP	Timestamp of creation
updated_at	TIMESTAMP DEFAULT CURRENT_TIMESTAMP	Last updated time

---

🔁 Table: event_repeat_days

(Only used when repeat_pattern = 'custom_days')

Field Name	Data Type	Description
id	SERIAL PRIMARY KEY	Unique row ID
event_id	INTEGER	References events(id)
day_of_week	INTEGER	0=Sunday to 6=Saturday

---

👥 Table: event_pupils

(Connects pupils to events — many-to-many)

Field Name	Data Type	Description
id	SERIAL PRIMARY KEY	Unique row ID
event_id	INTEGER	References events(id)
pupil_id	INTEGER	References pupils(id)
added_at	TIMESTAMP DEFAULT CURRENT_TIMESTAMP	When the pupil was added

---

🔐 Foreign Key Constraints:

ALTER TABLE event_repeat_days
ADD FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE;

ALTER TABLE event_pupils
ADD FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE;

ALTER TABLE event_pupils
ADD FOREIGN KEY (pupil_id) REFERENCES pupils(id) ON DELETE CASCADE;

💰 Pupil Payments Schema (SQL)

🧾 Table: payments

Field Name	Data Type	Description
id	SERIAL PRIMARY KEY	Unique payment record ID
pupil_id	INTEGER	References pupils(id)
amount	NUMERIC(10,2)	Amount paid (e.g., 2500.00)
month	INTEGER	Month of the payment (1 = January, ..., 12)
year	INTEGER	Year of the payment (e.g., 2025)
payment_date	DATE	Actual calendar date the payment was made
payment_mode	VARCHAR(50)	Mode of payment (e.g., cash, UPI, bank transfer)
notes	TEXT	Optional notes or comments (e.g., late fee)
created_at	TIMESTAMP DEFAULT CURRENT_TIMESTAMP	Timestamp when record was created

---

🔐 Foreign Key Constraint

ALTER TABLE payments
ADD CONSTRAINT fk_pupil_payment
FOREIGN KEY (pupil_id) REFERENCES pupils(id) ON DELETE CASCADE;

APIs

• CRUD on users
• Pupils
• Login of users
• Groups Management
• Calendar Events
• Payments