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
