DAC Academy — Database Functional Map

1. DATA DOMAIN OVERVIEW

The DAC Academy database contains 34 tables organized into these functional domains:

Domain	Tables	Purpose
Academy Configuration	4	Academy settings, branches, sessions, courses
CRM (Leads)	2	Lead tracking and follow-up history
Registration	1	Paid registration bridge between lead and admission
Admissions	4	Admission records, fee components, checklist, items
Students	4	Student profiles, session enrollment, batch assignment
Fee Management	5	Fee types, batch templates, fee records, installments, components
Receipts	1	Immutable receipt generation
Hostel Management	4	Hostels, allocations, outings, medical requests
Communication	2	Announcements, WhatsApp logs
System	4	Users, audit logs, background jobs, notification settings
Authentication	3	Better Auth: session, account, verification

2. KEY RELATIONSHIP DIAGRAM (TEXT)

leads ──→ lead_followups
  │
  ├──→ registrations ──→ receipts (REG kind)
  │
  └──→ students ──→ student_sessions ──→ academic_sessions
         │             │
         │             └──→ admissions ──→ admission_items
         │                               ├── admission_checklist ──→ checklist_items
         │                               └── fee_components ──→ fee_types
         │
         ├──→ fee_records ──→ fee_installments ──→ receipts (ADM kind)
         │
         ├──→ hostel_allocations ──→ hostels ──→ branches
         │
         ├──→ hostel_outings
         │
         ├──→ medical_requests
         │
         └──→ receipts (ADM kind)

batches ──→ courses
batches ──→ branches
batches ──→ batch_fee_templates ──→ fee_types

users ──→ branches
users ──→ hostels

branches ──→ hostels

3. FINANCIAL INVARIANTS (DB-ENFORCED)

Table	Constraint	Formula	Enforcement
`fee_records`	`fee_aggregate_invariant`	`totalFeePaise = totalPaidPaise + remainingPaise`	CHECK constraint
`fee_records`	`fee_non_negative`	All three >= 0	CHECK constraint
`fee_records`	`fee_overpayment_invariant`	`totalPaidPaise <= totalFeePaise`	CHECK constraint
`admissions`	`admission_non_negative`	All three >= 0	CHECK constraint
`courses`	`course_fee_non_negative`	`baseFeePaise >= 0`	CHECK constraint
`batches`	`batches_fees_non_negative`	All fee columns >= 0	CHECK constraint
`registrations`	`reg_amounts_non_negative`	Fee and amount >= 0	CHECK constraint
`fee_installments`	`installment_amount_non_negative`	`amountPaise >= 0`	CHECK constraint
`receipts`	`receipt_amount_non_negative`	`amountPaise >= 0`	CHECK constraint

4. SOFT DELETE PATTERN

Tables using isDeleted boolean (never hard-deleted): admissions, admission_items, admission_checklist, fee_records, fee_installments, hostel_allocations, hostel_outings, receipts

Tables NOT soft-deleted (immutable/append-only): audit_log, receipts (use isVoided instead)

5. MONEY STORAGE

All monetary values stored as paise (integer), never floating point:

*Paise suffix on all money columns
Convert to rupees for display: amount / 100
Format: toLocaleString('en-IN') with ₹ symbol