Skip to main content

EPIC-3 Database Schema

Entity Relationship Diagram​

Core Tables​

students​

CREATE TABLE students (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    admission_no VARCHAR(20) NOT NULL UNIQUE,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    dob DATE NOT NULL,
    gender VARCHAR(10) NOT NULL,
    
    -- Encrypted PII (DPDPA)
    aadhaar_encrypted BYTEA,
    aadhaar_hash VARCHAR(64),
    
    -- Academic
    current_class_id UUID REFERENCES classes(id),
    current_section_id UUID REFERENCES sections(id),
    roll_number SMALLINT,
    
    -- Status
    status VARCHAR(20) DEFAULT 'ACTIVE',
    admission_date DATE NOT NULL,
    
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_students_admission_no ON students(admission_no);
CREATE INDEX idx_students_class_section ON students(current_class_id, current_section_id);
CREATE INDEX idx_students_status ON students(status) WHERE status = 'ACTIVE';

classes​

CREATE TABLE classes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(20) NOT NULL,
    grade_level SMALLINT NOT NULL,
    board VARCHAR(20) DEFAULT 'CBSE',
    stream VARCHAR(20),
    academic_year VARCHAR(9) NOT NULL,
    is_active BOOLEAN DEFAULT true,
    UNIQUE(name, academic_year)
);

sections​

CREATE TABLE sections (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    class_id UUID NOT NULL REFERENCES classes(id),
    name VARCHAR(5) NOT NULL,
    capacity SMALLINT DEFAULT 40,
    current_strength SMALLINT DEFAULT 0,
    class_teacher_id UUID REFERENCES staff(id),
    UNIQUE(class_id, name)
);

guardians​

CREATE TABLE guardians (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    relationship VARCHAR(20) NOT NULL,
    
    -- Encrypted
    phone_encrypted BYTEA NOT NULL,
    phone_hash VARCHAR(64),
    email VARCHAR(255),
    
    occupation VARCHAR(100)
);

admission_inquiries​

CREATE TABLE admission_inquiries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    inquiry_number VARCHAR(20) NOT NULL UNIQUE,
    source VARCHAR(20) DEFAULT 'WEB',
    
    parent_name VARCHAR(200) NOT NULL,
    parent_phone VARCHAR(15) NOT NULL,
    child_name VARCHAR(200) NOT NULL,
    applying_for_class VARCHAR(20) NOT NULL,
    
    status VARCHAR(20) DEFAULT 'NEW',
    
    -- AI Chat
    ai_chat_history JSONB,
    
    created_at TIMESTAMPTZ DEFAULT NOW()
);

student_documents​

CREATE TABLE student_documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    student_id UUID NOT NULL REFERENCES students(id),
    document_type VARCHAR(50) NOT NULL,
    s3_key VARCHAR(500) NOT NULL,
    
    is_verified BOOLEAN DEFAULT false,
    verified_by UUID REFERENCES staff(id),
    
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Data Flow​