Database Documentation

PostgreSQL. SkoolyHub uses the @neondatabase/serverless driver over HTTP in production and postgres.js locally, both behind one tagged-template client at @/lib/db.

No migration files

Every table is created by an idempotent ensure*Schema() function that runs before the first query against its tables. New columns arrive as ALTER TABLE … ADD COLUMN IF NOT EXISTS. A fresh database and one several versions old converge on the same schema without anyone running migrations in the right order.

import sql from "@/lib/db";

let ready = false;
export async function ensureFeeSchema() {
  if (ready) return;
  await sql`CREATE TABLE IF NOT EXISTS fee_heads ( … )`;
  await sql`ALTER TABLE fee_invoices ADD COLUMN IF NOT EXISTS currency VARCHAR(10) DEFAULT 'INR'`;
  ready = true;
}

The ready flag makes it once-per-process, not once-per-request.

Conventions

Convention	Why
`snake_case` columns	Postgres convention; TypeScript fields mirror them
Money columns end `_cents`	Integer minor units — no float ever touches money
`public_id` for anything addressable	URLs expose an opaque id, never a sequential one. Registered in `@/lib/public-id`
`created_at` / `updated_at` on records	Ordering, and the feed's cursor
Structural enums are TypeScript unions	Renaming "absent" would break queries
Editable labels are `master_items` rows	Renaming a designation must not break anything
Partial unique indexes for optional credentials	An email that is present must be unique; absent is fine

That last one has a sharp edge worth knowing:

SQL

CREATE UNIQUE INDEX guardians_email_ci ON guardians(lower(email)) WHERE email <> '';

Postgres cannot infer a partial index for ON CONFLICT without repeating its predicate:

SQL

INSERT INTO guardians (…) VALUES (…)
ON CONFLICT (lower(email)) WHERE email <> '' DO NOTHING;

The school domain

Academic backbone — `@/lib/school/schema.ts`

Table	Holds
`academic_years`	Years, one marked current. The scoping key for nearly everything
`terms`	Terms within a year
`school_classes`	Year groups
`class_sections`	Sections of a class, with capacity and class teacher
`subjects`	Subjects taught
`class_subjects`	Subject allocated to a class, optionally with a teacher
`school_periods`	The bell schedule; breaks included
`timetable_slots`	Period × day × section → subject, teacher, room
`school_calendar`	Events and holidays, published to families

People — `@/lib/school/people.ts`

Table	Holds
`students`	The roll, with placement, photo, medical notes
`guardians`	Parent accounts, verification state, notification preferences
`student_guardians`	The link, with primary and can-collect flags
`student_enrollments`	Placement history, one row per student per year
`student_documents`	Uploaded documents
`staff`	Teaching and support staff, credentials, notification preferences

childIdsOf(guardianId), guardianOwnsStudent() and teacherSectionIds() live here — the authorization primitives every portal endpoint scopes on.

Attendance — `@/lib/school/attendance.ts`

Table	Holds
`attendance`	Student attendance; `period_id IS NULL` is the DAY register
`staff_attendance`	Staff, with optional check-in / check-out

Fees — `@/lib/school/fees.ts`

Table	Holds
`fee_heads`	What can be charged
`fee_structures` / `fee_structure_items`	Per-class charge sets
`fee_concessions`	Per-student discounts
`fee_invoices` / `fee_invoice_items`	Issued invoices
`fee_payments`	The receipt ledger — the source of truth
`fee_payment_attempts`	A gateway payment in flight

The invoice's paid_cents and status are always recomputed from fee_payments. They are never set from a request. fee_payment_attempts exists so a payment's amount lives somewhere the client cannot reach between starting and settling — settling credits the stored amount, never a number from the callback.

Assessment — `@/lib/school/exams.ts`

Table	Holds
`exams`	Exams, with a status that gates visibility
`exam_schedules`	Per-subject papers, max and passing marks
`marks`	Marks per student per paper
`grade_scales`	Percentage → grade and points
`report_cards`	Computed totals, percentage, grade, rank

Results require report_cards.is_published and exams.status = 'published'.

Homework, communication, admissions

Table	Holds
`homework` / `homework_submissions`	Assignments and submitted work
`circulars` / `circular_reads`	Announcements and read receipts
`school_notifications`	Per-recipient feed the apps read
`leave_requests`	Student and staff leave
`message_threads` / `thread_messages`	Parent–teacher threads (admin only today)
`admission_applications`	The pipeline
`school_devices`	Push tokens

Facilities and HR

library_books, library_copies, library_loans · transport_routes, transport_stops, transport_vehicles, transport_assignments · hostels, hostel_rooms, hostel_allocations, hostel_visitors · inventory_items, inventory_suppliers, inventory_movements · staff_qualifications, staff_documents, salary_structures, payslips, syllabus_entries.

Platform tables

Shared with the site and admin rather than the school domain: users, roles, permissions, settings, theme_settings, app_settings, master_items, media_assets, pages, menus, menu_items, blogs, faqs, partners, hero_sliders, contacts, enquiries, newsletter, translations, languages, countries, currencies, integration_connections, message_templates, notification_templates, notification_logs, webhooks, api_tokens, activity_log, plugin_state, app_license, addon_codes.

integration_connections holds storage, payment, email and SMS credentials encrypted, which is why they survive a database dump and move with it. The encryption key derives from SECRET_KEY, falling back to JWT_SECRET — so two deployments sharing a secret can read each other's stored credentials, and two that do not, cannot.

Querying safely

// Parameterised by construction.
const rows = await sql`SELECT * FROM students WHERE section_id = ${sectionId}`;

// For a dynamic list, rawSql with $1 placeholders.
const rows = await rawSql(`SELECT * FROM students WHERE id = ANY($1)`, [ids]);

Two traps specific to this codebase:

The Neon HTTP driver cannot compose nested sql fragments. Build values in JS and interpolate them, rather than assembling query pieces.
A backtick inside a sql template literal terminates it. Never use one in a SQL comment.

Backups

pg_dump and pg_restore against the direct (unpooled) endpoint — the PgBouncer pooler stalls large single-transaction restores. See Backup and Restore.